oracle10g--11gR2分区表汇总十二
1.4 引用分区
SQL>
SQL>
SQL> create table tab_2(
2 time date,
3 id number primary key,
4 city_id number,
5 value1 number,
6 value2 varchar2(10))
7 partition by list(city_id)
8 (
9 partition p_tab_2_0 values(10),
10 partition p_tab_2_1 values(20),
11 partition p_tab_2_2 values(30),
12 partition p_tab_2_3 values(40)
13 )
14 ;
Table created.
SQL> col high_value for a20
SQL> select partition_name,partition_position,high_value from user_tab_partitions where TABLE_NAME='TAB_2';
PARTITION_NAME PARTITION_POSITION HIGH_VALUE
------------------------------ ------------------ --------------------
P_TAB_2_0 1 10
P_TAB_2_1 2 20
P_TAB_2_2 3 30
P_TAB_2_3 4 40
SQL>
SQL> create table tab_3(
2 time date,
3 id number,
4 city_id number,
5 value1 number,
6 value2 varchar2(10),
7 constraint con_tab_3 foreign key(id) references tab_2(id))
8 partition by reference(con_tab_3)
9 ;
partition by reference(con_tab_3)
*
ERROR at line 8:
ORA-14652: reference partitioning foreign key is not supported
SQL> create table tab_3(
2 time date,
3 id number not null,
4 city_id number,
5 value1 number,
6 value2 varchar2(10),
7 constraint con_tab_3 foreign key(id) references tab_2(id))
8 partition by reference(con_tab_3)
9 ;
Table created.
SQL> select partition_name,partition_position,high_value from user_tab_partitions where table_name in ('TAB_2','TAB_3');
PARTITION_NAME PARTITION_POSITION HIGH_VALUE
------------------------------ ------------------ --------------------
P_TAB_2_0 1 10
P_TAB_2_1 2 20
P_TAB_2_2 3 30
P_TAB_2_3 4 40
P_TAB_2_0 1
P_TAB_2_1 2
P_TAB_2_2 3
P_TAB_2_3 4
8 rows selected.
SQL> col partition_name for a10
SQL> col table_name for a10
SQL> select table_name,partition_name,partition_position,high_value from user_tab_partitions where table_name in ('TAB_2','TAB_3');
TABLE_NAME PARTITION_ PARTITION_POSITION HIGH_VALUE
---------- ---------- ------------------ --------------------
TAB_2 P_TAB_2_0 1 10
TAB_2 P_TAB_2_1 2 20
TAB_2 P_TAB_2_2 3 30
TAB_2 P_TAB_2_3 4 40
TAB_3 P_TAB_2_0 1
TAB_3 P_TAB_2_1 2
TAB_3 P_TAB_2_2 3
TAB_3 P_TAB_2_3 4
8 rows selected.
SQL> select table_name,partitioning_type,ref_ptn_constraint_name from user_part_tables where table_name in ('TAB_2','TAB_3');
TABLE_NAME PARTITION REF_PTN_CONSTRAINT_NAME
---------- --------- ------------------------------
TAB_2 LIST
TAB_3 REFERENCE CON_TAB_3
SQL> col tablespace_name for a10
SQL> select table_name,partition_name,partition_position,high_value,tablespace_name from user_tab_partitions where table_name in ('TAB_2','TAB_3');
TABLE_NAME PARTITION_ PARTITION_POSITION HIGH_VALUE TABLESPACE
---------- ---------- ------------------ -------------------- ----------
TAB_2 P_TAB_2_0 1 10 USERS
TAB_2 P_TAB_2_1 2 20 USERS
TAB_2 P_TAB_2_2 3 30 USERS
TAB_2 P_TAB_2_3 4 40 USERS
TAB_3 P_TAB_2_0 1 USERS
TAB_3 P_TAB_2_1 2 USERS
TAB_3 P_TAB_2_2 3 USERS
TAB_3 P_TAB_2_3 4 USERS
8 rows selected.
SQL> alter table tab_2
2 add partition p_tab_2_4 values(50);
Table altered.
SQL> select table_name,partition_name,partition_position,high_value,tablespace_name from user_tab_partitions where table_name in ('TAB_2','TAB_3');
TABLE_NAME PARTITION_ PARTITION_POSITION HIGH_VALUE TABLESPACE
---------- ---------- ------------------ -------------------- ----------
TAB_2 P_TAB_2_0 1 10 USERS
TAB_2 P_TAB_2_1 2 20 USERS
TAB_2 P_TAB_2_2 3 30 USERS
TAB_2 P_TAB_2_3 4 40 USERS
TAB_2 P_TAB_2_4 5 50 USERS
TAB_3 P_TAB_2_0 1 USERS
TAB_3 P_TAB_2_1 2 USERS
TAB_3 P_TAB_2_2 3 USERS
TAB_3 P_TAB_2_3 4 USERS
TAB_3 P_TAB_2_4 5 USERS
10 rows selected.
SQL> alter table tab_2
2 add partition p_tab_2_5 values(60)
3 tablespace tbs_2
4 ;
Table altered.
SQL> select table_name,partition_name,partition_position,high_value,tablespace_name from user_tab_partitions where table_name in ('TAB_2','TAB_3');
TABLE_NAME PARTITION_ PARTITION_POSITION HIGH_VALUE TABLESPACE
---------- ---------- ------------------ -------------------- ----------
TAB_2 P_TAB_2_0 1 10 USERS
TAB_2 P_TAB_2_1 2 20 USERS
TAB_2 P_TAB_2_2 3 30 USERS
TAB_2 P_TAB_2_3 4 40 USERS
TAB_2 P_TAB_2_4 5 50 USERS
TAB_2 P_TAB_2_5 6 60 TBS_2
TAB_3 P_TAB_2_0 1 USERS
TAB_3 P_TAB_2_1 2 USERS
TAB_3 P_TAB_2_2 3 USERS
TAB_3 P_TAB_2_3 4 USERS
TAB_3 P_TAB_2_4 5 USERS
TAB_3 P_TAB_2_5 6 TBS_2
12 rows selected.
--默认情况下，父子表的分区会用同一个表空间
SQL> alter table tab_2
2 add partition p_tab_2_6 values(70)
3 tablespace tbs_3
4 dependent tables
5 (tab_3 (partition p_tab_3_0 tablespace tbs_4));
Table altered.
--加入 dependent tables xxx，之后，可以指定子表分区的表空间
SQL> select table_name,partition_name,partition_position,high_value,tablespace_name from user_tab_partitions where table_name in ('TAB_2','TAB_3');
TABLE_NAME PARTITION_ PARTITION_POSITION HIGH_VALUE TABLESPACE
---------- ---------- ------------------ -------------------- ----------
TAB_2 P_TAB_2_0 1 10 USERS
TAB_2 P_TAB_2_1 2 20 USERS
TAB_2 P_TAB_2_2 3 30 USERS
TAB_2 P_TAB_2_3 4 40 USERS
TAB_2 P_TAB_2_4 5 50 USERS
TAB_2 P_TAB_2_5 6 60 TBS_2
TAB_2 P_TAB_2_6 7 70 TBS_3
TAB_3 P_TAB_2_0 1 USERS
TAB_3 P_TAB_2_1 2 USERS
TAB_3 P_TAB_2_2 3 USERS
TAB_3 P_TAB_2_3 4 USERS
TAB_3 P_TAB_2_4 5 USERS
TAB_3 P_TAB_2_5 6 TBS_2
TAB_3 P_TAB_3_0 7 TBS_4
14 rows selected.
1.5 复合分区
Range-range
List-range
List-list
List-hash
加上之前10g已经支持的，现在range/list的复合分区就已经齐全了。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10037372/viewspace-690029/ ，如需转载，请注明出处，否则将追究法律责任。
