Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

unable to drop tablespace

AJDec 10 2019 — edited Dec 11 2019

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

Comments
Post Details
Added on Dec 10 2019
8 comments
4,152 views