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

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章！

请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(3)

标签： Oracle DBA脚本

小麦苗

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

发表回复取消回复

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

2024年 4月
一	二	三	四	五	六	日
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30

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

18509239930
个人微信
DB宝
个人邮箱
点击加入QQ群
个人微店
回到顶部

合 Oracle DBA日常维护的SQL脚本(常用SQL)

查看PSU

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

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

查询表的历史统计信息

查询索引的历史统计信息

表上列的使用情况

查询字符集

生成AWR

AWR的SQL部分

AWR信息

AWR主机信息

查询碎片程度高的表

查询索引碎片的比例

集群因子clustering_factor高的表

根据sid查spid或根据spid查sid

根据sid查看具体的sql语句

根据spid查询具体的sql语句

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

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

查询DDL锁的sql

查询锁住的DDL对象

查询当前正在执行的sql

查询正在执行的SCHEDULER_JOB

查询正在执行的dbms_job

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

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

合 Oracle DBA日常维护的SQL脚本(常用SQL)

查看PSU

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

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

查询表的历史统计信息

查询索引的历史统计信息

表上列的使用情况

查询字符集

生成AWR

AWR的SQL部分

AWR信息

AWR主机信息

查询碎片程度高的表

查询索引碎片的比例

集群因子clustering_factor高的表

根据sid查spid或根据spid查sid

根据sid查看具体的sql语句

根据spid查询具体的sql语句

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

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

查询DDL锁的sql

查询锁住的DDL对象

查询当前正在执行的sql

查询正在执行的SCHEDULER_JOB

查询正在执行的dbms_job

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

相关文章

您可能还喜欢...

【故障处理】ORA-12545: Connect failed because target host or object does not exist

物理DG中主库的LNSn、NSS、NSA进程的比较

如何收集systemdump？如何收集hanganalyze？

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复