Re: what to check before dropping a tablespace

Answers interspersed ... and for the first time ... I must take exception with your advice.

Anna C. Dent wrote:

> Joe wrote:
>
>> I am going to drop a tablespace
>> and i want to sure the tablespace is empty before i really drop it
>> i know i can query the dba_tables and dba_indexes to see if there is any
>> table or index resides on this tablespace
>> what else do i need to check?
>
>
> Oracle will NOT allow you to drop a tablespace when objects exist in it.

Oracle has four different sytaxes for dropping tablespaces as follows:

DROP TABLESPACE <tablespace_name>; -- will not drop a tablespace containing segments
DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS; -- does exactly what it says
DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES -- as above but drops the datafile too
DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS -- as above but drops any constraints enforced using indexes in the tablespace.

>
>
>>
>> also
>> i would like to know which user have privileges on this tablespace,
>> and what
>> privileges it is
>> so that i can create a new tablespace and assign those privileges to
>> those
>> users on the new tablespace
>> can anyone suggest me where to find these information?
>
>
> User do NOT get "privileges on this tablespace".
> They are granted privs on DB objects.
> At most they have a quota for this tablespace.
>

To the contrary ...

ALTER USER <user_name>
QUOTA 10M on <tablespace_name>;

The query the OP is looking for is:

SELECT username, tablespace_name, bytes
FROM dba_ts_quotas;

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)