Checking the size of objects in an Oracle Database can be done using built-in dictionary views. These views provide information on how much space different database objects like tables and indexes are using. By querying these views, you can easily see the storage size of each object. This helps you manage your database storage more effectively and ensures that your database remains efficient. Using these views regularly can help you keep track of and optimize your database's performance.
Tablespace Size Query
SELECT ts.tablespace_name,
size_info.megs_alloc,
size_info.megs_free/1024,
size_info.megs_used,
size_info.pct_free,
size_info.pct_used,
size_info.MAX,
size_info.Available /1024 Available_GB,
Available_Percentage
FROM (SELECT a.tablespace_name,
ROUND (a.bytes_alloc / 1024 / 1024) megs_alloc,
ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) megs_free,
ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024)
megs_used,
ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100)
Pct_Free,
100 - ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100)
Pct_used,
ROUND (maxbytes / 1048576) MAX,
((ROUND (maxbytes / 1048576))- (ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024))) as Available
, (((ROUND (maxbytes / 1048576))- (ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024))) /(ROUND (maxbytes / 1048576)))*100 as Available_Percentage
FROM ( SELECT f.tablespace_name,
SUM (f.bytes) bytes_alloc,
SUM (
DECODE (f.autoextensible,
'YES', f.maxbytes,
'NO', f.bytes))
maxbytes
FROM dba_data_files f
GROUP BY tablespace_name) a,
( SELECT f.tablespace_name, SUM (f.bytes) bytes_free
FROM dba_free_space f
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
UNION ALL
SELECT h.tablespace_name,
ROUND (SUM (h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
ROUND (
SUM (
(h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0))
/ 1048576)
megs_free,
ROUND (SUM (NVL (p.bytes_used, 0)) / 1048576) megs_used,
ROUND (
( SUM (
(h.bytes_free + h.bytes_used)
- NVL (p.bytes_used, 0))
/ SUM (h.bytes_used + h.bytes_free))
* 100)
Pct_Free,
100
- ROUND (
( SUM (
(h.bytes_free + h.bytes_used)
- NVL (p.bytes_used, 0))
/ SUM (h.bytes_used + h.bytes_free))
* 100)
pct_used,
ROUND (
SUM (
DECODE (f.autoextensible,
'YES', f.maxbytes,
'NO', f.bytes)
/ 1048576))
MAX,
(((ROUND (SUM (DECODE (f.autoextensible,'YES', f.maxbytes,'NO', f.bytes) / 1048576))) -(ROUND (SUM (NVL (p.bytes_used, 0)) / 1048576)))/1024) as Available_GB
,((((ROUND (SUM (DECODE (f.autoextensible,'YES', f.maxbytes,'NO', f.bytes) / 1048576))) -(ROUND (SUM (NVL (p.bytes_used, 0)) / 1048576)))) /(ROUND (SUM (DECODE (f.autoextensible,'YES', f.maxbytes,'NO', f.bytes) / 1048576)))) * 100 as Available_Percentage
FROM sys.v_$TEMP_SPACE_HEADER h,
sys.v_$Temp_extent_pool p,
dba_temp_files f
WHERE p.file_id(+) = h.file_id
AND p.tablespace_name(+) = h.tablespace_name
AND f.file_id = h.file_id
AND f.tablespace_name = h.tablespace_name
GROUP BY h.tablespace_name) size_info,
sys.dba_tablespaces ts,
sys.dba_tablespace_groups tsg
WHERE ts.tablespace_name = size_info.tablespace_name
AND ts.tablespace_name = tsg.tablespace_name(+)
order by Available_Percentage asc;
Check Tables Size
SELECT (SELECT SYSDATE FROM DUAL) AS dbdate,
(SELECT NAME FROM v$database) AS database_name,
'' AS tablespace_name,
owner,
table_name,
SUM (BYTES) AS total,
0 AS free,
0 AS used,
0 AS maxbytes,
(SELECT num_rows
FROM dba_tables a
WHERE a.owner = ss.owner AND a.table_name = ss.table_name)
AS rowscount
FROM (SELECT segment_name table_name, owner, BYTES
FROM dba_segments
WHERE segment_type IN
('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
SELECT i.table_name, i.owner, s.BYTES
FROM dba_indexes i,
dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN
('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.BYTES
FROM dba_lobs l,
dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.BYTES
FROM dba_lobs l,
dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX') ss
where owner='CISADM'
GROUP BY table_name,owner
order by table_name
;
Check Recover Area Size
select name, floor(space_limit / 1024 / 1024/1024) "Size GB", ceil(space_used / 1024 / 1024/1024) "Used GB" from v$recovery_file_dest order by name;
Archive log Size During some Period
select trunc(first_time),count(),sum(blocksblock_size)/1024/1024/1024 from V$ARCHIVED_LOG
where first_time > sysdate -4 group by trunc(first_time) order by trunc(first_time) ;