Re: Failed to drop a tablespace in Oracle 6.0.36

bs (shatzman_at_netcom.com) wrote:
: Rick Loffredo (loffredo_at_nprdc.navy.mil) wrote:
 

: : Here is what I have done:
: : 1) connect to Sql*Plus as user SYSTEM
: : 2) execute the following commands:
: : alter tablespace TARGET offline normal;
: : drop tablespace TARGET including contents;
: : 3) re-boot because my machine was not responding
: : 4) examine the status on tablespace TARGET and find out the tablespace has
: : been brought off-line, but it is not dropped.
 

: : questions:
 

: : 1) Do I have to connect as INTERNAL instead?
: : 2) Do I need to modify my DC_FREE_EXTENTS/ROW_CHCEE_ENQUEUES in init.ora?
 

: I've never had much luck with DT Incl Contents. Either constraints got in
: the way (Oracle V6 DOES enforce them at the DDL level) or had rollback
: probs (it can generate tons o rollback) or it just took a long time. That
: could be why it looked like your machine was not responding. In fact, if
: you rebooted during the process, you may have just ended up rolling back,
: which was why you saw what you did. If you noticed it taking a long time
: to start ORACLE after rebooting, that's probably what happened.
 

: I usually suggest manually emptying out the tablespace and then just
: dropping it.
 

: barry

I had a similiar problem with 6.36.4.1. and with Oracle UKs help I was able to do this after having increased certain params (under guidance) in init.ora like 'ENQUEUEs'. You may also get some more info' from your alert.log file. It is a known 'feature'.

But dropping a table_space including contents can take an awful lot of time if you have a lot of data even if it is working correctly.

I now prefer to drop each table in turn - so I can see what's happening and get (visul) reassurance that the 'drops' are working.

Lez

-- 
Lez Oxley, Deputy ACS Manager and Database Administrator
Administration, University of Sussex, Falmer, Brighton, BN1 9RH, UK
Tel: +44 273 606755 x3808 Fax: +44 273 678335 
EMAIL: L.Oxley_at_sussex.ac.uk