oracle 复习

oracle复习整理（一）(2009-04-06 19:00:29)
标签：it

sqlplus命令和sql语句的区分
alter table XX add primary key( column name)

查找死锁进程
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;
当前操作用户IP（获取用户信息sys_context(‘userenv’,’?’)）
select sys_context('userenv', 'ip_address') from dual;
select SYS_CONTEXT('USERENV','TERMINAL') terminal from dual
当前操作 程序
dbms_application_info.read_module(v_msg,v_msg1)
换表空间
换表空间后，索引会失效（STATUS）,需要rebuild
SELECT TABLE_NAME, TABLESPACE_NAME,INDEX_NAME, status
FROM USER_INDEXES
select table_name,tablespace_name from user_tables
alter table AC02_SB move tablespace QYMIS_DATA;
ALTER INDEX PK_AC02_SB REBUILD tablespace qymis_data;

ora00600 19004:
execute dbms_stats.delete_schema_stats(‘zlhis’);
zlhis 是你报错的那个表所属的oracle用户
查看表空间
select f.tablespace_name,a.total,u.used,f.free,round((u.used/a.total)*100) "% used",
round((f.free/a.total)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total
from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) used
from dba_extents group by tablespace_name) u,
(select tablespace_name, round(sum(bytes/(1024*1024))) free
from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name
and a.tablespace_name = u.tablespace_name;
增加表空间
select tablespace_name,sum(bytes)/1024/1024 from dba_data_filesalter tablespace 表空间名 add datafile '路径/数据文件' size 1000m Autoextend on next 100m
alter tablespace 表空间名 add datafile '路径/数据文件' size 1000m maxsize 2000m
创建临时表空间

CREATE TEMPORARY TABLESPACE test_temp
TEMPFILE 'C:\oracle\product\10.1.0\oradata\orcl\test_temp01.dbf'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;
创建用户表空间

CREATE TABLESPACE test_data
LOGGING
DATAFILE 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\TEST_DATA01.DBF'
SIZE 32M
AUTOEXTEND ON
NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL;
创建用户并制定表空间

CREATE USER username IDENTIFIED BY password
DEFAULT TABLESPACE TEST_DATA
TEMPORARY TABLESPACE TEST_TEMP


导入|出单个表脚本
imp user/pwd@tnsname file=xxx.dmp tables=tabname,tabname1,tabname2
exp simis/zcsb_82642603_hp@zcsimis file= E:\20091202.dmp tables=deathkiss
exp user/pwd@tnsname file=xxx.dmp tables=tabname,tabname1,tabname2
取年龄：
FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE, 'MM'), TRUNC(AAC006, 'MM')) / 12)

分号
desc
登陆sqlplus conn 账号/口令 网络服务名
conn sys/oracle as sysdba

登陆isqlplus http//127.0.0.1:5560/isqlplus
登陆em http//127.0.0.1:1158/isqlplus

网络服务名的创建两种方式：编辑文件，图形方式
编辑文件：
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = china-08edeb268)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
图形方式：
Oracle-->配置和移植工具-->NetManager-->服务命名。。。。

注释：单行注释-- 多行注释
给一个表加注释：comment on table emp is 'emp';
给一个列加注释：comment on column emp.ename is 'ename';
查询对表或者列的注释：select * from user_tab_comments/user_col_comments


表空间：可不可以低于1M? 可以
最近将数据库从8i升级到10G,8i的一些tablespace 是Dictionary managed,而Locally managed速度要快很多,用户tablespace很容易修改,用Toad就很方便的改了.唯独System tablespace无法用工具修改,只好手动修改.方法如下:
SQL>shutdown immediate;
SQL>startup restrict;
SQL>alter tablespace sfis_ind read only;
……将除了system,sysaux,temp,undo tablespace之外的tablespace 都改为read only状态.
SQL>alter tablespace sysaux offline immediate;
SQL>execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');


环境变量Oracle_Home: e:\oracle\product\10.2.0\db_1
指的是哪个目录 Oracle_base: e:\oracle\product\10.2.0

sql语句的书写规则：不分大小写，不一定必须写在一行，分行只是为了提高可读性
SQL语句尽量写得一致是因为在Oracle内部在执行SQL语句之前要先将SQL语句编译到执行计划树中
如果在计划树当中可以找到一致的SQL语句则不需要再将其编译到执行计划树当中，可以直接执行，提高效率

列的别名不可以用单引号 双引号用在有空格和特殊字符的情况

在sqlplus中执行脚本 @/start
DQL:select
DML:insert delete update
DDL:create alter drop
DCL:grant revoke
数据类型:
char 固定长度 如果在数据库中将char的长度定位5，那么你输入一个字符，测其长度依旧为5
varchar2 可变长度
number类型的精度(5,2)小数部分过长可以四舍五入，整数部分必须符合精度，
例如number(5,2) 123.456可以正常插入 但是1234.5则不能插入

nls_session_parameters数据字典可以查看系统当前默认的日期，货币格式
nls_database_parameters
rownum和rowid
rownum是select之后的结果的行号，可以接受 <=任何值的条件和=1的条件
rowid是物理编号 是不可以重复的

空值null的判断：is null, is not null;null参与运算的结果一定是null
where条件中的like关键字用在字符串的匹配查询，通配符：%任意长度任意字符 _一个任意字符 转义使用escape
数值型，字符型，日期型的转换函数
数值型：靠上取整，靠下取整，截断trunc和四舍五入round
sqlplus中的次幂：3**2=9
字符型： 截取字串 搜索字符串 左填充，右填充 连接字符串
substr下标从一开始
instr 下标从一开始
concat(x,y)
initcap(x)每个字母首字母大写
nvl(x，value)空值判断
nvl2(x，value1，value2)空值判断
lpad
日期型：months_between next_day to_char to_date 日期的四舍五入和截断 2009-7-6按月截断 结果7-1
round(x,'格式')日期取整为最近的一天
trunc(x,'格式')截断MM按月截断为当月的第一天
to_char/to_date
YYYY/YY/YEAR/Year
MM/MONTH/Month/MON/Mon
WW一年中的第几周/w本月中的第几周
DDD本年/DD本月/D本周
HH24/HH
MI分钟
SS秒
聚合函数：用在groupby max count min 空值在聚合函数中的运算：不计算：特例：count(*) 不管有无空值，只取行数
内联视图
group by字句：group by字句限制了select中的输出项
小结
where和having
where是用来过滤行的，having是用来过滤组的
层次查询，加了where字句是用来过滤行的，如果是过滤分支，则把过滤条件放在连接子句上

DML：
insert update delete
事务的开始和结束(rollback commit exit 关闭会话，系统崩溃)
事务开始在当前会话的第一条DML语句
delete 和 truncate
truncate是一条DDL语句，自动提交
delete不提交
表的链接：内连接，外连接(左，右，全)
left(right)join
笛卡尔积：表没有链接条件
自连接
自然链接：不需要指定链接条件
using字句:放共有列，同名，同类型
等值链接，
不等值链接
三张表链接至少需要两个链接条件
select ename,dname
from dept natural join emp
向多表插入数据
insert all
如果两个表都满足，则两张表都插入数据
insert first
如果两个表都满足，则总是插入第一个满足条件的表
层次查询中可以从上到下，也可以从下往上
select ename,level
from emp
where 条件过滤节点
start with mgr is null --从一句开始查询
connect by mgr=prior empno
and 条件过滤分支
查询结果为：
KING 1
JONES 2
SCOTT 3
ADAMS 4
FORD 3
SMITH 4
BLAKE 2
ALLEN 3
WARD 3
MARTIN 3
TURNER 3
JAMES 3
CLARK 2
MILLER 3

DDL
创建表：
约束：主键，外键，非空，唯一，check
非空只能定义在列级
外键在表级和列级是有区别的
创建用户，需要分配配额才可以插入数据
指明默认的表空间 default tablespace
必须显式的指明表空间的配额
回收站
drop table 后加purge 不会添加到回收站
清空回收站命令 purge recyclebin
恢复回收站中的表:
conn sys/oracle as sysbad;
grant execute on dbms_flashback to scott;?
alter table emp enable row movement;
exec dbms_flashback.enable_at_time();--开启回闪
exec dbms_flashback.disable;
alter table
可以修改列 modify，
追加列 add，
删除列 drop column，
添加约束 add constraint，
删除约束 drop constraint
exist：如果内层的select没有查到数据行，则外层的查询也没有数据行
权限：
grant 权限/角色 to 用户
级联的授予系统权限，在回收的时候不可以级联回收
级联的赋予系统权限 with
级联的赋予对象权限 with
select * from role_sys_privs 查看角色系统权限
select * from role_tab_privs 查看角色对象权限
系统权限和对象权限的区别，有没有on关键字
TCL：commit rollback savepoint
视图：对简单的视图可以进行dml操作，复杂的视图通过instead of触发器进行dml操作
视图的好处
plsql
块的结构：
declare
变量名 变量类型 not null/default
begin--必须
exception
end;
常量的声明
类型：标量：number char date varchar2
引用类型:
复合类型：集合
索引表，嵌套表，数组
标量中的boolean不作为列存储
条件
if
elsif
else
end if;
case

end
循环
loop
exit when
end loop;
while loop
end loop;
for i in 1..9 loop
end loop;
跳转：goto　可以跳出循环
异常定义的方式：
if condition then
raise myexception

在捕获异常的exception的时候：
when myexception then
********;

用一个预定义的：
raise_application_error(code,message,keep_errors)
其中code为-20001~-20999 在需要获取这个code的时候用sqlcode
message是需要抛出的异常的信息 在或许这个信息的时候用sqlerrm
keep_errors为一个boolean类型，为true则表示新产生的错误将追加到错误列表中，
为false则替换当前已引发的错误

异常的传播
如果异常被系统捕获，那么异常后面的程序将会被正常执行下去，如果异常抛出但是没有被捕获，那么异常
后面的代码是不会被执行的。

执行部分的异常
执行部分的异常，如果在执行体内部可以被捕获，则在内部处理；如果内部不捕获，则向外层传播，会被外层的处理；

声明部分的异常
在声明部分的异常则不会被内部的异常捕获机制所捕获，会直接向外层传播，被外层的异常处理机制所捕获，在内部
即使没有处理机制也不能被处理。

raise_application_error(code(-20001~-20999),message)
自定义异常
过程的调用
局部子程序的声明
显式游标
声明游标
打开游标
提取游标
关闭游标
用一个程序示例：
declare
cursor cur_emp is
select ename,sal
from emp
where deptno=10；
v_ename emp.enmae%type;
v_sal emp.sal%type;
begin
open cur_emp;
loop
fetch cur_emp into v_ename,v_sal;
exit when cur_emp%notfound;
dbms_out.putline('v_ename'||v_name||','||'v_sal'||v_sal);
end loop;
close cur_emp;
exception
when others then
dbms_out.put_line('程序发生异常！');
end

隐式游标
declare
cursor cur_emp is
select ename,sal
from emp
where deptno=10;
begin
for r in cur_emp loop
dbms_out.put_line('ename'||r.ename||'sal'||r.sal);
end loop;
exception
when others then
dbms_out.put_line('程序异常！');
end;

sql用%取游标属性 如corsor%rowcount .......
select .. into .. 无行的多行都会返回异常
动态sql语句：ddl dcl语句 dml语句中含有不确定字符

数据字典
user_tables
user_objects
user_index
user_source
user_view
函数和过程的区别 函数必须要有返回值 过程可以有也可以没有
子程序的重载：参数个数和参数类型不同，相似不行
包
包头：包规范和包说明
包体
包体依赖包头，独立被存储
触发器
触发器里不可以含有TCL语句，和事件处于同一个事务单元，由事件处理事务
DML触发器
instead of视图触发器
系统触发器,数据库触发器，模式触发器
创建表空间的时候（可以）需要指明数据文件，创建其他则不需要指明数据文件
UTL
数据的导入导出
sqlldr的控制文件


函数总结：
基本的：
Oracle的SQL函数分为单行函数和多行函数。单行函数只对单条记录有效，多行函数对多条记录有效。

单行函数包括，字符、数字、日期、转换和普通函数。

字符函数举例：
?
全小写 LOWER('SQL Course') sql course
?
全大写 UPPER('SQL Course') SQL COURSE
?首字母大写 INITCAP('SQL Course') Sql Course
拼接 CONCAT('Good', 'String') GoodString
取子串 SUBSTR('String',1,3) Str
求长度 LENGTH('String') 6

数字函数举例：
ROUND -- 对数值进行四舍五入操作
TRUNC -- 截断。注意截断和舍入的位数，可以是负数。
ROUND (45.923, 2) 45.92
ROUND (45.923, 0) 46
ROUND (45.923, -1) 50
TRUNC (45.923, 2) 45.92
TRUNC (45.923) 45
TRUNC (45.923, -1) 40
MOD -- 返回两数相除的余数
MOD(1600, 300) -- 100
MOD(2.3,2.1) -- 0.2
日期函数：
?Oracle将日期按照内部格式将日期存成以下七个字节–世纪、年、月、日、时、分、秒。?在Oracle中日期缺省的显示格式为：DD - MON - YY 。?可以使用系统变量 SYSDATE 获取系统时间?。查看 SYSDATE时，可以使用虚拟表DUAL。
举例：
求两个日期间有多少个月份 MONTHS_BETWEEN('01-SEP-95','11-JAN-94') 1.9774194
在日期上加指定的月数 ADD_MONTHS('11-JAN-94',6) '11-JUL-94'
下一个星期几是什么时候 NEXT_DAY('01-SEP-95','FRIDAY') '08-SEP-95'
某月最后一天 LAST_DAY('01-SEP-95') '30-SEP-95'
把日期四舍五入到月份 ROUND('25-MAY-95','MONTH') 01-JUN-95
ROUND('25-MAY-95 ','YEAR') 01-JAN-95
把日期截断到月份 TRUNC('25-MAY-95 ','MONTH') 01-MAY-95
TRUNC('25-MAY-95 ','YEAR') 01-JAN-95

转换函数
TO_CHAR 将数字或日期转换为字符串
TO_NUMBER 将字符串转换成数字
TO_DATE 将字符串转换成日期
在转换函数中会使用格式串

TO_CHAR(date, 'fmt')
格式串包含在''中, 大小写敏感, 可以是任意有效的日期
格式
格式串：
YYYY 表示完整的四位数字年
YEAR 表示英文拼写的年
MM 表示两位数字月
MONTH 表示英文全拼的年
DY 表示三位缩写的星期
DAY 表示英文全拼的星期

一些特殊用法：
时间格式
HH24:MI:SS AM 15:45:32 PM
在格式串中加入字符串
DD " of " MONTH 12 of OCTOBER
用后缀拼出整个日期
ddspth fourteenth

TO_CHAR(number, 'fmt')
使用此函数将数字转换成数字
9 表示数字
0 强制为0
$ 设置美元符号
L 使用当前字符集的货币符号
. 小数点
, 千位分隔符

可以使用TO_NUMBER将字符串转换成数字
可以使用TO_DATE将字符串转换成格式日期
TO_DATE ('10 September 1992', 'DD Month YYYY')

使用格式串
TO_DATE(char [, 'fmt'])


普通函数
NULL值处理函数

NVL (expr1, expr2)
如果expr1为NULL，返回expr2
NVL2 (expr1, expr2, expr3)
如果expr1为非空，则返回expr2，如果expr1为NULL，则返回expr3
NULLIF (expr1, expr2)
如果expr1=expr2，返回NULL，否则返回expr1
COALESCE (expr1, expr2, ..., exprn)
返回表达式列表中的第一个非空表达式


条件表达式，用case表达式或decode实现条件逻辑
CASE表达式
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE results]
END
decode函数
DECODE(col|expression, search1, result1
[, search2,result2,...,]
[, default])
分组函数:
AVG (DISTINCT|ALL|n)
COUNT (DISTINCT|ALL|expr|*)
MAX (DISTINCT|ALL|expr)
MIN (DISTINCT|ALL|expr)
STDDEV (DISTINCT|ALL|n)标准差
SUM (DISTINCT|ALL|n)
VARIANCE (DISTINCT|ALL|n)方差