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.<\/p>\n
You can call it as follows:<\/p>\n
\r\n.\/getDdl.py case_insensitive_user_for_which_to_get_tables_and_indexes\r\n<\/pre>\n\r\n#!\/home\/oracle\/local\/bin\/python\r\n#-----------------------------------------------------------------------\r\n------------------------\r\n\"\"\"\r\nAuthor: Steve Howard\r\nDate: October 22, 2010\r\nPurpose: This will print the DDL for all tables and indexes in a\r\ngiven schema. It will strip\r\n the storage clauses, as well as double quotes.\r\n\"\"\"\r\n#-----------------------------------------------------------------------\r\n------------------------\r\n\r\nimport cx_Oracle, string, re, sys\r\n\r\nUSER=sys.argv[1]\r\n\r\n#-----------------------------------------------------------------------\r\n------------------------\r\n\r\ndef stripStorage(ddl):\r\n found = False\r\n for line in str(ddl).split(\"\\n\"):\r\n line = string.replace(line,'\"','')\r\n if re.match('^ STORAGE',line) > 0:\r\n found = True\r\n elif found == True:\r\n if re.search('\\)$',line) > 0:\r\n found = False\r\n else:\r\n print line\r\n print \";\"\r\n\r\n#-----------------------------------------------------------------------\r\n------------------------\r\n\r\nconn = cx_Oracle.connect(mode = cx_Oracle.SYSDBA)\r\ncursor = conn.cursor()\r\n\r\ncursor.execute(\"select table_name from dba_Tables where owner =\r\nupper(:1)\",[USER])\r\n\r\nfor row in cursor:\r\n try:\r\n tableDDL = conn.cursor()\r\n tableDDL.execute(\"select dbms_metadata.get_ddl('TABLE',:1,upper(:2))\r\nfrom dual\",[row[0],USER])\r\n for table in tableDDL:\r\n stripStorage(table[0])\r\n indexes = conn.cursor()\r\n indexes.execute(\"select index_name from dba_indexes where owner =\r\nupper(:1) and table_name = :1\",[row[0],USER])\r\n for index in indexes:\r\n indexDDL = conn.cursor()\r\n indexDDL.execute(\"select\r\ndbms_metadata.get_ddl('INDEX',:1,upper(:2)) from dual\",[index[0],USER])\r\n for indexDDLRow in indexDDL:\r\n stripStorage(indexDDLRow[0])\r\n except:\r\n print str(sys.exc_info()[0]) + row[0]\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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…<\/p>\n