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 OFFLast updated