0

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?

1

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.

  • I was considering such approach as well. In my case, the first and foremost obstacle for using it is the lack of permissions. While I already have Data Pump set up, I would have to ask DBAs to create a special user to run your script. The SELECT_CATALOG_ROLE won’t fit because of security considerations. There are objects I cannot have access to. Therefore I would have to grant the permissions explicitly. Thus, I have to stick to Data Pump. – Misha Kriachkov Aug 22 '18 at 14:59
0

No, you can't use DATA PUMP for this - its output is proprietary to Data Pump, and is not available for creating objects in say SQL*Plus or SQL Developer.

Your real solution is to do what @balazs-papp said in his answer, and to get your DBA to grant the privileges necessary.

If that won't work, then the dba will need to generate the scripts for you.

  • I use sqlfile parameter. In such case Data Pump generates plain SQL. – Misha Kriachkov Aug 23 '18 at 16:03
  • @MishaKriachkov that's for IMPORT only, not available for export. – thatjeffsmith Aug 23 '18 at 18:18
  • 1
    @MishaKriachkov - OP already stated the problem with DP. He needs to get the final output as a separate file for each object being created. Once you get the single file output from 'expd -sqlfile ...' then you've got to be able to parse that single file to extract the mutliple ddl's and write each as a separate output file. Care to take on THAT programming project? – EdStevens Aug 24 '18 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.