ORACLE_button
DEFAULT --format set linesize 200 set pagesize 200 set long 5000 col table_name for a30 col username for a30 col index_name for a30 col name for a30 col file_name for a50 col segment_name for a30 col owner for a20 col object_name for a30 col program for a20 col machine for a20 col event for a40 set sqlprompt "_USER'@'_CONNECT_IDENTIFIER>" alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; set time on set timing on --alert set lines 400 col sql for a400 select '!tail -500f ' || a.VALUE ||'/alert_'|| b.value||'.log' as "sql" from v$parameter a,v$parameter b where a.name='background_dump_dest' and b.name='instance_name'; --wait set pagesize 2000 \n set linesize 210 \n col sql_id for a14 \n col s_time for a12 \n col status for a6 \n col event format a30 \n col username for a14 \n col osuser for a8 \n col p1 for 9999999 \n col sid for 9999 \n col p2 for 999999 \n col p3 for 9999999999999 \n col program format a25 \n Col machine for a20 \n col serial# format 99999 \n select substr(a.program,1,25) program,a.SID,a.SERIAL#,a.USERNAME,substr(a.osuser,1,8) osuser,a.sql_id,substr(a.machine,1,20) machine,a.status, \n to_char(a.SQL_EXEC_START,'dd hh24:mi:ss') s_time,(case when a.STATE='WAITING' then a.event else 'CPU' end) event \n from gv$session a,gv$session_wait b \n where a.sid=b.sid and status = 'ACTIVE' and not ( a.type = 'BACKGROUND' and a.state='WAITING' \n and a.wait_class='Idle')and a.username is not null and a.SQL_ID is not null order by a.program,a.sid,a.sql_id; \n --queryPDB set line 200 col name for a20 select con_id, dbid, guid, name , open_mode from v$pdbs; alter session set container=&PDBNAME; alter pluggable database FASLCS open; ALTER PLUGGABLE DATABASE all save STATE instances=all; --tempwait set pagesize 2000 set linesize 210 col sql_id for a14 col s_time for a12 col status for a6 col event format a30 col username for a14 col osuser for a8 col p1 for 9999999 col sid for 9999 col p2 for 999999 col p3 for 9999999999999 col program format a25 Col machine for a20 col serial# format 99999 select substr(a.program,1,25) program,a.SID,a.SERIAL#,a.USERNAME,substr(a.osuser,1,8) osuser,a.sql_id,substr(a.machine,1,20) machine,a.status, to_char(a.SQL_EXEC_START,'dd hh24:mi:ss') s_time,(case when a.STATE='WAITING' then a.event else 'CPU' end) event from v$session a,v$session_wait b where a.sid=b.sid and status = 'ACTIVE' and not ( a.type = 'BACKGROUND' and a.state='WAITING' and a.wait_class='Idle')and a.username is not null and a.SQL_ID is not null order by a.program,a.sid,a.sql_id; --hh select substr(a.program,1,25) program,a.SID,a.SERIAL#,a.USERNAME,substr(a.osuser,1,8) osuser,a.sql_id,substr(a.machine,1,20) machine,a.status, to_char(a.SQL_EXEC_START,'dd hh24:mi:ss') s_time,a.LAST_CALL_ET/60/60 hh ,(case when a.STATE='WAITING' then a.event else 'CPU' end) event from v$session a,v$session_wait b where a.sid=b.sid and status = 'ACTIVE' and not ( a.type = 'BACKGROUND' and a.state='WAITING' and a.wait_class='Idle')and a.username is not null and a.SQL_ID is not null order by a.program,a.sid,a.sql_id;
--等待对象查询
select a.program,a.SID,a.SERIAL#,a.USERNAME,a.sql_id,a.machine,a.status,a.SQL_EXEC_START,a.LAST_CALL_ET,b.object_name,a.event
from gv$session a ,dba_objects b
where a.ROW_WAIT_OBJ#=b.object_id and a.status='ACTIVE';
--连接数 select INST_ID,username,status ,count(*) from gv$session where username is not null group by inst_id,username,status order by 1,2,3; --当前会话活跃对象 --非后台空闲等待进程活跃会话正在执行的存储过程 set pagesize 2000 set linesize 210 col sql_id for a14 col s_time for a12 col status for a8 col event format a30 col username for a14 col osuser for a8 col p1 for 9999999 col sid for 9999 col p2 for 999999 col p3 for 9999999999999 col program format a25 Col machine for a20 col serial# format 99999 col OBJECT_NAME for a33 select substr(b.program,1,25) program ,b.sid,b.serial#,b.username,ob.object_name, substr(b.osuser,1,8) osuser,b.sql_id,substr(b.machine,1,20) machine, to_char(b.SQL_EXEC_START,'dd hh24:mi:ss') s_time , (case when b.STATE='WAITING' then b.event else 'CPU' end) event from v$session b ,DBA_OBJECTS OB where b.status = 'ACTIVE' and ob.object_id=b.PLSQL_ENTRY_OBJECT_ID and not ( b.type = 'BACKGROUND' and b.state='WAITING' and b.wait_class='Idle' ) and b.username is not null Order by program,sid,sql_id; ##obj --编译失效对象 --conn user/passwd select 'alter ' ||decode(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' ' || OBJECT_NAME || decode(object_type, 'PACKAGE BODY',' compile body ; ', ' compile; ') from user_objects where status <> 'VALID' AND OBJECT_TYPE NOT LIKE 'JAVA%'; --dba_object col STATUS for a10 select OWNER ,OBJECT_NAME,OBJECT_TYPE,STATUS,CREATED,LAST_DDL_TIME from dba_objects where OWNER='&OWNER' and OBJECT_NAME='&OBJECT_NAME'; --dba_segments col TABLESPACE_NAME for a20 col SEGMENT_TYPE for a20 select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024/1024 G from dba_segments where SEGMENT_NAME='&SEGMENT_NAME' and OWNER='&OWNER'; --dba_indexes col TABLE_NAME for a20 col TABLE_OWNER for a20 col STATUS for a10 select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS,degree,TABLESPACE_NAME from dba_indexes where owner='&OWNER' and TABLE_NAME='&TABLE_NAME'; --dba_ind_columns set line 200 col col for a50 col index_name for a40 select index_name ,listagg(column_name,',') within group(order by column_position) as col from dba_ind_columns where table_name = '&tname'group by index_name; --dba_tab_col_statistics col table_name for a30 col column_name for a30 select table_name,column_name,num_distinct,last_analyzed,density,histogram from dba_tab_col_statistics where owner='&OWNER' and table_name='&TAB' order by 1; --tab_ind set lines 199 col table_owner for a15 col index_owner for a15 col table_name for a30 col index_name for a30 col UNIQUENESS for a10 col partitioned for a5 col column_name for a43 select d.table_owner,d.owner index_owner,d.table_name,d.index_name,d.UNIQUENESS,d.partitioned,VISIBILITY, listagg(i.column_name,',') within group (order by i.column_position) column_name from dba_indexes d,dba_ind_columns i where d.table_name=upper('&tname') and d.owner=i.index_owner and d.index_name=i.index_name and d.table_name=i.table_name and d.table_owner=i.table_owner group by d.table_owner,d.owner,d.table_name,d.index_name,d.UNIQUENESS,d.partitioned,VISIBILITY; ##sql优化 --sql性能 select last_active_time latime, child_number chdnum, executions execs, fetches, rows_processed rows_pro, buffer_gets/executions getperexec, disk_reads/executions diskperexec, cpu_time/executions/1000 "CPU_TIME(ms)", elapsed_time/executions/1000 "ELAP_TIME(ms)", rows_processed/executions rowperexec from v$sql where sql_id='&sql_id' ; --会话中PGA的使用情况 select s.SID,s.SERIAL#,s.USERNAME,s.STATUS,s.SQL_ID,p.PID,p.SPID,p.USERNAME,p.PGA_USED_MEM/1024/1024 PUSEDM ,p.PGA_ALLOC_MEM/1024/1024 PALLOCM ,p.PGA_MAX_MEM/1024/1024 PMAXM from v$session s,v$process p where s.paddr=p.addr and s.username is not null and s.status ='ACTIVE' ; --执行计划 --dbms_xplan.display_awr select * from table(dbms_xplan.display_awr('&sql',format=>'PEEKED_BINDS')); --display_cursor --dbms_xplan之display_cursor函数的使用:执行计划 --usage1： --set line 200 --set pagesize 999 --select * from table(dbms_xplan.display_cursor(null,null,'advanced')); --usage2： ---select * from table(dbms_xplan.display_cursor('&sqlid',null,'PEEKED_BINDS')) --select * from table(dbms_xplan.display_cursor('&SQL_IDorSQL_HASH_VALUE',0,'advanced')); --usage3： --set pagesize 200 --set line 200 --set autotrace off --alter session set statistics_level=all ; --select /*+ GATHER_PLAN_STATISTICS */ XXX from dual; --select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); --有时候获取不到，需要set serveroutput off --monitor sql monito实时获取执行sql的执行计划：delete/*+monitor */ delete /*+monitor */ from T_GROUP_PRODUCT where item_id in('516647203'); set long 50000 longc 100000 linesize 200 pagesize 10000 select dbms_sqltune.report_sql_monitor(sql_id => 'f2wb5vkx7nbrc' , type => 'text') from dual; --usage4： --@?/rdbms/admin/awrsqrpt.sql --usage5： --sql10.sql --awrc exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); --awr_class --rpt--@?/rdbms/admin/addmrpt --@?/rdbms/admin/awrrpt.sql --对比--@?/rdbms/admin/awrddrpt.sql--@?/rdbms/admin/ashrpt.sql --db_time set linesize 200 pagesize 200 col DB_CPU_s for 999999 col DB_TIME_s for 99999 col end_time for a20 select INSTANCE_NUMBER, SNAP_ID,to_char(END_INTERVAL_TIME,'yyyy-mm-dd hh24:mi') end_time, round(DB_CPU/60/1000/1000) DB_CPU_s , round(DB_TIME/60/1000/1000) DB_TIME_s from ( select s.instance_number,s.snap_id,s.stat_name ,st.BEGIN_INTERVAL_TIME, st.END_INTERVAL_TIME, value-lag(value) over (partition by s.stat_name order by s.snap_id) value from dba_hist_sys_time_model s,dba_hist_snapshot st where stat_name in ('DB CPU','DB time') and s.instance_number=st.instance_number and s.instance_number=1 and s.snap_id=st.snap_id ) pivot ( sum(value) for stat_name in ('DB CPU' as DB_CPU,'DB time' as DB_time) ) order by snap_id; --io set linesize 200 pagesize 200 col end_time for a20 select INSTANCE_NUMBER, SNAP_ID, to_char(END_INTERVAL_TIME,'yyyy-mm-dd hh24:mi') end_time, round(REDO_SIZE/1024/1024) REDO_SIZE_M, round(LOGICAL_READS/8/1024) LOGICAL_READS_M, round(PHYSICAL_READS/8/1024) PHYSICAL_READS_M,round(physical_writes/8/1024) physical_writes_M from ( select s.instance_number,s.snap_id,s.stat_name ,st.BEGIN_INTERVAL_TIME, st.END_INTERVAL_TIME, value-lag(value) over (partition by s.stat_name order by s.snap_id) value from dba_hist_sysstat s,dba_hist_snapshot st where stat_name in ('redo size','session logical reads','physical reads','physical writes') and s.instance_number=st.instance_number and s.instance_number=1 and s.snap_id=st.snap_id ) pivot ( sum(value) for stat_name in ('redo size' as redo_size ,'session logical reads' as logical_reads,'physical reads' as physical_reads,'physical writes' as physical_writes ) ) order by snap_id; --sql慢 --查询执行计划最慢的步骤 select count(*),sql_plan_line_id from gv$active_session_history where sql_id='&sql_id' group by sql_plan_line_id order by 2; --简易版绑执行计划 undefine bad_sqlid undefine good_hash declare v_hint SYS.SQLPROF_ATTR; v_sql_text clob; p_bad_sqlid varchar2(32) default '&&bad_sqlid'; begin select sql_text into v_sql_text from dba_hist_sqltext where sql_id = p_bad_sqlid and rownum=1; select extractvalue(value(d), '/hint') as outline_hints bulk collect into v_hint from xmltable('/*/outline_data/hint' passing (select xmltype(other_xml) as xmlval from dba_hist_sql_plan where sql_id = '&&bad_sqlid' and plan_hash_value = '&&good_hash' and other_xml is not null)) d; dbms_sqltune.import_sql_profile( v_sql_text,v_hint,'sql_profile_&&bad_sqlid',force_match=>true,replace=>true); end; / ##AddTBS --os空间和DG df -g --asm存储 set pagesize 2000 set linesize 200 col PATH for a40 col name for a20 col HEADER_STATUS for a10 select group_number, name,state,type,total_MB / 1024 total_GB,free_mb / 1024 FREE_GB,free_mb / total_MB * 100 free_per,(case when free_mb / total_mb * 100 < 15 then '*' else '' end) care from V$ASM_DISKGROUP; select GROUP_NUMBER,DISK_NUMBER,HEADER_STATUS,NAME,TOTAL_MB,FREE_MB,FREE_MB/TOTAL_MB*100 free_per,PATH,STATE from V$ASM_DISK WHERE GROUP_NUMBER!=0; --DTBS信息 SET LINESIZE 200 COL FILE_NAME FOR A80 COL TABLESPACE_NAME FOR A30 COL FILE_ID FOR 9999 select f.FILE_ID,f.TABLESPACE_NAME,t.BIGFILE,t.BLOCK_SIZE/1024 BK,f.AUTOEXTENSIBLE,f.file_name,f.BYTES/1024/1024/1024 UG,f.MAXBYTES/1024/1024/1024 TG from dba_tablespaces t,dba_data_files f where t.TABLESPACE_NAME=f.TABLESPACE_NAME and t.TABLESPACE_NAME='&TABLESPACE_NAME'; --DBsize Select DF.TOTAL/1073741824 "DataFile Size GB", LOG.TOTAL/1073741824 "Redo Log Size GB", CONTROL.TOTAL/1073741824 "Control File Size GB", (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/ 1073741824 "Total Size GB" from dual, (select sum(a.bytes) TOTAL from dba_data_files a) DF, (select sum(b.bytes) TOTAL from v$log b) LOG, (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL; --Adddiskquery --asm disk set linesize 200 pagesize 2000 col diskname for a15 col group_name for a15 col DISK_NUMBER for 999 col path for a20 col FREE_GB for 99999.99 col TOTAL_GB for 99999.99 col free_percentage for 99.99 select DISK_NUMBER,B.NAME GROUP_NAME,a.name diskname,a.free_MB/1024 FREE_GB,a.TOTAL_MB/1024 TOTAL_GB,a.free_mb/a.total_mb*100 free_percentage ,a.path,A.STATE FROM V$ASM_DISK A,V$ASM_DISKGROUP B WHERE A.GROUP_NUMBER=B.GROUP_NUMBER order by b.name,DISK_NUMBER ; --querytbs set lines200 col group_number for 99 col state for a15 col name for a20 col total_gb for 999999.99 col free_gb for 999999.99 col free_percent for 99.99 col care for a5 set linesize 131 set pagesize 200 set termout off set trimspool on set serveroutput on set lines 200 pages 300 col tablespace_name for a30 select u.*,round(p.avg_use_per_day_mb,1) avg_used_per_day_mb,round((max_size_mb-size_used_mb)/p.avg_use_per_day_mb) tbs_exhaust_days, case when u.MAX_FREE_RATE<20 or round((max_size_mb-size_used_mb)/p.avg_use_per_day_mb)<30 then '*' else null end care from (select a.tablespace_name,b.size_used_mb,a.data_size_mb, round(100-b.size_used_mb/a.data_size_mb*100) free_rate, a.max_size_mb, a.max_size_mb - b.size_used_mb free_mb, round(100-b.size_used_mb/a.max_size_mb*100) max_free_rate from (select tablespace_name,round(sum(bytes/1024/1024)) data_size_mb,round(sum(case when maxbytes>bytes then maxbytes else bytes end)/1024/1024) max_size_mb from dba_data_files group by tablespace_name) a, (select tablespace_name,round(sum(bytes/1024/1024)) size_used_mb from dba_segments group by tablespace_name) b where a.tablespace_name=b.tablespace_name order by 6 desc,4 desc) u, (select name,avg(use_per_day_mb)+0.0001 avg_use_per_day_mb from (select x.name,x.rdate,(x.used_blocks-lag(x.used_blocks) over (partition by name order by rdate))*y.block_size/1024/1024 use_per_day_mb from (select to_char(to_date(rtime,'mm/dd/yyyy hh24:mi:ss'),'yyyy-mm-dd') rdate ,name,max(tablespace_usedsize) used_blocks from dba_hist_tbspc_space_usage a,v$tablespace b where a.tablespace_id=b.ts# group by to_char(to_date(rtime,'mm/dd/yyyy hh24:mi:ss'),'yyyy-mm-dd'),name order by name ) x,dba_tablespaces y where x.name=y.tablespace_name ) where use_per_day_mb is not null group by name) p where u.TABLESPACE_NAME=p.name and u.TABLESPACE_NAME not like '%UNDO%' and u.TABLESPACE_NAME not like '%TEMP%' order by 4,7; --qtmptbs col TABLESPACE_NAME for a30 SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024/1024 AS "SPACE(G)"FROM DBA_TEMP_FILES; --stbs SELECT a.tablespace_name,used_g,max_g,(max_g-used_g) free_g from (select round(sum(decode(AUTOEXTENSIBLE,'YES',maxbytes,bytes)/1024/1024/1024),2) MAX_G,tablespace_name from dba_data_files group by tablespace_name) a,(select ROUND(sum(bytes)/1024/1024/1024,2) USED_G,tablespace_name from dba_segments group by tablespace_name) b where a.tablespace_name=b.tablespace_name and a.tablespace_name in('&_tbs'); --表空间使用率 select * from DBA_TABLESPACE_USAGE_METRICS; --sttbs SELECT TABLESPACE_NAME,BYTES/1024/1024/1024 AS "SPACE(G)"FROM DBA_TEMP_FILES T,dba_users U where T.TABLESPACE_NAME=U.TEMPORARY_TABLESPACE AND U.username in ('&USER_NAME'); --某tbs日增量 select t.name, tablespace_id, trunc(to_date(rtime,'mm/dd/yyyy hh24:mi:ss')) datetime, round(max(tablespace_usedsize*c.BLOCK_SIZE/1024/1024),0) used_size_GB, (round(max(tablespace_usedsize*c.BLOCK_SIZE/1024/1024),0)-lag(round(max(tablespace_usedsize*c.BLOCK_SIZE/1024/1024),0)) over(order by trunc(to_date(rtime,'mm/dd/yyyy hh24:mi:ss')))) inc_GB from dba_hist_tbspc_space_usage d,v$tablespace t,dba_tablespaces c where trunc(to_date(rtime,'mm/dd/yyyy hh24:mi:ss'),'HH') > trunc(sysdate - 10) and t.ts#=d.tablespace_id and t.name = c.TABLESPACE_NAME and t.name=upper('&tablespace_name') group by t.name,tablespace_id,trunc(to_date(rtime,'mm/dd/yyyy hh24:mi:ss')) order by 2,3; --TBS每小时增长情况 select a.name, b.* from v$tablespace a, (select tablespace_id, trunc(to_date(rtime,'mm/dd/yyyy hh24:mi:ss'),'HH') datetime, round(max(tablespace_usedsize * 8 / 1024),0) used_size_MB from dba_hist_tbspc_space_usage where trunc(to_date(rtime,'mm/dd/yyyy hh24:mi:ss'),'HH') > trunc(sysdate - 2) group by tablespace_id, trunc(to_date(rtime,'mm/dd/yyyy hh24:mi:ss'),'HH') order by tablespace_id, trunc(to_date(rtime,'mm/dd/yyyy hh24:mi:ss'),'HH')) b where a.ts# = b.tablespace_id and a.name=upper('&TABLESPACE'); --addbak --ALTER TABLESPACE ATS001_IDX ADD DATAFILE '+DATA' SIZE 30G; --alter database datafile &filenum AUTOEXTEND ON NEXT 1G MAXSIZE unlimited; --alter database tempfile 1 resize &sizeg; --ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE unlimited; --ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '+DATA' SIZE 1m AUTOEXTEND ON NEXT 1m MAXSIZE unlimited; --TTBS信息 SET LINESIZE 200 COL FILE_NAME FOR A80 COL TABLESPACE_NAME FOR A30 COL FILE_ID FOR 9999 select f.FILE_ID, t.TABLESPACE_NAME, t.BIGFILE, f.AUTOEXTENSIBLE, f.file_name, f.BYTES/1024/1024/1024 UG, f.MAXBYTES/1024/1024/1024 TG from dba_tablespaces t,DBA_TEMP_FILES f where t.TABLESPACE_NAME=f.TABLESPACE_NAME and t.TABLESPACE_NAME='&TABLESPACE_NAME'; --alltbsck set linesize 200 set pagesize 100 col TABLESPACE_NAME for a20 col PCT_AUTO for a10 col TSSIZE for a10 col TSSIZE_AUTO for a10 col FREE_AUTO for a10 col AVG_ADD for a10 col USE_DAY for a10 SELECT D.TABLESPACE_NAME, D.BIGFILE, lpad(TRUNC(NVL(A.TS_MB, 0))|| 'M',10,' ' ) TSSIZE, lpad(TRUNC(NVL(A.TS_MB_MAX, 0)) || 'M',10,' ' ) TSSIZE_AUTO, lpad(TRUNC(NVL(F.FREE_MB, 0) + NVL(A.TS_MB_MAX, 0) - NVL(A.TS_MB, 0)) || 'M',10,' ' ) FREE_AUTO, lpad(TRUNC((NVL(A.TS_MB, 0) - NVL(F.FREE_MB, 0)) / TRUNC(NVL(A.TS_MB_MAX, 0)) * 100) || '%',8,' ') PCT_AUTO, lpad(E.AVG_ADD_M || 'M',10,' ' ) AVG_ADD, lpad(round(TRUNC(NVL(F.FREE_MB, 0) + NVL(A.TS_MB_MAX, 0) - NVL(A.TS_MB, 0))/E.AVG_ADD_M)|| 'd',10,' ' ) USE_DAY FROM SYS.DBA_TABLESPACES D, (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TS_MB,SUM(DECODE(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) / 1024 / 1024 TS_MB_MAX FROM SYS.DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 FREE_MB FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT C.TABLESPACE_NAME TABLESPACE_NAME ,MAX(C.FREE_AUTO),DECODE(ROUND(SUM(C.USED-B.USED)/COUNT(C.USED)),0,1,ROUND(SUM(C.USED-B.USED)/COUNT(C.USED))) AS AVG_ADD_M FROM (SELECT TRUNC(GATHER_TIME) GT,TABLESPACE_NAME,USED,FREE_AUTO FROM DBAMONITOR.T_DB_TABLESPACE WHERE GATHER_TIME>TO_DATE(TO_CHAR(sysdate-10,'yyyymmdd'),'yyyymmdd')) C, (SELECT TRUNC(GATHER_TIME) GT,TABLESPACE_NAME,USED FROM DBAMONITOR.T_DB_TABLESPACE WHERE GATHER_TIME >TO_DATE(TO_CHAR(sysdate-11,'yyyymmdd'),'yyyymmdd')) B WHERE B.TABLESPACE_NAME=C.TABLESPACE_NAME and B.GT=C.GT-1 GROUP BY C.TABLESPACE_NAME) E WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+) AND D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) AND D.TABLESPACE_NAME = E.TABLESPACE_NAME(+) AND A.TABLESPACE_NAME NOT IN('SYSTEM','SYSAUX') AND A.TABLESPACE_NAME NOT LIKE 'UNDOTBS%' AND A.TABLESPACE_NAME NOT LIKE 'TEMP%' ORDER BY 8,5,3, 2; SELECT D.TABLESPACE_NAME, D.BIGFILE, lpad(TRUNC(NVL(A.TS_MB, 0))|| 'M',10,' ' ) TSSIZE, lpad(TRUNC(NVL(A.TS_MB_MAX, 0)) || 'M',10,' ' ) TSSIZE_AUTO, lpad(TRUNC(NVL(F.FREE_MB, 0) + NVL(A.TS_MB_MAX, 0) - NVL(A.TS_MB, 0)) || 'M',10,' ' ) FREE_AUTO, lpad(TRUNC((NVL(A.TS_MB, 0) - NVL(F.FREE_MB, 0)) / TRUNC(NVL(A.TS_MB_MAX, 0)) * 100) || '%',8,' ') PCT_AUTO, lpad(E.AVG_ADD_M || 'M',10,' ' ) AVG_ADD, lpad(round(TRUNC(NVL(F.FREE_MB, 0) + NVL(A.TS_MB_MAX, 0) - NVL(A.TS_MB, 0))/E.AVG_ADD_M)|| 'd',10,' ' ) USE_DAY FROM SYS.DBA_TABLESPACES D, (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TS_MB,SUM(DECODE(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES)) / 1024 / 1024 TS_MB_MAX FROM SYS.DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A, (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 FREE_MB FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT C.TABLESPACE_NAME TABLESPACE_NAME ,MAX(C.FREE_AUTO),DECODE(ROUND(SUM(C.USED-B.USED)/COUNT(C.USED)),0,1,ROUND(SUM(C.USED-B.USED)/COUNT(C.USED))) AS AVG_ADD_M FROM (SELECT TRUNC(GATHER_TIME) GT,TABLESPACE_NAME,USED,FREE_AUTO FROM DBAMONITOR.T_DB_TABLESPACE WHERE GATHER_TIME>TO_DATE(TO_CHAR(sysdate-10,'yyyymmdd'),'yyyymmdd')) C, (SELECT TRUNC(GATHER_TIME) GT,TABLESPACE_NAME,USED FROM DBAMONITOR.T_DB_TABLESPACE WHERE GATHER_TIME >TO_DATE(TO_CHAR(sysdate-11,'yyyymmdd'),'yyyymmdd')) B WHERE B.TABLESPACE_NAME=C.TABLESPACE_NAME and B.GT=C.GT-1 GROUP BY C.TABLESPACE_NAME) E WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+) AND D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) AND D.TABLESPACE_NAME = E.TABLESPACE_NAME(+) AND A.TABLESPACE_NAME NOT IN('SYSTEM','SYSAUX') AND A.TABLESPACE_NAME NOT LIKE 'UNDOTBS%' AND A.TABLESPACE_NAME NOT LIKE 'TEMP%' ORDER BY 8,5,3, 2; ##DG --lag set linesize 300 col name for a20 col value for a20 col time_computed for a30 select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag'); --v$archived_log col name for a80 select NAME,DEST_ID,THREAD#,SEQUENCE#,COMPLETION_TIME,REGISTRAR,APPLIED,DELETED from v$ARCHIVED_LOG where applied!='YES'; --v$database col NAME for a20 col OPEN_MODE for a20 col PROTECTION_MODE for a20 col DATABASE_ROLE for a20 col SWITCHOVER_STATUS for a20 select NAME,OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database; --v$managed_standby col GROUP# for a10 select PROCESS,STATUS,CLIENT_PROCESS,GROUP#,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby; --v$srchive_gap select * from v$archive_gap; --v$archive_dest_status set linesize 200 col DEST_NAME for a30 col STATUS for a15 col RECOVERY_MODE for a30 select DEST_NAME , STATUS , RECOVERY_MODE ,ERROR from v$ARCHIVE_DEST_STATUS; --dataguard_status col severity for a15 col message for a100 col timestamp for a20 select severity,error_code,to_char(timestamp,'DD-MON-YYYY HH24:MI:SS') "timestamp" , message from v$dataguard_status where dest_id=6; --p__>s recover managed standby database cancel; alter database convert to snapshot standby; alter database open; select database_role,open_mode,FLASHBACK_ON from v$database; --s__>p --startup mount force; --alter database convert to physical standby; --shutdown immediate; --startup --alter database recover managed standby database using current logfile disconnect from session; --select database_role,open_mode,FLASHBACK_ON from v$database; --开启方式 --recover managed standby database cancel; ----alter database open READ ONLY; --alter database recover managed standby database using current logfile disconnect from session; --v$log col GROUP# for 999 col FIRST_CHANGE# for 9999999999999999999999 col NEXT_CHANGE# for 9999999999999999999999 select GROUP#,THREAD#,SEQUENCE# ,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE#,NEXT_CHANGE# from v$log; ##kill --killsessionbysql_id set linesize 200 pagesize 100 col kill_txt for a80 SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''';' kill_txt FROM V$SESSION WHERE SQL_ID='&SQL_ID'; --kill spidbysql_id set linesize 200 pagesize 100 prompt prompt kill spid by event name prompt col kill_txt for a40 select /*+ rule */'kill -9 '||spid kill_txt from v$process where addr in (select paddr from v$session ss where sql_id='&sql_id'); --killspidbyevent set linesize 200 pagesize 100 prompt prompt kill spid by event name prompt col kill_txt for a40 select /*+ rule */'kill -9 '||spid kill_txt from v$process where addr in (select paddr from v$session ss where type='USER' and (ss.event='&event')); --kill -9 sid select '!ps -ef|grep '||pro.spid ||' '|| '!kill -9 '||pro.spid from v$session ses,v$process pro where ses.sid in( &sid ) and ses.paddr=pro.addr; select 'alter system kill session '''||sid ||','||serial#|| ''' immediate;' from v$session ses where ses.sid in(&sid); --kill所有外连接 ps -ef|grep oracle|grep LOCAL=NO|grep -v grep|awk {'print $2'}|xargs kill -9 --发布 col OSUSER for a15 select sid,MACHINE,OSUSER,username,EVENT,BLOCKING_INSTANCE,BLOCKING_SESSION from gv$session where MACHINE='rac11g870a' and OSUSER='oracle' and PROGRAM='sqlplus@rac11g870a (TNS V1-V3)' and username='PICCPROD'; --杀掉所有阻塞源sql col ksql for a40 set lines 199 set pages 9999 SELECT distinct 'alter system kill session '''||A.sid||','||A.serial#||',@'||A.inst_id||''' immediate;' FROM GV$SESSION A,GV$SESSION B WHERE A.SID=B.FINAL_BLOCKING_SESSION AND A.USERNAME IS NOT NULL AND A.INST_ID=B.INST_ID AND A.AUDSID<>B.AUDSID and a.program not like '%sqlplus%' ; --bysql_id select 'alter system kill session '''||a.SID||','||a.SERIAL#||''' immediate;' from v$session a,v$session_wait b where a.sid=b.sid and status = 'ACTIVE' and not ( a.type = 'BACKGROUND' and a.state='WAITING' and a.wait_class='Idle')and a.username is not null and a.SQL_ID ='&sql_id' order by a.program,a.sid,a.sql_id; --byuserkillparallel --根据用户杀并行会话 select 'alter system kill session '''||b.sid||','||b.serial#||',@'||b.inst_id||''' immediate;' ksql from gv$session a,gv$px_session bwhere a.inst_id=b.inst_id and a.sid=b.sid and a.serial#=b.serial# and b.sid=b.qcsidand A.username ='&username' order by qcsid; ##LOCK --QLOCKINFO set pagesize 900 linesize 900 col sid for 99999 col serial# for 99999 col username for a15 col SCHEMANAME for a15 col osuser for a10 col MACHINE for a15 col terminal for a10 col owner for a10 col PROGRAM for a25 col object_name for a15 col object_type for a10 select sid,serial#,username,SCHEMANAME,osuser,MACHINE,terminal,PROGRAM,owner,object_name,object_type,o.object_id,s.WAIT_TIME,l.LOCKED_MODE from dba_objects o,v$locked_object l,v$session s where o.object_id=l.object_id and s.sid=l.session_id; --gbinfo set linesize 200 pagesize 100 col sid for 9999 col object_name for a20 col sqltext for a46 col event for a30 col machine for a10 col program for a20 col username for a10 with sessions as (select /*+materialize*/ sid,blocking_session,blocking_instance,row_wait_obj#,sql_id,event,username,sql_child_number,machine from v$session ) select lpad(' ',level) || sid sid,blocking_instance b_inst,object_name,substr(sql_text,1,40) sqltext,event,username,machine from sessions s left outer join dba_objects on (object_id=row_wait_obj#) left outer join v$sql sql on (s.sql_id=sql.sql_id and s.sql_child_number=sql.child_number) where sid in (select blocking_session from sessions) or blocking_session is not null connect by prior sid=blocking_session start with blocking_session is null; --getblockingsession set linesize 300 pagesize 50 col bs for 9999 col wc for 99 col sql_id for a13 col username for a13 col event for a25 col schemaname for a17 col machine for a20 col program for a35 select inst_id,sid,serial#,username,schemaname,sql_id,machine,program,event,blocking_instance bins,blocking_session bs,wait_class# wc from gv$session where blocking_session is not null order by blocking_session,blocking_instance; --getlockbyuser&obj set linesize 200 pagesize 100 col oracle_username for a20 col os_user_name for a10 select xidusn,xidslot,xidsqn,object_id,session_id,oracle_username,os_user_name,process, decode(locked_mode,0, 'none',1, 'null',2, 'row share',3, 'row excl',4, 'share',5, 's/row excl',6, 'exclusive',locked_mode) lmode from v$locked_object where object_id in ( select object_id from dba_objects where owner=upper('&Owner') and object_name=upper('&Object_Name')) order by object_id,session_id; --getlckbysid set linesize 200 pagesize 100 col lmode for a20 col request for a10 select addr,kaddr,sid,type,id1,id2,decode(lmode,0, 'none',1, 'null',2, 'row share',3, 'row excl',4, 'share',5, 's/row excl',6, 'exclusive',lmode) lmode,decode(request,0, 'none',1, 'null',2, 'row share',3, 'row excl',4, 'share',5, 's/row excl',6, 'exclusive',request) request,ctime,block from v$lock where sid=&SESSION_ID; --v$lock&v$session col REQUEST for 9 col LMODE for 9 SELECT L.SID,L.TYPE,L.ID1,L.LMODE,L.REQUEST,L.BLOCK,S.USERNAME,S.MACHINE,S.EVENT FROM V$LOCK L,V$SESSION S WHERE L.SID = S.SID AND L.TYPE IN ('TM','TX') AND L.SID IN (SELECT SID FROM V$LOCK WHERE REQUEST > 0 UNION SELECT SID FROM V$LOCK WHERE LMODE > 0 ); --阻塞链 col SQL_ID for a20 col STATUS for a10 col INST_ID for 99 col TREE for a30 select * from (select a.inst_id, a.sid, a.serial#, a.sql_id, a.event, a.status, connect_by_isleaf as isleaf, sys_connect_by_path(a.SID||'@'||a.inst_id, ' <- ') tree, level as tree_level from gv$session a start with a.blocking_session is not null connect by (a.sid||'@'||a.inst_id) = prior (a.blocking_session||'@'||a.blocking_instance)) where isleaf = 1 order by tree_level asc; --某个会话信息 set linesize 600 pagesize 400 col username for a20 col program for a30 col machine for a20 col client_info for a20 col event for a30 col OSUSER for a10 col sql_id for a20 col status for a8 col event for a20 select inst_id,sid, serial#, username, status, osuser, machine, substr(program,1,10) program, PREV_SQL_ID, sql_id, sql_exec_start, --ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#， client_info, logon_time, round(last_call_et/60) ktm, substr(event,1,15) event,--p1,p2,p3, round(seconds_in_wait/60) min from gv$session where sid='&sid'; --实时查询锁 SELECT /*+leading(D L B E C)*/ L.SID, L.TYPE, L.ID1, L.ID2, L.LMODE, L.REQUEST, L.BLOCK, L.CTIME, B.ROW_WAIT_OBJ#, B.ROW_WAIT_FILE#, B.ROW_WAIT_BLOCK#, B.ROW_WAIT_ROW#, C.SQL_TEXT, B.SQL_HASH_VALUE, B.PREV_HASH_VALUE, C.PLAN_HASH_VALUE, B.TYPE UBTYPE, B.USERNAME, B.MACHINE, B.TERMINAL, B.PROGRAM, B.EVENT, B.OSUSER, C.SQL_ID, B.INST_ID FROM GV$LOCK L, GV$SESSION B, GV$PROCESS E, GV$SQLSTATS C WHERE (L.ID1, L.ID2, L.TYPE) IN (SELECT /*+unnest*/ D.ID1, D.ID2, D.TYPE FROM GV$LOCK D WHERE D.REQUEST > 0 AND D.TYPE NOT IN ('PR', 'KO', 'RO', 'CF','AE')) AND L.SID = B.SID AND L.INST_ID = B.INST_ID AND B.INST_ID = E.INST_ID AND B.PADDR = E.ADDR AND B.INST_ID = C.INST_ID(+) AND B.SQL_ID = C.SQL_ID(+) ORDER BY L.ID1, L.LMODE DESC, L.REQUEST DESC; --单实例lock select * from (select a.sid, a.sql_id, a.event, a.status, connect_by_isleaf as isleaf, sys_connect_by_path(SID, '<-') tree, level as tree_level from v$session a start with a.blocking_session is not null connect by nocycle a.sid = prior a.blocking_session) where isleaf = 1 order by tree_level asc; --锁源表 --阻塞源涉及的表set linesize 200col sid for 9999col serial# for 9999col osuser for a10col oracle_username for a10col os_user_name for a10col machine for a10col object_name for a30col client_info for a30select l.session_id sid,s.serial#,s.osuser,l.locked_mode,l.oracle_username,l.os_user_name,s.machine,o.object_name,s.client_infofrom v$locked_object l,all_objects o,v$session swhere l.object_id=o.object_id and l.session_id=s.sidorder by sid,s.serial#; ##query --等待事件 set pagesize 2000 set linesize 210 col sql_id for a14 col s_time for a12 col status for a6 col event format a30 col username for a14 col osuser for a8 col p1 for 9999999 col sid for 9999 col p2 for 999999 col p3 for 9999999999999 col program format a25 Col machine for a20 col serial# format 99999 select substr(a.program,1,25) program,a.SID,a.SERIAL#,a.USERNAME,substr(a.osuser,1,8) osuser,a.sql_id,substr(a.machine,1,20) machine,a.status, to_char(a.SQL_EXEC_START,'dd hh24:mi:ss') s_time,(case when a.STATE='WAITING' then a.event else 'CPU' end) event from gv$session a,gv$session_wait b where a.sid=b.sid and status = 'ACTIVE' and not ( a.type = 'BACKGROUND' and a.state='WAITING' and a.wait_class='Idle')and a.username is not null and a.SQL_ID is not null order by a.program,a.sid,a.sql_id; --v$mystat select distinct SID from v$mystat; ##querytab --gettabpartitioninfo set pagesize 50 set linesize 200 col high_value for a20 col PARTITION_NAME for a30 col TABLESPACE_NAME for a10 SELECT COMPOSITE,PARTITION_NAME,SUBPARTITION_COUNT,HIGH_VALUE,HIGH_VALUE_LENGTH,PARTITION_POSITION,TABLESPACE_NAME,LOGGING,COMPRESSION,COMPRESS_FOR,NUM_ROWS,LAST_ANALYZED,GLOBAL_STATS,INTERVAL,SEGMENT_CREATED,USER_STATS FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME=UPPER('&TABLE_NAME') AND TABLE_OWNER=UPPER('&OWNER'); --getdbsize Select DF.TOTAL/1073741824 "DataFile Size GB", LOG.TOTAL/1073741824 "Redo Log Size GB", CONTROL.TOTAL/1073741824 "Control File Size GB", (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/ 1073741824 "Total Size GB" from dual, (select sum(a.bytes) TOTAL from dba_data_files a) DF, (select sum(b.bytes) TOTAL from v$log b) LOG, (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL; --db&rate select GATHER_TIME,sum(USED/1024) SUM_G from DBAMONITOR.T_DB_TABLESPACE where GATHER_TIME >sysdate-31 and GATHER_TIME <sysdate-0 group by GATHER_TIME; --gettabmodifyinfo col TABLE_OWNER for a10 col TABLE_NAME for a30 set linesize 400 select distinct TABLE_OWNER,TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP,TRUNCATED,DROP_SEGMENTS from dba_tab_modifications where table_name=UPPER('&tablename') and table_owner=UPPER('&owner') order by TIMESTAMP; --listtabsize col segment_name for a40 SELECT s.SEGMENT_NAME,SUM(s.BYTES/1024/1024/1024),t.PARTITIONED,t.COMPRESSION FROM DBA_SEGMENTS s,dba_tables t WHERE s.segment_name=t.table_name and s.OWNER='ODSUSER' and s.tablespace_name='ODSUSER_TBS' group by s.segment_name,t.PARTITIONED,t.COMPRESSION order by 2 ; --log/day alter session set nls_date_format='yyyy-mm-dd'; select dest_id, trunc(completion_time), count(*) cnt_nu, trunc(sum(blocks * block_size)/1024/1024/1024 ,2) G from v$archived_Log group by trunc(completion_time),dest_id order by trunc(completion_time); --rac/log/hour alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; select trunc(completion_time,'HH'),dest_id,thread#,count(*),trunc(sum(block_size*blocks)/1024/1024/1024,2) G from v$archived_Log where dest_id=1 group by trunc(completion_time,'HH'),thread#,dest_id order by 1,2,3; --v$recovery_file_dest col name for a10 col used_per for a10 select name,trunc(space_limit/1024/1024/1024,2) limit_G, trunc(SPACE_USED/1024/1024/1024,2) USED_G, trunc(100*space_used/space_limit,2)||'%' used_per, (case when trunc(100*space_used/space_limit,2) > 85 then '*' else '' end ) care from V$RECOVERY_FILE_DEST; --总归档 col date1 for a30 col cnt for 999999999 col GB for 99999999999999 Select to_char(completion_time,'yyyy-mm-dd') as date1,count(0) as cnt,round(sum((blocks *block_size)/1024/1024/1024)) as GB from v$archived_log group by to_char(completion_time,'yyyy-mm-dd') order by date1 desc; ##sql --Getsqlbysid SELECT sql_id, sql_text FROM v$sqltext a WHERE a.hash_value = (SELECT sql_hash_value FROM v$session b WHERE b.SID = '&sid')ORDER BY piece ASC; --getsqlbysqlsid set long 88888 col sql_fulltext for a8888 set lines 8888 select sql_fulltext from v$sql where sql_id = '&sqlid' and rownum = 1; --getsqlbypid Select s.Sid, s.Status, q.Sql_Text From V$session s, V$sqltext q Where s.Sql_Hash_Value = q.Hash_Value And s.Sql_Address = q.Address And s.Sid In (Select s.Sid From V$session s, V$process p Where s.Paddr = p.Addr And p.Spid = '&pid' ) Order By q.Piece; --getfullqqltextbysqlid set pagesize 2000 select dbms_lob.substr(SQL_FULLTEXT) from v$sqlarea where sql_id='&sql_id'; --getDDL --PROCEDURE ddl --select text from all_source where name = '&PROCEDURE_NAME' and OWNER='&own' order by line; set long 10000 pagesize 100 select dbms_metadata.get_ddl('&OBJECT_TYPE','&OBJECT_NAME','&OBJECT_OWNER') ddl_text from dual; --查询实例绑定变量 col sql_id for a15 col name for a10 col datatype_string for a13 col last_captured for a24 col value_string for a30 select instance_number,sql_id,name, datatype_string,last_captured,value_string from dba_hist_sqlbind where sql_id='&sql_id' and rownum<10 order by LAST_CAPTURED,POSITION; --hist_sql_stat(执行计划） set lines 155 col execs for 999,999,999 col avg_etime for 999,999.999 col avg_lio for 999,999,999.9 col begin_interval_time for a30 col node for 99999 break on plan_hash_value on startup_time skip 1 select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs, (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime, (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = nvl('&sql_id','4dqs2k5tynk61') and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 order by 1, 2, 3; --sql/exec/hh select /*parallel 8*/ trunc(SAMPLE_TIME,'HH'),count(*) from dba_hist_active_sess_history where sample_time>= to_date('&yyyymmdd','YYYYMMDD') and sql_id='&sqlid' group by trunc(SAMPLE_TIME,'HH') order by 1; --sql col sql_id for a20 col date_time for a20 SELECT distinct s.snap_id ,SQL.sql_id,sql.INSTANCE_NUMBER, to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24mi')|| to_char(s.END_INTERVAL_TIME,'_hh24mi') Date_Time, SQL.executions_delta, round(SQL.elapsed_time_delta/1000000,3) elapsed_time, round((SQL.elapsed_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta),3) avg_etime, round((SQL.cpu_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta),3) avg_cputime , round(SQL.rows_processed_delta/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta),0) avg_row, round(SQL.buffer_gets_delta/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta),0) avg_lio, round(SQL.DISK_READS_DELTA/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta),0) avg_pio FROM dba_hist_sqlstat SQL, dba_hist_snapshot s, dba_hist_sqltext st WHERE SQL.dbid =(select dbid from v$database) --and SQL.instance_number =(select instance_number from v$instance) and s.snap_id = SQL.snap_id and sql.sql_id=st.SQL_ID(+) and SQL.sql_id='&sqlid' order by s.snap_id desc; ##TEMP --GETtempusagesession col username for a15 col machine for a32 col program for a20 col tablespace for a15 col space for a15 col sql_id for a16 col space for a10 ---超过100M temp的sess --- SELECT distinct se.username, se.SID, se.serial# , se.machine, se.program, su.TABLESPACE,su.segtype, TRUNC(su.BLOCKS * 8 / 1024) || 'M' SPACE, s.sql_id,s.SQL_TEXT from gv$session se, gv$sort_usage su,gv$sql s WHERE se.saddr = su.session_addr and su.sqladdr=s.ADDRESS and su.SQLHASH=s.HASH_VALUE and su.SQL_ID = s.SQL_ID AND su.BLOCKS > 100 * 1024 / 8 order by se.username; --querytempusage select upper(d.tablespace_name), (nvl(d.total_Mb,0)-nvl(v.total_used_Mb,0)) free, round(nvl(v.total_used_Mb,0)/ nvl(d.total_Mb,0) * 100, 2) rate from ( select tablespace_name, nvl(sum(used_blocks), 0)*(select value from v$parameter where name='db_block_size')/(1024*1024) total_used_Mb from v$sort_segment group by tablespace_name) v, (select tablespace_name,sum(blocks)*(select value from v$parameter where name='db_block_size')/(1024*1024) total_Mb from dba_temp_files group by tablespace_name) d where d.tablespace_name = v.tablespace_name(+); --querytempsegsess Set linesize 200 pagesize 200 Col username for a10 Col machine for a24 Col tablespace for a10 Col contents for a10 Col segtype for a10 SELECT se.username username, se.SID sid, se.serial# serial#, se.status status, se.sql_id, se.prev_sql_id, se.machine machine, su.TABLESPACE tablespace, su.segtype, su.CONTENTS CONTENTS FROM v$session se, v$tempseg_usage su WHERE se.saddr=su.session_addr; --tempu --谁占用了temp表空间 select t.BLOCKS*16/1024/1024, s.USERNAME, s.SCHEMANAME, t.TABLESPACE, t.SEGTYPE, t.EXTENTS, s.PROGRAM, s.OSUSER, s.TERMINAL, s.sid, s.SERIAL# from V$sort_usage t,v$session s where t.session_addr=s.saddr; --tmp_sqlid --还可查到具体sql select sql.sql_id, t.BLOCKS*16/1024/1024, s.USERNAME, s.SCHEMANAME, t.TABLESPACE, t.SEGTYPE, t.EXTENTS, s.PROGRAM, s.OSUSER, s.TERMINAL, s.sid, s.SERIAL#, sql.sql_text from V$sort_usage t,v$session s,v$sql sql where t.session_addr=s.saddr and t.sqladdr=sql.address and t.sqlhash=sql.hash_value; #TX_source --gv$session select INST_ID,sid,serial#,USERNAME,STATUS,MACHINE,SQL_ID,EVENT,(sysdate-LOGON_TIME)*86400 as "s",LAST_CALL_ET from gv$session where status='ACTIVE' and username is not null; --blocking_session select sql_id,EVENT,BLOCKING_INSTANCE,BLOCKING_SESSION,count(*)from gv$session where blocking_session is not null group by sql_id,EVENT,BLOCKING_INSTANCE,BLOCKING_SESSION; --gv$open_cursor select sql_id,sid,SQL_TEXT from gv$open_cursor where sid='&sid' and INST_ID='&inst_id' and (UPPER(SQL_TEXT) LIKE '%UPDATE%' OR UPPER(SQL_TEXT) like '%DELETE%'); ##undo --getrollbackuseundo select b.name "UNDO Segment Name", b.inst# "Instance ID", b.status$ STATUS, a.ktuxesiz "UNDO Blocks", a.ktuxeusn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq from x$ktuxe a, undo$ b where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%' and a.ktuxeusn = b.us#; --getundousage SELECT TABLESPACE_NAME,STATUS,TRUNC(SUM(BLOCKS) * 8 / 1024) AS "Size M",COUNT(*) Undo_Extent_Num FROM DBA_UNDO_EXTENTS GROUP BY TABLESPACE_NAME, STATUS order by 1,2,3; --getconsumeundosession set pagesize 50 linesize 210 segment_name format a20 col username for a10 col sid for 9999 Col program format a15 Col machine for a10 col serial# format 99999 Col sql_id for a15 col segment_name for a25 select s.sid, s.serial#, s.username, u.segment_name, count(u.extent_id) "extent count", t.used_ublk, t.used_urec,s.machine, s.program,s.sql_id from v$session s, v$transaction t, dba_undo_extents u where s.taddr = t.addr and u.segment_name like '_SYSSMU'||t.xidusn||'_%$' and u.status = 'ACTIVE' group by s.sid, s.serial#, s.username, u.segment_name, t.used_ublk, t.used_urec,s.machine, s.program,s.sql_id order by t.used_ublk desc, t.used_urec desc, s.sid, s.serial#, s.username, s.machine, s.program,s.sql_id; --rolltime select undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",decode(cputime,0,'unknown',to_char(sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss')) "Estimated time to complete",to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from v$fast_start_transactions; --transcation_size select s.inst_id, s.sid, s.SERIAL#, s.username, t.USED_UBLK*8/1024 M from gv$transaction t, gv$session s where s.saddr = t.ses_addr order by t.USED_UBLK desc; --undou --查谁占用undo表空间 select r.name undo_name, rssize/1024/1024/1024 "rssize(g)", s.sid, s.serial#, s.username, s.status, s.sql_hash_value, s.sql_address, s.machine, s.module, substr(s.program,1,78), r.usn, hwmsize/1024/1024/1024, shrinks, xacts from sys.v_$session s, sys.v_$transaction t, sys.v_$rollname r, v$rollstat rs where t.addr=s.taddr and t.xidusn=r.usn and r.usn=rs.usn order by rssize desc; ##zyt --AIX&C&M #操作系统物理CPU prtconf|grep Processors #操作系统逻辑cpu个数 pmcycles -m | wc -l #操作系统内存 prtconf|grep Memory #主机名 hostname --LINUX #操作系统物理CPU cat /proc/cpuinfo |grep "physical id"|sort |uniq|wc -l #操作系统逻辑cpu个数 cat /proc/cpuinfo |grep "processor"|wc -l #操作系统核数 cat /proc/cpuinfo |grep "cores"|uniq #操作系统内存 free -g #主机名 hostname #系统版本 cat /etc/redhat-release telnet 10.135.100.102 22 df -g --CPU cat /proc/cpuinfo |grep "physical id"|sort |uniq|wc -l cat /proc/cpuinfo |grep "processor"|wc -l cat /proc/cpuinfo |grep "cores"|uniq --失效包编译不过去查询 select E.*,DS.TEXT from dba_errors E,DBA_SOURCE DS where E.owner='MAIN_TEST' AND E.NAME IN (SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='PACKAGE BODY' AND OWNER='MAIN_TEST' AND STATUS='INVALID' AND OBJECT_NAME IS NOT NULL) AND E.OWNER=DS.OWNER(+) AND E.NAME=DS.NAME(+) AND E.LINE=DS.LINE(+) ORDER BY E.OWNER,E.NAME,E.TYPE,E.SEQUENCE,E.LINE,E.POSITION ; ##zytsql --参数相关 --查询结果 ISSYS_MODIFIABLE 的值为 IMMEDIATE，表明该参数是动态参数，可以在线修改立即生效（不加scope默认的是 both） --查询结果中 ISSYS_MODIFIABLE 的值为 FALSE，表明该参数是静态参数，通过 spfile 修改后需要重启数据库参数才生效（静态参数 必须指定scope） --查询结果中 ISSYS_MODIFIABLE 的值为 DERERRED，表明该参数是延迟参数，当前会话不生效，后续的会话有效（必须加上scope=spfile或者加上derferred） --MEMORY_TARGET ：操作系统上 Oracle 所能使用的最大内存值。动态参数,MEMORY_TARGET 是PAG 和SGA的总和。memory_target 为 0 是手动管理，非0为自动管理。 --动态参数,以cpu_count为例：alter system set cpu_count=xxx; --静态参数,以db_file_name_convert为例：alter system set db_file_name_convert='+DATA_RG/p8701rac/datafile','/piccdg/oradata/dgsmis' scope=spfile; select name,display_value,issys_modifiable,isinstance_modifiable from v$parameter where name='&prmt_value'; --查看隐含参数 set linesize 200 col name for a40 col value for a24 col description for a70 select a.ksppinm name,b.ksppstvl value,a.ksppdesc description from x$ksppi a,x$ksppcv b where a.inst_id = USERENV ('Instance') and b.inst_id = USERENV ('Instance') and a.indx = b.indx and upper(a.ksppinm) LIKE upper('%¶%') order by name / --用户权限 select * from DBA_SYS_PRIVS where GRANTEE='&USER'; --用户权限比对 select owner,table_name,grantor,privilege from dba_tab_privs p where p.grantee='&USER1'minus select owner,table_name,grantor,privilege from dba_tab_privs p where p.grantee='&USER2'; --dba_tab_privs --查看某张表的用户权限，要记得owner，字段 col GRANTEE for a20 col PRIVILEGE for a20 col GRANTOR for a20 select * from dba_tab_privs where TABLE_NAME='&TABLE_NAME'; --ind --pga占用最多的进程--登录时间最长的session（同时获取到spid，方便再主机层面ps -ef|grep spid 来查看）--逻辑读最多--物理读最多--执行次数最多--解析次数最多--求disk sort严重的sql--3.检查是否有过分提交的语句--索引结果情况（高度，重复度，并行度，叶子高度，聚合银子，记录数，状态，最近分析时间）--4.外键未建索引将引发思索及影响表连接性能。 --v$asm_disk --拼写加盘步骤--select ' alter diskgroup ' ||d.NAME|| ' add disk ' ||a.PATH|| ' rebalance power 8; ' from v$ASM_DISKGROUP d,V$ASM_DISK awhere d.NAME NOT IN ('CRS') and rownum < 2; ##临时表空间 --v$ASH SELECT sum(TEMP_SPACE_ALLOCATED/1024/1024/1024), A.SAMPLE_TIME,SQL_ID FROM V$ACTIVE_SESSION_HISTORY A WHERE A.SAMPLE_TIME BETWEEN TO_DATE('2018-00-00 13:20:00','YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2018-00-00 14:10:00','YYYY-MM-DD HH24:MI:SS') GROUP BY A.SAMPLE_TIME,SQL_ID having sum(TEMP_SPACE_ALLOCATED/1024/1024/1024)>100 ORDER BY 1; --V$S_WAIT set pagesize 2000 set linesize 210 col sql_id for a14 col s_time for a12 col status for a6 col event format a30 col username for a14 col osuser for a8 col p1 for 9999999 col sid for 9999 col p2 for 999999 col p3 for 9999999999999 col program format a25 Col machine for a20 col serial# format 99999 select substr(b.program,1,25) program ,b.sid,b.serial#,b.username, substr(b.osuser,1,8) osuser,b.sql_id,substr(b.machine,1,20) machine,b.status,to_char(b.SQL_EXEC_START,'dd hh24:mi:ss') s_time , (case when b.STATE='WAITING' then b.event else 'CPU' end) event from v$session_wait a,v$session b where a.sid=b.sid and status = 'ACTIVE' and not ( b.type = 'BACKGROUND' and b.state='WAITING' and b.wait_class='Idle' ) and b.username is not null Order by program,sid,sql_id; --V$SQL select sql_id,sql_fulltext from v$sql where sql_id='&SQL_ID'; select sql_id,SQL_TEXT from DBA_HIST_SQLTEXT where sql_id='&SQL_ID'; --DBA_JOBS select JOB,LOG_USER,SCHEMA_USER,what from DBA_JOBS where JOB='&JOBid'; --DBA_tables_an SELECT OWNER,TABLE_NAME,LAST_ANALYZED FROM DBA_TABLES where OWNER in ('&OWNER') and TABLE_NAME in ('&TAB_NAME'); --活跃会话数 select USERNAME,count(*) from gv$session where USERNAME is not null and STATUS='ACTIVE' GROUP BY USERNAME; ##优化 --planchange set lines 155 col execs for 999,999,999 col avg_etime for 999,999.999 col avg_lio for 999,999,999.9 col stime for a30 col node for 99999 col sql_id for a13 --break on plan_hash_value on startup_time skip 1 select ss.snap_id, ss.instance_number node, to_char(begin_interval_time,'yy-mm-dd hh24:mi:ss') stime, sql_id, plan_hash_value, nvl(executions_delta,0) execs, round((elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000,3) avg_etime_s, round((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta))) avg_lio from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS where sql_id = nvl('&sql_id','4dqs2k5tynk61') and ss.snap_id = S.snap_id and ss.instance_number = S.instance_number and executions_delta > 0 order by 1, 2, 3; --sql性能 col BEGIN_INTERVAL_TIME for a25 col END_INTERVAL_TIME for a25 col instance_number for 99 set linesize 1000 pagesize 999 select a.snap_id, a.sql_id, a.instance_number, b.BEGIN_INTERVAL_TIME, b.END_INTERVAL_TIME , a.EXECUTIONS_DELTA, a.plan_hash_value, a.CPU_TIME_DELTA from wrh$_sqlstat a, wrm$_snapshot b where a.snap_id = b.snap_id and a.instance_number = b.instance_number and a.sql_id = '&sql_id' order by 4, 1, 3; --sql10 --sosi.txt --一条sql的执行计划慢在哪里 --根据V$ACTIVE_SESSION_HISTORY视图中的SQL_PLAN_LINE_ID、COUNT(SQL_PLAN_LINE_ID)确定执行计划中一条sql中的性能过程的SQL_PLAN_LINE_ID;--select SQL_PLAN_LINE_ID,count(SQL_PLAN_LINE_ID) from V$ACTIVE_SESSION_HISTORY where SQL_ID='&sql_id' group by SQL_PLAN_LINE_ID;--select * from table(dbms_xplan.display_cursor('&sql_id',0,'advanced')); --整天抓sql --1.异常等待事件 --2.等待事件涉及sql select to_char(sample_time,'yyyymmdd-hh24'),count(*),sql_id,sql_plan_line_id from gv$active_session_history where event like '&event' group by to_char(sample_time,'yyyymmdd-hh24'),sql_id,sql_plan_line_id having count(*) > 100 order by 2 desc; 抓topsql col sql_text format a25 select a.sql_id, a.plan_hash_value, round(elapsed_time_delta /decode(executions_delta, 0, 1, executions_delta) / 1000000,2) per_time_s, executions_delta executions, round(buffer_gets_delta * 8 / 1024 /1024/decode(executions_delta, 0, 1, executions_delta)) per_gets_gb, to_char(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') begin_time, to_char(b.end_interval_time, 'yyyy-mm-dd hh24:mi:ss') end_time, substr(c.sql_text,1,25) sql_text from dba_hist_sqlstat a, dba_hist_snapshot b, dba_hist_sqltext c where a.snap_id = b.snap_id and a.instance_number=b.instance_number and a.dbid = c.dbid and a.sql_id = c.sql_id and a.parsing_schema_name<>'SYS' and a.instance_number=1 and b.begin_interval_time>to_date('2020-12-29 09:00:00','yyyy-mm-dd hh24:mi:ss') and round(elapsed_time_delta /decode(executions_delta, 0, 1, executions_delta) / 1000000,2)>3 and a.module='JDBC Thin Client' order by 3 desc; --数据库里面的procedure和package查找问题和进行优化的 select sq.sql_id, sq.child_number, sq.executions, round(elapsed_time / decode(sq.executions, 0, 1, sq.executions) / 1000000,2) per_time_s, round(buffer_gets / decode(sq.executions, 0, 1, sq.executions) * (select value from v$parameter where name = 'db_block_size') / 1024 / 1024) per_gets_mb, round(rows_processed / decode(sq.executions, 0, 1, sq.executions)) rows_per_exe, to_char(last_active_time, 'yyyy-mm-dd hh24:mi:ss') last_active_time from v$sql sq where round(elapsed_time / decode(sq.executions, 0, 1, sq.executions) / 1000000,2) > 3 and (sql_id, child_number) in (select sql_id, sql_child_number from v$active_session_history ash, dba_objects b where sample_time >= to_date('2020-12-28 09:00:00', 'yyyy-mm-dd hh24:mi:ss') and sql_id is not null and coalesce(plsql_object_id, plsql_entry_object_id) = b.object_id and b.object_name = '&大写的包名') order by 4 desc; ##巡检 --db_time set linesize 200 pagesize 200 col DB_CPU_s for 999999 col DB_TIME_s for 99999 col end_time for a20 select INSTANCE_NUMBER, SNAP_ID,to_char(END_INTERVAL_TIME,'yyyy-mm-dd hh24:mi') end_time, round(DB_CPU/60/1000/1000) DB_CPU_s , round(DB_TIME/60/1000/1000) DB_TIME_s from ( select s.instance_number,s.snap_id,s.stat_name ,st.BEGIN_INTERVAL_TIME, st.END_INTERVAL_TIME, value-lag(value) over (partition by s.stat_name order by s.snap_id) value from dba_hist_sys_time_model s,dba_hist_snapshot st where stat_name in ('DB CPU','DB time') and s.instance_number=st.instance_number and s.instance_number=1 and s.snap_id=st.snap_id ) pivot ( sum(value) for stat_name in ('DB CPU' as DB_CPU,'DB time' as DB_time) ) order by snap_id; --io set linesize 200 pagesize 200 col end_time for a20 select INSTANCE_NUMBER, SNAP_ID, to_char(END_INTERVAL_TIME,'yyyy-mm-dd hh24:mi') end_time, round(REDO_SIZE/1024/1024) REDO_SIZE_M, round(LOGICAL_READS/8/1024) LOGICAL_READS_M, round(PHYSICAL_READS/8/1024) PHYSICAL_READS_M,round(physical_writes/8/1024) physical_writes_M from ( select s.instance_number,s.snap_id,s.stat_name ,st.BEGIN_INTERVAL_TIME, st.END_INTERVAL_TIME, value-lag(value) over (partition by s.stat_name order by s.snap_id) value from dba_hist_sysstat s,dba_hist_snapshot st where stat_name in ('redo size','session logical reads','physical reads','physical writes') and s.instance_number=st.instance_number and s.instance_number=1 and s.snap_id=st.snap_id ) pivot ( sum(value) for stat_name in ('redo size' as redo_size ,'session logical reads' as logical_reads,'physical reads' as physical_reads,'physical writes' as physical_writes ) ) order by snap_id; ##开门红 --dbtime WITH timemodel_tmp AS (SELECT sn.begin_interval_time, sn.end_interval_time, ss.stat_name, i.INSTANCE_NAME, os.value cpu_cnt, ss.VALUE e_value, ss.snap_id e_snap_id, i.instance_number, LAG (ss.VALUE, 1) OVER (partition by ss.instance_number, ss.stat_name ORDER BY ss.snap_id) b_value, LAG (ss.snap_id, 1) OVER (partition by ss.instance_number, ss.stat_name ORDER BY ss.snap_id) b_snap_id FROM dba_hist_sys_time_model ss, dba_hist_snapshot sn, DBA_HIST_OSSTAT os, (select dbid, instance_number, INSTANCE_NAME, row_number()over(partition by dbid, instance_number order by STARTUP_TIME desc) rn from DBA_HIST_DATABASE_INSTANCE) i WHERE sn.end_interval_time>=trunc(sysdate - 0) --to_date('2016-11-30', 'yyyy-mm-dd') -- and sn.begin_interval_time<=to_date('2016-12-01', 'yyyy-mm-dd') AND ss.snap_id = sn.snap_id AND ss.dbid = sn.dbid AND ss.snap_id = os.snap_id AND ss.dbid = os.dbid AND ss.instance_number = sn.instance_number AND ss.instance_number = os.instance_number AND ss.dbid = (SELECT dbid FROM v$database) AND ss.instance_number = i.instance_number AND ss.dbid = i.dbid AND i.rn=1 AND ss.stat_name in ( 'DB time', 'DB CPU') AND os.stat_name = 'NUM_CPUS'), timemodel as (select begin_interval_time, end_interval_time, INSTANCE_NAME, cpu_cnt, instance_number, max(case stat_name when 'DB time' then e_value end) dt_e_value, max(case stat_name when 'DB CPU' then e_value end) ct_e_value, max(case stat_name when 'DB time' then e_snap_id end) e_snap_id, --max(case stat_name when 'DB CPU' then e_snap_id end) ct_e_snap_id, max(case stat_name when 'DB time' then b_value end) dt_b_value, max(case stat_name when 'DB CPU' then b_value end) ct_b_value, max(case stat_name when 'DB time' then b_snap_id end) b_snap_id --,max(case stat_name when 'DB CPU' then b_snap_id end) ct_b_snap_id from timemodel_tmp group by begin_interval_time, end_interval_time, INSTANCE_NAME, cpu_cnt, instance_number), candi_tmp as ( SELECT TO_CHAR (BEGIN_INTERVAL_TIME, 'mm-dd hh24:mi') || TO_CHAR (END_INTERVAL_TIME, '~hh24:mi') date_time, ROUND ((dt_e_value - NVL (dt_b_value, 0))/1000000 / ((cast (end_interval_time as date) - cast(begin_interval_time as date))*24*60*60), 2) db_times, ROUND ((dt_e_value - NVL (dt_b_value, 0))/1000000 / ((cast (end_interval_time as date) - cast(begin_interval_time as date))*24*60*60) / cpu_cnt, 2) db_times_per_cpu, ROUND ((ct_e_value - NVL (ct_b_value, 0))/1000000 / ((cast (end_interval_time as date) - cast(begin_interval_time as date))*24*60*60), 2) cpu_times, ROUND ((ct_e_value - NVL (ct_b_value, 0))/1000000 / ((cast (end_interval_time as date) - cast(begin_interval_time as date))*24*60*60) / cpu_cnt, 2) cpu_times_per_cpu, b_snap_id, e_snap_id, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME, INSTANCE_NAME, instance_number FROM timemodel WHERE (dt_e_value - NVL (dt_b_value, 0)) > 0 AND NVL (dt_b_value, 0) > 0), candi as (select t.*, row_number()over(partition by instance_number order by BEGIN_INTERVAL_TIME desc) rn from candi_tmp t) select max(dt) from ( select date_time, instance_number inst, db_times dt, db_times_per_cpu dtpc, cpu_times ct, cpu_times_per_cpu ctpc, b_snap_id, e_snap_id, 'awr_'||INSTANCE_NAME||'_'||TO_CHAR (BEGIN_INTERVAL_TIME, 'yyyy-mm-dd-hh24mi')||TO_CHAR (END_INTERVAL_TIME, '~hh24mi')||'_'||to_char(db_times)||'_'||to_char(db_times_per_cpu)||'_'||to_char(cpu_times_per_cpu)||'.html' awrrpt_name from candi c --where rn<=60 order by instance_number, BEGIN_INTERVAL_TIME desc) ; --io WITH si AS (SELECT snap_id, instance_number, to_char(begin_interval_time, 'yyyy-mm-dd hh24:mi') || '~' || to_char(end_interval_time, 'hh24:mi') interval_time, (CAST(end_interval_time AS DATE) - CAST(begin_interval_time AS DATE)) * 86400 els_sec FROM dba_hist_snapshot WHERE instance_number = (SELECT instance_number FROM v$instance) AND end_interval_time >= trunc(SYSDATE)), a AS (SELECT instance_number, MIN(snap_id) min_snap, MAX(snap_id) max_snap FROM si GROUP BY instance_number), v AS (SELECT s.instance_number, s.snap_id, s.stat_name, s.value, round(s.value - lag(s.value, 1, NULL) over(PARTITION BY s.stat_name ORDER BY s.snap_id), 1) delta FROM dba_hist_sysstat s, a WHERE s.stat_name IN ('session logical reads', 'physical read bytes', 'physical write bytes', 'redo size') AND s.instance_number = a.instance_number AND s.snap_id BETWEEN a.min_snap - 1 AND a.max_snap), b AS (SELECT v.snap_id, si.interval_time, si.els_sec, SUM(decode(v.stat_name, 'redo size', v.delta, 0)) redo_size, SUM(decode(v.stat_name, 'session logical reads', v.delta, 0)) logical_read, SUM(decode(v.stat_name, 'physical read bytes', v.delta, 0)) physical_read, SUM(decode(v.stat_name, 'physical write bytes', v.delta, 0)) physical_write FROM si, v WHERE si.snap_id = v.snap_id GROUP BY v.snap_id, si.interval_time, si.els_sec ORDER BY 1) select max(physical_read),max(physical_write) from( SELECT snap_id, round(redo_size / els_sec, 1) redo_size, round(logical_read / els_sec, 1) logical_read, round(physical_read / els_sec / POWER(2, 20), 1) physical_read, round(physical_write / els_sec / POWER(2, 20), 1) physical_write FROM b); --session select max(AVG_SESS_NUM_S) from( select sample_id ,count(1) AVG_SESS_NUM_S from V$ACTIVE_SESSION_HISTORY where sample_time>=trunc(sysdate) group by sample_id ); --cpu su - oracle sqlplus / as sysdba show parameter cpu ##查询进度 --querylongops set linesize 200 pagesize 50 col opname for a40 col target for a30 col message for a40 select sid,opname,target,sofar,totalwork,round(sofar/totalwork,4)*100 per,message FROM V$SESSION_LONGOPS where (sid,serial#) in (select sid,serial# from v$session where type='USER') and sofar<>totalwork order by sid,serial#; --per set linesize 2000 set pagesize 200 col SID for 9999 col SERIAL# for 9999999999 col OPNAME for a30 col TARGET for a10 col SOFAR for 9999999999 col TOTALWORK for 99999999999 select SID,SERIAL#,OPNAME,START_TIME,LAST_UPDATE_TIME, round(sofar/totalwork,4)*100 per, round(ELAPSED_SECONDS/3600,4) ELA_HH,--MESSAGE round(TIME_REMAINING/3600,4) re_hh from V$SESSION_LONGOPS where TOTALWORK !=0 AND sofar!=totalwork; --tempsc select SID,SERIAL#,OPNAME,START_TIME,LAST_UPDATE_TIME, round(sofar/totalwork,4)*100 per, round(ELAPSED_SECONDS/3600,4) ELA_HH,--MESSAGE round(TIME_REMAINING/3600,4) re_hh from V$SESSION_LONGOPS where TOTALWORK !=0 AND sofar!=totalwork and OPNAME like '%RMAN%'; ft_id SELECT /*+ rule */ 'kill -9 ' || c.SPID v_sql1, 'alter system kill session ''' || b.sid || ',' || b.serial# || ''';' sql2, B.SQL_ID, DECODE(A.REQUEST, 0, 'Holder', 'Waiter') LOCKROLE, A.INST_ID, A.SID SESS, A.ID1, A.ID2, A.LMODE, A.REQUEST, A.TYPE, B.EVENT, A.CTIME, B.MACHINE, B.PROGRAM, B.USERNAME FROM GV$LOCK A, GV$SESSION B, gv$process C WHERE (A.ID1, A.ID2, A.TYPE, A.INST_ID) IN (SELECT D.ID1, D.ID2, D.TYPE, D.INST_ID FROM GV$LOCK D WHERE D.REQUEST > 0) AND A.SID = B.SID AND A.INST_ID = B.INST_ID AND A.INST_ID = C.INST_ID AND B.PADDR = C.ADDR AND a.TYPE = 'TM' AND a.REQUEST > 3 -- AND DECODE(A.REQUEST, 0, 'Holder', 'Waiter')='Waiter' ORDER BY A.ID1, A.REQUEST;\n ----ID1是子表 --disable 约束 ---写父表---\nselect 'alter table '||table_name||' disable novalidate constraint '||\nconstraint_name||';' disable_cons from dba_constraints \nwhere R_CONSTRAINT_NAME in (select constraint_name from dba_constraints where table_name='&fubiao'); --ash获得子表 select ash.SAMPLE_TIME,\n ash.EVENT,\n ash.SESSION_ID,\n ash.BLOCKING_SESSION,\n ash.P1TEXT,\n ash.P1,\n ash.P2TEXT,\n ash.p2,\n ash.p3text,\n ash.p3,\n ash.SESSION_STATE,\n ash.SQL_OPNAME,\n ash.SQL_ID\n from v$active_session_history ash\n where ash.SAMPLE_TIME >\n to_date('&20180306170000', 'yyyymmddHH24MISS')\n and ash.SAMPLE_TIME <\n to_date('&20180306181000', 'yyyymmddHH24MISS')\n and ash.WAIT_CLASS <> 'Idle'\n and ash.EVENT like 'enq: TM - contention'\n order by sample_time desc; --FK&IND(STAB-->FTAB) col cindex for a80 \n col stab for a20 \n col ftab for a20 \n col SONCOLUMN for a20 \n col CINDEX for a80 \n set lines 400 \n SET pages 400 \n COL SFK FOR A20 \n COL FPK FOR A20 \n COL disable_cons FOR A60 \n with \n sont as ( \n select OWNER,table_name,constraint_name,r_constraint_name \n from dba_constraints \n where table_name='&son_tab' \n ), \n ftab as ( \n select DC.OWNER,dc.table_name,dc.constraint_name \n from dba_constraints dc,sont \n where dc.constraint_name=sont.r_constraint_name) \n select info.*,'alter table '||info.stab||' disable novalidate constraint '|| \n info.SFK||';' disable_cons from \n ( select \n sont.table_name stab, \n ftab.table_name ftab, \n sont.constraint_name SFK, \n ftab.constraint_name FPK, \n dcc.column_name soncolumn, \n 'create index '||dcc.owner||'.IDX_'||column_name||' on '||dcc.owner||'.'||dcc.table_name||'('||dcc.column_name||') online;' cindex \n from sont,ftab,dba_cons_columns dcc \n where sont.r_constraint_name=ftab.constraint_name \n and dcc.constraint_name=sont.constraint_name \n AND NOT EXISTS (SELECT COLUMN_NAME FROM DBA_IND_COLUMNS WHERE COLUMN_NAME=DCC.COLUMN_NAME and table_name=sont.table_name ) \n ORDER BY FTAB ) info; \n --FTAB-->STAB --这个回头需要加listagg \n select 'alter table '||sowner||'.'||son_table||' add constraint '||rconstraint_name||' foreign key ('||son_col||') references '|| \n fowner||'.'||f_table||'('||f_col||');',t.* \n from ( \n select r.owner sowner,r.table_name son_table,r.column_name son_col,r.deferrable rdeferrable,r.deferred rdeferred,r.validated rvalidated, \n r.constraint_name rconstraint_name, \n p.owner fowner,p.table_name f_table,p.column_name f_col,p.deferrable pdeferrable,p.deferred pdeferred,p.validated pvalidated, \n p.constraint_name pconstraint_name \n from \n ( \n select r.owner,r.table_name,rk.column_name,r.constraint_name,r_constraint_name,r.deferrable,r.deferred,r.validated from dba_constraints r,dba_cons_columns rk \n where r.constraint_name=rk.constraint_name \n and r.owner=rk.owner \n and r.table_name=rk.table_name \n and r.constraint_type='R' \n ) r, \n ( \n select p.owner,p.table_name,pk.column_name,p.constraint_name,p.deferrable,p.deferred,p.validated \n from dba_constraints p,dba_cons_columns pk \n where p.owner=pk.owner \n and p.table_name=pk.table_name \n and p.constraint_name=pk.constraint_name \n and p.constraint_type='P' \n and not regexp_like(p.owner,'SYS') \n ) p \n where r.r_constraint_name=p.constraint_name \n --and r.constraint_name='FK_DEPTNO' \n --and r.constraint_name=rk.constraint_name \n and p.table_name='T_ACCUM_BILL_PRODUCT' \n --and r.table_owner='' \n ) t \n ; \n --LO select OWNER,OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME from dba_objects where OBJECT_ID=&OBJECT_ID; --所有外键 --某用户所有外键 select 'ALTER TABLE '||a.OWNER||'.'||a.TABLE_NAME||' ADD CONSTRAINT '||a.CONSTRAINT_NAME||' FOREIGN KEY('||b.COLUMN_NAME||') REFERENCES '||a.R_OWNER||'.'||c.TABLE_NAME||'('||c.COLUMN_NAME||');' from dba_constraints a,dba_cons_columns b,dba_cons_columns c where a.OWNER in('&OWNER')and a.CONSTRAINT_TYPE='R' and a.OWNER=b.OWNER and a.table_name=b.table_name and a.CONSTRAINT_NAME=b.CONSTRAINT_NAME and a.R_OWNER=c.OWNER and a.R_CONSTRAINT_NAME=c.CONSTRAINT_NAME ; --阻塞链 col SQL_ID for a20 col STATUS for a10 col INST_ID for 99 col TREE for a30 select * from (select a.inst_id, a.sid, a.serial#, a.sql_id, a.event, a.status, connect_by_isleaf as isleaf, sys_connect_by_path(a.SID||'@'||a.inst_id, ' <- ') tree, level as tree_level from gv$session a start with a.blocking_session is not null connect by (a.sid||'@'||a.inst_id) = prior (a.blocking_session||'@'||a.blocking_instance)) where isleaf = 1 order by tree_level asc; --tab_ind set lines 199 \n col table_owner for a15 \n col index_owner for a15 \n col table_name for a30 \n col index_name for a30 \n col UNIQUENESS for a10 \n col partitioned for a5 \n col column_name for a43 \n select d.table_owner,d.owner index_owner,d.table_name,d.index_name,d.UNIQUENESS,d.partitioned,VISIBILITY, \n listagg(i.column_name,',') within group (order by i.column_position) column_name \n from dba_indexes d,dba_ind_columns i \n where d.table_name=upper('&tname') \n and d.owner=i.index_owner \n and d.index_name=i.index_name \n and d.table_name=i.table_name \n and d.table_owner=i.table_owner \n group by d.table_owner,d.owner,d.table_name,d.index_name,d.UNIQUENESS,d.partipartitioned,VISIBILITY; \n --mkdir cd /picclife/dbscript/BO mkdir -p /picclife/dbscript/BO/$(date +%Y%m%d) cd /picclife/dbscript/BO/$(date +%Y%m%d) --pack_install.sh pack_install.sh --wait_event set pagesize 2000 set linesize 210 col sql_id for a14 col s_time for a12 col status for a6 col event format a30 col username for a14 col osuser for a8 col p1 for 9999999 col sid for 9999 col p2 for 999999 col p3 for 9999999999999 col program format a25 Col machine for a20 col serial# format 99999 select substr(a.program,1,25) program,a.SID,a.SERIAL#,a.USERNAME,substr(a.osuser,1,8) osuser,a.sql_id,substr(a.machine,1,20) machine,a.status, to_char(a.SQL_EXEC_START,'dd hh24:mi:ss') s_time,(case when a.STATE='WAITING' then a.event else 'CPU' end) event from v$session a,v$session_wait b where a.sid=b.sid and status = 'ACTIVE' and not ( a.type = 'BACKGROUND' and a.state='WAITING' and a.wait_class='Idle')and a.username is not null and a.SQL_ID is not null order by a.program,a.sid,a.sql_id; --block_source col OSUSER for a15 select sid,MACHINE,OSUSER,username,EVENT,BLOCKING_INSTANCE,BLOCKING_SESSION from gv$session where MACHINE='rac11g870a' and OSUSER='oracle' and PROGRAM like '%sqlplus@rac11g870a (TNS V1-V3)%' and username='PICCPROD'; --kill&sid select '!ps -ef|grep '||pro.spid ||' '|| '!kill -9 '||pro.spid from v$session ses,v$process pro where ses.sid in( &sid ) and ses.paddr=pro.addr; --id&obj sqlplus PICCPROD/picc_sys_100115 select 'alter ' ||decode(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' ' || OBJECT_NAME || decode(object_type, 'PACKAGE BODY',' compile body ; ', ' compile; ')from user_objects where status <> 'VALID' AND OBJECT_TYPE NOT LIKE 'JAVA%'; --app_server su - weblogic id pack_deploy.sh --维护脚本 --阻塞查询 set linesize 300 pagesize 50 col bs for 9999 col wc for 99 col sql_id for a13 col username for a13 col event for a25 col schemaname for a17 col machine for a20 col program for a35 select inst_id,sid,serial#,username,sql_id,machine,program,event,blocking_instance bins,blocking_session bs,wait_class# wc from gv$session where blocking_session is not null order by blocking_session,blocking_instance; --pkg_lock select /*+ parallel(4) */ s.USERNAME,count(*),s.inst_idfrom gv$session s,(select distinct inst_id,sid,object from gv$access where object = 'PKG_LIFE_NEWBIZ_PKG') a --,gv$sql lwhere s.inst_id=a.inst_idand s.sid=a.sid--and s.sql_id=l.sql_id--and s.inst_id=l.inst_id--and s.sql_child_number=l.child_numbergroup by s.USERNAME,s.inst_idorder by count(*); --63blockme col OSUSER for a15 select sid,MACHINE,OSUSER,username,EVENT,BLOCKING_INSTANCE,BLOCKING_SESSION from gv$session where MACHINE='dgsoutha' and OSUSER='oracle' and PROGRAM like '%sqlplus@dgsoutha (TNS V1-%' and username='PICCPROD'; --63失效包 sqlplus piccprod/debug_piccprod_123 --conn claim_prod/debug_claim_prod_345 --conn piccprod/debug_piccprod_123 select 'alter ' ||decode(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) || ' ' || OBJECT_NAME || decode(object_type, 'PACKAGE BODY',' compile body ; ', ' compile; ')from user_objects where status <> 'VALID' AND OBJECT_TYPE NOT LIKE 'JAVA%'; --理赔发布 cd /picclife #sh deployStart_cbs.sh #0#PICC_MASTER.V202006.006 --kill SELECT --A.P1,A.P2,A.P3, A.SQL_EXEC_START,A.INST_ID,A.USERNAME blocker,A.SID,A.SERIAL#,A.EVENT,a.sql_id, B.USERNAME B_USERNAME,B.SID B_SID,B.EVENT B_EVENT,B.SQL_EXEC_START B_SQL_EXEC_START,b.sql_id, 'alter system kill session '''||A.sid||','||A.serial#||',@'||A.inst_id||''' immediate;' ksql, p.inst_id,'kill -9 '||p.spid FROM GV$SESSION A,GV$SESSION B,gv$process p WHERE A.SID=B.FINAL_BLOCKING_SESSION AND A.USERNAME IS NOT NULL AND A.INST_ID=B.INST_ID AND A.AUDSID<>B.AUDSID and a.paddr=p.addr and a.inst_id=p.inst_id and b.inst_id=p.inst_id AND b.PROGRAM LIKE '%sqlplus%' ; --清理归档 rman target / list archivelog all; list copy of archivelog until time 'SYSDATE-5'; delete archivelog until time 'SYSDATE-5'; delete force noprompt archivelog until time 'sysdate-3'; crosscheck archivelog all; oracle闪回查询 create table A.t_bak as select * from A.t as of timestamp to_timestamp('2021-09-10 17:20:00', 'yyyy-mm-dd hh24:mi:ss') resize空间剩余较大的数据文件：将数据文件大小设为高水位线位置 SELECT 'alter database datafile '''||a.file_name||''' resize ' ||round(c.hwmsize)||'M;' cmd, a.file_id,a.filesize, c.hwmsize FROM ( SELECT file_id, file_name, round(bytes/1024/1024) filesize FROM dba_data_files ) a, ( SELECT file_id, round(max(block_id)*8/1024) HWMsize FROM dba_extents GROUP BY file_id) c WHERE a.file_id = c.file_id and a.filesize > c.hwmsize+&size ; --job 停用job EXEC DBMS_JOB.BROKEN(26,TRUE);
--创建同义词后，需要再授权：
conn / as sysdba
CREATE OR REPLACE SYNONYM zytuser.zyttab FOR zytuser2.zyttab;
GRANT insert,delete,update,select ON zytuser.zyttab TO zytuser;
1.创建用户
--查询用户是否存在
select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATED from dba_users
where USERNAME in ('USER1','USER2');
--创建表空间
create tablespace PROD_TBS datafile '+DATA' size 30G;
create tablespace OPS_TBS datafile '+DATA' size 30G;
--创建用户
create user USER1 identified by prod_ora_365_001 DEFAULT TABLESPACE "PROD_TBS";
create user USER2 identified by ops_ora_365_100 DEFAULT TABLESPACE "OPS_TBS";
--授权
GRANT CONNECT,RESOURCE,CREATE PROCEDURE,UNLIMITED TABLESPACE TO USER1;
GRANT CONNECT,RESOURCE,CREATE PROCEDURE,UNLIMITED TABLESPACE TO user2;
--确认用户
select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATED from dba_users
where USERNAME in ('USER1','USER2');
--测试用户
conn USER1/prod_ora_365_001
conn user2/ops_ora_365_100
------重置用户密码
select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE,LOCK_DATE,PROFILE from dba_users where USERNAME='&USERNAME';
set lines 199
col sqlt for a199
select 'alter user '||name||' IDENTIFIED BY values '''||spare4||';'||PASSWORD||''';' sqlt from SYS.user$ where name ='&uname';
--闪回查询
select name,spare4,PASSWORD from SYS.user$ as of timestamp to_timestamp('2021-04-21 00:00:00','yyyy-mm-dd hh24:mi:ss') where name ='&name';
--从dba_jobs获取重建语句
select j.SCHEMA_USER,'declare ' ||' n_syn_points number; '||' begin '||' dbms_job.submit(n_syn_points,'||''''||j.what||''''||','|| ' sysdate,'||''''||j.interval||''''||');'||' commit; '||
' end; ' as ss
from dba_jobs j
--where j.LOG_USER not in ('SYS')
--and j.SCHEMA_USER='ZHAOPIN'
where job=&job;
--gather database stats
vi /home/oracle/gather_db_stats.sql
set time on
set timing on
select 1 from dual;
exec dbms_stats.gather_database_stats(estimate_percent=>1,cascade=>TRUE,degree => 8);
select 1 from dual;
exit
nohup sqlplus / as sysdba @/home/oracle/gather_db_stats.sql &
--gather schema stats
vi /home/oracle/gather_schema_stats.sql
set time on
set timing on
select 1 from dual;
EXEC DBMS_STATS.gather_schema_stats('SCOTT',estimate_percent=>1,cascade=>TRUE,degree => 8);
select 1 from dual;
exit
nohup sqlplus / as sysdba @/home/oracle/gather_schema_stats.sql &
--gather table stats
EXEC DBMS_STATS.gather_table_stats(
ownname=>'PICCPROD',
tabname=>'T_LETTER_RECEIVER',
DEGREE=>4,
cascade=>'true');
根据等待事件抓取sql
select to_char(sample_time,'yyyymmdd-hh24'),count(*),sql_id,sql_plan_line_id from gv$active_session_history
where event like '%&event%'
group by to_char(sample_time,'yyyymmdd-hh24'),sql_id,sql_plan_line_id
having count(*) > 100
order by 2 desc;
用户密码重置
SELECT 'ALTER USER '|| name ||' IDENTIFIED BY VALUES '''|| spare4 ||';'|| password ||''' ;' FROM sys.user$ WHERE name like UPPER('&name%');
1.password_versions的优先级大于sec_case_sensitive_logon
2.通过修改USER$.SPARE4为空实现了屏蔽ORACLE 11g密码大小写敏感
select 'ALTER PROFILE '||P.PROFILE||' LIMIT '||resource_name||' '||P.LIMIT||' ;' from dba_profiles p;