I am trying to find the space occupied by list of tables in Oracle DB (for various SCHEMAS ) ,currently i am using this query
QUERY 1 – DBA_SEGMENTS
SELECT SEGMENT_NAME,SUM (BYTES)/1024/1024/1024 AS GB, ROUND(100*RATIO_TO_REPORT(GB) OVER (), 4) PERCENTAGE FROM DBA_SEGMENTS WHERE OWNER='SCHEMA_NAME' AND SEGMENT_TYPE ='TABLE' AND SEGMENT_NAME IN ( 'LIST OF TABLES' ) GROUP BY SEGMENT_NAME,BYTES ORDER BY GB DESC;
QUERY 2 – DBA_OBJECTS
SELECT DISTINCT OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER='SCHEMA' AND OBJECT_NAME IN ( 'LIST OF TABLES' ) GROUP BY OBJECT_NAME ORDER BY OBJECT_NAME;
when i compare the LIST OF TABLES counts it’s not matching , what could be the reason ?
can you please suggest is this correct or is there a better way to achieve this ?
Reference https://serverfault.com/questions/132620/oracle-how-to-find-out-storage-space-used-by-a-table
Additional Query 1
SET SERVEROUTPUT ON;
DECLARE
input_owner NVARCHAR2(128) := 'SCHEMA_NAME';
segment_size_blocks NUMBER;
segment_size_bytes NUMBER;
used_blocks NUMBER;
used_bytes NUMBER;
expired_blocks NUMBER;
expired_bytes NUMBER;
unexpired_blocks NUMBER;
unexpired_bytes NUMBER;
total_blocks NUMBER;
total_bytes NUMBER;
unused_blocks NUMBER;
unused_bytes NUMBER;
last_ext_file_id NUMBER;
last_ext_blk_id NUMBER;
last_used_blk NUMBER;
result_table NVARCHAR2(128);
result_segment_type NVARCHAR2(128);
result_used_mb NUMBER;
result_unused_mb NUMBER;
result_total_mb NUMBER;
CURSOR cur
IS
SELECT
s.segment_name AS segment_name,
s.owner AS segment_owner,
s.partition_name AS partition_name,
s.segment_type AS segment_type,
CASE WHEN s.segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
THEN s.segment_name
WHEN s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
THEN (SELECT i.table_name
FROM dba_indexes i
WHERE s.segment_name = i.index_name AND s.owner = i.owner)
WHEN s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
THEN (SELECT l.table_name
FROM dba_lobs l
WHERE s.segment_name = l.segment_name AND s.owner = l.owner)
WHEN s.segment_type IN ('LOBINDEX')
THEN (SELECT l.table_name
FROM dba_lobs l
WHERE s.segment_name = l.index_name AND s.owner = l.owner)
ELSE 'Unknown'
END AS table_name,
s.bytes AS segment_bytes
FROM dba_segments s
WHERE owner = input_owner
and segment_name in
(
)
ORDER BY table_name, segment_type;
BEGIN
dbms_output.put_line('table ; segment type ; used (mb) ; unused (mb) ; total (mb)');
FOR ro IN cur
LOOP
result_table := ro.table_name;
result_segment_type := ro.segment_type;
IF ro.segment_type IN ('TABLE', 'INDEX')
THEN
dbms_space.unused_space(
segment_owner => ro.segment_owner,
segment_name => ro.segment_name,
segment_type => ro.segment_type,
total_blocks => total_blocks,
total_bytes => total_bytes,
unused_blocks => unused_blocks,
unused_bytes => unused_bytes,
last_used_extent_file_id => last_ext_file_id,
last_used_extent_block_id => last_ext_blk_id,
last_used_block => last_used_blk);
result_used_mb := (total_bytes - unused_bytes) / 1024 / 1024;
result_unused_mb := unused_bytes / 1024 / 1024;
result_total_mb := total_bytes / 1024 / 1024;
ELSIF ro.segment_type IN ('LOBSEGMENT')
THEN
dbms_space.space_usage(
segment_owner => ro.segment_owner,
segment_name => ro.segment_name,
segment_type => 'LOB',
partition_name => ro.partition_name,
segment_size_blocks => segment_size_blocks,
segment_size_bytes => segment_size_bytes,
used_blocks => used_blocks,
used_bytes => used_bytes,
expired_blocks => expired_blocks,
expired_bytes => expired_bytes,
unexpired_blocks => unexpired_blocks,
unexpired_bytes => unexpired_bytes
);
result_used_mb := used_bytes / 1024 / 1024;
result_unused_mb := (segment_size_bytes - used_bytes) / 1024 / 1024;
result_total_mb := segment_size_bytes / 1024 / 1024;
ELSE
-- TODO ??
result_used_mb := ro.segment_bytes / 1024 / 1024;
result_unused_mb := 0;
result_total_mb := result_used_mb + result_unused_mb;
END IF;
dbms_output.put_line(
RPAD(result_table, 30) || '; ' ||
RPAD(result_segment_type, 20)|| '; ' ||
TO_CHAR(result_used_mb / 1024 / 1024, '999999999990D00')|| '; ' ||
TO_CHAR(result_unused_mb / 1024 / 1024, '999999999990D00')|| '; ' ||
TO_CHAR(result_total_mb / 1024 / 1024, '999999999990D00'));
END LOOP;
END;
Additional Query 2
WITH DA AS (
SELECT OWNER, SEGMENT_NAME, SUM(BYTES)/1024/1024 SIZE_MB
FROM DBA_EXTENTS
GROUP BY ROLLUP(OWNER, SEGMENT_NAME)
) SELECT OWNER, SEGMENT_NAME, SIZE_MB, ROUND(SIZE_MB/TOTAL_MB*100)
FROM DA
CROSS JOIN (
SELECT SIZE_MB AS TOTAL_MB
FROM DA T WHERE OWNER IS NULL AND SEGMENT_NAME IS NULL
)
ORDER BY SIZE_MB DESC
Advertisement
Answer
Here is a fairly accurate query that takes tables, indexes, and any partitioned tables/indexes into account for all tables for a single schema owner. Note that there will be some additional overhead in actual space used due to blocksize, file headers, etc.
select segment_name
, segment_type
, sum(bytes)/1024/1024/1024 as GB
, ROUND(100*RATIO_TO_REPORT(bytes) OVER (), 4) PERCENTAGE
from dba_segments ds
where
owner ='SCOTT'
and (
segment_type like 'TABLE%'
and segment_name in ('EMP', 'SALGRADE', 'EMP_BKP', 'DEPT')
or
(
segment_type like 'INDEX%'
and segment_name in (
select index_name from dba_indexes di
where owner ='SCOTT'
and di.table_name in ('EMP', 'SALGRADE', 'EMP_BKP', 'DEPT')
)
-- more fun here for LOBs, etc.
)
)
group by segment_name, segment_type, bytes
order by GB desc;
Take a look at DBA_LOBS for additional criteria to add to the -- more fun here to pick up LOB information.