--创建分区表,以created为分区键，less than为在2007年前的为dbobjs_06分区,存储在users表空间
--在2008年前的为dbobjs_07分区,存储在test_tbs表空间
--有四种方法对表进行分区：区间分区range,散列分区hash,列表分区list，组合分区
SQL> create table dbobjs
2 (object_id number not null,
3 object_name varchar2(128),
4 created date not null
5 )
6 partition by range (created)
7 (partition dbobjs_06 values less than (to_date('01/01/2007','DD/MM/YYYY')) tablespace users,
8 partition dbobjs_07 values less than (to_date('01/01/2008','DD/MM/YYYY')) tablespace test_tbs );
Table created.
--查看分区范围
SQL> select partition_name,high_value,partition_position from user_tab_partitions where table_name = 'DBOBJS';
PARTITION_NAME HIGH_VALUE PARTITION_POSITION
-------------------- -------------------------------------------------------------------------------- ------------------
DBOBJS_06 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 1
DBOBJS_07 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
--查看哪一列分区(分区键)
SQL> col COLUMN_NAME for a10
SQL> select column_name,column_position from user_part_key_columns where name = 'DBOBJS';
COLUMN_NAM COLUMN_POSITION
---------- ---------------
CREATED 1
--创建本地分区索引
SQL> create index dbobjs_idx on dbobjs (created) LOCAL
2 (partition dbobjs_06 tablespace users,
3 partition dbobjs_07 tablespace test_tbs);
Index created.
--查看分区段
SQL> col segment_name for a20
SQL> col partition_name for a20
SQL> select segment_name,partition_name,tablespace_name
2 from dba_segments
3 where segment_name = 'DBOBJS_IDX';
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- -------------------- ------------------------------
DBOBJS_IDX DBOBJS_06 USERS
DBOBJS_IDX DBOBJS_07 TEST_TBS
--插入两条记录
SQL> insert into dbobjs values(1,'less than 07',to_date('12/12/2006','dd/mm/yyyy'));
1 row created.
SQL> insert into dbobjs values(1,'less than 08',to_date('21/10/2007','dd/mm/yyyy'));
1 row created.
--查看插入的这两条记录分别存放在哪个分区了
SQL> set line 300
SQL> col OBJECT_NAME for a20
SQL> select * from dbobjs partition(dbobjs_06);
OBJECT_ID OBJECT_NAME CREATED
---------- -------------------- ---------
1 less than 07 12-DEC-06
SQL> select * from dbobjs partition(dbobjs_07);
OBJECT_ID OBJECT_NAME CREATED
---------- -------------------- ---------
1 less than 08 21-OCT-07
--创建分区全局索引
SQL> create index dbobjs_global_idx
2 on dbobjs(object_id)
3 global
4 partition by range(object_id)
5 (
6 partition dbobjs_06 values less than (1000),
7 partition dbobjs_07 values less than (MAXVALUE)
8 );
Index created.
--查看分区索引
SQL> select owner,index_name,table_name,partitioning_type,partition_count,locality
2 from DBA_PART_INDEXES where owner = 'ZHAOMN' and table_name = 'DBOBJS';
OWNER INDEX_NAME TABLE_NAME PARTITI PARTITION_COUNT LOCALI
------------------------------ ------------------------------ ------------------------------ ------- --------------- ------
ZHAOMN DBOBJS_IDX DBOBJS RANGE 2 LOCAL
ZHAOMN DBOBJS_GLOBAL_IDX DBOBJS RANGE 2 GLOBAL
--添加分区
SQL> alter table dbobjs add partition dbobjs_08
2 values less than (to_date('01/01/2009','DD/MM/YYYY'));
Table altered.
--查看新添加的分区
SQL> select partition_count from DBA_PART_TABLES where owner = 'ZHAOMN' and table_name = 'DBOBJS';
PARTITION_COUNT
---------------
3
SQL> select partition_name,high_value,partition_position from user_tab_partitions where table_name = 'DBOBJS';
PARTITION_NAME HIGH_VALUE PARTITION_POSITION
-------------------- -------------------------------------------------------------------------------- ------------------
DBOBJS_06 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 1
DBOBJS_07 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
DBOBJS_08 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 3
--删除其中的一个分区
SQL> alter table dbobjs
2 drop partition dbobjs_08;
Table altered.
SQL> select partition_name,high_value,partition_position from user_tab_partitions where table_name = 'DBOBJS';
PARTITION_NAME HIGH_VALUE PARTITION_POSITION
-------------------- -------------------------------------------------------------------------------- ------------------
DBOBJS_06 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 1
DBOBJS_07 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
--添加分区dbobjs_08
SQL> alter table dbobjs add partition dbobjs_08
2 values less than (to_date('01/01/2009','DD/MM/YYYY'));
Table altered.
--插入记录至dbobjs_08
SQL> insert into dbobjs values(1,'less than 09',to_date('21/10/2008','dd/mm/yyyy'));
1 row created.
SQL> select * from dbobjs partition(DBOBJS_08);
OBJECT_ID OBJECT_NAME CREATED
---------- -------------------- ---------
1 less than 09 21-OCT-08
SQL> select * from dbobjs;
OBJECT_ID OBJECT_NAME CREATED
---------- -------------------- ---------
1 less than 07 12-DEC-06
1 less than 08 21-OCT-07
1 less than 09 21-OCT-08
--清空该分区中的记录，保留该结构
SQL> alter table dbobjs
2 truncate partition dbobjs_08;
Table truncated.
--刚刚插入到dbobjs_08分区的记录已被删除
SQL> select * from dbobjs;
OBJECT_ID OBJECT_NAME CREATED
---------- -------------------- ---------
1 less than 07 12-DEC-06
1 less than 08 21-OCT-07
--dbobjs_08分区结构仍然存在
SQL> select partition_name,high_value,partition_position from user_tab_partitions where table_name = 'DBOBJS';
PARTITION_NAME HIGH_VALUE PARTITION_POSITION
-------------------- -------------------------------------------------------------------------------- ------------------
DBOBJS_06 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 1
DBOBJS_07 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 2
DBOBJS_08 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA 3
