ORACLE SQL.doc

监控USERselect 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 21. 监控事例的等待： select event,sum(decode(wait_time,0,0,1) prev, sum(decode(wait_time,0,1,0) curr,count(*)from v$session_wait group 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 pbw from 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_name order by user_indexes.table_type, user_indexes.table_name,user_indexes.index_name, column_position;6. 监控 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 cwhere a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40; 7. 监控 SGA 中字典缓冲区的命中率select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 miss ratio,(1-(sum(getmisses)/ (sum(gets)+sum(getmisses)*100 Hit ratiofrom v$rowcache where 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 percent from 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_required from dba_object_size group 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_size SELECT 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_name having 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_space group 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 where tablespace_name=&tablespace_name and segment_type=TABLE group by tablespace_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常用用户SQLSQL语句： 表： 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 by table_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 by owner,next; 同义词： select * from syn; 序列： select * from seq; 数据库链路： select * from user_db_links; 约束限制： select TABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUS from 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 group by 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:=selectcount(*)from | table_nm;execute immediate sq into vCount;if vCount0 then dbms_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 M 2FROM DBA_SEGMENTS 3WHERE SEGMENT_TYPE=ROLLBACK 4GROUP 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,STATUS 3 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 datafile d: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 512M AUTOEXTEND 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 datafile e: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 512M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 128K next 2M pctincrease 0);-建索引表空间1create tablespace INDEX_DATA1 datafile f:oradataindex1_1.ora size 512M,f:oradataindex1_2.ora size 512M,f:oradataindex1_3.ora size 512M,f:oradataindex1_4.ora size 512M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 128K next 2M pctincrease 0);-建索引表空间2create tablespace INDEX_DATA2 datafile g:oradataindex2_1.ora size 512M,g:oradataindex2_2.ora size 512M,g:oradataindex2_3.ora size 512M,g:oradataindex2_4.ora size 512M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 128K next 2M pctincrease 0);-建回滚表空间1-设置初始值40M（initial 40M），则每在这个表空间中建一个回滚段，-此回滚段自动继承此回滚表空间的存储参数，也即默认文件为40Mcreate tablespace ROLLBACK_DATA1 datafile d:oradataroll1_1.ora size 512M，d:oradataroll1_2.ora size 512M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 40M next 5M pctincrease 0);-建回滚表空间2create tablespace ROLLBACK_DATA2 datafile e:oradataroll2_1.ora size 512M,e:oradataroll2_2.ora size 512M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITEDdefault storage (initial 40M next 5M pctincrease 0);-建临时表空间1create tablespace TEMPORARY_DATA1 datafile f:oradatatemp1_1.ora size 512M default storage (initial 10M next 3M pctincrease 0);-建临时表空间2create tablespace TEMPORARY_DATA2 datafile g:oradatatemp2_1.ora size 512M default 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 se