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?
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.
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?
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...).
March 28, 2019 - 11:28 am UTC
Thanks for sharing your experiences.