Any tools to export the whole Oracle DB as SQL scripts

Question

Here is my problem, I wants to create a baseline on our development Dateabase (Oracle 10g), and check into our svn for version control, and after this we will use liquibase to help us manage the incremental database changes.

My problem is how should I create baseline of Oracle 10g? the database now consists of 500+ tables, with large amount of configuration data, and I wants my db baseline to base on a set SQL scripts to check into subversion, rather then check in Oracle dump..

I have try use liquibase generateChangeLog, but it have some performance problem.. can anyone can recommends me any tools that will help me 1. Scan any Oracle Schema 2. Generate a set of SQL Scripts (With Table structures, and Data)..

Thanks in advance

James!

Pullen

Answer 1

Something like

SELECT DBMS_METADATA.GET_DDL('TABLE',table_name) FROM USER_TABLES;

is a good start. You can tweak it with PL/SQL and UTL_FILE to get it to write each table to a different file. You will probably need to do sequences too (though versioning them is fairly pointless), and maybe triggers/procedures/functions/packages etc.

Don't forget grants.

Superlative

Can you use PL/SQL and this query to export the DDL to a file on the client side? – Rode

The DB server can't write client files (unless you go to the hassle of sharing and mounting the drive, effectively making the client a file server). – Superlative

Answer 2

Have you tried Oracle's free SQLDeveloper tool? It gives you the possibility of exporting DDL and data.

Sunfast

Answer 3

EXPDP with CONTENT=METADATA_ONLY option, then IMPDP with SQLFILE=your_script.sql ?

Nicolas.

Nissie

Answer 4

More general solution would be to dump DDL sql for selected list of tables, but additionally also other types of objects. This could be done by using all_objects and all_users views.

Example that worked for me:

select dbms_metadata.GET_DDL(u.object_type,u.object_name, u.owner)
from  all_objects u
where 1=1
-- filter only selected object types
and u.object_type in ('TABLE', 'INDEX', 'FUNCTION', 'PROCEDURE', 'VIEW', 
                      'TYPE', 'TRIGGER', 'SEQUENCE')
-- don't want system objects, generated, temp, invalid etc.
and u.object_name not like 'SYS_%'
and temporary!='Y'
and generated!='Y'
and status!='INVALID'
and u.object_name not like 'TMP_%'
and u.object_name not like '%$%'

-- if you want to filter only changed from some date/timestamp:
-- and u.last_ddl_time > '2014-04-02'

-- filter by owner
and owner in (
  select username from dba_USERS where DEFAULT_TABLESPACE not like 'SYS%' 
  and username not in ('ORACLE_OCM')
  and username not like '%$%'
  )
;

I wrote a python script that refreshes db schema in incremental mode based on similar sql:

• runs sql with last_ddl_time>=max(last_ddl_time from last refresh)
• at the end stores last_ddl_time somewhere in filesystem for next refresh

References:

1. oracle dbms_metadata.GET_DDL function
2. oracle all_objects view

Plutonian