pat98's always & forever

prompt **********************************************************

prompt * Health Checker                                                          *

prompt ********************************************************** 

set feedback off

clear columns;

column timecol new_value today

column spool_extension new_value suffix

select to_char(sysdate,'YYYYMMDD') timecol,

'.txt' spool_extension from sys.dual;

column output new_value inst_name

select value || '_' output

from v$parameter where name = 'instance_name';

set linesize 160

set pages 150

spool health_&&inst_name&&today&&suffix

prompt

prompt *******************************************************

prompt *               host info                             *

prompt *******************************************************

prompt 

col Host format a15

col version for a10

col InstID for 99

select name "DB Name",  instance_number "InstID", instance_name "Inst Name",

      host_name "Host", version "Version", TO_CHAR(startup_time, 'YYYY-MM-DD HH24:MI:SS') "Startup Time",

      parallel "Parallel", log_mode "Log Mode", archiver "Archiver"

from v$instance, v$database

/

prompt

prompt *******************************************************

prompt *               parameter info                        *

prompt *******************************************************

prompt 

col name for a35

col value for a50

select name, value from v$parameter

where name IN ('cpu_count', 'db_name', 'db_block_size','db_cache_size', 'shared_pool_size','large_pool_size', 'java_pool_size', 'log_buffer');

select name, value from v$parameter order by 1;

clear columns;

prompt

prompt *******************************************************

prompt *               Memory Size                           *

prompt *******************************************************

select decode(pool,null,name,pool) as POOL ,trunc(sum(bytes)/1024/1024,1) as MB

from v$sgastat group by decode(pool,null,name,pool) order by 1

/

prompt

prompt *******************************************************

prompt *               version info                          *

prompt *******************************************************

prompt 

col comp_name for a40;

col version for a30;

select * from v$version;

select comp_name, version, status from dba_registry;

clear columns;

prompt 

prompt

prompt

clear columns;

prompt

prompt *******************************************************

prompt *               archived log mode                     *

prompt *******************************************************

prompt 

select name, log_mode from v$database;

archive log list;

prompt

prompt *******************************************************

prompt *               HOT BACKUP INFO                       *

prompt *******************************************************

prompt

col tablespace_name for a25

col file_name for a40

col BACKUP_TIME for a30

select /*+ use_nl (a, b) */ file#, tablespace_name, a.status, change#, to_char(TIME, 'YYYY-MM-DD HH24:MI') as BACKUP_TIME 

from v$backup a, dba_data_files b

where a.file#=b.file_id

order by 1, 2, 3;

clear columns;

prompt

prompt *******************************************************

prompt *               Resource Info                         *

prompt *******************************************************

prompt

select RESOURCE_NAME "Resource Name",

       CURRENT_UTILIZATION "Current Util",

       MAX_UTILIZATION "Max Util",

       INITIAL_ALLOCATION "Initial Alloc",

       LIMIT_VALUE "Limit"

from v$resource_limit

/

clear columns;

prompt *******************************************************

prompt *               Library Cache Hit Ratio (Namespace)   *

prompt *******************************************************

prompt

col "Gets" for 999,999,999,999

col "Pins" for 999,999,999,999

select namespace "Namespace",

 gets "Gets",  round(gethitratio*100,3) "GetHit Ratio",

 pins "Pins",  round(pinhitratio*100,3) "PinHit Ratio",

 invalidations "Invalidations",

 decode(pins,0,0,round((1-reloads/pins)*100, 3)) "Hit Ratio(Pins-Reloads)"

from v$librarycache

/

clear columns;

prompt

prompt *******************************************************

prompt *            DATA  DICTIONARY CACHE TUNING            *

prompt *******************************************************

prompt

select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') "Time", 

        trunc(sum(getmisses)/sum(gets)*100, 5)||'%  (less than 9.8%)' 

"Data dictionary miss ratio "

from v$rowcache

/

prompt

prompt *******************************************************

prompt *            Dictionary Cache Hit Ratio (Parameter)   *

prompt *******************************************************

prompt

SELECT parameter "Parameter",

         sum(gets) "Gets",

         sum(getmisses) "GetMisses" ,

         decode(sum(gets), 0, 0, round((1-sum(getmisses)/sum(gets))*100, 3)) "Hit Ratio"

FROM v$rowcache

GROUP BY parameter

/

prompt

prompt *******************************************************

prompt *               Shared Pool Free Space                *

prompt *******************************************************

prompt 

col "Shared Pool Reserved(MB)" format a40

select   s.total "Shared Pool Total(MB)",

         s.free - r.r_free "Shared Pool Free(MB)",

         'Free : ' || r.r_free || ',      Used : ' ||r.r_used "Shared Pool Reserved(MB)",

         s.other - r.r_used   "Shared Pool Other(MB)"

         from (select round(sum(bytes/1024/1024),2) total,

         round(sum(decode(name, 'free memory', bytes))/1024/1024,2) free,

         round(sum(decode(name, 'free memory',null, bytes))/1024/1024,2) other

         from   v$sgastat

         where  pool = 'shared pool' ) s,

         (select round(nvl(sum(free_space),0)/1024/1024,2) r_free,

         round(nvl(sum(used_space),0)/1024/1024,2) r_used

from v$shared_pool_reserved ) r

/

prompt

prompt *******************************************************

prompt *               Shared Pool Advice                    *

prompt *******************************************************

prompt 

select SHARED_POOL_SIZE_FOR_ESTIMATE "Shared Pool Size(MB)" ,SHARED_POOL_SIZE_FACTOR "Size Factor",

 ESTD_LC_SIZE "Estd Lib Cache Size", ESTD_LC_MEMORY_OBJECTS "Cached Objects",

 ESTD_LC_TIME_SAVED "Saved Time", ESTD_LC_TIME_SAVED_FACTOR "Saved Time Factor", ESTD_LC_MEMORY_OBJECT_HITS "Object Hits"

from v$shared_pool_advice

/

prompt

prompt *******************************************************

prompt *              BUFFER CACHE TUNING                    *

prompt *******************************************************

prompt

col "Logical Reads" for 999,999,999,999

col "Physical Reads" for 999,999,999,999

select   to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') "Time",

sum(decode(name,'session logical reads', value)) "Logical Reads",

sum(decode(name,'physical reads', value)) "Physical Reads",

sum(decode(name,'physical reads direct', value)) "Phy-Reads Direct",

sum(decode(name,'physical reads direct (lob)', value)) "Phy-Reads Direct(LOB)",

round((1 - (sum(decode(name,'physical reads', value))

- sum(decode(name,'physical reads direct', value))

- sum(decode(name,'physical reads direct (lob)', value)))

/ sum(decode(name,'session logical reads', value))) * 100, 3) "Hit Ratio"

from     v$sysstat

/

clear columns;

prompt

prompt *******************************************************

prompt *             LRU Queue Average Reusable Buffer       *

prompt *******************************************************

prompt

select a.value "free buffer found", b.value "free buffer requests", round(decode(b.value, 0, 0, (a.value / b.value)),1) "Average Reusable Buffer"

from v$sysstat a, v$sysstat b

where a.name = 'DBWR free buffers found'

and b.name = 'DBWR make free requests'

/

prompt

prompt *******************************************************

prompt *            Average LRU Buffer Scan                  *

prompt *******************************************************

prompt

select round(decode(b.value, 0, 0, (a.value / b.value)),1) "Average LRU Buffer Scan"

from v$sysstat a, v$sysstat b

where a.name = 'DBWR buffers scanned'

and b.name = 'DBWR lru scans'

/

prompt

prompt *******************************************************

prompt *            Free Buffer Scan Ratio                   *

prompt *******************************************************

prompt

select round(decode(b.value, 0, -1, (a.value / b.value)*100),2) "Free Buffer Scan Ratio"

from v$sysstat a, v$sysstat b

where a.name = 'free buffer inspected'

and b.name = 'free buffer requested'

/

prompt

prompt *******************************************************

prompt *            Buffer Cache Advice                      *

prompt *******************************************************

prompt

select size_for_estimate "Cache Size (MB)", size_factor "Size Factor", buffers_for_estimate "Buffers",

estd_physical_read_factor "Estd Phys Read Factor", estd_physical_reads "Estd Phys Reads"

from v$db_cache_advice where advice_status = 'ON' and block_size = (select value from v$parameter where name = 'db_block_size') and name = 'DEFAULT'

order by 1,2

/

prompt

prompt *******************************************************

prompt *        INTERNAL SORT AND EXTERNAL SORT              *

prompt *******************************************************

prompt

select a.value "Sort(memory)",

       b.value "Sort(disk)",

       round(a.value/(a.value+b.value) * 100 ,2) "Memory Sort Hit Ratio"

from v$sysstat a, v$sysstat b

       where a.name = 'sorts (memory)'

       and b.name = 'sorts (disk)'

/

prompt

prompt *******************************************************

prompt *        Rows per Sort                                *

prompt *******************************************************

prompt

select round(a.value / (b.value + c.value),2) "Rows per Sort"

from v$sysstat a, v$sysstat b, v$sysstat c

where a.name = 'sorts (rows)'

and b.name = 'sorts (disk)'

and c.name = 'sorts (memory)'

/

prompt

prompt *******************************************************

prompt *        Sort Segment Usage                           *

prompt *******************************************************

prompt

SELECT tablespace_name "Tablespace Name",

       extent_size "Extent Size",

       current_users "Current Users",

       total_extents "Total Exts",

       used_extents "Used Exts",

       free_extents "Free Exts",

       max_used_size "Max Used Exts"

FROM v$sort_segment

/

prompt

prompt *******************************************************

prompt *        PGA Statistics                               *

prompt *******************************************************

prompt

col name for a40

col value for 999,999,999,999,999

select name, value, 'bytes' unit from v$sysstat

where name like 'session pga memory%'

union all

select name, value, unit from v$pgastat

/

prompt

prompt *******************************************************

prompt *        SQL Workarea Statistics                      *

prompt *******************************************************

prompt

select name "Profile", cnt "Count",

decode(total, 0, 0, round(cnt*100/total)) "Percentage"

from (select name, value cnt,

(sum(value) over ()) total

from v$sysstat

where name like 'workarea executions%')

/

prompt

prompt *******************************************************

prompt *        PGA Target Advice                            *

prompt *******************************************************

prompt

select round(pga_target_for_estimate/1024/1024) "Target Size(MB)", 

pga_target_factor "Target Factor", estd_pga_cache_hit_percentage "PGA Cache Hit(%%)", 

estd_overalloc_count "Over-allocation Count"

from v$pga_target_advice where advice_status = 'ON'

/

prompt

prompt *******************************************************

prompt *        ROLLBACK SEGMENT`S WAIT RATIO                *

prompt *******************************************************

prompt

col "Rollback Segment" for a20

col "Miss Ratio" for a15

col "Rsize" for 999,999,999,999

col "Writes" for 999,999,999,999

col "Gets" for 999,999,999,999

select name "Rollback Segment",

     rssize "Rsize", writes "Writes", xacts "Xacts", status "Status",

     waits "Waits", gets "Gets",

     trunc(waits/gets*100, 5)||'%' "Miss Ratio"

from v$rollstat a, v$rollname b

where a.usn = b.usn

order by waits/gets desc

/

clear columns;

prompt

prompt *******************************************************

prompt *            REDO LOG FILE`S WAIT RATIO               *

prompt *******************************************************

prompt

select round(sum(decode(name, 'redo buffer allocation retries', value))/sum(decode(name, 'redo entries', value)),5)

      "Redo Buffer Space Wait(%%)",

      sum(decode(name, 'redo log space requests', value)) "Redo Space Reqeusts"

from v$sysstat

/

col name for a30

col value for a40

select name, value from v$parameter

where name IN('log_buffer', 'log_checkpoint_interval', 'log_checkpoint_timeout','fast_start_mttr_target');

clear columns;

prompt

prompt *******************************************************

prompt *            Redo Entry Average Size                  *

prompt *******************************************************

prompt

select round(decode(b.value, 0, -1, a.value / b.value),1) "Redo Entry Average Size(bytes)"

from v$sysstat a, v$sysstat b

where a.name = 'redo size'

and b.name = 'redo entries'

/

prompt

prompt *******************************************************

prompt *            Log Buffer Retry Ratio                   *

prompt *******************************************************

prompt

select round(decode(b.value, 0, -1, a.value / b.value * 100),6) "Log Buffer Retry Ratio(%%)"

from v$sysstat a, v$sysstat b

where a.name = 'redo buffer allocation retries'

and b.name = 'redo entries'

/

prompt

prompt *******************************************************

prompt *            Redo Log Switch Count(Daily) 1 week      *

prompt *******************************************************

prompt

select thread#, to_char(first_time, 'yyyy/mm/dd') "Time", count(*) "Count"

from v$loghist

where first_time  > sysdate -7

group by thread#, to_char(first_time, 'yyyy/mm/dd')

order by 1, 2

/

select thread#,  count(*) "1 Week Count"

from v$loghist

where first_time  > sysdate -7

group by thread#

order by 1

/

prompt

prompt *******************************************************

prompt *               Latch Hit Ratio                       *

prompt *******************************************************

select * from (select name "Name",

decode(sign(gets-immediate_gets), 1, gets, immediate_gets) "(I)Gets",

decode(sign(gets-immediate_gets), 1, misses, immediate_misses) "(I)Misses",

sleeps "Sleeps",

decode(sign(gets-immediate_gets), 1, round(decode(gets,0,-1,(1-misses/gets) * 100),3),

round(decode(immediate_gets+immediate_misses,0,-1,

(1- immediate_misses/ (immediate_gets+immediate_misses)) * 100), 3 ))    "Hit Ratio"

from   v$latch

where decode(sign(gets-immediate_gets), 1, gets, immediate_gets) > 0

order by 5, 2 desc) where rownum <= 10

/

clear columns;

prompt

prompt *******************************************************

prompt *               Session Count                         *

prompt *******************************************************

col username for a15

col machine for a25

select username, program, machine, count(*) as session_count

from v$session

where username is not null

group by username, program, machine

/

select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') "Time",

 count(*) "Total Sessions",

 count(decode(status, 'ACTIVE',1) ) "Active Sessions"

from v$session

where username is not null

/

clear columns;

rem prompt

rem prompt *******************************************************

rem prompt *               Session Cursor Cache Hit Ratio        *

rem prompt *******************************************************

rem col "Cursor Cache Hits(%%)" for a21

rem select

rem        round(100 * (calls - sess - hard) / calls, 3)  "Soft Parses(%%)",

rem        round(100 * hard / calls, 3)  "Hard Parses(%%)",

rem        decode(round(100 * sess / calls, 3),0,decode(parm,0,to_char('N/A'),0),round(100 * sess / calls, 3))  

rem           "Cursor Cache Hits(%%)"

rem from

rem       ( select value calls from v$sysstat where name = 'parse count (total)' ),

rem       ( select value hard from v$sysstat where name = 'parse count (hard)' ),

rem       ( select value sess from v$sysstat where name = 'session cursor cache hits' ),

rem       ( select value parm from v$parameter where name = 'session_cached_cursors')

rem /

rem clear columns;

prompt

prompt *******************************************************

prompt *               Cursors opened per transaction        *

prompt *******************************************************

select decode(b.value, 0, -1, round(a.value/b.value)) "Cursors opened per transaction"

from v$sysstat a, v$sysstat b

where a.name = 'opened cursors cumulative'

and b.name = 'user commits'

/

prompt

prompt *******************************************************

prompt *               Recursive to User Call Ratio          *

prompt *******************************************************

select decode(b.value, 0, -1, round(a.value/b.value*100)) "Recur to User Call Ratio(%%)"

from v$sysstat a, v$sysstat b

where a.name = 'recursive calls'

and b.name = 'user calls'

/

prompt

prompt *******************************************************

prompt *               Transaction Rate (TPS)                *

prompt *******************************************************

select round((a.value+b.value) / ((sysdate - (select startup_time from v$instance)) * 24 * 3600),2) "Transaction Rate"

from v$sysstat a, v$sysstat b

where a.name = 'user commits'

and b.name = 'user rollbacks'

/

prompt

prompt *******************************************************

prompt *               Parse Count per User Calls            *

prompt *******************************************************

select decode(b.value, 0, -1, round(a.value/b.value)) "Parse Count per User Calls"

from v$sysstat a, v$sysstat b

where a.name = 'parse count (total)'

and b.name = 'user calls'

/

prompt

prompt *******************************************************

prompt *               Active Transaction                    *

prompt *******************************************************

col username for a10

col "Machine" for a15

col "Pgm" for a15

col "RBS" for a10

col status for a10

col sid for 9999

col "Pgm" for a15

select /*+ ordered */ s.username,

     s.sid sid ,

     substr(s.machine,1,15) "Machine" ,

     substr(s.program,1,15) "Pgm",

     t.status "Status",

     RECURSIVE "Recur",

     to_char(to_date(t.start_time, 'mm/dd/yy HH24:MI:SS'), 'yyyy/mm/dd hh24:mi:ss') "StartTime",

     r.name  "RBS",

     t.used_ublk  "UBLKS",

     t.used_urec "URECS",

     t.log_io "L_IO",

     t.PHY_IO  "P_IO"

from v$transaction t, v$session s, v$rollname r, v$process p

where s.saddr = t.ses_addr and t.xidusn = r.usn

     and s.paddr = p.addr

     order by used_ublk

/

clear columns;

prompt

prompt *******************************************************

prompt *               Pending Distributed Transaction       *

prompt *******************************************************

col host for a10

col global_tran_id for a30

select local_tran_id, global_tran_id, state, mixed, host, commit#

from dba_2pc_pending;

col database for a30

select local_tran_id, in_out, database, dbuser_owner, interface

from dba_2pc_neighbors;

prompt

prompt *******************************************************

prompt *               System Wait Event                     *

prompt *******************************************************

col event format a35

select * 

from (select event,

total_waits "Total Waits",

time_waited "Time Waited(cs)",

round(average_wait,5) "Avg Wait Time(cs)",

total_timeouts "Total Timeouts"

from v$system_event

where event not in ('SQL*Net message to client','SQL*Net more data to client','SQL*Net message from client',

'SQL*Net more data from client','SQL*Net break/reset to client','rdbms ipc message','rdbms ipc reply',

'SQL*Net message from dblink','SQL*Net more data from dblink','SQL*Net message to dblink',

'pipe get','pipe put','smon timer','pmon timer','dispatcher timer','PL/SQL lock timer','wakeup time manager',

'ges remote message','gcs remote message','i/o slave wait','jobq slave wait','slave wait','class slave wait',

'PX Idle Wait','PX Deque wait','PX Deq: Execution Msg','PX Deq: Table Q Normal','PX Deq Credit: need buffer','PX Deq Credit: send blkd',

'PX Deq: Par Recov Reply','PX Deq: Par Recov Execute','PX Deq: Par Recov Change Vector','PX Deq: Join ACK','PX Deq: Execute Reply','PX Deq: Parse Reply',

'PX Deq: reap credit','PX Deq: Signal ACK','PX Deq: Msg Fragment','PX Deq: Table Q Get Keys',

'Queue Monitor Slave Wait','Queue Monitor Wait','Queue Monitor Shutdown Wait','Queue Monitor IPC wait','single-task message',

'Streams AQ: waiting for messages in the queue','Streams AQ: waiting for time management or cleanup tasks',

'Streams AQ: qmn coordinator idle wait','Streams AQ: qmn slave idle wait','virtual circuit status','Null event')

and time_waited > 0

order by time_waited desc)

where rownum < 21

/

prompt

prompt *******************************************************

prompt *            Invalid Obecjt                           *

prompt *******************************************************

prompt

SELECT count(*) as "ALL Count"

FROM DBA_OBJECTS

WHERE STATUS = 'INVALID'

/

select

 owner, object_type,

 count(*) "Invalid Count"

 from dba_objects

 where status  = 'INVALID'

group by owner, object_type

order by 1, 2

/

prompt

prompt *******************************************************

prompt *            User Objects in SYSTEM Tablespace        *

prompt *******************************************************

prompt

select owner "Owner", segment_name "Segment Name", segment_type "Segment Type"

from dba_segments

where tablespace_name = 'SYSTEM'

 and owner not in ('SYSTEM', 'SYS', 'OUTLN', 'DBSNMP', 'MDSYS', 'ORDSYS','OLAPDBA', 'ORDPLUGINS', 

 'OLAPSVR', 'OSE$HTTP$ADMIN', 'AURORA$ORB$UNAUTHENTICATED', 'AURORA$JIS$UTILITY$','WMSYS')

/

prompt *******************************************************

prompt *           Index Scan Ratio                          *

prompt *******************************************************

prompt

select

round(sum(decode(name, 'table fetch by rowid', value)) /

(sum(decode(name, 'table scan rows gotten', value)) + sum(decode(name, 'table fetch by rowid', value)))*100,2) "Index Scan Ratio"

from v$sysstat

/

prompt *******************************************************

prompt *            Session Index Scan Ratio                 *

prompt *******************************************************

prompt

select /*+ ordered use_hash(c) swap_join_inputs(c) */ s.sid, s.program "Program",

     sum(decode(c.name, 'table fetch by rowid', a.value)) "Index Scan",

     sum(decode(c.name, 'table scan rows gotten',a.value)) "Full Scan",

     decode(sum(decode(c.name, 'table fetch by rowid', a.value))+sum(decode(c.name, 'table scan rows gotten',a.value)),0,

     -1, round(sum(decode(c.name, 'table fetch by rowid', a.value))/

     (sum(decode(c.name, 'table fetch by rowid', a.value))+sum(decode(c.name, 'table scan rows gotten',a.value)))*100,2)) "Index Scan Ratio"

from v$session s, v$sesstat a, v$statname c

where c.name in ( 'table scan rows gotten', 'table fetch by rowid' )

      and a.statistic# = c.statistic#

      and s.sid = a.sid

      and s.type != 'BACKGROUND'

      and s.module <> 'Orange for ORACLE'

group by s.sid, s.program

order by 5

/

prompt *******************************************************

prompt *            Chained Row Ratio                        *

prompt *******************************************************

prompt

SELECT sum(decode(name,'table fetch continued row',value,0)) "Chained row Access",

  sum(decode(name,'table fetch by rowid',value,0)) "Index Access",

  sum(decode(name,'table scan rows gotten',value,0)) "Table Scan Access",

  round(sum(decode(name,'table fetch continued row',value,0)) /

  (sum(decode(name,'table fetch by rowid',value,0)) + sum(decode(name,'table scan rows gotten',value,0))) * 100,5) "Ratio"

FROM v$sysstat

/

prompt *******************************************************

prompt *            TOP-20 SQL (by Buffer Gets)               *

prompt *******************************************************

prompt

col username for a12

col "HASH_VALUE" for a12

col executions for 99,999,999

col buffer_gets for 99,999,999,999

col disk_reads for 999,999,999

col "RowsPerExec" for 999,999,999

col parse_calls for 99,999,999

col "SQL_TEXT" for a120

select * from (

select u.username, sharable_mem, executions, parse_calls, disk_reads, buffer_gets, round(decode(executions,0,-1,rows_processed/executions)) "RowsPerExec",

round(elapsed_time/(decode(executions,0,-1000000,executions*1000000)),2) "Elap-Time(Sec)", optimizer_mode,  to_char(hash_value) "HASH_VALUE", --address, child_number, 

substr(sql_text,1,120) "SQL_TEXT"

from v$sql s, all_users u

where s.parsing_user_id = u.user_id

and u.username <> 'SYS'

and u.username <> 'SYSTEM'

and command_type <> 47

order by buffer_gets desc)

where rownum <= 20

/

prompt *******************************************************

prompt *            TOP-20 SQL (by Buffer Gets per Execution)*

prompt *******************************************************

prompt

select * from (

select u.username, sharable_mem, executions, parse_calls, disk_reads,

trunc(decode(executions, 0, -1, (buffer_gets/executions))) "BufferGets/Exec", round(decode(executions,0,-1,rows_processed/executions)) "RowsPerExec",

round(elapsed_time/(decode(executions,0,-1000000,executions*1000000)),2) "Ela-Time(Sec)",

optimizer_mode,  to_char(hash_value) "HASH_VALUE", --address, child_number, 

substr(sql_text,1,120) "SQL_TEXT"

from v$sql s, all_users u

where s.parsing_user_id = u.user_id

and u.username <> 'SYS'

and u.username <> 'SYSTEM'

and command_type <> 47

order by decode(executions, 0, -1, (buffer_gets/executions)) desc)

where rownum <= 20

/

prompt *******************************************************

prompt *            TOP-20 SQL (by Disk Reads)               *

prompt *******************************************************

prompt

select * from (

select u.username, sharable_mem, executions, parse_calls, disk_reads, buffer_gets, round(decode(executions,0,-1,rows_processed/executions)) "RowsPerExec",

round(elapsed_time/(decode(executions,0,-1000000,executions*1000000)),2) "Elap-Time(Sec)", optimizer_mode,  to_char(hash_value) "HASH_VALUE", --address, child_number, 

substr(sql_text,1,120) "SQL_TEXT"

from v$sql s, all_users u

where s.parsing_user_id = u.user_id

and u.username <> 'SYS'

and u.username <> 'SYSTEM'

and command_type <> 47

order by disk_reads desc)

where rownum <= 20

/

prompt *******************************************************

prompt *            TOP-20 SQL (by Elapsed Time)             *

prompt *******************************************************

prompt

select * from (

select u.username, sharable_mem, executions, parse_calls, disk_reads, buffer_gets, round(decode(executions,0,-1,rows_processed/executions)) "RowsPerExec",

round(elapsed_time/(decode(executions,0,-1000000,executions*1000000)),2) "Elap-Time(Sec)", optimizer_mode,  to_char(hash_value) "HASH_VALUE", --address, child_number

substr(sql_text,1,120) "SQL_TEXT"

from v$sql s, all_users u

where s.parsing_user_id = u.user_id

and u.username <> 'SYS'

and u.username <> 'SYSTEM'

and command_type <> 47

order by round(elapsed_time/(decode(executions,0,-1000000,executions*1000000)),2) desc)

where rownum <= 20

/

clear columns;

prompt *******************************************************

prompt *            Literal SQL                              *

prompt *******************************************************

prompt

col "SQL_TEXT" for a70

col "HASH_VALUE" for a12

select substr(sql_text, 1, 70) "SQL_TEXT",

count(*) "Count",

sum(executions) "TotExecs",

sum(sharable_mem) "Mem",

--to_char(to_date(min(first_load_time), 'yyyy-mm-dd/hh24:mi:ss'), 'YYYY/MM/DD hh24:mi:ss') "Start Time",

--to_char(to_date(max(first_load_time), 'yyyy-mm-dd/hh24:mi:ss'), 'YYYY/MM/DD hh24:mi:ss') "End TIme",

to_char(max(hash_value)) "HASH_VALUE"

from v$sql

where executions < 5

group by substr(sql_text, 1, 70)

having count(*) > 30

order by 2 desc

/

clear columns;

prompt

prompt *******************************************************

prompt *            Tablespace Fragments                     *

prompt *******************************************************

prompt

select tablespace_name

      ,total_extents

      ,percent_extents_coalesced || '% Coalesced'

  from dba_free_space_coalesced

 where percent_extents_coalesced <> 100

/

prompt

prompt *******************************************************

prompt *            Many Extent Allocation segment(100 > )   *

prompt *            Max_Extents < Extents + 10               *

prompt *******************************************************

prompt

set feedback on

set pagesize 150

set linesize 150

col owner for a10;

col segment for a25;

col type for a15;

col tablespace for a18;

select owner

      ,segment_name                      "Segment"

      ,segment_type                      "Type"

      ,tablespace_name                   "Tablespace"

      ,round(bytes/1024/1024,2)          "Size(MB)"

      ,extents                           "Extents"

      ,max_extents                       "MaxExtents"

      ,round(initial_extent/1024/1024,2) "Initial(MB)"

      ,round(next_extent/1024/1024,2)    "Next(MB)"

   from dba_segments

  where 

          (extents > 100

                or max_extents < extents + 10)

                and owner NOT IN ('SYS','SYSTEM')

/

prompt

prompt *******************************************************

prompt *   Unable to allocate next extent                    *

prompt *   Not Enough Free space in Tablespace               *

prompt *******************************************************

prompt

select s.owner

        ,s.segment_name         "Segment"

        ,s.segment_type         "Type"

        ,s.tablespace_name      "Tablespace"

        ,round(s.next_extent/1024/1024,2)       "Next(MB)"

from    dba_segments s

where   s.next_extent > (select MAX(f.bytes) 

                        from dba_free_space f

                        where f.tablespace_name = s.tablespace_name)

/

clear columns;

prompt

prompt *******************************************************

prompt *            TABLESPACE Usage                         *

prompt *******************************************************

prompt

set feedback off

SET PAGESIZE 100 

SET LINESIZE 100

COLUMN pct_free FORMAT 999.99 HEADING "% Free" 

COLUMN pct_used FORMAT 999.99 HEADING "% Used"

COLUMN name FORMAT A25 HEADING "Tablespace Name" 

COLUMN mbytes FORMAT 99,999,999 HEADING "Total MBytes" 

COLUMN used FORMAT 99,999,999 HEADING "Used Mbytes" 

COLUMN free FORMAT 99,999,999 HEADING "Free Mbytes" 

BREAK ON REPORT

COMPUTE SUM LABEL TOTAL OF mbytes free used ON REPORT 

SELECT df.tablespace_name name, ts.extent_management ext_mgt, df.totalspace mbytes,

(df.totalspace - NVL(fs.freespace,0)) used, NVL(fs.freespace,0) free,

100 * (NVL(fs.freespace,0) / df.totalspace) pct_free,

100 * ((df.totalspace - NVL(fs.freespace,0)) / df.totalspace) pct_used

FROM    (SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) TotalSpace

FROM dba_data_files

GROUP BY tablespace_name) df, (SELECT tablespace_name,

ROUND(SUM(bytes) / 1048576) FreeSpace

FROM dba_free_space

GROUP BY tablespace_name ) fs, dba_tablespaces ts

WHERE df.tablespace_name = fs.tablespace_name(+)

AND df.tablespace_name = ts.tablespace_name

order by 1

/

rem select df.tablespace_name "Tablespace",

rem         round(df.TBS_byte /1048576,2) "Total(MB)",

rem         round((df.TBS_byte - fs.Free_byte)/1048576,2) "Used(MB)",

rem         round(fs.Free_byte /1048576,2) "Free(MB)",

rem         round((fs.Free_byte/df.TBS_byte) *100,0) "Free(%%)",

rem         fs.pieces "Pieces",

rem         round(fs.Max_free /1048576,2) "MaxFree(MB)"

rem from   ( select tablespace_name, sum(bytes) TBS_byte

rem          from dba_data_files group by tablespace_name ) df,

rem         ( select tablespace_name, max(bytes) Max_free, sum(bytes) Free_byte, count(*) pieces

rem          from dba_free_space group by tablespace_name ) fs,

rem         ( select tablespace_name, initial_extent, next_extent

rem          from dba_tablespaces ) db

rem where df.tablespace_name = db.tablespace_name

rem  and   df.tablespace_name = fs.tablespace_name(+)

rem order by 5

rem /

set feedback on

prompt

prompt *******************************************************

prompt *            datafile autoextensible                  *

prompt *******************************************************

prompt

set linesize 150

set pagesize 150

col file_name for a50;

col tablespace_name for a20;

select tablespace_name, file_name,bytes/1024/1024 as MB, maxbytes/1024/1024 as maxsize, autoextensible

from dba_data_files

order by 1

/

prompt

prompt *******************************************************

prompt *            File I/O INFO                            *

prompt *******************************************************

prompt

col name for a40;

select name "Name"

,phyrds "Phy Rds"

,phywrts "Phy Wrts"

,phyrds+phywrts "Total IO"

,trunc(phyrds/read_sum*100,2) "Read(%%)"

,trunc(phywrts/write_sum*100,2) "Write(%%)"

,trunc( (phyrds+phywrts) / (read_sum+write_sum) * 100 , 2) "Total IO(%%)"

,avgiotim "Avg IO Time"

from v$filestat a, v$datafile b,

(select sum(phyrds) read_sum, sum(phywrts) write_sum

from v$filestat ) c

where a.file# = b.file#

order by name desc

/

clear columns;

prompt

prompt *******************************************************

prompt *            Tablespace I/O INFO                      *

prompt *******************************************************

prompt

SELECT max(c.name) "Tablespace Name",

sum(phyrds) "Phy Rds",

sum(phywrts) "Phy Wrts",

sum(phyblkrd) "Phy Blk Rd",

sum(phyblkwrt) "Phy Blk Wrt",

sum(phyrds+phywrts) "Phy Rds+Wrts",

sum(phyblkrd+phyblkwrt) "Phy Blk Rd+Wrt",

trunc(decode(sum(phyrds),0,-1, sum(phyblkrd)/sum(phyrds)),1) "Blocks/Read"

FROM v$filestat a,

v$datafile b,

v$tablespace c

WHERE a.file# = b.file#

and b.ts# = c.ts#

group by b.ts#

order by "Blocks/Read" desc, "Phy Blk Rd" desc

/

prompt

prompt *******************************************************

prompt *            Table and Index Size Calculation        *

prompt *******************************************************

prompt

select ty as seg_type, sum(sz) as "Size(MB)"

from (select decode(substr(segment_type,1,5),'TABLE','Table Segments','INDEX','Index Segments','LOBSE','Table Segments','LOBIN','Index Segments','Etc Segments') as ty,

 trunc(sum(bytes/1024/1024),0) as sz

 from dba_segments

 where owner not in ('SYSTEM', 'SYS', 'SYSMAN', 'OUTLN', 'DBSNMP', 'MDSYS', 'ORDSYS','OLAPDBA', 'ORDPLUGINS',

  'OLAPSVR', 'OSE$HTTP$ADMIN', 'AURORA$ORB$UNAUTHENTICATED', 'AURORA$JIS$UTILITY$','WMSYS',

  'WMSYS', 'CTXSYS', 'XDB', 'ANONYMOUS', 'TRACESVR', 'REPADMIN', 'SCOTT', 'HR', 'SH')

 group by segment_type)

group by ty order by 1 desc;

spool off;

exit

사용법 : ./mon.sh mon.sql 3 (수행간격)

===============================

if [ $# -eq 1 ]

then

 arg2=2

elif [ $# -eq 2 ]

then

 arg2=$2

else

    echo "Usage : mon.sh sql term";

    exit;

fi

arg1=$1

while true

do

sqlplus -s '/ as sysdba' <<EOF

set linesize 200

set pause off

set pagesize 120

@$arg1

EOF

sleep $arg2

done

- mon.sql 화일내용

prompt ##################################################################################################################

!date

prompt ========================================================

prompt = Session Wait

prompt ========================================================

col DB      format 99

col sid     format 999999

col serial# format 99999

col spid    format a7

col cli_spid format a10

col process format a11

col event   format a30

col p       format a20

col W       format 999

col program format a15

col C       format a2

col blkSid  format 99999

col object_name format a20

set linesize 170 pagesize 100 trimspool on

select 

       a.inst_id "DB",

       a.sid, a.serial#, 

       (select p.spid from gv$process p where (a.inst_id = p.inst_id) and (a.paddr = p.addr))  spid, a.status,a.event,a.process as CLI_SPID,

       decode(a.event,'enqueue',

              decode(chr(bitand(a.p1,-16777216)/16777215)||chr(bitand(a.p1, 16711680)/65535),'TX',

              chr(bitand(a.p1,-16777216)/16777215)||chr(bitand(a.p1, 16711680)/65535)||to_char( bitand(a.p1, 65535) )||','||

              'usn.slot:'||bitand(a.p2, 4294901760)/65536||'.'||bitand(a.p2, 65535)||','||'wrap:'||a.p3,

              a.p1||','||a.p2||','||a.p3),a.p1||','||a.p2||','||a.p3) P,

       a.seconds_in_wait W, 

       substr(a.program,1,15) program, 

       decode(a.command,3,'S',2,'I',6,'U',7,'D',command) C,

       a.sql_id,

       substr((SELECT OBJECT_NAME FROM DBA_OBJECTS C WHERE C.OBJECT_ID = ROW_WAIT_OBJ# ),1,20) object_name,

       a.blocking_session blkSid, -- comment out for 10.2.0.2

       substr((SELECT NAME FROM V$LATCH WHERE LATCH# = p2 and event = 'latch free' ),1,10) LATCH_NAME

  from gv$session a

 where a.event not like 'SQL*Net message from client%'

   and a.event not like 'rdbms ipc%'

   and a.event not like 'smon timer'

   and a.event not like 'pmon timer'

   and a.event not like '%slave wait%'

   and a.event not like 'lock manager wait for remote message'

   and a.event not like 'PX Deq%'

   and a.event not like 'null event'

   and a.event not like 'gcs remote message'

   and a.event not like 'ges remote message'

   and a.event not like 'wakeup time manager'

   and a.event not like 'Queue Monitor%Wait'

   and a.event not like 'DIAG idle wait'

   and a.event not like 'Streams AQ:%'

   and a.sid <> (select sid from v$mystat where rownum = 1)

   and a.wait_class <> 'Idle'

order by a.inst_id, a.event, a.sid

/

prompt ========================================================

prompt =  v$latch_holder

prompt ========================================================

set linesize 150 pagesize 100 trimspool on

col name format a40

select 

'[latch]'||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') dt,

INST_ID,PID,SID,LADDR,NAME,GETS

from gv$latchholder

/

prompt ========================================================

prompt = 'Event summary'  having count(*) >= 3 

prompt ========================================================

col DB      format 99

col event   format a30

col count1   format 999,999

set linesize 150 pagesize 100 trimspool on

select '[event]'||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') dt,

        a.inst_id "DB", a.event,count(*) as count1

  from gv$session a

 where a.event not like 'SQL*Net message from client%'

   and a.event not like 'rdbms ipc%'

   and a.event not like 'smon timer'

   and a.event not like 'pmon timer'

   and a.event not like '%slave wait%'

   and a.event not like 'lock manager wait for remote message'

   and a.event not like 'PX Deq%'

   and a.event not like 'null event'

   and a.event not like 'gcs remote message'

   and a.event not like 'ges remote message'

   and a.event not like 'wakeup time manager'

   and a.event not like 'Queue Monitor%Wait'

   and a.event not like 'DIAG idle wait'

   and a.event not like 'Streams AQ:%'

   and a.sid <> (select sid from v$mystat where rownum = 1)

 group by a.inst_id, a.event

 having count(*) >= 3

 order by count(*) desc

/

--prompt ========================================================

--prompt = Program Info. having count(*) > 1

--prompt ========================================================

--col program format a40

--select '[program]'||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') dt,

--         program, count(*) as count1

--  from gv$session a

-- where a.event not like 'SQL*Net message from client%'

--   and a.event not like 'rdbms ipc%'

--   and a.event not like 'smon timer'

--   and a.event not like 'pmon timer'

--   and a.event not like '%slave wait%'

--   and a.event not like 'lock manager wait for remote message'

--   and a.event not like 'PX Deq%'

--   and a.event not like 'null event'

--   and a.event not like 'gcs remote message'

--   and a.event not like 'ges remote message'

--   and a.event not like 'wakeup time manager'

--   and a.event not like 'Queue Monitor%Wait'

--   and a.event not like 'DIAG idle wait'

--   and a.event not like 'Streams AQ:%'

--   and a.sid <> (select sid from v$mystat where rownum = 1)

-- group by program

-- having count(*) > 1

-- order by count(*) desc

--/

----

----prompt ========================================================

----prompt = 'gc buffer busy' - p1, p2 , count 

----prompt ========================================================

----select p1, p2, count(*) cnt

----  from gv$session

---- where event = 'gc buffer busy'

---- group by p1, p2

---- order by cnt desc

----/

--prompt ========================================================

--prompt = 'gv$resource ' - MAX_UTILIZATION > 0.8 * INITIAL_ALLOCATION

--prompt ========================================================

--set linesize 150 pagesize 100 trimspool on

--col RESOURCE_NAME format a17

--select 

--'[resource]'||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') dt, INST_ID,RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,INITIAL_ALLOCATION,LIMIT_VALUE

--from gv$resource_limit  

--where   MAX_UTILIZATION > 

--0.8*to_number( decode(INITIAL_ALLOCATION,' UNLIMITED','99999999999',INITIAL_ALLOCATION),'999999999999') 

--and RESOURCE_NAME not in ('ges_cache_ress','ges_big_msgs','gcs_shadows','gcs_resources')

--order by INST_ID asc 

--/

prompt ========================================================

prompt = Session Count.

prompt ========================================================

set linesize 150 pagesize 100 trimspool on

col dt format a29

select '[ses_cnt]'||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') dt,

       inst_id,

       sum(decode(status,'ACTIVE',1))-1 "Active",

       count(*) "Total"

  from gv$session

 where type <> 'BACKGROUND'

    or username is not null

 group by inst_id

 order by inst_id asc

/

--

--prompt ========================================================

--prompt = Insert Session Wait

--prompt ========================================================

--col DB      format 99

--col sid     format 999999

--col serial# format 99999

--col process format a11

--col event   format a30

--col p       format a20

--col W       format 999

--col program format a15

--col C       format a2

--col blkSid  format 99999

--col object_name format a20

--col spid format a6

--set linesize 170 pagesize 100 trimspool on

--

--select 

--       a.inst_id "DB",

--       a.sid, a.serial#, 

--       (select p.spid from gv$process p where (a.inst_id = p.inst_id) and (a.paddr = p.addr))  spid, a.status,a.event,a.process as CLI_SPID,

----       decode(a.event,'enqueue',

----              decode(chr(bitand(a.p1,-16777216)/16777215)||chr(bitand(a.p1, 16711680)/65535),'TX',

----              chr(bitand(a.p1,-16777216)/16777215)||chr(bitand(a.p1, 16711680)/65535)||to_char( bitand(a.p1, 65535) )||','||

----              'usn.slot:'||bitand(a.p2, 4294901760)/65536||'.'||bitand(a.p2, 65535)||','||'wrap:'||a.p3,

----              a.p1||','||a.p2||','||a.p3),a.p1||','||a.p2||','||a.p3) P,

--       a.seconds_in_wait W, 

--       substr(a.program,1,15) program, 

--       decode(a.command,3,'S',2,'I',6,'U',7,'D',command) C,

--       a.sql_id,

--       substr((SELECT OBJECT_NAME FROM DBA_OBJECTS C WHERE C.OBJECT_ID = ROW_WAIT_OBJ# ),1,20) object_name,

--       a.blocking_session blkSid,

--       substr((SELECT NAME FROM V$LATCH WHERE LATCH# = p2 and event = 'latch free' ),1,10) LATCH_NAME

--  from gv$session a

-- where a.event not like 'SQL*Net message from client%'

----   and a.event not like 'rdbms ipc%'

----   and a.event not like 'smon timer'

----   and a.event not like 'pmon timer'

----   and a.event not like '%slave wait%'

----   and a.event not like 'lock manager wait for remote message'

----   and a.event not like 'PX Deq%'

----   and a.event not like 'null event'

----   and a.event not like 'gcs remote message'

----   and a.event not like 'ges remote message'

----   and a.event not like 'wakeup time manager'

----   and a.event not like 'Queue Monitor%Wait'

----   and a.event not like 'DIAG idle wait'

----   and a.event not like 'Streams AQ:%'

--   and a.sid <> (select sid from v$mystat where rownum = 1)

--   and a.COMMAND = 2

--   and a.WAIT_TIME = 0 and a.SECONDS_IN_WAIT > 5

---- order by a.inst_id, a.event, a.sid

--/

--

--prompt ========================================================

--prompt =  DFS lock handle

--prompt ========================================================

--col DB      format 99

--col inst format 99

--col b_inst format 99

--col sid     format 999999

--col blk_sid     format 999999

--col serial# format 99999

--col blk_seri format 99999

--col process format a11

--col Mode   format a4

--col P2TEXT format a6

--col P3TEXT format a6

--col username format a9

--col W       format 999

--col program format a15

--col machine format a10

--col blkSid  format 99999

--col object_name format a20

--col spid format a6

--col dt format a29

--set linesize 170 pagesize 100 trimspool on

--

--select '[DFSlock]'||to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') dt, inst_id as inst,chr(to_char(bitand(p1,-16777216))/16777215)||

--      chr(to_char(bitand(p1, 16711680))/65535) "Lock",

--      to_char( bitand(p1, 65535) )    "Mode",  P2TEXT,        P2,     P3TEXT, P3,

--sid,serial#,username,program,sql_id

--,BLOCKING_INSTANCE as b_inst,BLOCKING_SESSION as blk_sid

----,BLOCKING_SESSION_STATUS,PREV_SQL_ID, ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#

--FROM gv$session

--WHERE event = 'DFS lock handle'

--/

--

--prompt ========================================================

--prompt = Global Enqueue 

--prompt ========================================================

--set numwidth 5

--col inst format 99

--column state format a8 tru;

--column event format a25 tru;

--select dl.inst_id as inst, s.sid, s.serial#,p.spid,s.username,s.program, dl.resource_name1, 

--decode(substr(dl.grant_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',

--'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',

--'KJUSEREX','Exclusive',request_level) as grant_level,

--decode(substr(dl.request_level,1,8),'KJUSERNL','Null','KJUSERCR','Row-S (SS)',

--'KJUSERCW','Row-X (SX)','KJUSERPR','Share','KJUSERPW','S/Row-X (SSX)',

--'KJUSEREX','Exclusive',request_level) as request_level, 

--decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening',

--'KJUSERCA','Canceling','KJUSERCV','Converting') as state,

-- sw.event, sw.seconds_in_wait W

--from gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw

--where blocker = 1

--and (dl.inst_id = p.inst_id and dl.pid = p.spid)

--and (p.inst_id = s.inst_id and p.addr = s.paddr)

--and (s.inst_id = sw.inst_id and s.sid = sw.sid)

--order by sw.seconds_in_wait desc

--/

--prompt ========================================================

--prompt = LOCK Blocking

--prompt ========================================================

--set verify off linesize 100

--col username format a10

--col sid format 999

--col lock_type format a18

--col mode_held format a11

--col mode_requested format a10

--col lock_id1 format a8

--col lock_id2 format a8

--select /*+ rule */ a.sid,

--decode(a.type,

--'MR', 'Media Recovery',

--'RT', 'Redo Thread',

--'UN', 'User Name',

--'TX', 'Transaction',

--'TM', 'DML',

--'UL', 'PL/SQL User Lock',

--'DX', 'Distributed Xact',

--'CF', 'Control File',

--'IS', 'Instance State',

--'FS', 'File Set',

--'IR', 'Instance Recovery',

--'ST', 'Disk Space TranX',

--'IR', 'Instance Recovery',

--'ST', 'Disk Space TranX',

--'TS', 'Temp Segment',

--'IV', 'Lib Cache Invalid',

--'LS', 'Log Start/Switch',

--'RW', 'Row Wait',

--'SQ', 'Sequence Number',

--'TE', 'Extend Table',

--'TT', 'Temp Table',

--a.type) lock_type,

--decode(a.lmode,

--0, 'None', /* Mon Lock equivalent */

--1, 'Null', /* N */

--2, 'Row-S (SS)', /* L */

--3, 'Row-X (SX)', /* R */

--4, 'Share', /* S */

--5, 'S/Row-X (SSX)', /* C */

--6, 'Exclusive', /* X */

--to_char(a.lmode)) mode_held,

--decode(a.request,

--0, 'None', /* Mon Lock equivalent */

--1, 'Null', /* N */

--2, 'Row-S (SS)', /* L */

--3, 'Row-X (SX)', /* R */

--4, 'Share', /* S */

--5, 'S/Row-X (SSX)', /* C */

--6, 'Exclusive', /* X */

--to_char(a.request)) mode_requested,

--to_char(a.id1) lock_id1, to_char(a.id2) lock_id2, BLOCK

--from v$lock a

--where a.type NOT IN ( 'MR','RT' )

--and (id1,id2) in

--( select b.id1, b.id2 from v$lock b

--where b.id1=a.id1

--and b.id2=a.id2

--and b.request > 0 )

--/

required mirror size 계산

================

SET SERVEROUTPUT ON

DECLARE

   v_num_disks    NUMBER;

   v_group_number   NUMBER;

   v_max_total_mb   NUMBER;

   v_required_free_mb   NUMBER;

   v_usable_mb      NUMBER;

   v_cell_usable_mb   NUMBER;

   v_one_cell_usable_mb   NUMBER;   

   v_enuf_free      BOOLEAN := FALSE;

   v_enuf_free_cell   BOOLEAN := FALSE;

   v_req_mirror_free_adj_factor   NUMBER := 1.10;

   v_req_mirror_free_adj         NUMBER := 0;

   v_one_cell_req_mir_free_mb     NUMBER  := 0;

   v_disk_desc      VARCHAR(10) := 'SINGLE';

   v_offset      NUMBER := 50;

   v_db_version   VARCHAR2(8);

   v_inst_name    VARCHAR2(1);

BEGIN

   SELECT substr(version,1,8), substr(instance_name,1,1)    INTO v_db_version, v_inst_name    FROM v$instance;

   IF v_inst_name <> '+' THEN

      DBMS_OUTPUT.PUT_LINE('ERROR: THIS IS NOT AN ASM INSTANCE!  PLEASE LOG ON TO AN ASM INSTANCE AND RE-RUN THIS SCRIPT.');

      GOTO the_end;

   END IF;

    DBMS_OUTPUT.PUT_LINE('------ DISK and CELL Failure Diskgroup Space Reserve Requirements  ------');

    DBMS_OUTPUT.PUT_LINE(' This procedure determines how much space you need to survive a DISK or CELL failure. It also shows the usable space ');

    DBMS_OUTPUT.PUT_LINE(' available when reserving space for disk or cell failure.  ');

   DBMS_OUTPUT.PUT_LINE(' Please see MOS note 1551288.1 for more information.  ');

   DBMS_OUTPUT.PUT_LINE('.  .  .');      

    DBMS_OUTPUT.PUT_LINE(' Description of Derived Values:');

    DBMS_OUTPUT.PUT_LINE(' One Cell Required Mirror Free MB : Required Mirror Free MB to permit successful rebalance after losing largest CELL regardless of redundancy type');

    DBMS_OUTPUT.PUT_LINE(' Disk Required Mirror Free MB     : Space needed to rebalance after loss of single or double disk failure (for normal or high redundancy)');

    DBMS_OUTPUT.PUT_LINE(' Disk Usable File MB              : Usable space available after reserving space for disk failure and accounting for mirroring');

    DBMS_OUTPUT.PUT_LINE(' Cell Usable File MB              : Usable space available after reserving space for SINGLE cell failure and accounting for mirroring');

   DBMS_OUTPUT.PUT_LINE('.  .  .');   

   IF v_db_version = '11.2.0.3' THEN

      v_req_mirror_free_adj_factor := 1.10;

      DBMS_OUTPUT.PUT_LINE('ASM Version: 11.2.0.3');

   ELSE

      v_req_mirror_free_adj_factor := 1.5;

      DBMS_OUTPUT.PUT_LINE('ASM Version: '||v_db_version||'  - WARNING DISK FAILURE COVERAGE ESTIMATES HAVE NOT BEEN VERIFIED ON THIS VERSION!');   

   END IF;

   DBMS_OUTPUT.PUT_LINE('.  .  .');      

   FOR dg IN (SELECT name, type, group_number, total_mb, free_mb, required_mirror_free_mb FROM v$asm_diskgroup ORDER BY name) LOOP

      v_enuf_free := FALSE;

     v_req_mirror_free_adj := dg.required_mirror_free_mb * v_req_mirror_free_adj_factor;

      -- Find largest amount of space allocated to a cell   

      SELECT sum(disk_cnt), max(max_total_mb), max(sum_total_mb)*v_req_mirror_free_adj_factor

     INTO v_num_disks, v_max_total_mb, v_one_cell_req_mir_free_mb

      FROM (SELECT count(1) disk_cnt, max(total_mb) max_total_mb, sum(total_mb) sum_total_mb 

      FROM v$asm_disk 

     WHERE group_number = dg.group_number 

     GROUP BY failgroup);     

      -- Eighth Rack

      IF dg.type = 'NORMAL' THEN

         -- Eighth Rack

         IF (v_num_disks < 36) THEN

            -- Use eqn: y = 1.21344 x+ 17429.8

            v_required_free_mb :=  1.21344 * v_max_total_mb + 17429.8;

            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;

         -- Quarter Rack

         ELSIF (v_num_disks >= 36 AND v_num_disks < 84) THEN 

            -- Use eqn: y = 1.07687 x+ 19699.3

            v_required_free_mb := 1.07687 * v_max_total_mb + 19699.3;

            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;

         -- Half Rack

         ELSIF (v_num_disks >= 84 AND v_num_disks < 168) THEN 

            -- Use eqn: y = 1.02475 x+53731.3

            v_required_free_mb := 1.02475 * v_max_total_mb + 53731.3;

            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;

         -- Full rack is most conservative, it will be default

         ELSE

            -- Use eqn: y = 1.33333 x+83220.

            v_required_free_mb := 1.33333 * v_max_total_mb + 83220;

            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;      

         END IF;

         -- DISK usable file MB

         v_usable_mb := ROUND((dg.free_mb - v_required_free_mb)/2);

         v_disk_desc := 'ONE disk';

         -- CELL usable file MB

         v_cell_usable_mb := ROUND( (dg.free_mb - v_one_cell_req_mir_free_mb)/2 );

         v_one_cell_usable_mb := v_cell_usable_mb;

      ELSE

         -- HIGH redundancy

         -- Eighth Rack

         IF (v_num_disks <= 18) THEN

            -- Use eqn: y = 4x + 0

            v_required_free_mb :=  4.0 * v_max_total_mb;

            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;

         -- Quarter Rack

         ELSIF (v_num_disks > 18 AND v_num_disks <= 36) THEN 

            -- Use eqn: y = 3.87356 x+417692.

            v_required_free_mb := 3.87356 * v_max_total_mb + 417692;

            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;

         -- Half Rack

         ELSIF (v_num_disks > 36 AND v_num_disks <= 84) THEN 

            -- Use eqn: y = 2.02222 x+56441.6

            v_required_free_mb := 2.02222 * v_max_total_mb + 56441.6;

            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;

         -- Full rack is most conservative, it will be default

         ELSE

            -- Use eqn: y = 2.14077 x+54276.4

            v_required_free_mb := 2.14077 * v_max_total_mb + 54276.4;

            IF dg.free_mb > v_required_free_mb THEN v_enuf_free := TRUE; END IF;      

         END IF;

         -- DISK usable file MB

         v_usable_mb := ROUND((dg.free_mb - v_required_free_mb)/3);      

         v_disk_desc := 'TWO disks';   

         -- CELL usable file MB

         v_one_cell_usable_mb := ROUND( (dg.free_mb - v_one_cell_req_mir_free_mb)/3 );

      END IF;

      DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------');

      DBMS_OUTPUT.PUT_LINE('DG Name: '||LPAD(dg.name,v_offset-9));

      DBMS_OUTPUT.PUT_LINE('DG Type: '||LPAD(dg.type,v_offset-9));

      DBMS_OUTPUT.PUT_LINE('Num Disks: '||LPAD(TO_CHAR(v_num_disks),v_offset-11));

      DBMS_OUTPUT.PUT_LINE('Disk Size MB: '||LPAD(TO_CHAR(v_max_total_mb,'999,999,999,999'),v_offset-14));  

      DBMS_OUTPUT.PUT_LINE('.  .  .');

      DBMS_OUTPUT.PUT_LINE('DG Total MB: '||LPAD(TO_CHAR(dg.total_mb,'999,999,999,999'),v_offset-13));

      DBMS_OUTPUT.PUT_LINE('DG Used MB: '||LPAD(TO_CHAR(dg.total_mb - dg.free_mb,'999,999,999,999'),v_offset-12));

      DBMS_OUTPUT.PUT_LINE('DG Free MB: '||LPAD(TO_CHAR(dg.free_mb,'999,999,999,999'),v_offset-12));

      DBMS_OUTPUT.PUT_LINE('.  .  .');     

      DBMS_OUTPUT.PUT_LINE('One Cell Required Mirror Free MB: '||LPAD(TO_CHAR(ROUND(v_one_cell_req_mir_free_mb),'999,999,999,999'),v_offset-34));

      DBMS_OUTPUT.PUT_LINE('.  .  .');          

      DBMS_OUTPUT.PUT_LINE('Disk Required Mirror Free MB: '||LPAD(TO_CHAR(ROUND(v_required_free_mb),'999,999,999,999'),v_offset-30));

      DBMS_OUTPUT.PUT_LINE('.  .  .');          

      DBMS_OUTPUT.PUT_LINE('Disk Usable File MB: '||LPAD(TO_CHAR(ROUND(v_usable_mb),'999,999,999,999'),v_offset-21));   

      DBMS_OUTPUT.PUT_LINE('Cell Usable File MB: '||LPAD(TO_CHAR(ROUND(v_one_cell_usable_mb),'999,999,999,999'),v_offset-21));   

      DBMS_OUTPUT.PUT_LINE('.  .  .');

      IF v_enuf_free THEN 

         DBMS_OUTPUT.PUT_LINE('Enough Free Space to Rebalance after loss of '||v_disk_desc||': PASS');

      ELSE

         DBMS_OUTPUT.PUT_LINE('Enough Free Space to Rebalance after loss of '||v_disk_desc||': FAIL');

      END IF;   

     IF dg.type = 'NORMAL' THEN

        -- Calc Free Space for Rebalance Due to Cell Failure

        IF v_req_mirror_free_adj < dg.free_mb THEN 

          DBMS_OUTPUT.PUT_LINE('Enough Free Space to Rebalance after loss of ONE cell: PASS');

        ELSE

          DBMS_OUTPUT.PUT_LINE('Enough Free Space to Rebalance after loss of ONE cell: WARNING (cell failure is very rare)');

        END IF;         

   ELSE

        -- Calc Free Space for Rebalance Due to Single Cell Failure

        IF v_one_cell_req_mir_free_mb < dg.free_mb THEN 

          DBMS_OUTPUT.PUT_LINE('Enough Free Space to Rebalance after loss of ONE cell: PASS');

        ELSE

          DBMS_OUTPUT.PUT_LINE('Enough Free Space to Rebalance after loss of ONE cell: WARNING (cell failure is very rare and high redundancy offers ample protection already)');

        END IF;

   END IF;

   END LOOP;

   <<the_end>>

   DBMS_OUTPUT.PUT_LINE('.  .  .');  

   DBMS_OUTPUT.PUT_LINE('Script completed.');

END;

/

수행결과

==================================================

------ DISK and CELL Failure Diskgroup Space Reserve Requirements  ------

This procedure determines how much space you need to survive a DISK or CELL

failure. It also shows the usable space

available when reserving space for disk or cell failure.

Please see MOS note 1551288.1 for more information.

.  .  .

Description of Derived Values:

One Cell Required Mirror Free MB : Required Mirror Free MB to permit successful

rebalance after losing largest CELL regardless of redundancy type

Disk Required Mirror Free MB     : Space needed to rebalance after loss of

single or double disk failure (for normal or high redundancy)

Disk Usable File MB              : Usable space available after reserving space

for disk failure and accounting for mirroring

Cell Usable File MB              : Usable space available after reserving space

for SINGLE cell failure and accounting for mirroring

.  .  .

ASM Version: 12.2.0.1  - WARNING DISK FAILURE COVERAGE ESTIMATES HAVE NOT BEEN

VERIFIED ON THIS VERSION!

.  .  .

-------------------------------------------------------------------------

DG Name:                                      DATA

DG Type:                                    EXTERN

Num Disks:                                      10

Disk Size MB:                               20,480

.  .  .

DG Total MB:                               204,800

DG Used MB:                                147,799

DG Free MB:                                 57,001

.  .  .

One Cell Required Mirror Free MB:           30,720

.  .  .

Disk Required Mirror Free MB:               81,920

.  .  .

Disk Usable File MB:                        -8,306

Cell Usable File MB:                         8,760

.  .  .

Enough Free Space to Rebalance after loss of TWO disks: FAIL

Enough Free Space to Rebalance after loss of ONE cell: PASS

.  .  .

Script completed.

PL/SQL procedure successfully completed.

대부분 구글링해보면 12c 기준으로 포스팅 되어 있어서 작성해봄..

EM 13c 사용시 자동 Logout 안되고 싶다면..

이전 버전인 12c 는 default 45분 이었음..

- The page has expired. Click OK to continue 메세지 계속 뜰때 Timeout 안되게 하는 방법

1. 설정 확인 (default 값은 없음)

[[oracle:/home/oracle]#emctl get property -name oracle.sysman.eml.maxInactiveTime -sysman_pwd [암호]
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Value for property oracle.sysman.eml.maxInactiveTime for oms All Management Servers is null

2. 설정 (logout 안되게 -1로 바꿈)
[oracle:/home/oracle]#emctl set property -name oracle.sysman.eml.maxInactiveTime -value -1 -sysman_pwd [암호]
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Property oracle.sysman.eml.maxInactiveTime has been set to value -1 for all Management Servers
OMS restart is not required to reflect the new property value

리스타트 필요없다고 했는데 중간에 logout 됐다..^^

3. 설정확인

[oracle:/home/oracle]#emctl get property -name oracle.sysman.eml.maxInactiveTime -sysman_pwd [암호]
Oracle Enterprise Manager Cloud Control 13c Release 2 
Copyright (c) 1996, 2016 Oracle Corporation.  All rights reserved.
Value for property oracle.sysman.eml.maxInactiveTime at Global level is -1

ps. Dynamic 값이여서 재기동 하지 않아도 될것 같았는데 설정후에도 log out 됨..-_-;

    메뉴얼에 기재된 대로 설정한 후에는 재기동 하는게 제일 확실한듯..

   그럴려면 왜 Dynamic 이라고 Check 해 놨는지 이해불가..

각 버전별 CRS 로그 경로

10g: 

<CRS_HOME>/log/<nodename>/crsd 

11g: 

su - grid 

$ORACLE_HOME/log/<nodename>/crsd 

12c(12.1.0.1) 

su - grid 

$ORACLE_HOME/log/<nodename>/crsd 

12c(12.1.0.1 이상) 

su - grid 

cd $GRID_BASE/diag/crs/<nodename>/crs/trace 

12.2.0.1 부터 alertlog 의 날짜 기록 포맷이 약간 바뀌었다. 옛날 버전으로 보고 싶을 때는 아래와 같이 하면 된다.

UTC time 은 ISO8601 국제 표준이다.

NAME                                 TYPE VALUE                          ISDEFAULT ISSES ISSYS_MOD

------------------------------ ---------- ------------------------------ --------- ----- ---------

uniform_log_timestamp_format            1 TRUE                           FALSE     FALSE IMMEDIATE

동적으로 바로 적용가능한 파라미터임.

SQL> ALTER SYSTEM SET uniform_log_timestamp_format=FALSE ; 

예)

2017-05-31T14:06:48.190946+09:00

Starting background process CJQ0

Completed: ALTER DATABASE OPEN

2017-05-31T14:06:48.215043+09:00

CJQ0 started with pid=38, OS id=1596 

2017-05-31T14:07:03.520965+09:00     <<<<<<--- 12.2 default 방식

Wed May 31 14:10:47 2017

ALTER SYSTEM SET uniform_log_timestamp_format=FALSE SCOPE=BOTH;

Wed May 31 14:11:00 2017                <<<<<<--- pre 12.2 방식

Thread 1 advanced to log sequence 13 (LGWR switch)

  Current log# 1 seq# 13 mem# 0: /oracle/oradata/ORA12/redo01.log

2017-05-31T14:11:27.982779+09:00

ALTER SYSTEM SET uniform_log_timestamp_format=TRUE SCOPE=BOTH;

2017-05-31T14:11:47.020188+09:00

Thread 1 advanced to log sequence 14 (LGWR switch)

  Current log# 2 seq# 14 mem# 0: /oracle/oradata/ORA12/redo02.log

 12.2 version [Background Processes]

SELECT VALUE 
FROM   V$PARAMETER 
WHERE  NAME='cpu_count';

2016. 6.12 현재 무슨 이유에서 인지 공개적으로 Download 가 되지 않는다. 내부 Internal 버전이었던듯...빛의 속도로 삭제되었다.

12.2..0.1 용 DBBP 최초 버전이 나왔다. 아직 정식버전은 아니거 같고 7월 공식 Release 날짜에 안정적인 버전이 나올듯..

Patch 25793908 - Database Proactive Bundle Patch 12.2.0.1.170516

적용 테스트

[oracle:/home/oracle]#opatch version

OPatch Version: 12.2.0.1.9

[oracle:/home/oracle]#unzip p25793908_122010_Linux-x86-64.zip

cd /home/oracle/25793908/25862693

[oracle:/home/oracle/25793908/25862693]#opatch apply -local

Oracle Interim Patch Installer version 12.2.0.1.9

Copyright (c) 2017, Oracle Corporation.  All rights reserved.

Oracle Home       : /oracle/product/12.2.0.1

Central Inventory : /oracle/oraInventory

   from           : /oracle/product/12.2.0.1/oraInst.loc

OPatch version    : 12.2.0.1.9

OUI version       : 12.2.0.1.4

Log file location : /oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2017-05-26_12-42-03PM_1.log

Verifying environment and performing prerequisite checks...

OPatch continues with these patches:   25862693  

Do you want to proceed? [y|n]

y

User Responded with: Y

All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/oracle/product/12.2.0.1')

Is the local system ready for patching? [y|n]

y

User Responded with: Y

Backing up files...

Applying interim patch '25862693' to OH '/oracle/product/12.2.0.1'

Patching component oracle.oracore.rsf, 12.2.0.1.0...

Patching component oracle.rdbms.deconfig, 12.2.0.1.0...

Patching component oracle.ctx, 12.2.0.1.0...

Patching component oracle.rdbms.rsf, 12.2.0.1.0...

Patching component oracle.rdbms.rman, 12.2.0.1.0...

Patching component oracle.rdbms, 12.2.0.1.0...

Patching component oracle.rdbms.dbscripts, 12.2.0.1.0...

Patch 25862693 successfully applied.

Log file location: /oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2017-05-26_12-42-03PM_1.log

OPatch succeeded.

[oracle:/home/oracle/25793908/25862693]#opatch lsinv

Oracle Interim Patch Installer version 12.2.0.1.9

Copyright (c) 2017, Oracle Corporation.  All rights reserved.

Oracle Home       : /oracle/product/12.2.0.1

Central Inventory : /oracle/oraInventory

   from           : /oracle/product/12.2.0.1/oraInst.loc

OPatch version    : 12.2.0.1.9

OUI version       : 12.2.0.1.4

Log file location : /oracle/product/12.2.0.1/cfgtoollogs/opatch/opatch2017-05-26_12-43-59PM_1.log

Lsinventory Output file location : /oracle/product/12.2.0.1/cfgtoollogs/opatch/lsinv/lsinventory2017-05-26_12-43-59PM.txt

--------------------------------------------------------------------------------

Local Machine Information::

Hostname: 12c

ARU platform id: 226

ARU platform description:: Linux x86-64

Installed Top-level Products (1): 

Oracle Database 12c                                                  12.2.0.1.0

There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch  25862693     : applied on Fri May 26 12:42:24 KST 2017

Unique Patch ID:  21259009

Patch description:  "DATABASE BUNDLE PATCH: 12.2.0.1.170516 (25862693)"

   Created on 9 May 2017, 00:04:41 hrs PST8PDT

   Bugs fixed:

     23026585, 24336249, 24929210, 24942749, 25036474, 25110233, 25410877

     25417050, 25427662, 25459958, 25547901, 25569149, 25600342, 25600421

     25606091, 25655390, 25662088, 24385983, 24923215, 25099758, 25429959

     25662101, 25728085, 25823754

--------------------------------------------------------------------------------

OPatch succeeded.

[oracle:/home/oracle/25793908/25862693]#sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Fri May 26 12:44:24 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

idle> connect / as sysdba

Connected to an idle instance.

idle> startup

ORACLE instance started.

Total System Global Area 1660944384 bytes

Fixed Size                  8621376 bytes

Variable Size            1056965312 bytes

Database Buffers          587202560 bytes

Redo Buffers                8155136 bytes

Database mounted.

Database opened.

idle> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

[oracle:/home/oracle/25793908/25862693]#cd

[oracle:/home/oracle]#cdo

[oracle:/oracle/product/12.2.0.1]#cd OPatch

[oracle:/oracle/product/12.2.0.1/OPatch]#ls

README.txt  datapatch      emdpatch.pl  modules  opatch.bat     opatchauto      operr             oplan          scripts

auto        datapatch.bat  jlib         ocm      opatch.pl      opatchauto.cmd  operr.bat         oracle_common  version.txt

config      docs           jre          opatch   opatch_env.sh  opatchprereqs   operr_readme.txt  plugins

[oracle:/oracle/product/12.2.0.1/OPatch]#./datapatch -verbose

SQL Patching tool version 12.2.0.1.0 Production on Fri May 26 12:45:15 2017

Copyright (c) 2012, 2017, Oracle.  All rights reserved.

Log file for this invocation: /oracle/cfgtoollogs/sqlpatch/sqlpatch_3678_2017_05_26_12_45_15/sqlpatch_invocation.log

Connecting to database...OK

Bootstrapping registry and package to current versions...done

Determining current state...done

Current state of SQL patches:

Bundle series DBBP:

  ID 170516 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...

Installation queue:

  Nothing to roll back

  The following patches will be applied:

    25862693 (DATABASE BUNDLE PATCH 12.2.0.1.170516)

Installing patches...

Patch installation complete.  Total patches installed: 1

Validating logfiles...

Patch 25862693 apply: SUCCESS

  logfile: /oracle/cfgtoollogs/sqlpatch/25862693/21259009/25862693_apply_ORA12_2017May26_12_45_24.log (no errors)

SQL Patching tool complete on Fri May 26 12:45:42 2017

[oracle:/oracle/product/12.2.0.1/OPatch]#ss

SQL*Plus: Release 12.2.0.1.0 Production on Fri May 26 12:47:44 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SET LINESIZE 200

SET LINESIZE 400

COLUMN action_time FORMAT A20

COLUMN action FORMAT A10

COLUMN status FORMAT A10

COLUMN description FORMAT A40

COLUMN version FORMAT A10

COLUMN bundle_series FORMAT A10

SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time, action, status, description, version, patch_id, bundle_series  FROM   sys.dba_registry_sqlpatch ORDER by action_time;

ACTION_TIME          ACTION     STATUS     DESCRIPTION                              VERSION      PATCH_ID BUNDLE_SER

-------------------- ---------- ---------- ---------------------------------------- ---------- ---------- ----------

26-MAY-2017 12:45:42 APPLY      SUCCESS    DATABASE BUNDLE PATCH 12.2.0.1.170516    12.2.0.1     25862693 DBBP

Tablespace name에 공백이 들어갔는지 확인하는 방법

SQL> select '"'||name||'"' from sys.ts$;

만약 공백이 들어있다면 ORA-959 에러후 drop 되지 않는다.

SQL> drop tablespace 'TBS_TRC ' including contents and datafiles;

drop tablespace 'TBS_TRC ' including contents and datafiles

  *

ERROR at line 1:

ORA-02216: tablespace name expected

SQL> drop tablespace "TBS_TRC " including contents and datafiles;

drop tablespace "TBS_TRC " including contents and datafiles

*

ERROR at line 1:

ORA-00959: tablespace 'TBS_TRC ' does not exist

=========================================================

해결책

-> rename 후 drop 해 볼것.

SQL> ALTER TABLESPACE "TBS_TRC " RENAME TO "TESTDROP";

SQL> drop tablespace testdrop including contents and datafiles;

oracle parameter session 값 도출 공식

processes 의 설정값에 따라 maximum session 수가 결정된다.

- 11g 이전

Sessions=(1.1 * processes) + 5 

Transactions = (1.1 * Sessions)

- 11g 이후 

Sessions=(1.5 * processes) + 22 (-> 동시 유저 및 백그라운드 프로세스, 대략 10%정도의 반복적인 세션을 고려한 값) 

Transactions = (1.1 * Sessions)

- 12c  

Sessions=(1.5 * processes) + 22 (-> 동시 유저 및 백그라운드 프로세스, 대략 10%정도의 반복적인 세션을 고려한 값) 

Transactions = (1.1 * Sessions)

최대값 설정은 1 에서 65536 까지 가능하다.