(Cueing my deep baritone Morpheus voice...) What if I told you that you can reference non-existent tablespaces in your DDL?

OK, it sounds like a gimmick, but there is a real issue that I'll get to shortly. But first, the gimmick :)

I've created a partitioned table called "T" (I'll pause here for your applause at my incredible imagination skills for table naming :) ) and to show you the complete DDL, I'll extract it using the familiar DBMS_METADATA package.

SQL> select dbms_metadata.get_ddl('TABLE','T','SCOTT') x from dual

X
-------------------------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."T"
 (    "X" NUMBER(*,0)
 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE (  
  BUFFER_POOL DEFAULT 
  FLASH_CACHE DEFAULT 
  CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DEMO" PARTITION BY LIST ("X") (PARTITION "P1" VALUES (1) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE ( INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ,  PARTITION "P2" VALUES (2) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE ( INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "LARGETS" ) 

With a little color coding, you can see that there are three tablespaces that pertain to this table:

• DEMO
• USERS
• LARGETS

But look what happens when I query the data dictionary for those tablespaces:

SQL> select tablespace_name
  2  from   dba_tablespaces
  3  where  tablespace_name in ('DEMO','USERS','LARGETS');

TABLESPACE_NAME
------------------------
LARGETS
USERS

Where is DEMO? Where has it gone? More startlingly, how can I have an existing table that that tablespace, and yet the tablespace is present in the database? Have I lost data? Is there corruption?

Fortunately, the answer to all of these questions do not involve data loss and/or corruption. It is a quirk of the syntax that can be used for partitioned tables. Here is the DDL as I wrote it for the table T.

SQL> create table t ( x int ) tablespace demo
  2  partition by list ( x )
  3  ( partition p1 values (1) tablespace users,
  4    partition p2 values (2) tablespace largets
  5  );

Table created.

And immediately after I created the table, I did the following

SQL> drop tablespace demo including contents and datafiles;

Tablespace dropped.

You might be thinking that such an operation would surely drop the table I just created as well, but it is still here just fine.

SQL> desc t
 Name                                                              Null?    Type
 ----------------------------------------------------------------- -------- ---------------
 X                                                                          NUMBER(38)

The specification of the tablespace at table level for a partitioned table is nominating the tablespace for each partition in case it is not specified at the partition level. Because I specified a tablespace explicitly for the two partitions on T, the tablespace DEMO does not contain any data or any partitions for that matter. Which is why I was able to drop it without any problems. Compounding the confusion that often arises is that you won't see the tablespace name DEMO listed in the USER_TABLES dictionary view even if I had dropped the tablespace.

SQL> select tablespace_name from user_tables
  2  where table_name = 'T';

TABLESPACE_NAME
------------------------
(null)

The tablespace name in USER_TABLES nominates the tablespace for the segment that will be associated with this table. You will see a similar null value in this column when the table is an Index Organized Table, because it is the underlying that maps to a tablespace, not the definition. For a partitioned table, to see which tablespace is the default tablespace, you need to query the USER_PART_TABLES dictionary view.

SQL> select def_tablespace_name
  2  from user_part_tables
  3  where table_name = 'T';

DEF_TABLESPACE_NAME
------------------------------
DEMO

Besides this being some trickery with tablespace definitions, there is a good reason why you should know about the segments tablespace and the default tablespace for partitioned objects. As it stands, I could run a datapump export command on the table T and it will successfully be unloaded to a data pump file. However, if I attempt to run a data pump import, the creation of the table will fail, because of the (now illegal) reference to the DEMO tablespace.

So if you are planning to run a data pump export, here's a query I whipped up to run a check against your database to ensure that you don't have any references to tablespaces that no longer exist in your database.

SQL> with all_possible_ts as
  2  (
  3  select tablespace_name from dba_lobs                       union all
  4  select tablespace_name from dba_clusters                   union all
  5  select tablespace_name from dba_indexes                    union all
  6  select tablespace_name from dba_rollback_segs              union all
  7  select tablespace_name from dba_tables                     union all
  8  select tablespace_name from dba_object_tables              union all
  9  select def_tablespace_name from dba_part_tables            union all
 10  select def_tablespace_name from dba_part_indexes           union all
 11  select tablespace_name from dba_tab_partitions             union all
 12  select tablespace_name from dba_ind_partitions             union all
 13  select tablespace_name from dba_tab_subpartitions          union all
 14  select tablespace_name from dba_ind_subpartitions          union all
 15  select def_tablespace_name from dba_part_lobs              union all
 16  select tablespace_name from dba_lob_partitions             union all
 17  select tablespace_name from dba_lob_subpartitions          union all
 18  select tablespace_name from dba_subpartition_templates     union all
 19  select tablespace_name from dba_lob_templates              union all
 20  select tablespace_name from dba_segments                   union all
 21  select tablespace_name from dba_extents                    union all
 22  select tablespace_name from dba_undo_extents
 23  )
 24  select tablespace_name from all_possible_ts
 25  minus
 26  select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
--------------------
DEMO