This one turned out to be a an interesting bug the other day…
I did a simple select from DBA_TAB_PARTITIONS and noticed that some tablespace_names were of the form “_$deleted$n$m” where n and m are numbers. Slightly worrying, but at least the data was all present and correct, when I checked. I knew the DBA team had been doing some reorganisations the previous weekend, to recover some space, so I wondered if that was connected….it was, and after opening an SR, the DBA, Phil, found an explanation (from Oracle Note: 604648.1) and a resolution.
Reproducing the issue and the way to fix it, is simple, using this script…
DROP TABLESPACE old_tbs INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE new_tbs
DATAFILE 'C:APPORACLEORADATAT111NEW_TBS.DBF'
SIZE 100M
ONLINE;
CREATE TABLESPACE old_tbs
DATAFILE 'C:APPORACLEORADATAT111OLD_TBS.DBF'
SIZE 100M
ONLINE;
SELECT ts#,name FROM sys.ts$ WHERE name LIKE '%TBS';
CREATE TABLE jeff_test(col1 DATE NOT NULL
,col2 NUMBER NOT NULL
,col3 VARCHAR2(200) NOT NULL
)
TABLESPACE old_tbs
PARTITION BY RANGE(col1)
SUBPARTITION BY LIST(col2)
SUBPARTITION TEMPLATE(
SUBPARTITION "S1" VALUES(1)
,SUBPARTITION "S2" VALUES(2)
)
(PARTITION p1 VALUES LESS THAN(TO_DATE('31-DEC-2009','DD-MON-YYYY'))
,PARTITION p2 VALUES LESS THAN(TO_DATE('31-DEC-2010','DD-MON-YYYY'))
)
/
SELECT partition_name,tablespace_name FROM dba_tab_partitions WHERE table_name='JEFF_TEST';
SELECT subpartition_name,tablespace_name FROM dba_tab_subpartitions WHERE table_name='JEFF_TEST';
ALTER TABLE jeff_test MOVE SUBPARTITION p1_s1 TABLESPACE NEW_TBS;
ALTER TABLE jeff_test MOVE SUBPARTITION p1_s2 TABLESPACE NEW_TBS;
ALTER TABLE jeff_test MOVE SUBPARTITION p2_s1 TABLESPACE NEW_TBS;
ALTER TABLE jeff_test MOVE SUBPARTITION p2_s2 TABLESPACE NEW_TBS;
DROP TABLESPACE old_tbs INCLUDING CONTENTS AND DATAFILES;
ALTER TABLESPACE new_tbs RENAME TO old_tbs;
SELECT partition_name,tablespace_name FROM dba_tab_partitions WHERE table_name='JEFF_TEST';
SELECT subpartition_name,tablespace_name FROM dba_tab_subpartitions WHERE table_name='JEFF_TEST';
ALTER TABLE jeff_test MODIFY DEFAULT ATTRIBUTES FOR PARTITION p1 TABLESPACE old_tbs;
ALTER TABLE jeff_test MODIFY DEFAULT ATTRIBUTES FOR PARTITION p2 TABLESPACE old_tbs;
SELECT partition_name,tablespace_name FROM dba_tab_partitions WHERE table_name='JEFF_TEST';
SELECT subpartition_name,tablespace_name FROM dba_tab_subpartitions WHERE table_name='JEFF_TEST';
Which, when run in 11.1.0.6 on Windows 2003 Server 64 bit, gives:
DROP TABLESPACE old_tbs succeeded.
CREATE TABLESPACE succeeded.
CREATE TABLESPACE succeeded.
TS# NAME
---------------------- ------------------------------
9 NEW_TBS
10 OLD_TBS
2 rows selected
CREATE TABLE succeeded.
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P1 OLD_TBS
P2 OLD_TBS
2 rows selected
SUBPARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P1_S2 OLD_TBS
P1_S1 OLD_TBS
P2_S2 OLD_TBS
P2_S1 OLD_TBS
4 rows selected
ALTER TABLE jeff_test succeeded.
ALTER TABLE jeff_test succeeded.
ALTER TABLE jeff_test succeeded.
ALTER TABLE jeff_test succeeded.
DROP TABLESPACE old_tbs succeeded.
ALTER TABLESPACE new_tbs succeeded.
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P1 _$deleted$10$0
P2 _$deleted$10$0
2 rows selected
SUBPARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P1_S2 OLD_TBS
P1_S1 OLD_TBS
P2_S2 OLD_TBS
P2_S1 OLD_TBS
4 rows selected
ALTER TABLE jeff_test succeeded.
ALTER TABLE jeff_test succeeded.
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P1 OLD_TBS
P2 OLD_TBS
2 rows selected
SUBPARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P1_S2 OLD_TBS
P1_S1 OLD_TBS
P2_S2 OLD_TBS
P2_S1 OLD_TBS
4 rows selected
Notice that the $n in “_$deleted$n$m” is 10, which is the ts# of the OLD_TBS before the rename. The problem revolves around entries in TS$, when you rename tablespaces to names that have previously been used and then dropped, basically because the old entries are not removed from TS$.
Related references:
Bug Numbers:8291493, itself a duplicate of 5769963
Note: 604648.1
According to the SR and bug, it was noticed in 10.2.0.4 and is fixed in 10.2.0.5. We’ve reproduced it in 11.1.0.6 on various ports, (results above) and updated our SR, so I guess the fix might also find it’s way into 11.1.0.7, perhaps.
