Posted by (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 colour 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 needs that tablespace, and yet the tablespace is not 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 default tablespace for each partition in case it is not specified at 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 not 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 index that maps to a tablespace, not the table 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
Learning is not a spectator sport 
Connor – you have not changed at all – still the Ultimate Oracle geek!! Hope you are all well in Oz land.
Pauline (Liverpool)
Well…there’s a blast from the past 🙂
Funny thing, I’d recently encountered the same issue and also called it “phantom tablespaces”.
Either we’re of a similar vintage, or I’m reading your blogs and tweets way too much!!