Problem solve Get help with specific problems with your technologies, process and projects.

Retrieving lost scripts

I'm on Oracle9i and I have a schema called "HEAT" that owns all the tables, sequences, indexes, etc. I lost the scripts that I used to have to create them all. Is there any DBMS package that I can use to get the scripts back? How do I recreate that schema exactly the same way in another database? Please explain if any scripts need to be executed.
In Oracle 9i, you can easily reverse engineer the DDL statement necessary to create your schema. Sign on to the database as a DBA and issue a query similar to the following:

SELECT DBMS_METADATA.GET_DDL(object_type,object_name,owner)
FROM dba_objects
WHERE owner='HEAT';

This query uses Oracle9i's new DBMS_METADATA package, specifically the GET_DDL routine to reverse engineer the DDL statements to create objects. I told the routine to use the object type, name, and owner from the DBA_OBJECTS view. You can modify this query to handle just tables, or whatever other criteria you need.

Dig Deeper on Oracle database backup and recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.