Re: How to shrink a database

dherdman_at_osil.demon.co.uk (Dave G Herdman) writes:
: We are tseting an Oracle 7 application from another company
: with a view to buying it. The application was delivered as
: a dump of all the database files. We have now cleared out
: most of the rows we dont need and we want to shrink the
: database down from about 4Gig to say 300 meg. We cannot
: use the creation scripts as they are incomplete and have
: not been updated for several years.
:
: Plan 1 was to build a database with everything scaled down
: to about 25% or the original. Export everything from the old
: database and then import it into the new database.
:
: This failed as some of the tables and indexes have storage
: parameters with large initial allocations (300Meg)
:
: Plan 2 is to create the new database again. Extract the object
: defs from the import file (index option). Alter all the
: storage allocations. Create all the objects . Then import the
: rows. This will take some time (1500 objects). Also it will
: need some more thought as the object owners will need to be
: defined before we create the relavent objects
:
: Is there a quicker cleaner way?. If not some good advice on
: plan 2 would be most welcome.
:
: thanks in advance
:

Plan one is your best bet.

However, before doing the export, just make sure that you alter the tables with the large storage defs.

ie
alter table <table_name> storage (initial xx next xx pctincrease xx maxtextents xx);

These new storage defs will be exported with the table.

--
regards,
Carl
+-----------------------------------------------------------------------------+
Carl Gohringer, Oracle European Development Centre, World Wide Education 
Oracle Park, Bittams Lane, Chertsey, Surrey, England, KT16 9RG
Internet   : cgohring_at_uk.oracle.com    
+-----------------------------------------------------------------------------+