Orphaned Tables after dropping a Table with SDO_GEOMETRY Column and statistics have been gathered (Doc ID 1610877.1)

Last updated on SEPTEMBER 28, 2015

Applies to:

Oracle Spatial and Graph - Version 11.2.0.4 and later
Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.1 [Release 12.1]
Information in this document applies to any platform.

Symptoms

'DBMS_STATS.gather_table_stats' on an empty table that contains an SDO_Geometry column creates orphaned tables such as MDXT_162DB$BKTS and MDXT_162DB$BKTS.
Dropping the table does not drop these MD* tables.

Issue reproduces in 11.2.0.4 and 12.1.0.1.

Steps to Reproduce the issue:
1)
  CREATE TABLE T55
  ( OBJECTID NUMBER(38,0) NOT NULL ENABLE,
AREA NUMBER(38,8),
STATE_NAME NVARCHAR2(25),
STATE_FIPS NVARCHAR2(2),
SUB_REGION NVARCHAR2(7),
STATE_ABBR NVARCHAR2(2),
POP1990 NUMBER(38,8),
POP1996 NUMBER(38,8),
SHAPE MDSYS.SDO_GEOMETRY ,
SE_ANNO_CAD_DATA BLOB,
SDE_STATE_ID NUMBER(38,0) NOT NULL ENABLE,
CONSTRAINT T55_PK PRIMARY KEY (OBJECTID, SDE_STATE_ID)
  ) ;

2)insert into user_sdo_geom_metadata
values('T55','SHAPE',SDO_DIM_ARRAY(SDO_DIM_ELEMENT(NULL, -178.215, -66.97,
.0000005), SDO_DIM_ELEMENT(NULL, 18.9247999, 71.4066467, .0000005)),NULL);

3) CREATE INDEX T24_IX1_A ON T55 (SHAPE) INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS ('SDO_COMMIT_INTERVAL = 1000');

4) BEGIN DBMS_STATS.gather_table_stats ('MAP','T55'); END;

5) drop table T55;


 Orphaned tables such as MDXT_xxx$_BKTS and MDXT_xxx$_MBR still exist, which do not get dropped upon dropping the original table T55.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms