I have just noted that today, one of the datafiles that was yesterday, 484 Meg full - is today 250 Meg full.
Each datafile relates directly to a tablespace for simplicity so.
Can someone help me discover every object that exists in a tablespace please, tables - indexes and anything else.
I assume I need a query of some sort.
Hey I had a thought.
Yesterday, I rebuilt all indexes, coalesced all tablespaces, analyzed everything. Also coalesced the indexes after the rebuild.
This could possibly have affected my DB. :D.
Do you think its possible I retrieved like 200 Meg from a 500 Meg file.
If you were only to identify what object type
in a certain tablespace then:
select distinct segment_type
The consumed size of your tablespace becomes lower
because you issues commands to optimize your objects
resulting your tablespace to consume less size where in
your object resides.
you can do :
select segment_name, segment_type
where tablespace_name = '...';
select object_name, object_type
where sysdate-created <2;
first one will give you all the segments in the tablespace you enter, and second one will give you the objects that were created less than 2 days ago, that way you can find out what filled your tablespace.
DBA_SEGMENTS does not show all objects, like LOB Indexes.
Use this quote:
select 'SEGMENTS' tipo, owner, segment_name, TABLESPACE_NAME from dba_segments where TABLESPACE_NAME <> 'TSD_DBAMLAT' and owner in ('CAP_WATTS','DBAMLAT')
select 'TABLES' tipo, owner, table_name, TABLESPACE_NAME from dba_tables where tablespace_name <> 'TSD_DBAMLAT' and owner in ('CAP_WATTS','DBAMLAT')
select 'INDEXES' tipo, owner, index_name, TABLESPACE_NAME from dba_indexes where tablespace_name <> 'TSD_DBAMLAT' and owner in ('CAP_WATTS','DBAMLAT')