Re: Partitioned table question – why tablespace_name is null in user_tables
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Fri, 16 Apr 2010 12:40:31 -0700 (PDT)
Message-ID: <e98ac389-5990-4666-9602-5b39eecef6a4_at_8g2000yqz.googlegroups.com>
On Apr 14, 3:40 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "zigzagdna" <zigzag..._at_yahoo.com> a écrit dans le message de news:
> 386091ec-092a-4714-a0c6-c203d3092..._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
Date: Fri, 16 Apr 2010 12:40:31 -0700 (PDT)
Message-ID: <e98ac389-5990-4666-9602-5b39eecef6a4_at_8g2000yqz.googlegroups.com>
On Apr 14, 3:40 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "zigzagdna" <zigzag..._at_yahoo.com> a écrit dans le message de news:
> 386091ec-092a-4714-a0c6-c203d3092..._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
I agree with Michel, the tablespace_name column is meaningless for the base table_name of a partitioned table. The tablespace_name column is also null where the table is an IOT and for temporary tables.
HTH -- Mark D Powell -- Received on Fri Apr 16 2010 - 14:40:31 CDT