Generate DDL for any objects in Oracle 9i

To generate the ddl of any objects in oracle 9i, the following script can be used.

1. Login as sysdba

2. Run the below query:

select dbms_metadata.get_ddl('TABLE',u.object_name) from DBA_objects u where u.object_type='TABLE' and u.owner='Schema1'

This will get the ddl for all the tables in a particular schema -schema1.

3. In case, if you get any errors because of the dbms metadata as shown below:

 ERROR:
ORA-04068: existing state of packages has been discarded
ORA-04067: not executed, package body "SYS.DBMS_XMLGEN" does not exist
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "SYS.DBMS_METADATA", line 345
ORA-06512: at "SYS.DBMS_METADATA", line 410
ORA-06512: at "SYS.DBMS_METADATA", line 449
ORA-06512: at "SYS.DBMS_METADATA", line 615
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

4.To fix the error, recreate the dbms_xmlgen package with the following steps.

Recreate the DBMS_XMLGEN package as follows.
From SQL*Plus connected to the database as AS SYSDBA, enter:
SQL> @?/rdbms/admin/dbmsxml.sql
SQL> @?/rdbms/admin/prvtxml.plb
SQL> alter package SYS.DBMS_XMLGEN compile body;
SQL> alter package SYS.DBMS_METADATA compile body;This will fix the dbms_metadata issues and the ddl can be generated.