Skip to main content
added comments about quotas
Source Link
EdStevens
  • 2k
  • 1
  • 10
  • 8

First, I'd check that it is not specified as the default TS for any user:

select default_tablespace,
       username
from dba_users
order by default_tablespace,
         username
;

Then double-check that nothing has crept back in;

select owner,
       segment_name,
       segment_type
from dba_segments
where tablespace_name='nameoftswearelookingat';

-- Edit - add comments about quota

One other thing to check and that is user quota. If a user has available quota on a TS, they can create segments in it even if it is not their default TS.

select *
from dba_ts_quotas
where tablespace_name='nameofyourtablespace';

First, I'd check that it is not specified as the default TS for any user:

select default_tablespace,
       username
from dba_users
order by default_tablespace,
         username
;

Then double-check that nothing has crept back in;

select owner,
       segment_name,
       segment_type
from dba_segments
where tablespace_name='nameoftswearelookingat';

First, I'd check that it is not specified as the default TS for any user:

select default_tablespace,
       username
from dba_users
order by default_tablespace,
         username
;

Then double-check that nothing has crept back in;

select owner,
       segment_name,
       segment_type
from dba_segments
where tablespace_name='nameoftswearelookingat';

-- Edit - add comments about quota

One other thing to check and that is user quota. If a user has available quota on a TS, they can create segments in it even if it is not their default TS.

select *
from dba_ts_quotas
where tablespace_name='nameofyourtablespace';
Source Link
EdStevens
  • 2k
  • 1
  • 10
  • 8

First, I'd check that it is not specified as the default TS for any user:

select default_tablespace,
       username
from dba_users
order by default_tablespace,
         username
;

Then double-check that nothing has crept back in;

select owner,
       segment_name,
       segment_type
from dba_segments
where tablespace_name='nameoftswearelookingat';