DBA_EXTENTS
DBA_SEGMENTS
DBA_TABLESPACES
DBA_DATA_FILES
DBA_FREE_SPACE
SELECT SUBSTR(name,1,25) name, substr(value,1,25) value from v$parameter
where name='db_block_size';
NAME VALUE
------------- -----
db_block_size 6144
DBA_SEGMENTS view:
SELECT substr(segment_name,1,23) segment_name, substr(segment_type,1,8) seg_type,
substr(tablespace_name,1,15) tablespace_name, substr(extents,1,7) extents,
substr(blocks,1,6) blocks, substr(bytes,1,5) bytes FROM dba_segments WHERE owner='HR';
SEGMENT_NAME SEG_TYPE TABLESPACE_NAME EXTENTS BLOCKS BYTES
----------------------- -------- --------------- ------- ------ -----
REGIONS TABLE EXAMPLE 1 11 67584
LOCATIONS TABLE EXAMPLE 1 11 67584
DEPARTMENTS TABLE EXAMPLE 1 11 67584
JOBS TABLE EXAMPLE 1 11 67584
EMPLOYEES TABLE EXAMPLE 1 11 67584
JOB_HISTORY TABLE EXAMPLE 1 11 67584
REG_ID_PK INDEX EXAMPLE 1 11 67584
COUNTRY_C_ID_PK INDEX EXAMPLE 1 11 67584
LOC_ID_PK INDEX EXAMPLE 1 11 67584
DEPT_ID_PK INDEX EXAMPLE 1 11 67584
JOB_ID_PK INDEX EXAMPLE 1 11 67584
=> implies 11 blocks x 6K = 66K in 1 extent allocated to each segment
DBA_EXTENTS view:
SELECT extent_id, file_id, block_id, blocks from dba_extents
WHERE owner='HR' AND segment_name='EMPLOYEES';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 5 110 11
SELECT substr(segment_name,1,20) segment_name, substr(tablespace_name,1,15) tablespace_name,
extent_id, file_id, block_id, blocks from dba_extents WHERE owner='HR';
SEGMENT_NAME TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
-------------------- --------------- ---------- ---------- ---------- ----------
REGIONS EXAMPLE 0 5 11 11
LOCATIONS EXAMPLE 0 5 44 11
DEPARTMENTS EXAMPLE 0 5 66 11
JOBS EXAMPLE 0 5 88 11
EMPLOYEES EXAMPLE 0 5 110 11
JOB_HISTORY EXAMPLE 0 5 143 11
REG_ID_PK EXAMPLE 0 5 22 11
COUNTRY_C_ID_PK EXAMPLE 0 5 33 11
LOC_ID_PK EXAMPLE 0 5 55 11
DEPT_ID_PK EXAMPLE 0 5 77 11
JOB_ID_PK EXAMPLE 0 5 99 11
SELECT SUM(blocks) from dba_extents WHERE tablespace_name='EXAMPLE';
SUM(BLOCKS)
-----------
23936
SELECT substr(segment_name,1,20) segment_name, substr(tablespace_name,1,15) tablespace_name,
extent_id, file_id, block_id, blocks from dba_extents WHERE tablespace_name='EXAMPLE'
ORDER BY segment_name;
Shows the list of extents allocated to each segment as the object grew.
DBA_FREE_SPACE view:
SELECT SUBSTR(tablespace_name,1,15) tablespace_name, count(*), max(blocks), sum(blocks),
sum(bytes) from dba_free_space GROUP BY tablespace_name;
TABLESPACE_NAME COUNT(*) MAX(BLOCKS) SUM(BLOCKS) SUM(BYTES)
--------------- ---------- ----------- ----------- ----------
CWMLITE 1 11 11 67584
DRSYS 1 33 33 202752
EXAMPLE 1 88 88 540672
INDX 1 4257 4257 26155008
SYSTEM 1 852 852 5234688
TOOLS 1 1694 1694 10407936
UNDOTBS 11 3872 9966 61231104
USERS 1 4257 4257 26155008
(DB_BLOCK_SIZE=6K)
Max(blocks) -> size of the biggest free data block.
Sum(blocks) -> total no. of free data blocks -> x block size -> total free space in bytes.
SELECT df.file#, substr(df.name,1,45) datafile, substr(ts.name,1,15) tablespace_name,
df.bytes, df.blocks from v$datafile df, v$tablespace ts where df.TS#=4 and df.ts#=ts.ts# ;
FILE# DATAFILE TABLESPACE_NAME BYTES BLOCKS
----- ----------------- ------------------ --------------- --------- ------
5 D:\ORACLE\ORADATA\CHIK\EXAMPLE01.DBF EXAMPLE 147683328 24037
Accounting for Tablespace utilization : (Tablespace : Example)
-------------------------------------------------------------
created size : 152.00 MB
reported size : 140.84 MB
free space+used extent : 140.77 MB
Free space: 88 BLOCKS X 6k = 540672 bytes
Extents : 23936 BLOCKS X 6k = 147062784 bytes
-----------------------------------------------
Total : 24024 blocks = 147603456 bytes
===============================================
