ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle10g--11gR2分区表汇总十二

oracle10g--11gR2分区表汇总十二

原创 Linux操作系统 作者:jack22220613 时间:2011-03-21 09:43:56 0 删除 编辑

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博客 ” ,链接:http://blog.itpub.net/10037372/viewspace-690029/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-05-13

  • 博文量
    94
  • 访问量
    350367