Bizarre tables: starting with MD* . Let's drop some.

What are these strange tables starting with MD*? 

I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there.

Can I drop them?  I will answer first: 

    Yes, You can drop some of them 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 exist. 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%';

view raw drop_mdxt_tables.sql hosted with ❤ by GitHub

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;

view raw required_mdrt_tables.sql hosted with ❤ by GitHub

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%';

view raw secondary_objs_starts_with_mdrt.sql hosted with ❤ by GitHub

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);

view raw drop_mdrt_tables.sql hosted with ❤ by GitHub

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');

view raw change_tablespace_of_mdrt_table.sql hosted with ❤ by GitHub

Hope it helps.