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
Copyright (c) 1982, 2010, Oracle. All rights reserved.
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.