This discussion is archived
10 Replies Latest reply: Dec 9, 2012 7:59 AM by knowledgespring RSS

tables (part or no part) and their tablespaces in oracle 11g

knowledgespring Newbie
Currently Being Moderated
I need to list all the tables and their table spaces for non part tables along with partition tables, partition name and its table space , I am using the following query to find it however query result is not something what i am looking for as i see null in table space columns.

tablespace column has null for even non partition table.
in query result there are cases that null in all the tablespace_name columns.. ..
tablespace columns have nulls for even part tables in ALL_TAB_PARTITIONS ,ALL_PART_TABLES tables for few part tables ..

Should we generat object stats to correct name filled in tablespace column.. or am i using wrong tables in my query..? how come null in all the tablespace columns or should i include any other dictionary table?

select AT.OWNER, AT.TABLE_NAME, AT.TABLESPACE_NAME,AT.PARTITIONED,APT.PARTITIONING_TYPE,ATP.PARTITION_NAME, ATP.TABLESPACE_NAME,APT.DEF_TABLESPACE_NAME
From ALL_tABLES AT,
ALL_TAB_PARTITIONS ATP ,
ALL_PART_TABLES APT
WHERE AT.OWNER IN ('CISADM')
AND ATP.TABLE_OWNER(+)=AT.OWNER
AND APT.OWNER(+)=AT.OWNER
AND ATP.TABLE_NAME(+)=AT.TABLE_NAME
AND APT.TABLE_NAME(+)=AT.TABLE_NAME
ORDER BY ATP.TABLE_NAME,ATP.PARTITION_POSITION;
--

could you please suggest what wrong with this query or query to find table space used for both non part and each table partition table space. Thanks in advance..
  • 1. Re: tables (part or no part) and their tablespaces in oracle 11g
    hitgon Expert
    Currently Being Moderated
    Refer the documents
    http://docs.oracle.com/cd/B14117_01/server.101/b10755/statviews_1190.htm#i1592091
    http://docs.oracle.com/cd/B14117_01/server.101/b10755/statviews_1184.htm#i1591118
  • 2. Re: tables (part or no part) and their tablespaces in oracle 11g
    knowledgespring Newbie
    Currently Being Moderated
    I am not looking for table definition urls..... am i missing any columns in the query that gives the information i am looking for??
  • 3. Re: tables (part or no part) and their tablespaces in oracle 11g
    orafad Oracle ACE
    Currently Being Moderated
    Could this have something to do with Deferred segment creation? (No segment => unknown tablespace)
  • 4. Re: tables (part or no part) and their tablespaces in oracle 11g
    knowledgespring Newbie
    Currently Being Moderated
    I have a partitioned table which has 9000000 records , has 16 partitions and has null in all the *tablespace_name columns , do you all think the given query is wrong to get tables and their partitions table spaces??!!!

    can someone plz give me the query to find each table tablespace and table partitions table space name etc.? thanks..
  • 5. Re: tables (part or no part) and their tablespaces in oracle 11g
    sb92075 Guru
    Currently Being Moderated
    knowledgespring wrote:
    I have a partitioned table which has 9000000 records , has 16 partitions and has null in all the *tablespace_name columns , do you all think the given query is wrong to get tables and their partitions table spaces??!!!

    can someone plz give me the query to find each table tablespace and table partitions table space name etc.? thanks..
    obtain TABLESPACE_NAME from ALL_TAB_PARTITIONS; not from ALL_TABLES

    Handle:     knowledgespring
    Status Level:     Newbie (10)
    Registered:     Dec 26, 2007
    Total Posts:     289
    Total Questions:     94 (89 unresolved)


    why so MANY unanswered questions?
  • 6. Re: tables (part or no part) and their tablespaces in oracle 11g
    knowledgespring Newbie
    Currently Being Moderated
    dear sb92075,

    do not look for stats.. did u read my query fully? i added tablespace_name from all the tables listed in from clause... it would be good to read the message completely.........
  • 7. Re: tables (part or no part) and their tablespaces in oracle 11g
    sb92075 Guru
    Currently Being Moderated
    knowledgespring wrote:
    dear sb92075,

    do not look for stats.. did u read my query fully? i added tablespace_name from all the tables listed in from clause... it would be good to read the message completely.........
    you can lead some folks to knowledge, but you can't make them think.
  • 8. Re: tables (part or no part) and their tablespaces in oracle 11g
    knowledgespring Newbie
    Currently Being Moderated
    i think i need to get the data from user_segments/user_extents for tablespace info..

    Thank you all for your time.. and inputs..
    however not sure why all_part_Tables,all_Tables tablespace_name columns have null and not usre if we need to generate dictionary objects stats at times.. i am using user_extents/segments objects for tablespace info.. thanks again............ please feel free to provide your inputs..........
  • 9. Re: tables (part or no part) and their tablespaces in oracle 11g
    SomeoneElse Guru
    Currently Being Moderated
    For a partitioned table you should get tablespace_name from user_tab_partitions because it's possible to have different partitions stored in different tablespaces. This can't be shown in the single row of user_tables.
    SQL> CREATE TABLE part_test
      2  (part_key    NUMBER
      3  )
      4  PARTITION BY RANGE(part_key)
      5  (PARTITION  part_test1 VALUES LESS THAN (100) tablespace users
      6  ,PARTITION  part_test2 VALUES LESS THAN (200) tablespace example
      7  );
    
    Table created.
    
    SQL> select table_name, tablespace_name from user_tables where table_name = 'PART_TEST';
    
    TABLE_NAME                     TABLESPACE_NAME
    ------------------------------ ------------------------------
    PART_TEST
    
    SQL> select table_name, partition_name, tablespace_name from user_tab_partitions where table_name = 'PART_TEST';
    
    TABLE_NAME                     PARTITION_NAME                 TABLESPACE_NAME
    ------------------------------ ------------------------------ ------------------------------
    PART_TEST                      PART_TEST1                     USERS
    PART_TEST                      PART_TEST2                     EXAMPLE
    Edited by: SomeoneElse on Dec 9, 2012 10:07 AM

    (OK, I see you tried that)
  • 10. Re: tables (part or no part) and their tablespaces in oracle 11g
    sb92075 Guru
    Currently Being Moderated
    knowledgespring wrote:
    i think i need to get the data from user_segments/user_extents for tablespace info..

    Thank you all for your time.. and inputs..
    however not sure why all_part_Tables,all_Tables tablespace_name columns have null and not usre if we need to generate dictionary objects stats at times.. i am using user_extents/segments objects for tablespace info.. thanks again............ please feel free to provide your inputs..........
      1  select tablespace_name , count(*) from dba_tab_partitions
      2* where table_owner = 'SH' group by tablespace_name
    SQL> /
    
    TABLESPACE_NAME                  COUNT(*)
    ------------------------------ ----------
    EXAMPLE                                56

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points