(wrong string) – why tablespace_name is null in user_tables

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 14 Apr 2010 21:40:28 +0200
Message-ID: <4bc61a2d$0$20652$426a34cc_at_news.free.fr>

"zigzagdna" <zigzagdna_at_yahoo.com> a écrit dans le message de news: 386091ec-092a-4714-a0c6-c203d309278c_at_r27g2000yqn.googlegroups.com... I created a partitioned table:
CREATE TABLE test

   (

        SITEID VARCHAR2(8) NOT NULL ENABLE    )
   tablespace MAXIMO
partition by LIST(siteid)
(

   partition west_coast values ('CA1'),

   partition ocd values ('OCDRA'),
   partition jom values ('JOM'),
   partition rar values ('PSGARA'),

   partition site_null values (NULL),
   partition site_unknown values (DEFAULT) )
;
But when I go to user_tables,
Select table_name, tablespace_name from user_tables where table = ‘TEST’;
I get table_name but tablespace_name is NULL. I know table is made of partitions and tablespace_name can be different for each partition,
But is there anyway to populate tablespace_name for table TEST.

I have a third party application, I want to do spartitiong on some large tables but somehow bendor’s code is looking at user_tables and stops working because tablespace_name is NULL. I was under the impression that under the covers one can partition table (just like adding indexes) and
Third party application will remain working without problem. Any ideas will be appreciated.

---

Tablespace is meaningless for a partitioned table as the table is not a segment but a set of segments and these partitions/segments have their tablespace.
You can find the default tablespace (the one you give in CREATE TABLE statement) in %_part_tables views, DEF_TABLESPACE_NAME column.

For your application, create a view named user_tables in its schema that makes the join between user_tables and user_part_tables to fill the tablespace name.

Regards
Michel Received on Wed Apr 14 2010 - 14:40:28 CDT