select a.CONSTRAINT_NAME,b.TABLE_NAME 引用表,b.COLUMN_NAME 引用列,c.TABLE_NAME 被引用表,c.COLUMN_NAME 被引用列 fromuser_constraints a,user_cons_columns b,user_cons_columns cwhere a.CONSTRAINT_NAME=b.CONSTRAINT_NAMEand a.R_CONSTRAINT_NAME=c.CONSTRAINT_NAMEand a.CONSTRAINT_TYPE='R'and c.TABLE_NAME like 'T_PROD%';

select sql_id,s.SQL_TEXT,s.ELAPSED_TIME/s.EXECUTIONSfrom v$sqlstats swhere s.LAST_ACTIVE_TIME>to_date('20220124 12:00:00','yyyymmdd hh24:mi:ss')and s.LAST_ACTIVE_TIME>to_date('20220124 12:00:00','yyyymmdd hh24:mi:ss')and s.EXECUTIONS>0order by 3 desc;
--查询一段时间内sql总等待时间select event,a.sql_id,sql_text,sum(time_waited)from v$active_session_history a,v$sql swhere a.SQL_ID=s.SQL_IDand a.SAMPLE_TIME>to_date('20220124 12:00:00','yyyymmdd hh24:mi:ss')and a.SAMPLE_TIMEgroup by event,a.sql_id,sql_textorder by 4 desc;

select a.SAMPLE_TIME,a.SESSION_ID,a.SQL_ID,a.WAIT_TIME,a.BLOCKING_SESSION from v$active_session_history awhere a.MACHINE='41cb3c835bb1'and a.SAMPLE_TIME>to_date('20220125 15:50:00','yyyymmdd hh24:mi:ss')and a.SAMPLE_TIME

select a.segment_name, count(*)  from dba_undo_extents a,       (SELECT n.name name          FROM V$session s, V$transaction t, V$rollstat r, v$rollname n         WHERE s.saddr = t.ses_addr           AND t.xidusn = r.usn           AND r.usn = n.usn) b where a.segment_name = b.name   and a.status = 'ACTIVE' group by a.segment_name order by count(*);

select b.sid,       b.serial#,       b.username,       b.machine,       b.program,       a.xidusn as "UndoSegID",       a.used_ublk * to_number(rtrim(p.value))/1024/1024 as Undo_mb,       c.name,       d.tablespace_name  from v$transaction     a,       v$session         b,       v$rollname        c,       dba_rollback_segs d,       v$parameter       p where a.addr = b.taddr   and a.xidusn = c.usn   and c.name = d.segment_name   and p.name = 'db_block_size' order by Undo_mb desc;SELECT s.username,       s.sid,       s.serial#,       t.xidusn,       t.ubafil,       t.ubablk,       t.used_ublk,       t.used_ublk /       (select sum(blocks)          from dba_data_files         where tablespace_name =               (select value from v$parameter where name = 'undo_tablespace')) * 100 as "使用率 %"  FROM v$session s, v$transaction t WHERE s.saddr = t.ses_addr;

select s.sid,       s.serial#,       s.username,       s.schemaname,       s.machine,       s.program,       p.PGA_USED_MEM / 1024 / 1024 as "PGS Used(mb)",       p.PGA_ALLOC_MEM / 1024 / 1024 as "PGS Allocate(mb)"  from v$session s, v$process p where p.addr = s.paddr   and s.username is not null   and p.PGA_USED_MEM / 1024 / 1024 > 1142 order by p.PGA_USED_MEM;

select tablespace_name,       round(free_space / 1024 / 1024 / 1024, 2) "free(GB)",       round(tablespace_size / 1024 / 1024 / 1024, 2) "total(GB)",       round(nvl(free_space, 0) * 100 / tablespace_size, 3) "Free percent"  from dba_temp_free_space;

select se.username,       se.sid,       se.serial#,       se.machine,       se.program,       su.extents,       su.blocks * to_number(rtrim(p.value)) / 1024 / 1024 as Space_mb,       tablespace,       segtype,       s.sql_text  from v$sort_usage su, v$parameter p, v$session se, v$sql s where p.name = 'db_block_size'   and s.HASH_VALUE = su.SQLHASH   and s.ADDRESS = su.SQLADDR order by Space_mb desc;

select s.sid,       s.serial#,       s.status,       s.machine,       s.program,       t.username,       t.sql_id,       t.TABLESPACE,       t.SEGTYPE,       blocks  from gv$session s, gv$tempseg_usage t where s.SADDR = t.SESSION_ADDR   and s.serial# = t.session_num   and s.INST_ID = t.inst_id;

SELECT l.session_id sid,       s.serial#,       l.locked_mode,       l.oracle_username,       l.os_user_name,       s.machine,       s.terminal,       o.object_name,       s.logon_time,       p.spid  FROM v$locked_object l, all_objects o, gv$session s, v$process p WHERE l.object_id = o.object_id   AND l.session_id = s.sid   AND s.PADDR = p.ADDR ORDER BY sid, s.serial#;--使用spid查询相应machine的IP和进程启动时间：netstat -anp |grep spidps auxw|head -1ps auxw|grep SPIDps –ef | grep spid

select s.sql_text,s.sql_id  from v$sql s, v$session se, v$locked_object l where s.hash_value = se.SQL_HASH_VALUE   and se.sid = l.session_id；select username, sql_text, machine, osuser  from v$session a, v$sqltext_with_newlines b where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value) =       b.hash_value   and a.sid = &sid order by piece;select ss.SID,       pr.SPID,       ss.action,       sa.SQL_FULLTEXT,       ss.machine,       ss.PROGRAM,       ss.SERIAL#,       ss.USERNAME,       ss.STATUS,       ss.OSUSER,       ss.last_call_et  from v$process pr, v$session ss, v$sqlarea sa where ss.status = 'ACTIVE'   and ss.username is not null   and pr.ADDR = ss.PADDR   and ss.SQL_ADDRESS = sa.ADDRESS   and ss.SQL_HASH_VALUE = sa.HASH_VALUE   and pr.spid = &spid;

select s.sid,s.username,s.serial#,s.INST_ID,'alter system disconnect session '''||s.sid||','||s.serial#||',@'||s.INST_ID||''' immediate;',s.EVENT,s.machine,s.program,s.sql_id,l.ctime,l.type,l.lmode,l.request,o.object_name,o.object_typefrom gv$session s, gv$locked_object lo, gv$lock l, dba_objects owhere s.sid = l.sidand l.sid = lo.SESSION_IDand lo.OBJECT_ID = o.object_idand s.status='ACTIVE'and l.type in ('TX', 'TM')and s.USERNAME is not null and s.USERNAME<>'SYS'and ctime > 600;

select 'alter system disconnect session '''||sid||','||serial#||''' immediate;' from v$session where username='BLUESKY';alter system disconnect session 'sid,serial#' immediate;--如果遇到RAC环境，一定要用gv$session来查，并且执行alter system disconnect session 'sid,serial#'  immediate--要到RAC对应的实例上去执行

select * from table(dbms_xplan.display_awr('&&sql'));select a.hash_value,a.* from v$sql a where sql_id='&sql_id'select * from table(dbms_xplan.display_cursor(2729381371,0,'advanced'));select * from table(dbms_xplan.display_awr('91tw3s78z14k3'))；含顺序的select * from table(xplan.display_cursor('9bd10aujay3gv',0,'advanced'));不过要先创建 xplan包，再执行SQL> CREATE PUBLIC SYNONYM XPLAN FOR SYS.XPLAN;SQL> grant execute on sys.xplan to public;

select c.tablespace_name,       a.file#,       a.name,       a.bytes / 1024 / 1024 CurrentMB,       ceil(HWM * a.block_size) / 1024 / 1024 ResizeTo,       (a.bytes - HWM * a.block_size) / 1024 / 1024 ReleaseMB,       'alter database datafile ''' || a.name || ''' resize ' ||       ceil(HWM * a.block_size / 1024 / 1024) || 'M;' ResizeCMD  from v$datafile a,       (select file_id, max(block_id + blocks - 1) HWM          from dba_extents         group by file_id) b,       dba_data_files c where a.file# = b.file_id(+)   and (a.bytes - HWM * block_size) > 0   and a.file# = c.file_id order by 2;

select * from sys.dba_role_privs where granted_role='XXX';

SELECT SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 AS "Size(M)",       TRUNC(completion_time)  FROM v$archived_log GROUP BY TRUNC(completion_time);--三日内归档切换频率查询：select sequence#,       to_char(first_time, 'yyyymmdd_hh24:mi:ss') firsttime,       round((first_time - lag(first_time) over(order by first_time)) * 24 * 60,2) minutes  from v$log_history where first_time > sysdate - 3 order by first_time desc;

通常由于记录审计信息造成truncate table sys.aud$;

-- listener 日志将无法再被记录，同时 listener 也会变得不稳定lsnrctl set log_status off;mv listener.log listener.log.1;lsnrctl set log_status on;或set current_listener XXXXset log_file XXXsave_config

lsnrctl status XXX--查看是否长期出现监听的子进程及其pidps -ef|grep tnslsnr--对目标监听进程和子进程，收集至少2次进程堆栈pstack  --收集strace的输出strace -frT -o /tmp/strace-lsnr.log -p 

Begindbms_job.broken(43,true);commit;end;/
Job相关试图：dba_scheduler_running_jobsdba_jobs(_running)

SELECT TABLE_NAME,       (BLOCKS * 8192 / 1024 / 1024) "使用大小M",       (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) "实际大小M",       round((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /             (BLOCKS * 8192 / 1024 / 1024),             3) * 100 || '%' "实际使用率%"  FROM USER_TABLES where blocks > 100   and (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /       (BLOCKS * 8192 / 1024 / 1024) < 0.3 order by (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /          (BLOCKS * 8192 / 1024 / 1024) desc;
select name,       del_lf_rows,       lf_rows,       round(del_lf_rows / decode(lf_rows, 0, 1, lf_rows) * 100, 0) || '%' frag_pct  from index_stats where round(del_lf_rows / decode(lf_rows, 0, 1, lf_rows) * 100, 0) > 30;

select round(sum(pga_used_mem) / 1024 / 1024, 0) total_used_M,       round(sum(pga_used_mem) / count(1) / 1024 / 1024, 0) avg_used_M,       round(sum(pga_alloc_mem) / 1024 / 1024, 0) total_alloc_M,       round(sum(pga_alloc_mem) / count(1) / 1024 / 1024, 0) avg_alloc_M  from v$process;

select event,       sum(decode(wait_time, 0, 0, 1)) "之前等待会话数",       sum(decode(wait_time, 0, 1, 0)) "正在等待会话数",       count(*)  from v$session_wait group by event order by 4 desc;

select sum(percent_space_used)||'%' "已使用空间比例" from V$RECOVERY_AREA_USAGE;

select a.tablespace_name,b.total / 1024 / 1024 / 1024 total_gb,a.free/1024/1024/1024 free_gb,ROUND((total - free) / total, 4) * 100 "使用率%"from (select tablespace_name, sum(bytes) freefrom dba_free_spacegroup by tablespace_name) a,(select tablespace_name, sum(bytes) totalfrom dba_data_filesgroup by tablespace_name) bwhere a.tablespace_name = b.tablespace_nameorder by a.tablespace_name;
--查看ASM磁盘组使用率select name,       round(total_mb / 1024) "总容量",       round(free_mb / 2) "空闲空间",       round(((total_mb - free_mb) / total_mb) * 100) "使用率"  from gv$asm_diskgroup;

select sql_id, FORCE_MATCHING_SIGNATURE, sql_textfrom v$SQLwhere FORCE_MATCHING_SIGNATURE in(select /*+ unnest */FORCE_MATCHING_SIGNATUREfrom v$sqlwhere FORCE_MATCHING_SIGNATURE > 0and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATUREgroup by FORCE_MATCHING_SIGNATUREhaving count(1) > 10);

SELECT e.owner, e.segment_name, e.segment_type, b.tch  FROM dba_extents e,       (SELECT *          FROM (SELECT dbarfil, dbablk, tch                  FROM x$bh                 ORDER BY tch DESC)         WHERE ROWNUM < 11) b WHERE e.relative_fno = b.dbarfil   AND e.block_id <= b.dbablk   AND e.block_id + e.blocks > b.dbablk;

WITH sysstat AS (select sn.begin_interval_time begin_interval_time,         sn.end_interval_time end_interval_time,         ss.stat_name stat_name,         ss.value e_value,         lag(ss.value, 1) over(order by ss.snap_id) b_value    from dba_hist_sysstat ss, dba_hist_snapshot sn   where trunc(sn.begin_interval_time) >= sysdate - 7     and ss.snap_id = sn.snap_id     and ss.dbid = sn.dbid     and ss.instance_number = sn.instance_number     and ss.dbid = (select dbid from v$database)     and ss.instance_number = (select instance_number from v$instance)     and ss.stat_name = 'DB time')select to_char(BEGIN_INTERVAL_TIME, 'mm-dd hh24:mi') ||       to_char(END_INTERVAL_TIME, ' hh24:mi') date_time,       stat_name,       round((e_value - nvl(b_value, 0)) /             (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60 +             extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60 +             extract(minute from(end_interval_time - begin_interval_time)) * 60 +             extract(second from(end_interval_time - begin_interval_time))),             0) per_sec  from sysstat where (e_value - nvl(b_value, 0)) > 0   and nvl(b_value, 0) > 0;

select * from dba_hist_snapshotselect * from table(dbms_workload_repository.awr_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid))select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid, DBID, INSTANCE_NUMBER, startsnapid,endsnapid));

col WINDOW_NAME for a15col REPEAT_INTERVAL for a60col DURATION for a30set linesize 120
SELECT t1.window_name, t1.repeat_interval, t1.durationFROM dba_scheduler_windows t1, dba_scheduler_wingroup_members t2WHERE t1.window_name = t2.window_nameAND t2.window_group_name IN('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');
WINDOW_NAME     REPEAT_INTERVAL                                              DURATION--------------- ------------------------------------------------------------ ------------------------------MONDAY_WINDOW   freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0        +000 04:00:00TUESDAY_WINDOW  freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00WEDNESDAY_WINDO freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0        +000 04:00:00THURSDAY_WINDOW freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00FRIDAY_WINDOW   freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0        +000 04:00:00SATURDAY_WINDOW freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0         +000 20:00:00SUNDAY_WINDOW   freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:00
--备注：#freq=daily：每天收集；#;byday=SUN：一周之内的星期，例如这里是星期日；#byhour=22 ：每天的时间点时，这里是22时；#byminute=0：每天的的时间点分，这里是0分，则第1分；#bysecond=0：每天的时间点秒，这里是0秒，则第1秒；#+000 20:00:00 ：表示收集信息的时间区间长，这里表示20小时。
---修改自动收集统计信息计划任务时间：--首先停止原来计划;
BEGINDBMS_SCHEDULER.DISABLE(name => '"SYS"."THURSDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.DISABLE(name => '"SYS"."MONDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.DISABLE(name => '"SYS"."TUESDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.DISABLE(name => '"SYS"."WEDNESDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.DISABLE(name => '"SYS"."FRIDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.DISABLE(name => '"SYS"."SATURDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.DISABLE(name => '"SYS"."SUNDAY_WINDOW"');end;/ --修改计划任务的执行时间：
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."THURSDAY_WINDOW"',attribute => 'REPEAT_INTERVAL',value =>'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');end;/
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."MONDAY_WINDOW"',attribute => 'REPEAT_INTERVAL',value =>'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');end;/
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."TUESDAY_WINDOW"',attribute => 'REPEAT_INTERVAL',value =>'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');end;/
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."WEDNESDAY_WINDOW"',attribute => 'REPEAT_INTERVAL',value =>'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');end;/
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."FRIDAY_WINDOW"',attribute => 'REPEAT_INTERVAL',value =>'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');end;/
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."SATURDAY_WINDOW"',attribute => 'REPEAT_INTERVAL',value =>'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');end;/
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."SUNDAY_WINDOW"',attribute => 'REPEAT_INTERVAL',value =>'freq=daily;byday=THU;byhour=3;byminute=0;bysecond=0');end;/
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."SATURDAY_WINDOW"',attribute => 'DURATION',value =>'+000 04:00:00');end;/
BEGINDBMS_SCHEDULER.SET_ATTRIBUTE(name => '"SYS"."SUNDAY_WINDOW"',attribute => 'DURATION',value =>'+000 04:00:00');end;/
--启用新的计划任务的执行时间：
BEGINDBMS_SCHEDULER.ENABLE(name => '"SYS"."THURSDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.ENABLE(name => '"SYS"."MONDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.ENABLE(name => '"SYS"."TUESDAY_WINDOW"');end;/BEGINDBMS_SCHEDULER.ENABLE(name => '"SYS"."WEDNESDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.ENABLE(name => '"SYS"."FRIDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.ENABLE(name => '"SYS"."SATURDAY_WINDOW"');end;/
BEGINDBMS_SCHEDULER.ENABLE(name => '"SYS"."SUNDAY_WINDOW"');end;/
禁用 SQL TUNING TASK
BEGINdbms_auto_task_admin.disable(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);END;/
BEGINdbms_auto_task_admin.disable(client_name => 'auto space advisor',operation => NULL,window_name => NULL);END;/

SET PAGESIZE 9999 LINESIZE 180;TTI 'Tablespace Usage Status'COL TABLESPACE_NAME FOR A20;COL TBS_MAX_SIZE FOR 99999.99;COL TABLESPACE_SIZE FOR 99999.99;COL TBS_AVABLE_SIZE FOR 999999.99;COL "USED_RATE(%)" FOR A16;COL "ACT_USED_RATE(%)" FOR A16;COL "FREE_SIZE(GB)" FOR 99999999.99;
SELECT  UPPER(F.TABLESPACE_NAME)                           AS "TABLESPACE_NAME",        ROUND(D.MAX_BYTES,2)                               AS "TBS_MAX_SIZE" ,        ROUND(D.AVAILB_BYTES ,2)                           AS "ACT_TABLESPACE_SIZE",        ROUND((D.AVAILB_BYTES - F.USED_BYTES),2)           AS "TBS_USED_SIZE",        ROUND(F.USED_BYTES, 2)                             AS "FREE_SIZE(GB)",        TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100,                     2),               '999.99')                                   AS "USED_RATE(%)",        TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES)/D.MAX_BYTES*100,                     2),               '999.99')                                   AS "ACT_USED_RATE(%)",        ROUND(D.MAX_BYTES - D.AVAILB_BYTES +USED_BYTES,2)  AS "TBS_AVABLE_SIZE"  FROM (SELECT TABLESPACE_NAME,               ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES,               ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES          FROM SYS.DBA_FREE_SPACE         GROUP BY TABLESPACE_NAME) F,       (SELECT DD.TABLESPACE_NAME,               ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6)  AVAILB_BYTES,               ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES))/(1024*1024*1024),6)   MAX_BYTES          FROM SYS.DBA_DATA_FILES DD         GROUP BY DD.TABLESPACE_NAME) DWHERE D.TABLESPACE_NAME = F.TABLESPACE_NAMEORDER BY "ACT_USED_RATE(%)" DESC;
vim get_tablespace_used_v3.sql
set pagesize 1000 linesize 180tti 'Tablespace Usage Status'col "TOTAL(GB)" for 99,999,999.999col "USAGE(GB)" for 99,999,999.999col "FREE(GB)" for 99,999,999.999 col "EXTENSIBLE(GB)" for 99,999,999.999col "MAX_SIZE(GB)" for 99,999,999.999col "FREE PCT %" for 999.99col "USED PCT OF MAX %" for 999.99col "NO_AXF_NUM" for 9999col "AXF_NUM" for 999select d.tablespace_name "TBS_NAME"      ,d.contents "TYPE"      ,nvl(a.bytes /1024/1024/1024,0) "TOTAL(GB)"      ,nvl(a.bytes - nvl(f.bytes,0),0)/1024/1024/1024 "USAGE(GB)"      ,nvl(f.bytes,0)/1024/1024/1024 "FREE(GB)"      ,nvl((a.bytes - nvl(f.bytes,0))/a.bytes * 100,0) "FREE PCT %"      ,nvl(a.ARTACAK,0)/1024/1024/1024 "EXTENSIBLE(GB)"      ,nvl(a.MAX_BYTES,0)/1024/1024/1024 "MAX_SIZE(GB)"      ,nvl((a.bytes - nvl(f.bytes,0))/ (a.bytes + nvl(a.ARTACAK,0)) * 100,0) "USED PCT OF MAX %"      ,a.NO_AXF_NUM      ,a.AXF_NUMfrom sys.dba_tablespaces d,(select tablespace_name       ,sum(bytes) bytes       ,sum(decode(autoextensible,'YES',maxbytes - bytes,0 )) ARTACAK       ,count(decode(autoextensible,'NO',0))  NO_AXF_NUM       ,count(decode(autoextensible,'YES',0)) AXF_NUM       ,sum(decode(maxbytes, 0, BYTES, maxbytes))   MAX_BYTESfrom dba_data_filesgroup by tablespace_name) a,(select tablespace_name       ,sum(bytes) bytesfrom dba_free_spacegroup by tablespace_name) fwhere d.tablespace_name = a.tablespace_name(+)  and d.tablespace_name = f.tablespace_name(+)  and not (d.extent_management like 'LOCAL'and d.contents like 'TEMPORARY')union allselect d.tablespace_name "TBS_NAME"      ,d.contents "TYPE"      ,nvl(a.bytes /1024/1024/1024,0) "TOTAL(GB)"      ,nvl(t.bytes,0)/1024/1024/1024 "USAGE(GB)"      ,nvl(a.bytes - nvl(t.bytes,0),0)/1024/1024/1024 "FREE(GB)"      ,nvl(t.bytes/a.bytes * 100,0) "FREE PCT %"      ,nvl(a.ARTACAK,0)/1024/1024/1024 "EXTENSIBLE(GB)"      ,nvl(a.MAX_BYTES,0)/1024/1024/1024 "MAX_SIZE(GB)"      ,nvl(t.bytes/(a.bytes + nvl(a.ARTACAK,0)) * 100,0) "USED PCT OF MAX %"      ,a.NO_AXF_NUM      ,a.AXF_NUMfrom sys.dba_tablespaces d,(select tablespace_name       ,sum(bytes) bytes       ,sum(decode(autoextensible,'YES',MAXbytes - bytes,0 )) ARTACAK       ,count(decode(autoextensible,'NO',0)) NO_AXF_NUM       ,count(decode(autoextensible,'YES',0)) AXF_NUM       ,sum(decode(maxbytes, 0, BYTES, maxbytes))   MAX_BYTESfrom dba_temp_filesgroup by tablespace_name) a,(select tablespace_name      , sum(bytes_used) bytes from v$temp_extent_poolgroup by tablespace_name) twhere d.tablespace_name = a.tablespace_name(+)  and d.tablespace_name = t.tablespace_name(+)  and d.extent_management like 'LOCAL'  and d.contents like 'TEMPORARY%'order by 6 desc;

SELECT *FROM ( SELECT c.TABLESPACE_NAME,c.segment_name,b.object_type,ROUND (SUM (space_used_delta) / 1024 / 1024, 2) "Growth (MB)"FROM dba_hist_snapshot sn,dba_hist_seg_stat a,dba_objects b,dba_segments cWHERE begin_interval_time > TRUNC (SYSDATE) - &days_backAND sn.snap_id = a.snap_idAND b.object_id = a.obj#AND b.owner = c.ownerAND b.object_name = c.segment_nameAND c.owner = '&SCHEMANAME'GROUP BY c.TABLESPACE_NAME, c.segment_name, b.object_type)ORDER BY 1,4 ASC;

SELECT TO_CHAR (sp.begin_interval_time,'YYYY-MM-DD') days, ts.tsname, max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB, max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MBFROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts, DBA_HIST_SNAPSHOT sp, DBA_TABLESPACES dtWHERE tsu.tablespace_id= ts.ts#AND tsu.snap_id = sp.snap_idAND ts.tsname = dt.tablespace_nameAND ts.tsname NOT IN ('SYSAUX','SYSTEM')AND TS.tsname='&TBS_NAME'GROUP BY TO_CHAR (sp.begin_interval_time,'YYYY-MM-DD'), ts.tsnameORDER BY days ;

select instance_number,          metric_unit,           trunc(begin_time) time,           sum(average*3600) "Transactions Per Day",   --一天的平均总和           avg(average) "Transactions Per Second"      --某个时间段的平均值      from DBA_HIST_SYSMETRIC_SUMMARY     where metric_unit = 'Transactions Per Second'       and begin_time >=           to_date('2022-04-18 00:00:00', 'yyyy-mm-dd hh24:mi:ss')       and begin_time < to_date('2022-04-18 16:00:00', 'yyyy-mm-dd hh24:mi:ss')    group by instance_number, metric_unit, trunc(begin_time)order by instance_number;

set echo off feedback off timing off pause offset pages 100 lines 155 trimspool on trimout on space 1 recsep offcol username format a13col prog format a10 trunccol sql_text format a40 trunccol sid format a12col sql_id format a16col child for 99999col execs format 9999999col sqlprofile format a22col avg_ela for 999999.99col last_ela for 999999col event format a20select /*+ rule */       sid||','||serial# sid,       substr(a.event,1,15) event,       b.sql_id||','||child_number sql_id,       plan_hash_value,       executions execs,       (elapsed_time/decode(nvl(executions,0),0,1,executions))/1000000 avg_ela,       last_call_et last_ela,       sql_textfrom v$session a, v$sql bwhere status = 'ACTIVE'and username is not nulland a.sql_id = b.sql_idand a.sql_child_number = b.child_numberand sql_text not like '%from v$session a, v$sql b%'and a.program not like '%(P%)'order by plan_hash_value,last_call_et,sql_id, sql_child_number;
--字段含义：SID：为sid和serial#的值。EVENT：等待事件。 SQL_ID：为sql_id和child_number的值。PLAN_HASH_VALUE：sql执行计划的PLAN_HASH_VALUE。EXECS：执行次数。AVG_ELA：平均执行时间。LAST_ELA：本次已经执行了多久。SQL_TEXT：sql文本。

set echo off feedback off timing off pause off verify offset pages 100 lines 132 trimspool on trimout on space 1 recsep offaccept v_sqlid prompt 'Enter sqlid(default xxx): ' default 'xxxxxxx'accept v_days prompt 'Enter Days ago(default 7): ' default 7col execs for 999,999,999col etime for 999,999,999.9col avg_elas for 999,999.999col avg_cpus for 999,999.999col avg_lios for 999,999,999.9col avg_pios for 9,999,999.9col begin_interval_time for a30col node for 99999break on plan_hash_value on startup_time skip 1select  to_char(begin_time,'mmdd hh24:mi') btime,        sql_id,        plan_hash_value,        sum(execs) execs,        sum(etime)/sum(decode(execs,0,1,execs))    avg_elas,        sum(cpu_time)/sum(decode(execs,0,1,execs)) avg_cpus,        sum(lio)/sum(decode(execs,0,1,execs))      avg_lios,        sum(pio)/sum(decode(execs,0,1,execs))      avg_piosfrom(select ss.instance_number node,        begin_interval_time begin_time,        sql_id,        plan_hash_value,        nvl(executions_delta,0) execs,        elapsed_time_delta/1000000 etime,        (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,        buffer_gets_delta lio,        disk_reads_delta pio,        cpu_time_delta/1000000 cpu_time,        (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,        (cpu_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_cpu_time  from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS  where sql_id = '&&v_sqlid'  and ss.snap_id = s.snap_id  and ss.instance_number = S.instance_number  and elapsed_time_delta>0)where begin_time >= sysdate-&&v_daysgroup by to_char(begin_time,'mmdd hh24:mi'),sql_id, plan_hash_valueorder by 1/undefine v_sqlidundefine v_days
--字段含义：BTIME：快照时间点。SQL_ID：sql_idPLAN_HASH_VALUE：sql执行计划的PLAN_HASH_VALUE，如果这里为空，表示遵循上一个PLAN_HASH_VALUE。EXECS：执行次数。AVG_ELAS：平均执行时间。AVG_CPUS：平均cpu时间AVG_LIOS：平均逻辑读数量

set line 250col ssid format 9999 heading SID;col opname format a15 TRUNCATE ;col target format a28 TRUNCATE ;col es format 99999.9 Heading "Time|Ran";col tr format 99999.90 Heading "Time|Left";col pct format 999.90 Heading "PCT";col RATE FORMAT a6 truncate Heading "I/O |Rate/m" ;col program format a20 TRUNCATE;col MACHINE format a20 truncate;select L.sid ssid, substr(OPNAME,1,15) opname,target, trunc((sofar/totalwork)*100) pct, to_char(60*sofar*8192/(24*60*(last_update_time - start_time))/1024/1024/60, '9999.0') Rate,elapsed_seconds/60 es,time_remaining/60 tr,PROGRAM,MACHINEfrom v$session_longops L,V$SESSION Swhere time_remaining > 0 AND L.SID=S.SIDorder by start_time;

select a.OWNER,       a.INDEX_NAME,       a.TABLE_NAME,       a.DISTINCT_KEYS Cardinality,       a.NUM_ROWS,            round(a.DISTINCT_KEYS / NUM_ROWS * 100, 2) selectivity  from dba_ind_statistics a where a.NUM_ROWS > 0   and round(a.DISTINCT_KEYS / NUM_ROWS * 100, 2) <= 5   and A.OWNER = upper('&owner');   --如果统计信息有可能不是最新的 最好使用下面的语句select table_name,index_name,round(distinct_keys/num_rows * 100, 2) selectivity from user_indexes;

select a.column_name,b.num_rows,a.num_distinct Cardinality,round(a.num_distinct / b.num_rows * 100, 2) selectivity,a.histogram,a.num_bucketsfrom dba_tab_col_statistics a, dba_tables bwhere a.owner = b.ownerand a.table_name = b.table_nameand a.owner = upper('&owner')and a.table_name = upper('&table_name')and a.column_name = upper('&column_name');