Copy oracle schema from one database to another

Using expdp and impdp tools to backup a user/schema data and restore to another database.

Create a backup directory which will be used by expdp tool

CREATE OR REPLACE DIRECTORY "BACKUP_DIR" AS '/u01/expdp'

Use expdp to take full schema backup

expdp \"/ as sysdba\" schemas=pin2 directory=backup_dir dumpfile=pin2.dmp logfile=pin2.log

Dump file set for SYS.SYS_EXPORT_SCHEMA_02 is:
  /u01/expdp/pin2.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at 07:08:14

[email protected][dev02]/u01/expdp>ls 
pin2.dmp         pin2.log

copy the dump file to another server where we need to restore. We need to create a backup_dir on that server and copy dump file to that directory.

CREATE OR REPLACE DIRECTORY "BACKUP_DIR" AS '/u01/expdp'
scp [email protected]:/u01/expdp/pin2.dmp /u01/expdp/.

Start importing the schema to pin3, we need to drop the existing schema if it exists.

SQL> drop user pin3 cascade;
User dropped.
[email protected][dev01 ]/u01/expdp>impdp \"/ as sysdba\" schemas=pin2 directory=backup_dir dumpfile=pin2.dmp logfile=import_pin2.dmp REMAP_SCHEMA=pin2:pin3
.
.
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:"PIN3"."CREATE_A_VIEW" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"PIN3"."CREATE_VIEWS" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_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/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
ORA-39082: Object type PACKAGE_BODY:"PIN3"."WIRELESS_PG" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"PIN3"."DEF_TAX" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 4 error(s) at 07:13:59

We need to fix the above errors, the reason could be because of missing object privileges for PIN3 user. By default expdp doesn’t copy user privileges.

Backup object privileges for pin2 from database 1 and restore to database 2 pin3 schema.

SQL> SELECT 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO PIN3;' FROM dba_tab_privs where GRANTEE='PIN2';

Recompile all the invalid objects in PIN3 schema on database 2, below should generate us compile statements.

SELECT 'ALTER '|| OBJECT_TYPE ||' '||OWNER||'.'||OBJECT_NAME||' compile;' FROM dba_objects where owner='PIN3' AND STATUS='INVALID' AND OBJECT_TYPE!='PACKAGE BODY';

Generating package body compile queries –

SELECT 'ALTER PACKAGE '||OWNER||'.'||OBJECT_NAME||' compile BODY;' FROM dba_objects where owner='PIN3' AND STATUS='INVALID' AND OBJECT_TYPE='PACKAGE BODY';

 

Leave a Reply

Your email address will not be published. Required fields are marked *