--- 查看psu
/nfs/software/db/install/chk/chkora.sh
opatch lsinventory -bugs_fixed | grep 'PSU'
opatch lsinv
[ZFCASSDB1:grid]:/home/grid>opatch lspatches
13343438;Database Patch Set Update : 11.2.0.3.1 (13343438)
13348650;Grid Infrastructure Patch Set Update : 11.2.0.3.1 (13348650)

[ZFCASSDB1:grid]: /home/grid

col action_time   for a30
col action       for a10
col namespace     for a10
col version       for a10
col bundle_series for a10
col comments    for a30

SELECT to_char(action_time, 'YYYY-MM-DD HH24:MI:SS') action_time,
       action,
       namespace,
       version,
       id,
       bundle_series,
       comments
  FROM dba_registry_history D;

select action,comments from registry$history;

--- grid和oracle分别回滚
$ORACLE_HOME/OPatch/opatch rollback -local -id 13348650 -oh /oracle/app/oracle/product/11.2.0/db

--- 查看psu

/nfs/software/db/install/chk/chkora.sh

opatch lsinventory -bugs_fixed | grep 'PSU'

opatch lsinv

[ZFCASSDB1:grid]:/home/grid>opatch lspatches

13343438;Database Patch Set Update : 11.2.0.3.1 (13343438)

13348650;Grid Infrastructure Patch Set Update : 11.2.0.3.1 (13348650)

[ZFCASSDB1:grid]: /home/grid

col action_time for a30

col action for a10

col namespace for a10

col version for a10

col bundle_series for a10

col comments for a30

SELECT to_char(action_time, 'YYYY-MM-DD HH24:MI:SS') action_time,

action,

namespace,

version,

id,

bundle_series,

comments

FROM dba_registry_history D;

select action,comments from registry$history;

--- grid和oracle分别回滚

$ORACLE_HOME/OPatch/opatch rollback -local -id 13348650 -oh /oracle/app/oracle/product/11.2.0/db

根据文件号和块号查询数据库对象

SELECT tablespace_name,
       segment_type,
       owner,
       segment_name,
       partition_name
FROM   dba_extents
WHERE  file_id = &file_id
AND    &block_id BETWEEN block_id AND block_id + blocks - 1
;

SELECT ENAME,SAL,ROWID,DBMS_ROWID.ROWID_CREATE(1,125628,147,4,7),DBMS_ROWID.ROWID_OBJECT(ROWID) OBJECT_ID,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) RN  FROM SCOTT.EMP WHERE EMPNO=7839;

SELECT ENAME,
       SAL,
       ROWID,
       DBMS_ROWID.ROWID_CREATE(1, 125628, 147, 4, 7),
       DBMS_ROWID.ROWID_OBJECT(ROWID) OBJECT_ID,
       DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#,
       DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
       DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) RN
  FROM SCOTT.EMP
 WHERE EMPNO = 7839;

SELECT ROWID,
       dbms_rowid.rowid_object(ROWID) object_id,
       dbms_rowid.rowid_relative_fno(ROWID) file_id,
       dbms_rowid.rowid_block_number(ROWID) block_id,
       d.*
FROM   scott.SALGRADE  d
WHERE  dbms_rowid.rowid_block_number(ROWID) = 163
AND    dbms_rowid.rowid_relative_fno(ROWID) = 4;

SELECT DBMS_ROWID.ROWID_CREATE(1,
                               (SELECT DATA_OBJECT_ID
                                  FROM DBA_OBJECTS
                                 WHERE OBJECT_ID = ROW_WAIT_OBJ#),
                               ROW_WAIT_FILE#,
                               ROW_WAIT_BLOCK#,
                               ROW_WAIT_ROW#),
       A.ROW_WAIT_OBJ#,
       A.ROW_WAIT_FILE#,
       A.ROW_WAIT_BLOCK#,
       A.ROW_WAIT_ROW#,
       (SELECT D.OWNER || '.' || D.OBJECT_NAME
          FROM DBA_OBJECTS D
         WHERE OBJECT_ID = ROW_WAIT_OBJ#) OBJECT_NAME
  FROM V$SESSION A
 WHERE A.ROW_WAIT_OBJ# <> -1;

 SELECT * FROM SYS.COM$ A WHERE A.ROWID='AAAACJAABAAAARGAAA';

SELECT tablespace_name,

segment_type,

owner,

segment_name,

partition_name

FROM dba_extents

WHERE file_id = &file_id

AND &block_id BETWEEN block_id AND block_id + blocks - 1

;

SELECT ENAME,SAL,ROWID,DBMS_ROWID.ROWID_CREATE(1,125628,147,4,7),DBMS_ROWID.ROWID_OBJECT(ROWID) OBJECT_ID,DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#,DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) RN FROM SCOTT.EMP WHERE EMPNO=7839;

SELECT ENAME,

SAL,

ROWID,

DBMS_ROWID.ROWID_CREATE(1, 125628, 147, 4, 7),

DBMS_ROWID.ROWID_OBJECT(ROWID) OBJECT_ID,

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#,

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,

DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) RN

FROM SCOTT.EMP

WHERE EMPNO = 7839;

SELECT ROWID,

dbms_rowid.rowid_object(ROWID) object_id,

dbms_rowid.rowid_relative_fno(ROWID) file_id,

dbms_rowid.rowid_block_number(ROWID) block_id,

d.*

FROM scott.SALGRADE d

WHERE dbms_rowid.rowid_block_number(ROWID) = 163

AND dbms_rowid.rowid_relative_fno(ROWID) = 4;

SELECT DBMS_ROWID.ROWID_CREATE(1,

(SELECT DATA_OBJECT_ID

FROM DBA_OBJECTS

WHERE OBJECT_ID = ROW_WAIT_OBJ#),

ROW_WAIT_FILE#,

ROW_WAIT_BLOCK#,

ROW_WAIT_ROW#),

A.ROW_WAIT_OBJ#,

A.ROW_WAIT_FILE#,

A.ROW_WAIT_BLOCK#,

A.ROW_WAIT_ROW#,

(SELECT D.OWNER || '.' || D.OBJECT_NAME

FROM DBA_OBJECTS D

WHERE OBJECT_ID = ROW_WAIT_OBJ#) OBJECT_NAME

FROM V$SESSION A

WHERE A.ROW_WAIT_OBJ# <> -1;

SELECT * FROM SYS.COM$ A WHERE A.ROWID='AAAACJAABAAAARGAAA';

元数据获取(表空间、用户、权限)

SELECT to_char(DBMS_METADATA.GET_DDL('TABLESPACE', a.tablespace_name))
  FROM DBA_TABLESPACES a
 where a.TABLESPACE_NAME = 'TS_LHR';

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')) DDL_SQL FROM DUAL;

SELECT ((DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'LHRSYS'))) FROM DUAL
UNION ALL
SELECT ((DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'LHRSYS'))) FROM DUAL
UNION ALL
SELECT ((DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'LHRSYS'))) FROM DUAL;

SELECT to_char(DBMS_METADATA.GET_DDL('TABLESPACE', a.tablespace_name))

FROM DBA_TABLESPACES a

where a.TABLESPACE_NAME = 'TS_LHR';

SELECT TO_CHAR(DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')) DDL_SQL FROM DUAL;

SELECT ((DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'LHRSYS'))) FROM DUAL

UNION ALL

SELECT ((DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'LHRSYS'))) FROM DUAL

UNION ALL

SELECT ((DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'LHRSYS'))) FROM DUAL;

查询表的历史统计信息

SELECT D.OWNER,
       D.TABLE_NAME,
       TO_CHAR(D.STATS_UPDATE_TIME, 'YYYY-MM-DD HH24:MI:SS') STATS_UPDATE_TIME
  FROM DBA_TAB_STATS_HISTORY D
 WHERE D.TABLE_NAME IN ('TPCCBOKBAL', 'TPCCBOKBAL_TMP', 'TPCCBOKBALJN')
 ORDER BY D.owner,D.table_name, D.stats_update_time;

SELECT B.OWNER,
       B.OBJECT_NAME TABLE_NAME, 
       TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,
       TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,
       D.ROWCNT
  FROM SYS.WRI$_OPTSTAT_TAB_HISTORY D, DBA_OBJECTS B
 WHERE D.OBJ# = B.OBJECT_ID
   AND B.OBJECT_NAME IN
       ('TEST_STAT', 'TPCCBOKBAL_TMP', 'TPCCBOKBALJN', 'PK_TPCCBOKBAL')
 ORDER BY D.OBJ#, D.SAVTIME;

SELECT D.OWNER,

D.TABLE_NAME,

TO_CHAR(D.STATS_UPDATE_TIME, 'YYYY-MM-DD HH24:MI:SS') STATS_UPDATE_TIME

FROM DBA_TAB_STATS_HISTORY D

WHERE D.TABLE_NAME IN ('TPCCBOKBAL', 'TPCCBOKBAL_TMP', 'TPCCBOKBALJN')

ORDER BY D.owner,D.table_name, D.stats_update_time;

SELECT B.OWNER,

B.OBJECT_NAME TABLE_NAME,

TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,

TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,

D.ROWCNT

FROM SYS.WRI$_OPTSTAT_TAB_HISTORY D, DBA_OBJECTS B

WHERE D.OBJ# = B.OBJECT_ID

AND B.OBJECT_NAME IN

('TEST_STAT', 'TPCCBOKBAL_TMP', 'TPCCBOKBALJN', 'PK_TPCCBOKBAL')

ORDER BY D.OBJ#, D.SAVTIME;

查询索引的历史统计信息

 SELECT B.OWNER,
       B.OBJECT_NAME INDEX_NAME,
       TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,
       TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,
       D.ROWCNT,
       D.BLEVEL,
             D.LEAFCNT,
             D.DISTKEY,
             D.CLUFAC
  FROM SYS.WRI$_OPTSTAT_IND_HISTORY D, DBA_OBJECTS B
 WHERE D.OBJ# = B.OBJECT_ID
   AND B.OBJECT_NAME IN ('IND_TEST')
 ORDER BY D.OBJ#, D.SAVTIME;

SELECT B.OWNER,

B.OBJECT_NAME INDEX_NAME,

TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,

TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,

D.ROWCNT,

D.BLEVEL,

D.LEAFCNT,

D.DISTKEY,

D.CLUFAC

FROM SYS.WRI$_OPTSTAT_IND_HISTORY D, DBA_OBJECTS B

WHERE D.OBJ# = B.OBJECT_ID

AND B.OBJECT_NAME IN ('IND_TEST')

ORDER BY D.OBJ#, D.SAVTIME;

表上列的使用情况

CREATE OR REPLACE VIEW VW_COLUMN_USAGE_LHR AS
SELECT oo.name             owner,
       o.name              table_name,
       c.name              column_name,
       u.equality_preds,
       u.equijoin_preds,
       u.nonequijoin_preds,
       u.range_preds,
       u.like_preds,
       u.null_preds,
       u.timestamp
  FROM sys.col_usage$ u, sys.obj$ o, sys.user$ oo, sys.col$ c
 WHERE o.obj# = u.obj#
   AND oo.user# = o.owner#
   AND c.obj# = u.obj#
   AND c.col# = u.intcol#
;

CREATE OR REPLACE VIEW VW_COLUMN_USAGE_LHR AS

SELECT oo.name owner,

o.name table_name,

c.name column_name,

u.equality_preds,

u.equijoin_preds,

u.nonequijoin_preds,

u.range_preds,

u.like_preds,

u.null_preds,

u.timestamp

FROM sys.col_usage$ u, sys.obj$ o, sys.user$ oo, sys.col$ c

WHERE o.obj# = u.obj#

AND oo.user# = o.owner#

AND c.obj# = u.obj#

AND c.col# = u.intcol#

;

查询字符集

0001  US7ASCII
0369  AL32UTF8
0354  ZHS16GBK

SELECT NLS_CHARSET_NAME(TO_NUMBER('0001', 'XXXX')) US7ASCII,
       NLS_CHARSET_NAME(TO_NUMBER('0354', 'XXXX')) ZHS16GBK,
       NLS_CHARSET_NAME(TO_NUMBER('0369', 'XXXX')) AL32UTF8,
       TO_CHAR(NLS_CHARSET_ID('US7ASCII'), 'XXXX') US7ASCII_ID,
       TO_CHAR(NLS_CHARSET_ID('ZHS16GBK'), 'XXXX') ZHS16GBK_ID,
       TO_CHAR(NLS_CHARSET_ID('AL32UTF8'), 'XXXX') AL32UTF8_ID
  FROM DUAL;

select userenv('language') from dual;
select  SYS_CONTEXT('USERENV', 'LANGUAGE') from dual;
select * from v$nls_parameters;

0001 US7ASCII

0369 AL32UTF8

0354 ZHS16GBK

SELECT NLS_CHARSET_NAME(TO_NUMBER('0001', 'XXXX')) US7ASCII,

NLS_CHARSET_NAME(TO_NUMBER('0354', 'XXXX')) ZHS16GBK,

NLS_CHARSET_NAME(TO_NUMBER('0369', 'XXXX')) AL32UTF8,

TO_CHAR(NLS_CHARSET_ID('US7ASCII'), 'XXXX') US7ASCII_ID,

TO_CHAR(NLS_CHARSET_ID('ZHS16GBK'), 'XXXX') ZHS16GBK_ID,

TO_CHAR(NLS_CHARSET_ID('AL32UTF8'), 'XXXX') AL32UTF8_ID

FROM DUAL;

select userenv('language') from dual;

select SYS_CONTEXT('USERENV', 'LANGUAGE') from dual;

select * from v$nls_parameters;

生成AWR

select * from table(dbms_workload_repository.awr_report_html(3424884828,1,1161,1165));
@$ORACLE_HOME/rdbms/admin/awrrpt.sql;

select * from DBA_HIST_WR_CONTROL;
select * from DBA_HIST_SNAPSHOT;
select * from DBA_HIST_ACTIVE_SESS_HISTORY;
select * from DBA_HIST_ASH_SNAPSHOT;

select * from DBA_HIST_SEG_STAT;
select * from DBA_HIST_SQLBIND; 
select * from DBA_HIST_SQLSTAT;
select * from DBA_HIST_SQLTEXT;
select * from DBA_HIST_SQL_BIND_METADATA;
select * from DBA_HIST_SQL_PLAN;

select * from table(dbms_workload_repository.awr_report_html(3424884828,1,1161,1165));

@$ORACLE_HOME/rdbms/admin/awrrpt.sql;

select * from DBA_HIST_WR_CONTROL;

select * from DBA_HIST_SNAPSHOT;

select * from DBA_HIST_ACTIVE_SESS_HISTORY;

select * from DBA_HIST_ASH_SNAPSHOT;

select * from DBA_HIST_SEG_STAT;

select * from DBA_HIST_SQLBIND;

select * from DBA_HIST_SQLSTAT;

select * from DBA_HIST_SQLTEXT;

select * from DBA_HIST_SQL_BIND_METADATA;

select * from DBA_HIST_SQL_PLAN;

AWR的SQL部分

select &begin_snap || '~' || &end_snap snap_id_range,
       (SELECT round(sum(db_time) / 1000000 / 60, 2) db_time_m
          FROM (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time
                  from dba_hist_sys_time_model a, dba_hist_snapshot b
                 where a.snap_id = b.snap_id
                   and a.dbid = b.dbid
                   and a.instance_number = b.instance_number
                   and a.stat_name = 'DB time'
                   and a.snap_id between &begin_snap and &end_snap)
         where db_time IS NOT NULL) "db_time(m)",
       round(nvl((sqt.elap / 1000000), to_number(null)), 2) "Elapsed Time (s)",
       round(nvl((sqt.cput / 1000000), to_number(null)), 2) "CPU Time (s)",
       round(nvl((sqt.iowait_delta / 1000000), to_number(null)), 2) "User I/O Time (s)",
       round(nvl((sqt.buffer_gets_delta), to_number(null)), 2) "Buffer Gets",
       round(nvl((sqt.disk_reads_delta), to_number(null)), 2) "Physical Reads",
       round(nvl((sqt.rows_processed_delta), to_number(null)), 2) "Rows Processed",
       round(nvl((sqt.parse_calls_delta), to_number(null)), 2) "Parse Calls",
       sqt.exec executions,
       round(decode(sqt.exec,
                    0,
                    to_number(null),
                    (sqt.elap / sqt.exec / 1000000)),
             2) "Elapsed Time per Exec (s)",
       round(decode(sqt.exec,
                    0,
                    to_number(null),
                    (sqt.cput / sqt.exec / 1000000)),
             2) "CPU per Exec (s)",
       round(decode(sqt.exec,
                    0,
                    to_number(null),
                    (sqt.iowait_delta / sqt.exec / 1000000)),
             2) "UIO per Exec (s)",
       round(sqt.cput * 100 / sqt.elap, 2) "%CPU",
       round(sqt.iowait_delta * 100 / sqt.elap, 2) "%IO",
       round(sqt.elap * 100 /
             (SELECT sum(db_time)
                FROM (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time
                        from dba_hist_sys_time_model a, dba_hist_snapshot b
                       where a.snap_id = b.snap_id
                         and a.dbid = b.dbid
                         and a.instance_number = b.instance_number
                         and a.stat_name = 'DB time'
                         and a.snap_id between &begin_snap and &end_snap)
               where db_time IS NOT NULL),
             2) "elapsed/dbtime",
       sqt.sql_id,
       parsing_schema_name,
       (decode(sqt.module, null, null, sqt.module)) module,
       nvl((select dbms_lob.substr(st.sql_text, 2000, 1)
             from dba_hist_sqltext st
            WHERE st.sql_id = sqt.sql_id
              and st.dbid = sqt.dbid),
           (' ** SQL Text Not Available ** ')) sql_text
  from (select sql_id,
               a.dbid,
               a.parsing_schema_name,
               max(module || '--' || a.action) module,
               sum(elapsed_time_delta) elap,
               sum(cpu_time_delta) cput,
               sum(executions_delta) exec,
               SUM(a.iowait_delta) iowait_delta,
               sum(a.buffer_gets_delta) buffer_gets_delta,
               sum(a.disk_reads_delta) disk_reads_delta,
               sum(a.rows_processed_delta) rows_processed_delta,
               sum(a.parse_calls_delta) parse_calls_delta
          from dba_hist_sqlstat a
         where &begin_snap < snap_id
           and snap_id <= &end_snap
         group by sql_id, parsing_schema_name, a.dbid) sqt
 order by nvl(sqt.elap, -1) desc, sqt.sql_id
;

select &begin_snap || '~' || &end_snap snap_id_range,

(SELECT round(sum(db_time) / 1000000 / 60, 2) db_time_m

FROM (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time

from dba_hist_sys_time_model a, dba_hist_snapshot b

where a.snap_id = b.snap_id

and a.dbid = b.dbid

and a.instance_number = b.instance_number

and a.stat_name = 'DB time'

and a.snap_id between &begin_snap and &end_snap)

where db_time IS NOT NULL) "db_time(m)",

round(nvl((sqt.elap / 1000000), to_number(null)), 2) "Elapsed Time (s)",

round(nvl((sqt.cput / 1000000), to_number(null)), 2) "CPU Time (s)",

round(nvl((sqt.iowait_delta / 1000000), to_number(null)), 2) "User I/O Time (s)",

round(nvl((sqt.buffer_gets_delta), to_number(null)), 2) "Buffer Gets",

round(nvl((sqt.disk_reads_delta), to_number(null)), 2) "Physical Reads",

round(nvl((sqt.rows_processed_delta), to_number(null)), 2) "Rows Processed",

round(nvl((sqt.parse_calls_delta), to_number(null)), 2) "Parse Calls",

sqt.exec executions,

round(decode(sqt.exec,

to_number(null),

(sqt.elap / sqt.exec / 1000000)),

2) "Elapsed Time per Exec (s)",

round(decode(sqt.exec,

to_number(null),

(sqt.cput / sqt.exec / 1000000)),

2) "CPU per Exec (s)",

round(decode(sqt.exec,

to_number(null),

(sqt.iowait_delta / sqt.exec / 1000000)),

2) "UIO per Exec (s)",

round(sqt.cput * 100 / sqt.elap, 2) "%CPU",

round(sqt.iowait_delta * 100 / sqt.elap, 2) "%IO",

round(sqt.elap * 100 /

(SELECT sum(db_time)

FROM (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time

from dba_hist_sys_time_model a, dba_hist_snapshot b

where a.snap_id = b.snap_id

and a.dbid = b.dbid

and a.instance_number = b.instance_number

and a.stat_name = 'DB time'

and a.snap_id between &begin_snap and &end_snap)

where db_time IS NOT NULL),

2) "elapsed/dbtime",

sqt.sql_id,

parsing_schema_name,

(decode(sqt.module, null, null, sqt.module)) module,

nvl((select dbms_lob.substr(st.sql_text, 2000, 1)

from dba_hist_sqltext st

WHERE st.sql_id = sqt.sql_id

and st.dbid = sqt.dbid),

(' ** SQL Text Not Available ** ')) sql_text

from (select sql_id,

a.dbid,

a.parsing_schema_name,

max(module || '--' || a.action) module,

sum(elapsed_time_delta) elap,

sum(cpu_time_delta) cput,

sum(executions_delta) exec,

SUM(a.iowait_delta) iowait_delta,

sum(a.buffer_gets_delta) buffer_gets_delta,

sum(a.disk_reads_delta) disk_reads_delta,

sum(a.rows_processed_delta) rows_processed_delta,

sum(a.parse_calls_delta) parse_calls_delta

from dba_hist_sqlstat a

where &begin_snap < snap_id

and snap_id <= &end_snap

group by sql_id, parsing_schema_name, a.dbid) sqt

order by nvl(sqt.elap, -1) desc, sqt.sql_id

;

AWR信息


---------  信息
select s.snap_date,
       snap_time_range,
       t.snap_id + 1 snap_id,
       decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME",
       startup_time,
       to_char(round(s.seconds / 60, 2)) "elapse(min)",
       round(t.db_time / 1000000 / 60, 2) "DB time(min)",
       s.redosize redo,
       round(s.redosize / s.seconds, 2) "redo/s",
       round(s.redosize / s.transactions, 2) "redo/t",
       s.logicalreads logical,
       round(s.logicalreads / s.seconds, 2) "logical/s",
       round(s.logicalreads / s.transactions, 2) "logical/t",
       physicalreads physical,
       round(s.physicalreads / s.seconds, 2) "phy/s",
       round(s.physicalreads / s.transactions, 2) "phy/t",
       s.executes execs,
       round(s.executes / s.seconds, 2) "execs/s",
       round(s.executes / s.transactions, 2) "execs/t",
       s.parse,
       round(s.parse / s.seconds, 2) "parse/s",
       round(s.parse / s.transactions, 2) "parse/t",
       s.hardparse,
       round(s.hardparse / s.seconds, 2) "hardparse/s",
       round(s.hardparse / s.transactions, 2) "hardparse/t",
       s.transactions trans,
       round(s.transactions / s.seconds, 2) "trans/s"
  from (select curr_redo - last_redo redosize,
               curr_logicalreads - last_logicalreads logicalreads,
               curr_physicalreads - last_physicalreads physicalreads,
               curr_executes - last_executes executes,
               curr_parse - last_parse parse,
               curr_hardparse - last_hardparse hardparse,
               curr_transactions - last_transactions transactions,
               round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds,
               to_char(currtime, 'yyyy-mm-dd') snap_date,
               to_char(currtime, 'hh24:mi') currtime,
               to_char(lasttime, 'YYYY-MM-DD HH24:MI') || '~' ||
               to_char(currtime, 'YYYY-MM-DD HH24:MI') snap_time_range,
               currsnap_id endsnap_id,
               to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time
          from (select a.redo last_redo,
                       a.logicalreads last_logicalreads,
                       a.physicalreads last_physicalreads,
                       a.executes last_executes,
                       a.parse last_parse,
                       a.hardparse last_hardparse,
                       a.transactions last_transactions,
                       lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo,
                       lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads,
                       lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads,
                       lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes,
                       lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse,
                       lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse,
                       lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions,
                       b.end_interval_time lasttime,
                       lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime,
                       lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id,
                       b.startup_time
                  from (select snap_id,
                               dbid,
                               instance_number,
                               sum(decode(stat_name, 'redo size', value, 0)) redo,
                               sum(decode(stat_name,
                                          'session logical reads',
                                          value,
                                          0)) logicalreads,
                               sum(decode(stat_name,
                                          'physical reads',
                                          value,
                                          0)) physicalreads,
                               sum(decode(stat_name, 'execute count', value, 0)) executes,
                               sum(decode(stat_name,
                                          'parse count (total)',
                                          value,
                                          0)) parse,
                               sum(decode(stat_name,
                                          'parse count (hard)',
                                          value,
                                          0)) hardparse,
                               sum(decode(stat_name,
                                          'user rollbacks',
                                          value,
                                          'user commits',
                                          value,
                                          0)) transactions
                          from dba_hist_sysstat
                         where stat_name in
                               ('redo size',
                                'session logical reads',
                                'physical reads',
                                'execute count',
                                'user rollbacks',
                                'user commits',
                                'parse count (hard)',
                                'parse count (total)')
                         group by snap_id, dbid, instance_number) a,
                       dba_hist_snapshot b
                 where a.snap_id = b.snap_id
                   and a.dbid = b.dbid
                   and a.instance_number = b.instance_number
                 order by end_interval_time)) s,
       (select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time,
               lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id,
               b.snap_id
          from dba_hist_sys_time_model a, dba_hist_snapshot b
         where a.snap_id = b.snap_id
           and a.dbid = b.dbid
           and a.instance_number = b.instance_number
           and a.stat_name = 'DB time') t
 where s.endsnap_id = t.endsnap_id
 order by s.snap_date desc, snap_id desc, time asc;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

--------- 信息

select s.snap_date,

snap_time_range,

t.snap_id + 1 snap_id,

decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME",

startup_time,

to_char(round(s.seconds / 60, 2)) "elapse(min)",

round(t.db_time / 1000000 / 60, 2) "DB time(min)",

s.redosize redo,

round(s.redosize / s.seconds, 2) "redo/s",

round(s.redosize / s.transactions, 2) "redo/t",

s.logicalreads logical,

round(s.logicalreads / s.seconds, 2) "logical/s",

round(s.logicalreads / s.transactions, 2) "logical/t",

physicalreads physical,

round(s.physicalreads / s.seconds, 2) "phy/s",

round(s.physicalreads / s.transactions, 2) "phy/t",

s.executes execs,

round(s.executes / s.seconds, 2) "execs/s",

round(s.executes / s.transactions, 2) "execs/t",

s.parse,

round(s.parse / s.seconds, 2) "parse/s",

round(s.parse / s.transactions, 2) "parse/t",

s.hardparse,

round(s.hardparse / s.seconds, 2) "hardparse/s",

round(s.hardparse / s.transactions, 2) "hardparse/t",

s.transactions trans,

round(s.transactions / s.seconds, 2) "trans/s"

from (select curr_redo - last_redo redosize,

curr_logicalreads - last_logicalreads logicalreads,

curr_physicalreads - last_physicalreads physicalreads,

curr_executes - last_executes executes,

curr_parse - last_parse parse,

curr_hardparse - last_hardparse hardparse,

curr_transactions - last_transactions transactions,

round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds,

to_char(currtime, 'yyyy-mm-dd') snap_date,

to_char(currtime, 'hh24:mi') currtime,

to_char(lasttime, 'YYYY-MM-DD HH24:MI') || '~' ||

to_char(currtime, 'YYYY-MM-DD HH24:MI') snap_time_range,

currsnap_id endsnap_id,

to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time

from (select a.redo last_redo,

a.logicalreads last_logicalreads,

a.physicalreads last_physicalreads,

a.executes last_executes,

a.parse last_parse,

a.hardparse last_hardparse,

a.transactions last_transactions,

lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo,

lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads,

lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads,

lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes,

lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse,

lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse,

lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions,

b.end_interval_time lasttime,

lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime,

lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id,

b.startup_time

from (select snap_id,

dbid,

instance_number,

sum(decode(stat_name, 'redo size', value, 0)) redo,

sum(decode(stat_name,

'session logical reads',

value,

0)) logicalreads,

sum(decode(stat_name,

'physical reads',

value,

0)) physicalreads,

sum(decode(stat_name, 'execute count', value, 0)) executes,

sum(decode(stat_name,

'parse count (total)',

value,

0)) parse,

sum(decode(stat_name,

'parse count (hard)',

value,

0)) hardparse,

sum(decode(stat_name,

'user rollbacks',

value,

'user commits',

value,

0)) transactions

from dba_hist_sysstat

where stat_name in

('redo size',

'session logical reads',

'physical reads',

'execute count',

'user rollbacks',

'user commits',

'parse count (hard)',

'parse count (total)')

group by snap_id, dbid, instance_number) a,

dba_hist_snapshot b

where a.snap_id = b.snap_id

and a.dbid = b.dbid

and a.instance_number = b.instance_number

order by end_interval_time)) s,

(select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time,

lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id,

b.snap_id

from dba_hist_sys_time_model a, dba_hist_snapshot b

where a.snap_id = b.snap_id

and a.dbid = b.dbid

and a.instance_number = b.instance_number

and a.stat_name = 'DB time') t

where s.endsnap_id = t.endsnap_id

order by s.snap_date desc, snap_id desc, time asc;

AWR主机信息


 ----主机信息
 SELECT s.snap_id,
       DB_NAME,
       s.dbid,
       INSTANCE_NAME,
       s.instance_number,
       s.startup_time,
       Version           Release,
       PARALLEL          RAC,
       HOST_NAME,
       di.platform_name,
       v.cpus            CPUS,
       v.cores,
       v.sockets,
       v.Memory          "Memory (GB)"
FROM   DBA_HIST_DATABASE_INSTANCE di,
       DBA_HIST_SNAPSHOT s,
       (SELECT snap_id,
               dbid,
               instance_number,
               SUM(CPUs) CPUs,
               SUM(Cores) Cores,
               SUM(Sockets) Sockets,
               SUM(Memory) Memory
        FROM   (SELECT o.snap_id,
                       o.dbid,
                       o.instance_number,
                       decode(o.stat_name, 'NUM_CPUS', o.value) CPUs,
                       decode(o.stat_name, 'NUM_CPU_CORES', o.value) Cores,
                       decode(o.stat_name, 'NUM_CPU_SOCKETS', o.value) Sockets,
                       decode(o.stat_name,
                              'PHYSICAL_MEMORY_BYTES',
                              trunc(o.value / 1024 / 1024 / 1024, 2)) Memory
                FROM   dba_hist_osstat o
                WHERE  o.stat_name IN
                       ('NUM_CPUS',
                        'NUM_CPU_CORES',
                        'NUM_CPU_SOCKETS',
                        'PHYSICAL_MEMORY_BYTES'))
        GROUP  BY snap_id,
                  dbid,
                  instance_number) v
WHERE  s.instance_number = di.instance_number
AND    s.startup_time = di.startup_time
AND    s.dbid = di.dbid
AND    s.snap_id = v.snap_id
AND    s.dbid = s.dbid
AND    s.instance_number = v.instance_number;

----主机信息

SELECT s.snap_id,

DB_NAME,

s.dbid,

INSTANCE_NAME,

s.instance_number,

s.startup_time,

Version Release,

PARALLEL RAC,

HOST_NAME,

di.platform_name,

v.cpus CPUS,

v.cores,

v.sockets,

v.Memory "Memory (GB)"

FROM DBA_HIST_DATABASE_INSTANCE di,

DBA_HIST_SNAPSHOT s,

(SELECT snap_id,

dbid,

instance_number,

SUM(CPUs) CPUs,

SUM(Cores) Cores,

SUM(Sockets) Sockets,

SUM(Memory) Memory

FROM (SELECT o.snap_id,

o.dbid,

o.instance_number,

decode(o.stat_name, 'NUM_CPUS', o.value) CPUs,

decode(o.stat_name, 'NUM_CPU_CORES', o.value) Cores,

decode(o.stat_name, 'NUM_CPU_SOCKETS', o.value) Sockets,

decode(o.stat_name,

'PHYSICAL_MEMORY_BYTES',

trunc(o.value / 1024 / 1024 / 1024, 2)) Memory

FROM dba_hist_osstat o

WHERE o.stat_name IN

('NUM_CPUS',

'NUM_CPU_CORES',

'NUM_CPU_SOCKETS',

'PHYSICAL_MEMORY_BYTES'))

GROUP BY snap_id,

dbid,

instance_number) v

WHERE s.instance_number = di.instance_number

AND s.startup_time = di.startup_time

AND s.dbid = di.dbid

AND s.snap_id = v.snap_id

AND s.dbid = s.dbid

AND s.instance_number = v.instance_number;

查询碎片程度高的表

条件为什么block>100，因为一些很小的表，只有几行数据实际大小很小，但是block一次性分配就是5个（11g开始默认一次性分配1M的block大小了，见create table storged的NEXT参数），5个block相对于几行小表数据来说就相差太大了。

算法中/0.9是因为块的pfree一般为10%，所以一个块最多只用了90%，而且一行数据大于8KB时容易产生行链接，把一行分片存储，一样的一个块连90%都用不满、

AVG_ROW_LEN还是比较准的，比如个人实验情况一表6个字段，一个number，其他5个都是char(100)但是实际数据都是’1111111’7位，AVG_ROW_LEN显示依然为513

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

1 2	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;

集群因子clustering_factor高的表

集群因子越接近块数越好，接近行数则说明索引列的列值相等的行分布极度散列，可能不走索引扫描而走全表扫描

select tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor,
round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "集群因子接近行数"
from user_tables tab, user_indexes ind where tab.table_name=ind.table_name
and tab.blocks>100
and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3

select tab.table_name,tab.blocks,tab.num_rows,ind.index_name,ind.clustering_factor,

round(nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows),3)*100||'%' "集群因子接近行数"

from user_tables tab, user_indexes ind where tab.table_name=ind.table_name

and tab.blocks>100

and nvl(ind.clustering_factor,1)/decode(tab.num_rows,0,1,tab.num_rows) between 0.35 and 3

根据sid查spid或根据spid查sid

select s.sid,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal 
from v$session s,v$process p where s.paddr=p.addr and s.sid=XX or p.spid=YY

1 2	select s.sid,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal from v$session s,v$process p where s.paddr=p.addr and s.sid=XX or p.spid=YY

根据sid查看具体的sql语句

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;

1 2	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;

根据spid查询具体的sql语句

select ss.SID, pr.SPID, ss.action, sa.SQL_FULLTEXT, ss.TERMINAL, 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 = XX;

select ss.SID, pr.SPID, ss.action, sa.SQL_FULLTEXT, ss.TERMINAL, 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 = XX;

查看历史session_id的SQL来自哪个IP

(当然这是个误解，都是历史的了，怎么可能还查到spid，其实查看trace文件名就可以知道spid,trace文件里面有sid和具体sql，如果trace存在incident，那trace就看不到具体sql，但是可以在incident文件中看到具体的sql，如DW_ora_17751.trc中17751就是spid，里面有这样的内容Incident 115 created, dump file: /XX/incident/incdir_115/DW_ora_17751_i115.trc，那么在DW_ora_17751_i115.trc就可以看到具体的sql语句)

DB_ora_29349.trc中出现如下

*** SESSION ID:(5057.12807) 2016-10-26 14:45:52.726

通过表V$ACTIVE_SESSION_HISTORY来查，如下

select a.sql_id,a.machine,a.* from V$ACTIVE_SESSION_HISTORY a where a.session_id=5057 and a.SESSION_SERIAL#=12807

1	select a.sql_id,a.machine,a.* from V$ACTIVE_SESSION_HISTORY a where a.session_id=5057 and a.SESSION_SERIAL#=12807

查询上面的machine的IP是多少

select s.sid,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal from v$session s,v$process p where s.paddr=p.addr and s.machine='localhost'

1	select s.sid,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal from v$session s,v$process p where s.paddr=p.addr and s.machine='localhost'

通过上面的spid在oracle服务器上执行netstat -anp |grep spid即可

[oracle@dwdb trace]$ netstat -anp |grep 17630
tcp   210   0 192.168.64.228:11095    192.168.21.16:1521     ESTABLISHED 17630/oracleDB
tcp    0   0 ::ffff:192.168.64.228:1521 ::ffff:192.168.64.220:59848 ESTABLISHED 17630/oracleDB

[oracle@dwdb trace]$ netstat -anp |grep 17630

tcp 210 0 192.168.64.228:11095 192.168.21.16:1521 ESTABLISHED 17630/oracleDB

tcp 0 0 ::ffff:192.168.64.228:1521 ::ffff:192.168.64.220:59848 ESTABLISHED 17630/oracleDB

出现两个，说明来自220，连接了228数据库服务器，但是又通过228服务器的dblink去连接了16服务器

查询DML死锁会话sid，及引起死锁的堵塞者会话blocking_session

select sid, blocking_session, LOGON_TIME,sql_id,status,event,seconds_in_wait,state, BLOCKING_SESSION_STATUS
from v$session where event like 'enq%' and state='WAITING' and BLOCKING_SESSION_STATUS='VALID'

1 2	select sid, blocking_session, LOGON_TIME,sql_id,status,event,seconds_in_wait,state, BLOCKING_SESSION_STATUS from v$session where event like 'enq%' and state='WAITING' and BLOCKING_SESSION_STATUS='VALID'

BLOCKING_SESSION:Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID.

可以在v$session.LOGON_TIME上看到引起死锁的堵塞者会话比等待者要早

如果遇到RAC环境，一定要用gv$来查，并且执行alter system kill session 'sid,serial#'要到RAC对应的实例上去执行

或如下也可以

select
    (select username from v$session where sid=a.sid) blocker,
   a.sid,
   a.id1,
   a.id2,
  ' is blocking ' "IS BLOCKING",
   (select username from v$session where sid=b.sid) blockee,
     b.sid
  from v$lock a, v$lock b
  where a.block = 1
   and b.request > 0
   and a.id1 = b.id1
   and a.id2 = b.id2;

select

(select username from v$session where sid=a.sid) blocker,

a.sid,

a.id1,

a.id2,

' is blocking ' "IS BLOCKING",

(select username from v$session where sid=b.sid) blockee,

b.sid

from v$lock a, v$lock b

where a.block = 1

and b.request > 0

and a.id1 = b.id1

and a.id2 = b.id2;

查询DDL锁的sql

SELECT sid, event, p1raw, seconds_in_wait, wait_time
FROM sys.v_$session_wait
WHERE event like 'library cache %'

p1raw结果为'0000000453992440'

SELECT s.sid, kglpnmod "Mode", kglpnreq "Req", s.LOGON_TIME
FROM x$kglpn p, v$session s
WHERE p.kglpnuse=s.saddr
AND kglpnhdl='0000000453992440';

SELECT sid, event, p1raw, seconds_in_wait, wait_time

FROM sys.v_$session_wait

WHERE event like 'library cache %'

p1raw结果为'0000000453992440'

SELECT s.sid, kglpnmod "Mode", kglpnreq "Req", s.LOGON_TIME

FROM x$kglpn p, v$session s

WHERE p.kglpnuse=s.saddr

AND kglpnhdl='0000000453992440';

结果为671 0 3 2011-11-1 12:00:00

525 2 0 2011-11-4 12:00:00

查询锁住的DDL对象

select d.session_id,s.SERIAL#,d.name from dba_ddl_locks d,v$session s where d.owner='MKLMIGEM' and d.SESSION_ID=s.sid

1	select d.session_id,s.SERIAL#,d.name from dba_ddl_locks d,v$session s where d.owner='MKLMIGEM' and d.SESSION_ID=s.sid

查询当前正在执行的sql

SELECT s.sid,s.serial#,s.username,spid,v$sql.sql_id,machine,s.terminal,s.program,sql_text
FROM v$process,v$session s,v$sql 
WHERE addr=paddr and s.sql_id=v$sql.sql_id AND sql_hash_value=hash_value

SELECT s.sid,s.serial#,s.username,spid,v$sql.sql_id,machine,s.terminal,s.program,sql_text

FROM v$process,v$session s,v$sql

WHERE addr=paddr and s.sql_id=v$sql.sql_id AND sql_hash_value=hash_value

查询正在执行的SCHEDULER_JOB


select owner,job_name,sid,b.SERIAL#,b.username,spid 
from ALL_SCHEDULER_RUNNING_JOBS,v$session b,v$process
where session_id=sid and paddr=addr

select owner,job_name,sid,b.SERIAL#,b.username,spid

from ALL_SCHEDULER_RUNNING_JOBS,v$session b,v$process

where session_id=sid and paddr=addr

查询正在执行的dbms_job

select job,b.sid,b.SERIAL#,b.username,spid 
from DBA_JOBS_RUNNING a ,v$session b,v$process
where a.sid=b.sid and paddr=addr

select job,b.sid,b.SERIAL#,b.username,spid

from DBA_JOBS_RUNNING a ,v$session b,v$process

where a.sid=b.sid and paddr=addr

查询一个会话session、process平均消耗多少内存，查看下面avg_used_M值


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

TOP 10 执行次数排序


select *
from (select executions,username,PARSING_USER_ID,sql_id,sql_text 
  from v$sql,dba_users where user_id=PARSING_USER_ID order by executions desc)
where rownum <=5;

select *

from (select executions,username,PARSING_USER_ID,sql_id,sql_text

from v$sql,dba_users where user_id=PARSING_USER_ID order by executions desc)

where rownum <=5;

TOP 10 物理读排序

select *
from (select DISK_READS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text 
  from v$sql,dba_users where user_id=PARSING_USER_ID order by DISK_READS desc)
where rownum <=5;

select *

from (select DISK_READS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text

from v$sql,dba_users where user_id=PARSING_USER_ID order by DISK_READS desc)

where rownum <=5;

（不要使用DISK_READS/ EXECUTIONS来排序，因为任何一条语句不管执行几次都会耗逻辑读和cpu，可能不会耗物理读（遇到LRU还会耗物理读，LRU规则是执行最不频繁的且最后一次执行时间距离现在最久远的就会被交互出buffer cache），是因为buffer cache存放的是数据块，去数据块里找行一定会消耗cpu和逻辑读的。Shared pool执行存放sql的解析结果，sql执行的时候只是去share pool中找hash value，如果有匹配的就是软解析。所以物理读逻辑读是在buffer cache中，软解析硬解析是在shared pool）

TOP 10 逻辑读排序


select *
from (select BUFFER_GETS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text 
  from v$sql,dba_users where user_id=PARSING_USER_ID order by BUFFER_GETS desc)
where rownum <=5;

select *

from (select BUFFER_GETS,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text

from v$sql,dba_users where user_id=PARSING_USER_ID order by BUFFER_GETS desc)

where rownum <=5;

（不要使用BUFFER_GETS/ EXECUTIONS来排序，原因同16）

TOP 10 CPU排序


select *
from (select CPU_TIME/1000000,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text 
  from v$sql,dba_users where user_id=PARSING_USER_ID order by CPU_TIME/1000000 desc)
where rownum <=5;

select *

from (select CPU_TIME/1000000,username,PARSING_USER_ID,sql_id,ELAPSED_TIME/1000000,sql_text

from v$sql,dba_users where user_id=PARSING_USER_ID order by CPU_TIME/1000000 desc)

where rownum <=5;

（不要使用CPU_TIME/ EXECUTIONS来排序，原因同16）

查询等待事件


select a.inst_id, a.EVENT, count(*) counts
  from gv$session a
 where a.status = 'ACTIVE'
 group by a.inst_id,a.EVENT   
 having count(*) > 1
 order by a.inst_id,counts desc;

select a.EVENT, count(*) counts
  from v$session a
 where a.status = 'ACTIVE'
 group by a.EVENT   
 having count(*) > 1
 order by counts desc;

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 a.inst_id, a.EVENT, count(*) counts

from gv$session a

where a.status = 'ACTIVE'

group by a.inst_id,a.EVENT

having count(*) > 1

order by a.inst_id,counts desc;

select a.EVENT, count(*) counts

from v$session a

where a.status = 'ACTIVE'

group by a.EVENT

having count(*) > 1

order by counts desc;

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;

查询当前正在消耗temp空间的sql语句

Select distinct se.username,
       se.sid,
       su.blocks * to_number(rtrim(p.value))/1024/1024 as space_G,
       su.tablespace,
       sql_text
  from V$TEMPSEG_USAGE su, v$parameter p, v$session se, v$sql s
  where p.name = 'db_block_size'
   and su.session_addr=se.saddr
   and su.sqlhash=s.hash_value
   and su.sqladdr=s.address

Select distinct se.username,

se.sid,

su.blocks * to_number(rtrim(p.value))/1024/1024 as space_G,

su.tablespace,

sql_text

from V$TEMPSEG_USAGE su, v$parameter p, v$session se, v$sql s

where p.name = 'db_block_size'

and su.session_addr=se.saddr

and su.sqlhash=s.hash_value

and su.sqladdr=s.address

查询需要使用绑定变量的sql,10G以后推荐第二种

（任何一条执行过的语句不管执行了几次在V$SQL中都只有一条记录，V$SQL中会记录执行了几次。两条一模一样的语句但是在不同的schema下执行的两种结果，如select from t1.test在sye、system下执行则V$SQL只有一条记录(谁先执行则PARSING_SCHEMA_NAME显示谁)。如在sys和system都执行select from test则V$SQL中有两条记录，两条记录的CHILD_NUMBER和PARSING_SCHEMA_NAME不一样。同一个用户下执行一样的语句如果大小写不一样或加了hint的话则会出现多个V$SQL记录，说明V$SQL对应的sql语句必须一模一样，如果alter system flush shared_pool（主站慎用）后再执行一样的语句，发现语句在V$SQL中的SQL_ID和HASH_VALUE与之前的一样，说明SQL_ID和HASH_VALUE应该是oracle自己的一套算法来的，只是根据sql语句内容来进行转换，sql语句不变则SQL_ID和HASH_VALUE也不变。）

第一种


select * from (
select count(*),sql_id, substr(sql_text,1,40)
from v$sql
group by sql_id, substr(sql_text,1,40) having count(*) > 10 order by count(*) desc) where rownum<10

select * from (

select count(*),sql_id, substr(sql_text,1,40)

from v$sql

group by sql_id, substr(sql_text,1,40) having count(*) > 10 order by count(*) desc) where rownum<10

第二种

count(1)>10表示类语句运行了10次以上

select sql_id, FORCE_MATCHING_SIGNATURE, sql_text
from v$SQL
where FORCE_MATCHING_SIGNATURE in
(select /*+ unnest */
FORCE_MATCHING_SIGNATURE
from v$sql
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 10)

select sql_id, FORCE_MATCHING_SIGNATURE, sql_text

from v$SQL

where FORCE_MATCHING_SIGNATURE in

(select /*+ unnest */

FORCE_MATCHING_SIGNATURE

from v$sql

where FORCE_MATCHING_SIGNATURE > 0

and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE

group by FORCE_MATCHING_SIGNATURE

having count(1) > 10)

查看数据文件可用百分比

select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE,
ROUND(b.bytes/1024/1024/1024,2) ||'G' "文件总容量",
ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "文件已用容量",
ROUND(sum(nvl(a.bytes,0))/1024/1024/1024,2)||'G' "文件可用容量",
ROUND(sum(nvl(a.bytes,0))/(b.bytes),2)*100||'%' "文件可用百分比"
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE
order by b.tablespace_name;

select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE,

ROUND(b.bytes/1024/1024/1024,2) ||'G' "文件总容量",

ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "文件已用容量",

ROUND(sum(nvl(a.bytes,0))/1024/1024/1024,2)||'G' "文件可用容量",

ROUND(sum(nvl(a.bytes,0))/(b.bytes),2)*100||'%' "文件可用百分比"

from dba_free_space a,dba_data_files b

where a.file_id=b.file_id

group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE

order by b.tablespace_name;

查看数据文件可用百分比

select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE,
ROUND(b.MAXBYTES/1024/1024/1024,2) ||'G' "文件最大可用总容量",
ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "文件已用容量",
ROUND(((b.MAXBYTES/1024/1024/1024)-((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024))/(b.MAXBYTES/1024/1024/1024),2)*100||'%' "文件可用百分比"
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id and b.file_id>4
group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE,b.MAXBYTES
order by b.tablespace_name;

select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE,

ROUND(b.MAXBYTES/1024/1024/1024,2) ||'G' "文件最大可用总容量",

ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "文件已用容量",

ROUND(((b.MAXBYTES/1024/1024/1024)-((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024))/(b.MAXBYTES/1024/1024/1024),2)*100||'%' "文件可用百分比"

from dba_free_space a,dba_data_files b

where a.file_id=b.file_id and b.file_id>4

group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE,b.MAXBYTES

order by b.tablespace_name;

查看表空间可用百分比


select b.tablespace_name,a.total,b.free,round((b.free/a.total)*100) "% Free" from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
order by "% Free";

select b.tablespace_name,a.total,b.free,round((b.free/a.total)*100) "% Free" from

(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,

(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name

order by "% Free";

查看临时表空间使用率

SELECT temp_used.tablespace_name,total,used,
      total - used as "Free",
      round(nvl(total-used, 0) * 100/total,3) "Free percent"
  FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used
       FROM GV_$TEMP_SPACE_HEADER
       GROUP BY tablespace_name) temp_used,
      (SELECT tablespace_name, SUM(bytes)/1024/1024 total
       FROM dba_temp_files
       GROUP BY tablespace_name) temp_total
  WHERE temp_used.tablespace_name = temp_total.tablespace_name

SELECT temp_used.tablespace_name,total,used,

total - used as "Free",

round(nvl(total-used, 0) * 100/total,3) "Free percent"

FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used

FROM GV_$TEMP_SPACE_HEADER

GROUP BY tablespace_name) temp_used,

(SELECT tablespace_name, SUM(bytes)/1024/1024 total

FROM dba_temp_files

GROUP BY tablespace_name) temp_total

WHERE temp_used.tablespace_name = temp_total.tablespace_name

查询undo表空间使用情况

select tablespace_name,status,sum(bytes)/1024/1024 M from dba_undo_extents group by tablespace_name,status

1	select tablespace_name,status,sum(bytes)/1024/1024 M from dba_undo_extents group by tablespace_name,status

查看ASM磁盘组使用率


select name,round(total_mb/1024) "总容量",round(free_mb/1024) "空闲空间",round((free_mb/total_mb)*100) "可用空间比例" 
from gv$asm_diskgroup

select name,round(total_mb/1024) "总容量",round(free_mb/1024) "空闲空间",round((free_mb/total_mb)*100) "可用空间比例"

from gv$asm_diskgroup

统计每个用户使用表空间率

SELECT c.owner                 "用户",
   a.tablespace_name            "表空间名",
   total/1024/1024             "表空间大小M",
   free/1024/1024              "表空间剩余大小M",
   ( total - free )/1024/1024        "表空间使用大小M",
   Round(( total - free ) / total, 4) * 100 "表空间总计使用率  %",
   c.schemas_use/1024/1024         "用户使用表空间大小M",
   round((schemas_use)/total,4)*100     "用户使用表空间率 %"
FROM  (SELECT tablespace_name,
       Sum(bytes) free
   FROM  DBA_FREE_SPACE
   GROUP BY tablespace_name) a,
   (SELECT tablespace_name,
       Sum(bytes) total
   FROM  DBA_DATA_FILES
   GROUP BY tablespace_name) b,
   (Select owner ,Tablespace_Name,
       Sum(bytes) schemas_use
   From Dba_Segments
   Group By owner,Tablespace_Name) c
WHERE a.tablespace_name = b.tablespace_name
and a.tablespace_name =c.Tablespace_Name
order by "用户","表空间名"

SELECT c.owner "用户",

a.tablespace_name "表空间名",

total/1024/1024 "表空间大小M",

free/1024/1024 "表空间剩余大小M",

( total - free )/1024/1024 "表空间使用大小M",

Round(( total - free ) / total, 4) * 100 "表空间总计使用率 %",

c.schemas_use/1024/1024 "用户使用表空间大小M",

round((schemas_use)/total,4)*100 "用户使用表空间率 %"

FROM (SELECT tablespace_name,

Sum(bytes) free

FROM DBA_FREE_SPACE

GROUP BY tablespace_name) a,

(SELECT tablespace_name,

Sum(bytes) total

FROM DBA_DATA_FILES

GROUP BY tablespace_name) b,

(Select owner ,Tablespace_Name,

Sum(bytes) schemas_use

From Dba_Segments

Group By owner,Tablespace_Name) c

WHERE a.tablespace_name = b.tablespace_name

and a.tablespace_name =c.Tablespace_Name

order by "用户","表空间名"

查看闪回区\快速恢复区空间使用率


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

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

查看僵死进程，分两种

alter system kill session一执行则session即标记为KILLED，但是如果会话产生的数据量大则这个kill可能会比较久，在这个过程中session标记为KILLED但是这个会话还在V$session中，则V$session.paddr还在，所以可以匹配到V$process.addr，所以process进程还在；当kill过程执行完毕，则这个会话即不在V$session中

会话不在的


select * from v$process where addr not in (select paddr from v$session) and pid not in (1,17,18)

1 2	select * from v$process where addr not in (select paddr from v$session) and pid not in (1,17,18)

会话还在的，但是会话标记为killed


select * from v$process where addr in (select paddr from v$session where status='KILLED')

1 2	select * from v$process where addr in (select paddr from v$session where status='KILLED')

再根据上述结果中的SPID通过如下命令可以查看到process的启动时间


ps auxw|head -1;ps auxw|grep SPID

1 2	ps auxw\|head -1;ps auxw\|grep SPID

查看行迁移或行链接的表

select * From dba_tables where nvl(chain_cnt,0)<>0

1	select * From dba_tables where nvl(chain_cnt,0)<>0

chain_cnt ：Number of rows in the table that are chained from one data block to another or that have migrated to a new block, requiring a link to preserve the old rowid. This column is updated only after you analyze the table.

数据缓冲区命中率

SELECT a.VALUE+b.VALUE logical_reads, c.VALUE phys_reads,
round(100*(1-c.value/(a.value+b.value)),2)||'%' hit_ratio
FROM v$sysstat a,v$sysstat b,v$sysstat c
WHERE a.NAME='db block gets'
AND b.NAME='consistent gets'
AND c.NAME='physical reads';
或
SELECT DB_BLOCK_GETS+CONSISTENT_GETS Logical_reads,PHYSICAL_READS phys_reads,
round(100*(1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS))),2)||'%' "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS WHERE NAME='DEFAULT';

SELECT a.VALUE+b.VALUE logical_reads, c.VALUE phys_reads,

round(100*(1-c.value/(a.value+b.value)),2)||'%' hit_ratio

FROM v$sysstat a,v$sysstat b,v$sysstat c

WHERE a.NAME='db block gets'

AND b.NAME='consistent gets'

AND c.NAME='physical reads';

或

SELECT DB_BLOCK_GETS+CONSISTENT_GETS Logical_reads,PHYSICAL_READS phys_reads,

round(100*(1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS))),2)||'%' "Hit Ratio"

FROM V$BUFFER_POOL_STATISTICS WHERE NAME='DEFAULT';

共享池命中率

以下两者应该都可以，看个人怎么理解

select sum(pinhits)/sum(pins)*100 from v$librarycache;

select sum(pinhits-reloads)/sum(pins)*100 from v$librarycache;

select sum(pinhits)/sum(pins)*100 from v$librarycache;

select sum(pinhits-reloads)/sum(pins)*100 from v$librarycache;

查询归档日志切换频率


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,minutes;

或

select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') First_time,First_change#,switch_change# from
v$loghist where first_time>sysdate-3 order by 1;

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,minutes;

或

select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') First_time,First_change#,switch_change# from

v$loghist where first_time>sysdate-3 order by 1;

查询lgwr进程写日志时每执行一次lgwr需要多少秒

在state是waiting的情况下，某个等待编号seq#下，seconds_in_wait达多少秒，就是lgwr进程写一次IO需要多少秒


select event,state,seq#,seconds_in_wait,program from v$session where program like '%LGWR%' and state='WAITING'

1 2	select event,state,seq#,seconds_in_wait,program from v$session where program like '%LGWR%' and state='WAITING'

查询没有索引的表


Select table_name from user_tables where table_name not in (select table_name from user_indexes)

Select table_name from user_tables where table_name not in (select table_name from user_ind_columns)

Select table_name from user_tables where table_name not in (select table_name from user_indexes)

Select table_name from user_tables where table_name not in (select table_name from user_ind_columns)

查询7天的db time

TH 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

TH 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

查询产生热块较多的对象

x$bh .tch(Touch)表示访问次数越高，热点快竞争问题就存在

SELECT e.owner, e.segment_name, e.segment_type
FROM dba_extents e,
(SELECT *
FROM (SELECT addr,ts#,file#,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;

SELECT e.owner, e.segment_name, e.segment_type

FROM dba_extents e,

(SELECT *

FROM (SELECT addr,ts#,file#,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;

导出AWR报告的SQL语句

select * from dba_hist_snapshot

select * 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));

select * from dba_hist_snapshot

select * 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));

查询某个SQL的执行计划

select a.hash_value,a.* from v$sql a where sql_id='0n4qfzbqfsjm3'

select * from table(dbms_xplan.display_cursor(v$sql.hash_value,0,'advanced'));

select a.hash_value,a.* from v$sql a where sql_id='0n4qfzbqfsjm3'

select * from table(dbms_xplan.display_cursor(v$sql.hash_value,0,'advanced'));

含顺序的

select * from table(xplan.display_cursor('v$sql.sql_id',0,'advanced'));

1	select * from table(xplan.display_cursor('v$sql.sql_id',0,'advanced'));

不过要先创建xplan包，再执行

SQL> CREATE PUBLIC SYNONYM XPLAN FOR SYS.XPLAN;

SQL> grant execute on sys.xplan to public;

SQL> CREATE PUBLIC SYNONYM XPLAN FOR SYS.XPLAN;

SQL> grant execute on sys.xplan to public;

在 Oracle 中生成随机数值

这是 Oracle 普通的旧的随机数值生成器。这个可以生成 0-100 之间的随机数值，如果你想自己设置数值范围，那么改变乘数就可以了。
--generate random number between 0 and 100


SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL;

1 2	SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL;

检查表中是否含有任何的数据

这个可以有很多中写法，你可以使用 count(*) 来查看表里的行的数量，但是这个查询语句比较高效和快速，而且我们只是想知道表里是否有任何的数据。

SELECT 1
 FROM TABLE_NAME
WHERE ROWNUM = 1;

SELECT 1

FROM TABLE_NAME

WHERE ROWNUM = 1;

把数值转换成文字

SELECT TO_CHAR (TO_DATE (1526, 'j'), 'jsp') FROM DUAL;

1	SELECT TO_CHAR (TO_DATE (1526, 'j'), 'jsp') FROM DUAL;

输出
one thousand five hundred twenty-six

在包的源代码中查询字符串

这个查询语句会在所有包的源代码上搜索‘FOO_SOMETHING’ ，可以帮助用户在源代码中查找特定的存储过程或者是函数调用。

search a string foo_something in package source code


SELECT *
 FROM dba_source
WHERE UPPER (text) LIKE '%FOO_SOMETHING%'
AND owner = 'USER_NAME';

SELECT *

FROM dba_source

WHERE UPPER (text) LIKE '%FOO_SOMETHING%'

AND owner = 'USER_NAME';

把用逗号分隔的数据插入的表中

当你想把用逗号分隔开的字符串插入表中的时候，你可以使用其他的查询语句，比如 IN 或者是 NOT IN 。这里我们把‘AA,BB,CC,DD,EE,FF’转换成包含 AA,BB，CC 等作为一行的表，这样你就很容易把这些字符串插入到其他表中，并快速的做一些相关的操作。


WITH csv
   AS (SELECT 'AA,BB,CC,DD,EE,FF'
          AS csvdata
     FROM DUAL)
  SELECT REGEXP_SUBSTR (csv.csvdata, '[^,]+', 1, LEVEL) pivot_char
   FROM DUAL, csv
CONNECT BY REGEXP_SUBSTR (csv.csvdata,'[^,]+', 1, LEVEL) IS NOT NULL;

WITH csv

AS (SELECT 'AA,BB,CC,DD,EE,FF'

AS csvdata

FROM DUAL)

SELECT REGEXP_SUBSTR (csv.csvdata, '[^,]+', 1, LEVEL) pivot_char

FROM DUAL, csv

CONNECT BY REGEXP_SUBSTR (csv.csvdata,'[^,]+', 1, LEVEL) IS NOT NULL;

查询表中的最后一个记录

这个查询语句很直接，表中没有主键，或者是用户不确定记录最大主键是否是最新的那个记录时，就可以使用这个语句来查询表中最后一个记录。


SELECT *
 FROM employees
WHERE ROWID IN (SELECT MAX (ROWID) FROM employees);

SELECT * FROM employees
MINUS
SELECT *
 FROM employees
WHERE ROWNUM < (SELECT COUNT (*) FROM employees);

SELECT *

FROM employees

WHERE ROWID IN (SELECT MAX (ROWID) FROM employees);

SELECT * FROM employees

MINUS

SELECT *

FROM employees

WHERE ROWNUM < (SELECT COUNT (*) FROM employees);

在 Oracle 中做行数据乘法

这个查询语句使用一些复杂的数学函数来做每个行的数值乘法


WITH tbl
  AS (SELECT -2 num FROM DUAL
     UNION
     SELECT -3 num FROM DUAL
     UNION
     SELECT -4 num FROM DUAL),
  sign_val
  AS (SELECT CASE MOD (COUNT (*), 2) WHEN 0 THEN 1 ELSE -1 END val
      FROM tbl
     WHERE num < 0)
 SELECT EXP (SUM (LN (ABS (num)))) * val
  FROM tbl, sign_val
GROUP BY val;

WITH tbl

AS (SELECT -2 num FROM DUAL

UNION

SELECT -3 num FROM DUAL

UNION

SELECT -4 num FROM DUAL),

sign_val

AS (SELECT CASE MOD (COUNT (*), 2) WHEN 0 THEN 1 ELSE -1 END val

FROM tbl

WHERE num < 0)

SELECT EXP (SUM (LN (ABS (num)))) * val

FROM tbl, sign_val

GROUP BY val;

获取当前月份的第一天

运行这个命令能快速返回当前月份的第一天。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。


SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month"
FROM DUAL;

SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month"

FROM DUAL;

获取当前月份的最后一天

这个查询语句类似于上面那个语句，而且充分照顾到了闰年，所以当二月份有 29 号，那么就会返回 29/2 。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。


SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"
FROM DUAL;

SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"

FROM DUAL;

获取当前年份的第一天

每年的第一天都是1 月1日，这个查询语句可以使用在存储过程中，需要对当前年份第一天做一些计算的时候。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。


SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day" FROM DUAL;

1 2	SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day" FROM DUAL;

获取当前年份的最后一天

类似于上面的查询语句。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。


SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day" FROM DUAL

1 2	SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day" FROM DUAL

获取当前月份的天数

这个语句非常有用，可以计算出当前月份的天数。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。


SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days
FROM DUAL;

SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days

FROM DUAL;

获取当前月份剩下的天数

下面的语句用来计算当前月份剩下的天数。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。


SELECT SYSDATE,
LAST_DAY (SYSDATE) "Last",
LAST_DAY (SYSDATE) - SYSDATE "Days left"
FROM DUAL;

SELECT SYSDATE,

LAST_DAY (SYSDATE) "Last",

LAST_DAY (SYSDATE) - SYSDATE "Days left"

FROM DUAL;

获取两个日期之间的天数

使用这个语句来获取两个不同日期自检的天数。


SELECT ROUND ( (MONTHS_BETWEEN ('01-Feb-2014', '01-Mar-2012') * 30), 0)
  num_of_days
FROM DUAL;

SELECT TRUNC(sysdate) - TRUNC(e.hire_date) FROM employees;

SELECT ROUND ( (MONTHS_BETWEEN ('01-Feb-2014', '01-Mar-2012') * 30), 0)

num_of_days

FROM DUAL;

SELECT TRUNC(sysdate) - TRUNC(e.hire_date) FROM employees;

显示当前年份截止到上个月每个月份开始和结束的日期

这个是个很聪明的查询语句，用来显示当前年份每个月的开始和结束的日期，你可以使用这个进行一些类型的计算。你可以用任何的日期值替换 “SYSDATE”来指定查询的日期。


SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), i) start_date,
    TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, i))) end_date
FROM XMLTABLE (
     'for $i in 0 to xs:int(D) return $i'
     PASSING XMLELEMENT (
           d,
           FLOOR (
            MONTHS_BETWEEN (
              ADD_MONTHS (TRUNC (SYSDATE, 'YEAR') - 1, 12),
              SYSDATE)))
     COLUMNS i INTEGER PATH '.');

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), i) start_date,

TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, i))) end_date

FROM XMLTABLE (

'for $i in 0 to xs:int(D) return $i'

PASSING XMLELEMENT (

FLOOR (

MONTHS_BETWEEN (

ADD_MONTHS (TRUNC (SYSDATE, 'YEAR') - 1, 12),

SYSDATE)))

COLUMNS i INTEGER PATH '.');

获取直到目前为止今天过去的秒数（从 00：00 开始算）


SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 num_of_sec_since_morning
FROM DUAL;

SELECT (SYSDATE - TRUNC (SYSDATE)) * 24 * 60 * 60 num_of_sec_since_morning

FROM DUAL;

获取今天剩下的秒数（直到 23：59：59 结束）


SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left
 FROM DUAL;

SELECT (TRUNC (SYSDATE+1) - SYSDATE) * 24 * 60 * 60 num_of_sec_left

FROM DUAL;

检查在当前数据库模式下是否存在指定的表

这是一个简单的查询语句，用来检查当前数据库是否有你想要创建的表，允许你重新运行创建表脚本，这个也可以检查当前用户是否已经创建了指定的表（根据这个查询语句在什么环境下运行来查询）。


SELECT table_name
 FROM user_tables
WHERE table_name = 'TABLE_NAME';

SELECT table_name

FROM user_tables

WHERE table_name = 'TABLE_NAME';

检查在当前表中是否存在指定的列

这是个简单的查询语句来检查表里是否有指定的列，在你尝试使用 ALTER TABLE 来添加新的列新到表中的时候非常有用，它会提示你是否已经存在这个列。

SELECT column_name AS FOUND
 FROM user_tab_cols
WHERE table_name = 'TABLE_NAME' AND column_name = 'COLUMN_NAME';

SELECT column_name AS FOUND

FROM user_tab_cols

WHERE table_name = 'TABLE_NAME' AND column_name = 'COLUMN_NAME';

显示表结构

这个查询语句会显示任何表的 DDL 状态信息。请注意我们已经将‘TABLE’作为第一个信息提交了。这个查询语句也可以用来获取任何数据库对象的 DDL 状态信息。举例说明，只需要把第一个参数替换成‘VIEW’，第二个修改成视图的名字，就可以查询视图的 DDL 信息了。


SELECT DBMS_METADATA.get_ddl ('TABLE', 'TABLE_NAME', 'USER_NAME') FROM DUAL;

1 2	SELECT DBMS_METADATA.get_ddl ('TABLE', 'TABLE_NAME', 'USER_NAME') FROM DUAL;

获取当前模式

这是另一个可以获得当前模式的名字的查询语句。


SELECT SYS_CONTEXT ('userenv', 'current_schema') FROM DUAL;

1 2	SELECT SYS_CONTEXT ('userenv', 'current_schema') FROM DUAL;

本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

修改当前模式

这是另一个可以修改当前模式的查询语句，当你希望你的脚本可以在指定的用户下运行的时候非常有用，而且这是非常安全的一个方式。

ALTER SESSION SET CURRENT_SCHEMA = new_schema;

1	ALTER SESSION SET CURRENT_SCHEMA = new_schema;

数据库版本信息

返回 Oracle 数据库版本


SELECT * FROM v$version;

1 2	SELECT * FROM v$version;

数据库默认信息

返回一些系统默认的信息


SELECT username,
   profile,
   default_tablespace,
   temporary_tablespace
 FROM dba_users;

SELECT username,

profile,

default_tablespace,

temporary_tablespace

FROM dba_users;

数据库字符设置信息

显示数据库的字符设置信息


SELECT * FROM nls_database_parameters;

1 2	SELECT * FROM nls_database_parameters;

获取 Oracle 版本


SELECT VALUE
 FROM v$system_parameter
WHERE name = 'compatible';

SELECT VALUE

FROM v$system_parameter

WHERE name = 'compatible';

存储区分大小写的数据，但是索引不区分大小写

某些时候你可能想在数据库中查询一些独立的数据，可能会用 UPPER(..) = UPPER(..) 来进行不区分大小写的查询，所以就想让索引不区分大小写，不占用那么多的空间，这个语句恰好能解决你的需求。


CREATE TABLE tab (col1 VARCHAR2 (10));
CREATE INDEX idx1
  ON tab (UPPER (col1));
ANALYZE TABLE a COMPUTE STATISTICS;

CREATE TABLE tab (col1 VARCHAR2 (10));

CREATE INDEX idx1

ON tab (UPPER (col1));

ANALYZE TABLE a COMPUTE STATISTICS;

调整没有添加数据文件的表空间

另一个 DDL 查询来调整表空间大小


ALTER DATABASE DATAFILE '/work/oradata/STARTST/STAR02D.dbf' resize 2000M;

1 2	ALTER DATABASE DATAFILE '/work/oradata/STARTST/STAR02D.dbf' resize 2000M;

检查表空间的自动扩展开关

在给定的表空间中查询是否打开了自动扩展开关


SELECT SUBSTR (file_name, 1, 50), AUTOEXTENSIBLE FROM dba_data_files;

SELECT tablespace_name, AUTOEXTENSIBLE FROM dba_data_files;

SELECT SUBSTR (file_name, 1, 50), AUTOEXTENSIBLE FROM dba_data_files;

SELECT tablespace_name, AUTOEXTENSIBLE FROM dba_data_files;

在表空间添加数据文件

在表空间中添加数据文件

ALTER TABLESPACE data01 ADD DATAFILE '/work/oradata/STARTST/data01.dbf'  SIZE 1000M AUTOEXTEND OFF;

1	ALTER TABLESPACE data01 ADD DATAFILE '/work/oradata/STARTST/data01.dbf' SIZE 1000M AUTOEXTEND OFF;

增加数据文件的大小

给指定的表空间增加大小


ALTER DATABASE DATAFILE '/u01/app/Test_data_01.dbf' RESIZE 2G;

1 2	ALTER DATABASE DATAFILE '/u01/app/Test_data_01.dbf' RESIZE 2G;

查询数据库的实际大小

给出以 GB 为单位的数据库的实际大小

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

1	SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

查询数据库中数据占用的大小或者是数据库使用细节

给出在数据库中数据占据的空间大小

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_segments;

1	SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_segments;

查询模式或者用户的大小

以 MB 为单位给出用户的空间大小


SELECT SUM (bytes / 1024 / 1024) "size"
 FROM dba_segments
WHERE owner = '&owner';

SELECT SUM (bytes / 1024 / 1024) "size"

FROM dba_segments

WHERE owner = '&owner';

查询数据库中每个用户最后使用的 SQL 查询

此查询语句会显示当前数据库中每个用户最后使用的 SQL 语句。


SELECT S.USERNAME || '(' || s.sid || ')-' || s.osuser UNAME,
     s.program || '-' || s.terminal || '(' || s.machine || ')' PROG,
     s.sid || '/' || s.serial# sid,
     s.status "Status",
     p.spid,
     sql_text sqltext
 FROM v$sqltext_with_newlines t, V$SESSION s, v$process p
 WHERE   t.address = s.sql_address
     AND p.addr = s.paddr(+)
     AND t.hash_value = s.sql_hash_value
ORDER BY s.sid, t.piece;

SELECT S.USERNAME || '(' || s.sid || ')-' || s.osuser UNAME,

s.program || '-' || s.terminal || '(' || s.machine || ')' PROG,

s.sid || '/' || s.serial# sid,

s.status "Status",

p.spid,

sql_text sqltext

FROM v$sqltext_with_newlines t, V$SESSION s, v$process p

WHERE t.address = s.sql_address

AND p.addr = s.paddr(+)

AND t.hash_value = s.sql_hash_value

ORDER BY s.sid, t.piece;

查询用户 CPU 的使用率

这个语句是用来显示每个用户的 CPU 使用率，有助于用户理解数据库负载情况


SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds
  FROM v$session ss, v$sesstat se, v$statname sn
  WHERE   se.STATISTIC# = sn.STATISTIC#
   AND NAME LIKE '%CPU used by this session%'
   AND se.SID = ss.SID
   AND ss.status = 'ACTIVE'
   AND ss.username IS NOT NULL
ORDER BY VALUE DESC;

SELECT ss.username, se.SID, VALUE / 100 cpu_usage_seconds

FROM v$session ss, v$sesstat se, v$statname sn

WHERE se.STATISTIC# = sn.STATISTIC#

AND NAME LIKE '%CPU used by this session%'

AND se.SID = ss.SID

AND ss.status = 'ACTIVE'

AND ss.username IS NOT NULL

ORDER BY VALUE DESC;

查询数据库长查询进展情况

显示运行中的长查询的进展情况


SELECT a.sid,
    a.serial#,
    b.username,
    opname OPERATION,
   target OBJECT,
    TRUNC (elapsed_seconds, 5) "ET (s)",
    TO_CHAR (start_time, 'HH24:MI:SS') start_time,
    ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE (%)"
  FROM v$session_longops a, v$session b
  WHERE   a.sid = b.sid
    AND b.username NOT IN ('SYS', 'SYSTEM')
     AND totalwork > 0
ORDER BY elapsed_seconds;

SELECT a.sid,

a.serial#,

b.username,

opname OPERATION,

target OBJECT,

TRUNC (elapsed_seconds, 5) "ET (s)",

TO_CHAR (start_time, 'HH24:MI:SS') start_time,

ROUND ( (sofar / totalwork) * 100, 2) "COMPLETE (%)"

FROM v$session_longops a, v$session b

WHERE a.sid = b.sid

AND b.username NOT IN ('SYS', 'SYSTEM')

AND totalwork > 0

ORDER BY elapsed_seconds;

获取当前会话 ID，进程 ID，客户端 ID 等

这个专门提供给想使用进程 ID 和会话 ID 做些 voodoo magic 的用户。


SELECT b.sid,
   b.serial#,
   a.spid processid,
   b.process clientpid
 FROM v$process a, v$session b
WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');

SELECT b.sid,

b.serial#,

a.spid processid,

b.process clientpid

FROM v$process a, v$session b

WHERE a.addr = b.paddr AND b.audsid = USERENV ('sessionid');

V$SESSION.SID AND V$SESSION.SERIAL# 是数据库进程 ID
V$PROCESS.SPID 是数据库服务器后台进程 ID
V$SESSION.PROCESS 是客户端 PROCESS ID, ON windows it IS : separated THE FIRST # IS THE PROCESS ID ON THE client AND 2nd one IS THE THREAD id.

查询特定的模式或者表中执行的最后一个 SQL 语句


SELECT CREATED, TIMESTAMP, last_ddl_time
 FROM all_objects
WHERE   OWNER = 'MYSCHEMA'
   AND OBJECT_TYPE = 'TABLE'
   AND OBJECT_NAME = 'EMPLOYEE_TABLE';

SELECT CREATED, TIMESTAMP, last_ddl_time

FROM all_objects

WHERE OWNER = 'MYSCHEMA'

AND OBJECT_TYPE = 'TABLE'

AND OBJECT_NAME = 'EMPLOYEE_TABLE';

查询每个执行读取的前十个 SQL


SELECT *
 FROM ( SELECT ROWNUM,
        SUBSTR (a.sql_text, 1, 200) sql_text,
        TRUNC (
         a.disk_reads / DECODE (a.executions, 0, 1, a.executions))
         reads_per_execution,
        a.buffer_gets,
        a.disk_reads,
        a.executions,
        a.sorts,
       a.address
     FROM v$sqlarea a
   ORDER BY 3 DESC)
WHERE ROWNUM < 10;

SELECT *

FROM ( SELECT ROWNUM,

SUBSTR (a.sql_text, 1, 200) sql_text,

TRUNC (

a.disk_reads / DECODE (a.executions, 0, 1, a.executions))

reads_per_execution,

a.buffer_gets,

a.disk_reads,

a.executions,

a.sorts,

a.address

FROM v$sqlarea a

ORDER BY 3 DESC)

WHERE ROWNUM < 10;

在视图中查询并显示实际的 Oracle 连接


SELECT osuser,
   username,
   machine,
   program
FROM v$session
ORDER BY osuser;

SELECT osuser,

username,

machine,

program

FROM v$session

ORDER BY osuser;

查询并显示通过打开连接程序打开连接的组


SELECT program application, COUNT (program) Numero_Sesiones
FROM v$session
GROUP BY program
ORDER BY Numero_Sesiones DESC;

SELECT program application, COUNT (program) Numero_Sesiones

FROM v$session

GROUP BY program

ORDER BY Numero_Sesiones DESC;

查询并显示连接 Oracle 的用户和用户的会话数量


SELECT username Usuario_Oracle, COUNT (username) Numero_Sesiones
FROM v$session
GROUP BY username
ORDER BY Numero_Sesiones DESC;

SELECT username Usuario_Oracle, COUNT (username) Numero_Sesiones

FROM v$session

GROUP BY username

ORDER BY Numero_Sesiones DESC;

获取拥有者的对象数量


SELECT owner, COUNT (owner) number_of_objects
FROM dba_objects
GROUP BY owner
ORDER BY number_of_objects DESC;

SELECT owner, COUNT (owner) number_of_objects

FROM dba_objects

GROUP BY owner

ORDER BY number_of_objects DESC;

表空间历史增长量

select a.name, b.*
  from v$tablespace a,
       (select tablespace_id ts#,
               trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) datetime,
               round(max(tablespace_usedsize * 8 / 1024),2) ts_used_size_M,
               round(max(v.tablespace_size * 8 / 1024),2) ts_size_MB,
               round(max(tablespace_maxsize * 8 / 1024/1024)) ts_maxsize_G
          from dba_hist_tbspc_space_usage v
         where trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) >=
               trunc(sysdate - 10)
         group by tablespace_id,
                  trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))
         order by tablespace_id,
                  trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))) b
 where a.ts# = b.ts#
 ORDER BY b.TS#,b.datetime;

select a.name, b.*

from v$tablespace a,

(select tablespace_id ts#,

trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) datetime,

round(max(tablespace_usedsize * 8 / 1024),2) ts_used_size_M,

round(max(v.tablespace_size * 8 / 1024),2) ts_size_MB,

round(max(tablespace_maxsize * 8 / 1024/1024)) ts_maxsize_G

from dba_hist_tbspc_space_usage v

where trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) >=

trunc(sysdate - 10)

group by tablespace_id,

trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))

order by tablespace_id,

trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))) b

where a.ts# = b.ts#

ORDER BY b.TS#,b.datetime;

清理killed的会话


'ALTER SYSTEM KILL SESSION ''' || s.SID || ',' || s.SERIAL# ||',@'||A.INST_ID||' IMMEDIATE ;' kill_session,
'ALTER SYSTEM KILL SESSION ''' || s.SID || ',' || s.SERIAL# ||''' IMMEDIATE ;' kill_session, 
ALTER SYSTEM KILL SESSION '1228,42549,@1';
SELECT 'ALTER SYSTEM DISCONNECT SESSION ''' || V.SID || ',' || V.SERIAL# || ',@' ||
       V.INST_ID || ''' IMMEDIATE',
       'ALTER SYSTEM DISCONNECT SESSION ''' || V.SID || ',' || V.SERIAL# ||
       ''' IMMEDIATE',
       V.*
  FROM GV$SESSION V;

-----清理killed的会话
-----方法1
select spid, program from v$process 
    where program!= 'PSEUDO' 
    and addr not in (select paddr from v$session)
    and addr not in (select paddr from v$bgprocess)
    and addr not in (select paddr from v$shared_server);

select INST_ID, spid, program,'kill -9 '|| spid  kill9
  from gv$process a
 where program != 'PSEUDO'
   and (INST_ID, addr) not in (select INST_ID, paddr from gv$session)
   and (INST_ID, addr) not in (select INST_ID, paddr from gv$bgprocess)
   and (INST_ID, addr) not in (select INST_ID, paddr from gv$shared_server)
   and a.PNAME is null;

-----方法2
set line 9999
col sessionid format a20
col sessionid_killed format a20
col kill_session format a60

SELECT a.INST_ID,
       a.SID || ',' || a.SERIAL# || ',' ||
       (select spid
          from gv$process b
         where b.INST_ID = a.INST_ID
           and A.creator_addr = b.ADDR --and decode(a.status,'KILLED',A.creator_addr,A.PADDR) = b.ADDR 
        ) sessionid,
       a.PADDR,
       a.STATUS,
       a.PROGRAM,
       'alter system disconnect session ''' || sid || ',' || serial# || ''' immediate;' kill_session
  FROM gv$session a
 WHERE a.USERNAME = 'SYS'
   and a.STATUS = 'KILLED';

-----方法3
SELECT a.SID || ',' || a.SERIAL# || ',' ||
       (select spid
          from gv$process b
         where b.INST_ID = a.INST_ID
           and A.pid = b.pid) sessionid,
       'alter system kill session ''' || sid || ',' || serial# || ''';' kill_session
  FROM gV$DETACHED_SESSION a;

-----方法4
SELECT INST_ID, spid, program, 'kill -9 ' || spid kill9
  FROM gv$process a
 WHERE (a.INST_ID, a.addr) in (select INST_ID, p.addr
                                 from gv$process p
                                where pid <> 1
                               minus
                               select INST_ID, s.paddr
                                 from gv$session s)
    and a.PNAME is null;

'ALTER SYSTEM KILL SESSION ''' || s.SID || ',' || s.SERIAL# ||',@'||A.INST_ID||' IMMEDIATE ;' kill_session,

'ALTER SYSTEM KILL SESSION ''' || s.SID || ',' || s.SERIAL# ||''' IMMEDIATE ;' kill_session,

ALTER SYSTEM KILL SESSION '1228,42549,@1';

SELECT 'ALTER SYSTEM DISCONNECT SESSION ''' || V.SID || ',' || V.SERIAL# || ',@' ||

V.INST_ID || ''' IMMEDIATE',

'ALTER SYSTEM DISCONNECT SESSION ''' || V.SID || ',' || V.SERIAL# ||

''' IMMEDIATE',

V.*

FROM GV$SESSION V;

-----清理killed的会话

-----方法1

select spid, program from v$process

where program!= 'PSEUDO'

and addr not in (select paddr from v$session)

and addr not in (select paddr from v$bgprocess)

and addr not in (select paddr from v$shared_server);

select INST_ID, spid, program,'kill -9 '|| spid kill9

from gv$process a

where program != 'PSEUDO'

and (INST_ID, addr) not in (select INST_ID, paddr from gv$session)

and (INST_ID, addr) not in (select INST_ID, paddr from gv$bgprocess)

and (INST_ID, addr) not in (select INST_ID, paddr from gv$shared_server)

and a.PNAME is null;

-----方法2

set line 9999

col sessionid format a20

col sessionid_killed format a20

col kill_session format a60

SELECT a.INST_ID,

a.SID || ',' || a.SERIAL# || ',' ||

(select spid

from gv$process b

where b.INST_ID = a.INST_ID

and A.creator_addr = b.ADDR --and decode(a.status,'KILLED',A.creator_addr,A.PADDR) = b.ADDR

) sessionid,

a.PADDR,

a.STATUS,

a.PROGRAM,

'alter system disconnect session ''' || sid || ',' || serial# || ''' immediate;' kill_session

FROM gv$session a

WHERE a.USERNAME = 'SYS'

and a.STATUS = 'KILLED';

-----方法3

SELECT a.SID || ',' || a.SERIAL# || ',' ||

(select spid

from gv$process b

where b.INST_ID = a.INST_ID

and A.pid = b.pid) sessionid,

'alter system kill session ''' || sid || ',' || serial# || ''';' kill_session

FROM gV$DETACHED_SESSION a;

-----方法4

SELECT INST_ID, spid, program, 'kill -9 ' || spid kill9

FROM gv$process a

WHERE (a.INST_ID, a.addr) in (select INST_ID, p.addr

from gv$process p

where pid <> 1

minus

select INST_ID, s.paddr

from gv$session s)

and a.PNAME is null;

删除主键及主键索引

--alter table table_name add constraint pk_id primary key(object_id);
alter table table_name drop primary key cascade drop index;
alter table table_name drop constraint constraint_name cascade drop index;

--alter table table_name add constraint pk_id primary key(object_id);

alter table table_name drop primary key cascade drop index;

alter table table_name drop constraint constraint_name cascade drop index;

数据文件自动扩展


alter database datafile 5 autoextend on next 5M;
如果是bigfile可以采用： ALTER TABLESPACE TBS2 AUTOEXTEND ON NEXT   20G;

修改表空间数据文件E:\oradata\ORCL19C\JDE92TEST\为不限制的语句为：
alter database datafile '/oradata/orcl/demo01.dbf' autoextend on maxsize unlimited;

创建表空间数据文件E:\oradata\ORCL19C\JDE92TEST\为不限制的语句为：
create tablespace demo2 datafile '/oradata/orcl/demo201.dbf' size 10M autoextend on maxsize unlimited;

----取消已有数据文件的自动增长方式
alter database datafile 'i:\oracle\oradata\dmusertbs01.dbf' autoextend off;

alter database datafile 5 autoextend on next 5M;

如果是bigfile可以采用： ALTER TABLESPACE TBS2 AUTOEXTEND ON NEXT 20G;

修改表空间数据文件E:\oradata\ORCL19C\JDE92TEST\为不限制的语句为：

alter database datafile '/oradata/orcl/demo01.dbf' autoextend on maxsize unlimited;

创建表空间数据文件E:\oradata\ORCL19C\JDE92TEST\为不限制的语句为：

create tablespace demo2 datafile '/oradata/orcl/demo201.dbf' size 10M autoextend on maxsize unlimited;

----取消已有数据文件的自动增长方式

alter database datafile 'i:\oracle\oradata\dmusertbs01.dbf' autoextend off;

注册监听


*.LOCAL_LISTENER='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1523)))'
*.LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1522))';

ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1523)))';
ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1522))';

ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1523)))','(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1522)))';
ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1522))','(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1523))';

ALTER SYSTEM REGISTER;

*.LOCAL_LISTENER='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1523)))'

*.LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1522))';

ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1523)))';

ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1522))';

ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1523)))','(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1522)))';

ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1522))','(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.194.64)(PORT=1523))';

ALTER SYSTEM REGISTER;

用户PROFILE


----------  用户PROFILE 
alter system set resource_limit=true;
alter profile default limit idle_time 10;

create profile pro_lhr limit idle_time 1;
alter user lhr profile pro_lhr;
select * from dba_profiles where profile='PRO_LHR';

ALTER USER LHR PROFILE DEFAULT;
ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;     --永不过期  无限
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED; 

------取消复杂性验证 
ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;

------启用密码复杂性验证
ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G;

---------- 用户PROFILE

alter system set resource_limit=true;

alter profile default limit idle_time 10;

create profile pro_lhr limit idle_time 1;

alter user lhr profile pro_lhr;

select * from dba_profiles where profile='PRO_LHR';

ALTER USER LHR PROFILE DEFAULT;

ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; --永不过期无限

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

------取消复杂性验证

ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;

------启用密码复杂性验证

ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G;

打赏赞(3)

标签： DBA脚本 Oracle

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

嘿，我是小麦，需要帮助随时找我哦

18509239930
个人微信
麦老师QQ聊天
个人邮箱
点击加入QQ群
个人微店
回到顶部