Oracle: schemas/tables measurements

Size of schemas:

1 2 3 4 5 6

SELECT owner, sum(bytes)/1024/1024 "Size in MiB" FROM dba_segments WHERE owner not in ('GPRS_TEST_DB', 'GPRS_TRIAL_DB', 'MSG_DB', 'PARAM_DB', 'PERF_DB', 'TASK_DB', 'VAS_INDICATOR_DB', 'VAS_LOG_DB', 'VAS_MULTIMSI_DB', 'VAS_PROPERTY_DB', 'XDB') and owner like '%_DB' group by owner order by owner;

Size of tables

1 2 3 4 5

SELECT owner, segment_type, segment_name, sum(bytes)/1024/1024 "Size in MiB" FROM dba_segments WHERE segment_type like 'TABLE%' and segment_name not like 'BIN%' group by owner, segment_type, segment_name;

Num_rows of table

1 2 3 4 5 6

select owner, table_name, num_rows from dba_tables WHERE owner not in ('GPRS_TEST_DB', 'GPRS_TRIAL_DB', 'MSG_DB', 'PARAM_DB', 'PERF_DB', 'TASK_DB', 'VAS_INDICATOR_DB', 'VAS_LOG_DB', 'VAS_MULTIMSI_DB', 'VAS_PROPERTY_DB', 'XDB') and owner like '%_DB' and table_name ='HT_CO_OFFER' order by owner;

Count indexes per table

1 2 3 4 5 6

SELECT owner, table_name, count(index_name) FROM all_indexes WHERE owner not in ('GPRS_TEST_DB', 'GPRS_TRIAL_DB', 'MSG_DB', 'PARAM_DB', 'PERF_DB', 'TASK_DB', 'VAS_INDICATOR_DB', 'VAS_LOG_DB', 'VAS_MULTIMSI_DB', 'VAS_PROPERTY_DB', 'XDB') and owner like '%_DB' group by owner, table_name order by owner;