oracle 分区表，分区索引
1、分区表
一）范围分区
SCOTT@orcl#select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
UNDOTBS2
VPN_HUADAN
TEST1
TEMP01
TEMP001
BIGTABLESPACE
已选择11行。
已用时间: 00: 00: 01.90
SCOTT@orcl#col name for a80
SCOTT@orcl#select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/ORCL/datafile/o1_mf_system_8no75lqw_.dbf
/opt/oracle/oradata/ORCL/datafile/o1_mf_sysaux_8no736vh_.dbf
/opt/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_8no736w0_.dbf
/opt/oracle/oradata/ORCL/datafile/o1_mf_users_8no75m69_.dbf
/opt/oracle/oradata/ORCL/datafile/huadan
/opt/oracle/oradata/sysaux_add.dbf
/opt/oracle/oradata/system_add.dbf
/opt/oracle/oradata/test1.dbf
/opt/oracle/oradata/ORCL/datafile/bigtablespace.dbf
/opt/oracle/oradata/ORCL/datafile/undotbs02.dbf
已选择10行。
已用时间: 00: 00: 00.59
SCOTT@orcl#create tablespace test2 datafile '/opt/oracle/oradata/ORCL/datafile/test2.dbf' size 50m
2 autoextend on next 2M maxsize 100M
3 extent management local autoallocate
4 segment space management auto;
create tablespace test2 datafile '/opt/oracle/oradata/ORCL/datafile/test2.dbf' size 50m
*
第 1 行出现错误:
ORA-19502: 文件 "/opt/oracle/oradata/ORCL/datafile/test2.dbf", 块编号 2944 (块大小=8192) 上出现写入错误
ORA-27072: 文件 I/O 错误
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4
Additional information: 2944
Additional information: 831488
已用时间: 00: 00: 03.08
SCOTT@orcl#!df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 18G 18G 24M 100% /
udev 8.0G 172K 8.0G 1% /dev
shmfs 2.0G 519M 1.5G 26% /dev/shm
/dev/mapper/vgscp-lvscu
2.0G 33M 2.0G 2% /tellin
df: `/tellinshare/sms': Permission denied
.host:/ 180G 177G 3.3G 99% /mnt/hgfs
SCOTT@orcl#l
1 create tablespace test2 datafile '/opt/oracle/oradata/ORCL/datafile/test2.dbf' size 50m
2 autoextend on next 2M maxsize 100M
3 extent management local autoallocate
4* segment space management auto
SCOTT@orcl#save p.sql replace
已写入 file ././p.sql
SCOTT@orcl#exit
从 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
ramoracle@oracle:~> ramn target /
-bash: ramn: command not found
oracle@oracle:~> rman target /
恢复管理器: Release 11.1.0.6.0 - Production on 星期六 5月 18 18:23:14 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
连接到目标数据库: ORCL (DBID=1307032085)
RMAN> list backup of files;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: 分析输入命令时出错
RMAN-01009: 语法错误: 找到 "files": 应为: "archivelog, controlfile, database, datafile, foreign, spfile, tablespace" 中的一个
RMAN-01007: 在第 1 行第 16 列, 文件: standard input
RMAN> list backup by file;
使用目标数据库控制文件替代恢复目录
RMAN> exit
恢复管理器完成。
oracle@oracle:~> sqlplus "/as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on 星期六 5月 18 18:25:11 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@orcl#get p.sql
1 create tablespace test2 datafile '/opt/oracle/oradata/ORCL/datafile/test2.dbf' size 50m
2 autoextend on next 2M maxsize 100M
3 extent management local autoallocate
4* segment space management auto
SYS@orcl#! vi p.sql
create tablespace test2 datafile '/opt/oracle/oradata/ORCL/datafile/test2.dbf' size 50m
autoextend on next 2M maxsize 100M
extent management local autoallocate
segment space management auto
/
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
SYS@orcl#get p.sql
1 create tablespace test2 datafile '/opt/oracle/oradata/ORCL/datafile/test2.dbf' size 50m
2 autoextend on next 2M maxsize 100M
3 extent management local autoallocate
4* segment space management auto
SYS@orcl#r
1 create tablespace test2 datafile '/opt/oracle/oradata/ORCL/datafile/test2.dbf' size 50m
2 autoextend on next 2M maxsize 100M
3 extent management local autoallocate
4* segment space management auto
表空间已创建。
SYS@orcl#1
1* create tablespace test2 datafile '/opt/oracle/oradata/ORCL/datafile/test2.dbf' size 50m
SYS@orcl#c /test2/test3
1* create tablespace test3 datafile '/opt/oracle/oradata/ORCL/datafile/test2.dbf' size 50m
SYS@orcl#r
1 create tablespace test3 datafile '/opt/oracle/oradata/ORCL/datafile/test2.dbf' size 50m
2 autoextend on next 2M maxsize 100M
3 extent management local autoallocate
4* segment space management auto
create tablespace test3 datafile '/opt/oracle/oradata/ORCL/datafile/test2.dbf' size 50m
*
第 1 行出现错误:
ORA-01537: 无法添加文件 '/opt/oracle/oradata/ORCL/datafile/test2.dbf' -
该文件已是数据库的一部分
SYS@orcl#1
1* create tablespace test3 datafile '/opt/oracle/oradata/ORCL/datafile/test2.dbf' size 50m
SYS@orcl#c /test2/test3
1* create tablespace test3 datafile '/opt/oracle/oradata/ORCL/datafile/test3.dbf' size 50m
SYS@orcl#r
1 create tablespace test3 datafile '/opt/oracle/oradata/ORCL/datafile/test3.dbf' size 50m
2 autoextend on next 2M maxsize 100M
3 extent management local autoallocate
4* segment space management auto
表空间已创建。
SYS@orcl#conn scott/root
已连接。
SCOTT@orcl#create table achivement(
2 id number primary key,
3 name varchar2(8),
4 subject varchar2(20),
5 score number
6 )
7 partition by range(score)
8 (
9 partition part1 values less than(60) tablespace test1,
10 partition part2 values less than(80) tablespace test2,
11 partition part3 values less than(maxvalue) tablespace test3
12 );
表已创建。
SCOTT@orcl#insert into archivement values(1,'rhys1','C#',40);
insert into archivement values(1,'rhys1','C#',40)
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
SCOTT@orcl#insert into achivement values(1,'rhys1','c#',40);
已创建 1 行。
SCOTT@orcl#insert into achivement values(2,'rhys2','java',60);
已创建 1 行。
SCOTT@orcl#insert into achivement values(3,'rhys3','c++',70);
已创建 1 行。
SCOTT@orcl#insert into achivement values(4,'rhys4','oracle',120);
已创建 1 行。
SCOTT@orcl#commit;
提交完成。
SCOTT@orcl#select * from achivement ;
ID NAME SUBJECT SCORE
---------- ---------------- ---------------------------------------- ----------
1 rhys1 c# 40
2 rhys2 java 60
3 rhys3 c++ 70
4 rhys4 oracle 120
SCOTT@orcl#select * from achivement partition(part1);
ID NAME SUBJECT SCORE
---------- ---------------- ---------------------------------------- ----------
1 rhys1 c# 40
SCOTT@orcl#select * from achivement partition(part2);
ID NAME SUBJECT SCORE
---------- ---------------- ---------------------------------------- ----------
2 rhys2 java 60
3 rhys3 c++ 70
SCOTT@orcl#select * from achivement partition(part3);
ID NAME SUBJECT SCORE
---------- ---------------- ---------------------------------------- ----------
4 rhys4 oracle 120
二）散列分区
SCOTT@orcl#drop table achivement;
表已删除。
SCOTT@orcl#
SCOTT@orcl#create table achivement(
2 id number primary key,
3 name varchar2(10),
4 subject varchar2(10),
5 score number
6 )
7 partition by hash(id)
8 (
9 partition part1 tablespace test1,
10 partition part2 tablespace test2,
11 partition part3 tablespace test3
12 );
表已创建。
SCOTT@orcl#
SCOTT@orcl#
SCOTT@orcl#
SCOTT@orcl#
SCOTT@orcl#insert into achivement values(1,'rhys1','c#',40);
已创建 1 行。
SCOTT@orcl#insert into achivement values(2,'rhys2','java',60);
已创建 1 行。
SCOTT@orcl#insert into achivement values(3,'rhys3','c++',70);
已创建 1 行。
SCOTT@orcl#insert into achivement values(4,'rhys4','oracle',120);
已创建 1 行。
SCOTT@orcl#commit;
提交完成。
SCOTT@orcl#select * from achivement partition(part1);
未选定行
SCOTT@orcl#select * from achivement;
ID NAME SUBJECT SCORE
---------- -------------------- -------------------- ----------
1 rhys1 c# 40
3 rhys3 c++ 70
4 rhys4 oracle 120
2 rhys2 java 60
SCOTT@orcl#select * from achivement partition(part2);
ID NAME SUBJECT SCORE
---------- -------------------- -------------------- ----------
1 rhys1 c# 40
3 rhys3 c++ 70
4 rhys4 oracle 120
SCOTT@orcl#select * from achivement partition(part3);
ID NAME SUBJECT SCORE
---------- -------------------- -------------------- ----------
2 rhys2 java 60
SCOTT@orcl#insert into achivement values(5,'xiaohai','linux',120);
已创建 1 行。
SCOTT@orcl#insert into achivement values(6,'xiaohai2','unix',100);
已创建 1 行。
SCOTT@orcl#commit;
提交完成。
SCOTT@orcl#select * from achivement partition(part1);
ID NAME SUBJECT SCORE
---------- -------------------- -------------------- ----------
6 xiaohai2 unix 100
SCOTT@orcl#select * from achivement partition(part2);
ID NAME SUBJECT SCORE
---------- -------------------- -------------------- ----------
1 rhys1 c# 40
3 rhys3 c++ 70
4 rhys4 oracle 120
SCOTT@orcl#select * from achivement partition(part3);
ID NAME SUBJECT SCORE
---------- -------------------- -------------------- ----------
2 rhys2 java 60
5 xiaohai linux 120
三）列表分区
SCOTT@orcl#drop table achivement;
表已删除。
SCOTT@orcl#create table achivement(
2 id number primary key,
3 name varchar2(10),
4 subject varchar2(10),
5 score number
6 )
7 partition by list(subject)
8 (
9 partition part1 values('c#','c++') tablespace test1,
10 partition part2 values('oracle','linux','unix') tablespace test2,
11 partition part3 values('java') tablespace test3
12 );
表已创建。
SCOTT@orcl#insert into achivement values(1,'rhys1','c#',40);
已创建 1 行。
SCOTT@orcl#insert into achivement values(2,'rhys2','java',60);
已创建 1 行。
SCOTT@orcl#insert into achivement values(3,'rhys3','c++',70);
已创建 1 行。
SCOTT@orcl#insert into achivement values(4,'rhys4','oracle',120);
insert into achivement values(5,'xiaohai','linux',120);
insert into achivement values(6,'xiaohai2','unix',100);
已创建 1 行。
SCOTT@orcl#
已创建 1 行。
SCOTT@orcl#commit
2 ;
insert into achivement values(6,'xiaohai2','unix',100);commit
*
第 1 行出现错误:
ORA-00911: 无效字符
SCOTT@orcl#insert into achivement values(6,'xiaohai2','unix',100);
已创建 1 行。
SCOTT@orcl#commit;
提交完成。
SCOTT@orcl#select * from achivement;
ID NAME SUBJECT SCORE
---------- -------------------- -------------------- ----------
1 rhys1 c# 40
3 rhys3 c++ 70
4 rhys4 oracle 120
5 xiaohai linux 120
6 xiaohai2 unix 100
2 rhys2 java 60
已选择6行。
SCOTT@orcl#select * from achivement partition(part1);
ID NAME SUBJECT SCORE
---------- -------------------- -------------------- ----------
1 rhys1 c# 40
3 rhys3 c++ 70
SCOTT@orcl#select * from achivement partition(part2);
select * from achivement partition(part3);
ID NAME SUBJECT SCORE
---------- -------------------- -------------------- ----------
4 rhys4 oracle 120
5 xiaohai linux 120
6 xiaohai2 unix 100
SCOTT@orcl#
ID NAME SUBJECT SCORE
---------- -------------------- -------------------- ----------
2 rhys2 java 60
SCOTT@orcl#insert into achivement values(6,'xiaohai2','aix',100);
insert into achivement values(6,'xiaohai2','aix',100)
*
第 1 行出现错误:
ORA-14400: 插入的分区关键字未映射到任何分区
SCOTT@orcl#
SCOTT@orcl#注意了！！！！
SP2-0734: 未知的命令开头 "注意了！！..." - 忽略了剩余的行。
SCOTT@orcl#四）组合范围散列分区
oracle@oracle:~> sqlplus "/as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on 星期六 5月 18 20:19:18 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@orcl#conn scott/root
已连接。
SCOTT@orcl#drop table achivement;
表已删除。
SCOTT@orcl#create table achivement(
2 id number primary key,
3 name varchar2(10),
4 subject varchar2(10),
5 score number)
6 partition by range(score)
7 subpartition by hash(id)
8 subpartition 2 store in(test1,test2)
9 (
10 partition part1 values less than(60),
11 partition part2 values less than(80),
12 partition part3 values less than(120)
13 );
subpartition 2 store in(test1,test2)
*
第 8 行出现错误:
ORA-00905: 缺失关键字
SCOTT@orcl#l
1 create table achivement(
2 id number primary key,
3 name varchar2(10),
4 subject varchar2(10),
5 score number)
6 partition by range(score)
7 subpartition by hash(id)
8 subpartition 2 store in(test1,test2)
9 (
10 partition part1 values less than(60),
11 partition part2 values less than(80),
12 partition part3 values less than(120)
13* )
SCOTT@orcl#8
8* subpartition 2 store in(test1,test2)
SCOTT@orcl#c /subpartition/subpartitions
8* subpartitions 2 store in(test1,test2)
SCOTT@orcl#r
1 create table achivement(
2 id number primary key,
3 name varchar2(10),
4 subject varchar2(10),
5 score number)
6 partition by range(score)
7 subpartition by hash(id)
8 subpartitions 2 store in(test1,test2)
9 (
10 partition part1 values less than(60),
11 partition part2 values less than(80),
12 partition part3 values less than(120)
13* )
表已创建。
SCOTT@orcl#insert into achivement values(1,'rhys1','c#',40);
已创建 1 行。
SCOTT@orcl#insert into achivement values(2,'rhys2','java',60);
insert into achivement values(3,'rhys3','c++',70);
insert into achivement values(4,'rhys4','oracle',120);
已创建 1 行。
SCOTT@orcl#insert into achivement values(5,'xiaohai','linux',120);
已创建 1 行。
SCOTT@orcl#
已创建 1 行。
SCOTT@orcl#select * from achivement;
ID NAME SUBJECT SCORE
---------- -------------------- -------------------- ----------
1 rhys1 c# 40
2 rhys2 java 60
3 rhys3 c++ 70
6 xiaohai2 unix 100
SCOTT@orcl#insert into achivement values(4,'rhys4','oracle',120);
insert into achivement values(4,'rhys4','oracle',120)
*
第 1 行出现错误:
ORA-14400: 插入的分区关键字未映射到任何分区
SCOTT@orcl#insert into achivement values(4,'rhys4','oracle',110);
已创建 1 行。
SCOTT@orcl#insert into achivement values(5,'xiaohai','linux',110);
已创建 1 行。
SCOTT@orcl#
SCOTT@orcl#
SCOTT@orcl#commit;
提交完成。
SCOTT@orcl#select * from achivement partition(part1);
ID NAME SUBJECT SCORE
---------- -------------------- -------------------- ----------
1 rhys1 c# 40
SCOTT@orcl#select * from achivement partition(part2);
select * from achivement partition(part3);
ID NAME SUBJECT SCORE
---------- -------------------- -------------------- ----------
2 rhys2 java 60
3 rhys3 c++ 70
SCOTT@orcl#
ID NAME SUBJECT SCORE
---------- -------------------- -------------------- ----------
6 xiaohai2 unix 100
5 xiaohai linux 110
4 rhys4 oracle 110
SCOTT@orcl#
SCOTT@orcl#
SCOTT@orcl#
五）组合范围列表分区
SCOTT@orcl#
SCOTT@orcl#drop table achivement;
表已删除。
SCOTT@orcl#get p.sql
1 create table achivement(
2 id number,
3 name varchar2(10),
4 subject varchar2(10),
5 score number ,
6 constraint primary_key primary key(id)
7 )
8 partition by range(score)
9 subpartition by hash(id)
10 subpartitions 2 store in (test1,test3)
11 (
12 partition part1 values less than(60) tablespace test1,
13 partition part2 values less than(80) tablespace test2,
14 partition part3 values less than(130) tablespace test3
15* )
SCOTT@orcl#r
1 create table achivement(
2 id number,
3 name varchar2(10),
4 subject varchar2(10),
5 score number ,
6 constraint primary_key primary key(id)
7 )
8 partition by range(score)
9 subpartition by hash(id)
10 subpartitions 2 store in (test1,test3)
11 (
12 partition part1 values less than(60) tablespace test1,
13 partition part2 values less than(80) tablespace test2,
14 partition part3 values less than(130) tablespace test3
15* )
表已创建。
SCOTT@orcl#insert into achivement values(1,'rhys1','c#',40);
已创建 1 行。
SCOTT@orcl#insert into achivement values(2,'rhys2','java',60);
已创建 1 行。
SCOTT@orcl#insert into achivement values(3,'rhys3','c++',70);
已创建 1 行。
SCOTT@orcl#insert into achivement values(4,'rhys4','oracle',110);
已创建 1 行。
SCOTT@orcl#insert into achivement values(5,'xiaohai','linux',110);
已创建 1 行。
SCOTT@orcl#insert into achivement values(6,'xiaohai2','unix',100);
已创建 1 行。
SCOTT@orcl#
SCOTT@orcl#select * from achivement partition(part1);
ID NAME SUBJECT SCORE
---------- -------------------- -------------------- ----------
1 rhys1 c# 40
SCOTT@orcl#select * from achivement partition(part2);
ID NAME SUBJECT SCORE
---------- -------------------- -------------------- ----------
2 rhys2 java 60
3 rhys3 c++ 70
SCOTT@orcl#select * from achivement partition(part3);
ID NAME SUBJECT SCORE
---------- -------------------- -------------------- ----------
5 xiaohai linux 110
6 xiaohai2 unix 100
4 rhys4 oracle 110
SCOTT@orcl#drop table achivement;
表已删除。
SCOTT@orcl#create table achivement(
2 id number primary key,
3 name varchar2(10),
4 subject varchar2(20),
5 score number not null
6 )
7 partition by range(score)
8 subpartition by list(subject)
9 (
10 partition part1 values less than(60)
11 (
12 subpartition part1_1 values('java','c','asp.net') tablespace test1,
13 subpartition part1_2 values('linux','unix') tablespace test1
14 )
15 partition part2 values less than(80)
16 (
17 subpartition part2_1 values('oracle') tablespace test2
18 )
19 );
partition part2 values less than(80)
*
第 15 行出现错误:
ORA-00907: 缺失右括号
SCOTT@orcl#14
14* )
SCOTT@orcl#append ,
14* ),
SCOTT@orcl#r
1 create table achivement(
2 id number primary key,
3 name varchar2(10),
4 subject varchar2(20),
5 score number not null
6 )
7 partition by range(score)
8 subpartition by list(subject)
9 (
10 partition part1 values less than(60)
11 (
12 subpartition part1_1 values('java','c','asp.net') tablespace test1,
13 subpartition part1_2 values('linux','unix') tablespace test1
14 ),
15 partition part2 values less than(80)
16 (
17 subpartition part2_1 values('oracle') tablespace test2
18 )
19* )
表已创建。
SCOTT@orcl#get p.sql
1 create table achivement(
2 id number primary key,
3 name varchar2(10),
4 subject varchar2(20),
5 score number not null
6 )
7 partition by range(score)
8 subpartition by list(subject)
9 (
10 partition part1 values less than(60) tablespace test2
11 (
12 subpartition part1_1 values('java','c','asp.net') tablespace test1,
13 subpartition part1_2 values('linux','unix') tablespace test3
14 ),
15 partition part2 values less than(80) tablespace test1
16 (
17 subpartition part2_1 values('oracle') tablespace test2
18 )
19* )
SCOTT@orcl#r
1 create table achivement(
2 id number primary key,
3 name varchar2(10),
4 subject varchar2(20),
5 score number not null
6 )
7 partition by range(score)
8 subpartition by list(subject)
9 (
10 partition part1 values less than(60) tablespace test2
11 (
12 subpartition part1_1 values('java','c','asp.net') tablespace test1,
13 subpartition part1_2 values('linux','unix') tablespace test3
14 ),
15 partition part2 values less than(80) tablespace test1
16 (
17 subpartition part2_1 values('oracle') tablespace test2
18 )
19* )
表已创建。
SCOTT@orcl#
SCOTT@orcl#insert into achivement values(1,'rhys1','c',40);
已创建 1 行。
SCOTT@orcl#insert into achivement values(2,'rhys2','java',60);
insert into achivement values(2,'rhys2','java',60)
*
第 1 行出现错误:
ORA-14400: 插入的分区关键字未映射到任何分区
SCOTT@orcl#insert into achivement values(3,'rhys3','asp.net',70);
insert into achivement values(4,'rhys4','oracle',110);
insert into achivement values(5,'xiaohai','linux',110);
insert into achivement values(3,'rhys3','asp.net',70)
*
第 1 行出现错误:
ORA-14400: 插入的分区关键字未映射到任何分区
SCOTT@orcl#insert into achivement values(2,'rhys2','java',50);
已创建 1 行。
SCOTT@orcl#insert into achivement values(3,'rhys3','asp.net',50);
已创建 1 行。
SCOTT@orcl#insert into achivement values(5,'xiaohai','linux',40);
已创建 1 行。
SCOTT@orcl#insert into achivement values(6,'xiaohai2','unix',40);
已创建 1 行。
SCOTT@orcl#commit;
提交完成。
SCOTT@orcl#select * from achivement;
ID NAME SUBJECT
---------- -------------------- ----------------------------------------
SCORE
----------
1 rhys1 c
40
2 rhys2 java
50
3 rhys3 asp.net
50
ID NAME SUBJECT
---------- -------------------- ----------------------------------------
SCORE
----------
5 xiaohai linux
40
6 xiaohai2 unix
40
SCOTT@orcl#r
1* select * from achivement
ID NAME SUBJECT
---------- -------------------- ----------------------------------------
SCORE
----------
1 rhys1 c
40
2 rhys2 java
50
3 rhys3 asp.net
50
ID NAME SUBJECT
---------- -------------------- ----------------------------------------
SCORE
----------
5 xiaohai linux
40
6 xiaohai2 unix
40
SCOTT@orcl#set linesize 200
SCOTT@orcl#r
1* select * from achivement
ID NAME SUBJECT SCORE
---------- -------------------- ---------------------------------------- ----------
1 rhys1 c 40
2 rhys2 java 50
3 rhys3 asp.net 50
5 xiaohai linux 40
6 xiaohai2 unix 40
SCOTT@orcl#insert into achivement values(4,'rhys4','oracle',70);
已创建 1 行。
SCOTT@orcl#commit;
提交完成。
SCOTT@orcl#select * from achivement;
ID NAME SUBJECT SCORE
---------- -------------------- ---------------------------------------- ----------
1 rhys1 c 40
2 rhys2 java 50
3 rhys3 asp.net 50
5 xiaohai linux 40
6 xiaohai2 unix 40
4 rhys4 oracle 70
已选择6行。
SCOTT@orcl#select * from achivement partition(part1);
ID NAME SUBJECT SCORE
---------- -------------------- ---------------------------------------- ----------
1 rhys1 c 40
2 rhys2 java 50
3 rhys3 asp.net 50
5 xiaohai linux 40
6 xiaohai2 unix 40
SCOTT@orcl#select * from achivement partition(part1_1);
select * from achivement partition(part1_1)
*
第 1 行出现错误:
ORA-02149: 指定的分区不存在
SCOTT@orcl#select * from achivement subpartition(part1_1);
ID NAME SUBJECT SCORE
---------- -------------------- ---------------------------------------- ----------
1 rhys1 c 40
2 rhys2 java 50
3 rhys3 asp.net 50
SCOTT@orcl#select * from achivement subpartition(part1_2);
ID NAME SUBJECT SCORE
---------- -------------------- ---------------------------------------- ----------
5 xiaohai linux 40
6 xiaohai2 unix 40
SCOTT@orcl#select * from achivement partition(part2);
ID NAME SUBJECT SCORE
---------- -------------------- ---------------------------------------- ----------
4 rhys4 oracle 70
SCOTT@orcl#select * from achivement subpartition(part2_1);
ID NAME SUBJECT SCORE
---------- -------------------- ---------------------------------------- ----------
4 rhys4 oracle 70
2、分区索引
一）部分分区索引
SCOTT@orcl#
SCOTT@orcl#drop index achivement_index;
索引已删除。
SCOTT@orcl#drop table achivement;
表已删除。
SCOTT@orcl#create table achivement(
2 id number,
3 name varchar2(20),
4 subject varchar2(20),
5 score number
6 )
7 partition by range(score)
8 (
9 partition part1 values less than(70) tablespace test1,
10 partition part2 values less than(80) tablespace test2,
11 partition part3 values less than(maxvalue) tablespace test3
12 );
表已创建。
SCOTT@orcl#create index part_index
2 on achivement(id)
3 locate
4 (
5 partition index1 tablespace test1,
6 partition index2 tablespace test2,
7 partition index3 tablespace test3
8 );
locate
*
第 3 行出现错误:
ORA-02158: 无效的 CREATE INDEX 选项
SCOTT@orcl#3
3* locate
SCOTT@orcl#create index part_index
2 on achivement(id)
3 local
4 (
5 partition index1 tablespace test1,
6 partition index2 tablespace test2,
7 partition index3 tablespace test3
8 );
索引已创建。
SCOTT@orcl#
二）全局分区索引
SCOTT@orcl#drop index part_index;
索引已删除。
SCOTT@orcl#create index global_index
2 on achivement(id)
3 global partition by range(id)
4 (
5 partition index1 values less than (10000) tablespace test1,
6 partition index2 values less than (20001) tablespace test2,
7 partition index3 values less than(maxvalue) tablespace test3
8 );
索引已创建。
SCOTT@orcl#drop index global_index;
索引已删除。
三）全局非分区索引
SCOTT@orcl#create index no_part_global_index on achivement(id);
索引已创建。
SCOTT@orcl#drop index no_part_global_index;
索引已删除。
SCOTT@orcl#select a.table_name,a.partitioning_type,b.object_type,b.column_name
2 from user_part_tables a ,user_part_key_columns b where a.table_name=b.name;
TABLE_NAME PARTITIONING_TYPE OBJECT_TYP
------------------------------------------------------------ ------------------ ----------
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ACHIVEMENT RANGE TABLE
SCORE
BIN$3P4ccOeGW/vgQKjABIkDGQ==$0 LIST TABLE
SUBJECT
BIN$3P4ccOeJW/vgQKjABIkDGQ==$0 RANGE TABLE
SCORE
TABLE_NAME PARTITIONING_TYPE OBJECT_TYP
------------------------------------------------------------ ------------------ ----------
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BIN$3P4ccOeMW/vgQKjABIkDGQ==$0 RANGE TABLE
SCORE
BIN$3P4ccOeQW/vgQKjABIkDGQ==$0 RANGE TABLE
SCORE
BIN$3P4ccOeUW/vgQKjABIkDGQ==$0 RANGE TABLE
SCORE
TABLE_NAME PARTITIONING_TYPE OBJECT_TYP
------------------------------------------------------------ ------------------ ----------
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BIN$3Py1XNsEZTXgQKjABIl/WQ==$0 RANGE TABLE
SCORE
BIN$3Py1XNsHZTXgQKjABIl/WQ==$0 HASH TABLE
ID
已选择8行。
SCOTT@orcl#col table_name for a20
SCOTT@orcl#col column_name for a30
SCOTT@orcl#r
1 select a.table_name,a.partitioning_type,b.object_type,b.column_name
2* from user_part_tables a ,user_part_key_columns b where a.table_name=b.name
TABLE_NAME PARTITIONING_TYPE OBJECT_TYP COLUMN_NAME
-------------------- ------------------ ---------- ------------------------------
ACHIVEMENT RANGE TABLE SCORE
BIN$3P4ccOeGW/vgQKjA LIST TABLE SUBJECT
BIkDGQ==$0
BIN$3P4ccOeJW/vgQKjA RANGE TABLE SCORE
BIkDGQ==$0
BIN$3P4ccOeMW/vgQKjA RANGE TABLE SCORE
BIkDGQ==$0
BIN$3P4ccOeQW/vgQKjA RANGE TABLE SCORE
TABLE_NAME PARTITIONING_TYPE OBJECT_TYP COLUMN_NAME
-------------------- ------------------ ---------- ------------------------------
BIkDGQ==$0
BIN$3P4ccOeUW/vgQKjA RANGE TABLE SCORE
BIkDGQ==$0
BIN$3Py1XNsEZTXgQKjA RANGE TABLE SCORE
BIl/WQ==$0
BIN$3Py1XNsHZTXgQKjA HASH TABLE ID
BIl/WQ==$0
已选择8行。
SCOTT@orcl#
SCOTT@orcl#
3、管理分区表
SCOTT@orcl#alter table achivement add partition part4 values less than(150) tablespace test1;
alter table achivement add partition part4 values less than(150) tablespace test1
*
第 1 行出现错误:
ORA-14074: 分区界限必须调整为高于最后一个分区界限
SCOTT@orcl#drop table achivement;
表已删除。
SCOTT@orcl#create table achivement(
2 id number,
3 name varchar2(10),
4 subject varchar2(20),
5 score number,
6 constraint primary_key primary key(id)
7 )
8 partition by range (score)
9 (
10 partition part1 values less than(60) tabelspace test1,
11 partition part2 values less than(80) tablespace test2
12 );
partition part1 values less than(60) tabelspace test1,
*
第 10 行出现错误:
ORA-14020: 不能指定表分区的此物理属性
SCOTT@orcl#10
10* partition part1 values less than(60) tabelspace test1,
SCOTT@orcl#c /tabelspace/tablespace
10* partition part1 values less than(60) tablespace test1,
SCOTT@orcl#r
1 create table achivement(
2 id number,
3 name varchar2(10),
4 subject varchar2(20),
5 score number,
6 constraint primary_key primary key(id)
7 )
8 partition by range (score)
9 (
10 partition part1 values less than(60) tablespace test1,
11 partition part2 values less than(80) tablespace test2
12* )
表已创建。
SCOTT@orcl#alter table achivement add partition part4 values less than(120) tablespace test3;
表已更改。
SCOTT@orcl#alter table achivement add partition part5 values less than(180) tablespace test2;
表已更改。
SCOTT@orcl#alter table achivement
2 split partition part4 at(100)
3 into
4 (
5 partition part6 tablespace test2,
6 partition part7 tablespace test3
7 );
表已更改。
SCOTT@orcl#alter table achivement
2 split partition part5 at(150)
3 into
4 (
5 partition part8 tablespace test1,
6 partition part9 tablespace test3
7 );
表已更改。
SCOTT@orcl#drop table achivement;
表已删除。
SCOTT@orcl#
SCOTT@orcl#
SCOTT@orcl#
SCOTT@orcl#create table achivement(
2 id number primary key,
3 name varchar2(10),
4 subject varchar2(20),
5 score number)
6 partition by hash(id)
7 (
8 partition part1 tablespace test1,
9 partition part2 tablespace test2,
10 partition part3 tablespace test3
11 );
表已创建。
SCOTT@orcl#alter table achivement add partition part4 tablespace test1;
表已更改。
SCOTT@orcl#alter table achivement add partition part6 tablespace test2;
表已更改。
SCOTT@orcl#alter table achivement merge partitions part1,part2 into partition part3;
alter table achivement merge partitions part1,part2 into partition part3
*
第 1 行出现错误:
ORA-14255: 未按范围, 列表, 组合范围或组合列表方法对表进行分区
SCOTT@orcl#insert into achivement values(1,'rhys1','c',40);
已创建 1 行。
SCOTT@orcl#insert into achivement values(2,'rhys2','java',50);
已创建 1 行。
SCOTT@orcl#insert into achivement values(3,'rhys3','asp.net',50);
已创建 1 行。
SCOTT@orcl#insert into achivement values(4,'rhys4','oracle',70);
已创建 1 行。
SCOTT@orcl#insert into achivement values(5,'xiaohai','linux',40);
已创建 1 行。
SCOTT@orcl#
SCOTT@orcl#
SCOTT@orcl#
SCOTT@orcl#commit;
提交完成。
SCOTT@orcl#select * from achivement partition(part1);
未选定行
SCOTT@orcl#select * from achivement partition(part2);
未选定行
SCOTT@orcl#select * from achivement partition(part3);
ID NAME SUBJECT SCORE
---------- -------------------- ---------------------------------------- ----------
2 rhys2 java 50
5 xiaohai linux 40
SCOTT@orcl#select * from achivement partition(part4);
ID NAME SUBJECT SCORE
---------- -------------------- ---------------------------------------- ----------
1 rhys1 c 40
3 rhys3 asp.net 50
4 rhys4 oracle 70
SCOTT@orcl#drop table achivement partition part4;
drop table achivement partition part4
*
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束
SCOTT@orcl#alter table achivement drop partition part4;
alter table achivement drop partition part4
*
第 1 行出现错误:
ORA-14255: 未按范围, 列表, 组合范围或组合列表方法对表进行分区
SCOTT@orcl#drop table achivement;
表已删除。
SCOTT@orcl#create table achivement(
2 id number primary key,
3 name varchar2(10),
4 subject varchar2(20),
5 score number
6 )
7 partition by list(subject)
8 (
9 partition part1 values('c','asp.net') tablespace test1,
10 partition part2 values('java','oracle','linux') tablespace test2
11 );
表已创建。
SCOTT@orcl#alter table achivement add partition part3 values('unix') tablespace test2;
表已更改。
SCOTT@orcl#insert into achivement values(1,'rhys1','c',40);
已创建 1 行。
SCOTT@orcl#insert into achivement values(2,'rhys2','java',50);
insert into achivement values(3,'rhys3','asp.net',50);
insert into achivement values(4,'rhys4','oracle',70);
insert into achivement values(5,'xiaohai','linux',40);
insert into achivement values(6,'xiaohai2','unix',40);
commit;
已创建 1 行。
SCOTT@orcl#
已创建 1 行。
SCOTT@orcl#
已创建 1 行。
SCOTT@orcl#
已创建 1 行。
SCOTT@orcl#
已创建 1 行。
SCOTT@orcl#
提交完成。
SCOTT@orcl#select * from achivement partition(part1);
ID NAME SUBJECT SCORE
---------- -------------------- ---------------------------------------- ----------
1 rhys1 c 40
3 rhys3 asp.net 50
SCOTT@orcl#select * from achivement partition(part2);
select * from achivement partition(part3);
ID NAME SUBJECT SCORE
---------- -------------------- ---------------------------------------- ----------
2 rhys2 java 50
4 rhys4 oracle 70
5 xiaohai linux 40
SCOTT@orcl#
ID NAME SUBJECT SCORE
---------- -------------------- ---------------------------------------- ----------
6 xiaohai2 unix 40
SCOTT@orcl#alter table achivement merge partitions part3,part4 into partition part3;
alter table achivement merge partitions part3,part4 into partition part3
*
第 1 行出现错误:
ORA-02149: 指定的分区不存在
SCOTT@orcl#alter table achivement merge partitions part2,part3 into partition part2;
表已更改。
SCOTT@orcl#select * from achivement partition(part2);
ID NAME SUBJECT SCORE
---------- -------------------- ---------------------------------------- ----------
2 rhys2 java 50
4 rhys4 oracle 70
5 xiaohai linux 40
6 xiaohai2 unix 40
SCOTT@orcl#alter table achivement drop partition part2;
表已更改。
SCOTT@orcl#select * from achivement;
ID NAME SUBJECT SCORE
---------- -------------------- ---------------------------------------- ----------
1 rhys1 c 40
3 rhys3 asp.net 50
SCOTT@orcl#
99
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
