Using datapump to directly create a sqlfile with the create script for a schema



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