CODEWITHSUNDEEP
sqloracle-databaseoracle11g
ProgNi
ProgNi

Reputation: 109

Check schemas tablespace usage in oracle

I have multiple non-dba schemas each assigned their respective tablespaces.

Is there a way for schema user to check their own assigned tablespace useage without having access to dba tables?

I want schema user to be able to check their own tablespace useage only.

I am using Oracle 11g

Upvotes: 0

Views: 10826

Answers (1)

user330315
user330315

Reputation:

Every user is allowed to access user_segments which stores the size information for each segment (table, index, ...)

To get the size of all segments use: 

select segment_name,
       segment_type,
       tablespace_name,
       sum(bytes) / 1024 / 1024 as total_bytes_mb
from user_segments
group by segment_name, segment_type, tablespace_name
order by total_bytes_mb desc;

The above will show LOB segments as e.g. SYS_LOB0005330538C00010$$

To match LOB segments to the corresponding columns you can use: 

select case 
          when lb.column_name is not null then lb.table_name||'.'||lb.column_name||' ('||sg.segment_name||')' 
          else sg.segment_name 
        end as segment_name, 
        sg.tablespace_name,
        sg.segment_type,
        sum(sg.bytes) / 1024 / 1024 as total_bytes_mb
from user_segments sg
  left join user_lobs lb on lb.segment_name = sg.segment_name
group by sg.segment_name, sg.segment_type, lb.column_name, lb.table_name, sg.tablespace_name
order by total_bytes_mb desc;

Upvotes: 3

Related Questions