Oracle常用命令

         ORACLE相关语法及命令
一、Oracle入门
  理论知识：
    Oracle的物理组件有三个：
      (1)数据文件     数据文件是用于存储数据库数据的文件，如表、索引数据。每个Oracle数据库有一个或多个物理数据文件， 一个数据文件只能与一个数据库关联。
      (2)日志文件     用于记录对数据库进行的修改信息，日志文件主要用于在数据库出现故障时实施数据库恢复。
      (3)控制文件     控制文件是记录数据库物理结构的二进制文件，每个Oracle数据库都含有一个控制文件。
    Oracle的逻辑组件：
      表空间(TableSpace) 表空间是数据库最大的逻辑单位，一个数据库至少包含一个表空间，一个表空间包含一个或多个段等等。
      段(Segment)     段存在于表空间中，分成4类，数据段、索引段、回退段、临时段。              
      区(Extent)     区是磁盘空间分配最小单位，由连续的数据块组成，一个或多个区构成段，区只能存在于一个数据文件中。
      数据块(Data Block) 数据块是数据库中最小的数据组织单位与管理单位，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数据库

　语法知识：
  创建表空间的语法如下：
    CREATE TABLESPACE tablespacename DATAFILE 'd:\filename.DBF' [SIZE int [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
    alter user 用户名 identified by 密码 //修改用户口令
　　drop user 用户名 cascade;            //删除用户
    alter user 用户名 account lock;      //给某个用户加锁
    alter user 用户名 account unlock;    //给某个用户解锁
    ed  回车:  //打开缓冲区
    /   回车:  //执行缓冲区中的语句
    create user 用户名 identified by 密码 [password expire]
[default tablespace 表空间名] [temporary tablespace 临时表空间名];  //创建用户
  相关权限：
    grant connect to scott;           //connect角色将允许用户创建数据库并在数据库中创建表或其他对象
　　grant resource to scott;          //resource角色将允许用户使用数据库中的空间
　　grant create sequence to scott;   //create sequence权限将允许用户创建序列，此权限包含在connect连接角色中
　　grant select on emp to scott;     //将emp表的查询权限授予用户scott
　　grant update(vencode,venname) on 表名 to scott;　//将特定列的更新权限授予用户scott
    grant 权限 on 表名 to 用户名 with grant option;  //接受该权限的用户可以将此权限授予其他用户
　　revoke select,update on 表名 from 用户名;　　//收回相应的权限
二、SQL查询和SQL函数
　SQL支持如下类别的命令：
　　数据定义语言：　create(创建)、alter(更改)、drop(删除)和truncate(截断)命令。
　　数据操纵语言：　insert(插入)、select(选择)、delete(删除)和update(更新)命令。
　　事务控制语言：　commit(提交)、savepoint(保存点)和rollback(回滚)命令。
　　数据控制语言：　grant(授予)和revoke(回收)命令。
　数据类型：
　　char:           长度在1到2000个字节，声明多少字节在内存中就占用多少字节，输入的值小于指定的长度时用空格填充。
　　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月 -07 04.08.30.000000 下午。
　　raw:            此数据类型用于存储基于字节的数据，如二进制数据或字节串，该类型最多能存储2000个字节，可以建立索引。
　　long raw:　　　 此数据类型用于可变长度的二进制数据，最多能存储2GB。long数据类型的所有限制对long raw数据类型也同样有效。
　　lob又称为"大对象"数据类型，最多能存储4GB的非结构化信息。包括：
　　clob:     clob代表Character LOB(字符LOB)，它能存储大量字符数据。如XML文档。
    blob:　 blob代表Binary LOB(二进制LOB)，它能存储较大的二进制对象，如图形、视频剪辑和声音剪辑。
    bfile:　　bfile代表Binary File(二进制文件)，它能够将二进制文件存储在数据库外部的操作系统文件中。
　伪列：
　　rowid:　　select rowid,ename, from scott.emp where empno='7900';
    rownum:   select * from scott.emp where rownum<11;  //限制查询返回的行数
  语法知识：
　　desc 表名;　//查看表结构
　　alter table 表名 modify (列名 varchar2(25));    //修改列
　　alter table 表名 add (列名 varchar2(12),列名 number(12));　　//添加列
　　alter table 表名 drop column 列名;　　//删除列
　　truncate table 表名;　　//中删除记录而不删除结构，不使用事务处理，因此无法回滚
　　drop table 表名;　　//删除表及其全部数据
　　create table 新表名 as select * from 表名 where 1=2;　　//用现有的表创建一个新表
　　select deptno*2 "New No",dname,loc from dept;　　//指定一个含有特殊字符(如空格)的列标题
    commit;      //提交事务
　　savepoint 标记名;　　　//标记事务点
　　rollback;    //回滚整个事务处理
　　rollback to [savepoint] 标记名;//回滚到事务中某个特定的保存点
  集合操作符：
    union(联合):      此操作符返回两个查询选定的所有不重复的行。
    语法 select orderno from order_master UNION select orderno from order_detail;
    union all(联合所有):   此操作符合并两个查询选定的所有行，包括重复的行。
    语法：select orderno,ename from order_master UNION ALL select orderno,proname from order_detail order by 2;
    注意：在两个select语句中指定的列名不必相同，但数据类型必须匹配。也可以对联合查询的结果进行排序，使用Order By子句时，它必须放在最后
一个select语句之后，而且必须指定列索引来排序，而不是指定列名，列索引是从1开始的整数。上述语法便是以proname的索引排序
    intersect(交集):       此操作符只返回两个查询都有的行。
    语法：select orderno from order_master INTERSECT select orderno from order_detail;
    minus(减集):           此操作符中返回由第一个查询选定但是第二个查询中没有选定的行，也就是在第一个查询结果中排除第二个查询结果中出现的行。
语法：select orderno from order_master MINUS select orderno from order_detail;查询尚未交付的订单
  连接( || )操作符：
    语法：   select ('供应商'||venname||'的地址是'||venadd1||' '||venadd2||' '||venadd3) 地址 from vendor_master where vencode='V002'; 将多个字符串合并为一个字符串。
　SQL函数：
　　1、日期函数：
    add_months:       此函数返回给指定的日期加上指定的月数后的日期值。语法为add_months(d,n),其中d是日期，n表示月数。
    示例：select add_months(sysdate,2) from dual;    将当前时间加上2个月后的日期值。
    months_between:   此函数返回两个日期之间的月数。语法为months_between(d1,d2),其中d1和d2是日期，如果d1大于d2，则结果为正数；否则为负数。
    last_day:   此函数返回指定日期当月的最后一天的日期值，语法为last_day(d),其中d表示日期。
    示例：select last_day(sysdate) from dual;    返回当前日期的月的最后一天，如果是9月就返回30-09月-07
    round:   此函数返回日期值，将日期四舍五入为格式模型指定的单位。语法为round(d,[fmt])。其中d是日期，fmt是格式模型。fmt是一个可选项，日期默认舍入为最靠近的那一天。如果指定格式为年"Year"，则舍入到年的开始，即1月1日;如果格式为月"Month"，则舍入到月的第一日;如果格式为周"Day"，则舍入到最靠近的星期日。
    示例：select round(sysdate,'month') from dual;   返回最接近的一个月。
    next_day:  此函数返回指定的下一个星期几的日期。语法为next_day(d,day)。其中d表示日期，而day指周内任何一天。
    示例：select next_day(sysdate,'星期日') from dual; 返回下一个星期日的日期，也可以用1表示，以此类推，星期一以2表示。
    trunc:   此函数将指定日期截断为由格式模型指定的单位日期，与Round函数不同的是它只舍不入，语法为trunc(d,[fmt])，与round格式相同。
    示例：select trunc(sysdate,'year') from dual;    返回当前年的第一天，也就是1月1日。
    示例：select trunc(sysdate,'day') from dual;     返回紧靠前面的星期日。如果为"2005年1月27日"就会返回"2005年1月23日"。
    extract:  此函数提取日期时间类型中的特定部分。语法为extract(fmt from d)，其中d是日期时间表达式，fmt是要提取的部分的格式。格式的取值可以是year,month,day,hour,minute,second,注意此处的格式不使用单引号。
    示例：select extract(year from sysdate) from dual;  返回当前的年份。
    2、字符函数：
    initcap(char):    首字母大写，示例：select initcap('hello') from dual; 输出结果:Hello。
    lower(char):      转换为小写，示例：select lower('FUN') from dual; 输出结果:fun。
    upper(char):      转换为大写，示例：select upper('sun') from dual; 输出结果:SUN。
    ltrim(char,set):  左剪裁，示例：select ltrim('xyzadams','xyz') from dual; 输出结果:adams。
    rtrim(char,set):  右剪裁，示例：select rtrim('xyzadams','ams') from dual; 输出结果:xyzad。
    translate(char,from,to):  按字符翻译，示例：select translate('jack','abcd','1234') from dual; 输出结果:j13k。
    replace(char,search_str,replace_str):  字符串替换，示例：select replace('jack and jue','j','bl') from dual; 输出结果:black and blue。
    instr(char,substr[,pos1,pos2]):  查找子字串位置。
    示例：select instr('vorldwide','d') from dual; 输出结果:5。pos1为可选，表示从第几个位置查找。pos2为可选，表示从第几次出现的位置找。
    substr(char,pos,len):  取子字符串，示例：select substr('abcdefg',3,2) from dual; 输出结果:cd。
    concat(char1,char2):  连接字符串，示例：select concat('Hello','world') from dual; 输出结果:Helloworld。
    chr: 此函数根据Ascii码返回对应的字符，示例：select chr(45788),chr(53671),chr(50167),chr(65) from dual; 输出结果:曹 学 明 A。
    ascii:   此函数返回GBK编码值，示例：select ascii('曹') cao ,ascii('学') xue,ascii('明') Ming from dual; 输出结果:45788 53671 50167。
    lpad和rpad：  示例：select lpad('function',15,'=') from dual;  输出结果:=======function。而rpad则相反，字符串填充在右边。
    trim:    此函数从字符串的开头或结尾(或开头和结尾)剪裁特定的字符，默认剪裁空格。如果加上leading选项时与ltrim函数相似。指定trailing时和             rtrim函数相似。
     示例： select trim(9 from 999992598899) from dual; 输出结果:25988。
     示例： select trim(leading 9 from 999992598899) from dual; 输出结果:2598899。
     示例： select trim(trailing 9 from 999992598899) from dual; 输出结果:9999925988。
    length:  此函数返回字符串的长度，示例：select length('frances') from dual; 输出结果:7。
decode:  示例：select deptno,dname,decode(loc,'NEW YORK','纽约','BOSTON','波士顿') from scott.dept;
此示例将替换显示loc列的结果,结果为"NEW YORK"的替换为"纽约"，"BOSTON"的替换为"波士顿"。
    GREATEST/least:  返回一组表达式中的最大值/最小值,即比较字符的编码大小.
    示例：select greatest('AA','AB','AC') from dual;  输出结果:AC。
          select least('AA','AB','AC') from dual;  输出结果:AA。
          select greatest('啊','安','天') from dual;  输出结果:天。
          select least('啊','安','天') from dual;  输出结果:啊。
    3、数字函数：
    abs(n):     取绝对值，示例：select abs(-15) from dual;  输出结果:15。
    ceil(n):    向上取整，示例：select ceil(44.778) from dual;  输出结果:45。
    sign(n):    取符号，示例：select sign(-2) from dual;  输出结果:-1。
    floor(n):   向下取整，示例：select floor(200.88) from dual;  输出结果:200。
    power(m,n): m的n次幂，示例：select power(5,3) from dual;  输出结果:125。
    mod(m,n):   取余数，示例：select mod(10,3) from dual;  输出结果:1。
    round(m,n): 四舍五入，示例：select round(100.256,2) from dual;  输出结果:100.26。
    trunc(m,n): 截断，示例：select trunc(100.256,2) from dual;  输出结果:100.25。
    sqrt(n):    平方根，示例：select sqrt(4) from dual;  输出结果:2。
    4、转换函数：
    to_char(d|n[,fmt]):   其中d是日期，n是数字，fmt指定日期或数字的格式。
    示例：select to_char(sysdate,'yyyy"年"fmmm"月"fmdd"日" hh24:mi:ss') from dual;  输出结果:2007年9月09日 20:44:27。
          select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
          select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
          select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24"时"mi"分"ss"秒"') from dual;
    示例：select to_char(sal,'$99999') from emp;  输出结果:$1600。
    to_date(char[,fmt]):  此函数将char或varchar2数据类型转换为日期数据类型。
    示例：select to_date('2005-12-06','yyyy-mm-dd') from dual;  输出结果:06-12月-05。
          select to_date('2008/08/10 20:08:08','yyyy/mm/dd hh24:mi:ss') from dual;
          select to_date('2008-08-10 20:08:08','yyyy/mm/dd hh24:mi:ss') from dual;
          select to_date('2008年08月10日 20时08分08秒','yyyy"年"mm"月"dd"日" hh24"时"mi"分"ss"秒"') from dual;
    to_number(char):  此函数将包含数字的字符串转换为number数据类型，通常不用这么做，因为Oracle可以对数字字符串进行隐式转换。
    示例：select sqrt(to_number('100')) from dual;  输出结果: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表达式：
    case when expr1=expr2 then null else expr1 end
    6、分组函数：
    avg:   此函数返回指定列值的平均值，示例：select avg(sal) from emp;  输出结果:2073.21429。
    min:   此函数返回指定列值的最小值，示例：select min(sal) from emp;  输出结果:800。
    max:   此函数返回指定列值的最大值，示例：select max(sal) from emp;  输出结果:5000。
    sum:   此函数返回指定列值的总和，示例：select sum(sal) from emp;  输出结果:29025。
    count: 此函数是为了计算行数，它可以接受3种不同的参数
    示例：select count(*) from emp;  输出结果:14。
    示例：select count(列名) from 表名;  
    示例：select count(distinct 列名) from 表名;
    group by:  此子句用于将信息表划分为组，按组进行聚合运算。select后面跟的列名只能是分组函数、group by子句中出现的列或表达式。
    示例：select deptno,max(sal) from emp group by(deptno); 查出每个部门的最高工资。
    示例：select deptno,count(*),sum(sal) from scott.emp group by deptno;　　//group by主要用来对一组数进行统计
    having:    此子句用来指定group by子句的检索条件。
    示例：select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;  //having对分组统计再加限制条件
    7、分析函数：只能出现在select列表或order by子句中。
    row_number:  下面对所有员工的工资进行排名，即使工资相同，其排名也不能相同。
    示例：select ename,job,deptno,sal,row_number() over(order by sal desc) as 排名 from scott.emp;
    下面对所有员工的工资按部门进行排名，即使工资相同，排名也不同。
    示例：select ename,job,deptno,sal,row_number() over(partition by deptno order by sal desc) as 排名 from scott.emp;
    rank:  此函数计算一个值在一组值中的排位，排位是以1开头的连续整数，如果两行的序数为1，则没有序数2，下行的序数为3。
    下面根据员工的工资和佣金对员工在每个部门中进行排位。相同的工资排位相同，并且排位不连续。
    示例：select ename,sal,comm,deptno,rank() over(partition by deptno order by sal desc,comm) 排名 from scott.emp;
    dense_rank:  此函数计算一个行在一组有序行中的排位，排位是以1开头的连续整数，具有相同值的排位相同，并且排位是连续的。
    下面首先选择所有在accounting或research部门中工作的员工的部门名称、员工姓名和工资，
    然后分别计算每个员工的工资在部门中的排位，相等的工次排位相同
    示例：select d.dname,e.ename,e.sal,dense_rank() over(partition by e.deptno order by e.sal desc) 排名
          from emp e,dept d where e.deptno=d.deptno;
            
三、锁和表分区
  锁定是数据库用来控制共享资源并发访问的机制。
  Oracle提供以确保在多用户环境下数据的完整性和一致性。
  锁的两种级别：(只有在提交或回滚后才能释放锁定)
  (1)行级锁：是一种排他锁，防止其他事务修改此行，但是不会阻止读取此行的操作。在使用Insert、Update、Delete和Select...For Update
     等语句时，Oracle会自动应用行级锁定。
     Select...For Update语法为：
     Select...For Update [OF column_list] [WAIT n | NOWAIT]
     其中：OF子句用于指定即将更新的列，即锁定行上的特定列。
           WAIT子句指定等待其他用户释放锁的秒数，防止无限期的等待。NOWAIT为不等待。
     示例1：演示如何锁定deptno值为10的所有行。
     select * from dept where deptno=10 for update of dname,loc;
  (2)表级锁：将保护表数据，在事务处理过程中，表级锁会限制对整个表的访问。表级锁用来限制对表执行添加、更新和删除等修改操作。
     语法：
     LOCK TABLE <table_name> IN <lock_mode> MODE [NOWAIT];
     其中：table_name是要被锁定的表的名称。
           lock_mode是锁定的模式。
     表级锁的模式：
     1、行共享(ROW SHARE,RS)：    允许其他用户访问和锁定该表，但是禁止排他锁锁定整个表。
     2、行排他(ROW EXCLUSIVE,RX)：与行共享模式相同，同时禁止其他用户在此表上使用共享锁。使用Select...For update语句会自动应用行排他。  
     3、共享(SHARE,S)：共享锁将锁定表，仅允许其他用户查询表中的行，但不允许插入、更新或删除行。多个用户可以同时在同一张表中放置共享锁
                       即允许资源共享。但是这样极容易造成死锁。
     4、共享行排他(SHARE ROW EXCLUSIVE,SRX)： 执行比共享表锁更多的限制。防止其他事务在表上应用共享锁、共享行排他锁以及排他锁。
     5、排他(EXCLUSIVE,X)： 对表执行最大限制。除了允许其他用户查询该表的记录，排他锁防止其他事务对表做任何更改或在表上应用任何类型的锁。
     示例2：演示如何以共享模式锁定表。
     lock table dept in share mode nowait;
  表分区的优点：
     改善表的查询性能;表更容易管理;便于备份和恢复;提高数据安全性。
     注意：要分区的表不能具有Long和Long Raw数据类型的列。
     四种分区方法：
     1、范围分区：根据表的某个列或一组列的值范围，决定将该数据存储在哪个分区上。
        语法如下：
        在Create Table语句后增加
        PARTITION BY RANGE(column_name)
        (
           PARTITION part1 VALUE LESS THAN (range1) [TABLESPACE tbs1],
           PARTITION part2 VALUE LESS THAN (range2) [TABLESPACE tbs2],
           ....
           PARTITION partN VALUE LESS THAN (MAXVALUE) [TABLESPACE tbsN]
        );
        其中：column_name是以其为基础创建范围分区的列，特定行的该列值称为分区键。
              part1...partN是分区的名称。
              range1...MAXVALUE是分区的边界值。
              tbs1...tbsN是分区所在的表空间，TABLESPACE子句是可选项。
        示例3：
        create table t_emp
        (
          empno number(4),
          ename varchar2(30),
          sal number
        )
        partition by range(empno)
        (
          partition e1 values less than (1000) tablespace emp1,       也可以     than (to_date('2003-01-01','yyyy-mm-dd'))
          partition e2 values less than (2000) tablespace emp2,       也可以     than (to_date('2004-01-01','yyyy-mm-dd'))
          partition e3 values less than (maxvalue) tablespace emp3
        );
     2、散列分区：语法有两种如下
        PARTITION BY HASH(column_name)
        PARTITIONS number_of_partitions [STORE IN (tablespace_list)];
        或
        PARTITION BY HASH(column_name)
        (
           PARTITION part1 [TABLESPACE tbs1],
           PARTITION part2 [TABLESPACE tbs2],
           ...
           PARTITION partN [TABLESPACE tbsN]
        );
        其中：column_name是以其为基础创建散列分区的列。
       number_of_partitions是散列分区的数目，使用这种方法系统会自动生成分区的名称。
       tablespace_list指定分区使用的表空间，如果分区数目比表空间的数目多，分区将会以循环的方式分配到表空间中。
       part1...partN是分区的名称。
       tbs1...tbsN是分区所在的表空间，TABLESPACE子句是可选项。
        示例4：自动分配4个散列分区，
          可以使用select partition_name,HIGH_VALUE from user_tab_partitions where table_name=upper('t_emp')查询分区名
        create table t_emp
        (
          empno number(4),
          ename varchar2(30),
          sal number
        )
        partition by hash (empno)
        partitions 4;
     3、复合分区：是范围分区和散列分区的结合。在创建复合分区时，先根据范围对数据进行分区，然后在这些分区内创建散列子分区。
        语法如下：
        PARTITION BY RANGE(column_name1)
        SUBPARTITION BY HASH(column_name2)
        SUBPARTITIONS number_of_partitions [STORE IN (tablespace_list)]
        (
           PARTITION part1 VALUE LESS THAN (range1) [TABLESPACE tbs1],
           PARTITION part2 VALUE LESS THAN (range2) [TABLESPACE tbs2],
           ....
           PARTITION partN VALUE LESS THAN (MAXVALUE) [TABLESPACE tbsN]
        );
        其中：column_name1是以其为基础创建范围分区的列。
              column_name2是以其为基础创建散列分区的列。
              number_of_partitions是要创建的子分区的数目。
              part1...partN是分区的名称。
              range1...MAXVALUE是范围分区的边界值
        示例5：将雇员表先按照雇佣时间hiredate进行了范围分区，然后再把每个分区分为2个子hash分区，此表一共是6个分区。
        create table t_emp
        (
          empno number(4),
          ename varchar2(30),
          hiredate date
        )
        partition by range (hiredate)
        subpartition by hash (empno)
        subpartitions 2
        (
          partition e1 values less than (to_date('20020501','YYYYMMDD')),
          partition e2 values less than (to_date('20021001','YYYYMMDD')),
          partition e3 values less than (maxvalue)
        );
     4、列表分区：此分区允许用户明确地控制行到分区的映射。
        语法如下：
        PARTITION BY LIST(column_name)
        (
           PARTITION part1 VALUES (values_list1),
           PARTITION part2 VALUES (values_list2),
           ....
           PARTITION partN VALUES (DEFAULT)
        );
        其中：column_name是以其为基础创建列表分区的列。
              part1...partN是分区的名称。
              values_list是对应分区的分区键值的列表。
              DEFAULT关键字允许存储前面的分区不能存储的记录。
        示例6：
        create table t_emp1
        (
          empno number(4),
          ename varchar2(30),
          location varchar2(30)
        )
          partition by list (location)
        (
          partition e1 values ('北京'),
          partition e2 values ('上海','天津','重庆'),
          partition e3 values ('广东','福建'),
        );
  要查询表分区中的数据行：select * from table_name PARTITION(p1);  注：p1是分区名。
  分区维护操作：
    1、添加分区：ALTER...ADD PARTITION语句用于在现有的最后一个分区之后添加新的分区。
       示例7：演示如何将名为E4的新分区添加到示例3中创建的t_emp表。
       ALTER TABLE t_emp ADD PARTITION E4 VALUES LESS THAN (3000);
       在此请注意：上例公适用于已使用特定的键值定义了最后一个分区的表。如果要在表的开始或中间位置添加分区，或者最高分区的分区
       边界是MAXVALUE，则应使用SPLIT PARTITION语句。

    2、删除分区：使用ALTER TABLE...DROP PARTITION语句。
       示例8：演示了如何删除t_emp表的E4分区。删除分区时，分区中的数据也随之删除。
       ALTER TABLE t_emp DROP PARTITION E4;  

    3、截断分区：使用ALTER TABLE...TRUNCATE PARTITION语句来截断分区，只删除表分区中的所有记录。
       示例9：演示了如何删除t_emp表中e3的分区的所有记录。
       ALTER TABLE t_emp TRUNCATE PARTITION e3;

    4、合并分区：可以将范围分区或复合分区表的两个相邻分区连接起来。结果分区将继承被合并的两个分区的较高上界。
       语法如下：
       ALTER TABLE table_name MERGE PARTITIONS partitions_name,partitions_name INTO PARTITION partition_name;
       示例10：演示了如何将e1和e2合并成一个e2分区。
       ALTER TABLE t_emp MERGE PARTITIONS e1,e2 INTO PARTITION e2;

    5、拆分分区：使用SPLIT PARTITION语句在表的开头或中间添加分区。拆分分区允许用户将一个分区拆分为两个分区。
       语法如下：
       ALTER TABLE table_name SPLIY PARTITION partition_name AT (value) INTO (PARTITION partition1,PARTITION partition2);
       示例11：演示了如何将t_emp表中的e3分区拆分为e31和e32两个分区。
       ALTER TABLE t_emp SPLIT PARTITION e3 AT (Date '2005-01-01') INTO (PARTITION e31,PARTITION e32);

    6、重新命名拆分后的分区:
       ALTER TABLE t_emp RENAME PARTITION e31 TO P3;
       ALTER TABLE t_emp RENAME PARTITION e32 TO P4;

  可以查询字典视图user_tab_partitions来查看用户所创建的分区的详细信息
  示例12：select table_name,partition_name,high_value from user_tab_partitions;
  
  可以查询字典视图dba_tab_subpartitions来查看用户所创建的子分区的详细信息
  示例13：select table_name,partition_name,subpartition_name from dba_tab_subpartitions;

四、数据库对象
  表、视图、序列、过程、函数、程序包，甚至其它同义词都可以创建同义词。
  
  1、同义词：私有同义词、公有同义词。
  私有同义词只能被当前模式的用户访问。私有同义词名称不可与当前模式的对象名称相同。要在自身的模式创建私有同义词，
  用户必须拥有Create Synonym系统权限。要在其它用户模式创建私有同义词，用户必须拥有Create Any Synonym系统权限。
  公有同义词可被所有的数据库用户访问。要创建公有同义词，用户必须拥有Create Public Synonym系统权限。

  创建私有同义词语法：
  Create [OR REPLACE] SYNONYM [schema.]synonym_name FOR [schema.]object_name;
  其中：OR REPLACE表示在同义词存在的情况下替换该同义词。
        synonym_name表示要创建的同义词的名称。
        object_name指定要为之创建同义词的对象的名称。
  示例1：create synonym s_emp for scott.emp;

  创建公有同义词语法：
  Create PUBLIC SYNONYM synonym_name FOR [schema.]object_name;
  示例2：create public synonym emp_syn from scott.emp;

  可以查询字典视图User_Synonyms来查看用户所创建的同义词的详细信息

  删除同义词语法：Drop Synonyms synonym_name; 删除公有同义词加上一个Public
  此命令只删除同义词，不会删除对应的表。

  2、序列：是用来生成唯一、连续的整数的数据库对象。序列通常用来自动生成主键或唯一键的值。
  创建序列语法如下：
  Create SEQUENCE sequence_name
    [START WITH integer]
    [INCREMENT BY integer]
    [MAXVALUE integer|NOMAXVALUE]
    [MINVALUE integer|NOMINVALUE]
    [CYCLE|NOCYCLE]
    [CACHE interger|NOCACHE];
  其中：START WITH是指定要生成的第一个序列号。对于升序序列，其默认值为序列的最小值。对于降序序列，其默认值为序列的最大值。
        INCREMENT BY是用于指定序列号之间的间隔。其默认值为1。如果integer为正值，则生成的序列将按升序排列，否则按降序排列。
        MAXVALUE指定序列可以生成的最大值。
        NOMAXVALUE这是默认选项，将升序序列的最大值设为10的27次幂，将降序序列的最大值设为-1。
        MINVALUE指定序列的最小值。MINVALUE必须小于或等于START WITH的值，并且必须小于MAXVALUE。
        NOMINVALUE这是默认选项，将升序序列的最小值设为1，将降序序列的最小值设为-10的26次幂。
        CYCLE指定序列在达到最大值或最小值后，将继续从头开始生成值。
        NOCYCLE这是默认选项。指定序列在达到最大值或最小值后，将不能再继续生成值。
        CACHE使用CACHE选项可以预先分配一组序列号，并将其保留在内存中，这样可以更快的访问序列号。
        NOCACHE此项则不会为加快速度而预先分配序列号。如果在创建序列时忽略了CACHE和NOCACHE选项，Oracle将默认缓存20个序列号。
  示例3：Create SEQUENCE toys_seq
           START WITH 10
           INCREMENT BY 2
           MAXVALUE 2000
           MINVALUE 10
           NOCYCLE
           CACHE 30;
  访问序列：可以通过CURRVAL和NEXTVAL伪列来访问该序列的值。
  示例4：演示从序列toys_seq中选择值插入toys表中的toyid列。执行成功将会在该表的toyid列插入值"P10"和"P12"。
         INSERT INTO toys(toyid,toyname,toyprice) values('p'||toys_seq.NEXTVAL,'TWENTY',25);
         INSERT INTO toys(toyid,toyname,toyprice) values('p'||toys_seq.NEXTVAL,'MAGIC PENCIL',75);
  示例5：演示如何查看序列当前值
         Select toys_seq.CURRVAL from dual;
  更改序列：ALTER SEQUENCE命令用于设置或删除MINVALUE或MAXVALUE、修改增量值、修改缓存中的序列号的数目。
  修改序列语法如下：注意，不能修改序列的START WITH参数。在修改序列时，应注意升序序列的最小值应小于最大值。
  ALTER SEQUENCE [schema.]sequence_name
    [INCREMENT BY integer]
    [MAXVALUE integer|NOMAXVALUE]
    [MINVALUE integer|NOMINVALUE]
    [CYCLE|NOCYCLE]
    [CACHE interger|NOCACHE];
  示例6：演示如何设置一个新的MAXVALUE，并为toys_seq序列打开了CYCLE。
        ALTER SEQUENCE toys_seq
          MAXVALUE 5000
          CYCLE;
  可以查询字典视图User_Sequences来查看用户所创建的序列的详细信息

  删除序列语法：Drop SEQUENCE toys_seq

 

二、用户如何有效地利用数据字典
  　ORACLE的数据字典是数据库的重要组成部分之一，它随着数据库的产生而产生, 随着数据库的变化而变化,
体现为sys用户下的一些表和视图。数据字典名称是大写的英文字符。

    数据字典里存有用户信息、用户的权限信息、所有数据对象信息、表的约束条件、统计分析数据库的视图等。
我们不能手工修改数据字典里的信息。

　　很多时候，一般的ORACLE用户不知道如何有效地利用它。

　　dictionary　　　全部数据字典表的名称和解释，它有一个同义词dict
    dict_column　　 全部数据字典表里字段名称和解释

    如果我们想查询跟索引有关的数据字典时，可以用下面这条SQL语句:

    SQL>select * from dictionary where instr(comments,'index')>0;

    如果我们想知道user_indexes表各字段名称的详细含义，可以用下面这条SQL语句:

    SQL>select column_name,comments from dict_columns where table_name='USER_INDEXES';

    依此类推，就可以轻松知道数据字典的详细名称和解释，不用查看ORACLE的其它文档资料了。

    下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。

    1、用户

            查看当前用户的缺省表空间
            SQL>select username,default_tablespace from user_users;

        查看当前用户的角色
        SQL>select * from user_role_privs;

        查看当前用户的系统权限和表级权限
        SQL>select * from user_sys_privs;
        SQL>select * from user_tab_privs;

    2、表

            查看用户下所有的表
            SQL>select * from user_tables;

            查看名称包含log字符的表
            SQL>select object_name,object_id from user_objects
                where instr(object_name,'LOG')>0;

            查看某表的创建时间
            SQL>select object_name,created from user_objects where object_name=upper('&table_name');

            查看某表的大小
            SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
                where segment_name=upper('&table_name');

            查看放在ORACLE的内存区里的表
            SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;

    3、索引

            查看索引个数和类别
            SQL>select index_name,index_type,table_name from user_indexes order by table_name;

            查看索引被索引的字段
            SQL>select * from user_ind_columns where index_name=upper('&index_name');

            查看索引的大小
            SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
                where segment_name=upper('&index_name');

    4、序列号

            查看序列号，last_number是当前值
            SQL>select * from user_sequences;

    5、视图

            查看视图的名称
            SQL>select view_name from user_views;

            查看创建视图的select语句
            SQL>set view_name,text_length from user_views;
            SQL>set long 2000;                说明：可以根据视图的text_length值设定set long 的大小
            SQL>select text from user_views where view_name=upper('&view_name');

    6、同义词

            查看同义词的名称
            SQL>select * from user_synonyms;

    7、约束条件

            查看某表的约束条件
            SQL>select constraint_name, constraint_type,search_condition, r_constraint_name
                from user_constraints where table_name = upper('&table_name');

        SQL>select c.constraint_name,c.constraint_type,cc.column_name
            from user_constraints c,user_cons_columns cc
            where c.owner = upper('&table_owner') and c.table_name = upper('&table_name')
            and c.owner = cc.owner and c.constraint_name = cc.constraint_name
            order by cc.position;

    8、存储函数和过程

            查看函数和过程的状态
            SQL>select object_name,status from user_objects where object_type='FUNCTION';
            SQL>select object_name,status from user_objects where object_type='PROCEDURE';

            查看函数和过程的源代码
            SQL>select text from all_source where owner=user and name=upper('&plsql_name');


connect / as sysdba 

Oracle数据库有哪几种启动方式


说明：

有以下几种启动方式：
1、startup nomount
非安装启动，这种方式启动下可执行：重建控制文件、重建数据库

读取init.ora文件，启动instance，即启动SGA和后台进程，这种启动只需要init.ora文件。


2、startup mount dbname
安装启动，这种方式启动下可执行：
数据库日志归档、
数据库介质恢复、
使数据文件联机或脱机，
重新定位数据文件、重做日志文件。

执行“nomount”，然后打开控制文件，确认数据文件和联机日志文件的位置，
但此时不对数据文件和日志文件进行校验检查。


3、startup open dbname
先执行“nomount”，然后执行“mount”，再打开包括Redo log文件在内的所有数据库文件，
这种方式下可访问数据库中的数据。


4、startup，等于以下三个命令
startup nomount
alter database mount
alter database open


5、startup restrict
约束方式启动
这种方式能够启动数据库，但只允许具有一定特权的用户访问
非特权用户访问时，会出现以下提示：
ERROR：
ORA-01035: ORACLE 只允许具有 RESTRICTED SESSION 权限的用户使用


6、startup force
强制启动方式
当不能关闭数据库时，可以用startup force来完成数据库的关闭
先关闭数据库，再执行正常启动数据库命令


7、startup pfile=参数文件名
带初始化参数文件的启动方式
先读取参数文件，再按参数文件中的设置启动数据库
例：startup pfile=E:Oracleadminoradbpfileinit.ora


8、startup EXCLUSIVE

七、ORACLE 常用的SQL语法和数据对象

一.数据控制语句 (DML) 部分

1.INSERT  (往数据表里插入记录的语句)

INSERT INTO 表名(字段名1, 字段名2, ……) VALUES ( 值1, 值2, ……);
INSERT INTO 表名(字段名1, 字段名2, ……)  SELECT (字段名1, 字段名2, ……) FROM 另外的表名;

字符串类型的字段值必须用单引号括起来, 例如: ’GOOD DAY’
如果字段值里包含单引号’ 需要进行字符串转换, 我们把它替换成两个单引号''.
字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验.

日期字段的字段值可以用当前数据库的系统时间SYSDATE, 精确到秒
或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)
TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC.
年-月-日 小时:分钟:秒 的格式YYYY-MM-DD HH24:MI:SS

INSERT时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型,
方法借用ORACLE里自带的DBMS_LOB程序包.

INSERT时如果要用到从1开始自动增长的序列号, 应该先建立一个序列号
CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) INCREMENT BY 1  START  WITH  1
MAXVALUE  99999  CYCLE  NOCACHE;
其中最大的值按字段的长度来定, 如果定义的自动增长的序列号 NUMBER(6) , 最大值为999999
INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL

2.DELETE  (删除数据表里记录的语句)

DELETE FROM表名 WHERE 条件;

注意：删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些被删除的数据块标成unused.

如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间
TRUNCATE TABLE 表名;
此操作不可回退.

3.UPDATE  (修改数据表里记录的语句)

UPDATE表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 条件;

如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL, 最好在修改前进行非空校验;
值N超过定义的长度会出错, 最好在插入前进行长度校验..

注意事项:
A.        以上SQL语句对表都加上了行级锁,
        确认完成后, 必须加上事物处理结束的命令 COMMIT 才能正式生效,
        否则改变不一定写入数据库里.
        如果想撤回这些操作, 可以用命令 ROLLBACK 复原.

B.        在运行INSERT, DELETE 和 UPDATE 语句前最好估算一下可能操作的记录范围,
        应该把它限定在较小 (一万条记录) 范围内,. 否则ORACLE处理这个事物用到很大的回退段.
        程序响应慢甚至失去响应. 如果记录数上十万以上这些操作, 可以把这些SQL语句分段分次完成,
        其间加上COMMIT 确认事物处理.
二.数据定义 (DDL) 部分

1.CREATE (创建表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)

ORACLE常用的字段类型有
CHAR                        固定长度的字符串
VARCHAR2                可变长度的字符串
NUMBER(M,N)                数字型M是位数总长度, N是小数的长度
DATE                        日期类型

创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面

创建表时可以用中文的字段名, 但最好还是用英文的字段名

创建表时可以给字段加上默认值, 例如 DEFAULT SYSDATE
这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间

创建表时可以给字段加上约束条件
例如 不允许重复 UNIQUE, 关键字 PRIMARY KEY

2.ALTER        (改变表, 索引, 视图等)

改变表的名称
ALTER TABLE 表名1  TO 表名2;

在表的后面增加一个字段
ALTER TABLE表名 ADD 字段名 字段名描述;

修改表里字段的定义描述
ALTER TABLE表名 MODIFY字段名 字段名描述;

给表里的字段加上约束条件
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名);
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名);

把表放在或取出数据库的内存区
ALTER TABLE 表名 CACHE;
ALTER TABLE 表名 NOCACHE;

3.DROP        (删除表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)

删除表和它所有的约束条件
DROP TABLE 表名 CASCADE CONSTRAINTS;

4.TRUNCATE (清空表里的所有记录, 保留表的结构)

TRUNCATE 表名;

三.查询语句 (SELECT) 部分

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE 条件;

字段名可以带入函数
  例如:  COUNT(*), MIN(字段名),  MAX(字段名),  AVG(字段名), DISTINCT(字段名),
           TO_CHAR(DATE字段名,'YYYY-MM-DD HH24:MI:SS')

NVL(EXPR1, EXPR2)函数
解释:
IF EXPR1=NULL
                RETURN EXPR2
ELSE
                       RETURN EXPR1

DECODE(AA﹐V1﹐R1﹐V2﹐R2....)函数
解释:
IF AA=V1 THEN RETURN R1
IF AA=V2 THEN RETURN R2
..…
ELSE
RETURN NULL

LPAD(char1,n,char2)函数
解释:
字符char1按制定的位数n显示，不足的位数用char2字符串替换左边的空位

字段名之间可以进行算术运算
例如:  (字段名1*字段名1)/3

查询语句可以嵌套
例如: SELECT …… FROM
(SELECT …… FROM表名1, [表名2, ……] WHERE 条件) WHERE 条件2;

两个查询语句的结果可以做集合操作
例如: 并集UNION(去掉重复记录), 并集UNION ALL(不去掉重复记录), 差集MINUS,  交集INTERSECT

分组查询
SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] GROUP BY字段名1
[HAVING 条件] ;

两个以上表之间的连接查询

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE
                表名1.字段名 = 表名2. 字段名 [ AND ……] ;

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……] WHERE
                表名1.字段名 = 表名2. 字段名(+) [ AND ……] ;

有(+)号的字段位置自动补空值

查询结果集的排序操作, 默认的排序是升序ASC, 降序是DESC

SELECT字段名1, 字段名2, …… FROM 表名1, [表名2, ……]
ORDER BY字段名1, 字段名2 DESC;

字符串模糊比较的方法

INSTR(字段名, ‘字符串’)>0
字段名 LIKE  ‘字符串%’  [‘%字符串%’]

每个表都有一个隐含的字段ROWID, 它标记着记录的唯一性.

四.ORACLE里常用的数据对象 (SCHEMA)

1.索引 (INDEX)

CREATE INDEX 索引名ON 表名 ( 字段1, [字段2, ……] );
ALTER INDEX 索引名 REBUILD;

一个表的索引最好不要超过三个 (特殊的大表除外), 最好用单字段索引, 结合SQL语句的分析执行情况,
也可以建立多字段的组合索引和基于函数的索引

ORACLE8.1.7字符串可以索引的最大长度为1578 单字节
ORACLE8.0.6字符串可以索引的最大长度为758 单字节

2.视图 (VIEW)

CREATE VIEW 视图名AS SELECT …. FROM …..;
ALTER VIEW视图名 COMPILE;

视图仅是一个SQL查询语句, 它可以把表之间复杂的关系简洁化.

3.同义词 (SYNONMY)
CREATE SYNONYM同义词名FOR 表名;
CREATE SYNONYM同义词名FOR 表名@数据库链接名;

4.数据库链接 (DATABASE LINK)
CREATE DATABASE LINK数据库链接名CONNECT TO 用户名 IDENTIFIED BY 密码 USING ‘数据库连接字符串’;

数据库连接字符串可以用NET8 EASY CONFIG或者直接修改TNSNAMES.ORA里定义.

数据库参数global_name=true时要求数据库链接名称跟远端数据库名称一样

数据库全局名称可以用以下命令查出
SELECT * FROM GLOBAL_NAME;

查询远端数据库里的表
SELECT …… FROM 表名@数据库链接名;

五.权限管理 (DCL) 语句

1.GRANT        赋于权限
常用的系统权限集合有以下三个:
CONNECT(基本的连接), RESOURCE(程序开发), DBA(数据库管理)
常用的数据对象权限有以下五个:
ALL         ON 数据对象名,         SELECT ON 数据对象名,         UPDATE ON 数据对象名,
DELETE         ON 数据对象名,  INSERT ON 数据对象名,   ALTER  ON 数据对象名

GRANT CONNECT, RESOURCE TO 用户名;
GRANT SELECT ON 表名 TO 用户名;
GRANT SELECT, INSERT, DELETE ON表名 TO 用户名1, 用户名2;

2.REVOKE 回收权限

REVOKE CONNECT, RESOURCE FROM 用户名;
REVOKE SELECT ON 表名 FROM 用户名;
REVOKE SELECT, INSERT, DELETE ON表名 FROM 用户名1, 用户名2;


查询数据库中第63号错误：
select orgaddr,destaddr from sm_histable0116 where error_code='63';

查询数据库中开户用户最大提交和最大下发数： select MSISDN,TCOS,OCOS from ms_usertable；


查询数据库中各种错误代码的总和：
select error_code,count(*) from sm_histable0513 group by error_code order
by error_code;

查询报表数据库中话单统计种类查询。
select sum(Successcount) from tbl_MiddleMt0411 where ServiceType2=111
select sum(successcount),servicetype from tbl_middlemt0411 group by servicetype