0

In Oracle 12c, I'm trying to find an Oracle SQL query that may determine how much total storage was consumed from a particular tablespace by one schema or multiple schemas.

Note: I don't need to know the available space, just the space consumed by those particular schemas together.

Thanks in advance.

Phillip
  • 43
  • 1
  • 12
  • Does this answer your question? [Find out free space on tablespace](https://stackoverflow.com/questions/7672126/find-out-free-space-on-tablespace) – Popeye Jan 23 '20 at 18:02
  • @Tejash It does not. I need to know how much space that a schema or schemas have taken. Not the available space of a tablespace. – Phillip Jan 23 '20 at 18:34

2 Answers2

0

I believe the data you need is in DBA_SEGMENTS. For example, you could list all the schemas with objects in the 'USERS' tablespace as follows:

select tablespace_name, owner
, round(sum(bytes) / (1024*1024) , 1) mb_used_in_ts
from dba_segments
where 1=1
  and tablespace_name = 'USERS'
group by tablespace_name, owner
;

The following query does a little more than you asked, but you should find it useful:

with ts_name as
(
select 'DEFAULT' tablespace_type
, DEFAULT_TABLESPACE tablespace_name
from dba_users where username = upper(:username)
union
select 'INDEX'  tablespace_type
, substr(default_tablespace, 1, instr(default_tablespace, '_DATA'))||'INDEX' tablespace_name
from dba_users where username = upper(:username)
union
select 'Data?'  tablespace_type
, upper(:username) || '_DATA' tablespace_name
from dual 
union
select 'Index?'  tablespace_type
, upper(:username) || '_INDEX' tablespace_name
from dual 
union
select ':tablespace input'  tablespace_type
,  upper(:tablespace)  tablespace_name
from dual 
union
select ':tablespace2 input'  tablespace_type
,  upper(:tablespace2)   tablespace_name
from dual 
)
SELECT to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') date_checked
, df.tablespace_name "Tablespace"
, nvl(totalusedspace, 0) "Used MB"
, nvl((df.totalspace - tu.totalusedspace), df.totalspace) "Free MB"
, df.totalspace "Total MB"
, nvl(ROUND (100 * ((df.totalspace - tu.totalusedspace) / df.totalspace)), 100) "Pct. Free"
, (select SEGMENT_SPACE_MANAGEMENT 
   from dba_tablespaces a
  where a.tablespace_name = df.tablespace_name) SEGMENT_SPACE_MANAGEMENT
FROM (SELECT   tablespace_name, ROUND (SUM (BYTES) / (1024*1024)) totalspace
            FROM dba_data_files
        GROUP BY tablespace_name) df,
       (SELECT   ROUND (SUM (BYTES) / (1024 * 1024)) totalusedspace,
                 tablespace_name
            FROM dba_segments
        GROUP BY tablespace_name) tu
 WHERE df.tablespace_name = tu.tablespace_name (+)
   AND df.tablespace_name in (select tablespace_name from ts_name) 
order by 6 
;
Roger Cornejo
  • 1,507
  • 1
  • 8
  • 7
  • Not sure if I completely understood this query correctly, but I was looking to see if I could get storage information on one or more schemas from a single tablespace, not multiple tablespaces. – Phillip Jan 23 '20 at 19:07
  • @Phillip I believe you want to see who all uses a tablespace and how much they use. See edit to original answer. – Roger Cornejo Jan 24 '20 at 16:03
0

Use the dba_segments view, e.g.:

select owner, sum(bytes)
from dba_segments
where tablespace_name = 'MYTABLESPACE'
group by owner
order by sum(bytes) desc;

The owner column is the schema name that owns the objects that have allocated some storage space.

You can further break down the results by object type (e.g. TABLE, INDEX) as well:

select segment_type, sum(bytes)
from dba_segments
where tablespace_name = 'MYTABLESPACE'
and owner = 'MYSCHEMA'
group by segment_type
order by sum(bytes) desc;

And further by object name:

select segment_name, sum(bytes)
from dba_segments
where tablespace_name = 'MYTABLESPACE'
and owner = 'MYSCHEMA'
and segment_type = 'TABLE'
group by segment_name
order by sum(bytes) desc;
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158