Sometimes it's useful to generate out the SQL creation scripts for a schema directly using datapump - this can easily be done in one step using a little trick of creating a database link back to the same database - see the demo below:
First lets set up a user to run the export and give it some rights before creating a public database link
SYS@>create user demo identified by demo;
User created.
SYS@>grant dba to demo;
Grant succeeded.
SYS@>create public database link demo connect to demo identified by demo using 'TEST';
Database link created.
SYS@>
Now we have the building blocks in place lets do an export.
[oracle@]::/oracle/home/oracle# impdp demo/demo network_link=demo schemas=demo sqlfile=createdemo.sql
Import: Release 11.2.0.3.0 - Production on Sat Jan 4 21:51:05 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Starting "DEMO"."SYS_SQL_FILE_SCHEMA_01": demo/******** network_link=demo schemas=demo sqlfile=createdemo.sql
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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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
Job "DEMO"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 21:51:25
Or we can do it as full with an include - the results are the same (note use of \ characters to escape all the special characters (best off using a parfile here)
[oracle@]:DB:/oracle/home/oracle# impdp demo/demo network_link=demo full=y include=SCHEMA\:\"\=\'DEMO\'\" sqlfile=createfulldemo.sql
Import: Release 11.2.0.3.0 - Production on Sat Jan 4 21:54:12 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Starting "DEMO"."SYS_SQL_FILE_FULL_01": demo/******** network_link=demo full=y include=SCHEMA:"='DEMO'" sqlfile=createfulldemo.sql
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Job "DEMO"."SYS_SQL_FILE_FULL_01" successfully completed at 21:54:37
The trick is the combination of impdp and the network link with sqlfile - you are not actually importing anything in to the database it's purely creating the sqlfiles with all the commands to create the schema.
Comments
Post a Comment