Script:列出Oracle每小时的redo重做日志产生量

2011/09/30 by 3 Comments

以下脚本可以用于列出最近Oracle数据库每小时估算的redo重做日志产生量，因为估算数据来源于archivelog的产生量和大小，所以数据是近似值，可供参考:

WITH times AS
 (SELECT /*+ MATERIALIZE */
   hour_end_time
    FROM (SELECT (TRUNC(SYSDATE, 'HH') + (2 / 24)) - (ROWNUM / 24) hour_end_time
            FROM DUAL
          CONNECT BY ROWNUM <= (1 * 24) + 3),
         v$database
   WHERE log_mode = 'ARCHIVELOG')
SELECT hour_end_time, NVL(ROUND(SUM(size_mb), 3), 0) size_mb, i.instance_name
  FROM(
SELECT hour_end_time, CASE WHEN(hour_end_time - (1 / 24)) > lag_next_time THEN(next_time + (1 / 24) - hour_end_time) * (size_mb / (next_time - lag_next_time)) ELSE 0 END + CASE WHEN hour_end_time < lead_next_time THEN(hour_end_time - next_time) * (lead_size_mb / (lead_next_time - next_time)) ELSE 0 END + CASE WHEN lag_next_time > (hour_end_time - (1 / 24)) THEN size_mb ELSE 0 END + CASE WHEN next_time IS NULL THEN(1 / 24) * LAST_VALUE(CASE WHEN next_time IS NOT NULL AND lag_next_time IS NULL THEN 0 ELSE(size_mb / (next_time - lag_next_time)) END IGNORE NULLS) OVER(
 ORDER BY hour_end_time DESC, next_time DESC) ELSE 0 END size_mb
  FROM(
SELECT t.hour_end_time, arc.next_time, arc.lag_next_time, LEAD(arc.next_time) OVER(
 ORDER BY arc.next_time ASC) lead_next_time, arc.size_mb, LEAD(arc.size_mb) OVER(
 ORDER BY arc.next_time ASC) lead_size_mb
  FROM times t,(
SELECT next_time, size_mb, LAG(next_time) OVER(
 ORDER BY next_time) lag_next_time
  FROM(
SELECT next_time, SUM(size_mb) size_mb
  FROM(
SELECT DISTINCT a.sequence#, a.next_time, ROUND(a.blocks * a.block_size / 1024 / 1024) size_mb
  FROM v$archived_log a,(
SELECT /*+ no_merge */
CASE WHEN TO_NUMBER(pt.VALUE) = 0 THEN 1 ELSE TO_NUMBER(pt.VALUE) END VALUE
  FROM v$parameter pt
 WHERE pt.name = 'thread') pt
 WHERE a.next_time > SYSDATE - 3 AND a.thread# = pt.VALUE AND ROUND(a.blocks * a.block_size / 1024 / 1024) > 0)
 GROUP BY next_time)) arc
 WHERE t.hour_end_time = (TRUNC(arc.next_time(+), 'HH') + (1 / 24)))
 WHERE hour_end_time > TRUNC(SYSDATE, 'HH') - 1 - (1 / 24)), v$instance i
 WHERE hour_end_time <= TRUNC(SYSDATE, 'HH')
 GROUP BY hour_end_time, i.instance_name
 ORDER BY hour_end_time
 /

Sample Output:

HOUR_END_TIME    SIZE_MB INSTANCE_NAME
------------- ---------- ----------------
2011/9/29 1:0       2.92 VPROD1
2011/9/29 2:0       2.92 VPROD1
2011/9/29 3:0       2.92 VPROD1
2011/9/29 4:0       2.92 VPROD1
2011/9/29 5:0       2.92 VPROD1
2011/9/29 6:0       2.92 VPROD1
2011/9/29 7:0       2.92 VPROD1
2011/9/29 8:0       2.92 VPROD1
2011/9/29 9:0       2.92 VPROD1
2011/9/29 10:       2.92 VPROD1
2011/9/29 11:       2.92 VPROD1
2011/9/29 12:      3.537 VPROD1
2011/9/29 13:       3.55 VPROD1
2011/9/29 14:       3.55 VPROD1
2011/9/29 15:       3.55 VPROD1
2011/9/29 16:       3.55 VPROD1
2011/9/29 17:       3.55 VPROD1
2011/9/29 18:       3.55 VPROD1
2011/9/29 19:       3.55 VPROD1
2011/9/29 20:       3.55 VPROD1
Filed Under: Oracle, Oracle脚本script Tagged With: ,

Script:收集11g Oracle实例IO性能信息

2011/09/30 by 1 Comment

以下脚本可以用于收集Oracle Instance I/O 性能信息：


set linesize 80 pagesize 1400;

SELECT ios.filetype_name,
       df.file_name,
       df.tablespace_name,
       ios.small_read_megabytes + ios.large_read_megabytes read_megabytes,
       ios.small_write_megabytes + ios.large_write_megabytes write_megabytes,
       ios.small_read_reqs + ios.large_read_reqs read_reqs,
       ios.small_write_reqs + ios.large_write_reqs write_reqs,
       ios.small_read_servicetime + ios.large_read_servicetime read_servicetime,
       ios.small_write_servicetime + ios.large_write_servicetime write_servicetime,
       ios.small_read_reqs,
       ios.small_read_servicetime
  FROM v$iostat_file ios, dba_data_files df
 WHERE ios.filetype_name = 'Data File'
   AND df.file_id = ios.file_no
UNION ALL
SELECT ios.filetype_name,
       tf.file_name,
       tf.tablespace_name,
       ios.small_read_megabytes + ios.large_read_megabytes read_megabytes,
       ios.small_write_megabytes + ios.large_write_megabytes write_megabytes,
       ios.small_read_reqs + ios.large_read_reqs read_reqs,
       ios.small_write_reqs + ios.large_write_reqs write_reqs,
       ios.small_read_servicetime + ios.large_read_servicetime read_servicetime,
       ios.small_write_servicetime + ios.large_write_servicetime write_servicetime,
       ios.small_read_reqs,
       ios.small_read_servicetime
  FROM v$iostat_file ios, dba_temp_files tf
 WHERE ios.filetype_name = 'Temp File'
   AND tf.file_id = ios.file_no
UNION ALL
SELECT ios.filetype_name,
       NULL file_name,
       NULL tablespace_name,
       ios.small_read_megabytes + ios.large_read_megabytes read_megabytes,
       ios.small_write_megabytes + ios.large_write_megabytes write_megabytes,
       ios.small_read_reqs + ios.large_read_reqs read_reqs,
       ios.small_write_reqs + ios.large_write_reqs write_reqs,
       ios.small_read_servicetime + ios.large_read_servicetime read_servicetime,
       ios.small_write_servicetime + ios.large_write_servicetime write_servicetime,
       ios.small_read_reqs,
       ios.small_read_servicetime
  FROM v$iostat_file ios
 WHERE ios.filetype_name NOT IN ('Temp File', 'Data File')
/

SELECT function_name,
       small_read_megabytes + small_write_megabytes + large_read_megabytes +
       large_write_megabytes throughput_megabytes,
       small_read_reqs + small_write_reqs + large_read_reqs +
       large_write_reqs throughput_reqs,
       number_of_waits,
       wait_time
  FROM v$iostat_function
/

SELECT * FROM v$iostat_file 
/


col filetype_name format a14 heading "File Type"
col reads format 9,999,999 heading "Reads"
col writes format 9,999,999 heading "Writes"
col read_time_sec format  99,999 heading "Read Time|sec"
col write_time_sec format  99,999 heading "Write Time|sec"
col avg_sync_read_ms format 999.99 heading "Avg Sync|Read ms"
col total_io_seconds format 9,999,999 heading "Total IO|sec"

set lines 80
set pages 10000
set echo on 

WITH iostat_file AS 
  (SELECT filetype_name,SUM(large_read_reqs) large_read_reqs,
          SUM(large_read_servicetime) large_read_servicetime,
          SUM(large_write_reqs) large_write_reqs,
          SUM(large_write_servicetime) large_write_servicetime,
          SUM(small_read_reqs) small_read_reqs,
          SUM(small_read_servicetime) small_read_servicetime,
          SUM(small_sync_read_latency) small_sync_read_latency,
          SUM(small_sync_read_reqs) small_sync_read_reqs,
          SUM(small_write_reqs) small_write_reqs,
          SUM(small_write_servicetime) small_write_servicetime
     FROM sys.v_$iostat_file
    GROUP BY filetype_name)
SELECT filetype_name, small_read_reqs + large_read_reqs reads,
       large_write_reqs + small_write_reqs writes,
       ROUND((small_read_servicetime + large_read_servicetime)/1000) 
          read_time_sec,
       ROUND((small_write_servicetime + large_write_servicetime)/1000) 
          write_time_sec,
       CASE WHEN small_sync_read_reqs > 0 THEN 
          ROUND(small_sync_read_latency / small_sync_read_reqs, 2) 
       END avg_sync_read_ms,
       ROUND((  small_read_servicetime+large_read_servicetime
              + small_write_servicetime + large_write_servicetime)
             / 1000, 2)  total_io_seconds
  FROM iostat_file
 ORDER BY 7 DESC
/
Filed Under: Oracle, Oracle脚本script Tagged With: ,

Script:检查数据库当前是否有备份操作在执行中

2011/09/30 by 5 Comments

以下脚本可以用于检测数据库当前是否有备份操作在执行中:

SELECT DECODE(os_backup.backup + rman_backup.backup, 0, 'FALSE', 'TRUE') backup
  FROM (SELECT COUNT(*) backup FROM gv$backup WHERE status = 'ACTIVE') os_backup,
       (SELECT COUNT(*) backup
          FROM gv$session
         WHERE status = 'ACTIVE'
           AND client_info like '%rman%') rman_backup
/
Filed Under: Oracle, Oracle数据恢复、数据库恢复、灾难恢复, Oracle脚本script Tagged With: ,

Script:GoldenGate For Oracle数据库预检查脚本

2011/09/26 by Leave a Comment

 

Script:GoldenGate For Oracle数据库预检查脚本

 

 

select * from dba_logstdby_not_unique where owner='&OWNER';

set null "NULL VALUE"
set feedback off
set heading off
set linesize 132 
set pagesize 9999
set echo off
set verify off
set trimspool on

col table_name for a30
col column_name for a30
col data_type for a15
col object_type for a20
col constraint_type_desc for a30
col Owner format a15

spool AllSchemaCheckOracle.out

SELECT '------ System Info: '
FROM dual;
set heading on
select to_char(sysdate, 'MM-DD-YYYY HH24:MI:SS') "DateTime: " from dual
/
select banner from v$version
/

select name, log_mode "LogMode", 
supplemental_log_data_min "SupLog: Min", supplemental_log_data_pk "PK",
supplemental_log_data_ui "UI", force_logging "Forced",
supplemental_log_data_fk "FK", supplemental_log_data_all "All",
to_char(created, 'MM-DD-YYYY HH24:MI:SS') "Created"
from v$database
/

select 
platform_name
from v$database
/
set heading off
SELECT '------ Objects stored in Tablespaces with Compression are not supported in the current release of OGG ' 
FROM dual;
set heading on
select
TABLESPACE_NAME,
DEF_TAB_COMPRESSION
from DBA_TABLESPACES
where 
DEF_TAB_COMPRESSION <> 'DISABLED';

set heading off
SELECT '------ Distinct Object Types and their Count By Schema: '
FROM dual;
set heading on
SELECT owner, object_type, count(*) total
FROM all_objects
WHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
GROUP BY object_type, owner
/

set heading off
SELECT '------ Distinct Column Data Types and their Count in the Schema: ' 
FROM dual;
set heading on
SELECT data_type, count(*) total
FROM all_tab_columns
WHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
GROUP BY data_type
/

set heading off
SELECT '------ Tables that will Fail Add Trandata (Only an issue for Oracle versions below Oracle 10G) in the Database ' 
FROM dual;
set heading on
SELECT distinct(table_name)
FROM dba_tab_columns
WHERE owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
AND column_id > 32
AND table_name in
(SELECT distinct(table_name)
FROM all_tables
WHERE owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
MINUS
(SELECT obj1.name
FROM SYS.user$ user1,
SYS.user$ user2,
SYS.cdef$ cdef,
SYS.con$ con1,
SYS.con$ con2,
SYS.obj$ obj1,
SYS.obj$ obj2
WHERE user1.name not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
AND cdef.type# = 2
AND con2.owner# = user2.user#(+)
AND cdef.robj# = obj2.obj#(+)
AND cdef.rcon# = con2.con#(+)
AND obj1.owner# = user1.user#
AND cdef.con# = con1.con#
AND cdef.obj# = obj1.obj#
UNION
SELECT idx.table_name
FROM all_indexes idx
WHERE idx.owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
AND idx.uniqueness = 'UNIQUE'))
/

set heading off
SELECT '------ Tables With No Primary Key or Unique Index by Schema: ' 
FROM dual;
set heading on
SELECT owner, table_name
FROM all_tables
WHERE owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
MINUS
(SELECT user1.name, obj1.name
FROM SYS.user$ user1,
SYS.user$ user2,
SYS.cdef$ cdef,
SYS.con$ con1,
SYS.con$ con2,
SYS.obj$ obj1,
SYS.obj$ obj2
WHERE user1.name not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
AND cdef.type# = 2 
AND con2.owner# = user2.user#(+)
AND cdef.robj# = obj2.obj#(+)
AND cdef.rcon# = con2.con#(+)
AND obj1.owner# = user1.user#
AND cdef.con# = con1.con#
AND cdef.obj# = obj1.obj#
UNION
SELECT distinct(owner), idx.table_name
FROM all_indexes idx
WHERE idx.owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
AND idx.uniqueness = 'UNIQUE')
/

set heading off
SELECT '------ Tables with NOLOGGING setting ' FROM dual;
SELECT '------ This may cause problems with missing data down stream. ' FROM dual;
set heading on
select owner, table_name, ' ', logging from DBA_TABLES
where logging <> 'YES'
and owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
UNION
select owner, table_name, partitioning_type, DEF_LOGGING "LOGGING" from DBA_part_tables
where DEF_LOGGING != 'YES' 
and owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
UNION
select table_owner, table_name, PARTITION_NAME, logging from DBA_TAB_PARTITIONS
where logging <> 'YES' 
and table_owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
UNION
select table_owner, table_name, PARTITION_NAME, logging from DBA_TAB_SUBPARTITIONS
where logging <> 'YES' 
and table_owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
;

set heading off
SELECT '------ Tables with Deferred constraints.Deferred constraints may cause TRANDATA to chose an incorrect Key ' FROM dual;
SELECT '------ Tables with Deferred constraints should be added using KEYCOLS in the trandata statement. Schema: ' ||:b0 FROM dual;
set heading on
SELECT c.TABLE_NAME,
c.CONSTRAINT_NAME,
c.CONSTRAINT_TYPE,
c.DEFERRABLE,
c.DEFERRED,
c.VALIDATED,
c.STATUS,
i.INDEX_TYPE,
c.INDEX_NAME,
c.INDEX_OWNER
FROM dba_constraints c,
dba_indexes i
WHERE
i.TABLE_NAME = c.TABLE_NAME
AND i.OWNER = c.OWNER
AND c.DEFERRED = 'DEFERRED'
And i.owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
;

set heading off
SELECT '------ Tables Defined with Rowsize > 2M in all Schemas '
FROM dual;
set heading on
SELECT table_name, sum(data_length) row_length_over_2M
FROM all_tab_columns
WHERE owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
GROUP BY table_name
HAVING sum(data_length) > 2000000
/

set heading off
SELECT '------ Tables With No Primary Key or Unique Index and Column lenght > 1M '
FROM dual;
set heading on
SELECT owner, table_name
FROM all_tab_columns
WHERE owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
group by owner, table_name
HAVING sum(data_length) > 1000000
MINUS
(SELECT user1.name, obj1.name
FROM SYS.user$ user1,
SYS.user$ user2,
SYS.cdef$ cdef,
SYS.con$ con1,
SYS.con$ con2,
SYS.obj$ obj1,
SYS.obj$ obj2
WHERE user1.name not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
AND cdef.type# = 2 
AND con2.owner# = user2.user#(+)
AND cdef.robj# = obj2.obj#(+)
AND cdef.rcon# = con2.con#(+)
AND obj1.owner# = user1.user#
AND cdef.con# = con1.con#
AND cdef.obj# = obj1.obj#
UNION
SELECT idx.owner, idx.table_name
FROM all_indexes idx
WHERE idx.owner not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
AND idx.uniqueness = 'UNIQUE')
/

set heading off
SELECT '------ Tables With CLOB, BLOB, LONG, NCLOB or LONG RAW Columns in ALL Schemas ' 
FROM dual;
set heading on
SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM all_tab_columns
WHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
AND data_type in ('CLOB', 'BLOB', 'LONG', 'LONG RAW', 'NCLOB')
/

set heading off
SELECT '------ Tables With Columns of UNSUPPORTED Datatypes in ALL Schemas ' 
FROM dual;
set heading on
SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM all_tab_columns
WHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
AND (data_type in ('ORDDICOM', 'BFILE', 'TIMEZONE_REGION', 'BINARY_INTEGER', 'PLS_INTEGER', 'UROWID', 'URITYPE', 'MLSLABEL', 'TIMEZONE_ABBR', 'ANYDATA', 'ANYDATASET', 'ANYTYPE')
or data_type like 'INTERVAL%')
/

set heading off
SELECT '------ Cluster, or Object Tables - ALL UNSUPPORTED - in ALL Schemas '
FROM dual;
set heading on
SELECT OWNER, TABLE_NAME, CLUSTER_NAME, TABLE_TYPE 
FROM all_all_tables
WHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
AND (cluster_name is NOT NULL or TABLE_TYPE is NOT NULL)
/

set heading off 
Select '------ All tables that have compression enabled (which we do not currently support): '
from dual;
set heading on
select owner, table_name
from DBA_TABLES
where COMPRESSION = 'ENABLED'
/

SELECT TABLE_OWNER, TABLE_NAME, COMPRESSION
FROM ALL_TAB_PARTITIONS
WHERE (COMPRESSION = 'ENABLED')
/
set heading off
SELECT '------ IOT (Fully support for Oracle 10GR2 (with or without overflows) using GGS 10.4 and higher) - in All Schemas: ' 
FROM dual;
set heading on
SELECT OWNER, TABLE_NAME, IOT_TYPE, TABLE_TYPE 
FROM all_all_tables
WHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
AND (IOT_TYPE is not null or TABLE_TYPE is NOT NULL)
/
set heading off
SELECT '------ Tables with Domain or Context Indexes' 
FROM dual;
set heading on
SELECT OWNER, TABLE_NAME, index_name, index_type 
FROM dba_indexes 
WHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
and index_type = 'DOMAIN'
/

set heading off
SELECT '------ Types of Constraints on the Tables in ALL Schemas '
FROM dual;
set heading on
SELECT DECODE(constraint_type,'P','PRIMARY KEY','U','UNIQUE', 'C', 'CHECK', 'R', 
'REFERENTIAL') constraint_type_desc, count(*) total
FROM all_constraints
WHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
GROUP BY constraint_type
/
set heading off
SELECT '------ Cascading Deletes on the Tables in ALL Schemas ' 
FROM dual;
set heading on
SELECT owner, table_name, constraint_name
FROM all_constraints
WHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
and constraint_type = 'R' and delete_rule = 'CASCADE'
/

set heading off
SELECT '------ Tables Defined with Triggers in ALL Schema: '
FROM dual;
set heading on
SELECT table_name, COUNT(*) trigger_count
FROM all_triggers
WHERE OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
GROUP BY table_name
/
set heading off
SELECT '------ Performance issues - Reverse Key Indexes Defined in ALL Schema: '
FROM dual;

col TABLE_OWNER format a10
col INDEX_TYPE format a12
SET Heading on

select 
OWNER, 
INDEX_NAME,
INDEX_TYPE, 
TABLE_OWNER,
TABLE_NAME, 
TABLE_TYPE, 
UNIQUENESS,
CLUSTERING_FACTOR,
NUM_ROWS,
LAST_ANALYZED,
BUFFER_POOL
from dba_indexes
where index_type = 'NORMAL/REV'
And OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
/

SET Heading off
SELECT '------ Sequence numbers - Sequences could be a issue for HA configurations '
FROM dual;

COLUMN SEQUENCE_OWNER FORMAT a15
COLUMN SEQUENCE_NAME FORMAT a30
COLUMN INCR FORMAT 999
COLUMN CYCLE FORMAT A5
COLUMN ORDER FORMAT A5
SET Heading on
SELECT SEQUENCE_OWNER,
SEQUENCE_NAME,
MIN_VALUE,
MAX_VALUE,
INCREMENT_BY INCR,
CYCLE_FLAG CYCLE,
ORDER_FLAG "ORDER",
CACHE_SIZE,
LAST_NUMBER
FROM DBA_SEQUENCES
WHERE SEQUENCE_OWNER not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC')
/
set linesize 132

col "Avg Log Size" format 999,999,999
select sum (BLOCKS) * max(BLOCK_SIZE)/ count(*)"Avg Log Size" From gV$ARCHIVED_LOG;

Prompt Table: Frequency of Log Switches by hour and day
SELECT SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),1,5) DAY, 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'00',1,0)),'99') "00", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'01',1,0)),'99') "01", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'02',1,0)),'99') "02", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'03',1,0)),'99') "03", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'04',1,0)),'99') "04", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'05',1,0)),'99') "05", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'06',1,0)),'99') "06", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'07',1,0)),'99') "07", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'08',1,0)),'99') "08", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'09',1,0)),'99') "09", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'10',1,0)),'99') "10", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'11',1,0)),'99') "11", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'12',1,0)),'99') "12", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'13',1,0)),'99') "13", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'14',1,0)),'99') "14", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'15',1,0)),'99') "15", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'16',1,0)),'99') "16", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'17',1,0)),'99') "17", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'18',1,0)),'99') "18", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'19',1,0)),'99') "19", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'20',1,0)),'99') "20", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'21',1,0)),'99') "21", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'22',1,0)),'99') "22", 
TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'23',1,0)),'99') "23" 
FROM V$LOG_HISTORY 
GROUP BY SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),1,5) 
order by 1;
set heading off
SELECT '------ Summary of log volume processed by day for last 7 days: '
FROM dual;
set heading on
select to_char(first_time, 'mm/dd') ArchiveDate,
sum(BLOCKS*BLOCK_SIZE/1024/1024) LOGMB
from v$archived_log
where first_time > sysdate - 7
group by to_char(first_time, 'mm/dd')
order by to_char(first_time, 'mm/dd');
/
set heading off
SELECT '------ Summary of log volume processed per hour for last 7 days: ' 
FROM dual;
set heading on
select to_char(first_time, 'MM-DD-YYYY') ArchiveDate, 
to_char(first_time, 'HH24') ArchiveHour,
sum(BLOCKS*BLOCK_SIZE/1024/1024) LogMB
from v$archived_log
where first_time > sysdate - 7
group by to_char(first_time, 'MM-DD-YYYY'), to_char(first_time, 'HH24')
order by to_char(first_time, 'MM-DD-YYYY'), to_char(first_time, 'HH24');
/ 

set heading off
select '* This output may be found in file: AllSchemsCheckOracle.out' from dual
/

spool off
undefine b0
-- exit
Filed Under: GoldenGate, Oracle Tagged With:

Script:List Schema/Table Constraints

2011/09/07 by Leave a Comment

以下脚本可以用于列出相关模式或表上的Constraints约束:

---<tfsscons.sql begin>------------------------------------------------------
SET ECHO off
REM NAME: tfsscons.sql
REM USAGE:"@path/tfsscons"
REM --------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT permissions on dba_constraints, dba_cons_columns and
REM    dba_ind_columns.
REM --------------------------------------------------------------------------

SET ARRAYSIZE 1
SET FEEDBACK off
SET SERVEROUT on
SET PAGESIZE 66
SET NEWPAGE 6
SET LINESIZE 75
SET PAUSE off
SET VERIFY off
ACCEPT puser PROMPT 'Enter the schema name: '
ACCEPT pexcp PROMPT 'Enter the EXCEPTIONS table name for schema &puser: '
PROMPT 'NOTE: This will take some time, please wait...'

SPOOL schema_cons_&puser
DECLARE
    CURSOR cons_cur (v_userid VARCHAR2) IS
      SELECT * FROM dba_constraints
      WHERE owner = v_userid
        AND constraint_type in ('P','U','C','R')
      ORDER BY constraint_type;
    CURSOR col_cur (con_name VARCHAR2, con_owner VARCHAR2) IS
      SELECT * FROM dba_cons_columns
      WHERE owner = con_owner
        AND constraint_name = con_name
      ORDER BY position;
    CURSOR indx_cur (con_name VARCHAR2, ind_own VARCHAR2) IS
      SELECT a.*
      FROM dba_indexes a, dba_ind_columns b, dba_cons_columns c
      WHERE c.constraint_name = con_name
        AND a.owner = ind_own
        AND b.index_owner = ind_own
        AND c.owner = b.index_owner
        AND c.position = 1
        AND c.table_name = b.table_name
        AND c.column_name = b.column_name
        AND b.index_name = a.index_name;
    col_str VARCHAR2(200);
    v_user      VARCHAR2(30) := UPPER('&puser');
    v_output    VARCHAR2(480);    -- max of 16 cols at 30 chars each
    v_excp      NUMBER(1) := 0;
    v_excptab   VARCHAR2(60) := NULL;
    v_delrule   VARCHAR2(4);
    v_status    VARCHAR2(4);
    srch_cond   VARCHAR2(1000);
    v_errcode   NUMBER := 0;
    v_errmsg    varchar2(50) := ' ';
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);         -- Prevents buffer exceeded error
  BEGIN
    v_excptab := UPPER('&pexcp');
    IF v_excptab IS NOT NULL THEN
      SELECT 1
        INTO v_excp
      FROM dba_objects
      WHERE owner = UPPER('&puser')
        AND   object_name = UPPER('&pexcp');
      v_excptab := 'EXCEPTIONS INTO '||LOWER('&pexcp');
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Exceptions table does not exist in the schema: ');
    RAISE NO_DATA_FOUND;
    GOTO err;
  END;
  FOR c1 IN cons_cur(v_user) LOOP
    begin
      srch_cond := substr(c1.search_condition,1,length(c1.search_condition));
      -- Dont remove table constraint NOT NULL
      IF (instr(srch_cond,'NOT NULL') < 1) or
        (instr(srch_cond,'NOT NULL') IS NULL) THEN
        BEGIN
          DBMS_OUTPUT.PUT_LINE('ALTER TABLE '||C1.OWNER||'.'||C1.TABLE_NAME);
          DBMS_OUTPUT.PUT_LINE('  ADD (CONSTRAINT '||C1.CONSTRAINT_NAME);

          IF c1.constraint_type = 'P' THEN v_output := '    PRIMARY KEY (';
          ELSIF c1.constraint_type = 'R' THEN v_output := '    FOREIGN KEY (';
          ELSIF c1.constraint_type = 'U' THEN v_output := '    UNIQUE (';
          ELSIF c1.constraint_type = 'C' THEN
            v_output := '    CHECK ('||c1.search_condition||') '||v_excptab;
          END IF;

          FOR c2 IN col_cur(c1.constraint_name, c1.owner) LOOP
            IF c2.position = 1 THEN
              v_output := v_output||c2.column_name;
            ELSIF c2.position > 1 THEN
              v_output := v_output||', '||c2.column_name;
            END IF;
          END LOOP;
          v_output := v_output||')';
          DBMS_OUTPUT.PUT_LINE(v_output);
          IF c1.constraint_type = 'R' THEN
            v_output := NULL;
            FOR c3 IN col_cur(c1.r_constraint_name, c1.r_owner) LOOP
              IF c3.position = 1 THEN
                v_output := '    REFERENCES '||c3.owner||'.'||c3.table_name||'(';
                v_output := v_output||c3.column_name;
              ELSIF c3.position > 1 THEN
                v_output := v_output||', '||c3.column_name;
              END IF;
            END LOOP;
            v_output := v_output||')';
            DBMS_OUTPUT.PUT_LINE(v_output);
            v_delrule := substr(c1.delete_rule,1,2);
            IF v_delrule IS NULL THEN v_output := v_excptab ||  ' )';
            ELSIF v_delrule = 'NO' THEN v_output :=  v_excptab || ' )';
            ELSIF v_delrule = 'CA' THEN v_output := ' ON DELETE CASCADE '||v_excptab || ')';
            END IF;
            DBMS_OUTPUT.PUT_LINE(v_output);
          END IF;

          FOR c4 IN indx_cur(c1.constraint_name, c1.owner) LOOP
            IF c1.constraint_type in ('P','U') THEN
              DBMS_OUTPUT.PUT_LINE(' USING INDEX ');
              DBMS_OUTPUT.PUT_LINE('   pctfree       '||c4.pct_free);
              DBMS_OUTPUT.PUT_LINE('   initrans      '||c4.ini_trans);
              DBMS_OUTPUT.PUT_LINE('   maxtrans      '||c4.max_trans);
              DBMS_OUTPUT.PUT_LINE('   tablespace    '||c4.tablespace_name);
              DBMS_OUTPUT.PUT_LINE(' Storage (');
              DBMS_OUTPUT.PUT_LINE('   initial        '||c4.initial_extent);
              DBMS_OUTPUT.PUT_LINE('   next           '||c4.next_extent);
              DBMS_OUTPUT.PUT_LINE('   minextents'||c4.min_extents);
              DBMS_OUTPUT.PUT_LINE('   maxextents     '||c4.max_extents);
              DBMS_OUTPUT.PUT_LINE('   pctincrease    '||c4.pct_increase||') '|| v_excptab ||')');
            END IF;
          END LOOP;

          v_output := NULL;
          v_status := substr(c1.status,1,1);
          IF v_status = 'E' THEN
            v_output := ' REM This constraint '||c1.constraint_name||' was ENABLED';
          ELSIF v_status = 'D' THEN
            v_output :=' REM This constraint '||c1.constraint_name ||' was DISABLED';
          END IF;
          DBMS_OUTPUT.PUT_LINE('/ ');
          DBMS_OUTPUT.PUT_LINE(v_output);
          DBMS_OUTPUT.PUT_LINE('-------------------------------------------- ');
          DBMS_OUTPUT.PUT_LINE('  ');
        END;
      END IF;
    EXCEPTION
    WHEN no_data_found THEN
      DBMS_OUTPUT.PUT_LINE('No Data Found');
    WHEN others THEN
      v_errcode := sqlcode;
      v_errmsg := SUBSTR(sqlerrm, 1, 50);
      DBMS_OUTPUT.PUT_LINE('ERROR: '||v_errcode||': ' || v_errmsg);
      DBMS_OUTPUT.PUT_LINE(c1.constraint_name||' '||c1.constraint_type);
      DBMS_OUTPUT.PUT_LINE(c1.search_condition);
    END;
  END LOOP;
  <<err>>
  NULL;
END;
/
SPOOL off
SET PAGESIZE 14
SET FEEDBACK on
SET NEWPAGE 0
SET ARRAYSIZE 20
SET SERVEROUT off
SET LINESIZE 79
SET VERIFY on
---<tfsscons.sql end>------------------------------------------------------

---<tfstcons.sql begin>------------------------------------------------------
SET ECHO off
REM NAME: tfstcons.sql
REM USAGE:"@path/tfstcons"
REM --------------------------------------------------------------------------
REM REQUIREMENTS:
REM    SELECT permissions on dba_constraints, dba_cons_columns and
REM    dba_ind_columns.
REM --------------------------------------------------------------------------

SET ARRAYSIZE 1
SET SERVEROUT on
SET PAGESIZE 66
SET NEWPAGE 6
SET LINESIZE 75
SET PAUSE off
SET VERIFY off
SET FEEDBACK off
ACCEPT puser PROMPT 'Enter the schema name: '
ACCEPT ptab  PROMPT 'Enter the table name: '
ACCEPT pexcp PROMPT 'Enter the EXCEPTIONS table name for schema &puser: '
PROMPT 'NOTE: This will take some time, please be patient...'

SPOOL tab_cons_&ptab
DECLARE
    CURSOR cons_cur (v_userid VARCHAR2, v_tabname VARCHAR2) IS
      SELECT * FROM sys.dba_constraints
      WHERE owner = v_userid
        AND constraint_type in ('P','U','C','R')
        AND table_name = v_tabname
      ORDER BY constraint_type;
    CURSOR col_cur (con_name VARCHAR2, con_owner VARCHAR2) IS
      SELECT * FROM sys.dba_cons_columns
      WHERE owner = con_owner
        AND constraint_name = con_name
      ORDER BY position;
    CURSOR indx_cur (con_name VARCHAR2, ind_own VARCHAR2) IS
      SELECT a.*
      FROM sys.dba_indexes a, sys.dba_ind_columns b, sys.dba_cons_columns c
      WHERE c.constraint_name = con_name
        AND a.owner = ind_own
        AND b.index_owner = ind_own
        AND c.owner = b.index_owner
        AND c.position = 1
        AND c.table_name = b.table_name
        AND c.column_name = b.column_name
        AND b.index_name = a.index_name;
    col_str     VARCHAR2(200);
    v_user      VARCHAR2(30) := UPPER('&puser');
    v_tabname   VARCHAR2(30) := UPPER('&ptab');
    v_output    VARCHAR2(480);    -- max of 16 cols at 30 chars each
    v_dummy     NUMBER := 0;
    v_delrule   VARCHAR2(4);
    v_status    VARCHAR2(4);
    v_excp      NUMBER(1) := 0;
    v_excptab   VARCHAR2(60) := NULL;
    srch_cond   VARCHAR2(1000);
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);         -- Prevents buffer exceeded error

  SELECT 1                             -- Check to see if the table exists
  INTO v_dummy
  FROM dba_tables
  WHERE table_name = v_tabname
  AND   owner = v_user;
  BEGIN
    v_excptab := UPPER('&pexcp');
    IF v_excptab IS NOT NULL THEN
      SELECT 1
        INTO v_excp
      FROM sys.dba_objects
      WHERE owner = UPPER('&puser')
        AND   object_name = UPPER('&pexcp');
      v_excptab := 'EXCEPTIONS INTO '||LOWER('&pexcp');
    END IF;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Exceptions table does not exist in your schema: ');
    RAISE NO_DATA_FOUND;
    GOTO err;
  END;
  FOR c1 IN cons_cur(v_user, v_tabname) LOOP
    begin
      srch_cond := substr(c1.search_condition,1,length(c1.search_condition));
      -- Dont remove table constraint NOT NULL
      IF (instr(srch_cond,'NOT NULL') < 1) or
        (instr(srch_cond,'NOT NULL') IS NULL) THEN
        BEGIN
          DBMS_OUTPUT.PUT_LINE('ALTER TABLE '||C1.OWNER||'.'||C1.TABLE_NAME);
          DBMS_OUTPUT.PUT_LINE('  ADD (CONSTRAINT '||C1.CONSTRAINT_NAME);

          IF c1.constraint_type = 'P' THEN v_output := '    PRIMARY KEY (';
          ELSIF c1.constraint_type = 'R' THEN v_output := '    FOREIGN KEY (';
          ELSIF c1.constraint_type = 'U' THEN v_output := ' UNIQUE (';
          ELSIF c1.constraint_type = 'C' THEN
            v_output := '    CHECK ('||c1.search_condition||') '||v_excptab;
          END IF;

          FOR c2 IN col_cur(c1.constraint_name, c1.owner) LOOP
            IF c2.position = 1 THEN
              v_output := v_output||c2.column_name;
            ELSIF c2.position > 1 THEN
              v_output := v_output||', '||c2.column_name;
            END IF;
          END LOOP;
          v_output := v_output ||')';
          DBMS_OUTPUT.PUT_LINE(v_output);
          IF c1.constraint_type = 'R' THEN
            v_output := NULL;
            FOR c3 IN col_cur(c1.r_constraint_name, c1.r_owner) LOOP
              IF c3.position = 1 THEN
                v_output := '    REFERENCES '||c3.owner||'.'||c3.table_name||'(';
                v_output := v_output||c3.column_name;
              ELSIF c3.position > 1 THEN
                v_output := v_output||', '||c3.column_name;
              END IF;
            END LOOP;
            v_output := v_output||') ';
            DBMS_OUTPUT.PUT_LINE(v_output);
            v_delrule := substr(c1.delete_rule,1,2);
            IF v_delrule IS NULL THEN v_output :=  v_excptab ||' )';
            ELSIF v_delrule = 'NO' THEN v_output := v_excptab || ' )';
            ELSIF v_delrule = 'CA' THEN v_output := ' ON DELETE CASCADE '||v_excptab || ')';
            END IF;
            DBMS_OUTPUT.PUT_LINE(v_output);
          END IF;

          FOR c4 IN indx_cur(c1.constraint_name, c1.owner) LOOP
            IF c1.constraint_type in ('P','U') THEN
              DBMS_OUTPUT.PUT_LINE(' USING INDEX ');
              DBMS_OUTPUT.PUT_LINE('   pctfree  '||c4.pct_free);
              DBMS_OUTPUT.PUT_LINE('   initrans      '||c4.ini_trans);
              DBMS_OUTPUT.PUT_LINE('   maxtrans      '||c4.max_trans);
              DBMS_OUTPUT.PUT_LINE('   tablespace    '||c4.tablespace_name);
              DBMS_OUTPUT.PUT_LINE(' Storage (');
              DBMS_OUTPUT.PUT_LINE('   initial        '||c4.initial_extent);
              DBMS_OUTPUT.PUT_LINE('   next           '||c4.next_extent);
              DBMS_OUTPUT.PUT_LINE(' minextents     '||c4.min_extents);
              DBMS_OUTPUT.PUT_LINE('   maxextents     '||c4.max_extents);
              DBMS_OUTPUT.PUT_LINE('   pctincrease    '||c4.pct_increase||') '|| v_excptab ||')');
            END IF;
          END LOOP;

          v_output := NULL;
          v_status := substr(c1.status,1,1);
          IF v_status = 'E' THEN
            v_output := ' REM This constraint '||c1.constraint_name||' was ENABLED';
          ELSIF v_status = 'D' THEN
            v_output :=' REM This constraint '||c1.constraint_name ||' was DISABLED';
          END IF;
          DBMS_OUTPUT.PUT_LINE('/ ');
          DBMS_OUTPUT.PUT_LINE(v_output);
          DBMS_OUTPUT.PUT_LINE('-------------------------------------------- ');
          DBMS_OUTPUT.PUT_LINE(' ');
        END;
      END IF;
    EXCEPTION
      WHEN no_data_found THEN
        DBMS_OUTPUT.PUT_LINE('No Data Found');
      WHEN others THEN
        DBMS_OUTPUT.PUT_LINE('Other: '||substr(sqlerrm,1,60));
        DBMS_OUTPUT.PUT_LINE(c1.constraint_name||' '||c1.constraint_type);
        DBMS_OUTPUT.PUT_LINE(c1.search_condition);
    END;
  END LOOP;
  <<err>>
  NULL;
EXCEPTION
WHEN no_data_found THEN
DBMS_OUTPUT.PUT_LINE('This table: '||v_tabname||', Does not exist or has no constraints!');
END;
/
SPOOL off
SET PAGESIZE 14
SET FEEDBACK on
SET NEWPAGE 0
SET ARRAYSIZE 20
SET SERVEROUT off
SET LINESIZE 79
SET VERIFY on
---<tfstcons.sql end>------------------------------------------------------
Filed Under: Oracle, Oracle脚本script Tagged With: ,

Script:RAC Failover检验脚本loop.sh

2011/08/30 by Leave a Comment

以下脚本可以用于验证RAC中FAILOVER的可用性:

loop.sh
  nohup sqlplus su/su@failover @verify.sql &
     sleep 1
  nohup sqlplus su/su@failover @verify.sql &
     sleep 1
  nohup sqlplus su/su@failover @verify.sql &
     sleep 1
  nohup sqlplus su/su@failover @verify.sql &
     sleep 1

verify.sql (检验SQL)
  REM  set pagesize 1000
  REM  the following query is for TAF connection verification
  col sid format 999
  col serial# format 9999999
  col failover_type format a13
  col failover_method format a15
  col failed_over format a11
  select sid, serial#, failover_type, failover_method, failed_over
    from v$session where username = 'SU';

  REM  the following query is for load balancing verification
  select instance_name from v$instance;
  exit

  REM you can also combine two queries:
  col inst_id format 999
  col sid format 999
  col serial# format 9999999
  col failover_type format a13
  col failover_method format a15
  col failed_over format a11

  select inst_id, sid, serial#, failover_type, failover_method,
         failed_over from gv$session where username = 'SU';

  REM  a simple select to see the distribution of users when testing 
  REM  connection load balancing

  select inst_id, count(*) from gv$session group by inst_id;

用法：
./loop.sh
Filed Under: Oracle, Oracle RAC, Oracle脚本script Tagged With: , ,

Script:Diagnostic Resource Manager

2011/08/30 by Leave a Comment

以下脚本可以用于诊断Oracle 10g以后的Resource Manager信息:

set echo on;
    set linesize 300;
    set pages 1000;
    set numwidth 10;
    set trimspool on;
    col VALUE for a30;
    col ATTRIBUTE for a15;
    col GRANTEE for a25;
    col CPU_METHOD for a15;
    col COMMENTS for a30;
    col MGMT_METHOD for a15;
    col STATUS for a10;
    alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
    spool info.lst;
    SELECT SYSDATE FROM DUAL;
    SELECT * FROM DBA_RSRC_MANAGER_SYSTEM_PRIVS;
    SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS;
    SELECT * FROM DBA_RSRC_CONSUMER_GROUPS;
    SELECT * FROM DBA_RSRC_PLANS;
    SELECT * FROM DBA_RSRC_PLAN_DIRECTIVES;
    SELECT * FROM V$RSRC_CONSUMER_GROUP;
    SELECT * FROM V$RSRC_CONSUMER_GROUP_CPU_MTH;
    SELECT * FROM V$RSRC_PLAN;
    SELECT * FROM V$RSRC_PLAN_CPU_MTH;
    SELECT * FROM DBA_RSRC_MAPPING_PRIORITY;
    SELECT * FROM DBA_RSRC_GROUP_MAPPINGS;
    -- For 10gR2 -------
    SELECT * FROM V$RSRC_CONS_GROUP_HISTORY;
    SELECT * FROM V$RSRC_PLAN_HISTORY;
    SELECT * FROM V$RSRC_SESSION_INFO;
    -- FOR 11gR1 -------
    SELECT * FROM V$RSRCMGRMETRIC
    order by BEGIN_TIME,END_TIME,CONSUMER_GROUP_ID;
    SELECT * FROM V$RSRCMGRMETRIC_HISTORY
    order by BEGIN_TIME,END_TIME,CONSUMER_GROUP_ID;
    spool off;

 create_sample_plan.sql
    ----------------------------------------------------------------------------
    set echo on
    begin
       dbms_resource_manager.create_pending_area();
    end;
    /
    begin
       dbms_resource_manager.create_plan(
          plan => 'ONLINE_PLAN',
          comment => 'Resource plan/method for Day Time On-Line sessions');
       dbms_resource_manager.create_plan(
          plan => 'BATCH_PLAN',
          comment => 'Resource plan/method for Night Time Batch sessions');
    end;
    /

opatch lsinventory -detail

 top -b -d 1 -n 3600 >> toplog
 sar -P ALL 1 3600 >> sarlog

ALTER SYSTEM SET resource_manager_plan='';
Filed Under: Oracle, Oracle脚本script Tagged With: ,

Script:List Grid Control Jobs

2011/08/29 by 1 Comment

以下脚本可以用于列出Grid Control中的定式作业:

SET verify OFF
SET linesize 255
SET pagesize 128
SET trimout ON
SET trimspool ON
SPOOL jobdump.log
ALTER SESSION SET nls_date_format='MON-DD-YYYY hh:mi:ss pm';
 
COLUMN status format a15
 
COLUMN job_name FORMAT a64
COLUMN job_type FORMAT a32
COLUMN job_owner FORMAT a32
COLUMN job_status format 99
COLUMN target_type format a64
 
COLUMN frequency_code format a20
COLUMN  interval format 99999999
 
VARIABLE JOBID VARCHAR2(64);
 
PROMPT *********************** JOB INFO ********************************
 
REM Get the job id
SET serveroutput on
BEGIN
    SELECT job_id INTO :JOBID
    FROM   MGMT_JOB
    WHERE  job_name='&&jobName'
    AND    job_owner='&&jobOwner'
    AND    nested=0;
 
EXCEPTION
    WHEN NO_DATA_FOUND THEN
    BEGIN
        DBMS_OUTPUT.put_line('JOB NOT FOUND, TRYING NAME ONLY');
        SELECT job_id INTO :JOBID
        FROM   MGMT_JOB
        WHERE  job_name='&&jobName'
        AND    nested=0
        AND    ROWNUM=1;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.put_line('JOB NOT FOUND');
    END;
END;
/
 
SELECT  job_name, job_owner, job_type, system_job, job_status, target_type
FROM    MGMT_JOB
WHERE   job_id=HEXTORAW(:JOBID);
 
PROMPT *********************** JOB SCHEDULE ****************************
SELECT  DECODE(frequency_code,
               1, 'Once',
               2, 'Interval',
               3, 'Daily',
               4, 'Day of Week',
               5, 'Day of Month',
               6, 'Day of Year', frequency_code) "FREQUENCY_CODE",
        start_time, end_time, execution_hours, execution_minutes,
        interval, months, days, timezone_info, timezone_target_index,
        timezone_offset, timezone_region
FROM    MGMT_JOB_SCHEDULE s, MGMT_JOB j
WHERE   s.schedule_id=j.schedule_id
AND     j.job_id=HEXTORAW(:JOBID);
 
PROMPT ********************** PARAMETERS ********************************
SELECT  parameter_name,
        decode(parameter_type,
               0, 'Scalar',
               1, 'Vector',
               2, 'Large', parameter_type) "PARAMETER_TYPE",
        scalar_value, vector_value
FROM    MGMT_JOB_PARAMETER
WHERE   job_id=HEXTORAW(:JOBID)
AND     execution_id=HEXTORAW('0000000000000000')
ORDER BY parameter_name;
 
PROMPT ********************** TARGETS ********************************
SELECT  target_name, target_type
FROM    MGMT_JOB_TARGET jt, MGMT_TARGETS t
WHERE   job_id=HEXTORAW(:JOBID)
AND     execution_id=HEXTORAW('0000000000000000')
AND     jt.target_guid=t.target_guid
ORDER BY target_type, target_name;
 
PROMPT ********************** FLAT TARGETS ********************************
SELECT  target_name, target_type
FROM    MGMT_JOB_FLAT_TARGETS jft, MGMT_TARGETS t
WHERE   job_id=HEXTORAW(:JOBID)
AND     jft.target_guid=t.target_guid
ORDER BY target_type, target_name;
 
 
PROMPT ************************ EXECUTIONS *******************************
SELECT  execution_id,
        DECODE(status,
               1, 'SCHEDULED',
               2, 'RUNNING',
               3, 'FAILED INIT',
               4, 'FAILED',
               5, 'SUCCEEDED',
               6, 'SUSPENDED',
               7, 'AGENT DOWN',
               8, 'STOPPED',
               9, 'SUSPENDED/LOCK',
               10, 'SUSPENDED/EVENT',
               11, 'SUSPENDED/BLACKOUT',
               12, 'STOP PENDING',
               13, 'SUSPEND PENDING',
               14, 'INACTIVE',
               15, 'QUEUED',
               16, 'FAILED/RETRIED',
               17, 'WAITING',
               18, 'SKIPPED', status) "STATUS",
        scheduled_time, start_time, end_time
FROM    MGMT_JOB_EXEC_SUMMARY e
WHERE   job_id=HEXTORAW(:JOBID)
ORDER BY scheduled_time;
 
 
UNDEFINE jobName
UNDEFINE jobOwner
UNDEFINE JOBID


SPOOL OFF
Filed Under: Oracle, Oracle脚本script Tagged With: , , , ,

Script:GridControl Repository Health Check

2011/08/29 by 1 Comment

以下脚本可以用于检查Grid Control(OMS) Repository的健康程度:

set linesize 130
set pagesize 50000
Set feedback off
set heading off
SPOOL RepositoryHealth.log

PROMPT ********* Report to capture the Health of the EM Grid Control Repository  ******

Prompt
Prompt ***   EM Vital Statistics   ***
Prompt --------------------------------

--- Total number of targets monitored by EM
select 'Total Targets=', TO_CHAR(count(*)) from mgmt_targets;

--- Number of targets that are not listed with an 'UP' availability status.
select 'Targets Not Up=',to_char(count(*)) from mgmt_current_availability where current_status != 1;

--- Loader Thread count
select  'Loader Threads=', TO_CHAR(count(distinct key_value))
from mgmt_metrics_1hour h, mgmt_metrics m, mgmt_targets t where
t.target_type = 'oracle_emrep' and
m.target_type = t.target_type and
m.metric_name = 'Management_Loader_Status' and
m.metric_column = 'load_processing' and
m.metric_guid = h.metric_guid and
h.target_guid = t.target_guid and
rollup_timestamp > sysdate-7 and
t.type_meta_ver = m.type_meta_ver and
(t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
(t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
(t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
(t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
(t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ');

--- Rows per second per loader thread.
select 'Avg Loader Rows/Second/Thread=', TO_CHAR(round(avg(value_average),2))
from mgmt_metrics_1hour h, mgmt_metrics m, mgmt_targets t where
t.target_type = 'oracle_emrep' and
m.target_type = t.target_type and
m.metric_name = 'Management_Loader_Status' and
m.metric_column = 'load_processing' and
m.metric_guid = h.metric_guid and
h.target_guid = t.target_guid and
rollup_timestamp > sysdate-7 and
t.type_meta_ver = m.type_meta_ver and
(t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
(t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
(t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
(t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
(t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')
group by metric_name, metric_column;
 
--- Loader rows per hour
select 'Avg Loader Rows/Hour/Thread=', TO_CHAR(round(avg(value_average),2))
from mgmt_metrics_1hour h, mgmt_metrics m, mgmt_targets t where
t.target_type = 'oracle_emrep' and
m.target_type = t.target_type and
m.metric_name = 'Management_Loader_Status' and
m.metric_column = 'loader_processing_hour' and
m.metric_guid = h.metric_guid and
h.target_guid = t.target_guid and
rollup_timestamp > sysdate-7 and
t.type_meta_ver = m.type_meta_ver and
(t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
(t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
(t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
(t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
(t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')
group by metric_name, metric_column;
 
--- Loader thread run time % of hour
select 'Avg Loader Pct Hour Run/Thread=', TO_CHAR(round((avg(value_average)/3600)*100,2))
from mgmt_metrics_1hour h, mgmt_metrics m, mgmt_targets t where
t.target_type = 'oracle_emrep' and
m.target_type = t.target_type and
m.metric_name = 'Management_Loader_Status' and
m.metric_column = 'load_run' and
m.metric_guid = h.metric_guid and
h.target_guid = t.target_guid and
rollup_timestamp > sysdate-7 and
t.type_meta_ver = m.type_meta_ver and
(t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
(t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
(t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
(t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
(t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')
group by metric_name, metric_column;
 
--- Rollup Rows per hour
select 'Avg Rollup Rows/Hour=', TO_CHAR(round(avg(value_average),2))
from mgmt_metrics_1hour h, mgmt_metrics m, mgmt_targets t where
t.target_type = 'oracle_emrep' and
m.target_type = t.target_type and
m.metric_name = 'DBMS_Job_Status' and
m.metric_column = 'jobthroughput' and
h.key_value = 'Rollup' and
m.metric_guid = h.metric_guid and
h.target_guid = t.target_guid and
rollup_timestamp > sysdate-7 and
t.type_meta_ver = m.type_meta_ver and
(t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
(t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
(t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
(t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
(t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')
group by metric_name, metric_column;

--- Rollup % of hour run
select 'Avg Rollup Pct Hour Run=', TO_CHAR(round(avg(value_average),2))
from mgmt_metrics_1hour h, mgmt_metrics m, mgmt_targets t where
t.target_type = 'oracle_emrep' and
m.target_type = t.target_type and
m.metric_name = 'DBMS_Job_Status' and
m.metric_column = 'jobprocessing' and
h.key_value = 'Rollup' and
m.metric_guid = h.metric_guid and
h.target_guid = t.target_guid and
rollup_timestamp > sysdate-7 and
t.type_meta_ver = m.type_meta_ver and
(t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
(t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
(t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
(t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
(t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')
group by metric_name, metric_column;

--- Number of job dispatchers (hint: equal to number of Management Servers)
select  'Job Dispatchers=', TO_CHAR(count(distinct key_value))
from mgmt_metrics_1hour h, mgmt_metrics m, mgmt_targets t where
t.target_type = 'oracle_emrep' and
m.target_type = t.target_type and
m.metric_name = 'Job_Dispatcher_Performance' and
m.metric_column = 'throughput' and
m.metric_guid = h.metric_guid and
h.target_guid = t.target_guid and
rollup_timestamp > sysdate-7 and
t.type_meta_ver = m.type_meta_ver and
(t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
(t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
(t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
(t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
(t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ');

 
--- Job steps processed per second
select 'Avg Job Steps/Second=', TO_CHAR(round(avg(value_average),2))
from mgmt_metrics_1hour h, mgmt_metrics m, mgmt_targets t where
t.target_type = 'oracle_emrep' and
m.target_type = t.target_type and
m.metric_name = 'Job_Dispatcher_Performance' and
m.metric_column = 'throughput' and
m.metric_guid = h.metric_guid and
h.target_guid = t.target_guid and
rollup_timestamp > sysdate-7 and
t.type_meta_ver = m.type_meta_ver and
(t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
(t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
(t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
(t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
(t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')
group by metric_name, metric_column;

 
---  Notifications Per Second
select 'Avg Notifications/Second=', TO_CHAR(round(avg(value_average),2))
from mgmt_metrics_1hour h, mgmt_metrics m, mgmt_targets t where
t.target_type = 'oracle_emrep' and
m.target_type = t.target_type and
m.metric_name = 'Notification_Performance' and
m.metric_column = 'notificationthroughput' and
m.metric_guid = h.metric_guid and
h.target_guid = t.target_guid and
rollup_timestamp > sysdate-7 and
t.type_meta_ver = m.type_meta_ver and
(t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
(t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
(t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
(t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
(t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')
group by metric_name, metric_column;

--- Notification % of hour run
select 'Avg Notification Pct Hour Run=', TO_CHAR(round(avg(value_average),2))
from mgmt_metrics_1hour h, mgmt_metrics m, mgmt_targets t where
t.target_type = 'oracle_emrep' and
m.target_type = t.target_type and
m.metric_name = 'Notification_Performance' and
m.metric_column = 'notificationprocessing' and
m.metric_guid = h.metric_guid and
h.target_guid = t.target_guid and
rollup_timestamp > sysdate-7 and
t.type_meta_ver = m.type_meta_ver and
(t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
(t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
(t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
(t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
(t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')
group by metric_name, metric_column;

--- Severities per hour
select 'Avg Severities Per Hour=', TO_CHAR(round(avg(count(*)),2)) as sev_count from mgmt_severity
where collection_timestamp > sysdate - 7
group by trunc(collection_timestamp, 'HH');

--- OMS Host CPU Util
select 'Avg Management Server Host CPU=', TO_CHAR(round(avg(value_average),2)), t.target_name
from mgmt_metrics_1hour h, mgmt_targets t where
h.target_guid in (select target_guid from mgmt_targets t where
target_type = 'host' and
target_name in (select substr(host_url,1,instr(host_url, '_Management_Service',-1,1)-1)
                                 from mgmt_oms_parameters)) and
h.metric_guid = (select m.metric_guid from mgmt_metrics m where
                          m.target_type = 'host' and
                          m.metric_name = 'Load' and
                          m.metric_column = 'cpuUtil'and
                          t.type_meta_ver = m.type_meta_ver and
                         (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
                         (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
                         (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
                         (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
                         (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')) and
h.target_guid = t.target_guid and
rollup_timestamp > sysdate-7
group by t.target_name;

--- Create a small table to get DB Guids. GV$INSTANCE performance is inconsistant across db versions.
create table mgmt_temp_vsign_db_guids as select h.target_guid from mgmt_targets h where
                   h.target_type = 'host' and
                   h.target_name in (select s.target_name from mgmt_targets s, gv$instance g where
                                                      s.target_type = 'host' and
                                                      s.target_name like g.host_name || '%');

--- Run CBO stat analysis on temp table for performance.
exec dbms_stats.gather_table_stats('SYSMAN','MGMT_TEMP_VSIGN_DB_GUIDS',NULL, DBMS_STATS.AUTO_SAMPLE_SIZE, FALSE,'FOR ALL COLUMNS SIZE AUTO',NULL,'GLOBAL',TRUE,NULL,NULL,NULL);

--- EM Repository CPU
select 'Avg Management Repository Host CPU=', TO_CHAR(round(avg(value_average),2)), t.target_name
from mgmt_metrics_1hour h, mgmt_targets t where
h.target_guid in (select target_guid from mgmt_temp_vsign_db_guids) and
h.metric_guid = (select m.metric_guid from mgmt_metrics m where
                           m.target_type = 'host' and
                           m.metric_name = 'Load' and
                           m.metric_column = 'cpuUtil'and
                           t.type_meta_ver = m.type_meta_ver and
                          (t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
                          (t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
                          (t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
                          (t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
                          (t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ')) and
h.target_guid = t.target_guid and
rollup_timestamp > sysdate-7
group by t.target_name;

--- Repository Used Space
select 'Repository Used Space (GB)=',  TO_CHAR(round(max(value_average)/1000,2))
from mgmt_metrics_1hour h, mgmt_metrics m, mgmt_targets t where
t.target_type = 'oracle_emrep' and
m.target_type = t.target_type and
m.metric_name = 'Configuration' and
m.metric_column = 'usedRepSpace' and
m.metric_guid = h.metric_guid and
h.target_guid = t.target_guid and
rollup_timestamp > sysdate-1 and
t.type_meta_ver = m.type_meta_ver and
(t.category_prop_1 = m.category_prop_1 OR m.category_prop_1 = ' ') and
(t.category_prop_2 = m.category_prop_2 OR m.category_prop_2 = ' ') and
(t.category_prop_3 = m.category_prop_3 OR m.category_prop_3 = ' ') and
(t.category_prop_4 = m.category_prop_4 OR m.category_prop_4 = ' ') and
(t.category_prop_5 = m.category_prop_5 OR m.category_prop_5 = ' ');


--- Drop the temp table
drop table mgmt_temp_vsign_db_guids;
 
set heading on

PROMPT ------------------------------------------------------------------------------------------
PROMPT
PROMPT **** The number of management Errors that have occurred per Module in the past 24 hours
 SELECT module_name Module, COUNT(*)
        FROM   mgmt_system_error_log
        WHERE  occur_date> SYSDATE-1
        GROUP BY module_name
        ORDER BY module_name;

PROMPT **** The Following Errors have occurred in the past two days
alter session set nls_date_format='MM/DD/YYYY HH24:MI';
column Module format a15
column Agent format a20
column error_msg format a60
select occur_date, module_name Module, emd_url Agent, error_msg
  from mgmt_system_error_log
 where occur_date > sysdate - 2
 order by occur_date;


 
PROMPT ------------------------------------------------------------------------------------------

PROMPT **** Check the status of all DBMS_JOBS and look for broken jobs or next_run data that is already in the past.
column Job format a8
column interval format a25
column what format a45

        SELECT to_char(job) Job, TO_CHAR(next_date,'DD-MON-YYYY HH24:MI:SS') next_run,
           interval, broken, what
        FROM   user_jobs
        ORDER BY next_date DESC;

PROMPT  -----------------------------------------------------------------------------------------

PROMPT **** Show the performance statistics of all jobs over the last 24 hours:
column Job_name format a50
     SELECT job_name, COUNT(*) n_recs,
            MIN(duration) min_duration, MAX(duration) max_duration, ROUND(AVG(duration),2) avg_duration
     FROM   mgmt_system_performance_log
     WHERE  is_total = 'Y'
       AND  time> SYSDATE-1
     GROUP BY job_name
     ORDER BY job_name;

 
PROMPT ------------------------------------------------------------------------------------------

PROMPT **** Overview of all Agent XML activity of the last day:
SELECT module, cnt "Number", TO_CHAR(FLOOR(mins/60),'999')||':'||TO_CHAR(MOD(mins,60),'09')||':'||TO_CHAR(MOD(secs,60),'09') "Time Spent"
FROM   (SELECT module, COUNT(*) cnt, ROUND(SUM(duration)/1000) secs, FLOOR(SUM(duration)/60000) mins
        FROM   mgmt_system_performance_log
        WHERE  job_name = 'LOADER'
          AND  time > SYSDATE-1
        GROUP BY module)
ORDER BY cnt DESC;

spool off
Filed Under: Oracle, Oracle脚本script Tagged With: , , ,

Script:诊断Scheduler信息

2011/08/29 by 1 Comment

以下脚本可以用于针对10g以后的Scheduler信息:

set long 400
set pagesize 1000
set linesize 120
column log_date format a37
column operation format a8
column status format a6
column additional_info format a400
column systimestamp format a37
column next_start_date format a40
column start_date format a40
column manual_open_time format a40
column manual_duration format a40
column duration format a40
column end_date format a40
column last_start_date format a40
column window_name format a26
column systimestamp format a35
column ATTR_INTV format a37
column ATTR_TSTAMP format a37
column start_time format a35
column obj_name format a20
column name format a30
column value format a30
column job_queue_name format a18  
column job_type format 9999
column flag format 9999  
column status format a6
column "SEQUENCE#" format 9999
column id format 99999
spool wintest
select * from dba_scheduler_windows ;
select log_date, window_name, operation,status, substr(additional_info,1,350)
 from dba_scheduler_window_log order by 1 ;
select window_name, substr(additional_info,1,350) x 
 from dba_scheduler_window_log where additional_info is not null;

-- Report current time in scheduler format
select dbms_scheduler.stime from dual;
-- Report Internal Queues v10.2(before)
select job_queue_name,   JOB_TYPE,  
    (select object_name from dba_objects where object_id = job_oid
      ) OBJ_NAME,  FLAG, start_time from x$jskjobq;
-- Report current open window attributes(before)
SELECT o.name, o.namespace, a.*
　FROM sys.obj$ o, sys.scheduler$_global_attribute a
　　WHERE o.obj# = a.obj# AND BITAND(a.flags,1) != 1
　　　AND o.name = 'CURRENT_OPEN_WINDOW' AND o.namespace = 51;

-- To implement the solution, please execute the following steps::
exec dbms_scheduler.disable('WEEKEND_WINDOW');
exec dbms_scheduler.disable('WEEKNIGHT_WINDOW');
exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');
select value from v$parameter where name='job_queue_processes';
alter system set job_queue_processes=0;
exec dbms_ijob.set_enabled(FALSE);
alter system flush shared_pool;
alter system flush shared_pool;

declare
cwo number;
begin
select o.obj# into cwo from sys.obj$ o
  where o.name = 'CURRENT_OPEN_WINDOW' and o.namespace = 51;
update sys.scheduler$_global_attribute set value = null, attr_tstamp = null,
attr_intv = null, additional_info = null where obj# = cwo;
end;
/
exec dbms_ijob.set_enabled(TRUE);
exec dbms_scheduler.enable('WEEKEND_WINDOW');
exec dbms_scheduler.enable('WEEKNIGHT_WINDOW');
exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');
exec dbms_ijob.set_enabled(TRUE);

-- Report current time in scheduler format
select dbms_scheduler.stime from dual;
-- Report Internal Queues v10.2(after)
select job_queue_name, JOB_TYPE,
 (select object_name from dba_objects where object_id = job_oid
  ) OBJ_NAME, FLAG, start_time from x$jskjobq;
-- Report current open window attributes(after)
SELECT o.name, o.namespace, a.*
 FROM sys.obj$ o, sys.scheduler$_global_attribute a
  WHERE o.obj# = a.obj# AND BITAND(a.flags,1) != 1
   AND o.name = 'CURRENT_OPEN_WINDOW' AND o.namespace = 51;

ACCEPT J_NUM NUMBER PROMPT "Enter job_queue_processes: "
prompt Setting job_queue_processes=&&J_NUM
alter system set job_queue_processes=&&J_NUM;
select value from v$parameter where name='job_queue_processes';
spool off
Filed Under: Oracle, Oracle脚本script Tagged With: , , ,

沪ICP备14014813号-2

沪公网安备 31010802001379号