How to export and import the schema from one user to another ?
Here I'm going to explain how to use impdp/expdp utility to import schema and export it to another user.
Getting Started
Create a directory object in database that point to a physical location on the Operating system where dumps,log etc are going to store.
CREATE OR REPLACE DIRECTORY java_latte_dir AS '/home/oracle/java_latte' ;
This directory object is only a pointer to a physical directory, it will not create any directory.
You can view all the directories with the help of DBA_DIRECTORIES view.
Those users who are going to use these utility must have READ,WRITE permission to this directory object.
GRANT READ OR WRITE ON DIRECTORY to SCOTT;
Schema Import/Export
Here we first try to export a schema of user TEST using the following expdp command.
expdp scott/tiger schemas=TEST directory=java_late_dir dumpfile=java_latte.dmp logfile=java_latte.log
Output is look like this :
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": sys/******** AS SYSDBA schemas=test directory=TEST1_DIR dumpfile=test.dmp logfile=test.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 55.12 MB
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/PRE_SCHEMA/PROCACT_SCHEMA
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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."BIG_TABLE" 46.61 MB 1000000 rows
. . exported "TEST"."ABC" 5.062 KB 1 rows
. . exported "TEST"."LOOKUP" 5.492 KB 3 rows
. . exported "TEST"."BIG_TABLE2":"BIG_TABLE_2005" 0 KB 0 rows
. . exported "TEST"."BIG_TABLE2":"BIG_TABLE_2006" 0 KB 0 rows
. . exported "TEST"."BIG_TABLE2":"BIG_TABLE_2007" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/java_latte/java_latte.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 23:56:15
Now we try to import this schema to different user. There is no need of creating another user, it will automatically create the new user.
impdp scott/tiger dumpfile=java_latte.dmp directory=java_late_dir remap_schema=TEST:TEST1
Output look like this:
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": sys/******** AS SYSDBA dumpfile=test.dmp directory=TEST1_DIR remap_schema=TEST:TEST3
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST3"."BIG_TABLE" 46.61 MB 1000000 rows
. . imported "TEST3"."ABC" 5.062 KB 1 rows
. . imported "TEST3"."LOOKUP" 5.492 KB 3 rows
. . imported "TEST3"."BIG_TABLE2":"BIG_TABLE_2005" 0 KB 0 rows
. . imported "TEST3"."BIG_TABLE2":"BIG_TABLE_2006" 0 KB 0 rows
. . imported "TEST3"."BIG_TABLE2":"BIG_TABLE_2007" 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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 00:13:01
Finally, you need to unlock the user and set the password for them.
Connect as sys user and use the following command :
ALTER USER test1 ACCOUNT UNLOCK;
ALTER USER test1 IDENTIFIED BY test1;
if you find this information useful, please comment.
No comments:
Post a Comment