窗体顶端
oracle 常用命令
1.增加主键
alter table TABLE_NAME add constraint KEY_NAME primary key(TABLE_COLUMN);
2.增加外键
alter table TABLE_NAME addconstraint FK_NAME foreign key (TABLE_COLUMN) references KEY_TABLE_NAME;
3.使主键或外键失效、生效
alter table TABLE_NAME disable(enable) constraint KEY_NAME;
4、查看各种约束
selectconstraint_name,table_name,constraint_type,status from user_constraints;
5、删除主键或外键
alter table TABLE_NAME drop constraintKEY_NAME;
6、在initorcl.ora中加入rollback_segments=(rb0,rb1,...)
其中rb0,rb1为自己定义的回滚段，可使这些回滚段在启动时生效
7、svgmgr>show sga 显示全局区
8、copy命令
格式: copy from <db> to <db><opt> <table>
{(<cols>)} using <sel>
其中:<db>:database string ；e.g:scott/tiger@oracle
9、客户端注册表修改
HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE
NLS_LANG_BAK ----语言字符集
简体中文:SIMPLIFIED CHINESE_CHINA.ZHS16CGB231280
美国英文:AMERICA.WE8ISO8859P1
10、在win95/win98中在注册表中:
HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE
填加一个字符串键值
local="oracle" （可根据情况而定,指数据库别名)
即可以在sql*plus中不用输入连接串
或在autoexec.bat 中添加"set local=alias_name"
11、在sqlplus快捷方式中，在属性窗口中，在目标中，在"d:\orawin95\bin\plus33w.exe"
后面加空格和"scott/tiger@oracle"即可快速进入
12、修改数据库的字符集
在表props$中
update props$ set value$='ZHS16CGB231280'
13、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 一个口令帐户被锁住的天数
14、管理初始化文件
show parameters
经常修改的项目有 v$parameter
shared_pool_size 分配给共享的字节数
rollback_segments 回滚段的个数
sessions 会话个数
processes 进程个数
15、管理控制文件
控制文件保存文件有关数据库创建时间、数据库名以及数据库运行时使用的所有文件位置和名称。
增加控制文件，在initorcl.ora中，找到control_file项,增加一项即可
删除控制文件，在initorcl.ora中去掉，然后删除物理文件
建立新的控制文件
create controlfile [reuse] [set] database 数据库名
logfile [group 整数] 文件名 [,[group 整数] 文件名],...
对于现有的数据库，可以间接地通过
alter database backup controlfile to trace命令生成控制文件，即可在\orant\rmb7\trace
下有ora00289.trc文件，其内容为文本
16、管理回滚段:
存放事务的恢复信息
建立回滚段
create public rollback segmentSEG_NAME tabelspace TABLESPACE_NAME;
alter rollback segmentSEG_NAME online;
删除回滚段
首先改变为offline状态
直接使用回滚段
sql>set transaction use rollback segment SEG_NAME;
17、管理日志
建立日志组
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;
删除日志组
alter database drop logfile group 1;
但是其物理文件并没有被删除掉
系统至少需要2个日志组，如果只有2个，就不能删除
不能删除正活动的日志组
手工归档
通过alter system 的archive log 子句来实现
archive log[thread 整数]
[seq 整数][change 整数][current][group 整数]
[logfile '文件名'][next][all][start][to '位置']
18 系统控制
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 强制执行日志转换
19 会话控制
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; 关闭远程链路
20、封锁机制
数据封锁:保护表数据，在多个用户并行存取数据时候，保证数据的完整性。
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 shareexclusive mode;
lock table 表名 in exclusivemode;
共享表封锁(S)
lock table 表名 in share mode;
允许其他事务可在该表上做查询和再实现共享表操作，但不能修改该表，同时也不能做如下封锁：
lock table 表名 inshare row exclusive mode;
lock table 表名in exclusive mode;
lock table 表名 inrow exclusive mode;
共享排他表封锁(SRX)
lock table 表名 in sharerow exclusive mode;
排他表封锁(SRX)
lock table 表名 in exclusive mode;
21、通常oracle需要启动OracleServiceORCL,OracleStartORCL,OracleTNSListener任务
在NT上至少要启动两个服务
oraclestartID和oracleserverID
22、每个数据库都有一个系统标识符(SID)，典型安装的数据库使用的系统标识符是ORCL
23、删除带约束的表
Drop table 表名 cascade constraints;
24、设置事务
set transaction [read only][read write][use rollback segment 回滚段名]
25、建外键
单字段时：create table 表名 (col1 char(8),
cno char(4) REFERENCE course);
多个字段时,在最后加上Foreign Key (字段名) REFERENCE 表名(字段)
连带删除选项 (on delete cascade
当指定时，如果父表中的记录被删除，则依赖于父表的记录也被删除
REFERENCE 表名（） on delete cascade;
26、启动关闭数据库
关闭：
svrmgr>connect internal/oracle
>shutdown --正常关闭数据库
svrmgr>shutdown immediate --立即关闭数据库
svrmgr>shutdown abort --一种最直接的关闭数据库的方式，执行之后，重新启动需要花6-8小时
启动：
$>svrmgrl
svrmgr>startup --正常启动
--等价于:startupnomount;
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 --带初始化参数文件的启动
27、缺省用户和密码
<1>. Oracle安裝完成后的初始口令?
internal/oracle
sys/change_on_install
system/manager
scott/tiger
sysman/oem_temp
<2>. ORACLE9IAS WEB CACHE的初始默认用户和密码？
administrator/administrator
28、对公共授予访问权
grant select on 表名 to public;
create public synonym 同义词名 for表名;
29、填加注释
comment on table 表名 is '注释';
comment on column 表名.列名 is '注释';
30 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')
31、程序中报错:maxinum cursor exceed!
应该调整数据库初始化文件
加如一项 open_cursors=200
32、生成用户时指定缺省表空间
create user 用户名 identified by 口令 default
tablespace 表空间名;
33、重新指定用户的缺省表空间
alter user 用户名 default tablespace 表空间名
34、约束条件
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 外部键约束
35、分布式数据库
create [public] database link LINKNAME
[connect to USERNAME identified byPASSWORD]
[using 'CONNECT_STRING']
可以在服务器端，也可以在客户端建立，但必须注意，两台服务器之间
数据库必须可以互访，必须各有各自的别名数据库
36、alter user语句的quota子句限制用户的磁盘空间
如:alter user jf quota 10M on system;
37 create table a
storage(
initial 1M /*第一次创建时分配空间*/
next 1M /*第一次分配的存储空间用完时在分配*/
)
as select * from b;
38、确定可用空间
select tablespace_name,sum(blocks),sum(bytes) fromsys.dba_free_space group by tablespace_name;
39、创建序列
select * from user_sequences;
create sequence SEQ_NAME start with 1000
maxvalue 1000 incrementby 1;
alter sequence SEQ_NAME minvalue 50 maxvalue 100;
40、删除重复行
update a set aa=null where aa is not null;
delete from a where rowid!=
(select max(rowid) from a bwhere a.aa=b.aa);
41、删除同其他表相同的行
delete from a where exits
(select 'X' from b where b.no=a.no);
或
delete from a where no in (select no fromb);
42、查看数据库链路
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;
43、快照
create snapshot SNAPSHOT_NAME
[storage (storage parameter)]
[tablespace TABLESPACE_NAME]
[refresh [fast\complete\force]
[start with START_DATE nextNEXT_DATE]
as QUERY;
create snapshot snapshot_to_study as select * from TABLE_NAME@to_study;
创建角色
create role aa identified by aaa;
授权 grant create snapshot,altersnapshot to aaa;
grant aaa to emp;
create snapshot SNAPSHOT_TO_HTML refresh complete start withsysdate 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;
44、包
包说明(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) returnnumber 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;
45、调度程序 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('Jobnumer='||to_char(job_num));
end;
移走任务
begin
dbms_job.remove(1);
end;
中止任务
begin
dbms_job.broken(1,true);
end;
46、动态sql
create or replace procedure my_execute(sql_string invarchar2) 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 testvalue'||'('||'''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 * fromokcai_'||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) = 0then
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;
46.1 用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, enamefrom 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 wherelast_name=''okcai'' RETURNING INTO :1'
returning into v_sql;
commit;
end;
47、用ref cursor来完成动态游标的功能
declare
type ct is ref cursor;
cc ct;
v_notify acc_woff_notify%rowtype;
begin
open cc for 'select * fromacc_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;
48、重新编译
sql>exec dbms_utility.compile_schema(schema);
如：
sql>exec dbms_utility.compile_schema(scott);
49、去除数据库中特殊字符
<1>.字符串字段中含有"'",如果用来组合sql语句，会造成语句不准确。
比如：replace(f1,'''','')
<2>.字符串字段中含有"\t \n",如果用来在c或者c＋＋程序中输出到文件，格式无法保证。
比如：replace(f2,'\t','')
<3>.清除换行和回车
比如: replace(f2,chr(13)||chr(10),'')
50.如果希望用aimtzmcc用户连接数据库，访问aicbs用户的表，不在表名前缀"aicbs."，可以在建立数据库连接后发下面的命令
alter session setcurrent_schema = aicbs;
51.查询锁的原因
如果进程被死锁，可以按下面方式查询
<1> 从v$session或者v$locked_object找到此session
<2> 如果有lockwait，查询v$lock,
select * fromv$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
52.oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。
<1>下面的语句可以进行总计
select region_code,count(*) fromaicbs.acc_woff_notify
group by rollup(region_code);
<2>下面的语句可以按照rollup不同的字段进行小计
selectregion_code,write_status,count(*) from aicbs.acc_woff_notify
group byregion_code,rollup(write_status);
select region_code,write_status,count(*)from aicbs.acc_woff_notify
group byrollup(region_code,write_status);
53. 查询正在执行语句的执行计划(也就是实际语句执行计划)
select * from v$sql_plan where hash_value = (selectsql_hash_value from v$session where sid = 1111);
其中id和parent_id表示了执行数的结构,数值最大的为最先执行
比如
ID PARENT_ID
-------------
0
1 0
2 1
3 2
4 3
5 4
6 3
------------则执行计划树为
0
1
2
3
6 4
5
54.alter table 语句详细说明
1 修改表的属性
<1> 物理属性：PCTFREE,PCTUSED,INITRANS,MAXTRANS和存储特征。
<2> logging
<3> 数据段压缩
2 修改字段
<1> 增加字段
<2> 修改字段
<3> 删除字段
<4> 字段改名
<5> modifycollection retrieval
<6> modify LOBstorage
<7> alter varraycol
3 修改约束
4 修改表的分区
5 alter external table
6 move table
7 enable disable
55. oracle中的裸设备指的是什么?
裸设备就是绕过文件系统直接访问的储存空间
56. oracle如何区分 64-bit/32bit 版本？？？
$ sqlplus '/ AS SYSDBA'
SQL*Plus: Release 9.0.1.0.0 -Production on Mon Jul 14 17:01:09 2003
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Connected t
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
With the Partitioning option
JServer Release 9.0.1.0.0 - Production
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
PL/SQL Release 9.0.1.0.0 - Production
CORE 9.0.1.0.0 Production
TNS for Solaris: Version 9.0.1.0.0 - Production
NLSRTL Version 9.0.1.0.0 - Production
SQL>
57. 怎样计算一个表占用的空间的大小？
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 thevalue of db_block_size ;
XXX is the table name you want to check
58. 如何查看最大会话数？
SELECT * FROM V$PARAMETER WHERE NAMELIKE '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纪录曾经到达的最大会话数
59. 如何以archivelog的方式运行oracle。
init.ora
log_archive_start = true
RESTART DATABASE
60. 数据表中的字段最大数是多少?
表或视图中的最大列数为 1000
61. 怎样查得数据库的SID ?
select name from v$database;
也可以直接查看 init.ora文件
63. 如何在Oracle服务器上通过SQLPLUS查看本机IP地址 ?
select sys_context('userenv','ip_address') from dual;
如果是登陆本机数据库，只能返回127.0.0.1
64. unix 下怎么调整数据库的时间？
su -root
date -u 08010000
65. 在ORACLE TABLE中如何抓取MEMO類型欄位為空的資料記錄?
select remark from oms_flowrec where trim(' ' from remark) is not null ;
66. P4電腦安裝方法
將SYMCJIT.DLL改為SYSMCJIT.OLD
67. 如何查詢SERVER是不是OPS?
SELECT * FROM V$OPTION;
如果PARALLEL SERVER=TRUE則有OPS能
68. 何查詢每個用戶的權限?
SELECT * FROM DBA_SYS_PRIVS;
69. 如何將表移動表空間?
ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME;
70. 如何將索引移動表空間?
ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME;
71. 在LINUX,UNIX下如何啟動DBA STUDIO?
OEMAPP DBASTUDIO
72. 查詢鎖的狀況的對象有?
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.ID1LOCK_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 ;
73. SQLPLUS下如何修改編輯器?
DEFINE _EDITOR="<编辑器的完整路经>" -- 必须加上双引号
来定义新的编辑器，也可以把这个写在$ORACLE_HOME/sqlplus/admin/glogin.sql里面使它永久有效。
74. ORACLE產生隨機函數是?
DBMS_RANDOM.RANDOM
75. LINUX下查詢磁盤競爭狀況命令?
Sar -d
76. LINUX下查詢CPU競爭狀況命令?
sar -r
77. 查看數據庫字符狀況?
SELECT * FROM NLS_DATABASE_PARAMETERS;
SELECT * FROM V$NLS_PARAMETERS;
78. 查詢表空間信息?
SELECT * FROM DBA_DATA_FILES;
79. 如何查看各个表空间占用磁盘情况？
SQL> coltablespace 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
80. 如把ORACLE设置为MTS或专用模式？
#dispatchers="(PROTOCOL=TCP) (SERVICE=SIDXDB)"
加上就是MTS，注释就是专用模式，SID是指你的实例名。
81. 如何才能得知系统当前的SCN号 ?
select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;
82. 请问如何在ORACLE中取毫秒?
9i之前不支持,9i开始有timestamp.
9i可以用select systimestamp from dual;
83. 如何在字符串里加回车？
select 'Welcome to visit'||chr(10)||'www.CSDN.NET' from dual ;
84. 中文是如何排序的？
Oracle9i之前，中文是按照二进制编码进行排序的。
在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值
SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序
SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序
SCHINESE_PINYIN_M 按照拼音排序
sql>show parameternls_sort
85. 怎样修改oracel数据库的默认日期?
alter session set nls_date_format='yyyymmddhh24miss';
OR
可以在init.ora中加上一行
nls_date_format='yyyymmddhh24miss'
86. 如何将小表放入keep池中?
alter table xxx storage(buffer_pool keep);
87. 如何检查是否安装了某个patch?
check that oraInventory
88. 如何知道数据库中某个表所在的tablespace?
select tablespace_name from user_tables where table_name='TEST';
select * from user_tables中有个字段TABLESPACE_NAME，（oracle）;
select * from dba_segments where …;
89. 如何修改oracle数据库的用户连接数？
修改initSID.ora，将process加大，重启数据库.
90. 怎樣把“&”放入一條記錄中？
insert into a values (translate ('at{&}t','at{}','at'));
91. EXP 如何加ＱＵＥＲＹ参数？
EXP USER/PASS FILE=A.DMP TABLES(BSEMPMS)
QUERY='"WHERE EMP_NO=\'S09394\'\";
IMP 如何到处DDL语句
imp aicbs/aicbs@busi_cs file=okcai.dmp rows=nindexfile=k.sql
92. 如何創建SPFILE?
SQL> connect / assysdba
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' FROMPFILE='E:\ora9i\admin\eygle\pfile\init.ora';
文件已创建。
93. 內核參數的應用?
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
94. 怎样查看哪些用户拥有SYSDBA、SYSOPER权限？
SQL>connsys/change_on_install
SQL>select * from V_$PWFILE_USERS;
96. 如何对CLOB字段进行全文检索？
SELECT * FROM A WHERE dbms_lob.instr(a.a,'K',1,1)>0;
97. 如何查看数据文件放置的路径 ?
col file_name format a50
SQL> select tablespace_name,file_id,bytes/1024/1024,file_name fromdba_data_files order by file_id;
98. 如何查看现有回滚段及其状态 ?
SQL> col segment format a30
SQL> SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUSFROM DBA_ROLLBACK_SEGS
99. Oracle常用系统文件有哪些？
通过以下视图显示这些文件信息：v$database,v$datafile,v$logfilev$controlfile v$parameter;
100. 如何查有多少個數据庫實例?
SQL>SELECT * FROM V$INSTANCE;
101. 怎样估算SQL执行的I/O数 ?
SQL>SET AUTOTRACE ON ;
SQL>SELECT * FROM TABLE;
OR
SQL>SELECT * FROM v$filestat ;
可以查看IO数
102. 怎样用Sql语句实现查找一列中第N大值？
select * from
(select t.*,dense_rank() over (order by sal) rank from employee)
where rank = N;
103. 怎样扩大REDO LOG的大小？
建立一个临时的redolog组，然后切换日志，删除以前的日志，建立新的日志。
104. 如何配置Sequence?
建sequence seq_custid
create sequence seq_custid start 1 incrememt by 1;
建表时:
create table cust
{ cust_id smallint not null,
...}
insert 时:
insert into table cust
values( seq_cust.nextval, ...)
105 虚擬字段
<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 empWHERE ROWNUM < 10 ;
<3>. ROWID
返回行的物理地址
SELECT ROWID,ename FROM emp WHERE deptno = 20 ;
106. 将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;
107. 如何查询做比较大的排序的进程？
<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 ;
108. ORA-01555SNAPSHOT TOO OLD的解决办法
增加MINEXTENTS的值，增加区的大小，设置一个高的OPTIMAL值。
109. 事务要求的回滚段空间不够，表现为表空间用满（ORA-01560错误），回滚段扩展到达参数 MAXEXTENTS的值（ORA-01628）的解决办法.
向回滚段表空间添加文件或使已有的文件变大；增加MAXEXTENTS的值。
110. 如何加密ORACLE的存储过程？
下列存储过程内容放在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 2:26:48 2001
Copyright (c) Oracle Corporation 1993, 2000. AllRights Reserved.
Processing AA.sql to AA.plb
运行AA.plb
SQL> @AA.plb ;
111. 如何监控事例的等待？
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;
112. 如何回滚段的争用情况？
select name, waits, gets, waits/gets "Ratio"
from v$rollstat C, v$rollname D
where C.usn = D.usn;
113 如何监控表空间的 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;
114. 如何监控文件系统的 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#;
115. 如何在某个用户下找所有的索引？
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;
116. 如何监控 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;
117. 如何监控 SGA 中字典缓冲区的命中率？
select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100"miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hitratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;
118. 如何监控 SGA 中共享缓存区的命中率，应该小于1% ？
select sum(pins) "Total Pins", sum(reloads) "TotalReloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;
selectsum(pinhits-reloads)/sum(pins) "hitradio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;
119. 如何显示所有数据库对象的类别和大小？
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;
120. 监控 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');
121. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_size
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)','sorts (disk)');
122. 如何监控当前数据库谁在运行什么SQL语句？
SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;
123. 如何监控字典缓冲区？
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) "CACHEMISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;
后者除以前者,此比率小于1%,接近0%为好。
SELECTSUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GETMISSES"
FROM V$ROWCACHE
124. 监控 MTS
select busy/(busy+idle) "shared servers busy" fromv$dispatcher;
此值大于0.5时，参数需加大
select sum(wait)/sum(totalq) "dispatcher waits" fromv$queue where type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;
servers_highwater接近mts_max_servers时，参数需加大
125. 如何查看碎片程度高的表?
SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BYsegment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUPBY segment_name);
126. 如何知道表在表空间中的存储情况?
select segment_name,sum(bytes),count(*) ext_quan from dba_extentswhere
tablespace_name='&tablespace_name' and segment_type='TABLE'group by tablespace_name,segment_name;
127. 如何知道索引在表空间中的存储情况?
select segment_name,count(*) from dba_extents wheresegment_type='INDEX' and owner='&owner'
group by segment_name;
128、如何知道使用CPU多的用户session?
11是cpu used by this session
selecta.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 byvalue desc;
129.自治事务
在存储过程的is\as
后面声明PRAGMA AUTONOMOUS_TRANSACTION;
自治事务防止嵌套提交，使事务在自己的事务区内提交或回滚不会影响其他的事务。
130.如何检查操作系统是否存在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已经满负荷运行了。
131.关注一下内存。
常用的工具便是vmstat，对于hp-unix来说可以用glance,Aix来说可以用topas,当你发现vmstat中pi列非零，memory中的free列的值很小，glance,topas中内存的利用率多于80%时，这时说明你的内存方面应该调节一下了，方法大体有以下几项。
♀划给Oracle使用的内存不要超过系统内存的1/2,一般保在系统内存的40%为益。
♀为系统增加内存
♀如果你的连接特别多，可以使用MTS的方式
♀打全补丁，防止内存漏洞。
132.查找前十条性能差的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 ;
二、查看占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=<sid>;
Select * from v$sqltext where address=<sql_address>;
执行以上两个语句便可以得到这个session的语句。
你也以用alter system kill session 'sid,serial#';把这个session杀掉。
3、应观注一下event这列，这是我们调优的关键一列，下面对常出现的event做以简要的说明：
a、buffer busy waits，free bufferwaits这两个参数所标识是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、其他参数可以不特别观注。
133.实现返回第几行数据的办法
select * from ( select rownum row_id,b.* from (select a.* from aicbs.sys_opera) b )
where row_id between 15 and 20
129.
<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
130. SQL语句的优化方法
<1> /*+ALL_ROWS*/
表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.
例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHEREEMP_NO='CCBZZP';
<2>. /*+FIRST_ROWS*/
表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.
例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMSWHERE EMP_NO='CCBZZP';
<3>. /*+CHOOSE*/
表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;
表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;
例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHEREEMP_NO='CCBZZP';
<4>. /*+RULE*/
表明对语句块选择基于规则的优化方法.
例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHEREEMP_NO='CCBZZP';
<5>. /*+FULL(TABLE)*/
表明对表选择全局扫描的方法.
例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHEREEMP_NO='CCBZZP';
<6>. /*+ROWID(TABLE)*/
提示明确表明对指定表根据ROWID进行访问.
例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHEREROWID>='AAAAAAAAAAAAAA'
AND EMP_NO='CCBZZP';
<7>. /*+CLUSTER(TABLE)*/
提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.
例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROMBSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
<8>. /*+INDEX(TABLE INDEX_NAME)*/
表明对表选择索引的扫描方法.
例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE AREFEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';
<9>. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明对表选择索引升序的扫描方法.
例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMSWHERE DPT_NO='CCBZZP';
<10>. /*+INDEX_COMBINE*/
为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的
布尔组合方式.
例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROMBSEMPMS
WHERE SAL<5000000 AND HIREDATE<SYSDATE;
<11>. /*+INDEX_JOIN(TABLE INDEX_NAME)*/
提示明确命令优化器使用索引作为访问路径.
例如:
SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
FROM BSEMPMS WHERE SAL<60000;
<12>. /*+INDEX_DESC(TABLE INDEX_NAME)*/
表明对表选择索引降序的扫描方法.
例如:
SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMSWHERE DPT_NO='CCBZZP';
<13>. /*+INDEX_FFS(TABLE INDEX_NAME)*/
对指定的表执行快速全索引扫描,而不是全表扫描的办法.
例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHEREDPT_NO='TEC305';
<14>. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/
提示明确进行执行规划的选择,将几个单列索引的扫描合起来.
例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROMBSEMPMS WHERE EMP_NO='CCBZZP' AND DPT_NO='TDC306';
<15>. /*+USE_CONCAT*/
对查询中的WHERE后面的OR条件进行转换为UNIONALL的组合查询.
例如:
SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' ANDSEX='M';
<16>. /*+NO_EXPAND*/
对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.
例如:
SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506'AND SEX='M';
<17>. /*+NOWRITE*/
禁止对查询块的查询重写操作.
<18>. /*+REWRITE*/
可以将视图作为参数.
<19>. /*+MERGE(TABLE)*/
能够对视图的各个查询进行相应的合并.
例如:
SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A(SELET DPT_NO
,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHEREA.DPT_NO=V.DPT_NO
AND A.SAL>V.AVG_SAL;
<20>. /*+NO_MERGE(TABLE)*/
对于有可合并的视图不再合并.
例如:
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMSA (SELET DPT_NO
,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHEREA.DPT_NO=V.DPT_NO
AND A.SAL>V.AVG_SAL;
<21>. /*+ORDERED*/
根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接.
例如:
SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C
WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;
<22>. /*+USE_NL(TABLE)*/
将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.
例如:
SELECT /*+ORDERED USE_NL(BSEMPMS)*/BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHEREBSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
<23>. /*+USE_MERGE(TABLE)*/
将指定的表与其他行源通过合并排序连接方式连接起来.
例如:
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMSWHERE
BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
<24>. /*+USE_HASH(TABLE)*/
将指定的表与其他行源通过哈希连接方式连接起来.
例如:
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE
BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
<25>. /*+DRIVING_SITE(TABLE)*/
强制与ORACLE所选择的位置不同的表进行查询执行.
例如:
SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHEREBSEMPMS.DPT_NO=DEPT.DPT_NO;
<26>. /*+LEADING(TABLE)*/
将指定的表作为连接次序中的首表.
<27>. /*+CACHE(TABLE)*/
当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
<28>. /*+NOCACHE(TABLE)*/
当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
<29>. /*+APPEND*/
直接插入到表的最后,可以提高速度.
insert /*+append*/ into test1 select * from test4 ;
<30>. /*+NOAPPEND*/
通过在插入语句生存期内停止并行模式来启动常规插入.
insert/*+noappend*/ into test1 select * from test4 ;
56 数据字典利用
SQL>select * from dictionary whereinstr(comments,'index')>0;
如果我们想知道user_indexes表各字段名称的详细含义，可以用下面这条SQL语句:
SQL>select column_name,comments from dict_columns wheretable_name='USER_INDEXES';
依此类推，就可以轻松知道数据字典的详细名称和解释，不用查看ORACLE的其它文档资料了。
下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。
一、用户
查看当前用户的缺省表空间
SQL>select username,default_tablespace from user_users;
查看当前用户的角色
SQL>select * fromuser_role_privs;
查看当前用户的系统权限和表级权限
SQL>select * from user_sys_privs;
SQL>select * from user_tab_privs;
二、表
查看用户下所有的表
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 whereobject_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;
三、索引
查看索引个数和类别
SQL>select index_name,index_type,table_name from user_indexes order bytable_name;
查看索引被索引的字段
SQL>select * from user_ind_columns whereindex_name=upper('&index_name');
查看索引的大小
SQL>select sum(bytes)/(1024*1024) as "size(M)" from user_segments
where segment_name=upper('&index_name');
四、序列号
查看序列号，last_number是当前值
SQL>select * from user_sequences;
五、视图
查看视图的名称
SQL>select view_name from user_views;
查看创建视图的select语句
SQL>setview_name,text_length from user_views;
SQL>setlong2000; 说明：可以根据视图的text_length值设定setlong 的大小
SQL>select text from user_views where view_name=upper('&view_name');
六、同义词
查看同义词的名称
SQL>select * from user_synonyms;
七、约束条件
查看某表的约束条件
SQL>select constraint_name, constraint_type,search_condition,r_constraint_name
from user_constraints where table_name =upper('&table_name');
SQL>selectc.constraint_name,c.constraint_type,cc.column_name
fromuser_constraints c,user_cons_columns cc
wherec.owner = upper('&table_owner') and c.table_name = upper('&table_name')
and c.owner= cc.owner and c.constraint_name = cc.constraint_name
order bycc.position;
八、存储函数和过程
查看函数和过程的状态
SQL>select object_name,status from user_objects whereobject_type='FUNCTION';
SQL>select object_name,status from user_objects whereobject_type='PROCEDURE';
查看函数和过程的源代码
SQL>select text from all_source where owner=user andname=upper('&plsql_name');
九、触发器
查看触发器
set long 50000;
set headingoff;
set pagesize2000;
select
'create orreplace 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
fromuser_triggers;
100.如果你要用ultraEdit-32编辑oracle脚本，可以设置对关键字不同亮度的显示。点"advanced"-->"configuration"-->"SyntaxHighlighting"
-->"open"，然后把下面的代码追加到最后保存,再重新打开ultraEdit，编辑.sql文件就可以看到了。这样颜色上容易分辨。方便一些。
/L8"sql"Nocase Line Comment = -- Block Comment On = /* Block Comment Off = */ StringChars = ' File Extensions = SQL
/Delimiters = ~!%@^&*()-+=|\/{}[]:;"'<>, .?
/Function String = "%[A-Z _]*[~\s]+(*)"
/C1 "Keywords"
ABORT ACCEPT ACCESS ADD ALL ALTER AND ANY ARRAY ARRAYLEN AS ASC ASSERT ASSIGNAT AUDIT AUTHORIZATION AVG
BASE_TABLE BEGIN BETWEEN BINARY_INTEGER BODY BOOLEAN BY
CASE CHAR CHAR_BASE CHECK CLOSE CLUSTER CLUSTERS COLAUTH COLUMN COMMENT COMMITCOMPRESS CONNECT CONSTANT CRASH CREATE CURRENT CURRVAL CURSOR
DATABASE DATA_BASE DATE DBA DEBUGOFF DEBUGON DECLARE DECIMAL DEFAULT DEFINITIONDELAY DELETE DESC DIGITS DISPOSE DISTINCT DO DROP
ELSE ELSIF END ENTRY EXCEPTION EXCEPTION_INIT EXCLUSIVE EXISTS EXIT
FALSE FETCH FILE FLOAT FOR FORM FROM FUNCTION
GENERIC GOTO GRANT GROUP
HAVING
IDENTIFIED IF IMMEDIATE IN INCREMENT INDEX INDEXES INDICATOR INITIAL INSERTINTEGER INTERFACE INTERSECT INTO IS
LEVEL LIKE LIMITED LOCK LONG LOOP
MAX MAXEXTENTS MIN MINEXTENTS MINUS MISLABEL MOD MODE
NATURAL NATURALN NEW NEXTVAL NOAUDIT NOCOMPRESS NOLOGGING NOT NOWAIT NULLNUMBER NUMBER_BASE
OF OFFLINE ON ONLINE OPEN OPTION OR ORDER OTHERS OUT
PACKAGE PARTITION PCTFREE PCTUSED PLS_INTEGER POSITIVE POSITIVEN PRAGMA PRIORPRIVATE PRIVILEGES PROCEDURE PUBLIC
RAISE RANGE RAW REAL RECORD REF RELEASE REMR RENAME RESOURCE RETURN REVERSEREVOKE ROLLBACK ROW ROWID ROWLABEL ROWNUM ROWS ROWTYPE RUN
SAVEPOINT SCHEMA SELECT SEPARATE SESSION SET SHARE SMALLINT SPACE SQL SQLCODESQLERRM START STATEMENT STDDEV SUBTYPE SUCCESSFUL SUM SYNONYM SYSDATE
TABAUTH TABLE TABLES TASK TERMINATE THEN TO TRIGGER TRUE TYPE
UID UNION UNIQUE UPDATE USE USER
VALIDATE VALUES VARCHAR VARCHAR2 VARIANCE VIEW VIEWS
WHEN WHENEVER WHERE WHILE WITH WORK WRITE
XOR
/C2 "Packages"
DBMS_OUTPUT
DBMS_JOB
DBMS_SQL
/C3 "Package Methods"
BIND_VARIABLE BIND_VARIABLE_CHAR BIND_VARIABLE_RAW BIND_VARIABLE_ROWID BROKEN
CHANGE CLOSE_CURSOR COLUMN_VALUE COLUMN_VALUE_CHAR COLUMN_VALUE_RAWCOLUMN_VALUE_ROWID
DEFINE_COLUMN DEFINE_COLUMN_CHAR DEFINE_COLUMN_RAW DEFINE_COLUMN_ROWID DISABLE
ENABLE EXECUTE EXECUTE_AND_FETCH
FETCH_ROWS
GET_LINE GET_LINES
INTERVAL IS_OPEN
LAST_ERROR_POSITION LAST_ROW_COUNT LAST_ROW_ID LAST_SQL_FUNCTION_CODE
NEW_LINE NEXT_DATE
OPEN_CURSOR
PARSE PUT PUT_LINE
REMOVE
SUBMIT
WHAT
/C4 "SQL*Plus Commands"
EXECUTE
GRANT
REPLACE
RUN
START
/C5 "SQL*Plus Functions"
ABS ADD_MONTHS ASCII
CEIL CHR CONCAT CONVERT COS COSH
DECODE
EXP
FLOOR
GREATEST
HEXTORAW
INITCAP INSTR
LAST_DAY LENGTH LN LOG LOWER LPAD LTRIM
MOD MONTHS_BETWEEN
NEXT_DAY NEW_TIME NLS_INITCAP NLS_LOWER NLS_UPPER NVL
POWER
RAWTOHEX REPLACE ROUND ROWIDTOCHAR RPAD RTRIM
SIGN SIN SINH SOUNDEX SQRT SUBSTR SUBSTRB
TAN TANH TO_CHAR TO_DATE TO_LABEL TO_MULTI_BYTE TO_NUMBER TO_SINGLE_TYPETRANSLATE TRUNC
UPPER USERENV
VSIZE
/C6 "Should Be Reserved"
BEFORE
EACH
FOR
KEY
PRIMARY
WHEN WHERE WHILE
/C7 "CONST Var"
DUP_VAL_INDEX TIMEOUT_ON_RESOURCE TRANSACTION_BACKED_OUT INVALID_CURSORNOT_LOGED_ON LOGIN_DENIED
NO_DATA_FOUND SYS_INVALID_ROWID TOO_MANY_ROWS ZERO_DIVIDE INVALID_NUMBERSTORAGE_ERROR PROGRAM_ERROR VALUE_ERROR
NOTFOUND FOUND
查看表的分区信息：
select * from user_tab_partitionswhere table_name ='PAYCARD_INFO'
3701
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
