Get all tables creation scripts from Oracle database

Question

I have an Oracle db with a lot of tables and a lot of properties like: primary key,Unique key, Foreign key,Indexes, Privileges and partitions.

I need to create the same tables with the same properties in new Oracle db. Is there a way to generate a single creation script for all tables in my database?

Thanks

Answer 1

Yes. You can execute below query and the content in 'Definition' column is a CLOB which contains the DDL for the corresponding object

SELECT OBJECT_TYPE,
       OBJECT_NAME,
       DBMS_METADATA.GET_DDL(OBJECT_TYPE, OBJECT_NAME) DEFINITION
  FROM DBA_OBJECTS
 WHERE OWNER = 'OWNER_NAME'
   AND OBJECT_TYPE IN ('FUNCTION',
                       'PACKAGE',
                       'PROCEDURE',
                       'SEQUENCE',
                       'TABLE',
                       'TRIGGER',
                       'VIEW')
 ORDER BY OBJECT_TYPE, OBJECT_NAME

Below is a quick VB Script which helps extract the CLOBs

folder = "Folder_name"
connStr="<Your connection string here>"
if fetchFromDB=true then
    set c=CreateObject("ADODB.Connection")
    c.ConnectionString=connStr
    c.Open

    set r=CreateObject("ADODB.recordset")

    sql="SELECT OBJECT_TYPE, OBJECT_NAME, DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME) DEFINITION FROM DBA_OBJECTS WHERE OWNER='<OWNER_NAME_HERE>' AND OBJECT_TYPE IN ('FUNCTION', 'PACKAGE', 'PROCEDURE', 'SEQUENCE', 'TABLE', 'TRIGGER', 'VIEW') ORDER BY OBJECT_TYPE, OBJECT_NAME"
    r.open sql,c,3,1
    dim fs,f
    set fs=CreateObject("Scripting.FileSystemObject")
    do until r.EOF
        set f=fs.OpenTextFile(folder & "\\" & r("OBJECT_TYPE") & "_" & r("OBJECT_NAME") & ".sql",8,true)
        f.WriteLine r("DEFINITION")
        r.MoveNext
        f.close
    loop
    r.close
end if

Answer 2

Try with Below code..

DECLARE
   P_File           UTL_FILE.File_Type;
   In_Schema_Name   VARCHAR2 (30) := UPPER ('BPI_R2_APP_TEST');
   In_Table_Name    VARCHAR2 (30) := UPPER (NULL);
   V_KEY_NAME       VARCHAR2(50);
   Var1             CLOB;
   Var3             CLOB;

   CURSOR C3                    --CURSOR FOR SELECTING TABLE NAMES FROM SCHEMA
   IS
      SELECT Table_Name
        FROM All_Tables
       WHERE     Owner = In_Schema_Name
             AND Table_Name =
                    CASE
                       WHEN In_Table_Name IS NULL THEN Table_Name
                       ELSE In_Table_Name
                    END;

   C4               C3%ROWTYPE;
BEGIN
   OPEN C3;

   LOOP
      FETCH C3 INTO C4;

      EXIT WHEN C3%NOTFOUND;
      -- THE FOLLOWING LINES WILL EMIT SEGMENT_ATTRIBUTES,SCHEMA,STORAGE NAME AS PER OUR SELECTION AND GIVES ALIGNMENT

      DBMS_METADATA.Set_Transform_Param (DBMS_METADATA.Session_Transform,
                                         'SQLTERMINATOR',
                                         FALSE);
      DBMS_METADATA.Set_Transform_Param (DBMS_METADATA.Session_Transform,
                                         'PRETTY',
                                         TRUE);
      DBMS_METADATA.Set_Transform_Param (DBMS_METADATA.Session_Transform,
                                         'SEGMENT_ATTRIBUTES',
                                         FALSE);
      DBMS_METADATA.Set_Transform_Param (DBMS_METADATA.Session_Transform,
                                         'STORAGE',
                                         FALSE);
      DBMS_METADATA.Set_Transform_Param (DBMS_METADATA.Session_Transform,
                                         'CONSTRAINTS',
                                         FALSE);
      DBMS_METADATA.Set_Transform_Param (DBMS_METADATA.Session_Transform,
                                         'REF_CONSTRAINTS',
                                         FALSE);
      DBMS_METADATA.Set_Transform_Param (DBMS_METADATA.Session_Transform,
                                         'EMIT_SCHEMA',
                                         FALSE);

      --VAR1 TO HANDLE TABLE SCRIPT

      Var1 :=
            'Declare
       V_Exists   NUMBER(38);
       SQL_STRING CLOB;
       Begin
       Select count(1) Into V_Exists
       From User_Tables
       Where Table_Name ='''
         || C4.Table_Name
         || ''';
       If V_Exists = 0 Then
       SQL_STRING := q''{'
         || DBMS_METADATA.Get_Ddl ('TABLE', C4.Table_Name, In_Schema_Name)
         || '}'';
       Execute Immediate SQL_STRING ;
       End If;
       End; 
       /';

      P_File := UTL_FILE.Fopen ('G2N_TAB_MAIN', C4.Table_Name || '.sql', 'W');
      UTL_FILE.Put_Line (P_File, '--SCRIPT FOR ' || C4.Table_Name || 'table');
      UTL_FILE.Put_Line (P_File, Var1);
      UTL_FILE.Fclose_All ();

      -- UNIQUE KEYS HANDLING STARTS HERE
      DECLARE
         CURSOR C1
         IS
              SELECT Constraint_Name, Constraint_Type
                FROM All_Constraints
               WHERE     Owner = In_Schema_Name
                     AND Table_Name = C4.Table_Name
                     AND Constraint_Type IN ('U', 'P')
            ORDER BY Constraint_Type;

         C2   C1%ROWTYPE;
      BEGIN
         OPEN C1;

         LOOP
            FETCH C1 INTO C2;

            EXIT WHEN C1%NOTFOUND;

            --VAR3 TO HANDLE UNIQUE KEYS
            Var3 :=
                  'Declare
          V_Exists NUMBER(38);
          Begin
          select count(1) into V_Exists
          From User_CONSTRAINTS
          Where  Constraint_Type = '''
               || C2.Constraint_Type
               || ''' And Table_Name = '''
               || C4.Table_Name
               || ''' and Constraint_Name = '''
               || C2.Constraint_Name
               || ''';
          if V_Exists=0 Then    
          execute immediate  '''
               || DBMS_METADATA.Get_Ddl ('CONSTRAINT',
                                         C2.Constraint_Name,
                                         In_Schema_Name)
               || ''';
          end if;
          end;
          /';
             SELECT DECODE(C2.Constraint_Type,'P','PRIMARY','U','UNIQUE') INTO V_KEY_NAME FROM DUAL;
            P_File :=
               UTL_FILE.Fopen ('G2N_TAB_MAIN', C4.Table_Name || '.sql', 'A');
            UTL_FILE.Put_Line (
               P_File,
               '--SCRIPT FOR '||V_KEY_NAME ||' KEYS OF TABLE ' || C4.Table_Name);
            UTL_FILE.Put_Line (P_File, (Var3));
            UTL_FILE.Fclose_All ();
         END LOOP;

         CLOSE C1;
      END;
   END LOOP;

   CLOSE C3;
END;
/