oracle常用笔记总结.docx
《oracle常用笔记总结.docx》由会员分享，可在线阅读，更多相关《oracle常用笔记总结.docx（12页珍藏版）》请在冰豆网上搜索。
oracle常用笔记总结
1、用dba权限的用户查看数据库都有哪些锁
selectt2.username,t2.sid,t2.serial#,t2.logon_time
fromv$locked_objectt1,v$sessiont2
wheret1.session_id=t2.sidorderbyt2.logon_time;
2、查看有哪些对象被锁了
selectb.owner,b.object_name,a.session_id,a.locked_mode
fromv$locked_objecta,dba_objectsb
whereb.object_id=a.object_id
3、根据sid查找进程id
selectpro.spidfromv$sessionses,
v$processprowhereses.sid=129andses.paddr=pro.addr;
selectses.sidfromv$sessionses,
v$processprowherepro.spid=129andses.paddr=pro.addr;
4：
根据这个spid对应linux下的进程pid
linux命令：
ps-ef|gre|'ora'（找到所有ora开头的进程）
kill-9（pid）
5、解决delete后表的高水位的问题
初步判断应该是之前有大量数据，后来delete了，选成高水位，用shrink清除高水位：
altertablet_mobile_client_tp_provenablerowmovement;
altertablet_mobile_client_tp_provshrinkspace;
重新收集统计信息：
begin
dbms_stats.gather_table_stats（ownname=>'traffic',tabname=>'T_MOBILE_CLIENT_TP_PROV'）;
end;
6、ORA-02064distributedoperationnotsupported
Cause:
Oneofthefollowingunsupportedoperationswasattempted:
Arrayexecuteofaremoteupdatewithasubquerythatreferencesadatabaselink,or
Anupdateofalongcolumnwithbindvariableandanupdateofasecondcolumnwithasubquerythatbothreferencesadatabaselinkandabindvariable,or
AcommitisissuedinacoordinatedsessionfromanRPCwithOUTparameters.
Action:
Simplifytheremoteupdatestatement.
问题:
数据库A,B通过DBlink互相访问,数据库A调用数据库B的存储过程pro_b,pro_b过程中有out输出参数,并且里面有dml语句,之后commit,或rollback.这时数据库A通过DBlink的调用pro_b@B就会产生这个错误.
解决办法:
a.去掉pro_b中的commit,rollback,统一有数据库B来控制事务.（当然也可以去掉out参数,这样也破坏是事务的完整性）
b.用自制事务来控制pro_b,在pro_b中添加.PRAGMAAUTONOMOUS_TRANSACTION;优点:
不用去修改调用的所有方法.当然这样也不能控制事务的完整.
7、建表开启并行
v_table_sql:
='CREATETABLET_MOBILE_CAP_DETA82_'||in_date
||'parallel（degree4）as'
||'select/*+parallel（t14）*/*fromT_MOBILE_CAP_DETAIL_'||in_date||'@QAS82t1';
EXECUTEIMMEDIATEv_table_sql;
8、创建dblink
CREATEDATABASELINKQAS65.REGRESS.RDBMS.DEV.US.ORACLE.COM
CONNECTTOTRAFFIC
IDENTIFIEDBY"pwd"
USING'（DESCRIPTION=（ADDRESS_LIST=（ADDRESS=（PROTOCOL=TCP）（HOST=192.168.228.65）（PORT=1521）））（CONNECT_DATA=（SID=qas）））'
9、创建临时表
a.createglobaltemporarytabletemp_tbl（col_avarchar2（30））
oncommitpreserverows（当提交后保留数据，session结束时才清除数据）
b.createglobaltemporarytabletemp_tbl（col_avarchar2（30））
oncommitdeleterows（当提交后不保留数据，session结束时也清除数据）
10、drop后的表被放在回收站（user_recyclebin）里，而不是直接删除掉。
这样，回收站里的表信息就可以被恢复，或彻底清除。
a.通过查询回收站user_recyclebin获取被删除的表信息，然后使用语句
flashbacktabletobeforedrop[renameto];
将回收站里的表恢复为原名称或指定新名称，表中数据不会丢失。
若要彻底删除表，则使用语句：
droptablepurge;
b.清除回收站里的信息
清除指定表：
purgetable;
清除当前用户的回收站：
purgerecyclebin;
清除所有用户的回收站：
purgedba_recyclebin;
11、REGEXP_SUBSTR函数格式如下：
functionREGEXP_SUBSTR（String,pattern,position,occurrence,modifier）
__srcstr：
需要进行正则处理的字符串
__pattern：
进行匹配的正则表达式
__position：
起始位置，从第几个字符开始正则表达式匹配（默认为1）
__occurrence：
标识第几个匹配组，默认为1
__modifier：
模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。
默认为'c'。
）
例：
selectregexp_substr（call_logs,'\d*',instr（call_logs,'"duration":
"'）+12,1,'i'）,call_logs
fromt_mobile_callogs_sdk_20130519whereupper（client_type）like'%C8812%'
12、altertableT_MON_APP_COOPmodifymarkvarchar（10）
13、altertableT_MOBILE_CAP_USE_20130822dropcolumnis_run
14、ORACLE利用TNS控制远程访问的IP地址
利用TNS可以实现IP地址的过滤。
如下：
在D:
\oracle\ora92\network\admin的sqlnet.ora文件中添加你需要排除或者允许访问的地址按照规定格式
TCP.VALIDNODE_CHECKING=YES该参数表明会对连接进行验证
TCP.EXCLUDED_NODES=（10.83.25.44）该参数表明排除该地址的访问
TCP.INVITED_NODES=（10.83.25.125）该参数表明允许该地址的访问
如果有多个地址怎么搞？
很简单，全部都搞就好了。
TCP.INVITED_NODES=（10.83.25.125,10.83.25.1）中间用逗号隔开。
首先查看当前使用的数据库实例:
selectnamefromV$database;
切换两个数据库实例
在sqlplus里connectusername/passWord@sid
或hostsetORACLE_SID=sid
或cmd里setORACLE_SID=sid
或修改注册表里ORACLE_SID项
更改oracle密码ALTERUSER"TRAFFIC"IDENTIFIEDBY"TRAFFIC209";
密码过期解决办法
1.查看用户的profile设置：
SELECTusername,profileFROMdba_users;
2.查看系统profiles中PASSWORD_LIFE_TIME设置：
SELECT*FROMdba_profilessWHEREs.profile='DEFAULT'ANDresource_name='PASSWORD_LIFE_TIME';
3.修改DBA_PROFILES中PASSWORD_LIFE_TIM的设置，改为ULIMITED
ALTERPROFILEDEFAULTLIMITPASSWORD_LIFE_TIMEUNLIMITED;
4.已经被报告了密码快要过期的账户必须再改一次密码（需要DBA权限）
以system用户为例
alterusersystemidentifiedbyroot;
job执行、删除
execdbms_job.run（4）;
execdbms_job.remove（4）;
commit;
恢复delete的数据
select*fromT_MOBILE_REPORT_LOGasoftimestampto_timestamp（'201312209:
40:
00','yyyy-mm-ddhh24:
mi:
ss'）
whereerror_infolike'%ORA%'orderbyop_datedesc
其中时间点只要在删除之前就行了
ALTERSYSTEMSETdb_block_buffers=1677721SCOPE=BOTH;
生成awr报告
@/home/oracle/product/10.2.0/rdbms/admin/awrrpt.sql;
@/home/oracle/product/11.2.0/rdbms/admin/awrrpt.sql;
@/home/oracle/11g/oracle/product/11.2.0/dbhome_1/rdbms/admin/awrrpt.sql;
oracle:
oinstallasmdbadbaoper
警告日志目录
select*fromv$diag_info;
oracle中NLSSORT函数的用法
NLSSORT（），用来进行语言排序
拼音
SELECT*FROMTEAMORDERBYNLSSORT（排序字段名,'NLS_SORT=SCHINESE_PINYIN_M'）
笔划
SELECT*FROMTEAMORDERBYNLSSORT（排序字段名,'NLS_SORT=SCHINESE_STROKE_M'）
部首
SELECT*FROMTEAMORDERBYNLSSORT（排序字段名,'NLS_SORT=SCHINESE_RADICAL_M'）
plsq求余
ifmod（to_number（substr（in_date,7,2））,2）=0then
endif
select*fromdba_objectsa,v$locked_objectbwherea.object_id=b.object_id;
ORACLE里锁有以下几种模式:
0：
none
1：
null空
2：
Row-S行共享（RS）：
共享表锁，subshare
3：
Row-X行独占（RX）：
用于行的修改，subexclusive
4：
Share共享锁（S）：
阻止其他DML操作，share
5：
S/Row-X共享行独占（SRX）：
阻止其他事务操作，share/subexclusive
6：
exclusive独占（X）：
独立访问使用，exclusive
selectobject_name,machine,s.sid,s.serial#fromv$locked_objectl,dba_objectso,v$sessionswherel.object_id=o.object_idandl.session_id=s.sid;
ps-ef|grepLOCAL=NO|grep-vgrep|cut-c9-15|xargskill-9
dbms_space_admin.segment_drop_corrupt
execdbms_space_admin.segment_corrupt（'TRAFFIC',153,977835）;
execdbms_space_admin.segment_drop_corrupt（'TRAFFIC',153,977835）;
execdbms_space_admin.tablespace_verify（'TRAFFIC'）;
select'execdbms_space_admin.segment_corrupt（''TRAFFIC'','||HEADER_FILE||','||HEADER_BLOCK||'）;'FROM
dba_segmentswheresegment_typelike'TEMP%'andtablespace_name='TRAFFIC'
让数据文件脱机
alterdatabasedatafile'/oradata/qas/traffic23.dbf'offlinedrop;
alterdatabasedatafile'/oradata/qas/system02.dbf'offlinedrop;
系统全局区SGA：
SGA包含的组件：
共享池（sharedpool）；数据库缓冲区高速缓存（databasebuffercache）；日志缓冲区（redobuffercache）；大池；Java池；流池等。
SGA相关视图：
v$sga：
V$SGA这个视图包括了SGA的的总体情况，只包含两个字段：
name（SGA内存区名字）和value（内存区的值，单位为字节）。
它的结果和showsga的结果一致。
v$sgastat：
10g之前用于查看各SGA组件大小。
V$SGAINFO的作用基本和V$SGA一样，只不过把Variablesize的部分更细化了一步
v$sgainfo：
10g及10g之后才有的。
用于查看SGA组件大小更简便。
v$sga_dynamic_components：
这个视图记录了SGA各个动态内存区的情况，它的统计信息是基于已经完成了的，针对SGA动态内存区大小调整的操作。
v$sga_dynamic_free_memory：
这个视图只有一个字段就是用来表示SGA当前可以用于调整各个组件的剩余大小。
v$sga_target_advice：
该视图可用于建议SGA大小设置是否合理。
SELECTa.sga_size,--sga期望大小
a.sga_size_factor,--期望sga大小与实际sga大小的百分比
a.estd_db_time,--sga设置为期望的大小后，其dbtime消耗期望的变化
a.estd_db_time_factor,--修改sga为期望大小后，dbtime消耗的变化与修改前的变化百分比
a.estd_physical_reads--修改前后物理读的差值
FROMv$sga_target_advicea;
OracleSqlplus设置行数和宽度
设置页面显示总行数
showpagesize;
setpagesize100;
设置行的宽度
showlinesize;
setlinesize100;
设置默认的值
在Oracle的安装目录下找到glogin.sql，加入
setlinesize300;
setpagesize100;
declare
in_dateVARCHAR2（10）;
V_TOTAL_DAYSnumber;
v_dateVARCHAR2（10）;
BEGIN
in_date:
='201402';
V_TOTAL_DAYS:
=last_day（to_date（in_date||'01','yyyymmdd'））-to_date（in_date||'01','yyyymmdd'）+1;
forXIN1..V_TOTAL_DAYSLOOP--循环开始
v_date:
=to_char（to_date（in_date||'01','yyyymmdd'）+X-1,'yyyymmdd'）;
dbms_output.put_line（v_date）;
endloop;
end;
设置job数量，如果为0，job就不会执行
ALTERSYSTEMSETjob_queue_processes=20;
commit;
启用/禁用sys用户远程登陆
altersystemsetremote_login_passwordfile=exclusivescope=spfile;
altersystemsetremote_login_passwordfile=nonescope=spfile;
查看占用system表空的用户
selectusername,default_tablespace,temporary_tablespacefromdba_userswhereusernamenotin
（'SYS','SYSTEM'）and（default_tablespace='SYSTEM'ortemporary_tablespace='SYSTEM'）;
11g中oracle的审计功能是默认开启的（10g是默认关闭的），统计信息会影响数据库性能，也会给system表空间带来巨大的压力
问题处理方法：
1.如果审计不是必须的，可以关掉审计功能；
SQL>altersystemsetaudit_trail=nonescope=spfile;
SQL>showdownimmediate;
SQL>startup
2.删除已有的审计信息
可以直接truncate表aud$,
或者采取dbms_audit_mgmt来清除。
3.或者将aud$表移到另外一个表空间下，以减少system表空间的压力和被撑爆的风险
sqlplus下查看执行计划：
1、EXPLAINPLANFOR
select*fromdual;
然后查看
2、SELECT*FROMTABLE（DBMS_XPLAN.DISPLAY）;
强制使用索引
select/*+index（ti_t）*/*fromtwhereusername='EYGLE';
遇到
ORA-39006:
internalerror
ORA-39213:
Metadataprocessingisnotavailable
执行
sqlplus/assysdba
executesys.dbms_metadata_util.load_stylesheets;
ORA-00020:
maximumnumberofprocesses（150）exceeded错误解决方法
1、批量杀死过多的进程
psx|greporacleqas|grep-vgrep|awk'{print$1}'|xargskill-9
启动OEM
emctlstartdbconsole
查看地址
https:
//172.16.22.151:
1158/em/
查询dblink的信息
selectcount（*）fromlink$;
查看某个表的索引
selectdistinctindex_namefromdba_indexeswheretable_name='T_MOBILE_MID_USE_P';
删除某一列
altertableT_MOBILE_APP_USE_201403301dropcolumnrn
更改某用户的环境变量，使之永久生效
如，在/home/oracle/.bash_profile中添加exportNLS_LANG=american_america.utf8
source/home/oracle/.bash_profile让更改的配置立即生效，不然得重新登陆此用户才有效
注意：
更改/etc/profile文件对所有用户有效果
查看redolog日志的切换时间
SELECTb.recid,
TO_CHAR（b.first_time,'dd-mon-yyhh24:
mi:
ss'）start_time,a.recid,
TO_CHAR（a.first_time,'dd-mon-yyhh24:
mi:
ss'）end_time,
ROUND（（（a.first_time-b.first_time）*25）*60,2）minutes
FROMv$log_historya,v$log_historyb
WHEREa.recid=b.recid+1
ORDERBYa.first_timeDESC
用createtableas创建事务级的临时表
createglobaltemporarytablet_app2ONCOMMITPRESERVEROWSparallel（degree4）asselect/*+parallel（t4）*/*fromt_mobile_application
设置oracle闪回
altersystemsetrecyclebin=on|off;
flashbacktabletb_employeestobeforedrop;
清理某个分区
altertablet_mobile_client_ptruncatepartitionP14
查看分区索引
SELECTINDEX_NAME"NAME",NUM_
升级会员 