oracle_dba常用sql脚本.doc

Oracle SQL脚本命令监控SQL1.监控事例的等待：select event,sum(decode(wait_time,0,0,1) prev, sum(decode(wait_time,0,1,0) curr,count(*)from v$session_waitgroup by event order by 4;2.回滚段的争用情况：select name,waits,gets,waits/gets ratio from v$rollstat a,v$rollname b where a.usn=b.usn;3.监控表空间的I/O 比例：select df.tablespace_name name,df.file_name file,f.phyrds pyr,f.phyblkrd pbr,f.phywrts pyw,f.phyblkwrt pbwfrom v$filestat f,dba_data_files dfwhere f.file#=df.file_id4.监空文件系统的I/O 比例：select substr(a.file#,1,2) #,substr(,1,30) name,a.status,a.bytes,b.phyrds,b.phywrtsfrom v$datafile a,v$filestat bwhere a.file#=b.file#5.在某个用户下找所有的索引：select user_indexes.table_name, user_indexes.index_name,uniqueness, column_namefrom user_ind_columns, user_indexeswhere user_ind_columns.index_name = user_indexes.index_nameand user_ind_columns.table_name = user_indexes.table_nameorder by user_indexes.table_type, user_indexes.table_name,user_indexes.index_name, column_position;6.进程监控：select distinct p.spid unix_process,s.terminal,to_char(s.logon_time,YYYY/MON/DD HH24:MI) Logon_Time,s.usernamefrom v$process p, v$session swhere p.addr=s.paddr order by 2 7. 监控 SGA 中字典缓冲区的命中率select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 miss ratio,(1-(sum(getmisses)/ (sum(gets)+sum(getmisses)*100 Hit ratiofrom v$rowcachewhere gets+getmisses 0group by parameter, gets, getmisses;8. 监控 SGA 中共享缓存区的命中率，应该小于1%select sum(pins) Total Pins, sum(reloads) Total Reloads,sum(reloads)/sum(pins) *100 libcachefrom v$librarycache;select sum(pinhits-reloads)/sum(pins) hit radio,sum(reloads)/sum(pins) reload percentfrom v$librarycache;9. 显示所有数据库对象的类别和大小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_requiredfrom dba_object_sizegroup by type order by 2;10. 监控 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) ratio2FROM v$latch WHERE name IN (redo allocation, redo copy);11. 监控内存和硬盘的排序比率，最好使它小于 .10，增加 sort_area_sizeSELECT name, value FROM v$sysstat WHERE name IN (sorts (memory), sorts (disk);12. 监控当前数据库谁在运行什么SQL语句SELECT osuser, username, sql_text from v$session a, v$sqltext bwhere a.sql_address =b.address order by address, piece;13. 监控字典缓冲区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 MISSESFROM V$ROWCACHE14. 找ORACLE 字符集select * from ps$ where name=NLS_CHARACTERSET;15. 监控 MTSselect 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 时，参数需加大16. 碎片程度select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_namehaving count(tablespace_name)10;alter tablespace name coalesce;alter table name deallocate unused;create or replace view ts_blocks_v asselect tablespace_name,block_id,bytes,blocks,free space segment_name from dba_free_spaceunion allselect tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;select * from ts_blocks_v;select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_spacegroup by tablespace_name;查看碎片程度高的表SELECT segment_name table_name , COUNT(*) extentsFROM dba_segments WHERE owner NOT IN (SYS, SYSTEM) GROUP BY segment_nameHAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);17. 表、索引的存储情况检查select segment_name,sum(bytes),count(*) ext_quan from dba_extents wheretablespace_name=&tablespace_name and segment_type=TABLE group bytablespace_name,segment_name;select segment_name,count(*) from dba_extents where segment_type=INDEX and owner=&ownergroup by segment_name;18、找使用CPU 多的用户session12 是cpu used by this sessionselect a.sid,spid,status,substr(gram,1,40) prog,a.terminal,osuser,value/60/100 valuefrom v$session a,v$process b,v$sesstat cwhere c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;20.监控log_buffer 的使用情况：(值最好小于1%，否则增加log_buffer 的大小)select ,rbar.value,,re.value,(rbar.value*100)/re.value|% radiofrom v$sysstat rbar,v$sysstat rewhere =redo buffer allocation retriesand =redo entries;19、查看运行过的SQL 语句：SELECT SQL_TEXTFROM V$SQL常用用户SQL表：select * from cat;select * from tab;select table_name from user_tables;视图：select text from user_views where view_name=upper(&view_name);索引：select index_name,table_owner,table_name,tablespace_name,status from user_indexes order bytable_name;触发器：select trigger_name,trigger_type,table_owner,table_name,status from user_triggers;快照：select owner,name,master,table_name,last_refresh,next from user_snapshots order byowner,next;同义词：select * from syn;序列：select * from seq;数据库链路：select * from user_db_links;约束限制：select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUSfrom user_constraints WHERE TABLE_name=upper(&TABLE_Name);本用户读取其他用户对象的权限：select * from user_tab_privs;本用户所拥有的系统权限：select * from user_sys_privs;用户：select * from all_users order by user_id;表空间剩余自由空间情况：select tablespace_name,sum(bytes) 总字节数,max(bytes),count(*) from dba_free_space groupby tablespace_name；数据字典：select table_name from dict order by table_name;锁及资源信息：select * from v$lock;不包括DDL 锁数据库字符集：select name,value$ from props$ where name=NLS_CHARACTERSET;inin.ora 参数：select name,value from v$parameter order by name;SQL 共享池：select sql_text from v$sqlarea;数据库：select * from v$database控制文件：select * from V$controlfile;重做日志文件信息：select * from V$logfile;来自控制文件中的日志文件信息：select * from V$log;来自控制文件中的数据文件信息：select * from V$datafile;NLS 参数当前值：select * from V$nls_parameters;ORACLE 版本信息：select * from v$version;描述后台进程：select * from v$bgprocess;查看版本信息：select * from product_component_version;查询表结构select substr(table_name,1,20) tabname,substr(column_name,1,20)column_name,rtrim(data_type)|(|data_length|) from system.dba_tab_columnswhere owner=username表空间使用状态select a.file_id FileNo,a.tablespace_name Tablespace_name,round(a.bytes/1024/1024,4) Total MB,round(a.bytes-sum(nvl(b.bytes,0)/1024/1024,4) Used MB,round(sum(nvl(b.bytes,0)/1024/1024,4) Free MB,round(sum(nvl(b.bytes,0)/a.bytes*100,4) %Freefrom dba_data_files a, dba_free_space bwhere a.file_id=b.file_id(+)group by a.tablespace_name,a.file_id,a.bytes order by a.tablespace_name查询某个模式下面数据不为空的表declareCursor c is select TNAME from tab;vCount Number;table_nm Varchar2(100);sq varchar2(300);beginfor r in c looptable_nm:=r.TNAME;sq:=select count(*) from | table_nm;execute immediate sq into vCount;if vCount0 thendbms_output.put_line(r.tname);end if;end loop;end;客户端主机信息SELECTSYS_CONTEXT(USERENV,TERMINAL) TERMINAL,SYS_CONTEXT(USERENV,HOST) HOST,SYS_CONTEXT(USERENV,OS_USER) OS_USER,SYS_CONTEXT(USERENV,IP_ADDRESS) IP_ADDRESSFROM DUAL安装Oracle 后，经常使用的修改表空间的SQL 代码配置：Windows NT 4.0 中文版5 块10.2GB SCSI 硬盘分：C:盘、D:盘、E:盘、F:盘、G:盘Oracle 8.0.4 for Windows NTNT 安装在C:WINNT，Oracle 安装在C:ORANT目标：因系统的回滚段太小，现打算生成新的回滚段，建立大的、新的表空间（数据表空间、索引表空间、回滚表空间、临时表空间、）建两个数据表空间、两个索引表空间，这样建的目的是根据实际应用，如：现有10 个应用用户，每个用户是一个独立子系统（如：商业进销存MIS系统中的财务、收款、库存、人事、总经理等）尤其大型商场中收款机众多，同时访问进程很多，经常达到50-100 个进程同时访问，这样，通过建立多个用户表空间、索引表空间，把各个用户分别建在不同的表空间里（多个用户表空间放在不同的物理磁盘上），减少了用户之间的I/O 竞争、读写数据与写读索引的竞争（用户表空间、索引表空间也分别放在不同的物理磁盘上）规划：C:盘、NT 系统，Oracle 系统D:盘、数据表空间1（3GB、自动扩展）、回滚表空间1（1GB、自动扩展）E:盘、数据表空间2（3GB、自动扩展）、回滚表空间2（1GB、自动扩展）F:盘、索引表空间1（2GB、自动扩展）、临时表空间1（0.5GB、不自动扩展）G:盘、索引表空间2（2GB、自动扩展）、临时表空间2（0.5GB、不自动扩展）注：这只是一个简单的规划，实际规划要依系统需求来定，尽量减少I/O 竞争实现：1、首先查看系统有哪些回滚段及其状态。SQL col owner format a20SQL col status format a10SQL col segment_name format a20SQL col tablespace_name format a20SQL SELECT OWNER,SEGMENT_NAME,TABLESPACE_NAME,SUM(BYTES)/1024/1024 M2 FROM DBA_SEGMENTS3 WHERE SEGMENT_TYPE=ROLLBACK4 GROUP BY OWNER,SEGMENT_NAME,TABLESPACE_NAME5 /OWNER SEGMENT_NAME TABLESPACE_NAME M- - - -SYS RB1 ROLLBACK_DATA .09765625SYS RB10 ROLLBACK_DATA .09765625SYS RB11 ROLLBACK_DATA .09765625SYS RB12 ROLLBACK_DATA .09765625SYS RB13 ROLLBACK_DATA .09765625SYS RB14 ROLLBACK_DATA .09765625SYS RB15 ROLLBACK_DATA .09765625SYS RB16 ROLLBACK_DATA .09765625SYS RB2 ROLLBACK_DATA .09765625SYS RB3 ROLLBACK_DATA .09765625SYS RB4 ROLLBACK_DATA .09765625SYS RB5 ROLLBACK_DATA .09765625SYS RB6 ROLLBACK_DATA .09765625SYS RB7 ROLLBACK_DATA .09765625SYS RB8 ROLLBACK_DATA .09765625SYS RB9 ROLLBACK_DATA .09765625SYS RB_TEMP SYSTEM .24414063SYS SYSTEM SYSTEM .1953125查询到18记录.SQL SELECT SEGMENT_NAME,OWNER,2 TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS3 FROM DBA_ROLLBACK_SEGS4 /SEGMENT_NAME OWNER TABLESPACE_NAME SEGMENT_ID FILE_ID STATUS- - - - - -SYSTEM SYS SYSTEM 0 1 ONLINERB_TEMP SYS SYSTEM 1 1 OFFLINERB1 PUBLIC ROLLBACK_DATA 2 3 ONLINERB2 PUBLIC ROLLBACK_DATA 3 3 ONLINERB3 PUBLIC ROLLBACK_DATA 4 3 ONLINERB4 PUBLIC ROLLBACK_DATA 5 3 ONLINERB5 PUBLIC ROLLBACK_DATA 6 3 ONLINERB6 PUBLIC ROLLBACK_DATA 7 3 OFFLINERB7 PUBLIC ROLLBACK_DATA 8 3 OFFLINERB8 PUBLIC ROLLBACK_DATA 9 3 OFFLINERB9 PUBLIC ROLLBACK_DATA 10 3 OFFLINERB10 PUBLIC ROLLBACK_DATA 11 3 OFFLINERB11 PUBLIC ROLLBACK_DATA 12 3 OFFLINERB12 PUBLIC ROLLBACK_DATA 13 3 OFFLINERB13 PUBLIC ROLLBACK_DATA 14 3 OFFLINERB14 PUBLIC ROLLBACK_DATA 15 3 OFFLINERB15 PUBLIC ROLLBACK_DATA 16 3 OFFLINERB16 PUBLIC ROLLBACK_DATA 17 3 OFFLINE查询到18记录.2、修改代码如下，可把以下代码存入一.sql 文件，如cg_sys.sql，然后以SQL cg_sys.sql调用执行。-注意：各个硬盘上要事先建好oradata 目录-修改现有回滚段，使之失效，下线alter rollback segment rb1 offline;alter rollback segment rb2 offline;alter rollback segment rb3 offline;alter rollback segment rb4 offline;alter rollback segment rb5 offline;alter rollback segment rb6 offline;alter rollback segment rb7 offline;alter rollback segment rb8 offline;alter rollback segment rb9 offline;alter rollback segment rb10 offline;alter rollback segment rb11 offline;alter rollback segment rb12 offline;alter rollback segment rb13 offline;alter rollback segment rb14 offline;alter rollback segment rb15 offline;alter rollback segment rb16 offline;-删除原有回滚段drop rollback segment rb1;drop rollback segment rb2;drop rollback segment rb3;drop rollback segment rb4;drop rollback segment rb5;drop rollback segment rb6;drop rollback segment rb7;drop rollback segment rb8;drop rollback segment rb9;drop rollback segment rb10;drop rollback segment rb11;drop rollback segment rb12;drop rollback segment rb13;drop rollback segment rb14;drop rollback segment rb15;drop rollback segment rb16;-建数据表空间1-收款、库存、订货、远程通信create tablespace USER_DATA1 datafiled:oradatauser1_1.ora size 512M,d:oradatauser1_2.ora size 512M,d:oradatauser1_3.ora size 512M,d:oradatauser1_4.ora size 512M,d:oradatauser1_5.ora size 512M,d:oradatauser1_6.ora size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 128K next 2M pctincrease 0);-initial 128K，因为，用户建在表空间上，而表建在用户里，为用户所拥有，-用户继承数据表空间的存储参数，表继承用户的存储参数-如果initial 设的过大，如：5M，则每建一个空表就要占用5M 的空间，即使一条记录也没有-AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED，设置数据文件自动扩展，每一次扩展增加5M，最大空间不受限-建数据表空间2-物价、人事、结算、财务、总经理、合同、统计create tablespace USER_DATA2 datafilee:oradatauser2_1.ora size 512M,e:oradatauser2_2.ora size 512M,e:oradatauser2_3.ora size 512M,e:oradatauser2_4.ora size 512M,e:oradatauser2_5.ora size 512M,e:oradatauser2_6.ora size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 128K next 2M pctincrease 0);-建索引表空间1create tablespace INDEX_DATA1 datafilef:oradataindex1_1.ora size 512M,f:oradataindex1_2.ora size 512M,f:oradataindex1_3.ora size 512M,f:oradataindex1_4.ora size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 128K next 2M pctincrease 0);-建索引表空间2create tablespace INDEX_DATA2 datafileg:oradataindex2_1.ora size 512M,g:oradataindex2_2.ora size 512M,g:oradataindex2_3.ora size 512M,g:oradataindex2_4.ora size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 128K next 2M pctincrease 0);-建回滚表空间1-设置初始值40M（initial 40M），则每在这个表空间中建一个回滚段，-此回滚段自动继承此回滚表空间的存储参数，也即默认文件为40Mcreate tablespace ROLLBACK_DATA1 datafiled:oradataroll1_1.ora size 512M，d:oradataroll1_2.ora size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 40M next 5M pctincrease 0);-建回滚表空间2create tablespace ROLLBACK_DATA2 datafilee:oradataroll2_1.ora size 512M,e:oradataroll2_2.ora size 512MAUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 40M next 5M pctincrease 0);-建临时表空间1create tablespace TEMPORARY_DATA1 datafilef:oradatatemp1_1.ora size 512Mdefault storage (initial 10M next 3M pctincrease 0);-建临时表空间2create tablespace TEMPORARY_DATA2 datafileg:oradatatemp2_1.ora size 512Mdefault storage (initial 10M next 3M pctincrease 0);-使其真正成为临时的alter tablespace TEMPORARY_DATA1 temporary;alter tablespace TEMPORARY_DATA2 temporary;-建立新的回滚段，每个都一样大，不同大小的回滚段没有什么意义，系统是随机选择的。-建多少个，根据并发访问用户的多少，-如果你们公司每天有50-100 个人员使用Oracle系统开发的管理软件，应该20 个以上create public rollback segment rb01 tablespace rollback_data1;create public rollback segment rb02 tablespace rollback_data1;create public rollback segment rb03 tablespace rollback_data1;create public rollback segment rb04 tablespace rollback_data1;create public rollback segment rb05 tablespace rollback_data1;create public rollback segment rb06 tablespace rollback_data1;create public rollback segment rb07 tablespace rollback_data1;create public rollback segment rb08 tablespace rollback_data1;create public rollback segment rb09 tablespace rollback_data2;create public rollback segment rb10 tablespace rollback_data2;-前8 个建在回滚表空间1 中，后8 个在回滚表空间2create public rollback segment rb11 tablespace rollback_data2;create public rollback segment rb12 tablespace rollback_data2;create public rollback segment rb13 tablespace rollback_data2;create public rollback segment rb14 tablespace rollback_data2;create public rollback segment rb15 tablespace rollback_data2;create public rollback segment rb16 tablespace rollback_data2;create public rollback segment rb17 tablespace rollback_data2;create public rollback segment rb18 tablespace rollback_data2;create public rollback segment rb19 tablespace rollback_data2;create public rollback segment rb20 tablespace rollback_data2;-使回滚段online，即有效alter rollback segment rb01 online;alter rollback segment rb02 online;alter rollback segment rb03 online;alter rollback segment rb04 online;alter rollback segment rb05 online;alter rollback segment rb06 online;alter rollback segment rb07 online;alter rollback segment rb08 online;alter rollback segment rb09 online;alter rollback segment rb10 online;alter rollback segment rb11 online;alter rollback segment rb12 online;alter rollback segment rb13 online;alter rollback segment rb14 online;alter rollback segment rb15 online;alter rollback segment rb16 online;alter rollback segment rb17 online;alter rollback segment rb18 online;alter rollback segment rb19 online;alter rollback segment rb20 online;-查看现有回滚段及其状态col segment format a30SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;-查看数据文件及其所在表空间、大小、状态col file_name format a40col tablespace_name format a20select file_name,file_id,tablespace_name,bytes,status from dba_data_files;至此，表空间重新规划完毕，这里讲的比较通俗，还有好多参数值得设置，能够把Oracle 设置到最优的境界，表空间设置完了，下面，就该好好的整理一下Oracle 的内存区了，Oracle 很有意思，内存越大，效果越明显，所以有必要好好调整一下SGA 区，也就是主要配置ininorcl.ora参数文件。查看回滚段名称及大小COLUMN roll_name FORMAT a13 HEADING Rollback NameCOLUMN tablespace FORMAT a11 HEADING TablspaceCOLUMN in_extents FORMAT a20 HEADING Init/Next ExtentsCOLUMN m_extents FORMAT a10 HEADING Min/Max ExtentsCOLUMN status FORMAT a8 HEADING StatusCOLUMN wraps FORMAT 999 HEADING WrapsCOLUMN shrinks FORMAT 999 HEADING ShrinksCOLUMN opt FORMAT 999,999,999 HEADING Opt. SizeCOLUMN bytes FORMAT 999,999,999 HEADING BytesCOLUMN extents FORMAT 999 HEADING ExtentsSELECTa.owner | . | a.segment_name roll_name, a.tablespace_name tablespace, TO_CHAR(a.initial_extent) | / |TO_CHAR(a.next_extent) in_extents, TO_CHAR(a.min_extents) | / |TO_CHAR(a.max_extents) m_extents, a.status status, b.bytes bytes, b.extents extents, d.shrinks shrinks, d.wraps wraps, d.optsize optFROMdba_rollback_segs a, dba_segments b, v$rollname c, v$rollstat dWHEREa.segment_name = b.segment_nameAND a.segment_name = (+)AND c.usn = d.usn (+)ORDER BY a.segment_name;PL/SQL 入门教程1.1 PL/SQL 简介PL/SQL是ORACLE的过程化语言，包括一整套的数据类型、条件结构、循环结构和异常处理结构，PL/S