To calculate the schema (user) size :
- The schema is owned by the user. To find out which user has how big of the DB:
SELECT s.owner,SUM (s.BYTES) / (1024 * 1024 * 1024) SIZE_IN_GB FROM dba_segments s GROUP BY s.owner;
- If you are logged in as the schema user, you can issue the following command to get the size of the database files:
select sum(bytes)/1024/1024/1024 data_size from dba_data_files ;
- You can find the size of the individual tables in that schema using the following query:
select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB
from all_tables
where owner='<schema owner username>' -- Exclude system tables.
and num_rows > 0 -- Ignore empty Tables.
order by MB desc -- Biggest first.
;
- To list all the objects owner by the schema user :
SELECT tablespace_name, owner, segment_type "Object Type"
FROM sys.dba_segments WHERE owner='TKCSOWNER' ;
select object_name, object_type from user_objects where object_type='TABLE' ;
select object_name, object_type from user_objects where object_type='INDEX' ;
Here is the script that does that :
spool sch.out replace
column object_name format a30
select object_name, object_type from user_objects where object_type='TABLE' ;
column owner format a30
SELECT s.owner,SUM (s.BYTES) / (1024 * 1024 * 1024) SIZE_IN_GB FROM dba_segments s GROUP BY s.owner;
select sum(bytes)/1024/1024/1024 data_size from dba_data_files ;
column owner format a30
column table_name format a30
select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB
from all_tables
where owner='tpcc' -- Exclude system tables.
and num_rows > 0 -- Ignore empty Tables.
order by MB desc -- Biggest first.
;
spool off ;
- To find info about columns and indexes on all tables
spool /tmp/123.lst replace
column table_name format a20
select table_name, count(*) from all_tab_columns where owner='TKCSOWNER' and table_name in (select object_name from user_objects where object_type='TABLE') group by table_name;
column uniqueness format a15
column index_name format a15
column table_name format a15
column column_name format a15
select
b.uniqueness, a.index_name, a.table_name, a.column_name
from all_ind_columns a, all_indexes b
where a.index_name=b.index_name
and a.index_owner='TKCSOWNER'
and a.table_name in (select object_name from user_objects where object_type='TABLE')
order by a.table_name, a.index_name, a.column_position;
spool off;
To find out if any non standard block size is used:
select unique block_size from V$buffer_pool ;
