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. 

Spatial unnecessary tables can be dropped

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. 

Drop bizarre table: starting with MD*.

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_# .

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


Also we can validate the above result with the query below. These tables are the required ones.  (Execute with system user.)


We can drop the orphaned MDRT_# tables with executing the query output. (Execute with system user.)


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.


Hope it helps.

Comments

Post a Comment

Popular posts from this blog

Oracle Grid Release Update by using Ansible Playbooks

-
Image
Ansible is a friend in need and a friend indeed for DBAs. 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. In this blog post, I will mention about Redhat Ansible and show how you can patch your grid infrastructure by using it. Ansible is an open source, command-line IT automation software application written in Python. It can configure systems, deploy software, and orchestrate advanced workflows to support application deployment, system updates, and more. Ansible's main strengths are simplicity and ease of use. It is a little bit similar to dcli commands in Exadata environments, but much more powerful as you may run playbooks which consist of different tasks. Ansible stands out from other configuration management tools because it doesn't need agents installed on target host...
Read more

Oracle Database Release Update by Using Ansible Playbooks

-
Image
Ansible to Rule Them All 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. In the previous post " Oracle Grid Software Patching by using Ansible Playbooks" , I mentioned how Ansible orchestration can be used to make Oracle Grid Software Patching a cleaner and simpler process. Now I will also patch database software using Ansible Playbooks. In this post,  I will use the blt01 host as the ansible host. The target environment is a two-node RAC database (gns01, gns02). I will patch the grid software to 19.22 using the out-of-place methodology with no downtime.  Although there are lots of useful blog posts about how you may use Ansible for automating tasks, there is not much about complete Oracle database software patching.  Ansible is simply installed by running "y...
Read more

How to Upgrade PostgreSQL, PostGIS and Patroni in Air-Gapped Environments

-
Image
Upgrade Your PostgreSQL Database: Major Steps and Minor Touches 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. In this blog post, I will show how a complete PostgreSQL environment major update can be done. Minor upgrades are not a tough task; they are no different from a standard Linux package upgrade. As my production environment serves in an airgapped-environment, I will complete all the major upgrades with no internet connection. I've downloaded all the required whl and .rpm packages before the upgrade. Before starting, a short description of major and minor PostgreSQL upgrades is provided below: Major Upgrade: Upgrading PostgreSQL to a new major version, such as moving from 10 to 11 or 12 to 13. Major upgrades bring significa...
Read more