Sunday, July 02, 2006

Views : Storage relationship

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
===============================================

Comments: Post a Comment

Links to this post:

Create a Link



<< Home
Advertisements

eBay.in - The World's Online Marketplace

ARCHIVES
  • March 2006
  • April 2006
  • May 2006
  • June 2006
  • July 2006
  • August 2006
  • September 2006

This page is powered by Blogger. Isn't yours?

Recent Postings

Google