Stale default tablespace for oracle partitioned tables


Great friends for free… uuhh?

Oracle partitioned tables offer a variety of options to actually specify where to store the data, that is, the table-, index- and lob-partition-segments. However, iff you want to keep things simple and generic, you may leave all this tablespace spec stuff alone, just relying on the fact that the schema/user default tablespace will always also serve as the default tablespace for generated (interval partitioning) or added (range partitioning) or whatever partitions. Behaving in such a way becomes more and more attractive these days where spreading load- and failover can be done transparantly at other storage layers like asm or san.

That said, still there are potholes to knock over. As I was pleased to learn now, Oracle does not seem to store tablespace names with table (and partition) definitions but tablespace id’s. I do not know of any arcane dictionary view to prove that, but being trapped i digged down into the trap logic until I was shure about the meaning of the question I was asking.

What one might see, knock knock, in a 11g2 installation, may look like so (notice the empty cause and action sections):

--SQL-Fehler: ORA-00959: tablespace '_$deleted$37$0' does not exist
--00959. 00000 -  "tablespace '%s' does not exist"
--*Cause:    
--*Action:

The series of steps to reproduce the problem may read as follows, for interval partitioning first, where it hit me originally. The steps also include the necessary statement to get the (automatic) interval partitioning going again after swapping a partitioned table between tablespaces, which is the actual answer to my question…:

  • Create the test tbs’s and user
-- SYSTEM
--drop tablespace test1 including contents; -- and datafiles;
--drop tablespace test2 including contents; -- and datafiles;
create tablespace test1 datafile 'g:\test1_01.DBF' size 64m reuse
  autoextend on next 64m maxsize 64m extent management local
  segment space management auto;
create tablespace test2 datafile 'g:\test2_01.DBF' size 64m reuse
  autoextend on next 64m maxsize 64m extent management local
  segment space management auto;
--
create user test12 identified by test12 default tablespace test1;
grant create session to test12;
grant create table to test12;
grant unlimited tablespace to test12;
-- SYSTEM
  • Create the (automatic) interval partitioning table and have some data and analysis
create table ntrvl_log
( log_id number not null, log_utc_ts timestamp(6) not null )
  partition by range (log_utc_ts) interval (numtodsinterval(1, 'DAY'))
  --store in (test1)
  (partition init_p00 values less than (to_date('01-01-1970', 'dd-mm-yyyy')));
alter table ntrvl_log add (constraint ntrvl_log_pk primary key (log_utc_ts, log_id)
  not deferrable using index local );
--
insert into ntrvl_log (log_id, log_utc_ts)
  values (1, sys_extract_utc(localtimestamp)+1);
insert into ntrvl_log (log_id, log_utc_ts)
  values (2, sys_extract_utc(localtimestamp)+2);
commit;
--
select * from ntrvl_log order by log_id desc;
2	31.10.15 15:47:42,000000000
1	30.10.15 15:47:41,000000000
--
select tablespace_name from user_tables where table_name = upper('ntrvl_log');
*no rows*
--
select partition_name, tablespace_name from user_tab_partitions
  where table_name = upper('ntrvl_log');
INIT_P00	TEST1
SYS_P2421	TEST1
SYS_P2422	TEST1
--
select segment_name, partition_name, segment_type, tablespace_name from user_segments
  where tablespace_name like 'TEST%';
NTRVL_LOG	SYS_P2422	TABLE PARTITION		TEST1
NTRVL_LOG	INIT_P00	TABLE PARTITION		TEST1
NTRVL_LOG	SYS_P2421	TABLE PARTITION		TEST1
NTRVL_LOG_PK	SYS_P2422	INDEX PARTITION		TEST1
NTRVL_LOG_PK	INIT_P00	INDEX PARTITION		TEST1
NTRVL_LOG_PK	SYS_P2421	INDEX PARTITION		TEST1
  • Swap all the table and index segments over to tbs test2
alter table ntrvl_log move partition init_p00 tablespace test2;
alter table ntrvl_log move partition sys_p2421 tablespace test2;
alter table ntrvl_log move partition sys_p2422 tablespace test2;
alter index NTRVL_LOG_PK rebuild partition INIT_P00 tablespace TEST2 online;
alter index ntrvl_log_pk rebuild partition SYS_P2421 tablespace test2 online;
alter index ntrvl_log_pk rebuild partition SYS_P2422 tablespace test2 online;
  • Discard test1 and have test2 be the new test1
-- SYSTEM
alter user test12 default tablespace test2;
drop tablespace test1 including contents and datafiles;
alter tablespace test2 rename to test1;
alter user test12 default tablespace test1;
-- SYSTEM
  • Trigger the creation of another new partition by a top ts insert, will raise the error
insert into ntrvl_log (log_id, log_utc_ts)
  values (3, sys_extract_utc(localtimestamp)+3);
commit;
SQL-Fehler: ORA-00959: Tablespace '_$deleted$40$0' nicht vorhanden
00959. 00000 -  "tablespace '%s' does not exist"
*Cause:    
*Action:
  • The fix actually, the original default tablespace needs to be reset, then it works again
alter table ntrvl_log set store in (test1);
--
insert into ntrvl_log (log_id, log_utc_ts)
  values (3, sys_extract_utc(localtimestamp)+3);
commit;
select * from ntrvl_log order by log_id desc;
3	01.11.15 16:17:40,000000000
2	31.10.15 15:47:42,000000000
1	30.10.15 15:47:41,000000000

That is it. Do regard that I also tried giving store in (test1) at table creation, see above, but this will not help any further.

At this point, I was curious to see whether the problem only affects (magic) interval partitioning or even (simple) range partition. To come to the point, range partition suffers from the same issue. The link to the tablespace name is stale after the rename, no way in or out. What follows below, only shows the adapted test script uncommented, no need to echo the stuff from above.

-- SYSTEM
drop tablespace test1 including contents; -- and datafiles;
drop tablespace test2 including contents; -- and datafiles;

create tablespace test1 datafile 'g:\test1_01.DBF' size 64m reuse
  autoextend on next 64m maxsize 64m extent management local
  segment space management auto;
create tablespace test2 datafile 'g:\test2_01.DBF' size 64m reuse
  autoextend on next 64m maxsize 64m extent management local segment
  space management auto;
-- TEST12
purge recyclebin;
--
create table ntrvl_log
( log_id number not null, log_utc_ts timestamp(6) not null )
  partition by range (log_id)
  (partition init_p01 values less than (2),
    partition init_p02 values less than (3));
alter table ntrvl_log add (constraint ntrvl_log_pk primary key (log_id)
  not deferrable using index local );
--
insert into ntrvl_log (log_id, log_utc_ts)
  values (1, sys_extract_utc(localtimestamp)+1);
insert into ntrvl_log (log_id, log_utc_ts)
  values (2, sys_extract_utc(localtimestamp)+2);
commit;
-- works without a tbs given
alter table ntrvl_log add partition init_p03 values less than (4);
--
select * from ntrvl_log order by log_id desc;
2	31.10.15 16:34:00,000000000
1	30.10.15 16:33:59,000000000--
--
select tablespace_name from user_tables where table_name = upper('ntrvl_log');
*no rows*
--
select partition_name, tablespace_name from user_tab_partitions
  where table_name = upper('ntrvl_log');
INIT_P01	TEST1
INIT_P02	TEST1
INIT_P03	TEST1
--
select segment_name, partition_name, segment_type, tablespace_name from user_segments
  where tablespace_name like 'TEST%';
NTRVL_LOG	INIT_P01	TABLE PARTITION		TEST1
NTRVL_LOG	INIT_P02	TABLE PARTITION		TEST1
NTRVL_LOG	INIT_P03	TABLE PARTITION		TEST1
NTRVL_LOG_PK	INIT_P01	INDEX PARTITION		TEST1
NTRVL_LOG_PK	INIT_P02	INDEX PARTITION		TEST1
NTRVL_LOG_PK	INIT_P03	INDEX PARTITION		TEST1
--
alter table ntrvl_log move partition init_p01 tablespace test2;
alter table ntrvl_log move partition init_p02 tablespace test2;
alter table ntrvl_log move partition init_p03 tablespace test2;
alter index NTRVL_LOG_PK rebuild partition INIT_P01 tablespace TEST2 online;
alter index ntrvl_log_pk rebuild partition init_p02 tablespace test2 online;
alter index ntrvl_log_pk rebuild partition init_p03 tablespace test2 online;
-- SYSTEM
alter user test12 default tablespace test2;
drop tablespace test1 including contents and datafiles;
alter tablespace test2 rename to test1;
alter user test12 default tablespace test1;
-- TEST12
alter table ntrvl_log add partition init_p04 values less than (5);
SQL-Fehler: ORA-00959: Tablespace '_$deleted$41$0' nicht vorhanden
00959. 00000 -  "tablespace '%s' does not exist"
*Cause:    
*Action:

The same issue, obviously. But what is available now to help us out aside from rebuilding the table altogether? Honestly, it took me a while to find out about store in (tbs, ...) for interval partitioning not only being meaningful at table create time for spreading the data all over the given tablespaces in round-robin but also being the answer just to my current question.

Not an adage really, sort of, an abbreviation actually but true on and on, rtfm. I did again, and here it is:

alter table ntrvl_log modify default attributes tablespace test1;
--
alter table ntrvl_log add partition init_p04 values less than (5) tablespace test1;
--
select segment_name, partition_name, segment_type, tablespace_name from user_segments
  where tablespace_name like 'TEST%';
NTRVL_LOG	INIT_P01	TABLE PARTITION		TEST1
NTRVL_LOG	INIT_P02	TABLE PARTITION		TEST1
NTRVL_LOG	INIT_P03	TABLE PARTITION		TEST1
NTRVL_LOG	INIT_P04	TABLE PARTITION		TEST1
NTRVL_LOG_PK	INIT_P01	INDEX PARTITION		TEST1
NTRVL_LOG_PK	INIT_P02	INDEX PARTITION		TEST1
NTRVL_LOG_PK	INIT_P03	INDEX PARTITION		TEST1
NTRVL_LOG_PK	INIT_P04	INDEX PARTITION		TEST1

Enjoy! And have your ie started only once, just to set the cookie policy to ask and find out what friends are around 😉

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s