Re: SQL to Generate CREATEs from System Tables?

In article <1994Nov8.105425_at_ebi.ac.uk>, ditommaso_at_ebi.ac.uk (MATTEO DITOMMASO) writes:
> I am looking for a SQL script to generate CREATEs for all tables & indexes
> in a database. I would like to use these scripts to recreate the database
> with new INITIAL and NEXT extents after a full export and before a full import.
> Anyone out there have such a script that they would share?
>
> Thanks for any assistance.
>
> Matteo diTommaso
> European Bioinformatics Institute
> ditommaso_at_ebi.ac.uk

The script that ORACLE uses to build the data dictionary tables is SQL.BSQ.

Under VMS, the file is located in ORA_RDBMS. Under UNIX (Solaris), it is in $ORACLE_HOME/dbs

The process for reorganizing the system tables would go something like this:

1. Do full database export using EXP.
2. Generate a listing of your tablespace files.
3. Shut down the database and do a cold backup (just in case ).
4. Delete your current database files.
5. Save a copy of sql.bsq.
6. Modify sql.bsq to have the new initial and next values that you want.
7. Create your database. I typically use the install procedure from Oracle, ORACLEINS on VMS, ORAINST on Solaris.
8. Restore the original copy of sql.bsq, so any subsequent database creations are not affected.
9. Create your tablespaces, using the listing that you generated in step 2.
10. Do your full database import using IMP.

Good luck!

-- 
Bob Swisshelm                | swisshelm_at_Lilly.com     | 317 276 5472
Eli Lilly and Company        | Lilly Corporate Center  | Indianapolis, IN 46285