Drop tablespace example

[steeve123]

Drop tablespace example

I have installed oracle 9i by default, and i want to drop the tablespace example. But it contains materialize view and a lot of object, and because of that i cant just do a drop tablespace example including contents. Is There a document or a script made to drop that tablespace?

[gandolf989]

Try this.

spool dropusers.sql
SELECT DISTINCT 'DROP USER '||owner||' CASCADE;' FROM DBA_TABLES
WHERE TABLESPACE_NAME = 'EXAMPLE';
@dropusers.sql

alter tablespace example offline;
drop tablespace example;

This will drop all of the schemas that use that tablespace and then drop that tablespace. Which is good as long as you don't have any real data stored there.

Dropping a tablespace with including contents should drop the tablespace and everything in it regardless of RI, Triggers, or indexes.

[nagarjuna]

Originally posted by gandolf989
Try this.
Dropping a tablespace with including contents should drop the tablespace and everything in it regardless of RI, Triggers, or indexes.

Regardless of Triggers???? you meant to say, you want to drop system tablespace????

[steeve123]

EXAMPLE, NOT SYSTEM