Skip to Main Content
  • Questions

  • what are tables with MDXT in the name and can I delete them?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Dennis.

Asked: January 07, 2019 - 2:32 pm UTC

Last updated: March 28, 2019 - 11:28 am UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Hi,

I have a datawarehouse with 5000 tables in it. 

select count(*) from tab
where TABTYPE = 'TABLE'


In this datawarehouse there are also tables with MDXT or MDRT or BIN in the tablename, more then 4600!! 400 actually use 400 tables.

What are these for tables (MDXT,BIN) and what can I do with these tables? Can I delete them or clean them up? and what is the best way?


best regards



and Chris said...

Assuming you've not created these yourself, then:

MDXT

The optimizer creates these tables when gathering stats on spatial objects:

When you run ANALYZE INDEX on a spatial domain index for a different schema (user), the user performing the ANALYZE operation needs the following privileges:

CREATE ANY TABLE to create missing temporary tables

DROP ANY TABLE to truncate or remove existing temporary tables

If the statistics are successfully collected, a table with the name in the form MDXT_%objID% will be created for each index for which the statistics are collected. Each such table will be populated with spatial statistics data, 512 rows by default.

So if you're using spatial, I would consider it unsafe to remove these.

https://docs.oracle.com/en/database/oracle/oracle-database/18/spatl/indexing-querying-spatial-data.html#GUID-F18EDF46-B0E2-437D-AB05-74941EC3608C

BIN

These are likely dropped tables in the recyclebin. You can use this to "undrop" a table. Read more about this at https://blogs.oracle.com/sql/how-to-recover-data-without-a-backup#Restore-Dropped-Tables

If you're sure you don't want to recover these, you can purge the recyclebin:

create table t (
  c1 int
);
insert into t values ( 1 );
commit;

drop table t;

select object_name from recyclebin;

OBJECT_NAME                      
BIN$fuKMAWqyE3XgU50DxAqccA==$0   

purge recyclebin;

select object_name from recyclebin;

no rows selected


The database will self-manage this though. So I wouldn't be overly concerned about these.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

thanx for the answer but...

Dennis Doeze Jager, January 08, 2019 - 10:02 am UTC

Hi,
thanx for the answer.

I'm using spatial, that's correct.
But is it possible that 500 tables generates more then 4000 MDXT tables?
There are also tables like MDXT_20D920$_BKTS or MDXT_20D920$_MBR (BKTS/MBR). Are these tables also ment for indexing?
Chris Saxon
January 08, 2019 - 12:04 pm UTC

I'm not familiar enough with Spatial. I've reached out to the PM team for more info on this.

Stay tuned.

More details on MDXT tables

Hans Viehmann, January 08, 2019 - 9:28 pm UTC

Prior to 12cR2 the MDXT tables were created when statistics were collected on spatial indexes. There were two MDXT base tables: MDXT_###### and MDXT_######_MBR, both "owned" by the spatial index user. The MDXT_###### table is created for non-partitioned spatial indexes, and the MDXT_###### and MDXT_######_MBR tables are created for partitioned spatial indexes. Further, for disk-based statistics collection, a MDXT_######_BKTS table is created. If these tables are removed, it doesn't hurt; they are rebuilt automatically.

Starting with 12.2.0.1.0, the MDXT tables were replaced by a common SDO_INDEX_HISTOGRAM_TABLE table in the MDSYS schema (and corresponding USER_SDO_INDEX_HISTOGRAM and ALL_SDO_INDEX_HISTOGRAM views). This has made index table management a lot simpler. If you upgrade to 12.2 or above there shouldn't be any latent MDXT tables.
Chris Saxon
January 09, 2019 - 2:08 pm UTC

Thanks Hans.

Deleting MDXT-tables can't hurt

Dennis Doeze Jager, January 09, 2019 - 8:22 am UTC

Thanx a lot Hans for your answer.
So if I delete MDXT tables Oracle is building these tables automatically?
Does this also apply to the removal of MDRT tables?


Chris Saxon
January 09, 2019 - 2:09 pm UTC

Yes, the database will recreate these tables if you drop them.

Caution - don't drop all the MDXT tables...

Samo, March 27, 2019 - 1:11 pm UTC

I was asking myself the same question few years ago when I started with Spatial. I would like to point out two documents that I find helpful.

MDXT_%$_BKTS and MDXT_%$_MBR Objects Taking Huge Amount of Space in the Schema, can these be Dropped? (Doc ID 2029072.1)
How To Determine Which MDXT_#####$ Tables Are Needed? (Doc ID 1916251.1)

It turns out that MDXT_%$_BKTS and MDXT_%$_MBR are temporary for the duration of statistics gathering and are safe to drop. Database usually drops them after statistics are collected but sometimes those tables remain orphaned (e.g. statistics collection fails due to insufficient space in TEMP tablespace).

However, I would not advise dropping all of your MDXT tables. Some of them may me orphaned, but some of them are actually needed. Orphaned ones are safe to drop. To find orphaned ones - you have a query that returns them in the second document.

I have "Spatial metadata mainenance" jobs implemented on my database that, among other things, drop only orphaned MDRS_%$, MDRT_%$, MDXT_%$ tables. No problems so far (knocking on wood...).
Chris Saxon
March 28, 2019 - 11:28 am UTC

Thanks for sharing your experiences.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.