Skip to Main Content
  • Questions
  • Interval Partitioning Tablespaces Metadata

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vijay.

Asked: June 30, 2021 - 11:23 pm UTC

Last updated: July 07, 2021 - 2:35 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

We have been using Interval partitioning for our range partitioned tables based on date columns with STORE IN clause to distribute the partitions across multiple tablespaces in a round robin mode as we only retain data upto 12 months for transactional tables. It is all working fine. My question really is, I am unable to find the data dictionary view which is capturing the tablespaces that we set using STORE IN clause. I checked dba_tables, dba_part_tables and quite a few other tables that I extracted from "SELECT table_name FROM dict WHERE table_name like 'DBA%TAB%';" with no luck. May I ask which data dictionary view captures the STORE IN tablespaces for the INTERVAL partitions ?

and Connor said...

You might be thinking of DBA_PART_TABLES ? which has all the defaults

SQL> desc dba_part_tables
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ---------------------------
 OWNER                                                                            VARCHAR2(128)
 TABLE_NAME                                                                       VARCHAR2(128)
 PARTITIONING_TYPE                                                                VARCHAR2(9)
 SUBPARTITIONING_TYPE                                                             VARCHAR2(9)
 PARTITION_COUNT                                                                  NUMBER
 DEF_SUBPARTITION_COUNT                                                           NUMBER
 PARTITIONING_KEY_COUNT                                                           NUMBER
 SUBPARTITIONING_KEY_COUNT                                                        NUMBER
 STATUS                                                                           VARCHAR2(8)
 DEF_TABLESPACE_NAME                                                              VARCHAR2(30)
 DEF_PCT_FREE                                                                     NUMBER
 DEF_PCT_USED                                                                     NUMBER
 DEF_INI_TRANS                                                                    NUMBER
 DEF_MAX_TRANS                                                                    NUMBER
 DEF_INITIAL_EXTENT                                                               VARCHAR2(40)
 DEF_NEXT_EXTENT                                                                  VARCHAR2(40)
 DEF_MIN_EXTENTS                                                                  VARCHAR2(40)
 DEF_MAX_EXTENTS                                                                  VARCHAR2(40)
 DEF_MAX_SIZE                                                                     VARCHAR2(40)
 DEF_PCT_INCREASE                                                                 VARCHAR2(40)
 DEF_FREELISTS                                                                    NUMBER
 DEF_FREELIST_GROUPS                                                              NUMBER
 DEF_LOGGING                                                                      VARCHAR2(7)
 DEF_COMPRESSION                                                                  VARCHAR2(8)
 DEF_COMPRESS_FOR                                                                 VARCHAR2(30)
 DEF_BUFFER_POOL                                                                  VARCHAR2(7)
 DEF_FLASH_CACHE                                                                  VARCHAR2(7)
 DEF_CELL_FLASH_CACHE                                                             VARCHAR2(7)
 REF_PTN_CONSTRAINT_NAME                                                          VARCHAR2(128)
 INTERVAL                                                                         VARCHAR2(1000)
 AUTOLIST                                                                         VARCHAR2(3)
 INTERVAL_SUBPARTITION                                                            VARCHAR2(1000)
 AUTOLIST_SUBPARTITION                                                            VARCHAR2(3)
 IS_NESTED                                                                        VARCHAR2(3)
 DEF_SEGMENT_CREATION                                                             VARCHAR2(4)
 DEF_INDEXING                                                                     VARCHAR2(3)
 DEF_INMEMORY                                                                     VARCHAR2(8)
 DEF_INMEMORY_PRIORITY                                                            VARCHAR2(8)
 DEF_INMEMORY_DISTRIBUTE                                                          VARCHAR2(15)
 DEF_INMEMORY_COMPRESSION                                                         VARCHAR2(17)
 DEF_INMEMORY_DUPLICATE                                                           VARCHAR2(13)
 DEF_READ_ONLY                                                                    VARCHAR2(3)
 DEF_CELLMEMORY                                                                   VARCHAR2(24)
 DEF_INMEMORY_SERVICE                                                             VARCHAR2(12)
 DEF_INMEMORY_SERVICE_NAME                                                        VARCHAR2(1000)

SQL>



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

not in dba_part_tables

Rajeshwaran Jeyabal, July 06, 2021 - 4:12 am UTC

How about a test case ?

don't see it available in "dba_part_tables" dictionary objects.

demo@XEPDB1> create table t
  2  partition by range(created)
  3  interval( numtoyminterval(1,'month') )
  4  store in ( ts_data,ts_data2 )
  5  ( partition p2010 values less than (
  6     to_date('01-jan-2011','dd-mon-yyyy') ) )
  7  as
  8  select owner, object_name,object_id,
  9             to_date('01-jan-2011','dd-mon-yyyy') + (rownum/365) as created
 10  from all_objects
 11  where rownum <= 500;

Table created.

demo@XEPDB1> @printtbl 'select * from dba_part_tables where table_name ="T"'
OWNER                         : "DEMO"
TABLE_NAME                    : "T"
PARTITIONING_TYPE             : "RANGE"
SUBPARTITIONING_TYPE          : "NONE"
PARTITION_COUNT               : "1048575"
DEF_SUBPARTITION_COUNT        : "0"
PARTITIONING_KEY_COUNT        : "1"
SUBPARTITIONING_KEY_COUNT     : "0"
STATUS                        : "VALID"
DEF_TABLESPACE_NAME           : "USERS"
DEF_PCT_FREE                  : "10"
DEF_PCT_USED                  : ""
DEF_INI_TRANS                 : "1"
DEF_MAX_TRANS                 : "255"
DEF_INITIAL_EXTENT            : "DEFAULT"
DEF_NEXT_EXTENT               : "DEFAULT"
DEF_MIN_EXTENTS               : "DEFAULT"
DEF_MAX_EXTENTS               : "DEFAULT"
DEF_MAX_SIZE                  : "DEFAULT"
DEF_PCT_INCREASE              : "DEFAULT"
DEF_FREELISTS                 : ""
DEF_FREELIST_GROUPS           : ""
DEF_LOGGING                   : "NONE"
DEF_COMPRESSION               : "NONE"
DEF_COMPRESS_FOR              : ""
DEF_BUFFER_POOL               : "DEFAULT"
DEF_FLASH_CACHE               : "DEFAULT"
DEF_CELL_FLASH_CACHE          : "DEFAULT"
REF_PTN_CONSTRAINT_NAME       : ""
INTERVAL                      : "NUMTOYMINTERVAL(1,'MONTH')"
AUTOLIST                      : "NO"
INTERVAL_SUBPARTITION         : ""
AUTOLIST_SUBPARTITION         : "NO"
IS_NESTED                     : "NO"
DEF_SEGMENT_CREATION          : "NONE"
DEF_INDEXING                  : "ON"
DEF_INMEMORY                  : "NONE"
DEF_INMEMORY_PRIORITY         : ""
DEF_INMEMORY_DISTRIBUTE       : ""
DEF_INMEMORY_COMPRESSION      : ""
DEF_INMEMORY_DUPLICATE        : ""
DEF_READ_ONLY                 : "NO"
DEF_CELLMEMORY                : ""
DEF_INMEMORY_SERVICE          : ""
DEF_INMEMORY_SERVICE_NAME     : ""
-----------------

PL/SQL procedure successfully completed.

demo@XEPDB1>

Connor McDonald
July 07, 2021 - 2:35 am UTC

Apologies to original poster - I didn't see the reference to DBA_PART_TABLES.

The STORE IN information is not readily exposed, but if you really need to see (without getting the DDL) you can do something like this:

SQL> create table t
  2  (
  3   OWNER                                                                   VARCHAR2(128)
  4   ,OBJECT_NAME                                                             VARCHAR2(128)
  5   ,SUBOBJECT_NAME                                                                   VARCHAR2(128)
  6   ,OBJECT_ID                                                               NUMBER
  7   ,DATA_OBJECT_ID                                                                   NUMBER
  8   ,OBJECT_TYPE                                                                      VARCHAR2(23)
  9   ,CREATED                                                                 DATE
 10   )
 11      partition by range(created)
 12      interval( numtoyminterval(1,'month') )
 13      store in ( users,asktom )
 14      ( partition p2010 values less than (
 15         to_date('01-jan-2011','dd-mon-yyyy') ) );

Table created.

SQL> select o.object_name, t.name
  2  from sys.insert_tsn_list$ i,
  3       dba_objects o,
  4       sys.ts$ t
  5  where i.bo# = o.object_id
  6  and   t.ts# = i.ts#;

OBJECT_NAME                              NAME
---------------------------------------- ----------
T                                        USERS
T                                        ASKTOM



More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database