Title: Generating CREATE USER DDL Statements
Author:Ted Martin, a database administrator in Ottawa, Ontario, Canada.
These scripts will generate SQL DDL statements related to the creation of user accounts. The types of statements generated
are as follows:
1. CREATE USER and ALTER USER...QUOTA x ON [tabspace] (GENUSER.SQL)
2. CREATE role (GENROLE.SQL)
3. GRANT [role|priv] TO user (GRANTPRIV.SQL)
All three scripts ask for execution parameters. If you leave such a parameter blank, the script will generate for all. The
exception is the prompt for the output filename.
Source/Text/Comments
REM
REM PROGRAM-ID : GENUSER.SQL
REM WRITTEN BY : Ted Martin
REM DATE WRITTEN : 26-AUG-1998
REM
clear screen
PROMPT GENUSER.SQL Generates CREATE USER commands
PROMPT
PROMPT Includes ALTER USER...QUOTA x ON tabspace commands
PROMPT
accept uname prompt 'Enter User Name : '
accept outfile prompt ' Output filename : '
col username noprint
col lne newline
set heading off pagesize 0 verify off feedback off
spool &&outfile..gen
prompt genuser.log
prompt set term on echo off
prompt prompt Creating User Accounts...
prompt set term off echo on
SELECT username, 'CREATE USER '||username||' '||
DECODE(password, 'EXTERNAL', 'IDENTIFIED EXTERNALLY',
'IDENTIFIED BY '''||password||''' ') lne,
'DEFAULT TABLESPACE '||default_tablespace lne,
'TEMPORARY TABLESPACE '||temporary_tablespace||';' lne
FROM DBA_USERS
WHERE USERNAME LIKE UPPER('%&&uname%')
OR UPPER('&&uname') IS NULL
ORDER BY USERNAME;
prompt set term on echo off
prompt prompt Granting Tablespace Quotas...
prompt set term off echo on
SELECT username, 'ALTER USER '||username||' QUOTA '||
DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K')
||' ON TABLESPACE '||tablespace_name||';' lne
FROM DBA_TS_QUOTAS
WHERE USERNAME LIKE UPPER('%&&uname%')
OR UPPER('&&uname') IS NULL
ORDER BY USERNAME;
spool off
PROMPT
PROMPT File &&outfile..GEN generated. Please review before using
PROMPT
EXIT
=============================================================
REM
REM PROGRAM-ID : GENROLE.SQL
REM WRITTEN BY : Ted Martin
REM DATE WRITTEN : 6-APR-1996
REM
set term on echo off linesize 132 pagesize 0 heading off
set verify off
clear screen
prompt GENROLE.SQL V1.0 Generate CREATE ROLE statements
prompt
prompt
accept rname prompt ' Grant Role : '
accept outfile prompt ' Output filename : '
set feedback off pagesize 0 heading off
col lne newline
spool &&outfile..gen
prompt prompt Run Parameters
prompt prompt . . Role = &&rname
prompt spool &&outfile..log
prompt set term on echo off feedback on
select 'CREATE ROLE '||role||';' lne
from dba_roles
where role like UPPER('%&&rname%')
and role not in ('CONNECT', 'RESOURCE', 'DBA',
'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
ORDER BY ROLE;
prompt spool off
prompt exit
spool off
prompt Script &&outfile..gen ready. Review before using it.
exit
=====================================================
REM
REM PROGRAM-ID : GRANTPRIVS.SQL
REM WRITTEN BY : Ted Martin
REM DATE WRITTEN : 26-AUG-1998
REM
clear screen
set term on echo off linesize 132 pagesize 0 heading off
set verify off
prompt GRANTPRIVS.SQL Generate Existing GRANT role/priv statements
prompt
prompt Handles both Roles and System Privs. Excludes SYS and SYSTEM accounts
prompt
accept rname prompt ' Grant Priv : '
accept towner prompt ' To User : '
accept outfile prompt ' Output filename : '
set feedback off verify off
spool &&outfile..gen
prompt prompt Run Parameters
prompt prompt . . Priv = &&rname
prompt prompt . . User = &&towner
prompt spool &&outfile..log
prompt set term on echo on feedback on
col grantee noprint
col granted_priv noprint
select grantee, granted_role granted_priv,
'GRANT '||granted_role||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_role_privs
where (granted_role like upper('%&&rname%') or '&&rname' IS NULL)
or (grantee like upper('%&&towner%') or '&&towner' is null)
and grantee not in ('SYS', 'SYSTEM')
UNION
select grantee, privilege granted_priv,
'GRANT '||privilege||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_sys_privs
where (privilege like upper('%&&rname%') or '&&rname' IS NULL)
or (grantee like upper('%&&towner%') or '&&towner' is null)
and grantee not in ('SYS', 'SYSTEM')
order by 1, 2;
prompt spool off
prompt exit
spool off
prompt Script &&outfile..gen ready. Review before using it.
exit
Script:Generating CREATE USER DDL Statements
2009/07/31 by Leave a Comment
Script:Diagnostic ORA-01000 maximum open cursors exceeded
2009/07/17 by Leave a Comment
以下脚本可以用于诊断ORA-01000打开游标过多错误:
set linesize 140 pagesize 1400
select
to_char(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits,
to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses,
to_char(100 * hard / calls, '999990.00') || '%' hard_parses
from
( select value calls from v$sysstat where name = 'parse count (total)' ),
( select value hard from v$sysstat where name = 'parse count (hard)' ),
( select value sess from v$sysstat where name = 'session cursor cache hits' )
/
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%cursor ca%'
/
select sum(a.value), b.name,a.sid
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
group by rollup (b.name,a.sid)
order by 1
/
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'session cursor cache count'
order by 1
/
select sid, count(*) from v$open_cursor group by sid
order by 2
/
Exec DBMS_WORKLOAD_REPOSITORY.create_snapshot();
exec dbms_lock.sleep(300);
Exec DBMS_WORKLOAD_REPOSITORY.create_snapshot();
@?/rdbms/admin/awrrpt
upload the awr report
or
select dbms_workload_repository.awr_report_text(l_dbid => dbid,
l_inst_num => instance_number,
l_bid => mid - 1,
l_eid => mid)
from (select vd.dbid, vi.instance_number, mid
from v$database vd,
v$instance vi,
(select max(snap_id) mid from dba_hist_snapshot dhs))
/
ORA-4030 PGA Usage Diagnostic Script
2009/07/07 by 2 Comments
REM Locate the top PGA user
set lines 75
set pages 999
set serveroutput on
spool topuser.out
select * from gv$version;
declare a1 number;
a2 number;
a3 varchar2(30);
a4 varchar2(30);
a5 number;
a6 number;
a7 number;
a8 number;
blankline varchar2(70);
cursor code is select pid, spid, substr(username,1,20) "USER" , substr(program,1,30) "Program",
PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM
from v$process where pga_alloc_mem=
(select max(pga_alloc_mem) from v$process
where program not like '%LGWR%');
begin
blankline:=chr(13);
open code;
fetch code into a1, a2, a3, a4, a5, a6, a7, a8;
dbms_output.put_line(blankline);
dbms_output.put_line(' Top PGA User');
dbms_output.put_line(blankline);
dbms_output.put_line('PID: '||a1||' '||'SPID: '||a2);
dbms_output.put_line('User Info: '||a3);
dbms_output.put_line('Program: '||a4);
dbms_output.put_line('PGA Used: '||a5);
dbms_output.put_line('PGA Allocated: '||a6);
dbms_output.put_line('PGA Freeable: '||a7);
dbms_output.put_line('Maximum PGA: '||a8);
end;
/
set lines 132
col value format 999,999,999,999,999
select * from v$pgastat;
spool off
REM
REM Investigate memory from the database side
REM
col TTL format 999,999,999,999 heading "Total Memory"
break on report
compute sum on report of TTL
select bytes TTL from v$sgainfo where name='Maximum SGA Size'
union
select value from v$pgastat where name='total PGA allocated'
/
set lines 132
set pages 999
spool workareaoverview.out
REM overview of PGA usage
col name format a40 head "Name"
col value format 999,999,999 head "Total"
col unit format a10 head "Units"
col pga_size format a25 head "PGA Size"
col optimal_executions format 999,999,999,999 head "Optimal"
col onepass_executions format 999,999,999,999 head "One-Pass"
col multipasses_executions format 999,999,999,999 head "Multi-Pass"
col optimal_count format 999,999,999,999 head "Optimal Count"
col optimal_perc format 999 head "Optimal|PCT"
col onepass_count format 999,999,999,999 head "One-Pass Count"
col onepass_perc format 999 head "One|PCT"
col multipass_count format 999,999,999,999 head "Multi-Pass Count"
col multipass_perc format 999 head "Multi|PCT"
col sid format 999,999 Head "SID"
col operation format a30 head "Operation"
col esize format 999,999,999 head "Expected Size"
col mem format 999,999,999 head "Actual Mem"
col "MAX MEM" format 999,999,999 head "Maximum Mem"
col pass format 999,999 head "Passes"
col tsize format 999,999,999,999,999 head "Temporary|Segment Size"
spool workareaoverview.out
SELECT name, decode(unit, 'bytes', trunc(value/1024/1024), value) value ,
decode(unit, 'bytes', 'MBytes', unit) unit FROM V$PGASTAT
/
REM Review workarea buckets to see how efficient memory is utilized
REM Ideal to see OPTIMAL EXECUTIONS vs. ONE-PASS and Multi-PASS
select case when low_optimal_size < 1024*1024
then to_char(low_optimal_size/1024,'999999') || 'kb 0
order by low_optimal_size
/
REM Review workarea buckets as percentages overall
REM this script assuming 64K optimal size
SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,
onepass_count, round(onepass_count*100/total, 2) onepass_perc,
multipass_count, round(multipass_count*100/total, 2) multipass_perc
FROM
(SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
sum(OPTIMAL_EXECUTIONS) optimal_count,
sum(ONEPASS_EXECUTIONS) onepass_count,
sum(MULTIPASSES_EXECUTIONS) multipass_count
FROM v$sql_workarea_histogram
WHERE low_optimal_size > 64*1024)
/
REM Review current activity in Work Areas
SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2
/
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;
select * from v$pgastat;
col time form a30
col name form a30
select a.BEGIN_INTERVAL_TIME time, b.*
from DBA_HIST_SNAPSHOT a, DBA_HIST_PGASTAT b
where a.SNAP_ID=b.SNAP_ID
and b.name='total PGA allocated'
order by a.BEGIN_INTERVAL_TIME desc
/
show parameter parallel_execution_message_size
show parameter memory
show parameter pga
spool off
clear col
#collect AIX info
ls -al $ORACLE_HOME/bin/oracle >> /tmp/support.txt
oslevel -s
whoami >> /tmp/support.txt
ulimit -a >> /tmp/support.txt
svmon -O unit=MB >> /tmp/support.txt
/usr/sbin/lsps -a >> /tmp/support.txt
/usr/sbin/lsattr -HE -l sys0 -a realmem >> /tmp/support.txt
ipcs -m >> /tmp/support.txt
opatch lsinventory -detail
#collect Linux info
arch
cat /etc/issue
whoami
ulimit -a
df -h /dev/shm
ipcs -ma
cat /etc/sysctl.conf
cat /proc/meminfo
cat /proc/swaps
cat /proc/vmstat
opatch lsinventory -detail
Tune Very Large Hash Join
2009/07/02 by Leave a Comment
set timing on; alter session set workarea_size_policy=MANUAL; alter session set workarea_size_policy=MANUAL; alter session set db_file_multiblock_read_count=512; alter session set db_file_multiblock_read_count=512; alter session set events '10351 trace name context forever, level 128'; alter session set hash_area_size=524288000; alter session set hash_area_size=524288000; alter session set "_hash_multiblock_io_count"=128; alter session set "_hash_multiblock_io_count"=128; alter session enable parallel query; select /*+ pq_distribute(a hash,hash) parallel(a) parallel(b) */ column1,column2.... from source_tab a, driving_tab b where condition ; --PQ_DISTRIBUTE(tab,out,in) How to distribute rows from tab in a PQ --(out/in may be HASH/NONE/BROADCAST/PARTITION)
Script to Collect Log File Sync Diagnostic Information (lfsdiag.sql)
2009/06/17 by 3 Comments
Script
set echo off
set feedback off
column timecol new_value timestamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hh24mi') timecol,
'.out' spool_extension from sys.dual;
column output new_value dbname
select value || '_' output
from v$parameter where name = 'db_name';
spool lfsdiag_&&dbname&×tamp&&suffix
set trim on
set trims on
set lines 130
set pages 100
set verify off
alter session set optimizer_features_enable = '10.2.0.4';
PROMPT LFSDIAG DATA FOR &&dbname&×tamp
PROMPT Note: All timings are in milliseconds (1000 milliseconds = 1 second)
PROMPT
PROMPT IMPORTANT PARAMETERS RELATING TO LOG FILE SYNC WAITS:
column name format a40 wra
column value format a40 wra
select inst_id, name, value from gv$parameter
where ((value is not null and name like '%log_archive%') or
name like '%commit%' or name like '%event=%' or name like '%lgwr%')
and name not in (select name from gv$parameter where (name like '%log_archive_dest_state%'
and value = 'enable') or name = 'log_archive_format')
order by 1,2,3;
PROMPT
PROMPT HISTOGRAM DATA FOR LFS AND OTHER RELATED WAITS:
PROMPT
PROMPT APPROACH: Look at the wait distribution for log file sync waits
PROMPT by looking at "wait_time_milli". Look at the high wait times then
PROMPT see if you can correlate those with other related wait events.
column event format a40 wra
select inst_id, event, wait_time_milli, wait_count
from gv$event_histogram
where event in ('log file sync','gcs log flush sync',
'log file parallel write','wait for scn ack',
'log file switch completion','gc cr grant 2-way',
'gc buffer busy','gc current block 2-way') or
event like '%LGWR%' or event like '%LNS%'
order by 2 desc,1,3;
PROMPT
PROMPT ORDERED BY WAIT_TIME_MILLI
select inst_id, event, wait_time_milli, wait_count
from gv$event_histogram
where event in ('log file sync','gcs log flush sync',
'log file parallel write','wait for scn ack',
'log file switch completion','gc cr grant 2-way',
'gc buffer busy','gc current block 2-way')
or event like '%LGWR%' or event like '%LNS%'
order by 3,1,2 desc;
PROMPT
PROMPT REDO WRITE STATS
PROMPT
PROMPT "redo write time" in centiseconds (100 per second)
PROMPT 11.1: "redo write broadcast ack time" in centiseconds (100 per second)
PROMPT 11.2: "redo write broadcast ack time" in microseconds (1000 per millisecond)
column value format 99999999999999999999
column milliseconds format 99999999999999.999
select v.version, ss.inst_id, ss.name, ss.value,
decode(substr(version,1,4),
'11.1',decode (name,'redo write time',value*10,
'redo write broadcast ack time',value*10),
'11.2',decode (name,'redo write time',value*10,
'redo write broadcast ack time',value/1000),
decode (name,'redo write time',value*10)) milliseconds
from gv$sysstat ss, v$instance v
where name like 'redo write%' and value > 0
order by 1,2,3;
PROMPT
PROMPT ASH THRESHOLD...
PROMPT
PROMPT This will be the threshold in milliseconds for average log file sync
PROMPT times. This will be used for the next queries to look for the worst
PROMPT 'log file sync' minutes. Any minutes that have an average log file
PROMPT sync time greater than the threshold will be analyzed further.
column threshold_in_ms new_value threshold format 999999999.999
select min(threshold_in_ms) threshold_in_ms
from (select inst_id, to_char(sample_time,'Mondd_hh24mi') minute,
avg(time_waited)/1000 threshold_in_ms
from gv$active_session_history
where event = 'log file sync'
group by inst_id,to_char(sample_time,'Mondd_hh24mi')
order by 3 desc)
where rownum <= 5;
PROMPT
PROMPT ASH WORST MINUTES FOR LOG FILE SYNC WAITS:
PROMPT
PROMPT APPROACH: These are the minutes where the avg log file sync time
PROMPT was the highest (in milliseconds).
column event format a30 tru
column program format a35 tru
column total_wait_time format 999999999999.999
column avg_time_waited format 999999999999.999
select to_char(sample_time,'Mondd_hh24mi') minute, inst_id, event,
sum(time_waited)/1000 TOTAL_WAIT_TIME , count(*) WAITS,
avg(time_waited)/1000 AVG_TIME_WAITED
from gv$active_session_history
where event = 'log file sync'
group by to_char(sample_time,'Mondd_hh24mi'), inst_id, event
having avg(time_waited)/1000 > &&threshold
order by 1,2;
PROMPT
PROMPT ASH LFS BACKGROUND PROCESS WAITS DURING WORST MINUTES:
PROMPT
PROMPT APPROACH: What is LGWR doing when 'log file sync' waits
PROMPT are happening? LMS info may be relevent for broadcast
PROMPT on commit and LNS data may be relevant for dataguard.
PROMPT If more details are needed see the ASH DETAILS FOR WORST
PROMPT MINUTES section at the bottom of the report.
column inst format 999
column event format a30 tru
column program format a35 wra
select to_char(sample_time,'Mondd_hh24mi') minute, inst_id inst, program, event,
sum(time_waited)/1000 TOTAL_WAIT_TIME , count(*) WAITS,
avg(time_waited)/1000 AVG_TIME_WAITED
from gv$active_session_history
where to_char(sample_time,'Mondd_hh24mi') in (select to_char(sample_time,'Mondd_hh24mi')
from gv$active_session_history
where event = 'log file sync'
group by to_char(sample_time,'Mondd_hh24mi'), inst_id
having avg(time_waited)/1000 > &&threshold)
and (program like '%LGWR%' or program like '%LMS%' or
program like '%LNS%' or event = 'log file sync')
group by to_char(sample_time,'Mondd_hh24mi'), inst_id, program, event
order by 1,2,3,5 desc, 4;
PROMPT
PROMPT AWR WORST AVG LOG FILE SYNC SNAPS:
PROMPT
PROMPT APPROACH: These are the AWR snaps where the average 'log file sync'
PROMPT times were the highest.
column begin format a12 tru
column end format a12 tru
column name format a13 tru
select dhs.snap_id, dhs.instance_number inst, to_char(dhs.begin_interval_time,'Mondd_hh24mi') BEGIN,
to_char(dhs.end_interval_time,'Mondd_hh24mi') END,
en.name, se.time_waited_micro/1000 total_wait_time, se.total_waits,
se.time_waited_micro/1000 / se.total_waits avg_time_waited
from dba_hist_snapshot dhs, wrh$_system_event se, v$event_name en
where (dhs.snap_id = se.snap_id and dhs.instance_number = se.instance_number)
and se.event_id = en.event_id and en.name = 'log file sync' and
dhs.snap_id in (select snap_id from (
select se.snap_id, se.time_waited_micro/1000 / se.total_waits avg_time_waited
from wrh$_system_event se, v$event_name en
where se.event_id = en.event_id and en.name = 'log file sync'
order by avg_time_waited desc)
where rownum < 4)
order by 1,2;
PROMPT
PROMPT AWR REDO WRITE STATS
PROMPT
PROMPT "redo write time" in centiseconds (100 per second)
PROMPT 11.1: "redo write broadcast ack time" in centiseconds (100 per second)
PROMPT 11.2: "redo write broadcast ack time" in microseconds (1000 per millisecond)
column stat_name format a30 tru
select v.version, ss.snap_id, ss.instance_number inst, sn.stat_name, ss.value,
decode(substr(version,1,4),
'11.1',decode (stat_name,'redo write time',value*10,
'redo write broadcast ack time',value*10),
'11.2',decode (stat_name,'redo write time',value*10,
'redo write broadcast ack time',value/1000),
decode (stat_name,'redo write time',value*10)) milliseconds
from wrh$_sysstat ss, wrh$_stat_name sn, v$instance v
where ss.stat_id = sn.stat_id
and sn.stat_name like 'redo write%' and ss.value > 0
and ss.snap_id in (select snap_id from (
select se.snap_id, se.time_waited_micro/1000 / se.total_waits avg_time_waited
from wrh$_system_event se, v$event_name en
where se.event_id = en.event_id and en.name = 'log file sync'
order by avg_time_waited desc)
where rownum < 4)
order by 1,2,3;
PROMPT
PROMPT AWR LFS AND OTHER RELATED WAITS FOR WORST LFS AWRs:
PROMPT
PROMPT APPROACH: These are the AWR snaps where the average 'log file sync'
PROMPT times were the highest. Look at related waits at those times.
column name format a40 tru
select se.snap_id, se.instance_number inst, en.name,
se.total_waits, se.time_waited_micro/1000 total_wait_time,
se.time_waited_micro/1000 / se.total_waits avg_time_waited
from wrh$_system_event se, v$event_name en
where se.event_id = en.event_id and (en.name in
('log file sync','gcs log flush sync',
'log file parallel write','wait for scn ack',
'log file switch completion','gc cr grant 2-way',
'gc buffer busy','gc current block 2-way')
or en.name like '%LGWR%' or en.name like '%LNS%')
and se.snap_id in (select snap_id from (
select se.snap_id, se.time_waited_micro/1000 / se.total_waits avg_time_waited
from wrh$_system_event se, v$event_name en
where se.event_id = en.event_id and en.name = 'log file sync'
order by avg_time_waited desc)
where rownum < 4)
order by 1, 6 desc;
PROMPT
PROMPT AWR HISTOGRAM DATA FOR LFS AND OTHER RELATED WAITS FOR WORST LFS AWRs:
PROMPT Note: This query won't work on 10.2 - ORA-942
PROMPT
PROMPT APPROACH: Look at the wait distribution for log file sync waits
PROMPT by looking at "wait_time_milli". Look at the high wait times then
PROMPT see if you can correlate those with other related wait events.
select eh.snap_id, eh.instance_number inst, en.name, eh.wait_time_milli, eh.wait_count
from wrh$_event_histogram eh, v$event_name en
where eh.event_id = en.event_id and
(en.name in ('log file sync','gcs log flush sync',
'log file parallel write','wait for scn ack',
'log file switch completion','gc cr grant 2-way',
'gc buffer busy','gc current block 2-way')
or en.name like '%LGWR%' or en.name like '%LNS%')
and snap_id in (select snap_id from (
select se.snap_id, se.time_waited_micro/1000 / se.total_waits avg_time_waited
from wrh$_system_event se, v$event_name en
where se.event_id = en.event_id and en.name = 'log file sync'
order by avg_time_waited desc)
where rownum < 4)
order by 1,3 desc,2,4;
PROMPT
PROMPT ORDERED BY WAIT_TIME_MILLI
PROMPT Note: This query won't work on 10.2 - ORA-942
select eh.snap_id, eh.instance_number inst, en.name, eh.wait_time_milli, eh.wait_count
from wrh$_event_histogram eh, v$event_name en
where eh.event_id = en.event_id and
(en.name in ('log file sync','gcs log flush sync',
'log file parallel write','wait for scn ack',
'log file switch completion','gc cr grant 2-way',
'gc buffer busy','gc current block 2-way')
or en.name like '%LGWR%' or en.name like '%LNS%')
and snap_id in (select snap_id from (
select se.snap_id, se.time_waited_micro/1000 / se.total_waits avg_time_waited
from wrh$_system_event se, v$event_name en
where se.event_id = en.event_id and en.name = 'log file sync'
order by avg_time_waited desc)
where rownum < 4)
order by 1,4,2,3 desc;
PROMPT
PROMPT ASH DETAILS FOR WORST MINUTES:
PROMPT
PROMPT APPROACH: If you cannot determine the problem from the data
PROMPT above, you may need to look at the details of what each session
PROMPT is doing during each 'bad' snap. Most likely you will want to
PROMPT note the times of the high log file sync waits, look at what
PROMPT LGWR is doing at those times, and go from there...
column program format a45 wra
column sample_time format a25 tru
column event format a30 tru
column time_waited format 999999.999
column p1 format a40 tru
column p2 format a40 tru
column p3 format a40 tru
select sample_time, inst_id inst, session_id, program, event, time_waited/1000 TIME_WAITED,
p1text||': '||p1 p1,p2text||': '||p2 p2,p3text||': '||p3 p3
from gv$active_session_history
where to_char(sample_time,'Mondd_hh24mi') in (select
to_char(sample_time,'Mondd_hh24mi')
from gv$active_session_history
where event = 'log file sync'
group by to_char(sample_time,'Mondd_hh24mi'), inst_id
having avg(time_waited)/1000 > &&threshold)
order by 1,2,3,4,5;
select to_char(sysdate,'Mondd hh24:mi:ss') TIME from dual;
spool off
PROMPT
PROMPT OUTPUT FILE IS: lfsdiag_&&dbname&×tamp&&suffix
PROMPT
Script:List Buffer Cache Details
2009/06/17 by 2 Comments
以下脚本用于列出Oracle Buffer Cache的详细信息:
REM List Buffer Cache Details
SET LINESIZE 200 PAGESIZE 1400
SELECT /*+ ORDERED USE_HASH(o u) MERGE */
DECODE(obj#,
NULL,
to_char(bh.obj),
u.name || '.' || o.name) name,
COUNT(*) total,
SUM(DECODE((DECODE(lru_flag, 8, 1, 0) + DECODE(SIGN(tch - 2), 1, 1, 0)),
2,
1,
1,
1,
0)) hot,
SUM(DECODE(DECODE(SIGN(lru_flag - 8), 1, 0, 0, 0, 1) +
DECODE(tch, 2, 1, 1, 1, 0, 1, 0),
2,
1,
1,
0,
0)) cold,
SUM(DECODE(BITAND(flag, POWER(2, 19)), 0, 0, 1)) fts,
SUM(tch) total_tch,
ROUND(AVG(tch), 2) avg_tch,
MAX(tch) max_tch,
MIN(tch) min_tch
FROM x$bh bh, sys.obj$ o, sys.user$ u
WHERE
bh.obj <> 4294967295
AND bh.state in (1, 2, 3)
AND bh.obj = o.dataobj#(+)
AND bh.inst_id = USERENV('INSTANCE')
AND o.owner# = u.user#(+)
-- AND o.owner# > 5
AND u.name NOT like 'AURORA$%'
GROUP BY DECODE(obj#,
NULL,
to_char(bh.obj),
u.name || '.' || o.name)
ORDER BY total desc
/
COLUMN object_name FORMAT A30
SELECT t.name AS tablespace_name,
o.object_name,
SUM(DECODE(bh.status, 'free', 1, 0)) AS free,
SUM(DECODE(bh.status, 'xcur', 1, 0)) AS xcur,
SUM(DECODE(bh.status, 'scur', 1, 0)) AS scur,
SUM(DECODE(bh.status, 'cr', 1, 0)) AS cr,
SUM(DECODE(bh.status, 'read', 1, 0)) AS read,
SUM(DECODE(bh.status, 'mrec', 1, 0)) AS mrec,
SUM(DECODE(bh.status, 'irec', 1, 0)) AS irec
FROM v$bh bh
JOIN dba_objects o ON o.data_object_id = bh.objd
JOIN v$tablespace t ON t.ts# = bh.ts#
GROUP BY t.name, o.object_name
order by xcur desc
/
set pages 999
set lines 92
ttitle 'Contents of Data Buffers'
drop view buffer_map;
create view buffer_map as
select
o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,
count(distinct file# || block#) num_blocks
from
dba_objects o,
v$bh bh
where
o.data_object_id = bh.objd
-- and o.owner not in ('SYS','SYSTEM')
and
bh.status != 'free'
group by
o.owner,
o.object_name,
o.subobject_name,
o.object_type
order by
count(distinct file# || block#) desc
;
column c0 heading "Owner" format a12
column c1 heading "Object|Name" format a30
column c2 heading "Object|Type" format a8
column c3 heading "Number of|Blocks in|Buffer|Cache" format 99,999,999
column c4 heading "Percentage|of object|blocks in|Buffer" format 999
column c5 heading "Buffer|Pool" format a7
column c6 heading "Block|Size" format 99,999
select
buffer_map.owner c0,
object_name c1,
case when object_type = 'TABLE PARTITION' then 'TAB PART'
when object_type = 'INDEX PARTITION' then 'IDX PART'
else object_type end c2,
sum(num_blocks) c3,
(sum(num_blocks)/greatest(sum(blocks), .001))*100 c4,
buffer_pool c5,
sum(bytes)/sum(blocks) c6
from
buffer_map,
dba_segments s
where
s.segment_name = buffer_map.object_name
and
s.owner = buffer_map.owner
and
s.segment_type = buffer_map.object_type
and
nvl(s.partition_name,'-') = nvl(buffer_map.subobject_name,'-')
group by
buffer_map.owner,
object_name,
object_type,
buffer_pool
having
sum(num_blocks) > 10
order by
sum(num_blocks) desc
;
REM dbbuffer
select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',
4,'2K SUBCACHE',5,'4K SUBCACHE',6,'8K SUBCACHE',
7,'16K SUBCACHE',8,'32KSUBCACHE','UNKNOWN') subcache,
bh.object_name,bh.blocks
from x$kcbwds ds,x$kcbwbpd pd,(select /*+ use_hash(x) */ set_ds,
o.name object_name,count(*) BLOCKS
from obj$ o, x$bh x where o.dataobj# = x.obj
and x.state !=0 and o.owner# !=0
group by set_ds,o.name) bh
where ds.set_id >= pd.bp_lo_sid
and ds.set_id <= pd.bp_hi_sid
and pd.bp_size != 0
and ds.addr=bh.set_ds;
column segment_name format a35
select /*+ RULE */
e.owner ||'.'|| e.segment_name segment_name,
e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,
x.tch,
l.child#
from
sys.v$latch_children l,
sys.x$bh x,
sys.dba_extents e
where
x.hladdr = '&ADDR' and
e.file_id = x.file# and
x.hladdr = l.addr and
x.dbablk between e.block_id and e.block_id + e.blocks -1
order by x.tch desc
/
with bh_lc as
(select /*+ ORDERED */
lc.addr, lc.child#, lc.gets, lc.misses, lc.immediate_gets,
lc.immediate_misses, lc.spin_gets, lc.sleeps,
bh.hladdr, bh.tch tch, bh.file#, bh.dbablk, bh.class,
bh.state, bh.obj
from
x$kslld ld,
v$session_wait sw,
v$latch_children lc,
x$bh bh
where lc.addr =sw.p1raw
and sw.p2= ld.indx
and ld.kslldnam='cache buffers chains'
and lower(sw.event) like '%latch%'
and sw.state='WAITING'
and bh.hladdr=lc.addr
)
select bh_lc.hladdr, bh_lc.tch, o.owner, o.object_name, o.object_type,
bh_lc.child#, bh_lc.gets,
bh_lc.misses, bh_lc.immediate_gets,
bh_lc.immediate_misses, spin_gets, sleeps
from
bh_lc,
dba_objects o
where bh_lc.obj = o.object_id(+)
union
select bh_lc.hladdr, bh_lc.tch, o.owner, o.object_name, o.object_type,
bh_lc.child#, bh_lc.gets, bh_lc.misses, bh_lc.immediate_gets,
bh_lc.immediate_misses, spin_gets, sleeps
from
bh_lc,
dba_objects o
where bh_lc.obj = o.data_object_id(+)
order by 1,2 desc
/
col class form A10
select decode(greatest(class,10),10,decode(class,1,'Data',2
,'Sort',4,'Header',to_char(class)),'Rollback') "Class",
sum(decode(bitand(flag,1),1,0,1)) "Not Dirty",
sum(decode(bitand(flag,1),1,1,0)) "Dirty",
sum(dirty_queue) "On Dirty",count(*) "Total"
from x$bh
group by decode(greatest(class,10),10,decode(class,1,'Data',2
,'Sort',4,'Header',to_char(class)),'Rollback')
/
Script:List NLS Parameters and Timezone
2009/06/17 by Leave a Comment
以下脚本用以列出Database-instance-session的NLS参数和所在时区:
REM List NLS Parameters set linesize 90 pagesize 1400 col Parameter for a40 col Value for a40 SELECT Parameter, Value FROM NLS_DATABASE_PARAMETERS / SELECT Parameter, Value FROM NLS_INSTANCE_PARAMETERS / SELECT Parameter, Value FROM NLS_SESSION_PARAMETERS ORDER BY 1 / select dbtimezone from dual / select sessiontimezone from dual /
Script:List SORT ACTIVITY监控临时空间的使用
2009/06/17 by Leave a Comment
以下脚本可以用于列出数据库内的排序活跃性能信息并监控临时空间的使用:
REM SORT ACTIVITY
set linesize 150 pagesize 1400;
SELECT d.tablespace_name "Name",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999') "HWM (M)",
TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,
TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY'
/
alter session set nls_date_format='dd-mon-yy';
set lines 160 pages 1000 echo off feedback off
col stat_name for a25
col date_time for a40
col BEGIN_INTERVAL_TIME for a20
col END_INTERVAL_TIME for a20
prompt "Enter the date in DD-Mon-YY Format and Stats you want to trend like 'redo size','physical reads','physical writes','session logical reads' etc."
WITH sysstat AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.value e_value,
lag(ss.value, 1) over(order by ss.snap_id) b_value
from dba_hist_sysstat ss, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) >= sysdate-7
and ss.snap_id = sn.snap_id
and ss.dbid = sn.dbid
and ss.instance_number = sn.instance_number
and ss.dbid = (select dbid from v$database)
and ss.instance_number = (select instance_number from v$instance)
and ss.stat_name = 'sorts (disk)')
select to_char(BEGIN_INTERVAL_TIME, 'mm/dd/yy_hh24_mi') || to_char(END_INTERVAL_TIME, '_hh24_mi') date_time,
stat_name,
round((e_value - nvl(b_value,0)) / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60
+ extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60
+ extract(minute from(end_interval_time - begin_interval_time)) * 60 + extract(second from(end_interval_time - begin_interval_time))),0) per_sec
from sysstat where(e_value - nvl(b_value,0)) > 0 and nvl(b_value,0) > 0
/
select temp_space/1024/1024,SQL_ID from DBA_HIST_SQL_PLAN where temp_space>0 order by 1 asc;
--For 8.1.7 to 9.2:
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$sort_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;
--For 10.1 and above:
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;
SELECT *
FROM (SELECT matching_criteria,
TO_CHAR(force_matching_signature) force_matching_signature,
sql_id,
child_number,
sql_text,
executions,
elapsed_time / 1000,
operation_type,
policy,
estimated_optimal_size,
last_memory_used,
last_execution,
active_time / 1000,
num_sort_operations,
tot_active_time / 1000,
tot_optimal_executions,
tot_onepass_executions,
tot_multipasses_executions,
all_tot_active_time / 1000,
max_tempseg_size,
parsing_schema_name
FROM (SELECT force_matching_signature,
sql_id,
child_number,
sql_text,
matching_criteria,
SUM(executions) OVER(PARTITION BY matching_criteria) executions,
SUM(elapsed_time) OVER(PARTITION BY matching_criteria) elapsed_time,
operation_type,
policy,
estimated_optimal_size,
last_memory_used,
last_execution,
active_time,
num_sort_operations,
SUM(tot_active_time) OVER(PARTITION BY matching_criteria) tot_active_time,
SUM(tot_optimal_executions) OVER(PARTITION BY matching_criteria) tot_optimal_executions,
SUM(tot_onepass_executions) OVER(PARTITION BY matching_criteria) tot_onepass_executions,
SUM(tot_multipasses_executions) OVER(PARTITION BY matching_criteria) tot_multipasses_executions,
MAX(max_tempseg_size) OVER(PARTITION BY matching_criteria) max_tempseg_size,
SUM(tot_active_time) OVER() all_tot_active_time,
ROW_NUMBER() OVER(PARTITION BY matching_criteria ORDER BY tot_multipasses_executions DESC, tot_onepass_executions DESC, last_memory_used DESC) rnum,
parsing_schema_name
FROM (SELECT s.sql_id,
s.child_number,
s.sql_text,
s.executions,
s.elapsed_time,
s.force_matching_signature,
CASE
WHEN s.force_matching_signature > 0 THEN
TO_CHAR(s.force_matching_signature)
ELSE
s.sql_id
END matching_criteria,
ROW_NUMBER() OVER(PARTITION BY s.sql_id, s.child_number ORDER BY sw.multipasses_executions DESC, sw.onepass_executions DESC, sw.last_memory_used DESC) rnum,
sw.operation_type,
sw.policy,
sw.estimated_optimal_size,
sw.last_memory_used,
sw.last_execution,
MAX(sw.max_tempseg_size) OVER(PARTITION BY s.sql_id, s.child_number) max_tempseg_size,
sw.active_time * sw.total_executions active_time,
SUM(sw.active_time * sw.total_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_active_time,
COUNT(*) OVER(PARTITION BY s.sql_id, s.child_number) num_sort_operations,
SUM(sw.optimal_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_optimal_executions,
SUM(sw.onepass_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_onepass_executions,
SUM(sw.multipasses_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_multipasses_executions,
NVL(u.username, s.parsing_schema_name) parsing_schema_name
FROM v$sql s, v$sql_workarea sw, all_users u
WHERE sw.sql_id = s.sql_id
AND sw.child_number = s.child_number
AND u.user_id(+) = s.parsing_user_id)
WHERE rnum = 1)
WHERE rnum = 1
ORDER BY tot_multipasses_executions DESC,
tot_onepass_executions DESC,
last_memory_used DESC)
WHERE ROWNUM <= 200
/
SELECT *
FROM (SELECT s.sid,
s.machine,
s.program,
s.module,
s.osuser,
NVL(DECODE(TYPE,
'BACKGROUND',
'SYS (' || b.ksbdpnam || ')',
s.username),
SUBSTR(p.program, INSTR(p.program, '('))) username,
NVL(SUM(CASE
WHEN sn.name = 'sorts (memory)' THEN
ss.VALUE
ELSE
0
END),
0) sorts_memory,
NVL(SUM(CASE
WHEN sn.name = 'sorts (disk)' THEN
ss.VALUE
ELSE
0
END),
0) sorts_disk,
NVL(SUM(CASE
WHEN sn.name = 'sorts (rows)' THEN
ss.VALUE
ELSE
0
END),
0) sorts_rows,
NVL(SUM(CASE
WHEN sn.name = 'physical reads direct temporary tablespace' THEN
ss.VALUE
ELSE
0
END),
0) reads_direct_temp,
NVL(SUM(CASE
WHEN sn.name = 'physical writes direct temporary tablespace' THEN
ss.VALUE
ELSE
0
END),
0) writes_direct_temp,
NVL(SUM(CASE
WHEN sn.name = 'workarea executions - optimal' THEN
ss.VALUE
ELSE
0
END),
0) workarea_exec_optimal,
NVL(SUM(CASE
WHEN sn.name = 'workarea executions - onepass' THEN
ss.VALUE
ELSE
0
END),
0) workarea_exec_onepass,
NVL(SUM(CASE
WHEN sn.name = 'workarea executions - multipass' THEN
ss.VALUE
ELSE
0
END),
0) workarea_exec_multipass
FROM v$session s,
v$sesstat ss,
v$statname sn,
v$process p,
x$ksbdp b
WHERE s.paddr = p.addr
AND b.inst_id(+) = USERENV('INSTANCE')
AND p.addr = b.ksbdppro(+)
AND s.TYPE = 'USER'
AND s.sid = ss.sid
AND ss.statistic# = sn.statistic#
AND sn.name IN ('sorts (memory)',
'sorts (disk)',
'sorts (rows)',
'physical reads direct temporary tablespace',
'physical writes direct temporary tablespace',
'workarea executions - optimal',
'workarea executions - onepass',
'workarea executions - multipass')
GROUP BY s.sid,
s.machine,
s.program,
s.module,
s.osuser,
NVL(DECODE(TYPE,
'BACKGROUND',
'SYS (' || b.ksbdpnam || ')',
s.username),
SUBSTR(p.program, INSTR(p.program, '(')))
ORDER BY workarea_exec_multipass DESC,
workarea_exec_onepass DESC,
reads_direct_temp + writes_direct_temp DESC,
sorts_rows DESC)
WHERE ROWNUM <= 200
/
SELECT rawtohex(workarea_address) workarea_address,
sql_id,
sql_text,
operation_type,
policy,
sid,
active_time,
work_area_size,
expected_size,
actual_mem_used,
max_mem_used,
number_passes,
tempseg_size,
tablespace,
complete_ratio,
elapsed,
time_remaining,
opname,
machine,
program,
module,
osuser,
username
FROM (SELECT swa.workarea_address,
swa.sql_id,
sa.sql_text,
swa.operation_type,
swa.policy,
swa.sid,
swa.active_time / 1000 active_time,
swa.work_area_size,
swa.expected_size,
swa.actual_mem_used,
swa.max_mem_used,
swa.number_passes,
swa.tempseg_size,
swa.tablespace,
(CASE
WHEN sl.totalwork <> 0 THEN
sl.sofar / sl.totalwork
ELSE
NULL
END) complete_ratio,
sl.elapsed_seconds * 1000 elapsed,
sl.time_remaining * 1000 time_remaining,
sl.opname,
s.machine,
s.program,
s.module,
s.osuser,
NVL(DECODE(TYPE,
'BACKGROUND',
'SYS (' || b.ksbdpnam || ')',
s.username),
SUBSTR(p.program, INSTR(p.program, '('))) username,
ROW_NUMBER() OVER(PARTITION BY swa.sql_id ORDER BY sl.last_update_time DESC) rnum
FROM v$sql_workarea_active swa,
v$sqlarea sa,
(SELECT * FROM v$session_longops WHERE sofar <> totalwork) sl,
v$session s,
v$process p,
x$ksbdp b
WHERE sl.sid(+) = swa.sid
AND sl.sql_id(+) = swa.sql_id
AND swa.sid <> USERENV('sid')
AND sa.sql_id = swa.sql_id
AND s.sid = swa.sid
AND s.paddr = p.addr
AND b.inst_id(+) = USERENV('INSTANCE')
AND p.addr = b.ksbdppro(+)
ORDER BY swa.number_passes DESC, swa.work_area_size DESC)
WHERE rnum = 1
/
Script:List OBJECT DEPENDENT
2009/06/17 by Leave a Comment
以下脚本用以列出数据库中对象的依赖性:
REM OBJECT DEPENDENT
select D_OBJ#,
do.object_name,
do.object_type dtyp,
do.status dsta,
D_TIMESTAMP,
ORDER#,
P_OBJ#,
po.object_name,
po.object_type ptyp,
po.status psta,
P_TIMESTAMP
from dependency$ d, DBA_OBJECTS do, DBA_OBJECTS po
where D_OBJ# = do.object_ID
and P_OBJ# = po.object_ID
and do.object_ID in
(select object_id from dba_objects where OBJECT_NAME = '&OBJNAME')
/
Select object_id, referenced_object_id, level
from public_dependency
start with object_id = (Select object_id
from sys.DBA_OBJECTS
WHERE owner = upper('&owner')
AND object_name = upper('&name')
AND object_type = upper('&type'))
connect by prior referenced_object_id = object_id
/
Select to_char(object_id) object_id, to_char(referenced_object_id) referenced_object_id, to_char(level) "LEVEL"
from public_dependency
connect by prior object_id = referenced_object_id
start with referenced_object_id = (
Select object_id from sys.DBA_OBJECTS
WHERE owner = upper('&owner')
AND object_name = upper('&name')
AND object_type = upper('&type'))
/
set feedback off
set ver off
set pages 10000
column Owner format "A10"
column Obj# format "9999999999"
column Object format "A35"
rem
ACCEPT OWN CHAR PROMPT "Enter OWNER pattern: "
ACCEPT NAM CHAR PROMPT "Enter OBJECT NAME pattern: "
prompt
prompt Objects matching &&OWN..&&NAM
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select o.obj# "Obj#",
decode(o.linkname, null, u.name||'.'||o.name,
o.remoteowner||'.'||o.name||'@'||o.linkname) "Object",
decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
10, '*Not Exist*',
11, 'PKG BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT', 66, 'JOB', 67, 'PROGRAM',
68, 'JOB CLASS', 69, 'WINDOW', 72, 'WINDOW GROUP',
74, 'SCHEDULE', 'UNDEFINED') "Type",
decode(o.status,0,'N/A',1,'VALID', 'INVALID') "Status"
from sys.obj$ o, sys.user$ u
where owner#=user#
and u.name like upper('&&OWN') and o.name like upper('&&NAM') ;
prompt
ACCEPT OBJID CHAR PROMPT "Enter Object ID required: "
prompt
prompt
prompt Object &&OBJID is:
prompt ~~~~~~~~~~~~~~~~~~~
select o.obj# "Obj#",
decode(o.linkname, null, u.name||'.'||o.name,
o.remoteowner||'.'||o.name||'@'||o.linkname) "Object",
decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
10, '*Not Exist*',
11, 'PKG BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT', 66, 'JOB', 67, 'PROGRAM',
68, 'JOB CLASS', 69, 'WINDOW', 72, 'WINDOW GROUP',
74, 'SCHEDULE', 'UNDEFINED') "Type",
decode(o.status,0,'N/A',1,'VALID', 'INVALID') "Status",
substr(to_char(stime,'DD-MON-YYYY HH24:MI:SS'),1,20) "S-Time"
from sys.obj$ o, sys.user$ u
where owner#=user# and o.obj#='&&OBJID' ;
prompt
prompt Depends on:
prompt ~~~~~~~~~~~~
select o.obj# "Obj#",
decode(o.linkname, null,
nvl(u.name,'Unknown')||'.'||nvl(o.name,'Dropped?'),
o.remoteowner||'.'||nvl(o.name,'Dropped?')||'@'||o.linkname) "Object",
decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
10, '*Not Exist*',
11, 'PKG BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, 'MATERIALIZED VIEW',
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT', 66, 'JOB', 67, 'PROGRAM',
68, 'JOB CLASS', 69, 'WINDOW', 72, 'WINDOW GROUP',
74, 'SCHEDULE', 'UNDEFINED') "Type",
decode(sign(stime-P_TIMESTAMP),
1,'*NEWER*',-1,'*?OLDER?*',null,'-','-SAME-')
"TimeStamp",
decode(o.status,0,'N/A',1,'VALID','INVALID') "Status"
from sys.dependency$ d, sys.obj$ o, sys.user$ u
where P_OBJ#=obj#(+) and o.owner#=u.user#(+) and D_OBJ#='&&OBJID' ;
Script:Logfile Switch Frequency Map
2009/06/14 by Leave a Comment
该脚本可以用于列出Oracle日志文件切换的频率图:
REM Log Switch Frequency Map
col Day for a10
col Date for a10
set linesize 80
SELECT trunc(first_time) "Date",
to_char(first_time, 'Dy') "Day",
count(1) "Total",
SUM(decode(to_char(first_time, 'hh24'), '00', 1, 0)) "h0",
SUM(decode(to_char(first_time, 'hh24'), '01', 1, 0)) "h1",
SUM(decode(to_char(first_time, 'hh24'), '02', 1, 0)) "h2",
SUM(decode(to_char(first_time, 'hh24'), '03', 1, 0)) "h3",
SUM(decode(to_char(first_time, 'hh24'), '04', 1, 0)) "h4",
SUM(decode(to_char(first_time, 'hh24'), '05', 1, 0)) "h5",
SUM(decode(to_char(first_time, 'hh24'), '06', 1, 0)) "h6",
SUM(decode(to_char(first_time, 'hh24'), '07', 1, 0)) "h7",
SUM(decode(to_char(first_time, 'hh24'), '08', 1, 0)) "h8",
SUM(decode(to_char(first_time, 'hh24'), '09', 1, 0)) "h9",
SUM(decode(to_char(first_time, 'hh24'), '10', 1, 0)) "h10",
SUM(decode(to_char(first_time, 'hh24'), '11', 1, 0)) "h11",
SUM(decode(to_char(first_time, 'hh24'), '12', 1, 0)) "h12",
SUM(decode(to_char(first_time, 'hh24'), '13', 1, 0)) "h13",
SUM(decode(to_char(first_time, 'hh24'), '14', 1, 0)) "h14",
SUM(decode(to_char(first_time, 'hh24'), '15', 1, 0)) "h15",
SUM(decode(to_char(first_time, 'hh24'), '16', 1, 0)) "h16",
SUM(decode(to_char(first_time, 'hh24'), '17', 1, 0)) "h17",
SUM(decode(to_char(first_time, 'hh24'), '18', 1, 0)) "h18",
SUM(decode(to_char(first_time, 'hh24'), '19', 1, 0)) "h19",
SUM(decode(to_char(first_time, 'hh24'), '20', 1, 0)) "h20",
SUM(decode(to_char(first_time, 'hh24'), '21', 1, 0)) "h21",
SUM(decode(to_char(first_time, 'hh24'), '22', 1, 0)) "h22",
SUM(decode(to_char(first_time, 'hh24'), '23', 1, 0)) "h23",
round(count(1) / 24, 2) "Avg"
FROM V$log_history
group by trunc(first_time), to_char(first_time, 'Dy')
Order by 1
/
