Extract ddl with "dbms metadata.get ddl" - Revision history

Stuart: /* Get Table DDL */

2018-11-03T12:42:04Z

‎Get Table DDL

Stuart: /* Get Table DDL */

2018-11-03T12:41:37Z

‎Get Table DDL

Stuart: /* Get View DDL */

2018-11-03T12:40:07Z

‎Get View DDL

Stuart: /* Get User DDL with related grants */

2018-02-05T16:22:52Z

‎Get User DDL with related grants

Stuart: /* Get User DDL with related grants */

2018-02-05T16:16:42Z

‎Get User DDL with related grants

Stuart: /* Extract db_links with password */

2017-05-17T12:16:46Z

‎Extract db_links with password

Stuart: /* Generate DDL and store it in a table */

2017-05-16T10:02:33Z

‎Generate DDL and store it in a table

Stuart: /* Extract db_links with password */

2017-05-08T14:24:03Z

‎Extract db_links with password

Stuart: /* Extract db_links with password */

2017-05-05T12:53:58Z

‎Extract db_links with password

Stuart: /* Extract db_links with password */

2017-05-05T11:18:46Z

‎Extract db_links with password

@@ Line 71: / Line 71: @@
 === Get Table DDL===
- set long 3000000
+<pre>
- set lines 1000
+set long 3000000
- set pages 0
+set lines 1000
- set trimspool on
+set pages 0
- set feedb off
+set trimspool on
- col eskewell for a1000
+set feedb off
- spool ddl_table.sql
+col eskewell for a1000
- select dbms_metadata.get_ddl ('TABLE', '&&table_name', '&&schema')||chr(10)||'/' eskewell from dual
+spool ddl_table.sql
+/
- spool off
+spool off
 === Get View DDL===

@@ Line 78: / Line 78: @@
   col eskewell for a1000
   spool ddl_table.sql
-  select dbms_metadata.get_ddl ('TABLE', '&&table_name', '&&schema')||chr(10)||'/' eskewell from dual;
+  select dbms_metadata.get_ddl ('TABLE', '&&table_name', '&&schema')||chr(10)||'/' eskewell from dual
   spool off
 === Get View DDL===
   set long 3000000

@@ Line 70: / Line 70: @@
 </pre>
 === Get View DDL===
   set long 3000000
@@ Line 78: / Line 88: @@
   col eskewell for a1000
   spool ddl_view.sql
-  select DBMS_METADATA.GET_DDL ('VIEW', '&&view_name', '&&schema')||chr(10)||'/' eskewell from dual;
+  select dbms_metadata.get_ddl ('VIEW', '&&view_name', '&&schema')||chr(10)||'/' eskewell from dual;
   spool off
 ===Get Trigger DDL===
   set long 3000000

@@ Line 36: / Line 36: @@
   and    type  = ‘TABLE’; — change to each of the valid types
-=== Get User DDL with related grants ===
+=== Get User DDL with related grants (used in clone user as ... or copy user as ...)===
 <pre>
 set long 5000000

@@ Line 37: / Line 37: @@
 === Get User DDL with related grants ===
- set long 5000000
+<pre>
- set pages 0
+set long 5000000
- set lines 2000
+set pages 0
- set feedb off
+set lines 2000
- set verif off
+set feedb off
- col deedeeyell for a1000 word_wrap
+set verif off
 col deedeeyell for a1000 word_wrap
- exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true );
 exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true );
- exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', true );
 exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY', true );
- select dbms_metadata.get_ddl('USER', username) deedeeyell
- from dba_users
+select dbms_metadata.get_ddl('USER', username) deedeeyell
- where  1=1
+from dba_users
- and    username = '&&username'
+where  1=1
- union all
+and    username = '&&username'
- select dbms_metadata.get_granted_ddl('ROLE_GRANT', username)
+union all
- from dba_users
+select dbms_metadata.get_granted_ddl('ROLE_GRANT', username)
- where  1=1
+from dba_users
- and    username = '&username'
+where  1=1
- union all
+and    username = '&username'
- select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', username)
+union all
- from dba_users
+select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', username)
- where  1=1
+from dba_users
- and    username = '&username'
+where  1=1
- union all
+and    username = '&username'
- select dbms_metadata.get_granted_ddl('OBJECT_GRANT', username)
+union all
- from dba_users
+select dbms_metadata.get_granted_ddl('OBJECT_GRANT', username)
- where  1=1
+from dba_users
- and    username = '&username'
+where  1=1
 === Get View DDL===

@@ Line 204: / Line 204: @@
 ===Extract db_links with password===
 Database links extracted with get_ddl (or a select from dba_db_links) do not show the password so we need a script that goes into the base tables
 <pre>
 set headi off lines 1000 pages 0 long 1000

@@ Line 169: / Line 169: @@
   END LOOP;
-  -- Directoryies
+  -- Directories
   for c1 in(select directory_name from all_directories order by 1) LOOP
           select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('DIRECTORY', c1.directory_name), 32767,1) INTO a1 from dual;

@@ Line 207: / Line 207: @@
 set headi off lines 1000 pages 0 long 1000
 select 'From user '||decode (u.name, 'PUBLIC', 'SYS', u.name) || ':' ||chr(10)||
-        'create ' || decode (u.name, 'PUBLIC', 'PUBLIC ') || 'database link ' || '"' || l.name || '"' ||
+        'create ' || decode (u.name, 'PUBLIC', 'public ') || 'database link ' || '"' || l.name || '"' ||
         ' connect to ' || '"' || l.userid || '"' ||
         ' identified by values ' || '"' || l.passwordx || '"' ||

@@ Line 214: / Line 214: @@
 ,      sys.user$ u
 where  l.owner# = u.user#
+/
 </pre>
 ===Generate script for creating schema objects===
 <pre>