最近要給客戶做一些split partition方面的操作，簡單的測試一下，測試過程如下：
http://blog.chinaunix.net/uid-17277885-id-2809640.html 順便蒐集總結的不錯的一篇文章

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 17 18:22:10 2015

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 313860096 bytes
Fixed Size                  1374304 bytes
Variable Size             171968416 bytes
Database Buffers          134217728 bytes
Redo Buffers                6299648 bytes
Database mounted.
Database opened.

SQL> create tablespace test_space01 datafile 'E:\ORACLE\ORADATA\TEST\tbs01.dbf' size 1m;

Tablespace created.

SQL> create tablespace test_space02 datafile 'E:\ORACLE\ORADATA\TEST\tbs02.dbf' size 1m;

Tablespace created.

SQL> create tablespace test_space03 datafile 'E:\ORACLE\ORADATA\TEST\tbs03.dbf' size 1m;

Tablespace created.

SQL> create tablespace test_space04 datafile 'E:\ORACLE\ORADATA\TEST\tbs04.dbf' size 1m;

Tablespace created.

SQL> CREATE TABLE range_example(
2 range_key_column DATE,
3 DATA VARCHAR2(20),
4 ID integer
5 )
6 PARTITION BY RANGE(range_key_column)
7 (
8 PARTITION part01 VALUES LESS THAN(TO_DATE('2014-07-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE test_space01,
9 PARTITION part02 VALUES LESS THAN(TO_DATE('2014-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE test_space02,
10 PARTITION part03 VALUES LESS THAN(TO_DATE('2014-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE test_space03,
11 PARTITION part04 VALUES LESS THAN (MAXVALUE)TABLESPACE test_space04
12 );

Table created.

SQL> drop table t;

Table dropped.

SQL> create table t tablespace users as select * from dba_objects;

Table created.

SQL> select distinct trunc(created) from t
2 ;

TRUNC(CREATED)
-------------------
2014/10/13 00:00:00
2014/10/16 00:00:00
2014/10/10 00:00:00
2014/10/14 00:00:00
2015/03/17 00:00:00
2014/10/11 00:00:00
2014/10/12 00:00:00
2014/10/17 00:00:00
2014/11/15 00:00:00
2015/02/03 00:00:00
2015/02/08 00:00:00

11 rows selected.

SQL> update t set created=created - 60 where object_type='VIEW';

3818 rows updated.

SQL> update t set created=created - 90 where object_type='PROCEDURE';

100 rows updated.

SQL> commit;

Commit complete.

SQL> desc range_example
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
RANGE_KEY_COLUMN                                   DATE
DATA                                               VARCHAR2(20)
ID                                                 NUMBER(38)

SQL> alter table range_example modify data varchar2(30);

Table altered.

SQL> insert into range_example select created,object_type,object_id from t;

14151 rows created.

SQL> commit;

Commit complete.

SQL> select * from range_example partition(part01);

no rows selected

SQL> select count(*) from range_example partition(part02);

COUNT(*)
----------
100

SQL> select count(*) from range_example partition(part03);

COUNT(*)
----------
3818

SQL> select count(*) from range_example partition(part04);

COUNT(*)
----------
10233

SQL> create tablespace test_space05 datafile 'E:\ORACLE\ORADATA\TEST\tbs05.dbf' size 1m;

Tablespace created.

SQL>
SQL> create tablespace test_space06 datafile 'E:\ORACLE\ORADATA\TEST\tbs06.dbf' size 1m;

Tablespace created.

SQL>
SQL> create tablespace test_space07 datafile 'E:\ORACLE\ORADATA\TEST\tbs07.dbf' size 1m;

Tablespace created.

SQL> desc range_example
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
RANGE_KEY_COLUMN                                   DATE
DATA                                               VARCHAR2(30)
ID                                                 NUMBER(38)

SQL> create index idx_id on range_example(id) tablespace users;

Index created.

SQL> create index idx_id1 on range_example(range_key_column) local tablespace users;

Index created.

SQL> update range_example set id1= RANGE_KEY_COLUMN;

14151 rows updated.

SQL> commit;

Commit complete.

SQL> create index idx_id2 on range_example(id1)
2 global partition by range(id1)
3 (partition part_01 values less than (TO_DATE('2014-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),
4 partition part_02 values less than(maxvalue)
5 ) tablespace users
6 /

Index created.

SQL>

SQL> ALTER TABLE range_example ADD PARTITION part05 VALUES LESS THAN (TO_DATE('2014-10-1 00:00:00','yyyy-mm-ddhh24:mi:ss
'));
ALTER TABLE range_example ADD PARTITION part05 VALUES LESS THAN (TO_DATE('2014-10-1 00:00:00','yyyy-mm-ddhh24:mi:ss'))
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

SQL>
SQL> ALTER TABLE range_example MERGE PARTITIONS part03, part04 INTO PARTITION part03;
ALTER TABLE range_example MERGE PARTITIONS part03, part04 INTO PARTITION part03
*
ERROR at line 1:
ORA-14275: cannot reuse lower-bound partition as resulting partition

SQL> ALTER TABLE range_example MERGE PARTITIONS part03, part04 INTO PARTITION part04;

Table altered.

SQL> select count(*) from range_example partition(part04);

COUNT(*)
----------
14051

SQL>

ALTER TABLE range_example MODIFY PARTITION part04 REBUILD UNUSABLE LOCAL INDEXES;

SQL> ALTER TABLE range_example SPLIT PARTITION part06
2 AT(TO_DATE('2014-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) INTO
3 ( PARTITION part07 TABLESPACE test_space07，
4 PARTITION part08
5 )update indexes;

Table altered.

SQL>
SQL> set linesize 200
SQL> SELECT * FROM RANGE_EXAMPLE WHERE ROWNUM=1;

RANGE_KEY_COLUMN DATA ID ID1
------------------- ------------------------------ ---------- -------------------
2014/07/12 11:47:20 PROCEDURE 4771 2014/07/12 11:47:20

SQL> insert into range_example values((TO_DATE('2014-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),'TEST',1000,(TO_DATE('201
4-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')));

1 row created.

SQL> insert into range_example values((TO_DATE('2014-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')),'TEST',1000,(TO_DATE('201
4-10-01 00:00:00','yyyy-mm-dd hh24:mi:ss')));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select * from range_example partition(part05) where id=1000;

no rows selected

SQL> select * from range_example partition(part06) where id=1000;

RANGE_KEY_COLUMN    DATA                                   ID ID1
------------------- ------------------------------ ---------- -------------------
2014/10/10 11:46:45 TABLE                                1000 2014/10/10 11:46:45
2014/11/01 00:00:00 TEST                                 1000 2014/11/01 00:00:00
2014/10/01 00:00:00 TEST                                 1000 2014/10/01 00:00:00

SQL>

SQL> ALTER TABLE range_example SPLIT PARTITION part06
2 AT(TO_DATE('2014-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) INTO
3 ( PARTITION part07 TABLESPACE test_space07，
4 PARTITION part08
5 )update indexes;

Table altered.

SQL> alter index idx_id1 rebuild partition part05;

Index altered.

SQL> alter index idx_id2 rebuild partition part_01;

Index altered.

SQL> alter index idx_id2 rebuild partition part_02;

Index altered.

SQL>

SQL> alter table range_example exchange partition part02 with table t_part02;

Table altered.

--===============================

SQL> select count(*) from t_range partition(part01);

COUNT(*)
----------
0

SQL> select count(*) from t_range partition(part02);

COUNT(*)
----------
0

SQL> select count(*) from t_range partition(part05);

COUNT(*)
----------
3818

SQL> select count(*) from t_range partition(part07);

COUNT(*)
----------
24285

SQL> select count(*) from t_range partition(part08);

COUNT(*)
----------
152

SQL> alter index idx_id1 rebuild partition part02;

Index altered.

SQL> alter index idx_id2 rebuild partition part_01;

Index altered.

SQL> alter index idx_id2 rebuild partition part_02;

Index altered.

SQL> alter index idx_id rebuild online;

Index altered.

SQL> alter table t_range drop partition part05;

Table altered.

SQL>

SQL> alter table t_range move partition part02 tablespace test_space02;

Table altered.

SQL>

SQL> alter table t_range truncate partition part08;

Table truncated.

SQL>

SQL> alter table t_range rename partition part01 to part03;

Table altered.

SQL>

SQL> alter index idx_id1 modify partition part03 unusable;

Index altered.

SQL>

--==============================
MODIFY PARTITION パーティション名 →
→┬┬ ───────────────────┬┬→
││［表領域は変更出來ない］                                    ││
│├   ────────────────────┤│
│├ ────────────────────┤│
│└ ← ─────────────────────────────┘│
├ UNUSABLE LOCAL INDEXES ─────────────────────┤
│［指定したパーティションに対応するローカル索引を使用不可にする］│
│［ (表、マテリアライズドビューのみ指定可能)                           ］│
└ REBUILD UNUSABLE LOCAL INDEXES ─────────────────┘
    ［指定したパーティションに対応する使用不可なローカル索引を再作成する］
    ［ (表、マテリアライズドビューのみ指定可能)
--=================================

SQL> alter table t_range modify partition part03 allocate extent ;

Table altered.

split partition的一些測試

相關文章