Version 12.1.0.2.190716
First of all: Yes I know 12.1 is out of support. An upgrade is planned but I'd like solve this "Mystery" before commencing upgrade..
A brief intro :
I recently did a bigger reorganization in one of our DW databases - basically what I did was to move lots of tables (both partitioned and not), indexes
and some lob segments from a big historical tablespace (5TB) (data_ts) to a new tablespace (data_ts_new) to enable BASIC compression for the tables.
I then renamed the tablespace "data_ts" to "data_ts_old" and "data_ts_new" to "data_ts".
After that I tried to drop the tablespace (data_ts_old) with "drop tablespace data_ts_old;"
but it errored out with : ORA-1549 signalled during: drop tablespace saying the tablespace wasn't empty.
So I checked the standard views for objects that I might have overseen..
- dba_tables
- dba_indexes
- dba_tab_partitions
- dba_ind_partitions
- dba_lob_partitions
- dba_lobs
- dba_segments
All returning 0 rows for the tablespace. I've also checked that the recyclebin is empty.
I then stumbled upon the following MOS note
"ORA-01549: tablespace not empty, use INCLUDING CONTENTS option" Reported Even When Tablespace Does Not Have Any Segments. ( Doc ID 1591998.1 )"
And yes, a query on sys.ind$ actually points to some objects. The other views mentioned in the note did not.
SQL>select obj# from sys.ind$ i where i.ts# = 9;
OBJ#
----------
772214
777391
779078
780305
781476
783908
836843
850111
909265
916612
918623
921472
969591
1039359
1129949
1131623
1133183
1165326
1170560
1173847
1178827
1180658
1182138
1183991
1186859
25 rows selected.
What I did next was to map these to dba_objects and got some hits.
Even though I could see that these partitions were in fact moved successfully to the new tablespace I tried to rebuild the index partition once again.
SQL>r
1 select owner, object_name, subobject_name, object_type, created, data_object_id from dba_objects
2 where data_object_id IN (
3* select obj# from sys.ind$ i where i.ts# = 9 )
OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE CREATED DATA_OBJECT_ID
------------------------------ ------------------------------ ------------------------------ ----------------------- --------- --------------
DATAOWNER PK_COLUMN P20191104 INDEX PARTITION 06-NOV-19 772214 <-- rebuild
DATAOWNER PK_COLUMN P20191105 INDEX PARTITION 07-NOV-19 777391
DATAOWNER PK_COLUMN P20191113 INDEX PARTITION 15-NOV-19 850111
DATAOWNER PK_COLUMN P20191106 INDEX PARTITION 08-NOV-19 779078
DATAOWNER PK_COLUMN P20191107 INDEX PARTITION 09-NOV-19 780305
DATAOWNER PK_COLUMN P20191108 INDEX PARTITION 10-NOV-19 781476
DATAOWNER PK_COLUMN P20191111 INDEX PARTITION 13-NOV-19 783908
DATAOWNER PK_COLUMN P20191112 INDEX PARTITION 14-NOV-19 836843
DATAOWNER PK_COLUMN P20191114 INDEX PARTITION 16-NOV-19 909265
DATAOWNER PK_COLUMN P20191115 INDEX PARTITION 19-NOV-19 916612
10 rows selected.
SQL>alter index DATAOWNER.PK_COLUMN rebuild partition P20191104 tablespace DATA_TS;
Index altered.
SQL>select obj# from sys.ind$ i where i.ts# = 9;
OBJ#
----------
772214 <-- still here
...
...
So I guess the data_object_id just changes after a move.
But what's puzzling is that sys.ind$ gets 1 new row each day.. and nothing should be using that tablespace now.
So do I have some sort of data dictionary corruption?
I ran hcheck script to verify ( hcheck.sql - Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c (Doc ID 136697.1))
It did found one problem, but I don't think that is related to what I see in sys.ind$ ?
HCKE-0003: SOURCE$ for OBJ# not in OBJ$ (Doc ID 1360233.1)
SOURCE$ has 5 rows for 1 OBJ# values not in OBJ$
So any ideas what's going on here?
Regards
AJ