ORACLE数据库资料整理经典.docx
《ORACLE数据库资料整理经典.docx》由会员分享，可在线阅读，更多相关《ORACLE数据库资料整理经典.docx（93页珍藏版）》请在冰豆网上搜索。
ORACLE数据库资料整理经典
ORACLE数据库管理
ORACLE数据库管理1
1数据库安装及卸载4
1.1系统安装4
1.1.1系统安装时错误4
1.2系统卸载4
2数据库对象设计5
2.1表设计5
2.1.1表空间设计说明5
2.1.2表分区设计说明5
2.2视图设计5
2.3触发器设计6
2.3.1触发器功能6
2.4索引设计6
2.4.1索引的默认结构6
2.4.2索引对dml的影响7
2.4.3索引状态7
2.5job设计7
2.6同义词说明8
3数据库答理（DBA）8
3.1执行SQL轨迹分析8
3.1.1执行计划分析8
3.1.2跟踪文件解析9
3.2数据库参数说明10
3.2.1命令行屏幕显示修改10
3.2.2系统参数修改说明11
3.3数据文件解析11
3.3.1口令文件11
3.3.2监听文件11
3.4用户及权限管理11
3.4.1表查询权限11
3.5数据库备份12
3.5.1冷备份脚本12
3.5.2热备份脚本12
3.6数据库恢复13
3.7数据库级常见问题分析处理13
3.7.1本地服务名配置无法启用13
3.7.2oracle服务无法自己启动问题14
3.7.3无法进行OEM问题14
3.7.4数据库出现乱码问题解决方法14
3.8数据字典说明15
3.8.1与用户有关的数据字典15
3.8.2与逻辑数据结构有关的数据字典15
3.8.3与实例有关的动态性能视图15
3.8.4与实例有关的动态数据字典15
3.9数据库优化16
3.9.1数据库性能优化16
3.9.2SQL语句优化16
3.9.3ORACLE运行环境优化17
3.9.4查询中索引无法使用20
3.10实例解析20
3.11数据库监听操作21
3.11.1对数据库所做的dml语句进行监听的方法21
3.12数据库对象锁22
3.12.1查看是否有被锁对象22
3.12.2对用户帐号进行上锁及解锁22
3.12.3查看哪一个用户处于死锁状态22
3.13正确删除没有用的归档日志文件22
3.14命中率说明22
3.14.1BUFFER命中率22
3.14.2LIBRARY命中率23
3.14.3LATCH命中率23
3.14.4In-MemorySort命中率23
3.14.5BufferNoWait24
3.14.6RedoNoWait24
3.14.7ExecutetoParse25
3.14.8ParseCPUtoParesElapsed25
3.14.9Non-ParseCPU25
3.14.10Rollbacksegment竟争情况26
3.14.11Tablespace的I/O比例26
3.14.12Datafile的I/O比例26
3.14.13重做日志缓存区命中率27
3.14.14碎片程度27
3.15ORACLE中的等待事件27
3.15.1Bufferbusywait27
3.15.2Dbfilescatteredread27
3.15.3Dbfilesequentialread27
3.15.4Dbfilesinglewrite28
3.15.5Directpathread28
3.15.6Directpathwrite28
3.15.7Freebufferinspected28
3.15.8Freebufferwaits28
3.15.9Latchfree28
3.15.10Librarycacheloadlock28
3.15.11Librarycachelock28
3.15.12Librarycachepin28
3.15.13Logbufferspace28
3.15.14Logfileparallelwrite28
3.15.15Logfilesinglewrite28
3.15.16Logfileswitch（archiveingneeded）28
3.15.17Logfileswitch（checkpointincomplete）29
3.15.18Logfilesync29
3.15.19Transaction29
3.15.20Undosegmentextension29
4数据库应用（开发）29
4.1表操作29
4.1.1表主键操作29
4.1.2表分区操作29
4.1.3修改表数据操作29
4.1.4删除表数据操作30
4.2索引操作30
4.2.1普通索引操作30
4.2.2函数索引操作30
4.2.3自定义函数索引操作30
4.2.4查看索引是否失效31
4.2.5索引表空间释放31
4.2.6分区索引在使用时，无法在执行计划列表中显示31
4.3存储过程及函数操作31
4.3.1循环使用31
4.3.2goto、exit及return在程序或循环语句中的作用32
4.3.3no_data_foundandsql%notfoundandsql%rowcount三者之间的区别32
4.3.4绑定变量的使用33
4.4触发器操作33
4.4.1使某个触发器无效33
4.4.2恢复触发器使用33
4.4.3使某张表所涉及的所有触发器无效33
4.4.4恢复某张表所涉及的所有触发器使用33
4.5表空间操作34
4.5.1删除表空间34
4.5.2判断表空间是否需要进行碎片整理34
4.5.3表空间使用大小计算34
4.6数据导入导出操作34
4.7大表数据操作34
4.7.1往主键表中插入上千万条数据的效率问题34
4.7.2快速删除大表中的数据35
4.7.3加速or连接查询大表效率35
4.7.4如果存在则修改否则将该数据添加到表中35
4.8不常见内置函数及分析函数36
4.8.1查看谁登陆了数据库的内置函数36
4.8.2分析函数解析36
4.8.3两个替换函数的区别（replace与translate）38
4.9SQL语句操作技巧汇总38
4.9.1判断某字符串是否是纯数字38
4.9.2查询结果如何按家庭树排列38
4.9.3如何求同一时间段内同一部门相隔5分钟的数据38
4.9.4如何将普通表改为分区表，并保留其数据，将其数据分区保存39
4.9.5查询执行次数多的SQL39
4.9.6查询读硬盘多的SQL39
4.9.7查询排序多的SQL39
4.9.8查询分析的次数太多的SQL39
4.9.9游标的观察SQL39
4.9.10查看当前用户执行的SQL40
4.9.11oracle中number类型的数值是如何存储的40
4.9.12查询数据时为什么要用指定的字段名来代替“*”40
4.9.13查询某个数据库对象的创建时间40
5ORACLE操作异常积累41
5.1ORA-00000:
语句级操作异常41
5.1.1to_char引发的无效数字问题41
5.1.2nvl使用问题41
5.1.3NULL使用使用问题41
5.2ORA-01113:
file3needsmediarecovery41
5.3ORA-00600:
内部错误代码，参数:
[kcratr1_lostwrt],[],[],[],[],[],[],[]42
5.4DUAL查询返回多行数据异常42
5.5ora-3055442
5.6ORA-01123:
无法启动联机备份；未启用介质恢复？
42
5.7ORA-00157:
控制文件版本不区配42
5.8ORA-01092:
ORACLE例程终止。
强行断开连接43
5.9ORA-12154:
TNS:
无法处理服务名45
5.10ORA-01017:
invalidusername/password;logondenied46
5.11ORA-00001无法启动，系统索引正处于监视状态46
数据库安装及卸载
1.1系统安装
1.1.1系统安装时错误
安装过程中选择路径后，会报area…错误，这是因为安装程序包的路径包括中文字符而导致的。
1.2系统卸载
1）单击“开始”—找到命令—ORACLEuniversalinstaller—点击卸载产品—按步骤卸载即可；
2）进入注册表删除相关服务信息
运行regedit选择HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE，press‘del’key，删除所有与oracle相关的项目。
1）选择HKEY_LOCAL_MACHINE|SOFTWARE|ORACLE注册表键，然后删除此键。
删除方法是在树型目录中选中Oracle栏，然后右击鼠标，在弹出式菜单中单击删除。
2）选择HKEY_LOCAL_MACHINE|SYSTEM|CURRENTCONTROLSET|SERVICES,删除SERVICES键下以Oracle为前缀的键。
3）选择选择HKEY_LOCAL_MACHINE|SYSTEM|CURRENTCONTROLSET|SERVICES|EVENTLOG|APPLICATION，删除此键下以Oracle为前缀的键。
4）选择HKEY_CLASSES_ROOT，删除此键下所有以Ora、Oracle、Orcl、EnumOra为前缀的键。
5）选择HKEY_CURRENT_USER|SOFTWARE|MICROSOFT|WINDOWS|CURRENTVERSION|EXPLORER|MENUORDER|STARTMENU|PROGRAMS,删除此键下所有以Oracle为前缀的键。
数据库对象设计
1.3表设计
删除不用的表字段：
ALTERTABLElunartestDROPUNUSEDCOLUMNS;
ALTERTABLElunartestDROPUNUSEDCOLUMNSCHECKPOINT20；
ALTERTABLEscott.employees
DEALLOCATEUNUSED;
1.3.1表空间设计说明
每个数据库都有一个默认表空间，一般新增用户都不会用系统默认的表空间，而是指定一个新的表空间作为新增用户的默认表空间。
表空间段的分配计算：
pctfree=（averagerowsize-initialrowsize）*100/averagerowsize
pctused=100-pctfree-（averagerowsize*100/availabledataspace—现有数据空间）
如果更新表可使得行增大的话，则pctused值设置在40到70之间，其它情况应该设置为60到85之间。
Initial：
该值最好设置为2的倍数；
移动表空间
Altertableusername.table_namemovetablespace_name;
注意：
移动表空间后，表索引需要重建，否则会出现ORA-01502:
索引'ICDSC.IX_CITYCODE_CITYID'或这类索引的分区处于不可用状态。
1.3.2表分区设计说明
分区定义：
在非常大的数据库中，可以通过把一个大表的数据分成多个小表来简化数据库的管理，这些小表称作分区。
一般在数据量相当大的情况下，而且经常需要查询大批量类似的数据时，一般都会考虑去分区的。
分区种类：
range、list、hash
range分区确定最大值之后，不可以再修改，而list可修改；
list和hash只适合9i或9i以上版本。
2.1.3临时表
1、会话临时表
--建表语句：
CreateGLOBALTEMPORARYtabletable_name（,,,）oncommitpreserverows
--该种会话临时表在会话结束时，会自动TRUNCATE
2、事物等待临时表
1.4视图设计
定义：
是一个数据库虚拟的表；
使用视图原则：
1、视图中尽量避免出现数据排序的SQL语句；
2、查询的数据来源于一个或多个表；
3、如果视图中嵌入视图，尽量不要超过三个。
使用视图优点：
用视图使复杂的查询简单化;
提高数据安全，使得只有查看该部分数据权限的人使用
1.5触发器设计
1.5.1触发器功能
主要用于两个数据库或不同用户间的数据同步或者用于管理员对数据库的一些操作进行监控。
触发器用于不同用户间的数据同步的一个用例：
--Theyareinsertingtothetabletestti2whenthedataoftabletesttiareupdated
createorreplacetriggertr_old_test
afterupdateontestti
foreachrow
begin
insertintotestti2（
ti2name,
ti2id,
flag
）values（
:
new.tiname,
:
new.tiid,
'Y'
）;
end;
--------------------------------------------------------------
createorreplaceproceduresp_old_test（
str_in_namevarchar2,
i_in_idnumber,
v_setoutnumber
）
is
str_g_vavarchar2（100）;
begin
str_g_va:
='updatetesttisettiname='||chr（39）||str_in_name||chr（39）||'wheretiid='||i_in_id;
dbms_output.put_line（str_g_va）;
EXECUTEIMMEDIATEstr_g_va;
v_set:
=0;
commit;
exception
whenothersthen
rollback;
v_set:
=1;
end;
----------------------------------------------------------------------
createtabletestti（
tinamevarchar2（10）,
tiidnumber
）;
createtabletestti2
（
ti2namevarchar2（10）,
ti2idnumber,
flagchar
（1）
）
1.6索引设计
1.6.1索引的默认结构
默认的索引是b_tree索引；
1.6.2索引对dml的影响
对insert的影响.（分裂，要保证tree的平衡）
对delete的影响.（删除行的时候要标记改节点为删除）.
对update的影响，如果更新表中的索引字段，则要相应的更新索引中的键值。
查询中包含索引字段的键值和行的物理地址。
1.6.3索引状态
1、VAILD---可用的；
2、N/A-----不确定的，一般为分区索引状态即本地索引状态；
--当普通索引被建为本地索引时，状态同样为N/A，修改为全局索引后，马上要变为VAILD即可用的
--对分区表建立索引时添加LOCAL关键字，索引只作用于分区，而不是作用于整个表。
--8I数据库执行计划看不出明显区别。
3、UNUSABLE—不可用的；
1.7job设计
修改文件initsid.ora的参数:
job_queue_processes=4 8i,9i （允许同时执行的JOB数）
job_queue_interval=10 8i
job_queue_keep_connections=true 8i
DBMS_JOB.SUBMIT（:
jobno,//job号
‘your_procedure;’,--shallrunprocedure
trunc（sysdate）+1/24,--nextruntime
‘trunc（sysdate）+1/24+1’--alternationtimeatatime
）;
删除job:
dbms_job.remove（jobno）;
修改job:
dbms_job.what（jobno,what）;
修改下一次运行的时间：
dbms_job.next_date（job,next_date）;
修改间隔时间：
dbms_job.interval（job,interval）;
stopjob:
dbms.broken（job,broken,nextdate）;
startjob:
dbms_job.run（jobno）;
注意：
修改后一定要COMMIT；payattendtiontoupdateaftermustcommit;
--删除临时表任务：
在每天临晨5点，清空报表临时表数据，以求加快报表查询速度
declare
v_countnumber;
v_jobnumnumber:
=400;
begin
selectcount（*）
intov_count
fromsys.dba_jobst
wheret.what='p_magent_truncrpttmptable;'
andt.schema_user='magent';
if（v_count=0）then
sys.dbms_job.submit（job=>v_jobnum,
what=>'p_magent_truncrpttmptable;',
next_date=>to_date（to_char（sysdate,'yyyy-mm-dd'）||
'05:
00:
00',
'yyyy-mm-ddhh24:
mi:
ss'）,
interval=>'trunc（sysdate+1,''dd''）+5/24'）;
commit;
else
selectt.job
intov_jobnum
fromsys.dba_jobst
wheret.what='p_magent_truncrpttmptable;'
andt.schema_user='magent';
sys.dbms_job.change（job=>v_jobnum,
what=>'p_magent_truncrpttmptable;',
next_date=>to_date（to_char（sysdate,'yyyy-mm-dd'）||
'05:
00:
00',
'yyyy-mm-ddhh24:
mi:
ss'）,
interval=>'trunc（sysdate+1,''dd''）+5/24'）;
commit;
endif;
end;
1.8同义词说明
A：
封装数据库对象
用于简单化同一数据库不同用户或不同数据库之间的访问
同义词创建，如在icd用户下创建以下同义词：
createorreplacesynonymT_PUB_COMMONINFOforICDSC.T_PUB_COMMONINFO--（或者是对象名+dblink）;
则在icd用户下访问icdsc用户下的表t_pub_commoninfo时，就可以直接用：
select*fromt_pub_commoninfo；
B：
同义词权限
同义词也分为privateandpublic，默认情况下就是私有的。
所以drop同义词时需要加上权限关键字，否则会提示相应的同义词不存在。
数据库答理（DBA）
1.9执行SQL轨迹分析
1.9.1执行计划分析
利用命令查看执行计划，一般需要对AUTOTRACE进行配置，具体步骤如下：
1、建计划表：
SQL>@?
\rdbms\admin\utlxplan；
表已创建。
2、建表的同义字：
SQL>createpublicsynonymplan_tableforplan_table;
3、授权同义字：
SQL>grantallonplan_tabletopublic;
授权成功。
4、创建角色：
SQL>createrolerol_strace;
角色已创建
5、授权：
SQL>grantselectonv_$sesstattorol_strace;
授权成功。
SQL>grantselectonv_$statnametorol_strace;
授权成功。
SQL>grantselectonv_$sessiontorol_strace;
授权成功。
SQL>grantrol_stracetodbawithadminoption;
授权成功。
DBA用户首先被授予了plustrace角色,然后我们可以把plustrace授予public
这样所有用户都将拥有plustrace角色的权限.
SQL>grantplustracetopublic;
授权成功。
SQL>connecticddrpt/icdrpt
SQL>setautotraceon
SQL>settimingon
关于Autotrace几个常用选项的说明:
SETAUTOTRACEOFF----------------不生成AUTOTRACE报告，这是缺省模式
SETAUTOTRACEONEXPLAIN------AUTOTRACE只显示优化器执行路径报告
SETAUTOTRACEONSTATISTICS--只显示执行统计信息
SETAUTOTRACEON-----------------包含执行计划和统计信息
SETAUTOTRACETRACEONLY------同setautotraceon，但是不显示查询输出
SQL>setautotracetraceonly
SQL>selecttable_namefromuser_tables;
已选择46行。
已用时间:
00:
00:
00.01
Exe
升级会员 