If you are going to drop all tables, the best
bet is to use a PL/SQL loop which does:
for r1 in (select table_name, tablespace_name from user_tables where
...)
dbms_utility.exec_ddl_statement('drop table ' || r1.table_name);
dbms_utility.exec_ddl_statement('alter tablespace ' ||
r1.tablespace_name || ' coalesce');
end loop;
This keeps the fet$ search list as short as possible,
which allows the coalesce to be a lot quicker (it's
done one fet$ entry at a time, hunting and sorting
all the others for the matching tablespace - I think
I described the method once in an article about
SMON on my website www.jlcomp.demon.co.uk)
To deal with the delete/truncate/fk problem,
generate a list of tables and foreign key constraints,
and a dependency order from dba_constraints,
then use a pl/sql loop to disable the FK constraints;
use the same list to re-enable them afterwards
In your position I would also look at splitting your
40+GB tablespaces into rather more tablespaces
of a more convenient size - somewhere between
1GB and 4 GB each if viable. In each tablespace,
pick a unit size (can't use LMTs in 8.0, I think)
then set the defaults
initial extent = next extent = minimum extent
to that unit size, and pctincrease = 0. This gets
rid of future fragmentation issues.
Jonathan Lewis
Seminars on getting the best out of Oracle
Last few places available for Sept 10th/11th
See http://www.jlcomp.demon.co.uk/seminar.html
-----Original Message-----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 16 August 2001 19:31
|Sun Sparc Solaris 2.6
|Oracle 8.0.6.2.0
|
|42 G tablespace made up of 21 2G files, called T1 for our example
|44 G tablespace made up of 22 2G files, called T2 for our example
|
|Scenario:
|
|We have two tablespaces where we wish to export/import all the data.
We
|wish to do this because of the excessive fragmentation in the
|tablespaces. The sizes/make up of the tablespaces are above.
|
|First I export all the data. After this, I then truncate all the
tables
|(to avoid redo generation). Some of these truncates fail due to
|parent/child key issues. Fine, no big deal. I then go and drop all
the
|tables (with cascade option).
|
|The timings for these items are currently
|
|Truncate 42 minutes
|Drop 149 minutes
|
|
|QUESTION: Anyone know of a way to speed either of these up? I don't
want
|to drop the schema.
|
|Finally, I want to coalesce the tablespaces before I do the import.
|
|What is the fastest way of doing this? I've tried
|alter tablespace coalesce t1
|alter tablespace coalesce t2
|
|These took a combined time of 150 minutes.
|
|Other ideas which I have yet to explore:
|a) change the pctincrease on the tablespaces from 0 to 1 back to 0.
This
|should force SMON to coalesce.
|b) Modify the init.ora (forgot exact parameter) to dedicate more time
to
|smon coalescing
|c) shutdown/startup the database (which should force smon to
coalesce)
|
|QUESTION: What of these (or other alternatives, I'm open...) would
be the
|fastest? I cannot believe that 2.5 hours to coalesce 86G of
tablespace is
|the best I can do.
|
|Thoughts?
|
|J
|
|--
|Please see the official ORACLE-L FAQ: http://www.orafaq.com
|--
|Author: Jay Weinshenker
| INET: [EMAIL PROTECTED]
|
|Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
|San Diego, California -- Public Internet access / Mailing
Lists
|--------------------------------------------------------------------
|To REMOVE yourself from this mailing list, send an E-Mail message
|to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
|the message BODY, include a line containing: UNSUB ORACLE-L
|(or the name of mailing list you want to be removed from). You may
|also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
