CONTENTS page
1. Steps to be performed in Production Database 1
2 Steps to be performed in Test/Dev Database 3
3. Exercises 5
1. Steps to be performed in Production Database
• Check the size of the schema
select sum(BYTES)/1024/1024 from dba_segments where owner='SLADMIN';
• Check type of objects and status of the objects the schema.
col OBJECT_TYPE for a18
set pages 100
col owner for a10
select owner ,object_type,status, count(*) from dba_objects
where owner='DSADMIN' group by owner, object_type, status;
• Check the total number of objects in the schema.
select count(*) from dba_objects where owner like 'DSADMIN';
• Check for constraints validity in the schema.
Select constraint_type,count(*) from dba_constraints where owner='DSADMIN'
Group by constraint_type;
• Take an export of the Database scheme
exp file=exp_DSADMIN_.dmp log=exp_DSADMIN_dev_bkup.log owner=DSADMIN compress=n buffer=20480000 consistent=y
Should Show: “export terminated successfully without warnings”
To list errors:
cat exp_DSADMIN_dev_bkup.log | grep EXP-
Note:In Oracle 10g you can use both expdp along with exp utility.
To take an export in Oracle 10g
1) Export schema
C:\Documents and Settings\201910>expdp system/manager DIRECTORY=MY_DIR dumpfile=scott2.dmp schemas=scott
Copy the file from production to the export directory of staging/dev/test using scp.
scp exp_DSADMIN_dev_02feb09.dmp oradb:/oraclus1s/exp01/dev
enter oracle@gemsora10's password for oradb
oracle@gemsora10's password:
exp_DSADMIN_EPROD_22d 100% |***********************************************| 604 MB 01:31
ls –ltr
2. Steps to be performed in Test /Dev Database
Take a backup of scheme before proceeding with the below steps.
1. Drop the tables and objects from staging before importing dmp:
Also check for constraints validity before dropping
select 'drop table DSADMIN.'||table_name||' cascade constraints;' from dba_tables where owner='DSADMIN';
Note: This will drop tables and dependent objects.
2. Drop all the remaining objects
select 'drop '||object_type||' DSADMIN.'||object_name||';' from dba_objects where owner=DSADMIN';
10g DROP objects
select 'drop table DSADMIN.'||table_name||' cascade constraints purge;' from dba_tables where owner='SLADMIN';
select 'purge table DSADMIN."'||object_name||'";' FROM dba_RECYCLEBIN;
select 'drop '||object_type||' DSADMIN.'||object_name||';' from dba_objects where owner='SLADMIN';
select count(*) from dba_objects where owner='DSADMIN';
3 After dropping, import the objects in staging from production:
imp fromuser=DSADMIN touser=DSADMIN file=exp_DSADMIN_EPROD_4feb05.dmp log=imp_DSADMIN_EPROD_4feb05.log commit=y resumable=y buffer=40000000
To perform and import using impdp utility in Oracle 10g
C:\Documents and Settings\201910>impdp system/manager dumpfile=scott3.dmp remap_schema=SCOTT:MDDATA LOGFILE=MDDATA.LOG DIRECTORY=MY_DIR
We can ignore the following error:
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "TBL_SCHEDULE_DETAILS" TO "DSADMIN_ICENTER_ROLE""
IMP-00003: ORACLE error 1917 encountered
Count should match with that of the Production database
select distinct status from dba_objects where owner='SLADMIN';
Should show as VALID
If invalid, compile it using run this script.:
alter owner.object_name compile;
select 'alter '||object_type||' DSADMIN.' ||object_name||' compile;' from dba_objects where owner='DSADMIN' and status='INVALID';
Execute below script to compile all invalid objects in the database.
@?/rdbms/admin/utlrp.sql
3. Exercises
• Export schema
C:\Documents and Settings\201910>expdp system/manager DIRECTORY=MY_DIR dumpfile=scott2.dmp schemas=scott
Note: USER SCHEMA IS CREATED BY IMPORT IF IT DOES NOT EXIST
The schema will only be created if the export dump file set contains
the necessary CREATE USER metadata and you are importing with enough
privileges
• Import schema
impdp directory=MY_DIR DUMPFILE=scott2.dmp remap_schema=SCOTT:SCOTT LOGFILE=importscott.log
• Export TABLE
1) expdp DIRECTORY=MY_DIR dumpfile=EMPK_TABLE.DMP logfile=EMP_TABLE.LOG tables=scott.empk
2) C:\Documents and Settings\201910>expdp dumpfile=MY_DIR:dept_table.dmp logfile=MY_DIR:dept_table.log tables=scott.dept
Export: Release 10.1.0.2.0 - Production on Saturday, 29 December, 2007 10:26
Copyright (c) 2003, Oracle. All rights reserved.
Username: SCOTT
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": SCOTT/******** dumpfile=MY_DIR:dept_table.dmp logfile=MY_DIR:dept_table.log tables=scott.dept
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.703 KB 6 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
C:\EXPDP_OUT\DEPT_TABLE.DMP
Job "SCOTT"." " successfully completed at 10:26
• EXPORT METADATA_ONLY
expdp DIRECTORY=MY_DIR dumpfile=EMPK_TABLE_M1.DMP logfile=EMP_TABLE_METADATA1.LOG tables=scott.empk CONTENT=METADATA_ONLY
• Loading objects of one schema into another schema.
1 select owner ,object_type,status, count(*) from dba_objects
2* where owner='SCOTT' group by owner, object_type, status
SQL> /
OWNER OBJECT_TYPE STATUS COUNT(*)
---------- ------------------ ------- ---------- ------------------------
SCOTT INDEX VALID 2
SCOTT TABLE VALID 7
SCOTT PACKAGE VALID 2
SCOTT TRIGGER VALID 1
SCOTT FUNCTION VALID 1
SCOTT FUNCTION INVALID 5
6 rows selected.
• Dropping existing objects in MDDATA Schema
select 'drop table MDATA.'||table_name||' cascade constraints purge;' from dba_tables where owner=' MDATA ';
select 'drop '||object_type||' SCOTT.'||object_name||';' from dba_objects where owner=' MDATA ';
• C:\Documents and Settings\201910>impdp system/manager dumpfile=scott3.dmp remap_schema=SCOTT:MDDATA LOGFILE=MDDATA.LOG DIRECTORY=MY_DIR
Import: Release 10.1.0.2.0 - Production on Saturday, 29 December, 2007 10:57
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=scott3.dmp remap_schema=SCOTT:MDDATA LOGFILE=MDDATA.LOG DIRECTORY=MY_DIR
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"MDDATA" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MDDATA"."DEPT" 5.703 KB 6 rows
. . imported "MDDATA"."DEPT_OLD" 5.687 KB 5 rows
. . imported "MDDATA"."EMPK" 7.820 KB 14 rows
. . imported "MDDATA"."ORDERS" 5.593 KB 2 rows
. . imported "MDDATA"."SALGRADE" 5.585 KB 5 rows
. . imported "MDDATA"."BONUS" 0 KB 0 rows
. . imported "MDDATA"."MY_STUFF" 0 KB 0 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/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
ORA-39082: Object type ALTER_FUNCTION:"MDDATA"."GETOSUSER" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"MDDATA"."GETOSUSER1" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"MDDATA"."GETOSUSER2" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"MDDATA"."GETOSUSER4" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"MDDATA"."GETOSUSER6" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 6 error(s) at 10:57
1 select owner ,object_type,status, count(*) from dba_objects
2* where owner='MDDATA' group by owner, object_type, status
SQL> /
OWNER OBJECT_TYPE STATUS COUNT(*)
---------- ------------------ ------- ----------
MDDATA INDEX VALID 2
MDDATA TABLE VALID 7
MDDATA PACKAGE VALID 2
MDDATA TRIGGER VALID 1
MDDATA FUNCTION VALID 1
MDDATA FUNCTION INVALID 5
6 rows selected.
• Exporting only tables and indexes of schema
• C:\Documents and Settings\201910>expdp DIRECTORY=MY_DIR EXCLUDE=INDEX,FUNCTION,PACKAGE,TRIGGER DUMPFILE=TABLE_ONLY1.DMP LOGFILE=TABLE_ONLY1.LOG SCHEMAS=SCOTT
Export: Release 10.1.0.2.0 - Production on Saturday, 29 December, 2007 11:26
Copyright (c) 2003, Oracle. All rights reserved.
Username: SCOTT
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": SCOTT/******** DIRECTORY=MY_DIR EXCLUDE=INDEX,FUNCTION,PACKAGE,TRIGGER DUMPFILE=TABLE_ONLY1.DMP LOGFILE=TABLE_ONLY1.LOG SCHEMA
SCOTT
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "SCOTT"."DEPT" 5.703 KB 6 rows
. . exported "SCOTT"."DEPT_OLD" 5.687 KB 5 rows
. . exported "SCOTT"."EMPK" 7.820 KB 14 rows
. . exported "SCOTT"."ORDERS" 5.593 KB 2 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
. . exported "SCOTT"."MY_STUFF" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
C:\EXPDP_OUT\TABLE_ONLY1.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:26
MDDATA Schema does not have any object .
SQL> col OBJECT_TYPE for a18
SQL> set pages 100
SQL> col owner for a10
SQL> select owner ,object_type,status, count(*) from dba_objects
2 where owner='MDDATA' group by owner, object_type, status;
no rows selected
C:\Documents and Settings\201910>IMPDP DIRECTORY=MY_DIR DUMPFILE=TABLE_ONLY1.DMP REMAP_SCHEMA=SCOTT:MDDATA
Import: Release 10.1.0.2.0 - Production on Saturday, 29 December, 2007 11:47
Copyright (c) 2003, Oracle. All rights reserved.
Username: SCOTT
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01": SCOTT/******** DIRECTORY=MY_DIR DUMPFILE=TABLE_ONLY1.DMP REMAP_SCHEMA=SCOTT:MDDATA
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"MDDATA" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MDDATA"."DEPT" 5.703 KB 6 rows
. . imported "MDDATA"."DEPT_OLD" 5.687 KB 5 rows
. . imported "MDDATA"."EMPK" 7.820 KB 14 rows
. . imported "MDDATA"."ORDERS" 5.593 KB 2 rows
. . imported "MDDATA"."SALGRADE" 5.585 KB 5 rows
. . imported "MDDATA"."BONUS" 0 KB 0 rows
. . imported "MDDATA"."MY_STUFF" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 11:47
After importing in another schema
C:\Documents and Settings\201910>EXIT
SQL>
SQL> col OBJECT_TYPE for a18
SQL> set pages 100
SQL> col owner for a10
SQL> select owner ,object_type,status, count(*) from dba_objects
2 where owner='MDDATA' group by owner, object_type, status;
OWNER OBJECT_TYPE STATUS COUNT(*)
---------- ------------------ ------- ----------
MDDATA INDEX VALID 2
MDDATA TABLE VALID 7
================
• Loading Objects of one Tablespace to another Tablespace.
You can use remap_tablespace option to import objects of one tablespace to another tablespace by giving the command
TABLESPACES ==Identifies a list of tablespaces to export.
expdp DIRECTORY=MY_DIR DUMPFILE=NEW.DMP TABLESPACES=NEW
$impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp
REMAP_TABLESPACE=users:sales
The above example loads tables, stored in users tablespace, in the sales tablespace
NOTE:Parameter TABLE_EXISTS_ACTION=REPLACE to be use if imporintg in same database
impdp DIRECTORY=MY_DIR DUMPFILE=NEW.DMP REMAP_TABLESPACE=NEW:OLD TABLE_EXISTS_ACTION=REPLACE
ORACLE DBA
Abdul Mateen.
1. Steps to be performed in Production Database 1
2 Steps to be performed in Test/Dev Database 3
3. Exercises 5
1. Steps to be performed in Production Database
• Check the size of the schema
select sum(BYTES)/1024/1024 from dba_segments where owner='SLADMIN';
• Check type of objects and status of the objects the schema.
col OBJECT_TYPE for a18
set pages 100
col owner for a10
select owner ,object_type,status, count(*) from dba_objects
where owner='DSADMIN' group by owner, object_type, status;
• Check the total number of objects in the schema.
select count(*) from dba_objects where owner like 'DSADMIN';
• Check for constraints validity in the schema.
Select constraint_type,count(*) from dba_constraints where owner='DSADMIN'
Group by constraint_type;
• Take an export of the Database scheme
exp file=exp_DSADMIN_.dmp log=exp_DSADMIN_dev_bkup.log owner=DSADMIN compress=n buffer=20480000 consistent=y
Should Show: “export terminated successfully without warnings”
To list errors:
cat exp_DSADMIN_dev_bkup.log | grep EXP-
Note:In Oracle 10g you can use both expdp along with exp utility.
To take an export in Oracle 10g
1) Export schema
C:\Documents and Settings\201910>expdp system/manager DIRECTORY=MY_DIR dumpfile=scott2.dmp schemas=scott
Copy the file from production to the export directory of staging/dev/test using scp.
scp exp_DSADMIN_dev_02feb09.dmp oradb:/oraclus1s/exp01/dev
enter oracle@gemsora10's password for oradb
oracle@gemsora10's password:
exp_DSADMIN_EPROD_22d 100% |***********************************************| 604 MB 01:31
ls –ltr
2. Steps to be performed in Test /Dev Database
Take a backup of scheme before proceeding with the below steps.
1. Drop the tables and objects from staging before importing dmp:
Also check for constraints validity before dropping
select 'drop table DSADMIN.'||table_name||' cascade constraints;' from dba_tables where owner='DSADMIN';
Note: This will drop tables and dependent objects.
2. Drop all the remaining objects
select 'drop '||object_type||' DSADMIN.'||object_name||';' from dba_objects where owner=DSADMIN';
10g DROP objects
select 'drop table DSADMIN.'||table_name||' cascade constraints purge;' from dba_tables where owner='SLADMIN';
select 'purge table DSADMIN."'||object_name||'";' FROM dba_RECYCLEBIN;
select 'drop '||object_type||' DSADMIN.'||object_name||';' from dba_objects where owner='SLADMIN';
select count(*) from dba_objects where owner='DSADMIN';
3 After dropping, import the objects in staging from production:
imp fromuser=DSADMIN touser=DSADMIN file=exp_DSADMIN_EPROD_4feb05.dmp log=imp_DSADMIN_EPROD_4feb05.log commit=y resumable=y buffer=40000000
To perform and import using impdp utility in Oracle 10g
C:\Documents and Settings\201910>impdp system/manager dumpfile=scott3.dmp remap_schema=SCOTT:MDDATA LOGFILE=MDDATA.LOG DIRECTORY=MY_DIR
We can ignore the following error:
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "TBL_SCHEDULE_DETAILS" TO "DSADMIN_ICENTER_ROLE""
IMP-00003: ORACLE error 1917 encountered
Count should match with that of the Production database
select distinct status from dba_objects where owner='SLADMIN';
Should show as VALID
If invalid, compile it using run this script.:
alter
select 'alter '||object_type||' DSADMIN.' ||object_name||' compile;' from dba_objects where owner='DSADMIN' and status='INVALID';
Execute below script to compile all invalid objects in the database.
@?/rdbms/admin/utlrp.sql
3. Exercises
• Export schema
C:\Documents and Settings\201910>expdp system/manager DIRECTORY=MY_DIR dumpfile=scott2.dmp schemas=scott
Note: USER SCHEMA IS CREATED BY IMPORT IF IT DOES NOT EXIST
The schema will only be created if the export dump file set contains
the necessary CREATE USER metadata and you are importing with enough
privileges
• Import schema
impdp directory=MY_DIR DUMPFILE=scott2.dmp remap_schema=SCOTT:SCOTT LOGFILE=importscott.log
• Export TABLE
1) expdp DIRECTORY=MY_DIR dumpfile=EMPK_TABLE.DMP logfile=EMP_TABLE.LOG tables=scott.empk
2) C:\Documents and Settings\201910>expdp dumpfile=MY_DIR:dept_table.dmp logfile=MY_DIR:dept_table.log tables=scott.dept
Export: Release 10.1.0.2.0 - Production on Saturday, 29 December, 2007 10:26
Copyright (c) 2003, Oracle. All rights reserved.
Username: SCOTT
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_EXPORT_TABLE_01": SCOTT/******** dumpfile=MY_DIR:dept_table.dmp logfile=MY_DIR:dept_table.log tables=scott.dept
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.703 KB 6 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
C:\EXPDP_OUT\DEPT_TABLE.DMP
Job "SCOTT"." " successfully completed at 10:26
• EXPORT METADATA_ONLY
expdp DIRECTORY=MY_DIR dumpfile=EMPK_TABLE_M1.DMP logfile=EMP_TABLE_METADATA1.LOG tables=scott.empk CONTENT=METADATA_ONLY
• Loading objects of one schema into another schema.
1 select owner ,object_type,status, count(*) from dba_objects
2* where owner='SCOTT' group by owner, object_type, status
SQL> /
OWNER OBJECT_TYPE STATUS COUNT(*)
---------- ------------------ ------- ---------- ------------------------
SCOTT INDEX VALID 2
SCOTT TABLE VALID 7
SCOTT PACKAGE VALID 2
SCOTT TRIGGER VALID 1
SCOTT FUNCTION VALID 1
SCOTT FUNCTION INVALID 5
6 rows selected.
• Dropping existing objects in MDDATA Schema
select 'drop table MDATA.'||table_name||' cascade constraints purge;' from dba_tables where owner=' MDATA ';
select 'drop '||object_type||' SCOTT.'||object_name||';' from dba_objects where owner=' MDATA ';
• C:\Documents and Settings\201910>impdp system/manager dumpfile=scott3.dmp remap_schema=SCOTT:MDDATA LOGFILE=MDDATA.LOG DIRECTORY=MY_DIR
Import: Release 10.1.0.2.0 - Production on Saturday, 29 December, 2007 10:57
Copyright (c) 2003, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=scott3.dmp remap_schema=SCOTT:MDDATA LOGFILE=MDDATA.LOG DIRECTORY=MY_DIR
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"MDDATA" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MDDATA"."DEPT" 5.703 KB 6 rows
. . imported "MDDATA"."DEPT_OLD" 5.687 KB 5 rows
. . imported "MDDATA"."EMPK" 7.820 KB 14 rows
. . imported "MDDATA"."ORDERS" 5.593 KB 2 rows
. . imported "MDDATA"."SALGRADE" 5.585 KB 5 rows
. . imported "MDDATA"."BONUS" 0 KB 0 rows
. . imported "MDDATA"."MY_STUFF" 0 KB 0 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/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
ORA-39082: Object type ALTER_FUNCTION:"MDDATA"."GETOSUSER" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"MDDATA"."GETOSUSER1" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"MDDATA"."GETOSUSER2" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"MDDATA"."GETOSUSER4" created with compilation warnings
ORA-39082: Object type ALTER_FUNCTION:"MDDATA"."GETOSUSER6" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 6 error(s) at 10:57
1 select owner ,object_type,status, count(*) from dba_objects
2* where owner='MDDATA' group by owner, object_type, status
SQL> /
OWNER OBJECT_TYPE STATUS COUNT(*)
---------- ------------------ ------- ----------
MDDATA INDEX VALID 2
MDDATA TABLE VALID 7
MDDATA PACKAGE VALID 2
MDDATA TRIGGER VALID 1
MDDATA FUNCTION VALID 1
MDDATA FUNCTION INVALID 5
6 rows selected.
• Exporting only tables and indexes of schema
• C:\Documents and Settings\201910>expdp DIRECTORY=MY_DIR EXCLUDE=INDEX,FUNCTION,PACKAGE,TRIGGER DUMPFILE=TABLE_ONLY1.DMP LOGFILE=TABLE_ONLY1.LOG SCHEMAS=SCOTT
Export: Release 10.1.0.2.0 - Production on Saturday, 29 December, 2007 11:26
Copyright (c) 2003, Oracle. All rights reserved.
Username: SCOTT
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": SCOTT/******** DIRECTORY=MY_DIR EXCLUDE=INDEX,FUNCTION,PACKAGE,TRIGGER DUMPFILE=TABLE_ONLY1.DMP LOGFILE=TABLE_ONLY1.LOG SCHEMA
SCOTT
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "SCOTT"."DEPT" 5.703 KB 6 rows
. . exported "SCOTT"."DEPT_OLD" 5.687 KB 5 rows
. . exported "SCOTT"."EMPK" 7.820 KB 14 rows
. . exported "SCOTT"."ORDERS" 5.593 KB 2 rows
. . exported "SCOTT"."SALGRADE" 5.585 KB 5 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
. . exported "SCOTT"."MY_STUFF" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
C:\EXPDP_OUT\TABLE_ONLY1.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:26
MDDATA Schema does not have any object .
SQL> col OBJECT_TYPE for a18
SQL> set pages 100
SQL> col owner for a10
SQL> select owner ,object_type,status, count(*) from dba_objects
2 where owner='MDDATA' group by owner, object_type, status;
no rows selected
C:\Documents and Settings\201910>IMPDP DIRECTORY=MY_DIR DUMPFILE=TABLE_ONLY1.DMP REMAP_SCHEMA=SCOTT:MDDATA
Import: Release 10.1.0.2.0 - Production on Saturday, 29 December, 2007 11:47
Copyright (c) 2003, Oracle. All rights reserved.
Username: SCOTT
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SCOTT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_FULL_01": SCOTT/******** DIRECTORY=MY_DIR DUMPFILE=TABLE_ONLY1.DMP REMAP_SCHEMA=SCOTT:MDDATA
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"MDDATA" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/SE_PRE_SCHEMA_PROCOBJACT/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MDDATA"."DEPT" 5.703 KB 6 rows
. . imported "MDDATA"."DEPT_OLD" 5.687 KB 5 rows
. . imported "MDDATA"."EMPK" 7.820 KB 14 rows
. . imported "MDDATA"."ORDERS" 5.593 KB 2 rows
. . imported "MDDATA"."SALGRADE" 5.585 KB 5 rows
. . imported "MDDATA"."BONUS" 0 KB 0 rows
. . imported "MDDATA"."MY_STUFF" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 11:47
After importing in another schema
C:\Documents and Settings\201910>EXIT
SQL>
SQL> col OBJECT_TYPE for a18
SQL> set pages 100
SQL> col owner for a10
SQL> select owner ,object_type,status, count(*) from dba_objects
2 where owner='MDDATA' group by owner, object_type, status;
OWNER OBJECT_TYPE STATUS COUNT(*)
---------- ------------------ ------- ----------
MDDATA INDEX VALID 2
MDDATA TABLE VALID 7
================
• Loading Objects of one Tablespace to another Tablespace.
You can use remap_tablespace option to import objects of one tablespace to another tablespace by giving the command
TABLESPACES ==Identifies a list of tablespaces to export.
expdp DIRECTORY=MY_DIR DUMPFILE=NEW.DMP TABLESPACES=NEW
$impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp
REMAP_TABLESPACE=users:sales
The above example loads tables, stored in users tablespace, in the sales tablespace
NOTE:Parameter TABLE_EXISTS_ACTION=REPLACE to be use if imporintg in same database
impdp DIRECTORY=MY_DIR DUMPFILE=NEW.DMP REMAP_TABLESPACE=NEW:OLD TABLE_EXISTS_ACTION=REPLACE
ORACLE DBA
Abdul Mateen.
Comments