How To Determine Which MDXT_###$ Tables Are Needed?

Hi,

MDXT_#####$ tables are created when statistics are gathered on Spatial indexes. Due to some outstanding issues, some of these tables may not be needed and can be dropped if space is needed.

1) Some of these tables may be orphaned. You can determine which tables are valid with the following query:

column sdo_index_owner format a12
column sdo_index_table format a16
select sdo_index_owner, index_name, sdo_index_table from mdsys.all_sdo_index_info;

For the column SDO_INDEX_TABLE, MDRT_#####$ = MDXT_#####$,
so this should give you the index name also

select index_name from mdsys.all_sdo_index_info where sdo_index_table = ‘MDRT_A0140$’;

If an index does not exist then those tables can be dropped.

2) After statistics have been gathered, Spatial only needs access to the table with the $ as the last character, like
MDXT_A010B$
MDXT_A012E$
these are the ones that store the actual statistics

Tables like MDXT_A0140$_MBR and MDXT_A0140$_BKTS can be dropped as they were only needed during the statistics collection operation. These MDXT_%$_BKTS and MDXT_%$_MBR tables are temporary, and are not needed once the stats are collected. In 11.2 these temporary tables are going to need as much space as the data set while the statistics are being created. After the analysis is finished, these tables are normally dropped automatically. If for some reason these tables are still around after the analyzing is finished, then you can manually drop them.

Dropping these temporary tables won’t cause any database corruption.