--==================================== 一.sql语句方面 ====================================================
--========================================================================================================
1.增加主键
alter table TABLE_NAME add constraint KEY_NAME primary key (TABLE_COLUMN);
指定表空间
alter table TABLE_NAME add constraint KEY_NAME primary key (TABLE_COLUMN) using index tablespace TABLE_SPACE_NAME;
2.增加外键
alter table TABLE_NAME add constraint FK_NAME foreign key (TABLE_COLUMN) references KEY_TABLE_NAME;
3.使主键或外键失效、生效
alter table TABLE_NAME disable(enable) constraint KEY_NAME;
4、查看各种约束
select constraint_name,table_name,constraint_type,status from user_constraints;
select constraint_name, constraint_type,search_condition, r_constraint_name from user_constraints where table_name = upper('&table_name')
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;
5、删除主键或外键
alter table TABLE_NAME drop constraint KEY_NAME;
6、建外键
单字段时：create table 表名 (col1 char(8),
cno char(4) REFERENCE course);
多个字段时,在最后加上 Foreign Key (字段名) REFERENCE 表名(字段)
连带删除选项 (on delete cascade
当指定时，如果父表中的记录被删除，则依赖于父表的记录也被删除
REFERENCE 表名（） on delete cascade;
7、删除带约束的表
Drop table 表名 cascade constraints;
8：索引管理
<1>.creating function-based indexes
sql> create index summit.item_quantity on summit.item(quantity-quantity_shipped);
<2>.create a B-tree index
sql> create [unique] index index_name on table_name(column,.. asc/desc) tablespace
sql> tablespace_name [pctfree integer] [initrans integer] [maxtrans integer]
sql> [logging | nologging] [nosort] storage(initial 200k next 200k pctincrease 0
sql> maxextents 50);
<3>.pctfree(index)=(maximum number of rows-initial number of rows)*100/maximum number of rows
<4>.creating reverse key indexes
sql> create unique index xay_id on xay(a) reverse pctfree 30 storage(initial 200k
sql> next 200k pctincrease 0 maxextents 50) tablespace indx;
<5>.create bitmap index
sql> create bitmap index xay_id on xay(a) pctfree 30 storage( initial 200k next 200k
sql> pctincrease 0 maxextents 50) tablespace indx;
<6>.change storage parameter of index
sql> alter index xay_id storage (next 400k maxextents 100);
7.allocating index space
sql> alter index xay_id allocate extent(size 200k datafile 'c:/oracle/index.dbf');
<8>.alter index xay_id deallocate unused;
<9>、查看索引
SQL>select index_name,index_type,table_name from user_indexes order by table_name;
<10>、查看索引被索引的字段
SQL>select * from user_ind_columns where index_name=upper('&index_name');
11、创建序列
select * from user_sequences;
create sequence SEQ_NAME start with 1000
maxvalue 1000 increment by 1;
alter sequence SEQ_NAME minvalue 50 maxvalue 100;
12、删除重复行
update a set aa=null where aa is not null;
delete from a where rowid!=
(select max(rowid) from a b where a.aa=b.aa);
13、删除同其他表相同的行
delete from a where exits
(select 'X' from b where b.no=a.no);
或
delete from a where no in (select no from b);
14、查询从多少行到多少行的记录(可以用在web开发中的分页显示)
select * from ( select rownum row_id,b.* from (select a.* from sys_oper a) b )
where row_id between 15 and 20
15、对公共授予访问权
grant select on 表名 to public;
create public synonym 同义词名 for 表名;
16、填加注释
comment on table 表名 is '注释';
comment on column 表名.列名 is '注释';
17、分布式数据库,创建数据库链路
create [public] database link LINKNAME
[connect to USERNAME identified by PASSWORD]
[using 'CONNECT_STRING']
可以在服务器端，也可以在客户端建立，但必须注意，两台服务器之间
数据库必须可以互访，必须各有各自的别名数据库
18、查看数据库链路
select * from all_db_links;
select * from user_db_links;
查询 select * from TABLENAME@DBLNKNAME;
创建远程数据库同义词
create synonym for TABLENAME@DBLNKNAME;
操纵远程数据库记录
insert into TABLENAME@DBLNKNAME (a,b) values (va,vb);
update TABLENAME@DBLNKNAME set a='this';
delete from TABLENAME@DBLNKNAME;
怎样执行远程的内嵌过程
begin
otherdbpro@to_html(参数);
end;
19、数据库链路用户密码有特殊字符的时候，可以用双引号把密码引起来
create public database link dblink1 connect to db1 identified by "123*456" using 'db11'
20.oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。
<1>下面的语句可以进行总计
select region_code,count(*) from aicbs.acc_woff_notify
group by rollup(region_code);
<2> 对第1个字段小计，最后合计
select region_code,write_status,count(*) from aicbs.acc_woff_notify
group by rollup(region_code,write_status);
----------------------
570 0 3
570 1 2
570 5 --此处小计了570的记录
571 0 10
571 1 2
571 12 --此处小计了571的记录
.....
100 --此处有总计
<3> 复合rollup表达式,只做总计
select region_code,write_status,count(*) from aicbs.acc_woff_notify
group by rollup(region_code,write_status);
<4> 对第1个字段小计，再对第2个字段小计，最后合计
select region_code,write_status,count(*) from aicbs.acc_woff_notify
group by cube(region_code,write_status);
----------------------
100 --此处有总计
0 60 --对write_status＝0的小计
1 39 --对write_status＝1的小计
3 1 --对write_status＝3的小计
570 5 --此处小计了570的记录
570 0 3
570 1 2
571 12 --此处小计了571的记录
571 0 10
571 1 2
....
<3> 复合cube表达式,只做总计
select region_code,write_status,count(*) from aicbs.acc_woff_notify
group by cube(region_code,write_status);
<4>下面的语句可以按照rollup不同的字段进行小计
select region_code,write_status,count(*) from aicbs.acc_woff_notify
group by region_code,rollup(write_status);
21.查询view的创建语句
sql>set long 1000
sql>select * from user_views where view_name='MY_VIEW_NAME';
or
sql>select * from all_views where view_name='MY_VIEW_NAME';
22、去除数据库中特殊字符
<1>.字符串字段中含有"'",如果用来组合sql语句，会造成语句不准确。
比如：replace(f1,'''','')
<2>.字符串字段中含有"\t \n",如果用来在c或者c＋＋程序中输出到文件，格式无法保证。
比如：replace(f2,'\t','')
<3>.清除换行和回车
比如: replace(f2,chr(13)||chr(10),'')
23、如何在字符串里加回车或者tab键
在sqlplus中执行
sql>select 'UserId=1233111'||chr(10)||'AccId=13431'||chr(9)||'AccId2=11111' from dual;
24、树形查询
create table zj(
bm number(8),
bmmc varchar2(20),
sjbm number(8)
)
insert into zj values(1,'aaa',0)
insert into zj values(11,'aaa1',1)
insert into zj values(12,'aaa2',1)
insert into zj values(111,'aaa11',11)
insert into zj values(112,'aaa12',11)
insert into zj values(113,'aaa13',11)
insert into zj values(121,'aaa21',12)
insert into zj values(122,'aaa22',12)
insert into zj values(123,'aaa23',12)
--
select bm,bmmc,sjbm,level
from zj
start with sjbm=0
connect by prior bm = sjbm
或者
select bm,bmmc,sjbm,level
from zj
start with sjbm=0
connect by sjbm = prior bm
25、快照
create snapshot SNAPSHOT_NAME
[storage (storage parameter)]
[tablespace TABLESPACE_NAME]
[refresh [fast\complete\force]
[start with START_DATE next NEXT_DATE]
as QUERY;
create snapshot snapshot_to_study as select * from TABLE_NAME@to_study;
创建角色
create role aa identified by aaa;
授权 grant create snapshot,alter snapshot to aaa;
grant aaa to emp;
create snapshot SNAPSHOT_TO_HTML refresh complete start with sysdate next
sysdate+5/(24*60*60) as select * from a@to_html;
删除 drop snapshot snap_to_html
手工刷新快照,(调用DBMS_SNAPSHOT包中的refresh过程)DBMS_SNAPSHOT.refresh(snapshot_name,refresh_type);
begin
DBMS_SNAPSHOT.REFRESH('snap_to_html','c');
end;
对所有快照进行刷新
begin
DBMS_SNAPSHOT.REFRESH_ALL;
end;
怎样执行远程的内嵌过程
begin
otherdbpro@to_html(参数);
end;
26、用户管理
create a user: database authentication
sql> create user juncky identified by oracle default tablespace users
sql> temporary tablespace temp quota 10m on data password expire
sql> [account lock|unlock] [profile profilename|default];
<1>.查看当前用户的缺省表空间
SQL>select username,default_tablespace from user_users;
<2>生成用户时指定缺省表空间
create user 用户名 identified by 口令 default tablespace 表空间名;
<3>重新指定用户的缺省表空间
alter user 用户名 default tablespace 表空间名
<4>查看当前用户的角色
SQL>select * from user_role_privs;
<5>查看当前用户的系统权限和表级权限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;
<6>查看用户下所有的表
SQL>select * from user_tables;
<7> alter user语句的quota子句限制用户的磁盘空间
如:alter user jf quota 10M on system;
27、查看放在ORACLE的内存区里的表
SQL>select table_name,cache from user_tables where instr(cache,'Y')>0;
28、约束条件
create table employee
(empno number(10) primary key,
name varchar2(40) not null,
deptno number(2) default 10,
salary number(7,2) check salary<10000,
birth_date date,
soc_see_num char(9) unique,
foreign key(deptno) references dept.deptno)
tablespace users;
关键字(primary key)必须是非空,表中记录的唯一性
not null 非空约束
default 缺省值约束
check 检查约束,使列的值符合一定的标准范围
unqiue 唯一性约束
foreign key 外部键约束
29、查看创建视图的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');
30、查看同义词的名称
SQL>select * from user_synonyms;
31、用Sql语句实现查找一列中第N大值
select * from
(select t.*,dense_rank() over (order by sal) rank from employee)
where rank = N;
32 虚拟自段
<1>. CURRVAL 和 nextval
为表创建序列
CREATE SEQUENCE EMPSEQ ... ;
SELECT empseq.currval FROM DUAL ;
自动插入序列的数值
INSERT INTO emp
VALUES (empseq.nextval, 'LEWIS', 'CLERK',
7902, SYSDATE, 1200, NULL, 20) ;
<2>. ROWNUM
按设定排序的行的序号
SELECT * FROM emp WHERE ROWNUM < 10 ;
<3>. ROWID
返回行的物理地址
SELECT ROWID, ename FROM emp WHERE deptno = 20 ;
33、对CLOB字段进行全文检索
SELECT * FROM A WHERE dbms_lob.instr(a.a,'K',1,1)>0;
34. 特殊字符的插入，比如"&"
insert into a values (translate ('at{&}t','at{}','at'));
35.表管理
<1>.create a table
sql> create table table_name (column datatype,column datatype]....)
sql> tablespace tablespace_name [pctfree integer] [pctused integer]
sql> [initrans integer] [maxtrans integer]
sql> storage(initial 200k next 200k pctincrease 0 maxextents 50)
sql> [logging|nologging] [cache|nocache]
<2>.copy an existing table
sql> create table table_name [logging|nologging] as subquery
<3> create table ... as 方式建表的时候，指定表参数
create table a
storage(
initial 1M /*第一次创建时分配空间*/
next 1M /*第一次分配的存储空间用完时在分配*/
)
as select * from b;
<4>.创建临时表
sql> create global temporary table xay_temp as select * from xay;
on commit preserve rows/on commit delete rows
在Oracle中，可以创建以下两种临时表:
a 会话特有的临时表:
create global temporary table () on commit preserve rows;
会话指定，当中断会话时ORACLE将截断表
b 事务特有的临时表:
create global temporary table () on commit delete rows;
事务指定,每次提交后ORACLE将截断表（删除全部行）
c 说明
临时表只在当前连接内有效
临时表不建立索引,所以如果数据量比较大或进行多次查询时,不推荐使用
数据处理比较复杂的时候时表快,反之视图快点
在仅仅查询数据的时候建议用游标: open cursor for 'sql clause';
<5>
pctfree = (average row size - initial row size) *100 /average row size
pctused = 100-pctfree- (average row size*100/available data space)
<6>.change storage and block utilization parameter
sql> alter table table_name pctfree=30 pctused=50 storage(next 500k
sql> minextents 2 maxextents 100);
<7>.manually allocating extents
sql> alter table table_name allocate extent(size 500k datafile 'c:/oracle/data.dbf');
<8>.move tablespace
sql> alter table employee move tablespace users;
<9>.deallocate of unused space
sql> alter table table_name deallocate unused [keep integer]
<10>.drop a column
sql> alter table table_name drop column comments cascade constraints checkpoint 1000;
alter table table_name drop columns continue;
<11>.mark a column as unused
sql> alter table table_name set unused column comments cascade constraints;
alter table table_name drop unused columns checkpoint 1000;
alter table orders drop columns continue checkpoint 1000
data_dictionary : dba_unused_col_tabs
37. 中文是如何排序的？
Oracle9i之前，中文是按照二进制编码进行排序的。
在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值
SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序
SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序
SCHINESE_PINYIN_M 按照拼音排序
38. 数据表中的字段最大数:
表或视图中的最大列数为 1000
39. oracle中的裸设备:
裸设备就是绕过文件系统直接访问的储存空间
40. 在Oracle服务器上通过SQLPLUS查看本机IP地址 ?
select sys_context('userenv','ip_address') from dual;
如果是登陆本机数据库，只能返回127.0.0.1
41. 在ORACLE中取毫秒?
9i之前不支持,9i开始有timestamp.
9i可以用select systimestamp from dual;
42. 将N秒转换为时分秒格式？
set serverout on
declare
N number := 1000000;
ret varchar2(100);
begin
ret := trunc(n/3600) || '小时' || to_char(to_date(mod(n,3600),'sssss'),'fmmi"分 "ss"秒"') ;
dbms_output.put_line(ret);
end;
43、在某个用户下找所有的索引
select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;
44. not in的替代。
一般not in的效率比较低。特别是数据量大的时候，几乎不能执行。
用下面几种方式可以替换写法
比如要查询在fee_rev_info表中已经销户的用户（不在cm_user中的）(不过下面的例子不是很好，因为bill_id是cm_user的唯一索引）
select * from fee_rev_info where bill_id not in (select bill_id from cm_user)
<1> 用not exists
select * from fee_rev_info a where not exists (select 'p' from cm_user b where b.bill_id = a.bill_id)
<2> 用外连接(+)
select a.* from fee_rev_info a,cm_user b
where a.bill_id = b.bill_id (+)
and b.bill_id is null
<3> 用hash_aj
select /*+HASH_AJ*/* from fee_rev_info where bill_id not in (select bill_id from cm_user)
45.怎么样查询特殊字符，如通配符%与_
假如数据库中有表 STATIONTYPE,STATION_571 STATION_572 ...
select * from tab where tname like 'STATION_%'
会显示 STATIONTYPE,STATION_571 ... 可以用下面的语句
select * from tab where tname like 'STATION\_%' escape'\'
46.如果存在就更新，不存在就插入可以用一个语句实现吗
9i已经支持了，是Merge，但是只支持select子查询，
如果是单条数据记录，可以写作select .... from dual的子查询。
语法为：
MERGE INTO table
USING data_source
ON (condition)
WHEN MATCHED THEN update_clause
WHEN NOT MATCHED THEN insert_clause;
如
MERGE INTO cm_user_credit
USING (select * from dual) ON (user_id =1302514690 )
when MATCHED then update set credit_value = 1000
when NOT MATCHED then insert (user_id,acc_id,bill_id,plan_id,region_code,credit_value) values(1302514690,1305032158,'13857141218',10070247,'571',1000);
47.怎么实现一条记录根据条件多表插入
9i以上可以通过Insert all语句完成，仅仅是一个语句，如：
INSERT ALL
WHEN (id=1) THEN
INTO table_1 (id, name)
values(id,name)
WHEN (id=2) THEN
INTO table_2 (id, name)
values(id,name)
ELSE
INTO table_other (id, name)
values(id, name)
SELECT id,name
FROM a;
如果没有条件的话，则完成每个表的插入，如
INSERT ALL
INTO table_1 (id, name)
values(id,name)
INTO table_2 (id, name)
values(id,name)
INTO table_other (id, name)
values(id, name)
SELECT id,name
FROM a;
48.如何实现行列转换
<1>、固定列数的行列转换
如
student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
...
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
...
语句如下：
select student,sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student
<2>、不定列行列转换
如
c1 c2
--------------
1 我
1 是
1 谁
2 知
2 道
3 不
...
转换为
1 我是谁
2 知道
3 不
这一类型的转换必须借助于PL/SQL来完成，这里给一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;
/
SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可
--例子：
create table okcai_1
(
user_id varchar2(10),
user_number varchar2(10),
user_num number(8)
)
user_id user_number user_num
---------------------
1 123 2
1 456 5
1 789 6
2 11 2
2 22 3
2 33 4
2 44 5
2 55 6
2 66 7
2 77 8
3 1234 1
3 5678 2
方式一：
create or replace function get_col(
p_userId number,
p_col number
) return varchar
as
v_tmp varchar2(255);
begin
select user_number||chr(9)||user_num into v_tmp
from
(select user_number,user_num,rownum row_id
from okcai_1
where user_id = p_userId) a
where row_id = p_col;
return ltrim(v_tmp);
--return v_tmp;
end;
然后
select distinct user_id,get_col(user_id,1),get_col(user_id,2),get_col(user_id,3) .... from okcai_1
方式二：
create or replace function get_col(
p_userId number,
p_col number
) return varchar
as
v_tmp varchar2(255);
begin
select user_number||chr(9)||user_num into v_tmp
from
(select user_number,user_num,rownum row_id
from okcai_1
where user_id = p_userId) a
where row_id = p_col;
return ltrim(v_tmp);
--return v_tmp;
end;
select distinct user_id,get_col_new(user_id) from okcai_1;
49.怎么设置存储过程的调用者权限
普通存储过程都是所有者权限，如果想设置调用者权限，请参考如下语句
create or replace
procedure ...()
AUTHID CURRENT_USER
As
begin
...
end;
50.Oracle有哪些常见关键字
详细信息可以查看v$reserved_words视图
51.怎么查看数据库参数
<1> show parameter 参数名
如通过show parameter spfile可以查看9i是否使用spfile文件
其中参数名是可以匹配的。
比如show parameter cursor ,则会显示跟cursor相关的参数
<2>
select * from v$parameter
<3>
除了这部分参数，Oracle还有大量隐含参数，可以通过如下语句查看:
SELECT NAME
,VALUE
,decode(isdefault, 'TRUE','Y','N') as "Default"
,decode(ISEM,'TRUE','Y','N') as SesMod
,decode(ISYM,'IMMEDIATE', 'I',
'DEFERRED', 'D',
'FALSE', 'N') as SysMod
,decode(IMOD,'MODIFIED','U',
'SYS_MODIFIED','S','N') as Modified
,decode(IADJ,'TRUE','Y','N') as Adjusted
,description
FROM ( --GV$SYSTEM_PARAMETER
SELECT x.inst_id as instance
,x.indx+1
,ksppinm as NAME
,ksppity
,ksppstvl as VALUE
,ksppstdf as isdefault
,decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE') as ISEM
,decode(bitand(ksppiflg/65536,3),
1,'IMMEDIATE',2,'DEFERRED','FALSE') as ISYM
,decode(bitand(ksppstvf,7),1,'MODIFIED','FALSE') as IMOD
,decode(bitand(ksppstvf,2),2,'TRUE','FALSE') as IADJ
,ksppdesc as DESCRIPTION
FROM x$ksppi x
,x$ksppsv y
WHERE x.indx = y.indx
AND substr(ksppinm,1,1) = '_'
AND x.inst_id = USERENV('Instance')
)
ORDER BY NAME
52.怎样建立基于函数索引
8i以上版本，确保
Query_rewrite_enabled=true
Query_rewrite_integrity=trusted
Compatible=8.1.0以上
Create index indexname on table (function(field));
53.怎么样移动表或表分区
[A]移动表的语法
Alter table tablename move
[Tablespace new_name
Storage(initial 50M next 50M
pctincrease 0 pctfree 10 pctused 50 initrans 2) nologging]
移动分区的语法
alter table tablename move (partition partname)
[update global indexes]
之后之后必须重建索引
Alter index indexname rebuild
如果表有Lob段，那么正常的Alter不能移动Lob段到别的表空间，而仅仅是移动了表段，可以采用如下的方法移动Lob段
alter table tablename move
lob(lobsegname) store as (tablespace newts);
54.怎么样修改表的列名
[A]9i以上版本可以采用rname命令
ALTER TABLE UserName.TabName
RENAME COLUMN SourceColumn TO DestColumn
9i以下版本可以采用create table …… as select * from SourceTable的方式。
另外，8i以上可以支持删除列了
ALTER TABLE UserName.TabName
SET UNUSED (ColumnName) CASCADE CONSTRAINTS
ALTER TABLE UserName.TabName
DROP (ColumnName) CASCADE CONSTRAINTS
55.case的用法
在sql语句中
CASE test_value
WHEN expression1 THEN value1
[[WHEN expression2 THEN value2] [...]]
[ELSE default_value]
END
比如1
SELECT last_name, job_id, salary
CASE job_id
WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees
比如2
select
case
when real_charge>=20000 and real_charge<30000 then 5000
when real_charge>=30000 and real_charge<40000 then 9000
when real_charge>=40000 and real_charge<50000 then 10000
when real_charge>=50000 and real_charge<60000 then 14000
when real_charge>=60000 and real_charge<70000 then 18000
when real_charge>=70000 and real_charge<80000 then 19000
when real_charge>=80000 and real_charge<90000 then 24000
when real_charge>=90000 and real_charge<100000 then 27000
when real_charge>=100000 and real_charge<110000 then 27000
when real_charge>=110000 and real_charge<120000 then 29000
when real_charge>=120000 then 36000
else
0
end ,acc_id,user_id,real_charge from okcai_jh_charge_200505
在存储过程中
case v_strGroupClassCode
when '1' then
v_nAttrNum := v_nAttrNum + 300;
v_strAttrFlag := '1'||substr(v_strAttrFlag,2,7);
when '2' then
v_nAttrNum := v_nAttrNum + 200;
v_strAttrFlag := '2'||substr(v_strAttrFlag,2,7);
else
NULL;
end case;
注意的是存储过程和sql语句有的细微差别是用end case，而不是end。语句后面跟";"
--========================================================================================================
--======================================= 二.pl/sql 编程方面 =============================================
--========================================================================================================
1.自治事务:8i以上版本，不影响主事务。
在存储过程的is\as
后面声明PRAGMA AUTONOMOUS_TRANSACTION;
自治事务防止嵌套提交，使事务在自己的事务区内提交或回滚不会影响其他的事务。
2、包
包说明(package specification)，包头,存放关于包的内容的信息，定义包的用户可见的过程、
函数，数据类型和变量
create or replace package tt_aa as
v1 varchar2(10);
v2 varchar2(10);
v3 number;
v4 boolean;
procedure proc1(x number);
procedure proc2(y varchar2);
procedure proc3(z number);
function my_add(x number,y number) return number;
end;
包主体（package body)是可选的
create or replace package body tt_aa as
procedure proc1(x number) as
begin
v1:=to_char(x);
end;
procedure proc2(y varchar2) as
begin
v2:=y;
end;
procedure proc3(z number) as
begin
v1:=z;
end;
procedure proc4(x number,y number) return number as
begin
return x+y;
end;
end;
调用
begin
tt_aa.proc1(6);
dbms_output.put_line(to_char(tt_aa.my_add(1,3));
end;
3、动态sql(使用dbms_sql)
create or replace procedure my_execute(sql_string in varchar2) as
v_cursor number;
v_numrows interger;
begin
v_cursor:=dbms_sql.open_cursor;
dbms_sql.parse(v_cursor,sql_string,dbms_sql.v7);
v_numrows:=dbms_sql.execute(v_cursor);
dbms_sql.close_cursor(v_cursor);
end;
则可以
sql>exec my_execute('select * from tab');
sql>exec my_execute('insert into test value'||'('||'''ddd'''||')');
sql>exec my_execute('commit');
对于查询方面的可以如下方式：
比如想用游标查询一个表，但是这个表是分月的，每个月可能表名都会改变。
create or replace procedure proc_test as
v_curid integer;
v_result integer;
v_strSql varchar2(255);
v_userid okcai.userid%type;
v_username okcai.username%type;
begin
v_strSql := 'select * from okcai_'||to_char(sysdate,'yyyymm');
v_curid := dbms_sql.open_cursor;
dbms_sql.parse(v_curid,v_strSql,dbms_sql.v7);
dbms_sql.define_column(v_curid,1,v_userid);
dbms_sql.define_column(v_curid,2,v_username,10); --必须指定大小
v_result := dbms_sql.execute(v_curid);
loop
if dbms_sql.fetch_rows(v_curid) = 0 then
exit; --没有了 ，退出循环
end if;
dbms_sql.column_value(v_curid,1,v_userid);
dbms_sql.column_value(v_curid,2,v_username);
dbms_output.put_line(v_userid);
dbms_output.put_line(v_username);
end loop;
dbms_sql.close(v_curid);
end;
4、用EXECUTE IMMEDIATE
<1>. 在PL/SQL运行DDL语句
begin
execute immediate 'set role all';
end;
<2>. 给动态语句传值(USING 子句)
declare
l_depnam varchar2(20) := 'testing';
l_loc varchar2(10) := 'Dubai';
begin
execute immediate 'insert into dept values (:1, :2, :3)'
using 50, l_depnam, l_loc;
commit;
end;
<3>. 从动态语句检索值(INTO子句)
declare
l_cnt varchar2(20);
begin
execute immediate 'select count(1) from emp'
into l_cnt;
dbms_output.put_line(l_cnt);
end;
<4>. 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.黓认为IN类型,其它类型必须显式指定
declare
l_routin varchar2(100) := 'gen2161.get_rowcnt';
l_tblnam varchar2(20) := 'emp';
l_cnt number;
l_status varchar2(200);
begin
execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
using in l_tblnam, out l_cnt, in out l_status;
if l_status != 'OK' then
dbms_output.put_line('error');
end if;
end;
<5>. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量
declare
type empdtlrec is record (empno number(4),
ename varchar2(20),
deptno number(2));
empdtl empdtlrec;
begin
execute immediate 'select empno, ename, deptno ' ||
'from emp where empno = 7934'
into empdtl;
end;
<6>. 传递并检索值.INTO子句用在USING子句前
declare
l_dept pls_integer := 20;
l_nam varchar2(20);
l_loc varchar2(20);
begin
execute immediate 'select dname, loc from dept where deptno = :1'
into l_nam, l_loc
using l_dept ;
end;
<7>. 多行查询选项.对此选项用insert语句填充临时表，用临时表进行进一步的处理,也可以用REF cursors纠正此缺憾.
declare
l_sal pls_integer := 2000;
begin
execute immediate 'insert into temp(empno, ename) ' ||
' select empno, ename from emp ' ||
' where sal > :1'
using l_sal;
commit;
end;
<8>. 完成update的returning功能
update可以用returning返回修改以后的值。比如:
UPDATE employees
SET job_id =’SA_MAN’, salary = salary + 1000, department_id = 140
WHERE last_name = ’Jones’
RETURNING salary*0.25, last_name, department_id
INTO :bnd1, :bnd2, :bnd3;
用execute immediate来完成的时候，可以用
declare
l_sal pls_integer;
begin
execute immediate 'update employees SET salary = salary + 1000 where last_name=''okcai'' RETURNING INTO :1'
returning into v_sql;
commit;
end;
5、用ref cursor来完成动态游标的功能
declare
type ct is ref cursor;
cc ct;
v_notify acc_woff_notify%rowtype;
begin
open cc for 'select * from acc_woff_notify';
loop
fetch cc into v_notify;
exit when cc%notfound;
dbms_output.put_line(v_notify.done_code);
end loop;
close cc;
end;
6、重新编译
对失效的过程
sql>exec dbms_utility.compile_schema(schema);
如：
sql>exec dbms_utility.compile_schema(scott);
7.存储过程使用table类型
<1>.字符串数组
declare
type regionType is table of varchar2(3) index by binary_integer;
v_listRegion regionType;
i number(2):=0;
begin
v_listRegion(1):='571';
v_listRegion(2):='572';
v_listRegion(3):='573';
v_listRegion(4):='574';
v_listRegion(5):='575';
v_listRegion(6):='576';
v_listRegion(7):=null;
i := 1;
while i<= v_listRegion.last loop
dbms_output.put_line( v_listRegion(i) );
i := v_listRegion.next(i);
end loop;
end;
<2>.rowtype数组
declare
type CmUserType is table of cm_user%rowtype index by binary_integer;
v_listUser CmUserType;
i number(5):=0;
r_user cm_user%rowtype;
begin
i := 1;
for r_user in (select * from cm_user where rownum<=5) loop
v_listUser(i):= r_user;
i := i + 1;
end loop;
i := 1;
while i<= v_listUser.last loop
dbms_output.put_line( v_listUser(i).bill_id );
i := v_listUser.next(i);
end loop;
end;
<3>. record数组
declare
type recCmUserType is record (bill_id cm_user.bill_id%type,cust_name varchar2(25));
type CmUserType is table of recCmUserType index by binary_integer;
v_listUser CmUserType;
i number(5):=0;
r_user cm_user%rowtype;
begin
i := 1;
for r_user in (select * from cm_user where rownum<=5) loop
v_listUser(i).bill_id:= r_user.bill_id;
v_listUser(i).cust_name:= '客户'||i;
i := i + 1;
end loop;
i := 1;
while i<= v_listUser.last loop
dbms_output.put_line( v_listUser(i).bill_id );
dbms_output.put_line( v_listUser(i).cust_name );
i := v_listUser.next(i);
end loop;
end;
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>set long 1000
SQL>set pagesize 0
SQL>set trimspool on
SQL>select text from all_source where owner=user and name=upper('&plsql_name');
9、触发器
查看触发器
set long 50000;
set heading off;
set pagesize 2000;
select
'create or replace trigger "' ||
trigger_name || '"' || chr(10)||
decode( substr( trigger_type, 1, 1 ),
'A', 'AFTER', 'B', 'BEFORE', 'I', 'INSTEAD OF' ) ||
chr(10) ||
triggering_event || chr(10) ||
'ON "' || table_owner || '"."' ||
table_name || '"' || chr(10) ||
decode( instr( trigger_type, 'EACH ROW' ), 0, null,
'FOR EACH ROW' ) || chr(10) ,
trigger_body
from user_triggers;
10. 加密ORACLE的存储过程
用wrap命令,如:
下列存储过程内容放在AA.SQL文件中
create or replace procedure testCCB(i in number) as
begin
dbms_output.put_line('输入参数是'||to_char(i));
end;
SQL>wrap iname=a.sql;
PL/SQL Wrapper: Release 8.1.7.0.0 - Production on Tue Nov 27 22:26:48 2001
Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved.
Processing a.sql to a.plb
提示a.sql转换为a.plb，这就是加密了的脚本，执行a.plb即可生成加密了的存储过程
运行a.plb
SQL> @a.plb ;
11.怎么样利用游标更新数据
cursor c1 is
select * from tablename
where name is null for update [of column]
...
update tablename set column = ...
where current of c1;
但是如果这种方式打开以后做了commit，则下次fetch会报ora-01002错误
12.怎样自定义异常
pragma_exception_init(exception_name,error_number);
如果立即抛出异常
raise_application_error(error_number,error_msg,true|false);
其中number从-20000到-20999，错误信息最大2048B
异常变量
SQLCODE 错误代码
SQLERRM 错误信息
13.在pl/sql中执行DDL语句
<1>、8i以下版本dbms_sql包
<2>、8i以上版本还可以用
execute immediate sql;
dbms_utility.exec_ddl_statement('sql');
14.用java写存储过程包
<1>
create or replace and compile
java source
named "CHelloWorld" as
public class HelloWorld
{
public static String print()
{
return System.out.println("Hello,World");
}
};
/
<2>
create or replace function my_helloWorld return varchar2
as language java
name 'HelloWorld.print() return java.lang.String';
/
<3>
select my_helloWorld from dual;
--========================================================================================================
--==================================== 三.系统函数和包使用方面 ===========================================
--========================================================================================================
1.十进制和十六进制转换
(oracle 8i以后)
select to_char(125,'XXXXX') from dual
-----------
7D
select to_char(125,'xxxxx') from dual
-----------
7d
select to_number('7D','XXXXX') from dual
-----------
125
2. ORACLE产生随机函数
DBMS_RANDOM.RANDOM
3、调度程序 DBMS_JOB
broken 中止一个任务调度
change 修改任务的属性
internal 改变间隔
submit 任务发送到任务队列中去
next_date 改变任务的运行时间
remove 删除一个任务
run 立即执行一个任务
submit 提交一个任务
user_export 任务说明
what 改变任务运行的程序
查询
select * from user_job;
建立一存储过程
create or replace procedure log_proc as
begin
insert into test(aa) values(sysdate);
commit;
end;
提交一个任务
declare
job_num number;
begin
dbms_job.submit(job_num,'log_proc;',sysdate,sysdate+5/(24*60*60),false);
dbms_output.put_line('Job numer='||to_char(job_num));
end;
1> 上面程序从当前开始，间隔5秒执行一次。
2> 如果每天几点执行，可以写为(比如从2004-09-13开始执行，每天7点执行)
next_date => to_date('13-09-2004 07:00:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'trunc(sysdate)+(7+24)/24')
3> 如果是每个月几号开始执行。比如每月2号21点执行。
add_months(trunc(sysdate,'MONTH'),1) + 2-1 + 21/24
移走任务
begin
dbms_job.remove(1);
end;
中止任务
begin
dbms_job.broken(1,true);
end;
查询正在执行的job
select * from dba_jobs_running
如果运行比较慢，加
select /*+ rule */* from dba_jobs_running
4.UTL_FILE包
在PL/SQL 3.3以上的版本中，UTL_FILE包允许用户通过PL/SQL读写操作系统文件。如下：
DECALRE
FILE_HANDLE UTL_FILE.FILE_TYPE;
BEGIN
FILE_HANDLE:=UTL_FILE.FOPEN('C:\','TEST.TXT','A');
UTL_FILE.PUT_LINE(FILE_HANDLE,'HELLO,IT iS A TEST TXT FILE');
UTL_FILE.FCLOSE(FILE_HANDLE);
END;
比如：怎么样在Oracle中写操作系统文件，如写日志
可以利用utl_file包，但是，在此之前，要注意设置好Utl_file_dir初始化参数
/**************************************************************************
parameter:textContext in varchar2 日志内容
desc: ·写日志,把内容记到服务器指定目录下
·必须配置Utl_file_dir初始化参数，并保证日志路径与Utl_file_dir路径一致或者是其中一个
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_Write_log(text_context VARCHAR2)
IS
file_handle utl_file.file_type;
Write_content VARCHAR2(1024);
Write_file_name VARCHAR2(50);
BEGIN
--open file
write_file_name := 'db_alert.log';
file_handle := utl_file.fopen('/u01/logs',write_file_name,'a');
write_content := to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'||'||text_context;
--write file
IF utl_file.is_open(file_handle) THEN
utl_file.put_line(file_handle,write_content);
END IF;
--close file
utl_file.fclose(file_handle);
EXCEPTION
WHEN OTHERS THEN
BEGIN
IF utl_file.is_open(file_handle) THEN
utl_file.fclose(file_handle);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END sp_Write_log;
5.SYS_CONTEXT的详细用法
select
SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_formAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data
from dual
6.怎么样在过程中暂停指定时间
DBMS_LOCK包的sleep过程
如：dbms_lock.sleep(5);表示暂停5秒。
7.怎么在Oracle中发邮件
可以利用utl_smtp包发邮件，以下是一个发送简单邮件的例子程序
/****************************************************************************
parameter: Rcpter in varchar2 接收者邮箱
Mail_Content in Varchar2 邮件内容
desc: ·发送邮件到指定邮箱
·只能指定一个邮箱，如果需要发送到多个邮箱，需要另外的辅助程序
****************************************************************************/
CREATE OR REPLACE PROCEDURE sp_send_mail( rcpter IN VARCHAR2,
mail_content IN VARCHAR2)
IS
conn utl_smtp.connection;
--write title
PROCEDURE send_header(NAME IN VARCHAR2, HEADER IN VARCHAR2) AS
BEGIN
utl_smtp.write_data(conn, NAME||': '|| HEADER||utl_tcp.CRLF);
END;
BEGIN
--opne connect
conn := utl_smtp.open_connection('smtp.com');
utl_smtp.helo(conn, 'oracle');
utl_smtp.mail(conn, 'oracle info');
utl_smtp.rcpt(conn, Rcpter);
utl_smtp.open_data(conn);
--write title
send_header('From', 'Oracle Database');
send_header('To', '"Recipient" ');
send_header('Subject', 'DB Info');
--write mail content
utl_smtp.write_data(conn, utl_tcp.crlf || mail_content);
--close connect
utl_smtp.close_data(conn);
utl_smtp.quit(conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(conn);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
WHEN OTHERS THEN
NULL;
END sp_send_mail;
8.怎么样获取对象的DDL语句
第三方工具就不说了主要说一下9i以上版本的dbms_metadata
<1>获得单个对象的DDL语句
set heading off
set echo off
set feedback off
set pages off
set long 90000
select dbms_metadata.get_ddl('TABLE','TABLE_NAME','SCAME') from dual;
比如
select dbms_metadata.get_ddl('TABLE','CM_USER','AICBS') from dual;
<2>.如果获取整个用户的脚本，可以用如下语句
select dbms_metadata.get_ddl('TABLE',u.table_name) from user_tables u;
当然，如果是索引，则需要修改相关table到index
<3>.还有
dbms_metadata.get_xml()
--========================================================================================================
--==================================== 四.DBA管理方面 ====================================================
--========================================================================================================
1、通常oracle需要启动OracleServiceORCL,OracleStartORCL,OracleTNSListener 任务
在NT上至少要启动两个服务
oraclestartID和oracleserverID
每个数据库都有一个系统标识符(SID)，典型安装的数据库使用的系统标识符是ORCL
2、启动关闭数据库
关闭：
svrmgr>connect internal/oracle
>shutdown --正常关闭数据库
svrmgr>shutdown immediate --立即关闭数据库
svrmgr>shutdown abort --一种最直接的关闭数据库的方式，执行之后，重新启动需要花6-8小时
启动：
svrmgr>startup --正常启动
--等价于:startup nomount;
alter database mount;
alter database open;
svrmgr>startup mount; --安装启动：用于改变数据库的归档或执行恢复状态
svrmgr>startup nomount; --用于重建控制文件或重建数据库
svrmgr>startup restrict; --约束启动，能启动数据库，但只允许具有一定特权的用户访问
如果希望改变这种状态，连接成功后
alter system disable restricted session;
svrmgr>startup force;当不能关闭数据库时，可采用强制启动数据库来完成数据库的关闭操作。
svrmgr>startup pfile=d:\orant\database\initorcl.ora --带初始化参数文件的启动
3、缺省用户和密码
<1>. Oracle安裝完成后的初始口令?
internal/oracle
sys/change_on_install
system/manager
scott/tiger
sysman/oem_temp
<2>. ORACLE9IAS WEB CACHE的初始默认用户和密码？
administrator/administrator
4、让定义自己的回滚段生效
在initorcl.ora中加入rollback_segments=(rb0,rb1,...)
其中rb0,rb1为自己定义的回滚段，可使这些回滚段在启动时生效
5、查看修改数据库的字符集
<1>数据库服务器字符集
在表props$中
update props$ set value$='ZHS16CGB231280'
where name ='NLS_CHARACTERSET'
然后重新启动数据库，而不需要重新安装
8i以上版本可以通过alter database来修改字符集，但也只限于子集到超集，不建议修改props$表，将可能导致严重错误。
Startup nomount;
Alter database mount exclusive;
Alter system enable restricted session;
Alter system set job_queue_process=0;
Alter database open;
Alter database character set zhs16gbk;
sql> show parameter NLS
查看数据库字符集:
SELECT * FROM NLS_DATABASE_PARAMETERS;
SELECT * FROM V$NLS_PARAMETERS;
<2>
客户端字符集环境select * from nls_instance_parameters,其来源于v$parameter，
表示客户端的字符集的设置，可能是参数文件，环境变量或者是注册表
会话字符集环境 select * from nls_session_parameters，其来源于v$nls_parameters，表示会话自己的设置，可能是会话的环境变量或者是alter session完成，如果会话没有特殊的设置，将与nls_instance_parameters一致。
客户端的字符集要求与服务器一致，才能正确显示数据库的非Ascii字符。如果多个设置存在的时候，alter session>环境变量>注册表>参数文件
字符集要求一致，但是语言设置却可以不同，语言设置建议用英文。如字符集是zhs16gbk，则nls_lang可以是American_America.zhs16gbk。
<3> 有时候用crontab发起的时候，由于执行的shell脚本的不同，导致很多的环境变量不同。常常看到插入到数据库中的汉字变成乱码。
比如shell脚本cai.sh如下内容。
#!/bin/ksh
export ORACLE_BASE=/u01/oracle/app/oracle
export ORACLE_HOME=${ORACLE_BASE}/product/9.2.0
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:$LD_LIBRARY_PATH
export SHLIB_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/lib32:/app/prepay/lib
/u01/oracle/app/oracle/product/9.2.0/bin/sqlplus aicbs/aicbs@busi_cs <insert into okcai values('蔡延寿是个大好人呀');
commit;
quit
!
在crontab中定时每天20:19执行一次
19 20 * * * /bin/sh /app/prepay/cai.sh >/dev/null 2>&1 &
则可以看到数据库中数据变成了:
2LQSJY
当前数据库的字符集是
SELECT * FROM NLS_DATABASE_PARAMETERS
AMERICAN.ZHS16GBK
为了正常，必须保持客户端和数据库一致的字符集
改脚本如下即可
#!/bin/ksh
export ORACLE_BASE=/u01/oracle/app/oracle
export ORACLE_HOME=${ORACLE_BASE}/product/9.2.0
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:$LD_LIBRARY_PATH
export SHLIB_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/rdbms/lib32:/app/prepay/lib
####下面就是增加的
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
/u01/oracle/app/oracle/product/9.2.0/bin/sqlplus aicbs/aicbs@busi_cs <insert into okcai values('蔡延寿是个大好人呀');
commit;
quit
!
6、svgmgr>show sga
7、查询锁的原因
如果进程被死锁，可以按下面方式查询
<1> 从v$session或者v$locked_object找到此session
<2> 如果有lockwait，查询v$lock,
select * from v$lock where kaddr = 'C00000024AB87210'
如果没有，根据sid
select * from v$lock where sid = 438
<3> 查看v$lock
lmode > 0，表示已经得到此锁
request > 0 表示正在请求此锁
根据id1和id2的值可以判断请求哪个锁的释放。
select * from v$lock where id1=134132 and id2 = 31431
8. 查询锁的状况的对象
V$LOCK, V$LOCKED_OBJECT, V$SESSION, V$SQLAREA, V$PROCESS ;
查询锁的表的方法:
SELECT S.SID SESSION_ID, S.USERNAME,
DECODE(LMODE, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(LMODE)) MODE_HELD,
DECODE(REQUEST, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(REQUEST)) MODE_REQUESTED,
O.OWNER||'.'||O.OBJECT_NAME||' ('||O.OBJECT_TYPE||')', S.TYPE LOCK_TYPE, L.ID1 LOCK_ID1, L.ID2 LOCK_ID2
FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S
WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID ;
9. 怎样查得数据库的SID ?
select name from v$database;
也可以直接查看 init.ora文件
10、管理回滚段:
存放事务的恢复信息
建立回滚段
create public rollback segment SEG_NAME tabelspace TABLESPACE_NAME;
alter rollback segment SEG_NAME online;
删除回滚段
首先改变为offline状态
直接使用回滚段
sql>set transaction use rollback segment SEG_NAME;
11. 计算一个表占用的空间的大小
select owner,table_name,
NUM_ROWS,
BLOCKS*AAA/1024/1024 "Size M",
EMPTY_BLOCKS,
LAST_ANALYZED
from dba_tables
where table_name='XXX';
Here: AAA is the value of db_block_size ;
XXX is the table name you want to check
12. 表在表空间中的存储情况
select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;
13. 索引在表空间中的存储情况
select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'
group by segment_name;
14.查看某表/索引的大小
表
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&table_name');
索引
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&index_name');
15、确定可用空间
select tablespace_name,sum(blocks),sum(bytes) from sys.dba_free_space group by tablespace_name;
16、程序中报错:maxinum cursor exceed!
<1> 查看当前的open cursor参数
sql> show parameter open_cursors
<2> 如果确实很小，应该调整数据库初始化文件
加如一项 open_cursors=200
<3> 如果很大，则
select sid,sql_text,count(*) from v$open_cursor
group by sid,sql_text
having count(*) > 200
其中200是随便写一个比较大的值。查询得到打开太多的cursor.
17、查看数据库的版本信息
SQL> select * from v$version;
包含版本信息，核心版本信息，位数信息(32位或64位)等
至于位数信息，在linux/unix平台上，可以通过file查看，如
file $ORACLE_HOME/bin/oracle
18. 查看最大会话数
SELECT * FROM V$PARAMETER WHERE NAME LIKE 'proc%';
SQL>
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
job_queue_processes integer 4
log_archive_max_processes integer 1
processes integer 200
这里为200个用户。
select * from v$license;
其中sessions_highwater纪录曾经到达的最大会话数
19. 以archivelog的方式运行oracle。
init.ora
log_archive_start = true
RESTART DATABASE
20. unix 下调整数据库的时间
su -root
date -u 08010000
21.P4电脑的安裝方法
将SYMCJIT.DLL改为SYSMCJIT.OLD
22. 如何查询SERVER是不是OPS?
SELECT * FROM V$OPTION;
如果PARALLEL SERVER=TRUE则有OPS能
23. 查询每个用户的权限
SELECT * FROM DBA_SYS_PRIVS;
24.将表/索引移动表空间
ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME;
ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME;
25.在LINUX,UNIX下启动DBA STUDIO?
OEMAPP DBASTUDIO
26.LINUX下查询磁盘竞争状况命令?
Sar -d
27.LINUX下查询磁盘CPU竞争状况命令?
sar -r
28. 查询表空间信息?
SELECT * FROM DBA_DATA_FILES;
29. 看各个表空间占用磁盘情况:
SQL> col tablespace format a20
SQL> select
b.file_id 文件ID号,
b.tablespace_name 表空间名,
b.bytes 字节数,
(b.bytes-sum(nvl(a.bytes,0))) 已使用,
sum(nvl(a.bytes,0)) 剩余空间,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id
30. 如把ORACLE设置为MTS或专用模式？
#dispatchers="(PROTOCOL=TCP) (SERVICE=SIDXDB)"
加上就是MTS，注释就是专用模式，SID是指你的实例名。
31. 如何才能得知系统当前的SCN号 ?
select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;
32. 修改oracel数据库的默认日期
alter session set nls_date_format='yyyymmddhh24miss';
OR
可以在init.ora中加上一行
nls_date_format='yyyymmddhh24miss'
33. 将小表放入keep池中
alter table xxx storage(buffer_pool keep);
34. 如何检查是否安装了某个patch?
check that oraInventory
35. 如何修改oracle数据库的用户连接数？
修改initSID.ora，将process加大，重启数据库.
36. 如何创建SPFILE?
SQL> connect / as sysdba
SQL> select * from v$version;
SQL> create pfile from spfile;
SQL> CREATE SPFILE FROM PFILE='E:\ora9i\admin\eygle\pfile\init.ora';
文件已创建。
SQL> CREATE SPFILE='E:\ora9i\database\SPFILEEYGLE.ORA' FROM PFILE='E:\ora9i\admin\eygle\pfile\init.ora';
文件已创建。
37. 內核参数的应用
shmmax
含义：这个设置并不决定究竟Oracle数据库或者操作系统使用多少物理内存，只决定了最多可以使用的内存数目。这个设置也不影响操作系统的内核资源。
设置方法：0.5*物理内存
例子：Set shmsys:shminfo_shmmax=10485760
shmmin
含义：共享内存的最小大小。
设置方法：一般都设置成为1。
例子：Set shmsys:shminfo_shmmin=1：
shmmni
含义：系统中共享内存段的最大个数。
例子：Set shmsys:shminfo_shmmni=100
shmseg
含义：每个用户进程可以使用的最多的共享内存段的数目。
例子：Set shmsys:shminfo_shmseg=20：
semmni
含义：系统中semaphore identifierer的最大个数。
设置方法：把这个变量的值设置为这个系统上的所有Oracle的实例的init.ora中的最大的那个processes的那个值加10。
例子：Set semsys:seminfo_semmni=100
semmns
含义：系统中emaphores的最大个数。
设置方法：这个值可以通过以下方式计算得到：各个Oracle实例的initSID.ora里边的processes的值的总和（除去最大的Processes参数）＋最大的那个Processes×2＋10×Oracle实例的个数。
例子：Set semsys:seminfo_semmns=200
semmsl:
含义：一个set中semaphore的最大个数。
设置方法：设置成为10＋所有Oracle实例的InitSID.ora中最大的Processes的值。
例子：Set semsys:seminfo_semmsl=-200
38. 怎样查看哪些用户拥有SYSDBA、SYSOPER权限？
SQL>conn sys/change_on_install
SQL>select * from V_$PWFILE_USERS;
39. 如何查看数据文件放置的路径 ?
col file_name format a50
SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;
40. 如何查看现有回滚段及其状态 ?
SQL> col segment format a30
SQL> SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS
41. Oracle常用系统文件有哪些？
通过以下视图显示这些文件信息：v$database,v$datafile,v$logfile v$controlfile v$parameter;
42.查看数据库实例
SQL>SELECT * FROM V$INSTANCE;
43. 怎样估算SQL执行的I/O数 ?
SQL>SET AUTOTRACE ON ;
SQL>SELECT * FROM TABLE;
OR
SQL>SELECT * FROM v$filestat ;
可以查看IO数
44. 怎样扩大REDO LOG的大小？
建立一个临时的redolog组，然后切换日志，删除以前的日志，建立新的日志。
45. 查询做比较大的排序的进程？
<1>
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks ;
<2>. 查询做比较大的排序的进程的SQL语句
select /*+ ORDERED */ sql_text from v$sqltext a
where a.hash_value = (
select sql_hash_value from v$session b
where b.sid = &sid and b.serial# = &serial)
order by piece asc ;
46. ORA-01555 SNAPSHOT TOO OLD的解决办法
增加MINEXTENTS的值，增加区的大小，设置一个高的OPTIMAL值。
如果是执行大的事务，报此错误，说明oracle给此事务随机分配的回滚段太小了,这时可以为它指定一个足够大的回滚段,以确保这个事务的成功执行.例如
set transaction use rollback segment roll_abc;
delete from table_name where ...
commit;
回滚段roll_abc被指定给这个delete事务,commit命令则在事务结束之后取消了回滚段的指定.
47. 事务要求的回滚段空间不够，表现为表空间用满（ORA-01560错误），回滚段扩展到达参数 MAXEXTENTS的值（ORA-01628）的解决办法.
向回滚段表空间添加文件或使已有的文件变大；增加MAXEXTENTS的值。
48. 监控事例的等待
select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4;
49. 回滚段的争用情况
select name, waits, gets, waits/gets "Ratio"
from v$rollstat C, v$rollname D
where C.usn = D.usn;
50 监控表空间的 I/O 比例
select B.tablespace_name name,B.file_name "file",A.phyrds pyr,
A.phyblkrd pbr,A.phywrts pyw, A.phyblkwrt pbw
from v$filestat A, dba_data_files B
where A.file# = B.file_id
order by B.tablespace_name;
51、监控文件系统的 I/O 比例
select substr(C.file#,1,2) "#", substr(C.name,1,30) "Name",
C.status, C.bytes, D.phyrds, D.phywrts
from v$datafile C, v$filestat D
where C.file# = D.file#;
52、监控 SGA 的命中率
select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;
53、监控 SGA 中字典缓冲区的命中率
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
54、监控 SGA 中共享缓存区的命中率，应该小于1%
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
55、显示所有数据库对象的类别和大小
select count(name) num_instances ,type ,sum(source_size) source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type order by 2;
56、监控 SGA 中重做日志缓存区的命中率，应该小于1%
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
57、监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
58、监控当前数据库谁在运行什么SQL语句？
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
59、监控字典缓冲区？
SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
后者除以前者,此比率小于1%,接近0%为好。
SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE
60、监控 MTS
select busy/(busy+idle) "shared servers busy" from v$dispatcher;
此值大于0.5时，参数需加大
select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers时，参数需加大
61、查看碎片程度高的表?
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);
62、如何知道使用CPU多的用户session?
11是cpu used by this session
select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by value desc;
63.如何检查操作系统是否存在IO的问题
使用的工具有sar,这是一个比较通用的工具。
#sar -u 2 10
即每隔2秒检察一次，共执行20次，当然这些都由你决定了。
示例返回：
HP-UX hpn2 B.11.00 U 9000/800 08/05/03
18:26:32 %usr %sys %wio %idle
18:26:34 80 9 12 0
18:26:36 78 11 11 0
18:26:38 78 9 13 1
18:26:40 81 10 9 1
18:26:42 75 10 14 0
18:26:44 76 8 15 0
18:26:46 80 9 10 1
18:26:48 78 11 11 0
18:26:50 79 10 10 0
18:26:52 81 10 9 0
Average 79 10 11 0
其中的%usr指的是用户进程使用的cpu资源的百分比，
%sys指的是系统资源使用cpu资源的百分比，
%wio指的是等待io完成的百分比，这是值得我们观注的一项，
%idle即空闲的百分比。如果wio列的值很大，如在35%以上，说明你的系统的IO存在瓶颈，你的CPU花费了很大的时间去等待IO的完成。
Idle很小说明系统CPU很忙。像我的这个示例，可以看到wio平均值为11说明io没什么特别的问题，而我的idle值为零，说明我的cpu已经满负荷运行了。
64.关注一下内存。
常用的工具便是vmstat，对于hp-unix来说可以用glance,Aix来说可以用topas,当你发现vmstat中pi列非零，memory中的free列的值很小，glance,topas中内存的利用率多于80%时，这时说明你的内存方面应该调节一下了，方法大体有以下几项。
a.划给Oracle使用的内存不要超过系统内存的1/2,一般保在系统内存的40%为益。
b.为系统增加内存
c.如果你的连接特别多，可以使用MTS的方式
d.打全补丁，防止内存漏洞。
65、查找前十条性能差的sql.
SELECT * FROM
(
SELECT PARSING_USER_ID
EXECUTIONS,
SORTS,
COMMAND_TYPE,
DISK_READS,
sql_text
FROM v$sqlarea
ORDER BY disk_reads DESC
)
WHERE ROWNUM<10 ;
66、查看占io较大的正在运行的session
SELECT se.sid,
se.serial#,
pr.SPID,
se.username,
se.status,
se.terminal,
se.program,
se.MODULE,
se.sql_address,
st.event,
st.p1text,
si.physical_reads,
si.block_changes
FROM v$session se,
v$session_wait st,
v$sess_io si,
v$process pr
WHERE st.sid=se.sid
AND st.sid=si.sid
AND se.PADDR=pr.ADDR
AND se.sid>6
AND st.wait_time=0
AND st.event NOT LIKE '%SQL%'
ORDER BY physical_reads DESC
对检索出的结果的几点说明：
<1> 我是按每个正在等待的session已经发生的物理读排的序，因为它与实际的IO相关。
<2> 你可以看一下这些等待的进程都在忙什么，语句是否合理？
Select sql_address from v$session where sid=
Select * from v$sqltext where address=
执行以上两个语句便可以得到这个session的语句。
你也以用alter system kill session 'sid,serial#';把这个session杀掉。
<3> 应观注一下event这列，这是我们调优的关键一列，下面对常出现的event做以简要的说明：
a、buffer busy waits，free buffer waits这两个参数所标识是dbwr是否够用的问题，与IO很大相关的，当v$session_wait中的free buffer wait的条目很小或没有的时侯，说明你的系统的dbwr进程决对够用，不用调整；free buffer wait的条目很多，你的系统感觉起来一定很慢，这时说明你的dbwr已经不够用了，它产生的wio已经成为你的数据库性能的瓶颈，这时的解决办法如下：
a.1增加写进程，同时要调整db_block_lru_latches参数
示例：修改或添加如下两个参数
db_writer_processes=4
db_block_lru_latches=8
a.2开异步IO，IBM这方面简单得多，hp则麻烦一些，可以与Hp工程师联系。
b、db file sequential read，指的是顺序读，即全表扫描，这也是我们应该尽量减少的部分，解决方法就是使用索引、sql调优，同时可以增大db_file_multiblock_read_count这个参数。
c、db file scattered read,这个参数指的是通过索引来读取，同样可以通过增加db_file_multiblock_read_count这个参数来提高性能。
d、latch free,与栓相关的了，需要专门调节。
e、其他参数可以不特别观注。
67. 文件说明
<1>监听器日志文件
以8I为例
$ORACLE_HOME/NETWORK/LOG/LISTENER.LOG
<2>. 监听器参数文件
以8I为例
$ORACLE_HOME/NETWORK/ADMIN/LISTENER.ORA
<3>. TNS 连接文件
以8I为例
$ORACLE_HOME/NETWORK/ADMIN/TNSNAMES.ORA
<4>. Sql*Net 环境文件
以8I为例
$ORACLE_HOME/NETWORK/ADMIN/SQLNET.ORA
<5>. 警告日志文件
以8I为例
$ORACLE_HOME/ADMIN/SID/BDUMP/SIDALRT.LOG
<6>. 基本结构
以8I为例
$ORACLE_HOME/RDBMS/ADMIN/STANDARD.SQL
<7>. 建立数据字典视图
以8I为例
$ORACLE_HOME/RDBMS/ADMIN/CATALOG.SQL
<8>.建立审计用数据字典视图
以8I为例
$ORACLE_HOME/RDBMS/ADMIN/CATAUDIT.SQL
<9>. 建立快照用数据字典视图
以8I为例
$ORACLE_HOME/RDBMS/ADMIN/CATSNAP.SQL
68、oracle 安全与审计
user_sys_privs,user_tab_privs;
配置文件
主要参数
session_per_user 每个用户可同时进行几个会话
cpu_per_session 每个用户可用多少个(cpu的)百分之一秒
cpu_per_call 语法分析、执行、取数可用多少个百分之一秒
connect_time 用户连接数据库的时间(分钟)
idle_time 用户不调用数据库的时间(分钟)
logical_reads_per_session 整个会话过程中用户访问oracle的块数
logical_reads_per_call 一次调用用户访问oracle的块数
private_SGA 一个用户进程所用SGA的内存数量
composite_limit 复合限制数
failed_login_attempts 连续多次注册失败引起一个帐户死锁
password_life_time 一个口令在其终止前可用的天数
password_reuse_time 一个口令在其n天才能重新使用
password_reuse_max 一个口令在重新使用之前必须改变多少次
password_lock_time 一个口令帐户被锁住的天数
69、管理初始化文件
show parameters
经常修改的项目有 v$parameter
shared_pool_size 分配给共享的字节数
rollback_segments 回滚段的个数
sessions 会话个数
processes 进程个数
70、管理控制文件
控制文件保存文件有关数据库创建时间、数据库名以及数据库运行时使用的所有文件位置和名称。
增加控制文件，在initorcl.ora中，找到control_file项,增加一项即可
删除控制文件，在initorcl.ora中去掉，然后删除物理文件
建立新的控制文件
create controlfile [reuse] [set] database 数据库名
logfile [group 整数] 文件名 [,[group 整数] 文件名],...
对于现有的数据库，可以间接地通过
alter database backup controlfile to trace命令生成控制文件，即可在\orant\rmb73\trace
下有ora00289.trc文件，其内容为文本
71、日志管理
<1>建立日志组
sql>select * from v$logfile;
sql>alter database add logfile group 3
('f:\orant\database\log1_g3.ora'
'f:\orant\database\log2_g3.ora') size 100k;
sql>select * from v$logfile;
----
sql> alter database add logfile group 4
('/disk3/log4a.rdo','/disk4/log4b.rdo') size 1m;
<2>删除日志组
alter database drop logfile group 1;
但是其物理文件并没有被删除掉
系统至少需要2个日志组，如果只有2个，就不能删除
不能删除正活动的日志组
<3>手工归档
通过alter system 的archive log 子句来实现
archive log [thread 整数]
[seq 整数][change 整数][current][group 整数]
[logfile '文件名'][next][all][start][to '位置']
<4> 强制日志切换
sql> alter system switch logfile;
<5> 强制checkpoints
sql> alter system checkpoint;
<6> adding online redo log members
sql>alter database add logfile member
'/disk3/log1b.rdo' to group 1,
'/disk4/log2b.rdo' to group 2;
<7>.changes the name of the online redo logfile
sql> alter database rename file 'c:/oracle/oradata/oradb/redo01.log'
to 'c:/oracle/oradata/redo01.log';
<8> drop online redo log members
sql> alter database drop logfile member 'c:/oracle/oradata/redo01.log';
<9>.clearing online redo log files
sql> alter database clear [unarchived] logfile 'c:/oracle/log2a.rdo';
<10>.using logminer analyzing redo logfiles
a. in the init.ora specify utl_file_dir = ' '
b. sql> execute dbms_logmnr_d.build('oradb.ora','c:\oracle\oradb\log');
c. sql> execute dbms_logmnr_add_logfile('c:\oracle\oradata\oradb\redo01.log',
sql> dbms_logmnr.new);
d. sql> execute dbms_logmnr.add_logfile('c:\oracle\oradata\oradb\redo02.log',
sql> dbms_logmnr.addfile);
e. sql> execute dbms_logmnr.start_logmnr(dictfilename=>'c:\oracle\oradb\log\oradb.ora');
f. sql> select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters
sql> v$logmnr_logs);
g. sql> execute dbms_logmnr.end_logmnr;
72 系统控制
alter system ...
alter system enable restricted session; 只允许具有restricted系统特权的用户登录
alter system flush shared_pool 清除共享池
alter system checkpoint 执行一 个检查点
alter system set license_max_session=64,license_session_warning=54 会话限制为64，会话的警界为54
alter system set license_max_session=0 会话数为无限制
alter system set license_max_users=300 用户限制为300个
alter system switch logfile 强制执行日志转换
73 会话控制
alter session
alter session set sql_trace=true 当前会话具有sql跟踪功能
alter session set NLS_language=French 出错信息设为法语
alter session set NLS_date_format='YYYY MM DD HH24:MI:SS';缺省日期格式
alter session set optimizier_goal=first_row改变优化方法为基于开销方法，具有快速响应速度
update student@teach set sold=sold+1 where sno='98010';
commit;
alter session close database link teach; 关闭远程链路
74、封锁机制
数据封锁:保护表数据，在多个用户并行存取数据时候，保证数据的完整性。
DML操作又在两个级别获取数据封锁：指定记录封锁和表封锁
表封锁可以有下列方式：行共享（RS），行排他（RX），共享封锁（S），共享行排他（SPX)和排他
封锁(X)
行共享表封锁(RS),允许其他事务并行查询、插入，修改和删除及再行封锁
select ...from 表名 ... for update of ...;
lock table 表名 in row share mode;
行排他表封锁(RX) 对该行有独占权利
insert into 表名 ...;
update 表名 ...;
delete from 表名 ...;
lock table 表名 in row exclusive mode;
允许并行查询、插入、删除或封锁其他行，但禁止其他事务使用下列命令进行并发封锁：
lock table 表名 in share mode;
lock table 表名 in share exclusive mode;
lock table 表名 in exclusive mode;
共享表封锁(S)
lock table 表名 in share mode;
允许其他事务可在该表上做查询和再实现共享表操作，但不能修改该表，同时也不能做如下封锁：
lock table 表名 in share row exclusive mode;
lock table 表名 in exclusive mode;
lock table 表名 in row exclusive mode;
共享排他表封锁(SRX)
lock table 表名 in share row exclusive mode;
排他表封锁(SRX)
lock table 表名 in exclusive mode;
75、设置事务
set transaction [read only][read write][use rollback segment 回滚段名]
76.如果希望用aimtzmcc用户连接数据库，访问aicbs用户的表，不在表名前缀"aicbs."，可以在建立数据库连接后发下面的命令
alter session set current_schema = aicbs;
77、表空间管理
<1> 创建表空间
sql> create tablespace tablespace_name datafile 'c:\oracle\oradata\file1.dbf' size 100m,
sql> 'c:\oracle\oradata\file2.dbf' size 100m minimum extent 550k [logging/nologging]
sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0)
sql> [online/offline] [permanent/temporary] [extent_management_clause]
<2>.locally managed tablespace
sql> create tablespace user_data datafile 'c:\oracle\oradata\user_data01.dbf'
sql> size 500m extent management local uniform size 10m;
<3>.temporary tablespace
sql> create temporary tablespace temp tempfile 'c:\oracle\oradata\temp01.dbf'
sql> size 500m extent management local uniform size 10m;
<4>.change the storage setting
sql> alter tablespace app_data minimum extent 2m;
sql> alter tablespace app_data default storage(initial 2m next 2m maxextents 999);
<5>.taking tablespace offline or online
sql> alter tablespace app_data offline;
sql> alter tablespace app_data online;
<6>.read_only tablespace
sql> alter tablespace app_data read only|write;
<7>.droping tablespace
sql> drop tablespace app_data including contents;
<8>.enableing automatic extension of data files
sql> alter tablespace app_data add datafile 'c:\oracle\oradata\app_data01.dbf' size 200m
sql> autoextend on next 10m maxsize 500m;
<9>.change the size fo data files manually
sql> alter database datafile 'c:\oracle\oradata\app_data.dbf' resize 200m;
<10>.Moving data files: alter tablespace
sql> alter tablespace app_data rename datafile 'c:\oracle\oradata\app_data.dbf'
sql> to 'c:\oracle\app_data.dbf';
<11>.moving data files:alter database
sql> alter database rename file 'c:\oracle\oradata\app_data.dbf'
sql> to 'c:\oracle\app_data.dbf';
78、BACKUP and RECOVERY
<1>. v$sga,v$instance,v$process,v$bgprocess,v$database,v$datafile,v$sgastat
<2>. Rman need set dbwr_IO_slaves or backup_tape_IO_slaves and large_pool_size
<3>. Monitoring Parallel Rollback
v$fast_start_servers , v$fast_start_transactions
<4>.perform a closed database backup (noarchivelog)
shutdown immediate
cp files /backup/
startup
<5>.restore to a different location
connect system/manager as sysdba
startup mount
alter database rename file '/disk1/../user.dbf' to '/disk2/../user.dbf';
alter database open;
<6>.recover syntax
--recover a mounted database
recover database;
recover datafile '/disk1/data/df2.dbf';
alter database recover database;
--recover an opened database
recover tablespace user_data;
recover datafile 2;
alter database recover datafile 2;
<7>.how to apply redo log files automatically
set autorecovery on
recover automatic datafile 4;
<8>.complete recovery:
--method 1(mounted databae)
copy c:\backup\user.dbf c:\oradata\user.dbf
startup mount
recover datafile 'c:\oradata\user.dbf;
alter database open;
--method 2(opened database,initially opened,not system or rollback datafile)
copy c:\backup\user.dbf c:\oradata\user.dbf (alter tablespace offline)
recover datafile 'c:\oradata\user.dbf' or
recover tablespace user_data;
alter database datafile 'c:\oradata\user.dbf' online or
alter tablespace user_data online;
--method 3(opened database,initially closed not system or rollback datafile)
startup mount
alter database datafile 'c:\oradata\user.dbf' offline;
alter database open
copy c:\backup\user.dbf d:\oradata\user.dbf
alter database rename file 'c:\oradata\user.dbf' to 'd:\oradata\user.dbf'
recover datafile 'e:\oradata\user.dbf' or recover tablespace user_data;
alter tablespace user_data online;
--method 4(loss of data file with no backup and have all archive log)
alter tablespace user_data offline immediate;
alter database create datafile 'd:\oradata\user.dbf' as 'c:\oradata\user.dbf''
recover tablespace user_data;
alter tablespace user_data online
<9>.perform an open database backup
alter tablespace user_data begin backup;
copy files /backup/
alter database datafile '/c:/../data.dbf' end backup;
alter system switch logfile;
<10>.backup a control file
alter database backup controlfile to 'control1.bkp';
alter database backup controlfile to trace;
<11>.recovery (noarchivelog mode)
shutdown abort
cp files
startup
<12>.recovery of file in backup mode
alter database datafile 2 end backup;
<13>.clearing redo log file
alter database clear unarchived logfile group 1;
alter database clear unarchived logfile group 1 unrecoverable datafile;
<14>.redo log recovery
alter database add logfile group 3 'c:\oradata\redo03.log' size 1000k;
alter database drop logfile group 1;
alter database open;
or >cp c:\oradata\redo02.log' c:\oradata\redo01.log
alter database clear logfile 'c:\oradata\log01.log';
79 managing password security and resources
<1>.controlling account lock and password
sql> alter user juncky identified by oracle account unlock;
<2>.user_provided password function
sql> function_name(userid in varchar2(30),password in varchar2(30),
old_password in varchar2(30)) return boolean
<3>.create a profile : password setting
sql> create profile grace_5 limit failed_login_attempts 3
sql> password_lock_time unlimited password_life_time 30
sql>password_reuse_time 30 password_verify_function verify_function
sql> password_grace_time 5;
<4>.altering a profile
sql> alter profile default failed_login_attempts 3
sql> password_life_time 60 password_grace_time 10;
<5>.drop a profile
sql> drop profile grace_5 [cascade];
<6>.create a profile : resource limit
sql> create profile developer_prof limit sessions_per_user 2
sql> cpu_per_session 10000 idle_time 60 connect_time 480;
<7>. view => resource_cost : alter resource cost
dba_Users,dba_profiles
<8>. enable resource limits
sql> alter system set resource_limit=true;
80.managing privileges
<1>.system privileges: view => system_privilege_map ,dba_sys_privs,session_privs
<2>.grant system privilege
sql> grant create session,create table to managers;
sql> grant create session to scott with admin option;
with admin option can grant or revoke privilege from any user or role;
<3>.sysdba and sysoper privileges:
sysoper: startup,shutdown,alter database open|mount,alter database backup controlfile,
alter tablespace begin/end backup,recover database
alter database archivelog,restricted session
sysdba: sysoper privileges with admin option,create database,recover database until
<4>.password file members: view:=> v$pwfile_users
<5>.O7_dictionary_accessibility =true restriction access to view or tables in other schema
<6>.revoke system privilege
sql> revoke create table from karen;
sql> revoke create session from scott;
<7>.grant object privilege
sql> grant execute on dbms_pipe to public;
sql> grant update(first_name,salary) on employee to karen with grant option;
<8>.display object privilege : view => dba_tab_privs, dba_col_privs
<9>.revoke object privilege
sql> revoke execute on dbms_pipe from scott [cascade constraints];
<10>.audit record view :=> sys.aud$
<11>. protecting the audit trail
sql> audit delete on sys.aud$ by access;
<12>.statement auditing
sql> audit user;
<13>.privilege auditing
sql> audit select any table by summit by access;
<14>.schema object auditing
sql> audit lock on summit.employee by access whenever successful;
<15>.view audit option : view=> all_def_audit_opts,dba_stmt_audit_opts,dba_priv_audit_opts,dba_obj_audit_opts
<16>.view audit result: view=> dba_audit_trail,dba_audit_exists,dba_audit_object,dba_audit_session,dba_audit_statement
81 manager role
<1>.create roles
sql> create role sales_clerk;
sql> create role hr_clerk identified by bonus;
sql> create role hr_manager identified externally;
<2>.modify role
sql> alter role sales_clerk identified by commission;
sql> alter role hr_clerk identified externally;
sql> alter role hr_manager not identified;
<3>.assigning roles
sql> grant sales_clerk to scott;
sql> grant hr_clerk to hr_manager;
sql> grant hr_manager to scott with admin option;
<4>.establish default role
sql> alter user scott default role hr_clerk,sales_clerk;
sql> alter user scott default role all;
sql> alter user scott default role all except hr_clerk;
sql> alter user scott default role none;
<5>.enable and disable roles
sql> set role hr_clerk;
sql> set role sales_clerk identified by commission;
sql> set role all except sales_clerk;
sql> set role none;
<6>.remove role from user
sql> revoke sales_clerk from scott;
sql> revoke hr_manager from public;
<7>.remove role
sql> drop role hr_manager;
<8>.display role information
view: =>dba_roles,dba_role_privs,role_role_privs,dba_sys_privs,role_sys_privs,role_tab_privs,session_roles
81.查询当前正在执行的job的情况
有时候对于需要执行的job查询执行情况，比如正在执行那条语句，或者想把job停下来等。一般不知道怎么查询到
job执行的session的sid.
方法一：
select * from dba_jobs_running
如果运行比较慢，加
select /*+ rule */* from dba_jobs_running
方法二：
<1>首先得到job号,从user_jobs或者dba_jobs
select * from user_jobs where upper(what) like '%2004PRESENT%'
<2> 根据job号查询sid号
select * from v$lock where id2 = 3361910 and type ='JQ'
就可以查询到sid了
比如查询当前的执行什么语句
select sql_text from v$sqlarea a,v$lock b,v$session c,user_jobs d
where d.upper(what) like '%2004PRESENT%'
and d.job = b.id2
and b.type='JQ'
and b.sid = c.sid
and a.hash_value = c.sql_hash_value
and a.address = c.sql_address
82.怎么样给sqlplus安装帮助
[A]SQLPLUS的帮助必须手工安装，shell脚本为$ORACLE_HOME/bin/helpins
在安装之前，必须先设置SYSTEM_PASS环境变量，如：
$ setenv SYSTEM_PASS SYSTEM/MANAGER
$ helpins
如果不设置该环境变量，将在运行脚本的时候提示输入环境变量
当然，除了shell脚本，还可以利用sql脚本安装，那就不用设置环境变量了，但是，我们必须以system登录。
$ sqlplus system/manager
SQL> @?/sqlplus/admin/help/helpbld.sql helpus.sql
安装之后，你就可以象如下的方法使用帮助了
SQL> help index
83.如何移动数据文件
<1>、关闭数据库，利用os拷贝
a.shutdown immediate关闭数据库
b.在os下拷贝数据文件到新的地点
c.Startup mount 启动到mount下
d.Alter database rename datafile '老文件' to '新文件';
e.Alter database open; 打开数据库
<2>、利用Rman联机操作
RMAN> sql "alter database datafile ''file name'' offline";
RMAN> run {
2> copy datafile 'old file location'
3> to 'new file location';
4> switch datafile ' old file location'
5> to datafilecopy ' new file location';
6> }
RMAN> sql "alter database datafile ''file name'' online";
说明：利用OS拷贝也可以联机操作，不关闭数据库，与rman的步骤一样，利用rman与利用os拷贝的原理一样，在rman中copy是拷贝数据文件，相当于OS的cp，而switch则相当于alter database rename，用来更新控制文件。
84.如何管理联机日志组与成员
以下是常见操作，如果在OPA/RAC下注意线程号
增加一个日志文件组
Alter database add logfile [group n] '文件全名' size 10M;
在这个组上增加一个成员
Alter database add logfile member '文件全名' to group n;
在这个组上删除一个日志成员
Alter database drop logfile member '文件全名';
删除整个日志组
Alter database drop logfile group n;
85.怎么样计算REDO BLOCK的大小
[A]计算方法为(redo size + redo wastage) / redo blocks written + 16
具体见如下例子
SQL> select name ,value from v$sysstat where name like '%redo%';
NAME VALUE
---------------------------------------------------------------- ----------
redo synch writes 2
redo synch time 0
redo entries 76
redo size 19412
redo buffer allocation retries 0
redo wastage 5884
redo writer latching time 0
redo writes 22
redo blocks written 51
redo write time 0
redo log space requests 0
redo log space wait time 0
redo log switch interrupts 0
redo ordering marks 0
SQL> select (19412+5884)/51 + 16 '"Redo black(byte)" from dual;
Redo black(byte)
------------------
512
86.如果发现表中有坏块，如何检索其它未坏的数据
[A]首先需要找到坏块的ID（可以运行dbverify实现），假设为，假定文件编码为。运行下面的查询查找段名：
SELECT segment_name,segment_type,extent_id,block_id, blocks
from dba_extents t
where
file_id =
AND between block_id and (block_id + blocks - 1)
一旦找到坏段名称，若段是一个表，则最好建立一个临时表，存放好的数据。若段是索引，则删除它，再重建。
create table good_table
as
select from bad_table where rowid not in
(select rowid
from bad_table where substr(rowid,10,6) = )
在这里要注意8以前的受限ROWID与现在ROWID的差别。
还可以使用诊断事件10231
SQL> ALTER SYSTEM SET EVENTS '10231 trace name context forever,level 10';
创建一个临时表good_table的表中除坏块的数据都检索出来
SQL>CREATE TABLE good_table as select * from bad_table;
最后关闭诊断事件
SQL> ALTER SYSTEM SET EVENTS '10231 trace name context off ';
关于ROWID的结构，还可以参考dbms_rowid.rowid_create函数
87.怎么样备份控制文件
在线备份为一个二进制的文件
alter database backup controlfile to '$BACKUP_DEPT/controlfile.000' [reuse];
备份为文本文件方式
alter database backup controlfile to trace [resetlogs|noresetlogs];
88.控制文件损坏如何恢复
<1>、如果是损坏单个控制文件
只需要关闭数据库，拷贝一个好的数据文件覆盖掉坏的数据文件即可
或者是修改init.ora文件的相关部分
<2>、如果是损失全部控制文件，则需要创建控制文件或从备份恢复
创建控制文件的脚本可以通过alter database backup controlfile to trace获取。
89.怎么样热备份一个表空间
<1>Alter tablespace 名称 begin backup;
host cp 这个表空间的数据文件 目的地;
Alter tablespace 名称 end backup;
如果是备份多个表空间或整个数据库，只需要一个一个表空间的操作下来就可以了。
90.怎么快速得到整个数据库的热备脚本
<1>可以写一段类似的脚本
SQL>set serveroutput on
begin
dbms_output.enable(10000);
for bk_ts in (select distinct t.ts#,t.name from v$tablespace t,v$datafile d where t.ts#=d.ts#) loop
dbms_output.put_line('--'||bk_ts.name);
dbms_output.put_line('alter tablespace '||bk_ts.name||' begin backup;');
for bk_file in (select file#,name from v$datafile where ts#=bk_ts.ts#) loop
dbms_output.put_line('host cp '||bk_file.name||' $BACKUP_DEPT/');
end loop;
dbms_output.put_line('alter tablespace '||bk_ts.name||' end backup;');
end loop;
end;
/
91.丢失一个数据文件，但是没有备份，怎么样打开数据库
如果没有备份只能是删除这个数据文件了，会导致相应的数据丢失。
SQL>startup mount
--ARCHIVELOG模式命令
SQL>Alter database datafile 'file name' offline;
--NOARCHIVELOG模式命令
SQL>Alter database datafile 'file name' offline drop;
SQLl>Alter database open;
注意：该数据文件不能是系统数据文件
92.丢失一个数据文件，没有备份但是有该数据文件创建以来的归档怎么恢复
保证如下条件
a. 不能是系统数据文件
b. 不能丢失控制文件
如果满足以上条件，则
SQL>startup mount
SQL>Alter database create datafile 'file name' as 'file name' size ... reuse;
SQL>recover datafile n; -文件号
或者
SQL>recover datafile 'file name';
或者
SQL>recover database;
SQL>Alter database open;
93.联机日志损坏如何恢复
<1>、如果是非当前日志而且归档，可以使用
Alter database clear logfile group n来创建一个新的日志文件
如果该日志还没有归档，则需要用
Alter database clear unarchived logfile group n
<2>、如果是当前日志损坏，一般不能clear，则可能意味着丢失数据
如果有备份，可以采用备份进行不完全恢复
如果没有备份，可能只能用_allow_resetlogs_corruption=true来进行强制恢复了，但是，这样的方法是不建议的，最好在有Oracle support的指导下进行。
94.怎么样创建RMAN恢复目录
首先，创建一个数据库用户，一般都是RMAN，并给予recovery_catalog_owner角色权限
sqlplus sys
SQL> create user rman identified by rman;
SQL> alter user rman default tablespace tools temporary tablespace temp;
SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;
然后，用这个用户登录，创建恢复目录
rman catalog rman/rman
RMAN> create catalog tablespace tools;
RMAN> exit;
最后，你可以在恢复目录注册目标数据库了
rman catalog rman/rman target backdba/backdba
RMAN> register database;
95.怎么样在恢复的时候移动数据文件，恢复到别的地点
给一个RMAN的例子
run {
set until time 'Jul 01 1999 00:05:00';
allocate channel d1 type disk;
set newname for datafile '/u04/oracle/prod/sys1prod.dbf'
to '/u02/oracle/prod/sys1prod.dbf';
set newname for datafile '/u04/oracle/prod/usr1prod.dbf'
to '/u02/oracle/prod/usr1prod.dbf';
set newname for datafile '/u04/oracle/prod/tmp1prod.dbf'
to '/u02/oracle/prod/tmp1prod.dbf';
restore controlfile to '/u02/oracle/prod/ctl1prod.ora';
replicate controlfile from '/u02/oracle/prod/ctl1prod.ora';
restore database;
sql "alter database mount";
switch datafile all;
recover database;
sql "alter database open resetlogs";
release channel d1;
}
96.怎么从备份片（backuppiece）中恢复(restore)控制文件与数据文件
可以使用如下方法，在RMAN中恢复备份片的控制文件
restore controlfile from backuppiecefile;
如果是9i的自动备份，可以采用如下的方法
restore controlfile from autobackup;
但是，如果控制文件全部丢失，需要指定DBID，如SET DBID=?
自动备份控制文件的默认格式是%F，这个格式的形式为
c-IIIIIIIIII-YYYYMMDD-QQ，其中IIIIIIIIII就是DBID
至于恢复(restore)数据文件，oracle 816开始有个包dbms_backup_restore
在 nomount 状态下就可以执行，可以读 815甚至之前的备份片，读出来的文件用于恢复
可以在SQLPLUS中运行，如下
SQL>startup nomount
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype := dbms_backup_restore.deviceallocate('', params=>'');
6 dbms_backup_restore.restoresetdatafile;
7 dbms_backup_restore.restorecontrolfileto('E:\Oracle\oradata\penny\control01.ctl');
8 dbms_backup_restore.restoreDataFileto(1,'E:\Oracle\oradata\penny\system01.dbf');
9 dbms_backup_restore.restoreDataFileto(2,'E:\Oracle\oradata\penny\UNDOTBS01.DBF');
10 dbms_backup_restore.restoreDataFileto(3,'E:\ORACLE\ORADATA\PENNY\USERS01.DBF');
11 dbms_backup_restore.restorebackuppiece('D:\orabak\BACKUP_1_4_04F4IAJT.PENNY',done=>done);
12 END;
13 /
PL/SQL 过程已成功完成。
SQL> alter database mount;
[Q]Rman的format格式中的%s类似的东西代表什么意义
[A]可以参考如下
%c 备份片的拷贝数
%d 数据库名称
%D 位于该月中的第几天 (DD)
%M 位于该年中的第几月 (MM)
%F 一个基于DBID唯一的名称,这个格式的形式为c-IIIIIIIIII-YYYYMMDD-QQ,其中IIIIIIIIII为该数据库的DBID，YYYYMMDD为日期，QQ是一个1-256的序列
%n 数据库名称，向右填补到最大八个字符
%u 一个八个字符的名称代表备份集与创建时间
%p 该备份集中的备份片号，从1开始到创建的文件数
%U 一个唯一的文件名，代表%u_%p_%c
%s 备份集的号
%t 备份集时间戳
%T 年月日格式(YYYYMMDD)
97.执行exec dbms_logmnr_d.build('Logminer.ora','file directory')，提示下标超界，怎么办
完整错误信息如下,
SQL> exec dbms_logmnr_d.build('Logminer.ora','file directory')
BEGIN dbms_logmnr_d.build('Logminer.ora','file directory'); END;
*
ERROR 位于第 1 行:
ORA-06532: 下标超出限制
ORA-06512: 在"SYS.DBMS_LOGMNR_D", line 793
ORA-06512: 在line 1
解决办法为：
<1>.编辑位于"$ORACLE_HOME/rdbms/admin"目录下的文件"dbmslmd.sql"
改变行:
TYPE col_desc_array IS VARRAY(513) OF col_description;
为
TYPE col_desc_array IS VARRAY(700) OF col_description;
并保存文件
<2>. 运行改变后的脚本
SQLPLUS> Connect internal
SQLPLUS> @$ORACLE_HOME/rdbms/admin/dbmslmd.sql
<3>.重新编译该包
SQLPLUS> alter package DBMS_LOGMNR_D compile body;
98.执行execute dbms_logmnr.start_logmnr(DictFileName=>'DictFileName')提示ORA-01843:无效的月份，这个是什么原因
我们分析start_logmnr包
PROCEDURE start_logmnr(
startScn IN NUMBER default 0 ,
endScn IN NUMBER default 0,
startTime IN DATE default TO_DATE('01-jan-1988','DD-MON-YYYY'),
endTime IN DATE default TO_DATE('01-jan-2988','DD-MON-YYYY'),
DictFileName IN VARCHAR2 default '',
Options IN BINARY_INTEGER default 0 );
可以知道，如果TO_DATE('01-jan-1988','DD-MON-YYYY')失败，将导致以上错误
所以解决办法可以为
<1>、Alter session set NLS_LANGUAGE=American
<2>、用类似如下的方法执行
execute dbms_logmnr.start_logmnr (DictFileName=> 'f:\temp2\TESTDICT.ora', starttime => TO_DATE(
'01-01-1988','DD-MM-YYYY'), endTime=>TO_DATE('01-01-2988','DD-MM-YYYY'));
99.unix
setenv ORACLE_SID kfcs
sqlplus "/as sysdba"
--========================================================================================================
--========================================== 五.oracle工具使用方面 ======================================
--========================================================================================================
1、oracle loader
控制文件的格式
load data
infile '数据文件名'
into table 表名
(first_name position(01:14) char,
surname position(15:28) char,
clssn position(29:36) char,
hire_data position(37:42) date 'YYMMDD')
2、sql*plus的copy命令
格式: copy from