About Schema

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 ;