How to reverse engineer a database - to produce a SQLDDL using SQL*Plus only ?

I'm trying to recreate the SQLDDL used to create a database and it's objects. The database has been running for some time and there appear to be some sizing and I/O problems. I want to try to do some initail examination of this using a SQL script to create a SQLDDL of what is there now but a bit stuck trying to sort it out.

I have system access to the Oracle database which has circa 20 tablespaces, a lot of objects and several users and what I want to obtain is the following:

The complete list of users and all their objects. The full table structures for all the tables with the current sizing information currently set for each table ? The tablespaces currently occupied by the tables ? The constraint information with sizing information and tablespace information ?
The index information along with current sizing information ? And some sort of analysis of where some inital problems may be ?

Is there a simple way to obtain all this information by running a single or a few scripts from 'SYSTEM' under oracle ?

I have some idea of obtaining some of the information but not how to tie it all together.

'SELECT object_name, object_type, owner
FROM all_objects;'

will present a list of all the objects, their types and their owners. This could be further refined to remove the SYS and SYSTEM owners objects by appending a NOT LIKE 'SYS%' clause.

SELECT table_name, column_name

    FROM user_tab_columns;

will present a list of all the table names, column names, data types, column lengths and orther information.

I don't know how to display the storage parameters or how to extract the complete constraint information.

Some guidance on initial analysis would be useful.

This is probably real easy but I'm a little stuck.

Can any Oracle GURU out there help ? Received on Wed Apr 08 1998 - 00:00:00 CDT