Oracle使用学习总结
Oracle使用学习经验总结
张浩博客
sql语句部分
1.新型企业级智能数据管理系统---SybaseASE12.5（AdaptiveServerEn
terprise）
2.**17260,"Can"trun%1!fromwithinatransaction."
3.在创建存储过程CREAT之前编写存储过程德错误编号说明4.charindex("sa_role",show_role())
5.begin
6.@@trancount
Oracle系统安装配置
1.修改sybase数据库sa用户密码
2.3.env查看环境变量
4.linux中.bash_profile和.bashrc的什么区别5.数据库中执行“存储”过程。
6.bcp导入、导出数据,备份数据库、备份表格数据、备份数据
●bcp
7.附录1、英文错误码描述：（灵活运用！！！）
1．Auserwiththesamenamealreadyexistsinthedatabase在数据库中已经存在相同的一个用户2．isnotavalidname不是一个可用的名字
3．Useralreadyhasaloginunderadifferentname.用户已经用一个不同的名字登录了4．Nologinwiththespecifiednameexists没有登录
5．Timerangenamemustbenon-NULL时间范围名字必须不为空6．Limittypemustbenon-NULL限制类型必须不为空7．Unknownlimittype不可知的限制类型8．Ge获得开始结束时间从当前的范围9．
8.附录2、错误语句：
9.附录3、错误语句：
10.附录4、错误语句：
11.附录5、错误语句：自我总结书籍和网站（建议经常去看看，呵呵）
1.2.3.4.5.
扩展阅读：有关Oracle学习总结
表xyzabc1mhj1mhjk1njk2uwe2uwert3qs4ads4adsaa
怎么删掉第1、5、8条记录？（字段a、b相同的几条记录只留一条，留下字段C较短的一条）
DELETEFROMLIANXI
WHERELENGTH(C)NOTIN(SELECTMIN(LENGTH(C))FROMLIANXIGROUPBYA,B)
oracle数据库常用的命令集锦
今日开始研究oracle,搜索到了一个好东东，拿出来与大家一同分享。下面是摘抄的部分：[local]2[/local]
ORACLE相关语法及命令一、Oracle入门理论知识：
Oracle的物理组件有三个：
(1)数据文件数据文件是用于存储数据库数据的文件，如表、索引数据。每个Oracle数据库有一个或多个物理数据文件，一个数据文件只能与一个数据库关联。(2)日志文件用于记录对数据库进行的修改信息，日志文件主要用于在数据库出现故障时实施数据库恢复。
(3)控制文件控制文件是记录数据库物理结构的二进制文件，每个Oracle数据库都含有一个控制文件。
Oracle的逻辑组件：
表空间(TableSpace)表空间是数据库最大的逻辑单位，一个数据库至少包含一个表空间，一个表空间包含一个或多个段等等。段(Segment)段存在于表空间中，分成4类，数据段、索引段、回退段、临时段。区(Extent)区是磁盘空间分配最小单位，由连续的数据块组成，一个或多个区构成段，区只能存在于一个数据文件中。
数据块(DataBlock)数据块是数据库中最小的数据组织单位与管理单位，Oracle数据库中的数据存储于数据块中，取值范围2K-64K之间。
模式(schema)模式是对用户所创建的数据库对象的总称，又称为用户模式。概念：
内存Oracle内存结构包含以下两个内存区。
1、系统全局区(SGA)实例启动时分配该内存区，是Oracle实例的一个基本组件。又称为共享全局区，它用来存储数据库信息，并由多个数据库进程共享。可分为共享池、数据缓冲区及日志缓冲区。
(1)共享池是对SQL、PL\\SQL程序进行语法分析、编译、执行的内存区域。共享池由库缓存和数据字典缓存组成。其中，库缓存含有最近执行的SQL、PL\\SQL语句的分析码和执行计划；数据字典缓存含有从数据字典中得到的表、索引、列定义和权限等信息。(2)数据缓冲区数据缓冲区用于存储从磁盘数据文件中读入的数据，所有用户共享。(3)日志缓冲区日志记录数据库的所有修改信息，主要用于恢复数据。
2、程序全局区(PGA)服务器进程启动时分配该内存区。PGA为非共享区，只能单个进程使用，当一个用户会话结束后，PGA释放。
用户进程(PGA)发送SQL语句到共享全局区(SGA)，先在共享池的库缓存中查询是否存在所需的数据块，如果存在就在数据字典中读取相应的数据块，如果不存在就由服务器进程(DBWR)来IO数据库
语法知识：
创建表空间的语法如下：
CREATETABLESPACEtablespacenameDATAFILE"d:\\filename.DBF"[SIZEint[KB|MB]][AUTOEXTEND[OFF|ON]];
tablespacename是需创建的表空间名称。
DATAFILE指定组成表空间的一个或多个数据文件，当有多个数据文件时使用逗号分隔。filename是表空间中数据文件的路径和名称。
SIZE指定文件的大小，用K指定千字节大小，用M指定兆字节大小。AUTOEXTEND子句用来启用或禁用数据文件的自动扩展。Oracle默认用户：
用户名：sys默认密码：chage_on_install用来管理拥有Oracle数据字典文件用户名：system默认密码：manager用来管理拥有数据字典视图对象用户名：scott默认密码：tiger示例用户，包括emp、dept等表连接Oracle:在控制台下输入sqlplus用户名/密码回车或sqlplusw回车相关命令：
disconn//退出当前登录conn用户名/密码//连接Oracle
alteruser用户名identifiedby密码//修改用户口令dropuser用户名cascade;//删除用户
alteruser用户名accountlock;//给某个用户加锁alteruser用户名accountunlock;//给某个用户解锁ed回车://打开缓冲区
/回车://执行缓冲区中的语句
createuser用户名identifiedby密码[passwordexpire]
[defaulttablespace表空间名][temporarytablespace临时表空间名];//创建用户相关权限：
grantconnecttoscott;//connect角色将允许用户创建数据库并在数据库中创建表或其他对象grantresourcetoscott;//resource角色将允许用户使用数据库中的空间grantcreatesequencetoscott;//createsequence权限将允许用户创建序列，此权限包含在connect连接角色中
grantselectonemptoscott;//将emp表的查询权限授予用户scott
grantupdate(vencode,venname)on表名toscott;//将特定列的更新权限授予用户scottgrant权限on表名to用户名withgrantoption;//接受该权限的用户可以将此权限授予其他用户
revokeselect,updateon表名from用户名;//收回相应的权限二、SQL查询和SQL函数SQL支持如下类别的命令：
数据定义语言：create(创建)、alter(更改)、drop(删除)和truncate(截断)命令。数据操纵语言：insert(插入)、select(选择)、delete(删除)和update(更新)命令。事务控制语言：commit(提交)、savepoint(保存点)和rollback(回滚)命令。数据控制语言：grant(授予)和revoke(回收)命令。数据类型：
char:长度在1到201*个字节，声明多少字节在内存中就占用多少字节，输入的值小于指定的长度时用空格填充。
varchar2:长度在1到4000个字节，输入的值是多少字节，就占用多少字节。
long:长度在2GB，设置为此类型的列时，要注意：一个表中只有一列可以为long类型，long类型列不能定义为唯一约束或主键约束，不能建立索引，过程或存储过程不能接受long类型的参数。
number(p,s):其中p为精度，表示数字的总位数，在1至38之间。s为范围，表示小数点右边数字的位数，在-84至127之间。
date:日期类型，sysdate为当前系统时间。格式为08-9月-07。
timestamp:用于存储日期的年、月、日以及时间的时、分和秒。其中秒精确到小数点后6位，
systimestamp返回当前日期、时间。格式为08-9月-0704.08.30.000000下午。
raw:此数据类型用于存储基于字节的数据，如二进制数据或字节串，该类型最多能存储201*个字节，可以建立索引。
longraw:此数据类型用于可变长度的二进制数据，最多能存储2GB。long数据类型的所有限制对longraw数据类型也同样有效。
lob又称为"大对象"数据类型，最多能存储4GB的非结构化信息。包括：
clob:clob代表CharacterLOB(字符LOB)，它能存储大量字符数据。如XML文档。blob:blob代表BinaryLOB(二进制LOB)，它能存储较大的二进制对象，如图形、视频剪辑和声音剪辑。
bfile:bfile代表BinaryFile(二进制文件)，它能够将二进制文件存储在数据库外部的操作系统文件中。伪列：
rowid:selectrowid,ename,fromscott.empwhereempno="7900";
rownum:select*fromscott.empwhererownumaltertable表名dropcolumn列名;//删除列
truncatetable表名;//中删除记录而不删除结构，不使用事务处理，因此无法回滚droptable表名;//删除表及其全部数据
createtable新表名asselect*from表名where1=2;//用现有的表创建一个新表selectdeptno*2"NewNo",dname,locfromdept;//指定一个含有特殊字符(如空格)的列标题
commit;//提交事务
savepoint标记名;//标记事务点rollback;//回滚整个事务处理
rollbackto[savepoint]标记名;//回滚到事务中某个特定的保存点集合操作符：
union(联合):此操作符返回两个查询选定的所有不重复的行。
语法selectordernofromorder_masterUNIONselectordernofromorder_detail;unionall(联合所有):此操作符合并两个查询选定的所有行，包括重复的行。
语法：selectorderno,enamefromorder_masterUNIONALLselectorderno,pronamefromorder_detailorderby2;
注意：在两个select语句中指定的列名不必相同，但数据类型必须匹配。也可以对联合查询的结果进行排序，使用OrderBy子句时，它必须放在最后
一个select语句之后，而且必须指定列索引来排序，而不是指定列名，列索引是从1开始的整数。上述语法便是以proname的索引排序
intersect(交集):此操作符只返回两个查询都有的行。
语法：selectordernofromorder_masterINTERSECTselectordernofromorder_detail;
minus(减集):此操作符中返回由第一个查询选定但是第二个查询中没有选定的行，也就是在第一个查询结果中排除第二个查询结果中出现的行。
语法：selectordernofromorder_masterMINUSselectordernofromorder_detail;查询尚未交付的订单
连接(||)操作符：
语法：select("供应商"||venname||"的地址是"||venadd1||""||venadd2||""||venadd3)地址fromvendor_masterwherevencode="V002";将多个字符串合并为一个字符串。SQL函数：
1、日期函数：
add_months:此函数返回给指定的日期加上指定的月数后的日期值。语法为add_months(d,n),其中d是日期，n表示月数。
示例：selectadd_months(sysdate,2)fromdual;将当前时间加上2个月后的日期值。months_between:此函数返回两个日期之间的月数。语法为months_between(d1,d2),其中d1和d2是日期，如果d1大于d2，则结果为正数；否则为负数。
last_day:此函数返回指定日期当月的最后一天的日期值，语法为last_day(d),其中d表示日期。
示例：selectlast_day(sysdate)fromdual;返回当前日期的月的最后一天，如果是9月就返回30-09月-07
round:此函数返回日期值，将日期四舍五入为格式模型指定的单位。语法为round(d,[fmt])。其中d是日期，fmt是格式模型。fmt是一个可选项，日期默认舍入为最靠近的那一天。如果指定格式为年"Year"，则舍入到年的开始，即1月1日;如果格式为月"Month"，则舍入到月的第一日;如果格式为周"Day"，则舍入到最靠近的星期日。示例：selectround(sysdate,"month")fromdual;返回最接近的一个月。
next_day:此函数返回指定的下一个星期几的日期。语法为next_day(d,day)。其中d表示日期，而day指周内任何一天。
示例：selectnext_day(sysdate,"星期日")fromdual;返回下一个星期日的日期，也可以用1表示，以此类推，星期一以2表示。
trunc:此函数将指定日期截断为由格式模型指定的单位日期，与Round函数不同的是它只舍不入，语法为trunc(d,[fmt])，与round格式相同。
示例：selecttrunc(sysdate,"year")fromdual;返回当前年的第一天，也就是1月1日。示例：selecttrunc(sysdate,"day")fromdual;返回紧靠前面的星期日。如果为"201*年1月27日"就会返回"201*年1月23日"。
extract:此函数提取日期时间类型中的特定部分。语法为extract(fmtfromd)，其中d是日期时间表达式，fmt是要提取的部分的格式。格式的取值可以是year,month,day,hour,minute,second,注意此处的格式不使用单引号。示例：selectextract(yearfromsysdate)fromdual;返回当前的年份。2、字符函数：
initcap(char):首字母大写，示例：selectinitcap("hello")fromdual;输出结果:Hello。lower(char):转换为小写，示例：selectlower("FUN")fromdual;输出结果:fun。upper(char):转换为大写，示例：selectupper("sun")fromdual;输出结果:SUN。
ltrim(char,set):左剪裁，示例：selectltrim("xyzadams","xyz")fromdual;输出结果:adams。rtrim(char,set):右剪裁，示例：selectrtrim("xyzadams","ams")fromdual;输出结果:xyzad。translate(char,from,to):按字符翻译，示例：selecttranslate("jack","abcd","1234")fromdual;输出结果:j13k。
replace(char,search_str,replace_str):字符串替换，示例：selectreplace("jackandjue","j","bl")fromdual;输出结果:blackandblue。
instr(char,substr[,pos1,pos2]):查找子字串位置。
示例：selectinstr("vorldwide","d")fromdual;输出结果:5。pos1为可选，表示从第几个位置查找。pos2为可选，表示从第几次出现的位置找。substr(char,pos,len):取子字符串，示例：selectsubstr("abcdefg",3,2)fromdual;输出结果:cd。concat(char1,char2):连接字符串，示例：selectconcat("Hello","world")fromdual;输出结果:Helloworld。
chr:此函数根据Ascii码返回对应的字符，示例：selectchr(45788),chr(53671),chr(50167),chr(65)fromdual;输出结果:曹学明A。
ascii:此函数返回GBK编码值，示例：selectascii("曹")cao,ascii("学")xue,ascii("明")Mingfromdual;输出结果:457885367150167。
lpad和rpad：示例：selectlpad("function",15,"=")fromdual;输出结果:=======function。而rpad则相反，字符串填充在右边。
trim:此函数从字符串的开头或结尾(或开头和结尾)剪裁特定的字符，默认剪裁空格。如果加上leading选项时与ltrim函数相似。指定trailing时和rtrim函数相似。示例：selecttrim(9from999992598899)fromdual;输出结果:25988。
示例：selecttrim(leading9from999992598899)fromdual;输出结果:2598899。示例：selecttrim(trailing9from999992598899)fromdual;输出结果:9999925988。
length:此函数返回字符串的长度，示例：selectlength("frances")fromdual;输出结果:7。decode:示例：selectdeptno,dname,decode(loc,"NEWYORK","纽约","BOSTON","波士顿")fromscott.dept;此示例将替换显示loc列的结果,结果为"NEWYORK"的替换为"纽约"，"BOSTON"的替换为"波士顿"。
GREATEST/least:返回一组表达式中的最大值/最小值,即比较字符的编码大小.示例：selectgreatest("AA","AB","AC")fromdual;输出结果:AC。selectleast("AA","AB","AC")fromdual;输出结果:AA。selectgreatest("啊","安","天")fromdual;输出结果:天。selectleast("啊","安","天")fromdual;输出结果:啊。3、数字函数：
abs(n):取绝对值，示例：selectabs(-15)fromdual;输出结果:15。ceil(n):向上取整，示例：selectceil(44.778)fromdual;输出结果:45。sign(n):取符号，示例：selectsign(-2)fromdual;输出结果:-1。
floor(n):向下取整，示例：selectfloor(200.88)fromdual;输出结果:200。power(m,n):m的n次幂，示例：selectpower(5,3)fromdual;输出结果:125。mod(m,n):取余数，示例：selectmod(10,3)fromdual;输出结果:1。
round(m,n):四舍五入，示例：selectround(100.256,2)fromdual;输出结果:100.26。trunc(m,n):截断，示例：selecttrunc(100.256,2)fromdual;输出结果:100.25。sqrt(n):平方根，示例：selectsqrt(4)fromdual;输出结果:2。4、转换函数：
to_char(d|n[,fmt]):其中d是日期，n是数字，fmt指定日期或数字的格式。
示例：selectto_char(sysdate,"yyyy"年"fmmm"月"fmdd"日"hh24:mi:ss")fromdual;输出结果:201*年9月09日20:44:27。
selectto_char(sysdate,"yyyy/mm/ddhh24:mi:ss")fromdual;selectto_char(sysdate,"yyyy-mm-ddhh24:mi:ss")fromdual;
selectto_char(sysdate,"yyyy"年"mm"月"dd"日"hh24"时"mi"分"ss"秒"")fromdual;示例：selectto_char(sal,"$99999")fromemp;输出结果:$1600。
to_date(char[,fmt]):此函数将char或varchar2数据类型转换为日期数据类型。示例：selectto_date("201*-12-06","yyyy-mm-dd")fromdual;输出结果:06-12月-05。selectto_date("201*/08/1020:08:08","yyyy/mm/ddhh24:mi:ss")fromdual;selectto_date("201*-08-1020:08:08","yyyy/mm/ddhh24:mi:ss")fromdual;
selectto_date("201*年08月10日20时08分08秒","yyyy"年"mm"月"dd"日"hh24"时"mi"分"ss"秒"")fromdual;
to_number(char):此函数将包含数字的字符串转换为number数据类型，通常不用这么做，因为Oracle可以对数字字符串进行隐式转换。
示例：selectsqrt(to_number("100"))fromdual;输出结果:10。5、其它函数：
nvl(expression1,expression2):如果expression1为NULL，则nvl返回expression2。
nvl2(expression1,expression2,expression3):如果expression1不是NULL，则nvl2返回expression2，如果expression1是NULL，则返回expression3。nullif(expr1,expr2):此函数比较两个表达式，如果它们相等，则返回空值，否则返回expr1。nullif函数等价于以下的case表达式：
casewhenexpr1=expr2thennullelseexpr1end6、分组函数：
avg:此函数返回指定列值的平均值，示例：selectavg(sal)fromemp;输出结果:2073.21429。min:此函数返回指定列值的最小值，示例：selectmin(sal)fromemp;输出结果:800。max:此函数返回指定列值的最大值，示例：selectmax(sal)fromemp;输出结果:5000。sum:此函数返回指定列值的总和，示例：selectsum(sal)fromemp;输出结果:29025。count:此函数是为了计算行数，它可以接受3种不同的参数示例：selectcount(*)fromemp;输出结果:14。示例：selectcount(列名)from表名;
示例：selectcount(distinct列名)from表名;
groupby:此子句用于将信息表划分为组，按组进行聚合运算。select后面跟的列名只能是分组函数、groupby子句中出现的列或表达式。
示例：selectdeptno,max(sal)fromempgroupby(deptno);查出每个部门的最高工资。示例：selectdeptno,count(*),sum(sal)fromscott.empgroupbydeptno;//groupby主要用来对一组数进行统计
having:此子句用来指定groupby子句的检索条件。
示例：selectdeptno,count(*),sum(sal)fromscott.empgroupbydeptnohavingcount(*)>=5;//having对分组统计再加限制条件
7、分析函数：只能出现在select列表或orderby子句中。
row_number:下面对所有员工的工资进行排名，即使工资相同，其排名也不能相同。示例：selectename,job,deptno,sal,row_number()over(orderbysaldesc)as排名fromscott.emp;
下面对所有员工的工资按部门进行排名，即使工资相同，排名也不同。
示例：selectename,job,deptno,sal,row_number()over(partitionbydeptnoorderbysaldesc)as排名fromscott.emp;
rank:此函数计算一个值在一组值中的排位，排位是以1开头的连续整数，如果两行的序数为1，则没有序数2，下行的序数为3。
下面根据员工的工资和佣金对员工在每个部门中进行排位。相同的工资排位相同，并且排位不连续。
示例：selectename,sal,comm,deptno,rank()over(partitionbydeptnoorderbysaldesc,comm)排名fromscott.emp;
dense_rank:此函数计算一个行在一组有序行中的排位，排位是以1开头的连续整数，具有相同值的排位相同，并且排位是连续的。
下面首先选择所有在accounting或research部门中工作的员工的部门名称、员工姓名和工资，
然后分别计算每个员工的工资在部门中的排位，相等的工次排位相同示例：selectd.dname,e.ename,e.sal,dense_rank()over(partitionbye.deptnoorderbye.saldesc)排名
fromempe,deptdwheree.deptno=d.deptno;
三、锁和表分区
锁定是数据库用来控制共享资源并发访问的机制。
Oracle提供以确保在多用户环境下数据的完整性和一致性。锁的两种级别：(只有在提交或回滚后才能释放锁定)
(1)行级锁：是一种排他锁，防止其他事务修改此行，但是不会阻止读取此行的操作。在使用Insert、Update、Delete和Select...ForUpdate等语句时，Oracle会自动应用行级锁定。Select...ForUpdate语法为：
Select...ForUpdate[OFcolumn_list][WAITn|NOWAIT]
其中：OF子句用于指定即将更新的列，即锁定行上的特定列。WAIT子句指定等待其他用户释放锁的秒数，防止无限期的等待。NOWAIT为不等待。示例1：演示如何锁定deptno值为10的所有行。
select*fromdeptwheredeptno=10forupdateofdname,loc;
(2)表级锁：将保护表数据，在事务处理过程中，表级锁会限制对整个表的访问。表级锁用来限制对表执行添加、更新和删除等修改操作。语法：
LOCKTABLEINMODE[NOWAIT];其中：table_name是要被锁定的表的名称。lock_mode是锁定的模式。表级锁的模式：
1、行共享(ROWSHARE,RS)：允许其他用户访问和锁定该表，但是禁止排他锁锁定整个表。
2、行排他(ROWEXCLUSIVE,RX)：与行共享模式相同，同时禁止其他用户在此表上使用共享锁。使用Select...Forupdate语句会自动应用行排他。
3、共享(SHARE,S)：共享锁将锁定表，仅允许其他用户查询表中的行，但不允许插入、更新或删除行。多个用户可以同时在同一张表中放置共享锁即允许资源共享。但是这样极容易造成死锁。
4、共享行排他(SHAREROWEXCLUSIVE,SRX)：执行比共享表锁更多的限制。防止其他事务在表上应用共享锁、共享行排他锁以及排他锁。
5、排他(EXCLUSIVE,X)：对表执行最大限制。除了允许其他用户查询该表的记录，排他锁防止其他事务对表做任何更改或在表上应用任何类型的锁。示例2：演示如何以共享模式锁定表。locktabledeptinsharemodenowait;表分区的优点：
改善表的查询性能;表更容易管理;便于备份和恢复;提高数据安全性。注意：要分区的表不能具有Long和LongRaw数据类型的列。四种分区方法：
1、范围分区：根据表的某个列或一组列的值范围，决定将该数据存储在哪个分区上。语法如下：
在CreateTable语句后增加
PARTITIONBYRANGE(column_name)(
PARTITIONpart1VALUELESSTHAN(range1)[TABLESPACEtbs1],PARTITIONpart2VALUELESSTHAN(range2)[TABLESPACEtbs2],....
PARTITIONpartNVALUELESSTHAN(MAXVALUE)[TABLESPACEtbsN]);
其中：column_name是以其为基础创建范围分区的列，特定行的该列值称为分区键。part1...partN是分区的名称。
range1...MAXVALUE是分区的边界值。
tbs1...tbsN是分区所在的表空间，TABLESPACE子句是可选项。示例3：
createtablet_emp(
empnonumber(4),enamevarchar2(30),salnumber)
partitionbyrange(empno)(
partitione1valueslessthan(1000)tablespaceemp1,也可以than(to_date("201*-01-01","yyyy-mm-dd"))
partitione2valueslessthan(201*)tablespaceemp2,也可以than(to_date("201*-01-01","yyyy-mm-dd"))
partitione3valueslessthan(maxvalue)tablespaceemp3);
2、散列分区：语法有两种如下
PARTITIONBYHASH(column_name)
PARTITIONSnumber_of_partitions[STOREIN(tablespace_list)];或
PARTITIONBYHASH(column_name)(
PARTITIONpart1[TABLESPACEtbs1],PARTITIONpart2[TABLESPACEtbs2],...
PARTITIONpartN[TABLESPACEtbsN]);
其中：column_name是以其为基础创建散列分区的列。
number_of_partitions是散列分区的数目，使用这种方法系统会自动生成分区的名称。tablespace_list指定分区使用的表空间，如果分区数目比表空间的数目多，分区将会以循环的方式分配到表空间中。part1...partN是分区的名称。
tbs1...tbsN是分区所在的表空间，TABLESPACE子句是可选项。示例4：自动分配4个散列分区，
可以使用selectpartition_name,HIGH_VALUEfromuser_tab_partitionswheretable_name=upper("t_emp")查询分区名createtablet_emp(
empnonumber(4),enamevarchar2(30),salnumber)
partitionbyhash(empno)partitions4;
3、复合分区：是范围分区和散列分区的结合。在创建复合分区时，先根据范围对数据进行分区，然后在这些分区内创建散列子分区。语法如下：
PARTITIONBYRANGE(column_name1)SUBPARTITIONBYHASH(column_name2)
SUBPARTITIONSnumber_of_partitions[STOREIN(tablespace_list)](
PARTITIONpart1VALUELESSTHAN(range1)[TABLESPACEtbs1],PARTITIONpart2VALUELESSTHAN(range2)[TABLESPACEtbs2],....
PARTITIONpartNVALUELESSTHAN(MAXVALUE)[TABLESPACEtbsN]);
其中：column_name1是以其为基础创建范围分区的列。column_name2是以其为基础创建散列分区的列。number_of_partitions是要创建的子分区的数目。part1...partN是分区的名称。
range1...MAXVALUE是范围分区的边界值
示例5：将雇员表先按照雇佣时间hiredate进行了范围分区，然后再把每个分区分为2个子hash分区，此表一共是6个分区。createtablet_emp(
empnonumber(4),enamevarchar2(30),hiredatedate)
partitionbyrange(hiredate)subpartitionbyhash(empno)subpartitions2(
partitione1valueslessthan(to_date("201*0501","YYYYMMDD")),partitione2valueslessthan(to_date("201*1001","YYYYMMDD")),partitione3valueslessthan(maxvalue));
4、列表分区：此分区允许用户明确地控制行到分区的映射。语法如下：
PARTITIONBYLIST(column_name)(
PARTITIONpart1VALUES(values_list1),PARTITIONpart2VALUES(values_list2),....
PARTITIONpartNVALUES(DEFAULT));
其中：column_name是以其为基础创建列表分区的列。part1...partN是分区的名称。
values_list是对应分区的分区键值的列表。DEFAULT关键字允许存储前面的分区不能存储的记录。示例6：
createtablet_emp1(
empnonumber(4),enamevarchar2(30),locationvarchar2(30))
partitionbylist(location)(
partitione1values("北京"),
partitione2values("上海","天津","重庆"),partitione3values("广东","福建"),);
要查询表分区中的数据行：select*fromtable_namePARTITION(p1);注：p1是分区名。分区维护操作：
1、添加分区：ALTER...ADDPARTITION语句用于在现有的最后一个分区之后添加新的分区。
示例7：演示如何将名为E4的新分区添加到示例3中创建的t_emp表。ALTERTABLEt_empADDPARTITIONE4VALUESLESSTHAN(3000);在此请注意：上例公适用于已使用特定的键值定义了最后一个分区的表。如果要在表的开始或中间位置添加分区，或者最高分区的分区
边界是MAXVALUE，则应使用SPLITPARTITION语句。
2、删除分区：使用ALTERTABLE...DROPPARTITION语句。
示例8：演示了如何删除t_emp表的E4分区。删除分区时，分区中的数据也随之删除。ALTERTABLEt_empDROPPARTITIONE4;
3、截断分区：使用ALTERTABLE...TRUNCATEPARTITION语句来截断分区，只删除表分区中的所有记录。
示例9：演示了如何删除t_emp表中e3的分区的所有记录。ALTERTABLEt_empTRUNCATEPARTITIONe3;
4、合并分区：可以将范围分区或复合分区表的两个相邻分区连接起来。结果分区将继承被合并的两个分区的较高上界。语法如下：
ALTERTABLEtable_nameMERGEPARTITIONSpartitions_name,partitions_nameINTOPARTITIONpartition_name;
示例10：演示了如何将e1和e2合并成一个e2分区。
ALTERTABLEt_empMERGEPARTITIONSe1,e2INTOPARTITIONe2;
5、拆分分区：使用SPLITPARTITION语句在表的开头或中间添加分区。拆分分区允许用户将一个分区拆分为两个分区。语法如下：ALTERTABLEtable_nameSPLIYPARTITIONpartition_nameAT(value)INTO(PARTITIONpartition1,PARTITIONpartition2);
示例11：演示了如何将t_emp表中的e3分区拆分为e31和e32两个分区。
ALTERTABLEt_empSPLITPARTITIONe3AT(Date"201*-01-01")INTO(PARTITIONe31,PARTITIONe32);
6、重新命名拆分后的分区:
ALTERTABLEt_empRENAMEPARTITIONe31TOP3;ALTERTABLEt_empRENAMEPARTITIONe32TOP4;
可以查询字典视图user_tab_partitions来查看用户所创建的分区的详细信息示例12：selecttable_name,partition_name,high_valuefromuser_tab_partitions;
可以查询字典视图dba_tab_subpartitions来查看用户所创建的子分区的详细信息
示例13：selecttable_name,partition_name,subpartition_namefromdba_tab_subpartitions;
四、数据库对象
表、视图、序列、过程、函数、程序包，甚至其它同义词都可以创建同义词。
1、同义词：私有同义词、公有同义词。私有同义词只能被当前模式的用户访问。私有同义词名称不可与当前模式的对象名称相同。要在自身的模式创建私有同义词，
用户必须拥有CreateSynonym系统权限。要在其它用户模式创建私有同义词，用户必须拥有CreateAnySynonym系统权限。
公有同义词可被所有的数据库用户访问。要创建公有同义词，用户必须拥有CreatePublicSynonym系统权限。
创建私有同义词语法：
Create[ORREPLACE]SYNONYM[schema.]synonym_nameFOR[schema.]object_name;其中：ORREPLACE表示在同义词存在的情况下替换该同义词。synonym_name表示要创建的同义词的名称。
object_name指定要为之创建同义词的对象的名称。示例1：createsynonyms_empforscott.emp;
创建公有同义词语法：
CreatePUBLICSYNONYMsynonym_nameFOR[schema.]object_name;示例2：createpublicsynonymemp_synfromscott.emp;
可以查询字典视图User_Synonyms来查看用户所创建的同义词的详细信息
删除同义词语法：DropSynonymssynonym_name;删除公有同义词加上一个Public此命令只删除同义词，不会删除对应的表。
2、序列：是用来生成唯一、连续的整数的数据库对象。序列通常用来自动生成主键或唯一键的值。
创建序列语法如下：
CreateSEQUENCEsequence_name[STARTWITHinteger][INCREMENTBYinteger]
[MAXVALUEinteger|NOMAXVALUE][MINVALUEinteger|NOMINVALUE][CYCLE|NOCYCLE]
[CACHEinterger|NOCACHE];
其中：STARTWITH是指定要生成的第一个序列号。对于升序序列，其默认值为序列的最小值。对于降序序列，其默认值为序列的最大值。
INCREMENTBY是用于指定序列号之间的间隔。其默认值为1。如果integer为正值，则生成的序列将按升序排列，否则按降序排列。MAXVALUE指定序列可以生成的最大值。
NOMAXVALUE这是默认选项，将升序序列的最大值设为10的27次幂，将降序序列的最大值设为-1。
MINVALUE指定序列的最小值。MINVALUE必须小于或等于STARTWITH的值，并且必须小于MAXVALUE。
NOMINVALUE这是默认选项，将升序序列的最小值设为1，将降序序列的最小值设为-10的26次幂。
CYCLE指定序列在达到最大值或最小值后，将继续从头开始生成值。
NOCYCLE这是默认选项。指定序列在达到最大值或最小值后，将不能再继续生成值。CACHE使用CACHE选项可以预先分配一组序列号，并将其保留在内存中，这样可以更快的访问序列号。
NOCACHE此项则不会为加快速度而预先分配序列号。如果在创建序列时忽略了CACHE和NOCACHE选项，Oracle将默认缓存20个序列号。示例3：CreateSEQUENCEtoys_seqSTARTWITH10INCREMENTBY2MAXVALUE201*MINVALUE10NOCYCLECACHE30;
访问序列：可以通过CURRVAL和NEXTVAL伪列来访问该序列的值。示例4：演示从序列toys_seq中选择值插入toys表中的toyid列。执行成功将会在该表的toyid列插入值"P10"和"P12"。
INSERTINTOtoys(toyid,toyname,toyprice)values("p"||toys_seq.NEXTVAL,"TWENTY",25);INSERTINTOtoys(toyid,toyname,toyprice)values("p"||toys_seq.NEXTVAL,"MAGICPENCIL",75);
示例5：演示如何查看序列当前值
Selecttoys_seq.CURRVALfromdual;
更改序列：ALTERSEQUENCE命令用于设置或删除MINVALUE或MAXVALUE、修改增量值、修改缓存中的序列号的数目。
修改序列语法如下：注意，不能修改序列的STARTWITH参数。在修改序列时，应注意升序序列的最小值应小于最大值。
ALTERSEQUENCE[schema.]sequence_name[INCREMENTBYinteger]
[MAXVALUEinteger|NOMAXVALUE][MINVALUEinteger|NOMINVALUE][CYCLE|NOCYCLE]
[CACHEinterger|NOCACHE];
示例6：演示如何设置一个新的MAXVALUE，并为toys_seq序列打开了CYCLE。ALTERSEQUENCEtoys_seqMAXVALUE5000CYCLE;
可以查询字典视图User_Sequences来查看用户所创建的序列的详细信息
删除序列语法：DropSEQUENCEtoys_seq;
[local]2[/local]
友情提示：本文中关于《Oracle使用学习总结》给出的范例仅供您参考拓展思维使用，Oracle使用学习总结：该篇文章建议您自主创作。
来源：网络整理 免责声明：本文仅限学习分享，如产生版权问题，请联系我们及时删除。
《Oracle使用学习总结》
由互联网用户整理提供,转载分享请保留原作者信息,谢谢!
http://m.bsmz.net/gongwen/712073.html
- 上一篇：幼儿园园长心得体会
- 下一篇：oracle实训总结