kiraaaak Posted February 9, 2012 Report Share Posted February 9, 2012 [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] Link to comment Share on other sites More sharing options...
150bryant Posted February 9, 2012 Report Share Posted February 9, 2012 lookinto commands like count and verify data columns Link to comment Share on other sites More sharing options...
kiraaaak Posted February 9, 2012 Author Report Share Posted February 9, 2012 [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 ? Link to comment Share on other sites More sharing options...
150bryant Posted February 9, 2012 Report Share Posted February 9, 2012 [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] Link to comment Share on other sites More sharing options...
150bryant Posted February 9, 2012 Report Share Posted February 9, 2012 [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] Link to comment Share on other sites More sharing options...
brahmichowdary Posted February 9, 2012 Report Share Posted February 9, 2012 Usual ga table size rows lo cheppu Ledantey give then approx tablespace Link to comment Share on other sites More sharing options...
kiraaaak Posted February 9, 2012 Author Report Share Posted February 9, 2012 jamajacha .. thanks ba Link to comment Share on other sites More sharing options...
Recommended Posts