Re: Ways to improve speediness of truncate, drop, coalesce

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 <ORACLE-L_at_fatcity.com> 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: jweinshe_at_concentric.net
|
|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: ListGuru_at_fatcity.com (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: jonathan_at_jlcomp.demon.co.uk

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: ListGuru_at_fatcity.com (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).