历史上最强的 SQL FAQ FOR ORACLE.docx
《历史上最强的 SQL FAQ FOR ORACLE.docx》由会员分享，可在线阅读，更多相关《历史上最强的 SQL FAQ FOR ORACLE.docx（78页珍藏版）》请在冰豆网上搜索。
历史上最强的SQLFAQFORORACLE
历史上最强的sqlFAQforOracle
ORACLE之常用FAQV1.0，已经停止更新，准备出chm版本
为便于大家阅读．此帖置顶．请不要在后面跟上一些"好"，"顶"之类的帖子,如果真的要感谢Piner，请多多提交FAQ．谢谢合作--byFenng
-----------------------------------------------------------------------------------------------------------------
第一部分、SQL&PL/SQL3
[Q]怎么样查询特殊字符，如通配符%与_3
[Q]如何插入单引号到数据库表中3
[Q]怎样设置事务一致性4
[Q]怎么样利用游标更新数据4
[Q]怎样自定义异常4
[Q]十进制与十六进制的转换4
[Q]能不能介绍SYS_CONTEXT的详细用法6
[Q]怎么获得今天是星期几，还关于其它日期函数用法7
[Q]随机抽取前N条记录的问题7
[Q]抽取从N行到M行的记录，如从20行到30行的记录8
[Q]怎么样抽取重复记录8
[Q]怎么样设置自治事务8
[Q]怎么样在过程中暂停指定时间8
[Q]怎么样快速计算事务的时间与日志量8
[Q]怎样创建临时表9
[Q]怎么样在PL/SQL中执行DDL语句9
[Q]怎么样获取IP地址10
[Q]怎么样加密存储过程10
[Q]怎么样在ORACLE中定时运行存储过程10
[Q]怎么样从数据库中获得毫秒10
[Q]如果存在就更新，不存在就插入可以用一个语句实现吗12
[Q]怎么实现左联，右联与外联12
[Q]怎么实现一条记录根据条件多表插入14
[Q]如何实现行列转换15
[Q]怎么样实现分组取前N条记录16
[Q]怎么样把相邻记录合并到一条记录17
[Q]如何取得一列中第N大的值？
17
[Q]怎么样把查询内容输出到文本17
[Q]如何在SQL*PLUS环境中执行OS命令？
18
[Q]怎么设置存储过程的调用者权限18
[Q]怎么快速获得用户下每个表或表分区的记录数18
[Q]可以利用utl_smtp包发邮件，以下是一个发送简单邮件的例子程序19
[Q]可以利用utl_file包，但是，在此之前，要注意设置好Utl_file_dir初始化参数20
第二部分、ORACLE构架体系22
[Q]ORACLE的有那些数据类型22
[Q]Oracle有哪些常见关键字，不能被用于对象名22
[Q]怎么查看数据库版本22
[Q]怎么查看数据库参数22
[Q]怎么样查看数据库字符集23
[Q]怎么样修改字符集23
[Q]怎样建立基于函数索引23
[Q]怎么样移动表或表分区23
[Q]怎么获得当前的SCN24
[Q]ROWID的结构与组成24
[Q]怎么样获取对象的DDL语句24
[Q]如何创建约束的索引在别的表空间上24
[Q]怎么知道那些表没有建立主键24
[Q]怎么样修改表的列名25
[Q]怎么样给sqlplus安装帮助25
[Q]怎么样快速下载Oracle补丁25
[Q]如何移动数据文件26
[Q]如果管理联机日志组与成员26
[Q]怎么样计算REDOBLOCK的大小26
[Q]控制文件包含哪些基本内容26
[Q]如果发现表中有坏块，如何检索其它未坏的数据27
[Q]我创建了数据库的所有用户，我可以删除这些用户吗27
第三部分、备份与恢复28
[Q]如何开启/关闭归档28
[Q]怎样设置定时归档29
[Q]不同版本怎么导出/导入29
[Q]不同的字符集之前怎么导数据29
[Q]怎么样备份控制文件29
[Q]控制文件损坏如何恢复29
[Q]怎么样热备份一个表空间29
[Q]怎么快速得到整个数据库的热备脚本29
[Q]丢失一个数据文件，但是没有备份，怎么样打开数据库30
[Q]丢失一个数据文件，没有备份但是有该数据文件创建以来的归档怎么恢复30
[Q]联机日志损坏如何恢复30
[Q]怎么样创建RMAN恢复目录30
[Q]怎么样在恢复的时候移动数据文件，恢复到别的地点30
[Q]怎么从备份片（backuppiece）中恢复（restore）控制文件与数据文件31
[Q]Rman的format格式中的%s类似的东西代表什么意义31
[Q]执行execdbms_logmnr_d.build（'Logminer.ora','filedirectory'），提示下标超界，怎么办31
[Q]执行executedbms_logmnr.start_logmnr（DictFileName=>'DictFileName'）提示ORA-01843:
无效的月份，这个是什么原因32
第四部分、性能调整32
[Q]如果设置自动跟踪33
[Q]如果跟踪自己的会话或者是别人的会话33
[Q]怎么设置整个数据库系统跟踪33
[Q]怎么样根据OS进程快速获得DB进程信息与正在执行的语句33
[Q]怎么样分析表或索引34
[Q]怎么样快速重整索引34
[Q]如何使用Hint提示35
[Q]怎么样快速复制表或者是插入数据35
[Q]怎么避免使用特定索引35
[Q]Oracle什么时候会使用跳跃式索引扫描35
[Q]怎么样创建使用虚拟索引36
[Q]怎样监控无用的索引36
[Q]怎么样能固定我的执行计划36
[Q]v$sysstat中的class分别代表什么37
[Q]怎么杀掉特定的数据库会话37
[Q]怎么快速查找锁与锁等待37
[Q]如何有效的删除一个大表（extent数很多的表）37
[Q]如何收缩临时数据文件的大小38
[Q]怎么清理临时段38
[Q]怎么样dump数据库内部结构，如上面显示的控制文件的结构38
[Q]如何获得所有的事件代码38
[Q]什么是STATSPACK，我怎么使用它？
39
-----------------------------------------------------------------------------------------------------------------
第一部分、SQL&PL/SQL
[Q]怎么样查询特殊字符，如通配符%与_
[A]select*fromtablewherenamelike'A\_%'escape'\'
[Q]如何插入单引号到数据库表中
[A]可以用ASCII码处理，其它特殊字符如&也一样，如
insertintotvalues（'i'||chr（39）||'m'）;--chr（39）代表字符'
或者用两个单引号表示一个
orinsertintotvalues（'I''m'）;--两个''可以表示一个'
[Q]怎样设置事务一致性
[A]settransaction[isolationlevel]readcommitted;默认语句级一致性
settransaction[isolationlevel]serializable;
readonly;事务级一致性
[Q]怎么样利用游标更新数据
[A]cursorc1is
select*fromtablename
wherenameisnullforupdate[ofcolumn]
……
updatetablenamesetcolumn=……
wherecurrentofc1;
[Q]怎样自定义异常
[A]pragma_exception_init（exception_name,error_number）;
如果立即抛出异常
raise_application_error（error_number,error_msg,true|false）;
其中number从-20000到-20999，错误信息最大2048B
异常变量
SQLCODE错误代码
SQLERRM错误信息
[Q]十进制与十六进制的转换
[A]8i以上版本：
to_char（100,'XX'）
to_number（'4D','XX'）
8i以下的进制之间的转换参考如下脚本
createorreplacefunctionto_base（p_decinnumber,p_baseinnumber）
returnvarchar2
is
l_strvarchar2（255）defaultNULL;
l_numnumberdefaultp_dec;
l_hexvarchar2（16）default'0123456789ABCDEF';
begin
if（p_decisnullorp_baseisnull）then
returnnull;
endif;
if（trunc（p_dec）<>p_decORp_dec<0）then
raisePROGRAM_ERROR;
endif;
loop
l_str:
=substr（l_hex,mod（l_num,p_base）+1,1）||l_str;
l_num:
=trunc（l_num/p_base）;
exitwhen（l_num=0）;
endloop;
returnl_str;
endto_base;
/
createorreplacefunctionto_dec
（p_strinvarchar2,
p_from_baseinnumberdefault16）returnnumber
is
l_numnumberdefault0;
l_hexvarchar2（16）default'0123456789ABCDEF';
begin
if（p_strisnullorp_from_baseisnull）then
returnnull;
endif;
foriin1..length（p_str）loop
l_num:
=l_num*p_from_base+instr（l_hex,upper（substr（p_str,i,1）））-1;
endloop;
returnl_num;
endto_dec;
/
[Q]能不能介绍SYS_CONTEXT的详细用法
[A]利用以下的查询，你就明白了
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
fromdual
[Q]怎么获得今天是星期几，还关于其它日期函数用法
[A]可以用to_char来解决，如
selectto_char（to_date（'2002-08-26','yyyy-mm-dd'）,'day'）fromdual;
在获取之前可以设置日期语言，如
ALTERSESSIONSETNLS_DATE_LANGUAGE='AMERICAN';
还可以在函数中指定
selectto_char（to_date（'2002-08-26','yyyy-mm-dd'）,'day','NLS_DATE_LANGUAGE=American'）fromdual;
其它更多用法，可以参考to_char与to_date函数
如获得完整的时间格式
selectto_char（sysdate,'yyyy-mm-ddhh24:
mi:
ss'）fromdual;
随便介绍几个其它函数的用法：
本月的天数
SELECTto_char（last_day（SYSDATE）,'dd'）daysFROMdual
今年的天数
selectadd_months（trunc（sysdate,'year'）,12）-trunc（sysdate,'year'）fromdual
下个星期一的日期
SELECTNext_day（SYSDATE,'monday'）FROMdual
[Q]随机抽取前N条记录的问题
[A]8i以上版本
select*from（select*fromtablenameorderbysys_guid（））whererownumselect*from（select*fromtablenameorderbydbms_random.value）whererownum注：
dbms_random包需要手工安装，位于$ORACLE_HOME/rdbms/admin/dbmsrand.sql
dbms_random.value（100,200）可以产生100到200范围的随机数
[Q]抽取从N行到M行的记录，如从20行到30行的记录
[A]select*from（selectrownumid,t.*fromtablewhere……
andrownum<=30）whereid>20;
[Q]怎么样抽取重复记录
[A]select*fromtablet1wherewheret1.rowed!
=
（selectmax（rowed）fromtablet2
wheret1.id=t2.idandt1.name=t2.name）
或者
selectcount（*）,t.col_a,t.col_bfromtablet
groupbycol_a,col_b
havingcount（*）>1
如果想删除重复记录，可以把第一个语句的select替换为delete
[Q]怎么样设置自治事务
[A]8i以上版本，不影响主事务
pragmaautonomous_transaction;
……
commit|rollback;
[Q]怎么样在过程中暂停指定时间
[A]DBMS_LOCK包的sleep过程
如：
dbms_lock.sleep（5）;表示暂停5秒。
[Q]怎么样快速计算事务的时间与日志量
[A]可以采用类似如下的脚本
DECLARE
start_timeNUMBER;
end_timeNUMBER;
start_redo_sizeNUMBER;
end_redo_sizeNUMBER;
BEGIN
start_time:
=dbms_utility.get_time;
SELECTvalueINTOstart_redo_sizeFROMv$mystatm,v$statnames
WHEREm.STATISTIC#=s.STATISTIC#
ANDs.NAME='redosize';
--transactionstart
INSERTINTOt1
SELECT*FROMAll_Objects;
--otherdmlstatement
COMMIT;
end_time:
=dbms_utility.get_time;
SELECTvalueINTOend_redo_sizeFROMv$mystatm,v$statnames
WHEREm.STATISTIC#=s.STATISTIC#
ANDs.NAME='redosize';
dbms_output.put_line（'EscapeTime:
'||to_char（end_time-start_time）||'centiseconds'）;
dbms_output.put_line（'RedoSize:
'||to_char（end_redo_size-start_redo_size）||'bytes'）;
END;
[Q]怎样创建临时表
[A]8i以上版本
createglobaltemporarytablename（columnlist）
oncommitpreserverows;--提交保留数据会话临时表
oncommitdeleterows;--提交删除数据事务临时表
临时表是相对于会话的，别的会话看不到该会话的数据。
[Q]怎么样在PL/SQL中执行DDL语句
[A]1、8i以下版本dbms_sql包
2、8i以上版本还可以用
executeimmediatesql;
dbms_utility.exec_ddl_statement（'sql'）;
[Q]怎么样获取IP地址
[A]服务器（817以上）：
utl_inaddr.get_host_address
客户端：
sys_context（'userenv','ip_address'）
[Q]怎么样加密存储过程
[A]用wrap命令，如（假定你的存储过程保存为a.sql）
wrapiname=a.sql
PL/SQLWrapper:
Release8.1.7.0.0-ProductiononTueNov2722:
26:
482001
Copyright（c）OracleCorporation1993,2000.AllRightsReserved.
Processinga.sqltoa.plb
提示a.sql转换为a.plb，这就是加密了的脚本，执行a.plb即可生成加密了的存储过程
[Q]怎么样在ORACLE中定时运行存储过程
[A]可以利用dbms_job包来定时运行作业，如执行存储过程，一个简单的例子，提交一个作业：
VARIABLEjobnonumber;
BEGIN
DBMS_JOB.SUBMIT（:
jobno,'ur_procedure;',SYSDATE,'SYSDATE+1'）;
commit;
END;
之后，就可以用以下语句查询已经提交的作业
select*fromuser_jobs;
[Q]怎么样从数据库中获得毫秒
[A]9i以上版本，有一个timestamp类型获得毫秒，如
SQL>selectto_char（systimestamp,'yyyy-mm-ddhh24:
mi:
ssxff'）time1,
to_char（current_timestamp）time2fromdual;
TIME1TIME2
---------------------------------------------------------------------------------------------
2003-10-2410:
48:
45.65600024-OCT-0310.48.45.656000AM+08:
00
可以看到，毫秒在to_char中对应的是FF。
8i以上版本可以创建一个如下的java函数
SQL>createorreplaceandcompile
javasource
named"MyTimestamp"
as
importjava.lang.String;
importjava.sql.Timestamp;
publicclassMyTimestamp
{
publicstaticStringgetTimestamp（）
{
return（newTimestamp（System.currentTimeMillis（）））.toString（）;
}
};
SQL>javacreated.
注：
注意java的语法，注意大小写
SQL>createorreplacefunctionmy_timestampreturnvarchar2
升级会员 