分区表必须掌握的几个字典

分布表必须掌握的几个字典 dba_tab_partitions,dba_part_tables,dba_part_key_columns
创建测试表
SQL> Create table &TAB (
ord_day NUMBER(2),
ord_month NUMBER(2),
ord_year NUMBER(4),
ord_id NUMBER(10)
)
storage (initial 12k next 12k pctincrease 0 minextents 1)
PARTITION BY RANGE (ord_year,ord_month,ord_day)
(
PARTITION P1 VALUES LESS THAN (2001,3,31) TABLESPACE &&TBS1,
PARTITION P2 VALUES LESS THAN (2001,6,30) TABLESPACE &&TBS2,
PARTITION P3 VALUES LESS THAN (2001,9,30) TABLESPACE &&TBS3,
PARTITION P4 VALUES LESS THAN (2001,12,32) TABLESPACE &&TBS4
)
/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Enter value for tab: range_t
old 1: Create table &TAB (
new 1: Create table range_t (
Enter value for tbs1: test_tbs
old 10: PARTITION P1 VALUES LESS THAN (2001,3,31) TABLESPACE &&TBS1,
new 10: PARTITION P1 VALUES LESS THAN (2001,3,31) TABLESPACE test_tbs,
Enter value for tbs2: test_tbs
old 11: PARTITION P2 VALUES LESS THAN (2001,6,30) TABLESPACE &&TBS2,
new 11: PARTITION P2 VALUES LESS THAN (2001,6,30) TABLESPACE test_tbs,
Enter value for tbs3: test_tbs
old 12: PARTITION P3 VALUES LESS THAN (2001,9,30) TABLESPACE &&TBS3,
new 12: PARTITION P3 VALUES LESS THAN (2001,9,30) TABLESPACE test_tbs,
Enter value for tbs4: test_tbs
old 13: PARTITION P4 VALUES LESS THAN (2001,12,32) TABLESPACE &&TBS4
new 13: PARTITION P4 VALUES LESS THAN (2001,12,32) TABLESPACE test_tbs

Table created.



1 查分区的表 ,这个表是分区表，查这个分区表的分区类型，分区数量以及分区键的数量。
SQL> select table_name, partitioning_type, partition_count, partitioning_key_count
from dba_part_tables
where table_name = UPPER('&&TAB')
/ 2 3 4
old 3: where table_name = UPPER('&&TAB')
new 3: where table_name = UPPER('range_t')

TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT PARTITIONING_KEY_COUNT
------------------------------ ------------------ --------------- ----------------------
RANGE_T RANGE 4 3

2 查表的分区，查分区表的分区名，对应存储空间以及分区键的高值。
SQL> SELECT table_name,partition_name,tablespace_name, high_value FROM dba_tab_partitions
WHERE table_name=UPPER('&&TAB')
order by partition_position
/ 2 3 4
old 2: WHERE table_name=UPPER('&&TAB')
new 2: WHERE table_name=UPPER('range_t')

TABLE_NAME PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ --------------- -------------------- --------------------
RANGE_T P1 TEST_TBS 2001, 3, 31
RANGE_T P2 TEST_TBS 2001, 6, 30
RANGE_T P3 TEST_TBS 2001, 9, 30
RANGE_T P4 TEST_TBS 2001, 12, 32


3 查分区段，查段名和段的分区名以及段的类型，段分区的表空间
SQL> column segment_name format A20
select segment_name, partition_name, segment_type, tablespace_name from dba_segments
where segment_name = UPPER('&&TAB')
order by partition_name
/SQL> 2 3 4
old 2: where segment_name = UPPER('&&TAB')
new 2: where segment_name = UPPER('range_t')

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
-------------------- --------------- ------------------------------------ --------------------
RANGE_T P1 TABLE PARTITION TEST_TBS
RANGE_T P2 TABLE PARTITION TEST_TBS
RANGE_T P3 TABLE PARTITION TEST_TBS
RANGE_T P4 TABLE PARTITION TEST_TBS

4查分区键，查询分区名称，分区类型（table ，index），分区键名字。
SQL> column name format A15
column column_name format A15
SELECT name, object_type, column_name FROM dba_part_key_columns
WHERE name=UPPER('&&TAB')
old 2: WHERE name=UPPER('&&TAB')
new 2: WHERE name=UPPER('range_t')

NAME OBJECT_TYP COLUMN_NAME
--------------- ---------- ---------------
RANGE_T TABLE ORD_DAY
RANGE_T TABLE ORD_MONTH
RANGE_T TABLE ORD_YEAR