Oracle

-d, --database

The name of the database to script. This value is required.

-s, --server

Full path to the database in the cloud (database-identifier.database.windows.net). This value is required.

-p, --port

Default value is 1521.

-u, --username

--password

--mode

Connection mode can be one of the following:

SID
Service Name

Default value is SID.

--as

Connect as:

Standard
SysDBA
SysOPER

Default value is Standard.

CREATE OR REPLACE FUNCTION get_whole_database_ddl
   RETURN CLOB
IS
   l_script CLOB := EMPTY_CLOB();
   l_ddl    CLOB;
BEGIN

   FOR rec IN (
      SELECT owner, object_name, object_type
        FROM dba_objects
       WHERE object_type IN ('TABLE','VIEW','PROCEDURE','FUNCTION','TRIGGER','PACKAGE','PACKAGE BODY','TYPE')
         AND owner NOT IN ('SYSTEM','XDB') AND UPPER(owner) NOT LIKE '%SYS' AND UPPER(owner) NOT LIKE 'APEX\_%' ESCAPE '\'
       ORDER BY object_type, owner, object_name
   )
   LOOP
      BEGIN
         l_ddl := DBMS_METADATA.GET_DDL(object_type => rec.object_type, name => rec.object_name, schema => rec.owner);
         l_script := l_script || TRIM(l_ddl) || CHR(10) || '/' || CHR(10);
      EXCEPTION
         WHEN OTHERS THEN
            l_script := l_script || '-- ERROR: ' || rec.object_type || ' ' || rec.owner || '.' || rec.object_name || ': ' || SQLERRM || CHR(10) || CHR(10);
      END;
   END LOOP;

   RETURN l_script;
END;

Create the procedure, then execute it in a decent client:

SELECT get_whole_database_ddl;

Ensure that your client doesn't truncate the output.

For example, DBeaver has a setting "Maximum text editor content length". Increase it.

SQL Developer actually needs to spool that to a file:

SET LONG 1000000000            -- allow very large output
SET LONGCHUNKSIZE 32767        -- size of each chunk
SET PAGESIZE 0                 -- suppress page headings
SET LINESIZE 32767             -- avoid wrapping

SPOOL C:\path\to\whole_db_ddl.sql
SELECT get_whole_database_ddl;
SPOOL OFF

PreviousSynapse Dedicated Pool NextPostgreSQL

Last updated 11 months ago