Hello,
I have few suggestions bellow.
Include Statistics :
I think it would be great, to add on table right click a statistics menu
wich can allow to execute dbms_stats.gather_table_stats
And on schema node DBMS_STATS.GATHER_schema_STATS
It can eventually open a dialog box with available options (mainly sampling rate)
Statistics are collected daily by jobs, but on development phases it's not enought.
Eventually it can be nice to have also a dialog or a report wich display statistics table, to have
list of table with row count, col counts,distinct and null values count by column ....
Space informations :
One stuff I found is missing since years in PL/SQL developper is information about used space.
I think it would be great to have some information on space used for an entire schema summarized by table and also detailled information for a table with each subset
I suggest you the query bellow perhaps with subtotal by type and or table
select segment_name TableName,segment_type,NULL Subset,tablespace_name,count(*) NbrExtent,sum(bytes)/1024 KBytes
from sys.user_extents e where segment_type='TABLE'
group by segment_name,segment_type,tablespace_name
union all
select I.table_name,segment_type,I.index_name ,e.tablespace_name,count(*) NbrExtent,sum(bytes)/1024 KBytes
from sys.user_indexes I,sys.user_extents e
where i.index_name=e.segment_name and e.segment_type='INDEX'
group by I.table_name,I.index_name ,segment_type,e.tablespace_name
union all
select l.TABLE_NAME,segment_type,l.COLUMN_NAME,e.tablespace_name,count(*) NbrExtent,sum(bytes)/1024 KBytes
from sys.user_lobs L,sys.user_extents e
where L.SEGMENT_NAME=e.segment_name and e.segment_type='LOBSEGMENT'
group by l.TABLE_NAME,segment_type,l.COLUMN_NAME,e.tablespace_name
union all
select l.TABLE_NAME,segment_type,l.COLUMN_NAME,e.tablespace_name,count(*) NbrExtent,sum(bytes)/1024 KBytes
from sys.user_lobs L,sys.user_extents e
where l.INDEX_NAME=e.segment_name and e.segment_type='LOBINDEX'
group by l.TABLE_NAME,segment_type,l.COLUMN_NAME,e.tablespace_name
order by 1
I have few suggestions bellow.
Include Statistics :
I think it would be great, to add on table right click a statistics menu
wich can allow to execute dbms_stats.gather_table_stats
And on schema node DBMS_STATS.GATHER_schema_STATS
It can eventually open a dialog box with available options (mainly sampling rate)
Statistics are collected daily by jobs, but on development phases it's not enought.
Eventually it can be nice to have also a dialog or a report wich display statistics table, to have
list of table with row count, col counts,distinct and null values count by column ....
Space informations :
One stuff I found is missing since years in PL/SQL developper is information about used space.
I think it would be great to have some information on space used for an entire schema summarized by table and also detailled information for a table with each subset
I suggest you the query bellow perhaps with subtotal by type and or table
select segment_name TableName,segment_type,NULL Subset,tablespace_name,count(*) NbrExtent,sum(bytes)/1024 KBytes
from sys.user_extents e where segment_type='TABLE'
group by segment_name,segment_type,tablespace_name
union all
select I.table_name,segment_type,I.index_name ,e.tablespace_name,count(*) NbrExtent,sum(bytes)/1024 KBytes
from sys.user_indexes I,sys.user_extents e
where i.index_name=e.segment_name and e.segment_type='INDEX'
group by I.table_name,I.index_name ,segment_type,e.tablespace_name
union all
select l.TABLE_NAME,segment_type,l.COLUMN_NAME,e.tablespace_name,count(*) NbrExtent,sum(bytes)/1024 KBytes
from sys.user_lobs L,sys.user_extents e
where L.SEGMENT_NAME=e.segment_name and e.segment_type='LOBSEGMENT'
group by l.TABLE_NAME,segment_type,l.COLUMN_NAME,e.tablespace_name
union all
select l.TABLE_NAME,segment_type,l.COLUMN_NAME,e.tablespace_name,count(*) NbrExtent,sum(bytes)/1024 KBytes
from sys.user_lobs L,sys.user_extents e
where l.INDEX_NAME=e.segment_name and e.segment_type='LOBINDEX'
group by l.TABLE_NAME,segment_type,l.COLUMN_NAME,e.tablespace_name
order by 1
