以下脚本可以用于检测数据库当前是否有备份操作在执行中:
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
/
以下脚本可以用于列出相关模式或表上的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>------------------------------------------------------
以下脚本可以用于验证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
以下脚本可以用于诊断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='';
以下脚本可以用于列出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
以下脚本可以用于检查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
以下脚本可以用于针对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
在10g RAC中我们常用crs_stat命令查看CRS资源的状态，但是crs_stat命令的输出并不完整。可以通过以下脚本来优化crs_stat的输出：
--------------------------- Begin Shell Script -------------------------------
#!/usr/bin/ksh
#
# Sample 10g CRS resource status query script
#
# Description:
# - Returns formatted version of crs_stat -t, in tabular
# format, with the complete rsc names and filtering keywords
# - The argument, $RSC_KEY, is optional and if passed to the script, will
# limit the output to HA resources whose names match $RSC_KEY.
# Requirements:
# - $ORA_CRS_HOME should be set in your environment
RSC_KEY=$1
QSTAT=-u
AWK=/usr/xpg4/bin/awk # if not available use /usr/bin/awk
# Table header:echo ""
$AWK \
'BEGIN {printf "%-45s %-10s %-18s\n", "HA Resource", "Target", "State";
printf "%-45s %-10s %-18s\n", "-----------", "------", "-----";}'
# Table body:
$ORA_CRS_HOME/bin/crs_stat $QSTAT | $AWK \
'BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$RSC_KEY'/ {appname = $2; state=1};
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
state == 3 {printf "%-45s %-10s %-18s\n", appname, apptarget, appstate; state=0;}'
--------------------------- End Shell Script -------------------------------
以下脚本可以用于诊断Oracle实例中的锁情况(Lock Status):
REM SCRIPT: FULLY DECODED LOCKING
set echo off
set lines 200
set pagesize 66
break on Kill on sid on username on terminal
column Kill heading 'Kill String' format a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column locking heading 'Lock Held/Lock Requested' format a40
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading "Username"
column terminal heading Term format a6
column obj format a30 heading "Table/Sequence Name"
column owner format a9
column LAddr heading "ID1 - ID2" format a18
column Lockt heading "Lock Type" format a40
column command heading "Command" format a25
column sid format 990
select
nvl(s.username,'Internal') username,
l.sid,
nvl(s.terminal,'None') terminal,
decode(l.type,'TM',u.name||'.'||substr(t.name,1,20),
'DL',u.name||'.'||substr(t.name,1,20),
'SQ',u.name||'.'||substr(t.name,1,20),'None') obj,
decode(command,
0,'None',
1,'CREATE TABLE',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP CLUSTER',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'CREATE SEQUENCE',
14,'ALTER SEQUENCE',
15,'ALTER TABLE',
16,'DROP SEQUENCE',
17,'GRANT',
18,'REVOKE',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK TABLE',
27,'NO OPERATION',
28,'RENAME',
29,'COMMENT',
30,'AUDIT',
31,'NOAUDIT',
32,'CREATE DATABASE LINK',
33,'DROP DATABASE LINK',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEGMENT',
37,'ALTER ROLLBACK SEGMENT',
38,'DROP ROLLBACK SEGMENT',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM SWITCH LOG',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
58,'ALTER TRACING',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
69,'-',
70,'ALTER RESOURCE COST',
71,'CREATE SNAPSHOT LOG',
72,'ALTER SNAPSHOT LOG',
73,'DROP SNAPSHOT LOG',
74,'CREATE SNAPSHOT',
75,'ALTER SNAPSHOT',
76,'DROP SNAPSHOT',
77,'CREATE TYPE',
78,'DROP TYPE',
79,'ALTER ROLE',
80,'ALTER TYPE',
81,'CREATE TYPE BODY',
82,'ALTER TYPE BODY',
83,'DROP TYPE BODY',
84,'DROP LIBRARY',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
87,'CREATE BITMAP FILE',
88,'ALTER VIEW',
89,'DROP BITMAP FILE',
90,'SET CONSTRAINTS',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PACKAGE BODY',
98,'ALTER PACKAGE BODY',
99,'DROP PACKAGE BODY',
command||' - ???') command,
decode(l.lmode,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive','NONE') lmode,
decode(l.request,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Share Row Exclusive',
6,'Exclusive','NONE') request,
l.id1||'-'||l.id2 Laddr,
l.type||' - '||
decode(l.type,
'BL','Buffer hash table instance',
'CF',' Control file schema global enqueue',
'CI','Cross-instance function invocation instance',
'CU','Cursor bind',
'DF','Data file instance',
'DL','Direct loader parallel index create',
'DM','Mount/startup db primary/secondary instance',
'DR','Distributed recovery process',
'DX','Distributed transaction entry',
'FS','File set',
'HW','Space management operations on a specific segment',
'IN','Instance number',
'IR','Instance recovery serialization global enqueue',
'IS','Instance state',
'IV','Library cache invalidation instance',
'JQ','Job queue',
'KK','Thread kick',
'LA','Library cache lock instance (A=namespace)',
'LB','Library cache lock instance (B=namespace)',
'LC','Library cache lock instance (C=namespace)',
'LD','Library cache lock instance (D=namespace)',
'LE','Library cache instance lock (E=namespace)',
'LF','Library cache instance lock (F=namespace)',
'LG','Library cache instance lock (G=namespace)',
'LH','Library cache instance lock (H=namespace)',
'LI','Library cache instance lock (I=namespace)',
'LJ','Library cache instance lock (J=namespace)',
'LK','Library cache instance lock (K=namespace)',
'LL','Library cache instance lock (L=namespace)',
'LM','Library cache instance lock (M=namespace)',
'LN','Library cache instance lock (N=namespace)',
'LO','Library cache instance lock (O=namespace)',
'LP','Library cache instance lock (P=namespace)',
'MM','Mount definition gloabal enqueue',
'MR','Media recovery',
'NA','Library cache pin instance (A=namespace)',
'NB','Library cache pin instance (B=namespace)',
'NC','Library cache pin instance (C=namespace)',
'ND','Library cache pin instance (D=namespace)',
'NE','Library cache pin instance (E=namespace)',
'NF','Library cache pin instance (F=namespace)',
'NG','Library cache pin instance (G=namespace)',
'NH','Library cache pin instance (H=namespace)',
'NI','Library cache pin instance (I=namespace)',
'NJ','Library cache pin instance (J=namespace)',
'NL','Library cache pin instance (K=namespace)',
'NK','Library cache pin instance (L=namespace)',
'NM','Library cache pin instance (M=namespace)',
'NN','Library cache pin instance (N=namespace)',
'NO','Library cache pin instance (O=namespace)',
'NP','Library cache pin instance (P=namespace)',
'NQ','Library cache pin instance (Q=namespace)',
'NR','Library cache pin instance (R=namespace)',
'NS','Library cache pin instance (S=namespace)',
'NT','Library cache pin instance (T=namespace)',
'NU','Library cache pin instance (U=namespace)',
'NV','Library cache pin instance (V=namespace)',
'NW','Library cache pin instance (W=namespace)',
'NX','Library cache pin instance (X=namespace)',
'NY','Library cache pin instance (Y=namespace)',
'NZ','Library cache pin instance (Z=namespace)',
'PF','Password file',
'PI','Parallel operation',
'PR','Process startup',
'PS','Parallel operation',
'QA','Row cache instance (A=cache)',
'QB','Row cache instance (B=cache)',
'QC','Row cache instance (C=cache)',
'QD','Row cache instance (D=cache)',
'QE','Row cache instance (E=cache)',
'QF','Row cache instance (F=cache)',
'QG','Row cache instance (G=cache)',
'QH','Row cache instance (H=cache)',
'QI','Row cache instance (I=cache)',
'QJ','Row cache instance (J=cache)',
'QL','Row cache instance (K=cache)',
'QK','Row cache instance (L=cache)',
'QM','Row cache instance (M=cache)',
'QN','Row cache instance (N=cache)',
'QO','Row cache instance (O=cache)',
'QP','Row cache instance (P=cache)',
'QQ','Row cache instance (Q=cache)',
'QR','Row cache instance (R=cache)',
'QS','Row cache instance (S=cache)',
'QT','Row cache instance (T=cache)',
'QU','Row cache instance (U=cache)',
'QV','Row cache instance (V=cache)',
'QW','Row cache instance (W=cache)',
'QX','Row cache instance (X=cache)',
'QY','Row cache instance (Y=cache)',
'QZ','Row cache instance (Z=cache)',
'RT','Redo thread global enqueue',
'SC','System commit number instance',
'SM','SMON',
'SN','Sequence number instance',
'SQ','Sequence number enqueue',
'SS','Sort segment',
'ST','Space transaction enqueue',
'SV','Sequence number value',
'TA','Generic enqueue',
'TM','DML enqueue',
'TS',decode(l.id2,0,'Temporary segment enqueue',
'New block allocation enqueue lock'),
'TT','Temporary table enqueue',
'TX','Transaction enqueue',
'UL','User supplied',
'UN','User name',
'US','Undo segment DDL',
'WL','Being-written redo log instance', '????') Lockt
from V$LOCK l,
V$SESSION s,
SYS.USER$ u,
SYS.OBJ$ t
where l.sid = s.sid
and t.obj# = decode(l.type,'TM',l.id1,'DL',l.id1,'SQ',l.id1,1)
and u.user# = t.owner#
and s.type != 'BACKGROUND'
order by 1,2,5 ;
select /*+ rule */ s.username, s.sid, s.serial#, l.type "LOCK TYPE", l.id1||'-'||l.id2 id1_id2,
decode(l.lmode,0,'NONE',
1,'NULL',
2,' RS',
3,' RX',
4,' S',
5,' SRX',
6,' X',
' ?') HELD,
decode(l.request,0,' NONE',
1,' NULL',
2,' RS',
3,' RX',
4,' S',
5,' SRX',
6,' X',
' ?') REQUESTED
from v$lock l,v$session s
where l.sid = s.sid
and s.username like upper('%%')
order by id1_id2, s.sid, l.type;
col gtxid form a50
select
s.ksusenum SID
,r.ksqrsidt TYPE
,r.ksqrsid1 ID1
,r.ksqrsid2 ID2
,l.lmode lmode
,l.request request
,l.ctime ctime
,t.ktcxbstm tran_start_time
,g.K2GTIFMT||'-'||g.K2GTITID_EXT||'-'||g.K2GTIBID XID
from
v$_lock l
,x$ksuse s
,x$ksqrs r
,x$k2gte g
,x$ktcxb t
where
l.saddr=s.addr(+)
and l.raddr=r.addr
and r.ksqrsidt ='TX'
and l.laddr = t.ktcxbxba(+)
and l.laddr = g.k2gtdxcb(+)
;
oradebug setmypid;
oradebug ulimite;
oradebug dump hanganalyze 3;
oradebug dump systemstate 10;
REM check lock script
--- begin [lockchk9.sql] ---
define spoolfile = &1
spool &spoolfile
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set timed_statistics = true;
alter session set max_dump_file_size = UNLIMITED;
set feedback on
set term on
set wrap on
set trimspool on
set pagesize 1000
set linesize 2000
set numwidth 10
set echo on
select to_char(sysdate) start_time from dual;
alter session set events 'immediate trace name systemstate level 10';
alter session set events 'immediate trace name hanganalyze level 3';
column host_name format a20 tru
select instance_name, host_name, version, status, startup_time from v$instance;
select * from v$session;
select * from v$process;
select * from v$bgprocess;
select * from v$lock;
select * from v$locked_object;
select * from v$session_wait;
select * from v$latch;
select * from v$latchholder;
select * from v$rowcache;
/* For MTS */
select * from v$dispatcher;
select * from v$shared_server;
select * from v$circuit;
select * from v$queue;
select * from v$dispatcher_rate;
set echo off
Prompt;
Prompt Output file name is:;
define spoolfile
Prompt;
Prompt ALERT.LOG and TRACE FILES are located in:;
column host_name format a12 tru
column name format a20 tru
column value format a60 tru
select distinct i.host_name, p.name, p.value from v$instance i, v$parameter p
where p.name like '%_dump_dest'
and p.name != 'core_dump_dest';
select to_char(sysdate) end_time from dual;
spool off
exit
--- end [lockchk9.sql] ---
lockchk10.sql:
--- begin [lockchk10.sql] ---
define spoolfile = &1
spool &spoolfile
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set timed_statistics = true;
alter session set max_dump_file_size = UNLIMITED;
set feedback on
set term on
set wrap on
set trimspool on
set pagesize 1000
set linesize 2000
set numwidth 10
set echo on
select to_char(sysdate) start_time from dual;
alter session set events 'immediate trace name systemstate level 266';
alter session set events 'immediate trace name hanganalyze level 3';
column host_name format a20 tru
select instance_name, host_name, version, status, startup_time from v$instance;
select * from v$session;
select * from v$process;
select * from v$bgprocess;
select * from v$lock;
select * from v$locked_object;
select * from v$session_wait;
select * from v$latch;
select * from v$latchholder;
select * from v$rowcache;
/* FOR MTS */
select * from v$dispatcher;
select * from v$shared_server;
select * from v$circuit;
select * from v$queue;
select * from v$dispatcher_rate;
set echo off
Prompt;
Prompt Output file name is:;
define spoolfile
Prompt;
Prompt ALERT.LOG and TRACE FILES are located in:;
column host_name format a12 tru
column name format a20 tru
column value format a60 tru
select distinct i.host_name, p.name, p.value from v$instance i, v$parameter p
where p.name like '%_dump_dest'
and p.name != 'core_dump_dest';
select to_char(sysdate) end_time from dual;
spool off
exit
--- end [lockchk10.sql] ---
lockchk10win.sql
--- begin [lockchk10win.sql] ---
define spoolfile = &1
spool &spoolfile
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set timed_statistics = true;
alter session set max_dump_file_size = UNLIMITED;
set feedback on
set term on
set wrap on
set trimspool on
set pagesize 1000
set linesize 2000
set numwidth 10
set echo on
select to_char(sysdate) start_time from dual;
alter session set events 'immediate trace name systemstate level 10';
alter session set events 'immediate trace name hanganalyze level 3';
column host_name format a20 tru
select instance_name, host_name, version, status, startup_time from v$instance;
select * from v$session;
select * from v$process;
select * from v$bgprocess;
select * from v$lock;
select * from v$locked_object;
select * from v$session_wait;
select * from v$latch;
select * from v$latchholder;
select * from v$rowcache;
/* FOR MTS */
select * from v$dispatcher;
select * from v$shared_server;
select * from v$circuit;
select * from v$queue;
select * from v$dispatcher_rate;
set echo off
Prompt;
Prompt Output file name is:;
define spoolfile
Prompt;
Prompt ALERT.LOG and TRACE FILES are located in:;
column host_name format a12 tru
column name format a20 tru
column value format a60 tru
select distinct i.host_name, p.name, p.value from v$instance i, v$parameter p
where p.name like '%_dump_dest'
and p.name != 'core_dump_dest';
select to_char(sysdate) end_time from dual;
spool off
exit
--- end [lockchk10win.sql] ---
以下脚本用于列出用户表空间的使用量和定额:
PROMPT Print the details of the Users Tablespace Quotas
TTITLE left _date center ' Database Users Space Quotas by Tablespace' skip 2 Quotas by Tablespace"
COL un format a25 heading 'User Name'
COL ta format a25 heading 'Tablespace'
COL usd format 9,999,999 heading 'K Used'
COL maxb format 9,999,999 heading 'Max K '
SELECT tablespace_name ta, username un, bytes / 1024 usd,
max_bytes / 1024 maxb
FROM dba_ts_quotas
WHERE MAX_BYTES!=-1
ORDER BY tablespace_name, username;
set linesize 140 pagesize 1400
SELECT
username,
tablespace_name,
privilege
FROM (
SELECT
grantee username, 'Any Tablespace' tablespace_name, privilege
FROM (
-- first get the users with direct grants
SELECT
p1.grantee grantee, privilege
FROM
dba_sys_privs p1
WHERE
p1.privilege='UNLIMITED TABLESPACE'
UNION ALL
-- and then the ones with UNLIMITED TABLESPACE through a role...
SELECT
r3.grantee, granted_role privilege
FROM
dba_role_privs r3
START WITH r3.granted_role IN (
SELECT
DISTINCT p4.grantee
FROM
dba_role_privs r4, dba_sys_privs p4
WHERE
r4.granted_role=p4.grantee
AND p4.privilege = 'UNLIMITED TABLESPACE')
CONNECT BY PRIOR grantee = granted_role)
-- we just whant to see the users not the roles
WHERE grantee IN (SELECT username FROM dba_users) OR grantee = 'PUBLIC'
UNION ALL
-- list the user with unimited quota on a dedicated tablespace
SELECT
username,tablespace_name,'DBA_TS_QUOTA' privilege
FROM
dba_ts_quotas
WHERE
max_bytes = -1 )
WHERE tablespace_name LIKE UPPER('SYSTEM')
OR tablespace_name = 'Any Tablespace';
Copyright © 2024 · Genesis Framework · WordPress · Log in
