Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Wednesday, October 3, 2007

Oracle database keeps the old dropped tablespace info in database registry for a partitoned table

Hi,

We have just upgraded our Oracle production database from 9i(9.2.0.4) to 10g(10.2.0.3).
I will prepare and publish a blog post about this subject in the following days.
While I struggle with the CBO subjects that are changed in 10g. I realised
something that I was aware of before but I am very shocked that it is not corrected in 10g.

The case is related to a partitioned table which is originally created together with its partitions in
"tablespace_1" but for some time later, as a reorganization need, its partitions are moved online to
"tablespace_2" and old "tablespace_1" is dropped from the database.

I know from my earlier experiences that in 9i, in a case like above, Oracle 9i keeps the old dropped
tablespace "tablespace_1" info in database registry as a part of the partitioned table's description.

Lets give an example...

conn dbuser01@prod10g

select username, default_tablespace from dba_users where username = 'DBUSER01';
/*
username|default_tablespace
DBUSER01|USER_DATA
*/

CREATE TABLESPACE USER_DATA_TEST DATAFILE 
'/data2/oracle3/oradata/oradb3/user_data_test.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 100M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

ALTER USER DBUSER01 DEFAULT TABLESPACE "USER_DATA_TEST";

select username, default_tablespace from dba_users where username = 'DBUSER01';
/*
username|default_tablespace
DBUSER01|USER_DATA_TEST
*/

--DROP TABLE DBUSER01.EGTEST1;

CREATE TABLE DBUSER01.EGTEST1
(
COL1           CHAR(21 BYTE),
COL2           CHAR(1 BYTE),
COL3           DATE
)
PARTITION BY RANGE (COL3) 
(  
PARTITION P1 VALUES LESS THAN (TO_DATE(' 2003-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE TB_DATA
,  
PARTITION P2 VALUES LESS THAN (MAXVALUE)
TABLESPACE TB_DATA
);

ALTER USER DBUSER01 DEFAULT TABLESPACE "USER_DATA";

select username, default_tablespace from dba_users where username = 'DBUSER01';
/*
username|default_tablespace
DBUSER01|USER_DATA
*/

drop tablespace USER_DATA_TEST INCLUDING CONTENTS AND DATAFILES; 

try to export this table
user01@test3:/data1/oracle3>exp dbuser01 file=egtest1 log=egtest1 tables=egtest1 rows=n

Export: Release 10.2.0.3.0 - Production on Thu Sep 27 11:21:45 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P9 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export specified tables via Conventional Path ...
. . exporting table                 EGTEST1
Export terminated successfully without warnings.

DROP TABLE DBUSER01.EGTEST1;

try to import this table
user01@test3:/data1/oracle3>imp dbuser01 file=egtest1 log=egtest1_imp fromuser=dbuser01 touser=dbuser01

Import: Release 10.2.0.3.0 - Production on Thu Sep 27 11:24:17 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Password:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8ISO8859P9 character set and AL16UTF16 NCHAR character set
. importing DBUSER01's objects into DBUSER01
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "EGTEST1" ("COL1" CHAR(21), "COL2" CHAR(1), "COL3" DATE)"  
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "USER_DATA_TEST" LOGGING P"
"ARTITION BY RANGE ("COL3" )  (PARTITION "P1" VALUES LESS THAN (TO_DATE('"
" 2003-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))"
"  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREEL"
"ISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TB_DATA" LOGGING "
"NOCOMPRESS, PARTITION "P2" VALUES LESS THAN (MAXVALUE)  PCTFREE 10 PCTUSED "
"40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUP"
"S 1 BUFFER_POOL DEFAULT) TABLESPACE "TB_DATA" LOGGING NOCOMPRESS )"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'USER_DATA_TEST' does not exist
Import terminated successfully with warnings.

import can not find the already dropped tablespace and can not import the table

---------------------------
testing the normal behaviour with a non-partitioned table.

CREATE TABLESPACE USER_DATA_TEST DATAFILE 
'/data2/oracle3/oradata/oradb3/user_data_test.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 100M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

create table test_tbl (col1 number) tablespace USER_DATA_TEST;
insert into test_tbl values (1);
commit;

select table_name,tablespace_name from user_tables where table_name = 'TEST_TBL';
/*
table_name|tablespace_name
TEST_TBL|USER_DATA_TEST
*/
drop tablespace USER_DATA_TEST INCLUDING CONTENTS AND DATAFILES; 
select table_name,tablespace_name from user_tables where table_name = 'TEST_TBL';
--No rows returned.
---------------------------

In normal database behaviour when we drop the tablespace, the tables, that resides in the dropped tablespace,
dropped also. Why the database behaves differently in case of parititioned tables ?

You can see this oddity when you try to extract the DDL script of such a parititoned table with any database tool,
because the Oracle database continues to keep this already dropped tablespace info in the database registry.

Oracle Advanced Resolution Team of Oracle Worldwide Support considered the issue not as a bug and
they accept it only as an enhancement request which is done in expdp/impdp (Oracle Data Pump Export/Import)
on 10G. When you try to import (impdp) a partitioned table like in the above case, which is exported with expdp,
you can use "remap_tablespace=user_data_test:an_existing_tblspace" parameter to change the old dropped
tablespace name to a tablespace that exists in the database.

So, in our case, since the related partitioned tables are very big and the database operates in archivelog mode
we do not want to export them with expdp and import them again into database with impdp by using the
"remap_tablespace" parameter. Nevermind our case, but the strange thing is that , Oracle still have this
oddity even in 10g database. I mean if you do not export and then import them, then you have to live with it.
Any tool (including expdp) extracts the DDL of such a partitioned table with wrong old tablespace,
then you will have to manually edit this generated script (except impdp) to change this non-existing
tablespace from the DDL script.

One more point, since the impdb can import only the export dump files that are taken with expdp, if you
just upgraded your database from 9i to 10g, I thing you should convert your export scripts to use the expdp,
if any. Otherwise, if, from time-to-time, you are importing some tables into any of your test databases,
there will be some manual extra works waiting for you, like for these partitioned tables containing old dropped
tablespace info you will have to extract the script from export file, change the tablespace part to point to an
existing tablespace,pre-create the tables and then import them.


Regards
Ural Ural