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]