Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: drop tablespace

Re: drop tablespace

From: <fitzjarrell_at_cox.net>
Date: Mon, 15 Oct 2007 07:34:39 -0700
Message-ID: <1192458879.908511.110550@i13g2000prf.googlegroups.com>


On Oct 15, 9:15 am, gazzag <gar..._at_jamms.org> wrote:
> On 15 Oct, 15:14, gazzag <gar..._at_jamms.org> wrote:
>
>
>
>
>
> > On 15 Oct, 15:06, Barry Bulsara <bbulsar..._at_hotmail.com> wrote:
>
> > > 10.2 RHEL
>
> > > Hi, could someone point me in the right direction for finding out all
> > > tablespaces being used for a given user.
>
> > > When our DB was created, someone just assigned users to default users
> > > tablespace and it got very very large. We have manually reassigned
> > > tablespaces for all users now and want to drop the default users
> > > tablespace. Before we do this, we want to check that this is not the
> > > default tablespace for any users.
>
> > > Thank you
> > > Barry.
>
> > select owner, tablespace_name, count(*)
> > from dba_segment
> > group by owner, tablespace_name
>
> > HTH
>
> > -g
>
> Apologies. The query above should be:
>
> select owner, tablespace_name, count(*)
> from dba_segmentS
> group by owner, tablespace_name
>
> HTH
>
> -g- Hide quoted text -
>
> - Show quoted text -

The default tablespace can be overridden in the create table/create index command, therefore that query may not reveal which users are still assigned the USERS tablespace as default. Querying DBA_USERS would, however:

select username, default_tablespace
from dba_users;

to produce a list of all user names and their default tablespace assignment. Or:

select default_tablespace, count(*)
from dba_users
group by default_tablespace;

to show how many users are assigned to each default tablespace. Modifying that query to prove that the USERS tablespace is no longer assigned as a default:

select default_tablespace, count(*)
from dba_users
where default_tablespace = 'USERS'
group by default_tablespace;

You should see 'no rows selected' if you've moved every user away from the USERS tablespace. Of course you will need to know if any objects still exist in the USERS tablespace, and gazzag's query would prove that. You'll need more than one query to ascertain that the USERS tablespace is no longer used and is safe to drop.

David Fitzjarrell Received on Mon Oct 15 2007 - 09:34:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US