Archive for category ORACLE DBA

Oracle Directory oluşturma

C:\dump>sqlplus / as sysdba;

SQL> create directory testdir as 'C:\dump';

Directory created.

SQL> grant read ,write on directory testdir to sezayir;

Grant succeeded.

SQL> grant read ,write on directory testdir to hr;

Grant succeeded.

SQL> select directory_name from all_directories;

DIRECTORY_NAME
------------------------------
TESTDIR
ORACLECLRDIR
XMLDIR
DATA_PUMP_DIR
ORACLE_OCM_CONFIG_DIR

SQL> select directory_path from all_directories;

DIRECTORY_PATH
--------------------------------------------------------------------------------
C:\dump
C:\oraclexe\app\oracle\product\11.2.0\server\bin\clr
C:\oraclexe\app\oracle\admin\xe\dpdump\
C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\xml
C:/ADE/jaikrish_xe/oracle\ccr\state

Advertisements

Leave a comment

Oracle komutlar

IMP_FULL_DATABASE

SQL> grant IMP_FULL_DATABASE to sezayir;
Grant succeeded.

export whole schema
C:\>expdp hr/hr directory=testdir dumpfile=exp.dmp logfile=exp.log

import schema -two tables
C:\> impdp hr/hr directory=testdir dumpfile=exp.dmp tables=employees,departments remap_schema=hr:sezayir logfile=exp.log

Directory commands
SQL> create directory testdir as 'C:\dump';
SQL> select directory_name from all_directories;
SQL> select directory_path from all_directories;
SQL> grant read ,write on directory testdir to sezayir;
SQL> drop directory testdir;

, ,

Leave a comment

Export whole hr schema-import 2 tables

C:\dump>sqlplus / as sysdba;

SQL> create directory testdir as 'C:\dump';

Directory created.

SQL> grant read ,write on directory testdir to sezayir;

Grant succeeded.

SQL> grant read ,write on directory testdir to hr;

Grant succeeded.

SQL> select directory_name from all_directories;

DIRECTORY_NAME
------------------------------
TESTDIR
ORACLECLRDIR
XMLDIR
DATA_PUMP_DIR
ORACLE_OCM_CONFIG_DIR

SQL> select directory_path from all_directories;

DIRECTORY_PATH
--------------------------------------------------------------------------------
C:\dump
C:\oraclexe\app\oracle\product\11.2.0\server\bin\clr
C:\oraclexe\app\oracle\admin\xe\dpdump\
C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\xml
C:/ADE/jaikrish_xe/oracle\ccr\state

C:\>sqlplus hr/hr

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> select * from cat;

TABLE_NAME TABLE_TYPE
------------------------------ -----------
COUNTRIES TABLE
DEPARTMENTS TABLE
DEPARTMENTS_SEQ SEQUENCE
EMPLOYEES TABLE
EMPLOYEES_SEQ SEQUENCE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
LOCATIONS_SEQ SEQUENCE
REGIONS TABLE

11 rows selected.

SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>

C:\>expdp hr/hr directory=testdir dumpfile=exp.dmp logfile=exp.log

Export: Release 11.2.0.2.0 - Production on Thu Feb 28 12:19:40 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/******** directory=testdir dumpfile=exp.dmp logfile=exp.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 448 KB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows
. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows
. . exported "HR"."JOBS" 6.992 KB 19 rows
. . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows
. . exported "HR"."LOCATIONS" 8.273 KB 23 rows
. . exported "HR"."REGIONS" 5.476 KB 4 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
C:\DUMP\EXP.DMP
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:21:04

C:\>cd dump

C:\dump>dir
Volume in drive C has no label.
Volume Serial Number is C0A2-D319

Directory of C:\dump

02/28/2013 12:19 PM .
02/28/2013 12:19 PM ..
02/28/2013 12:21 PM 487,424 EXP.DMP
02/28/2013 12:21 PM 2,126 exp.log
2 File(s) 489,550 bytes
2 Dir(s) 11,240,493,056 bytes free

C:\>impdp hr/hr directory=testdir dumpfile=exp.dmp tables=employees,departments remap_schema=hr:sezayir logfile=exp.log

Import: Release 11.2.0.2.0 - Production on Thu Feb 28 12:32:53 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
Master table "HR"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_TABLE_01": hr/******** directory=testdir dumpfile=exp.dmp tables=employees,departments remap_schema=hr:sezayir logfile
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SEZAYIR"."DEPARTMENTS" 7.007 KB 27 rows
. . imported "SEZAYIR"."EMPLOYEES" 16.80 KB 107 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "SEZAYIR"."DEPARTMENTS" ADD CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATION_ID") REFERENCES "SEZAYIR"."LOCATIONS" ("LOCATION_ID") ENA
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "SEZAYIR"."EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "SEZAYIR"."JOBS" ("JOB_ID") ENABLE
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"SEZAYIR"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"SEZAYIR"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"SEZAYIR"."UPDATE_JOB_HISTORY" created with compilation warnings
ORA-39082: Object type TRIGGER:"SEZAYIR"."UPDATE_JOB_HISTORY" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "HR"."SYS_IMPORT_TABLE_01" completed with 6 error(s) at 12:33:02

C:\>

SQL> show user
USER is "SEZAYIR"
SQL>

SQL> select *from cat;

TABLE_NAME TABLE_TYPE
------------------------------ -----------
DEPARTMENTS TABLE
EMPLOYEES TABLE
LOGIN_USER TABLE

SQL>

, ,

Leave a comment