oracle 10g分区表,oracle10g--11gR2分区表汇总十二

1.4引用分区

SQL>

SQL>

SQL> create table tab_2(

2time date,

3id number primary key,

4city_id number,

5value1 number,

6value2 varchar2(10))

7partition by list(city_id)

8(

9partition p_tab_2_0 values(10),

10partition p_tab_2_1 values(20),

11partition p_tab_2_2 values(30),

12partition 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_NAMEPARTITION_POSITION HIGH_VALUE

------------------------------ ------------------ --------------------

P_TAB_2_01 10

P_TAB_2_12 20

P_TAB_2_23 30

P_TAB_2_34 40

SQL>

SQL> create table tab_3(

2time date,

3id number,

4city_id number,

5value1 number,

6value2 varchar2(10),

7constraint con_tab_3 foreign key(id) references tab_2(id))

8partition 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(

2time date,

3id number not null,

4city_id number,

5value1 number,

6value2 varchar2(10),

7constraint con_tab_3 foreign key(id) references tab_2(id))

8partition 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_NAMEPARTITION_POSITION HIGH_VALUE

------------------------------ ------------------ --------------------

P_TAB_2_01 10

P_TAB_2_12 20

P_TAB_2_23 30

P_TAB_2_34 40

P_TAB_2_01

P_TAB_2_12

P_TAB_2_23

P_TAB_2_34

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_2P_TAB_2_01 10

TAB_2P_TAB_2_12 20

TAB_2P_TAB_2_23 30

TAB_2P_TAB_2_3 4 40

TAB_3P_TAB_2_01

TAB_3P_TAB_2_12

TAB_3P_TAB_2_23

TAB_3P_TAB_2_34

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_2LIST

TAB_3REFERENCE 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_VALUETABLESPACE

---------- ---------- ------------------ -------------------- ----------

TAB_2P_TAB_2_01 10USERS

TAB_2P_TAB_2_12 20USERS

TAB_2P_TAB_2_23 30USERS

TAB_2P_TAB_2_34 40USERS

TAB_3P_TAB_2_01USERS

TAB_3P_TAB_2_12USERS

TAB_3P_TAB_2_23USERS

TAB_3P_TAB_2_34USERS

8 rows selected.

SQL> alter table tab_2

2add 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_VALUETABLESPACE

---------- ---------- ------------------ -------------------- ----------

TAB_2P_TAB_2_01 10USERS

TAB_2P_TAB_2_12 20USERS

TAB_2P_TAB_2_23 30USERS

TAB_2P_TAB_2_34 40USERS

TAB_2P_TAB_2_45 50USERS

TAB_3P_TAB_2_01USERS

TAB_3P_TAB_2_12USERS

TAB_3P_TAB_2_23USERS

TAB_3P_TAB_2_34USERS

TAB_3P_TAB_2_45USERS

10 rows selected.

SQL> alter table tab_2

2add partition p_tab_2_5 values(60)

3tablespace 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_VALUETABLESPACE

---------- ---------- ------------------ -------------------- ----------

TAB_2P_TAB_2_01 10USERS

TAB_2P_TAB_2_12 20USERS

TAB_2P_TAB_2_23 30USERS

TAB_2P_TAB_2_34 40USERS

TAB_2P_TAB_2_45 50USERS

TAB_2P_TAB_2_56 60TBS_2

TAB_3P_TAB_2_01USERS

TAB_3P_TAB_2_12USERS

TAB_3P_TAB_2_23USERS

TAB_3P_TAB_2_34USERS

TAB_3P_TAB_2_45USERS

TAB_3P_TAB_2_56TBS_2

12 rows selected.

--默认情况下，父子表的分区会用同一个表空间

SQL> alter table tab_2

2add partition p_tab_2_6 values(70)

3tablespace tbs_3

4dependent 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_VALUETABLESPACE

---------- ---------- ------------------ -------------------- ----------

TAB_2P_TAB_2_01 10USERS

TAB_2P_TAB_2_12 20USERS

TAB_2P_TAB_2_23 30USERS

TAB_2P_TAB_2_34 40USERS

TAB_2P_TAB_2_45 50USERS

TAB_2P_TAB_2_56 60TBS_2

TAB_2P_TAB_2_67 70TBS_3

TAB_3P_TAB_2_01USERS

TAB_3P_TAB_2_12USERS

TAB_3P_TAB_2_23USERS

TAB_3P_TAB_2_34USERS

TAB_3P_TAB_2_45USERS

TAB_3P_TAB_2_56TBS_2

TAB_3P_TAB_3_07TBS_4

14 rows selected.

1.5复合分区

Range-range

List-range

List-list

List-hash

加上之前10g已经支持的，现在range/list的复合分区就已经齐全了。