Dumping Oracle DDL part two

Below is a python script that strip the double quotes from the output of dbms_metadata.get_ddl, as well as the storage clauses. The initial clause is often far too high, so this allows you to extract the DDL and run it wherever you like.

You can call it as follows:

./getDdl.py case_insensitive_user_for_which_to_get_tables_and_indexes

#!/home/oracle/local/bin/python
#-----------------------------------------------------------------------
------------------------
"""
Author:         Steve Howard
Date:           October 22, 2010
Purpose:        This will print the DDL for all tables and indexes in a
given schema.  It will strip
                 the storage clauses, as well as double quotes.
"""
#-----------------------------------------------------------------------
------------------------

import cx_Oracle, string, re, sys

USER=sys.argv[1]

#-----------------------------------------------------------------------
------------------------

def stripStorage(ddl):
 found = False
 for line in str(ddl).split("\n"):
   line = string.replace(line,'"','')
   if re.match('^  STORAGE',line) > 0:
     found = True
   elif found == True:
     if re.search('\)$',line) > 0:
       found = False
   else:
     print line
 print ";"

#-----------------------------------------------------------------------
------------------------

conn = cx_Oracle.connect(mode = cx_Oracle.SYSDBA)
cursor = conn.cursor()

cursor.execute("select table_name from dba_Tables where owner =
upper(:1)",[USER])

for row in cursor:
 try:
   tableDDL = conn.cursor()
   tableDDL.execute("select dbms_metadata.get_ddl('TABLE',:1,upper(:2))
from dual",[row[0],USER])
   for table in tableDDL:
     stripStorage(table[0])
     indexes = conn.cursor()
     indexes.execute("select index_name from dba_indexes where owner =
upper(:1) and table_name = :1",[row[0],USER])
     for index in indexes:
       indexDDL = conn.cursor()
       indexDDL.execute("select
dbms_metadata.get_ddl('INDEX',:1,upper(:2)) from dual",[index[0],USER])
       for indexDDLRow in indexDDL:
         stripStorage(indexDDLRow[0])
 except:
   print str(sys.exc_info()[0]) + row[0]