addon to my last question

[mokesql]

i want to get all tables which size is over as example 5 mb. is there a sql statement which gets me all the table names of the table who have more than this value?
thnx

kemo

 

[jcote]

select segment_name, bytes from user_segments
where segment_type = 'TABLE';

Take into account that this number represent the number of bytes allocated for inserting rows. This space might be 10%, 20% or 90% free. To have a better idea of the space used by your table, you would have to use the ANALYZE command and check the info in USER_TABLES (I don't know them off the top of my head).

 

[carp]

select segment_name, bytes from user_segments
where segment_type = 'TABLE'
WHERE bytes > 5242880;

To find tables with over 5M of data (rather than allocated space), analyze the tables, then go to DBA_TABLES -

SELECT table_name, num_rows * avg_row_len data_size
FROM dba_tables
WHERE data_size > 5242880;