ORACLE SQL.doc

1、监控 USERselect distinctp.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

2、 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.fi

3、le_id4.监空文件系统的 I/O 比例：select substr(a.file#,1,2) “#“,substr(a.name,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

4、 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 *

5、(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

6、(gets)+sum(getmisses)*100 “Hit ratio“from v$rowcache where gets+getmisses 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,

7、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 B

8、Y 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=select segment_name,count(*) from dba_extents where segment_type=INDEX and owner=18、找使用 CPU 多的用户 ses

9、sion12 是 cpu used by this sessionselect a.sid,spid,status,substr(a.program,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 r

10、bar.name,rbar.value,re.name,re.value,(rbar.value*100)/re.value|% “radio“from v$sysstat rbar,v$sysstat rewhere rbar.name=redo buffer allocation retriesand re.name=redo entries;19、查看运行过的 SQL 语句：SELECT SQL_TEXTFROM V$SQL常用用户 SQLSQL 语句： 表： select * from cat; select * from tab; select table_name from use

11、r_tables; 视图： select text from user_views where view_name=upper( 索引： 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,

12、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( 本用户读取其他用户对象的权限： select * from user_tab_privs; 本用

13、户所拥有的系统权限： 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 锁 数

14、据库字符集： 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

15、 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(

16、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

17、.bytes,0)/a.bytes*100,4) “%Free“from 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 loop

18、table_nm:=r.TNAME;sq:=select count(*) 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_ADDR

19、ESS) 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 个应用用户，每个用户是一个独立子系统（如：商业进

20、销存 MIS 系统中的财务、收款、库存、人事、总经理等）尤其大型商场中收款机众多，同时访问进程很多，经常达到 50-100 个进程同时访问，这样，通过建立多个用户表空间、索引表空间，把各个用户分别建在不同的表空间里（多个用户表空间放在不同的物理磁盘上） ，减少了用户之间的 I/O 竞争、读写数据与写读索引的竞争（用户表空间、索引表空间也分别放在不同的物理磁盘上）规划：C:盘、NT 系统，Oracle 系统D:盘、数据表空间 1（3GB 、自动扩展） 、回滚表空间 1（1GB 、自动扩展）E:盘、数据表空间 2（3GB 、自动扩展） 、回滚表空间 2（1GB、自动扩展）F:盘、索引表空间 1（2

21、GB、自动扩展） 、临时表空间 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(BYT

22、ES)/1024/1024 M 2 FROM DBA_SEGMENTS 3 WHERE SEGMENT_TYPE=ROLLBACK 4 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 ROLLBAC

23、K_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 .09765625SY

24、S 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- - - - - -SYST

25、EM 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 OFFLIN

26、ERB8 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 OFFLI

27、NERB16 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

28、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

29、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;