--創建分區表
create table emp3(id number(4) primary key,name varchar2(20),eff_dt date) partition by range(eff_dt)
(
partition p1 values less than (to_date('2015-07-01','yyyy-mm-dd')) ,//tablespace users,
partition p2 values less than (to_date('2016-01-01','yyyy-mm-dd')),
partition p3 values less than (to_date('2016-07-01','yyyy-mm-dd'))
);
--多列分區表 列的順序非常重要，只有第一列相等的時候，才比較第二列的值。
create table multicol (unit number(1), subunit char(1))
partition by range (unit,subunit)
(partition P_2b values less than (2,'B')
,partition P_2c values less than (2,'C')
,partition P_3b values less than (3,'B')
,partition P_4x values less than (4,'X'));
exp -help
imp -help
--導出p1分區
exp file=emp3.dmp tables=emp3:p1
imp file=emp3.dmp ignore=y
SQL> select * from emp3;
ID NAME EFF_DT
---------- -------------------- ---------
1 lilei 13-FEB-15
--查看分區情況
SQL> SELECT table_name,partition_name FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='EMP3';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
EMP3 P1
EMP3 P2
EMP3 P3
SQL> insert into scott.emp3 values(2,'hanmei',to_date('2015-07-11','yyyy-mm-dd'));
SQL> insert into scott.emp3 values(3,'lily',to_date('2015-12-30','yyyy-mm-dd'));
SQL> select * from emp3;
ID NAME EFF_DT
---------- -------------------- ---------
1 lilei 13-FEB-15
2 hanmei 11-JUL-15
3 lily 30-DEC-15
SQL> select * from emp3 partition(p1);
ID NAME EFF_DT
---------- -------------------- ---------
1 lilei 13-FEB-15
--刪除某個分區
SQL> alter table emp3 drop partition p3;
SQL> SELECT table_name,partition_name,tablespace_name FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='EMP3';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
EMP3 P1 USERS
EMP3 P2 USERS
SQL> conn system as sysdba
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/disk3/system01.dbf
/u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
/u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf
/u01/app/oracle/oradata/PROD/disk3/users01.dbf
/u01/app/oracle/oradata/PROD/disk3/cuug01.dbf
SQL> conn scott/tiger
Connected.
--添加分區
SQL> alter table emp3 add partition p3 values less than (to_date('2016-07-01','yyyy-mm-dd'));
SQL> SELECT table_name,partition_name,tablespace_name FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='EMP3';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
EMP3 P1 USERS
EMP3 P2 USERS
EMP3 P3 USERS
SQL> alter table emp3 drop partition p3;
SQL> alter table emp3 add partition p3 values less than (to_date('2016-07-01','yyyy-mm-dd')) tablespace cuug;
SQL> SELECT table_name,partition_name,tablespace_name FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='EMP3';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
EMP3 P1 USERS
EMP3 P2 USERS
EMP3 P3 CUUG
SQL> insert into emp3 values(4,'lucy',to_date('2016-03-03','yyyy-mm-dd'));
SQL> select * from emp3;
ID NAME EFF_DT
---------- -------------------- ---------
1 lilei 13-FEB-15
2 hanmei 11-JUL-15
3 lily 30-DEC-15
4 lucy 03-MAR-16
[oracle@gc1 ~]$ exp file=emp3.dmp tables=emp3
Export: Release 11.2.0.1.0 - Production on Thu Dec 10 15:36:58 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: scott
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table EMP3
. . exporting partition P1 1 rows exported
. . exporting partition P2 2 rows exported
. . exporting partition P3 1 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
[oracle@gc1 ~]$ sqlplus "/as sysdba"
SQL> drop tablespace cuug including contents and datafiles;
drop tablespace cuug including contents and datafiles
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace
SQL> drop table scott.emp3;
Table dropped.
SQL> drop tablespace cuug including contents and datafiles;
Tablespace dropped.
SQL>
SQL>
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$JoX+/znoIsfgUB6sMhl2Kw==$0 TABLE
BONUS TABLE
DEPT TABLE
EMP TABLE
EMP2 TABLE
MLOG$_EMP2 TABLE
SALGRADE TABLE
7 rows selected.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@gc1 ~]$ imp file=emp3.dmp ignore=y
Import: Release 11.2.0.1.0 - Production on Thu Dec 10 15:40:45 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: scott
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "EMP3" ("ID" NUMBER(4, 0), "NAME" VARCHAR2(20), "EFF_DT" DATE)"
" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "USERS" PARTITI"
"ON BY RANGE ("EFF_DT" ) (PARTITION "P1" VALUES LESS THAN (TO_DATE(' 2015-0"
"7-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFR"
"EE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576"
" MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE"
" "USERS" LOGGING NOCOMPRESS, PARTITION "P2" VALUES LESS THAN (TO_DATE(' 201"
"6-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PC"
"TFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048"
"576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESP"
"ACE "USERS" LOGGING NOCOMPRESS, PARTITION "P3" VALUES LESS THAN (TO_DATE(' "
"2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) "
" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1"
"048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABL"
"ESPACE "CUUG" LOGGING NOCOMPRESS )"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'CUUG' does not exist
Import terminated successfully with warnings.
[oracle@gc1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 10 15:42:15 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create tablespace cuug datafile '/u01/app/oracle/oradata/PROD/disk3/cuug01.dbf' size 50m;
Tablespace created.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@gc1 ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 10 15:43:10 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@gc1 ~]$ imp file=emp3.dmp ignore=y
Import: Release 11.2.0.1.0 - Production on Thu Dec 10 15:43:32 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: scott
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing partition "EMP3":"P1" 1 rows imported
. . importing partition "EMP3":"P2" 2 rows imported
. . importing partition "EMP3":"P3" 1 rows imported
Import terminated successfully without warnings.
[oracle@gc1 ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 10 15:44:01 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from emp3;
ID NAME EFF_DT
---------- -------------------- ---------
1 lilei 13-FEB-15
2 hanmei 11-JUL-15
3 lily 30-DEC-15
4 lucy 03-MAR-16
SQL>
注：導入分區表時，如果分區所在的表空間被刪除，導入時會報錯。
分區表的導入導出：
http://blog.163.com/yanenshun@126/blog/static/1283881692013672149452/
合並分區表
Alter table emp3 merge partitions p1,p2 into partition p3;
分區表合並後，原分區表空間被釋放，原數據均轉移到新的表空間下。
分割分區表
Alter table emp3 split partition p3 at (to_date(‘2015-07-01’,’yyyy-mm-dd’)) to (partition p1,partition p2);
以2015-07-01位分界點將數據分別插入分區表中。
更改分區表名
Alter table emp3 rename partition p3 to p31;
交換表分區：
alter table sales_range exchange partition sales_2000 with table sales_range_temp;
