How to generate entire DDL of an Oracle schema scriptable

Question

Can anyone tell me how I can generate the DDL for all tables  views  indexes  packages  procedures  functions  triggers  types  sequences  synonyms  grants  etc  inside an Oracle schema  Ideally  I would like to copy the rows too but that is less important   I want to do this on a scheduled job of some kind and not manually each time  so that rules out using the wizard in SQL Developer   Ideally  since I will be running this on several schemas that have grants and synonyms to one another  I would like to have a way to do a find replace in the output so the schema names match whatever the names of my new schemas are going to be   Thanks

User · Answer

The output of this query is very clean  original here   clear screen accept uname prompt  Enter User Name     accept outfile prompt    Output filename      spool  amp  amp outfile  gen  SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON  BEGIN    DBMS METADATA set transform param  DBMS METADATA session transform   SQLTERMINATOR   true      DBMS METADATA set transform param  DBMS METADATA session transform   PRETTY   true   END     SELECT dbms metadata get ddl  USER    amp  amp uname   FROM dual  SELECT DBMS METADATA GET GRANTED DDL  SYSTEM GRANT    amp  amp uname   from dual  SELECT DBMS METADATA GET GRANTED DDL  ROLE GRANT    amp  amp uname   from dual  SELECT DBMS METADATA GET GRANTED DDL  OBJECT GRANT    amp  amp uname   from dual   spool off

User · Answer

If you want to individually generate ddl for each object   Queries are   --GENERATE DDL FOR ALL USER OBJECTS  --1  FOR ALL TABLES  SELECT DBMS METADATA GET DDL  TABLE   TABLE NAME  FROM USER TABLES    --2  FOR ALL INDEXES  SELECT DBMS METADATA GET DDL  INDEX   INDEX NAME  FROM USER INDEXES WHERE INDEX TYPE   NORMAL     --3  FOR ALL VIEWS  SELECT DBMS METADATA GET DDL  VIEW   VIEW NAME  FROM USER VIEWS    OR  SELECT TEXT FROM USER VIEWS   --4  FOR ALL MATERILIZED VIEWS  SELECT QUERY FROM USER MVIEWS   --5  FOR ALL FUNCTION  SELECT DBMS METADATA GET DDL  FUNCTION   OBJECT NAME  FROM USER PROCEDURES WHERE OBJECT TYPE    FUNCTION                                                                                                     GET DDL Function doesnt support for some object type like LOB MATERIALIZED VIEW  TABLE PARTITION  SO  Consolidated query for generating DDL will be   SELECT OBJECT TYPE  OBJECT NAME DBMS METADATA GET DDL OBJECT TYPE  OBJECT NAME  OWNER    FROM ALL OBJECTS    WHERE  OWNER    XYZ   AND OBJECT TYPE NOT IN  LOB   MATERIALIZED VIEW    TABLE PARTITION   ORDER BY OBJECT TYPE  OBJECT NAME

User · Answer

To generate the DDL script for an entire SCHEMA i e  a USER  you could use dbms metadata get ddl   Execute the following script in SQL Plus created by Tim Hall   Provide the username when prompted   set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on column ddl format a1000  begin    dbms metadata set transform param  dbms metadata session transform   SQLTERMINATOR   true      dbms metadata set transform param  dbms metadata session transform   PRETTY   true   end     variable v username VARCHAR2 30    exec v username    upper   amp 1     select dbms metadata get ddl  USER   u username  AS ddl from   dba users u where  u username    v username union all select dbms metadata get granted ddl  TABLESPACE QUOTA   tq username  AS ddl from   dba ts quotas tq where  tq username    v username and    rownum   1 union all select dbms metadata get granted ddl  ROLE GRANT   rp grantee  AS ddl from   dba role privs rp where  rp grantee    v username and    rownum   1 union all select dbms metadata get granted ddl  SYSTEM GRANT   sp grantee  AS ddl from   dba sys privs sp where  sp grantee    v username and    rownum   1 union all select dbms metadata get granted ddl  OBJECT GRANT   tp grantee  AS ddl from   dba tab privs tp where  tp grantee    v username and    rownum   1 union all select dbms metadata get granted ddl  DEFAULT ROLE   rp grantee  AS ddl from   dba role privs rp where  rp grantee    v username and    rp default role    YES  and    rownum   1 union all select to clob     Start profile creation script in case they are missing   AS ddl from   dba users u where  u username    v username and    u profile  lt  gt   DEFAULT  and    rownum   1 union all select dbms metadata get ddl  PROFILE   u profile  AS ddl from   dba users u where  u username    v username and    u profile  lt  gt   DEFAULT  union all select to clob  End profile creation script      AS ddl from   dba users u where  u username    v username and    u profile  lt  gt   DEFAULT  and    rownum   1    set linesize 80 pagesize 14 feedback on trimspool on verify on

User · Answer

First export the schema metadata   expdp dumpfile filename logfile logname directory dir name schemas schema name   and then import by using the sqlfile option  it will not import data it will just write the schema DDL to that file   impdp dumpfile filename logfile logname directory dir name sqlfile ddl sql

User · Answer

There is a problem with objects such as PACKAGE BODY   SELECT DBMS METADATA get ddl object Type  object name  owner  FROM ALL OBJECTS WHERE OWNER    WEBSERVICE     ORA-31600 invalid input value PACKAGE BODY parameter OBJECT TYPE in function GET DDL ORA-06512       SYS DBMS METADATA   line 4018 ORA-06512       SYS DBMS METADATA   line 5843 ORA-06512      line 1 31600  00000 -   invalid input value  s for parameter  s in function  s   Cause     A NULL or invalid value was supplied for the parameter   Action    Correct the input value and try the call again     SELECT DBMS METADATA GET DDL REPLACE object type           object name  owner    FROM all OBJECTS    WHERE  OWNER    OWNER1

User · Answer

The get ddl procedure for a PACKAGE will return both spec AND body  so it will be better to change the query on the all objects so the package bodies are not returned on the select   So far I changed the query to this   SELECT DBMS METADATA GET DDL REPLACE object type             object name  owner  FROM all OBJECTS WHERE  OWNER    OWNER1   and object type not like   PARTITION  and object type not like   BODY  order by object type  object name    Although other changes might be needed depending on the object types you are getting

User · Answer

You can spool the schema out to a file via SQL Plus and dbms metadata package   Then replace the schema name with another one via sed    This works for Oracle 10 and higher   sqlplus lt  lt EOF set long 100000 set head off set echo off set pagesize 0 set verify off set feedback off spool schema out  select dbms metadata get ddl object type  object name  owner  from       --Convert DBA OBJECTS OBJECT TYPE to DBMS METADATA object type      select         owner          --Java object names may need to be converted with DBMS JAVA LONGNAME          --That code is not included since many database don t have Java installed          object name          decode object type               DATABASE LINK         DB LINK                JOB                   PROCOBJ                RULE SET              PROCOBJ                RULE                  PROCOBJ                EVALUATION CONTEXT    PROCOBJ                CREDENTIAL            PROCOBJ                CHAIN                 PROCOBJ                PROGRAM               PROCOBJ                PACKAGE               PACKAGE SPEC                PACKAGE BODY          PACKAGE BODY                TYPE                  TYPE SPEC                TYPE BODY             TYPE BODY                MATERIALIZED VIEW     MATERIALIZED VIEW                QUEUE                 AQ QUEUE                JAVA CLASS            JAVA CLASS                JAVA TYPE             JAVA TYPE                JAVA SOURCE           JAVA SOURCE                JAVA RESOURCE         JAVA RESOURCE                XML SCHEMA            XMLSCHEMA               object type           object type     from dba objects      where owner in   OWNER1           --These objects are included with other object types          and object type not in   INDEX PARTITION   INDEX SUBPARTITION               LOB   LOB PARTITION   TABLE PARTITION   TABLE SUBPARTITION           --Ignore system-generated types that support collection processing          and not  object type    TYPE  and object name like  SYS PLSQL             --Exclude nested tables  their DDL is part of their parent table          and  owner  object name  not in  select owner  table name from dba nested tables          --Exclude overflow segments  their DDL is part of their parent table          and  owner  object name  not in  select owner  table name from dba tables where iot type    IOT OVERFLOW     order by owner  object type  object name   spool off quit EOF  cat schema out sed  s OWNER1 MYOWNER g  gt schema out change sql   Put everything in a script and run it via cron  scheduler    Exporting objects can be tricky when advanced features are used   Don t be surprised if you need to add some more exceptions to the above code

[oracle] How to generate entire DDL of an Oracle schema (scriptable)?

Examples related to oracle

Examples related to schema

Examples related to ddl