Oracle Spatial MDRT tables
According to the Oracle Spatial MDRT_<>$ tables (Doc ID 2105798.1), The MDRT_<>$ tables and their related MDRS_<>$ sequences store data for spatial indexes in the database. Here are some key points to keep in mind:
Don’t Move These Tables: If you move the MDRT_<>$ tables to a different tablespace, the spatial index linked to them will stop working, and all spatial operations on the associated table will fail. To set the correct tablespace for these tables, use the tablespace parameter when creating the spatial index.
Don’t Alter or Drop Them: Avoid modifying or deleting MDRT_<>$ tables or MDRS_<>$ sequences. You can only delete them if they are not linked to any spatial index. To find out which MDRT_<>$ tables are linked to spatial indexes, you can query the USER_SDO_INDEX_METADATA view using the following query.
Don’t Export These Tables Directly: When you import data, the MDRT_<>$ tables are automatically created as part of the spatial index setup, so you don’t need to export them separately.
Don’t Replicate These Tables: Avoid replicating MDRT_<>$ tables to a different database, as this can cause issues with the spatial indexes.
Can I drop unrelated ones? I will answer first: Yes, You can drop unrelated ones, if your Oracle DB version is 12cR2 or later.
But which ones?
There were over 3000 tables starting with ‘MD*’ letters in one of our production database. I knew that those tables are related with Spatial indexes. But that was a huge amount.
So I took a deep dive into the spatial indexes.
For each spatial index created, one table named like “MDRT_#” is created also. There is a one to one relationship (except the partitioned ones) between them.
There are also tables named like “MDXT_#, MDXT_#_BKTS, MDXT_#_MBR”. According to the Doc Id 1916251.1, these tables are created for supporting statistics on Spatial Index and after the statistic analyzing is finished, MDXT_#_BKTS and MDXT_#_MBR should be dropped automatically. But somehow, we had lots of them.
Also Doc Id 2029072.1 states that “Dropping these temporary tables ( MDXT_#_BKTS and MDXT_#_MBR ones) won’t cause any database corruption.
With 12.2.0.1.0, the MDXT tables, no longer exists. They are replaced with MDSYS.SDO_INDEX_HISTOGRAM_TABLE table.
So we can drop all tables starting with MDXT_# .
| SQL> SELECT 'DROP TABLE ' || T.OWNER || '.' || '"' || T.TABLE_NAME || '" PURGE;' | |
| AS DROP_MDXTS_COMMAND | |
| FROM DBA_TABLES T | |
| WHERE T.TABLE_NAME LIKE 'MDXT%'; |
Here is the query to detect which MDRT_# tables are required for integrity and the spatial_indexes they are related with. (Execute with system user.)
| SQL> SELECT SM.* | |
| FROM ALL_TABLES T, ALL_SDO_INDEX_METADATA SM | |
| WHERE T.TABLE_NAME LIKE 'MDRT%' AND T.TABLE_NAME = SM.SDO_INDEX_TABLE | |
| ORDER BY SDO_INDEX_OWNER; |
Also we can validate the above result with the query below. These tables are the required ones. (Execute with system user.)
| SQL> SELECT * | |
| FROM DBA_SECONDARY_OBJECTS | |
| WHERE SECONDARY_OBJECT_NAME LIKE 'MDRT%'; |
We can drop the orphaned MDRT_# tables with executing the query output.(Execute with system user.)
| SQL> SELECT 'DROP TABLE ' || T.OWNER || '.' || '"' || T.TABLE_NAME || '" PURGE;' | |
| AS DROP_ORPHANED_MDRTS_COMMAND | |
| FROM DBA_TABLES T | |
| WHERE T.TABLE_NAME LIKE 'MDRT%' | |
| AND T.TABLE_NAME NOT IN (SELECT SM.SDO_INDEX_TABLE | |
| FROM ALL_SDO_INDEX_METADATA SM); |
We had got rid of over 2500 tables.
Also if you ever need to change the tablespace of these “MDRT_#” tables. You can change them with the command below.
| SQL> ALTER INDEX OWNER.SDO_INDEX_NAME | |
| REBUILD PARAMETERS ('TABLESPACE=NEW_TBS'); |
Hope it helps.
Osman’s DBlog 
Leave your comment