Oracle Dbt ... Db@'s Can Answer

[kiraaaak]

[color=#000000][font=arial, sans-serif][size=3]
[size=5]how to find out how big a table is?? ante table size entha undi chudu prod. lo anthakanna peddadi creat cheyamanali annadu manager .. ela kanukovadam...[/size][/size][/font][/color]
[color=#000000][font=arial, sans-serif][size=3]
[size=5]no. of records.. okati inka .... no of blocks, no of extends ani edo annadu .. mukka rdam kale ... DBA's plzz help [/size][size=5] [/size][size=5] [/size]
[size=5] [/size][/size][/font][/color][color=#000000][font=arial, sans-serif][size=3]
[size=5] [/size][/size][/font][/color]

[150bryant]

lookinto commands like count and verify data columns

[kiraaaak]

[quote name='jamajacha' timestamp='1328829982' post='1301396330']
lookinto commands like count and verify data columns
[/quote]
count ante ba .. no. of rows vastadi ... ok ... verify data columns ante ?

[150bryant]

[list]
[*]
[left]Do you want only the row space consumed? ( [i] select avg_row_len*num_rows from dba_tables[/i])
[/left]
[*]
[left]Do you want to include allocated file space for the table? ([i]select . . . from dba_segments[/i])
[/left]
[*]
[left]Do you want to include un-used extent space? ([i]select . . . from dba_data_files, dba_extents . . [/i])
[/left]
[*]
[left]Do you want to include un-used space up to the high water mark? This may over-estimate the real Oracle table size.[/left]
[/list]

[150bryant]

[color=#3333FF][font=courier new][color=#000000][font=georgia]There is no oracle defined function for getting size of a table. After all if it is easy with one simple query who will require a function. Isn't it?[/font]

[font=georgia]Anyway you can choose to save this query as a function for easy retrieval.[/font][/color]

select[/font][/color]
[font=courier new]segment_name table_name, [/font]
[font=courier new]sum(bytes)/(1024*1024) table_size_meg[/font]
[font=courier new][color=#3333FF]from[/color] user_extents[/font]
[font=courier new][color=#3333FF]where[/color] segment_type='TABLE'[/font]
[font=courier new][color=#3333FF]and[/color] segment_name = '&table_name'[/font]
[font=courier new][color=#3333FF]group by[/color] segment_name;

[font=georgia]Read more on what all to remember while getting the size of a table. [/font][url="http://askanantha.blogspot.com/2007/09/get-oracle-table-size.html"]Click here[/url]

[font=georgia][b]Create your own function for the purpose:[/b][/font][/font]

[font=courier new]CREATE OR REPLACE FUNCTION get_table_size
(t_table_name VARCHAR2)RETURN NUMBER IS[/font]
[font=courier new]l_size NUMBER;[/font]
[font=courier new]BEGIN[/font]
[font=courier new]SELECT [/font][font=courier new]sum(bytes)/(1024*1024)
INTO l_size
FROM [/font][font=courier new]user_extents
WHERE [/font][font=courier new]segment_type='TABLE'
AND segment_name = t_table_name;

RETURN l_size;[/font]
[font=courier new]EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;[/font]
[font=courier new]/[/font]

[font=georgia]Example:[/font]
[font=courier new]SELECT get_table_size('EMP') Table_Size from dual[/font][font=courier new];[/font]

[font=georgia]Result:[/font]
[font=courier new][b]Table_Size[/b][/font]
[font=courier new]0.0625[/font]

[brahmichowdary]

Usual ga table size rows lo cheppu

Ledantey give then approx tablespace

[kiraaaak]

jamajacha .. thanks ba