在数据字典中哪里可以找到范围区间分区表空间？

Question

对于范围区间分区表，您可以指定多个表空间，例如：

CREATE TABLE range_part_interval_table(col1 NUMBER, col2 NUMBER)
PARTITION BY RANGE (col1)
INTERVAL (10) STORE IN (ts2, ts3, ts4)
(PARTITION VALUES LESS THAN (100) TABLESPACE ts1);

但我找不到区间表空间存储在数据字典中的位置（此处为ts2、ts3、ts4）。此信息在某处可用吗？

Answer 1

这应该会为您提供所需的信息：

SELECT table_owner, table_name, partition_name, tablespace_name 
FROM dba_tab_partitions 
WHERE table_name = <table_name>;

以下是表格说明：

%> desc dba_tab_partitions

Name                           Null                             Type                                                                                                                                                                                                                                                                                       
------------------------------ -------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
TABLE_OWNER                                                     VARCHAR2(30)                                                                                                                                                                                                                                                                               
TABLE_NAME                                                      VARCHAR2(30)                                                                                                                                                                                                                                                                               
COMPOSITE                                                       VARCHAR2(3)                                                                                                                                                                                                                                                                                
PARTITION_NAME                                                  VARCHAR2(30)                                                                                                                                                                                                                                                                               
SUBPARTITION_COUNT                                              NUMBER                                                                                                                                                                                                                                                                                     
HIGH_VALUE                                                      LONG()                                                                                                                                                                                                                                                                                     
HIGH_VALUE_LENGTH                                               NUMBER                                                                                                                                                                                                                                                                                     
PARTITION_POSITION                                              NUMBER                                                                                                                                                                                                                                                                                     
TABLESPACE_NAME                                                 VARCHAR2(30)                                                                                                                                                                                                                                                                               
PCT_FREE                                                        NUMBER                                                                                                                                                                                                                                                                                     
PCT_USED                                                        NUMBER                                                                                                                                                                                                                                                                                     
INI_TRANS                                                       NUMBER                                                                                                                                                                                                                                                                                     
MAX_TRANS                                                       NUMBER                                                                                                                                                                                                                                                                                     
INITIAL_EXTENT                                                  NUMBER                                                                                                                                                                                                                                                                                     
NEXT_EXTENT                                                     NUMBER                                                                                                                                                                                                                                                                                     
MIN_EXTENT                                                      NUMBER                                                                                                                                                                                                                                                                                     
MAX_EXTENT                                                      NUMBER                                                                                                                                                                                                                                                                                     
MAX_SIZE                                                        NUMBER                                                                                                                                                                                                                                                                                     
PCT_INCREASE                                                    NUMBER                                                                                                                                                                                                                                                                                     
FREELISTS                                                       NUMBER                                                                                                                                                                                                                                                                                     
FREELIST_GROUPS                                                 NUMBER                                                                                                                                                                                                                                                                                     
LOGGING                                                         VARCHAR2(7)                                                                                                                                                                                                                                                                                
COMPRESSION                                                     VARCHAR2(8)                                                                                                                                                                                                                                                                                
COMPRESS_FOR                                                    VARCHAR2(18)                                                                                                                                                                                                                                                                               
NUM_ROWS                                                        NUMBER                                                                                                                                                                                                                                                                                     
BLOCKS                                                          NUMBER                                                                                                                                                                                                                                                                                     
EMPTY_BLOCKS                                                    NUMBER                                                                                                                                                                                                                                                                                     
AVG_SPACE                                                       NUMBER                                                                                                                                                                                                                                                                                     
CHAIN_CNT                                                       NUMBER                                                                                                                                                                                                                                                                                     
AVG_ROW_LEN                                                     NUMBER                                                                                                                                                                                                                                                                                     
SAMPLE_SIZE                                                     NUMBER                                                                                                                                                                                                                                                                                     
LAST_ANALYZED                                                   DATE                                                                                                                                                                                                                                                                                       
BUFFER_POOL                                                     VARCHAR2(7)                                                                                                                                                                                                                                                                                
GLOBAL_STATS                                                    VARCHAR2(3)                                                                                                                                                                                                                                                                                
USER_STATS                                                      VARCHAR2(3)                                                                                                                                                                                                                                                                                

Answer 2

除非您有一些数据，否则您的间隔分区并不存在......

SQL> CREATE TABLE range_part_interval_table(col1 NUMBER, col2 NUMBER)
  2  PARTITION BY RANGE (col1)
  3  INTERVAL (10) STORE IN (ts2, ts3, ts4)
  4  (PARTITION VALUES LESS THAN (100) TABLESPACE ts1);

Table created.

SQL> SELECT table_owner, table_name, partition_name, tablespace_name
  2  FROM dba_tab_partitions
  3  WHERE table_name = 'RANGE_PART_INTERVAL_TABLE'
  4  /

TABLE_OWNER TABLE_NAME                     PARTITION_NAME TABLESPACE_NAME
----------- ------------------------------ -------------- ---------------
APC         RANGE_PART_INTERVAL_TABLE      SYS_P55        TS1

SQL>

空表仅具有定义的分区。但是如果我们插入一些不同时间间隔的数据......

SQL> insert into range_part_interval_table values (90, 8888)
  2  /

1 row created.

SQL> insert into range_part_interval_table values (110, 8888)
  2  /

1 row created.

SQL> insert into range_part_interval_table values (310, 8888)
  2  /

1 row created.

SQL> insert into range_part_interval_table values (120, 8888)
  2  /

1 row created.

SQL> SELECT table_owner, table_name, partition_name, tablespace_name
  2  FROM dba_tab_partitions
  3  WHERE table_name = 'RANGE_PART_INTERVAL_TABLE'
  4  /

TABLE_OWNER TABLE_NAME                     PARTITION_NAME TABLESPACE_NAME
----------- ------------------------------ -------------- ---------------
APC         RANGE_PART_INTERVAL_TABLE      SYS_P58        TS2
APC         RANGE_PART_INTERVAL_TABLE      SYS_P55        TS1
APC         RANGE_PART_INTERVAL_TABLE      SYS_P56        TS4
APC         RANGE_PART_INTERVAL_TABLE      SYS_P57        TS3

SQL>

Answer 3

检查 sys.insert_tsn_list$