Re: SQL to Generate CREATEs from System Tables?

In article <1994Nov8.201827.1_at_corp02.d51.lilly.com> jl34778_at_corp02.d51.lilly.com writes:

> 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!

It would be worthwhile checking with Support that your new database will be supported. Last time I checked (a while ago now) it required approval.

In any case I don't think it answers the original question which I think refers to the storage parameters of user objects, not the data dictionary. An easy way to alter these parameters is via the IMP utility (since your question says you are doing an export). Using the INDEXFILE option of IMP will create an ascii file containing the create table & index statements. No data will be imported. Alter the storage parameters, run the create statements via SQL*Plus, and import your data with the IGNORE option.

Two hints: Create your big tables first. Don't precreate indexes before the import unless you have lots of time to spare.

You should question what you will achieve by changing the parameters. The number of extents per object is not generally an issue to database performance despite the common myth that you should use only a single extent for each object. In fact to take advantage of table striping you might want more than one extent. You can use the ALTER TABLE statement to change the NEXT clause and also to allocate extents in particular datafiles. This might be a more effective tuning measure and use of your time.

There are now a number of tuning bulletins, conference papers and books worth referring to.

Chris Received on Wed Nov 09 1994 - 23:46:44 CET