oracle 11 表分析,关于ORACLE11G自动分析表功能的疑问

set pagesize 1000

set trimout on

set trimspool on

Set Feedback off

set timing off

set verify off

prompt

prompt -- ----------------------------------------------------------------------- ---

prompt -- Scheduler Attribute ---

prompt -- ----------------------------------------------------------------------- ---

prompt

Set linesize 180

Set Pagesize 50

column an format a28 word_wrapped heading "Attribute|Name"

column v format a28 word_wrapped heading "Value"

select

attribute_name an

, value v

from

Dba_Scheduler_Global_Attribute

Order

By attribute_name

;

prompt

prompt -- Schedule jobs state (users only)

prompt -- ----------------------------------------------------------------------- ---

column status format a120 wrap heading "Status"

Set Heading Off

Set Feedback Off

Select status_01||' | '||status_02 status

From

(Select ' Schedule Ok '||Lpad(count(*),16) status_01 from Dba_Scheduler_Jobs where state <> 'DISABLED' and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'ORACLE_OCM'))

, (Select ' Schedule Disabled '||Lpad(count(*),13) status_02 from Dba_Scheduler_Jobs where state = 'DISABLED' and owner not in ('SYS', 'SYSTEM', 'DBSNMP', 'EXFSYS', 'MDSYS', 'OLAPSYS', 'WMSYS', 'TSMSYS', 'CTXSYS', 'SYSMAN', 'ORACLE_OCM'))

;

Prompt

Prompt

prompt -- Schedule jobs state (full)

prompt -- ----------------------------------------------------------------------- ---

set head on

column st format a10 word_wrapped heading "State"

column o format a10 word_wrapped heading "Owner"

column en format a10 word_wrapped heading "Enabled"

column ct format 999999 heading "Count"

clear breaks

break on st -

skip 1

compute Sum of ct on st

select state st

, enabled en

, owner o

, count(*) ct

from Dba_Scheduler_Jobs

Group

By state

, enabled

, owner

Order By state

, enabled

, owner

;

prompt -- Schedule jobs details

prompt -- ----------------------------------------------------------------------- ---

column o format a10 word_wrapped heading "Owner"

column jn format a30 word_wrapped heading "Job Name|(Subname - Creator)"

column jt format a17 word_wrapped heading "Job Type"

column ja format a38 heading "Job Action"

column st format a9 word_wrapped heading "State"

column lsd format a16 word_wrapped heading "Last Start Date"

column nrd format a16 word_wrapped heading "Next Run Date"

column fc format 9999 heading "Fail.|Ct."

column rc format 999999 heading "Run|Ct."

column en format a6 word_wrapped heading "Enab."

column ri format a30 word_wrapped heading "Rep.|Int."

column sosn format a24 word_wrapped heading "Sched. |Owner|Sched. Name"

column sowc format a5 word_wrapped heading "Stop|On|Wind.|Close"

column ad format a5 word_wrapped heading "Auto|Drop"

clear breaks

break on o -

skip 1

select

owner o

, job_name||'('||decode(job_subname,null,'',job_subname||' - ') ||job_creator||')' jn

, job_type jt

, replace(Substr(job_action,1,38),chr(10),' ') ja

, state st

, to_char(last_start_date, 'DD-MM-YYYY HH24:MI') lsd

, to_char(next_run_date, 'DD-MM-YYYY HH24:MI') nrd

-- , substr(repeat_interval,1,30) ri

, failure_count fc

, run_count rc

, enabled en

-- , decode(schedule_owner,null,'',schedule_owner||' - ')||schedule_name sosn

, stop_on_window_close sowc

, auto_drop ad

from

Dba_Scheduler_Jobs

Order

By owner

, state

, enabled

, job_name

;

clear breaks