Re: HELP with dropping tablespaces needed!

Gary Assa wrote:
>
> >Recently, I've being trying to drop a huge tablespace(about 100M), but
> >very fragmented(about 6 datafiles). The result was nill, after more
> >the 2 hours, then I decided is time to kill the process.
> >Does anybody know how long should run a 'drop tablespace ...' ? I
> >forgot to mention that, I didn't notice any error or warning running
> >the following statements:
> >
> 100M big? That's small! Although this should have worked, the best thing
> to do is to bring up the database in dba/exclusive mode and do what
> you have to do. It is possible, but highly unlikely with a small database
> that your redo logs and rollback segments were being written before the
> tablespace could be dropped. Also, someone could have had a table lock,
> so go into dba/exclusive mode and it should work.
> --
You're right ... that's not big.

However, based on processor speed, rollback segs, log space, etc. I have noticed that it can take a long time. This is especially true if there are a lot of constraints on the tables and even more true if these constraints include cascade delete references.

In addition to the previous response, I'd also consider dropping all constraints (use a SQL*Plus command like

SELECT 'ALTER TABLE '||owner||'.'||table||

       ' DROP CONSTRAINT '||constraint_name||';'   FROM DBA_CONSTRAINTS A
 WHERE EXISTS ( SELECT 1

                  FROM DBA_TABLES B
                 WHERE A.OWNER=B.OWNER
                   AND A.TABLE_NAME=B.TABLE_NAME
                   AND B.TABLESPACE_NAME='????' );

to generate an appropriate script. Check twice before running!), truncating and dropping all tables (again use generate a script).

(Note: I keyed in the command from memory - it may be flawed!!!, but it should lead you into the right direction)

Hope this helps

/Hans Received on Wed Sep 25 1996 - 00:00:00 CEST