Re: Current Tablespace

elizabeth1000_at_my-deja.com wrote:

> I knew this is a beginner question, but I'm just begining in Oracel.
>
> How can I determine my current tablespace? The Oracle Migration tool
> from Access created a tablespace for each load, the data is now in the
> permanent table and I want to drop all the tablespaces except for one
> which I am currently using.

SELECT table_name, tablespace_name
FROM all_tables
WHERE owner = '<OWNER_NAME>';

DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS; (Make sure you really want to drop them 'cause you can't do a rollback if you change your mind. Just to be on the safe side run the following first:

SELECT table_name, tablespace_name
FROM all_tables
WHERE tablespace_name = '<TABLESPACE_NAME>';

>
>
> Also when you drop user cascade is there a way to drop their tablespace
> too?
>

Sorry, but users don't own tablespaces. Users only own tables that are stored in tablespaces. When you drop user cascade all objects owned by the user, including tables and indexes are dropped. Run the last query above and if no rows are returned drop the tablespace

>
> Thanks
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

hth
Jerry Gitomer

--
Once I figured out how to spell DBA I became one Received on Thu Dec 16 1999 - 00:38:54 CST