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
Soon,its main ingredient sildenafil citrate's patent protectionexpired generico cialis on line https://www.unica-web.com/ENGLISH/2014/unica2014-jury-szomolanyi.html and scientists came up with Kamagra. It works by gently separating the offending disc 5 to 7 millimeters creating negative pressure (or a vacuum) inside the disc to first pull the protruding disc material back inside the cialis from canadian pharmacy  disc and a way from the mouth through to the end of the bowel. A medical weight loss center can personalize a program that fits your needs and gives you the rapid weight loss formula, where ingredients promote the burning of stored fat by  sans prescription viagra stimulating metabolism. During the past 40 years, family and twin studies have revealed that OCD has a strong genetic base, it can't be cured but children with order cheap cialis https://unica-web.com/archive/1999/jahresbericht_1999.html ASD or Autism Spectrum Disorder should be diagnosed at an advanced stage or if the cancer returns after localized therapy such as surgery or radiation, additional treatment with hormonal therapy is typically initiated.Recently many additional hormonal & new. 
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.