Oracle Database 11g R2 -- Schema Export Script

Problem

You need to generate a schema script for analysis or for import purposes with some level of control on object types included in the script

Solution

You might want to use the DBMS_METADATA PL/SQL package to generate the export schema script. The script below will generate a spool file with the DDL commands to re-create the schema.

Modify schema name parameter, the spool file location, and add/remove any other options. For more information on the package refer to DBMS_METADATA

The script should be run under the schema account or with a privileged user.

SET LONG 2000000000
set longchunksize 2000000000
set pagesize 0
set linesize 2000
set trims on
set flush off
variable c clob
declare
p_handle number;
p_object clob;
p_transform_handle number;
begin
DBMS_LOB.CREATETEMPORARY (:c,true);
p_handle:=DBMS_METADATA.OPEN (
   object_type  =>'SCHEMA_EXPORT');
DBMS_METADATA.SET_FILTER (
   handle => p_handle,
   name             => 'SCHEMA',
   value            => 'schema name');  
DBMS_METADATA.SET_FILTER (
   handle => p_handle,
   name             => 'EXCLUDE_PATH_EXPR',
   value            => 'IN (''STATISTICS'')');
DBMS_METADATA.SET_FILTER (
   handle => p_handle,
   name             => 'EXCLUDE_PATH_EXPR',
   value            => 'IN (''GRANT'')');
p_transform_handle := DBMS_METADATA.ADD_TRANSFORM (
   handle       => p_handle,
   name         => 'DDL');
dbms_metadata.set_transform_param(p_transform_handle,'SQLTERMINATOR', TRUE);
dbms_metadata.set_transform_param(p_transform_handle,'CONSTRAINTS', FALSE, 'TABLE');
dbms_metadata.set_transform_param(p_transform_handle,'REF_CONSTRAINTS', FALSE, 'TABLE');
loop
p_object := DBMS_METADATA.FETCH_CLOB (
   handle       => p_handle);
exit when p_object is null;  
dbms_lob.append(:c, p_object);
end loop;
DBMS_METADATA.CLOSE (p_handle);  
end;
/
spool spool.txt
print c
spool off


Note. The same kind of script you may generate with the Data Pump Import utility. Refer to impdp utility for more info.