Oracle Data Pump for extracting DDL as a set of files – one per object

datapumporacle

I use Oracle Data Pump to extract the DDL for all schema objects – impdp with sqlfile parameter. It generates one huge SQL file. I want to split it up into several files – one file per one schema object. E.g., a package P should be stored in P.pks and P.pkb – the specification and the body, a table T – in T.tbl, etc.

These files are going to be put into some repository for revision control. That is why one huge SQL file containing all the DDL is inconvenient compared to the set of files for each object. I have not find any tool for splitting the Data Pump SQL file. Therefore I will have to write my own program for splitting the Data Pump SQL file and organizing folder structure for the repository.

Is there any Data Pump options/parameters to generate such set of files? Is there any other tool to do so?

Best Answer

Why bother with Data Pump for a task like that? It is designed for something else, and there are simpler solutions for this without Data Pump, like using DBMS_METADATA directly.

$ mkdir /home/oracle/ddl

Then create the directory object:

SQL> create or replace directory ddl_dir as '/home/oracle/ddl';

Then just run something like below. In this example I generated the DDL for only first 5 (order based on object_id) packages, package bodies and tables to keep the output short.

declare
  l_ddl clob;
begin
  for o in (select * from (select owner, object_name, object_type, row_number() over (partition by object_type order by object_id) as rn from dba_objects where owner = 'SYS' and object_type in ('PACKAGE', 'PACKAGE BODY', 'TABLE')) where rn <= 5)
  loop
    l_ddl := dbms_metadata.get_ddl(replace(o.object_type, 'PACKAGE BODY', 'PACKAGE_BODY'), o.object_name, o.owner);
    dbms_xslprocessor.clob2file(l_ddl, 'DDL_DIR', o.object_name || '.' || case when o.object_type = 'PACKAGE' then 'pks' when o.object_type = 'PACKAGE BODY' then 'pkb' when o.object_type = 'TABLE' then 'tbl' end);
  end loop;
end;
/

And the files:

$ ls -l /home/oracle/ddl/
total 268
-rw-r--r--. 1 oracle oinstall    587 Aug 22 15:04 CDBVIEW_INTERNAL.pkb
-rw-r--r--. 1 oracle oinstall    732 Aug 22 15:04 CLU$.tbl
-rw-r--r--. 1 oracle oinstall  11901 Aug 22 15:04 DBMS_REGISTRY.pkb
-rw-r--r--. 1 oracle oinstall  19790 Aug 22 15:04 DBMS_REGISTRY.pks
-rw-r--r--. 1 oracle oinstall   3822 Aug 22 15:04 DBMS_REGISTRY_SERVER.pks
-rw-r--r--. 1 oracle oinstall    818 Aug 22 15:04 DBMS_REGISTRY_SIMPLE.pkb
-rw-r--r--. 1 oracle oinstall   1102 Aug 22 15:04 DBMS_REGISTRY_SIMPLE.pks
-rw-r--r--. 1 oracle oinstall   8659 Aug 22 15:04 DBMS_STANDARD.pks
-rw-r--r--. 1 oracle oinstall    202 Aug 22 15:04 FET$.tbl
-rw-r--r--. 1 oracle oinstall    740 Aug 22 15:04 SEG$.tbl
-rw-r--r--. 1 oracle oinstall  18722 Aug 22 15:04 STANDARD.pkb
-rw-r--r--. 1 oracle oinstall 164861 Aug 22 15:04 STANDARD.pks
-rw-r--r--. 1 oracle oinstall   1239 Aug 22 15:04 TAB$.tbl
-rw-r--r--. 1 oracle oinstall    344 Aug 22 15:04 UET$.tbl
-rw-r--r--. 1 oracle oinstall    870 Aug 22 15:04 XML_SCHEMA_NAME_PRESENT.pkb

The rest is up to you: customize the above for specific types and file extensions.

Related Question