| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: REORG A TABLE ?
Hi,
You can use this script tocalculate table row sizes and rows per block..it will also help you to determine table sizes and space requirements...! This script can do sizing for tables with data from the analyzed data in dba_tables or your own sizing table in sz_tables. The Catalog Space Needed amount typically exceeds that for Analyzed Space Needed, because full column sizes are used from the dba_tab_columns in the calculation of the catalog space. This calculation is useful because if all else stays the same, this is the largest amount of space you will need. You can run this script from dba_tables to report current space requirements and then run it from sz_tables to see future equirements, take the difference, and determine whether you have enough space to extend the Tablespace.
set echo off
set linesize 132
set pagesize 20
col owner for a08 head 'Owner'
col table_name for a30 head 'Table Name'
col cat_rowsize for 99999 head 'Catalog|Rowsize'
col cat_rows_per_block for 999999 head 'Catalog|Rows|per|Block'
col anl_rowsize for 99999 head 'Analyzed|Rowsize'
col anl_rows_per_block for 999999 head 'Analyzed|Rows|per|Block'
col anl_num_rows for 999999999 head 'Analyzed|Number|of Rows'
col anl_blocks for 999999 head 'Analyzed|Blocks|Needed'
col anl_space for 999999 head 'Analyzed|Space|Needed|(Meg)'
col cat_space for 999999 head 'Catalog|Space|Needed|(Meg)
col tbl_pct_free for 999 head 'Pct|Free'
col tbl_pct_used for 999 head 'Pct|Used'
select y.owner, y.table_name,
(3*z.ub1)+y.rowsize
cat_rowsize,
FLOOR(availspace/((3*z.ub1)+y.rowsize))
cat_rows_per_block,
(3*z.ub1)+avg_row_len
anl_rowsize,
FLOOR(availspace/((3*z.ub1)+avg_row_len))
anl_rows_per_block,
num_rows
anl_num_rows,
FLOOR(num_rows/(FLOOR(availspace/((3*z.ub1)+avg_row_len))))
anl_blocks,
FLOOR((db_block_size*FLOOR(num_rows/(FLOOR(availspace/
((3*z.ub1)+avg_row_len)))))/1024/1024)
anl_space,
FLOOR((db_block_size*FLOOR(num_rows/(FLOOR(availspace/
((3*z.ub1)+((3*z.ub1)+y.rowsize))))))/1024/1024)
cat_space,
tbl_pct_free,
tbl_pct_used
sum(decode(sign(data_length-249),1,data_length+3,data_length+1))
rowsize
from sys.dba_tab_columns zz
group by owner,table_name) y,
(select j.owner owner,
j.table_name table_name,
j.avg_row_len avg_row_len,
j.num_rows num_rows,
j.pct_free tbl_pct_free,
j.pct_used tbl_pct_used,
(a.db_block_size - b.kcbh - c.ub4 - d.ktbbh - f.kdbh -
((ini_trans-1)*e.ktbit)) hsize,
CEIL((a.db_block_size - b.kcbh - c.ub4 - d.ktbbh - f.kdbh -
((ini_trans-1)*e.ktbit)) *
( 1 - pct_free / 100 )) - g.kdbt availspace,
a.db_block_size,b.kcbh,c.ub4,d.ktbbh,e.ktbit,f.kdbh,g.kdbt,h.ub1,i.sb2,ini_t
rans,pct_free from
(select "NAME" ,"VALUE" db_block_size from v$parameter where name =
'db_block_size' ) a,
(select "TYPE" ,"TYPE_SIZE" kcbh from v$TYPE_SIZE where "TYPE" =
'KCBH' ) b,
(select "TYPE" ,"TYPE_SIZE" ub4 from v$TYPE_SIZE where "TYPE" =
'UB4' ) c,
(select "TYPE" ,"TYPE_SIZE" ktbbh from v$TYPE_SIZE where "TYPE" =
'KTBBH') d,
(select "TYPE" ,"TYPE_SIZE" ktbit from v$TYPE_SIZE where "TYPE" =
'KTBIT') e,
(select "TYPE" ,"TYPE_SIZE" kdbh from v$TYPE_SIZE where "TYPE" =
'KDBH' ) f,
(select decode(gg.kdbt,0,c.ub4,gg.kdbt) kdbt from
(select "TYPE" ,"TYPE_SIZE" ub4 from v$TYPE_SIZE where "TYPE" =
) c,
(select "TYPE" ,decode("TYPE_SIZE",NULL,0,"TYPE_SIZE") kdbt from
v$TYPE_SIZE ,dual
where "TYPE"(+) = 'KDBT' and dummy =
) gg ) g,
(select "TYPE" ,"TYPE_SIZE" ub1 from v$TYPE_SIZE where "TYPE" =
'UB1' ) h,
(select "TYPE" ,"TYPE_SIZE" sb2 from v$TYPE_SIZE where "TYPE" =
'SB2' ) i,
AVG_ROW_LEN avg_row_len,
INI_TRANS ini_trans,
PCT_FREE pct_free,
PCT_USED pct_used,
NUM_ROWS num_rows
from &dba_tables jj where jj.table_name like
table_name%') ) j ) z
where y.owner(+) = z.owner
 |
 |