'1. IT Story'에 해당되는 글 397건

set echo off

set lines 132

set pages 5000

set scan on

set verify off

set feedback off

set head on

prompt

accept owner prompt 'Table owner: '

accept table_name prompt 'Table name: '

col owner for a15

col synonym_name for a15

col table_owner for a15

col db_link for a15

col table_name for a30

col tablespace_name for a15

col mb for 999,990

col num_rows for 999,999,990

col blocks for 999,999,990

col empty_blocks for 999,990

col avg_space for 9,990

col chain_cnt for 990

col avg_row_len for 999,990

col avg_col_len for 990

col column_name for a30

col nullable for a4

col num_distinct for 999,999,990

col density for 9.999999

col index_name for a30

col uniq for a4

col blev for 90

col leaf_blocks for 9,999,990

col distinct_keys for 999,999,990

col key_lblocks for 99,990

col key_dblocks for 9,999,990

col clustering_factor for 999,999,990

col column_position format 999

col col for a25

col column_length for 990

col num_buckets for 990

col num_nulls for 999,999,990

select  t.table_name,

        t.tablespace_name,

        t.num_rows,

        t.blocks,

        s.bytes/1048576 mb,

        t.empty_blocks,

        t.chain_cnt,

        t.avg_row_len,

        t.last_analyzed

from    dba_tables t, dba_segments s

where   t.owner=upper(NVL('&&owner',USER)) and t.table_name=upper('&&table_name')

  and   s.owner=t.owner and s.segment_name=t.table_name

  and   s.segment_type <> 'TABLE PARTITION'

union all

select  p.partition_name||' (P)' table_name,

        p.tablespace_name,

        p.num_rows,

        p.blocks,

        s.bytes/1048576 mb,

        p.empty_blocks,

        p.chain_cnt,

        p.avg_row_len,

        p.last_analyzed

from    dba_segments s, dba_tab_partitions p

where   s.segment_type='TABLE PARTITION'

  and   p.table_owner=upper(NVL('&&owner',USER)) and p.table_name=upper('&&table_name')

  and   s.owner=p.table_owner and s.segment_name=p.table_name and s.partition_name=p.partition_name

order by table_name

/

SELECT CASE WHEN EXE_TIME BETWEEN 0 AND 0.9999 THEN '1초 미만' 

              WHEN EXE_TIME BETWEEN 1 AND 2.999 THEN  '1초이상 3초미만'

              WHEN EXE_TIME BETWEEN 3 AND 4.999 THEN  '3초이상 5초미만'

              WHEN EXE_TIME BETWEEN 5 AND 9.999 THEN  '5초이상 10초미만'

              WHEN EXE_TIME BETWEEN 10 AND 19.999 THEN '10초이상 20초미만'

              WHEN EXE_TIME >= 20 THEN '20초이상' END "GBN", 

              COUNT(DISTINCT SQL_ID), SUM(EXECUTIONS) EXE, SUM(ELapsed_time) elasped_time, sum(buffer_gets)

from

(select SQL_TEXT, MODULE,SQL_ID,PARSING_SCHEMA_NAME,

round(sum(Elapsed_time)/1000000/decode(sum(executions),0,1,sum(executions)),2) exe_time,

sum(parse_calls) parse_calls, sum(executions) executions,

sum(Disk_reads) Disk_reads, sum(Buffer_gets) Buffer_gets,

round((sum(Buffer_gets)-sum(Disk_reads))/decode(sum(Buffer_gets),0,1,sum(Buffer_gets)) *100,2) Hit_ratio,

round(sum(cpu_time)/1000000,2) cpu_time, round(sum(Elapsed_time)/1000000,2) Elapsed_time,

trunc(ratio_to_report(sum(Elapsed_time)) over (partition by null) * 100 ,2) "전체비중"

from v$sqlarea

Group by SQL_TEXT, MODULE, SQL_ID,PARSING_SCHEMA_NAME )

group by CASE WHEN EXE_TIME BETWEEN 0 AND 0.9999 THEN '1초 미만' 

              WHEN EXE_TIME BETWEEN 1 AND 2.999 THEN  '1초이상 3초미만'

              WHEN EXE_TIME BETWEEN 3 AND 4.999 THEN  '3초이상 5초미만'

              WHEN EXE_TIME BETWEEN 5 AND 9.999 THEN  '5초이상 10초미만'

              WHEN EXE_TIME BETWEEN 10 AND 19.999 THEN '10초이상 20초미만'

              WHEN EXE_TIME >= 20 THEN '20초이상' END

ORDER BY GBN

-- Example of running the script:

-- sqlplus <userid>/<pw> @schemaCheckOracle.sql

set null "NULL VALUE"

set feedback off

set heading off

set linesize 132 

set pagesize 9999

set echo off

set verify off

col table_name for a30

col column_name for a30

col data_type for a15

col object_type for a20

col constraint_type_desc for a30

--spool schemaCheckOracle.&&schema_name.out

ACCEPT schema_name char prompt 'Enter the Schema Name > '

variable b0 varchar2(50)

exec :b0 := upper('&schema_name');

spool schemaCheckOracle.&&schema_name..out

SET Heading off

SELECT '------ System Info for: '||:b0 

FROM dual;

set heading on

select to_char(sysdate, 'MM-DD-YYYY HH24:MI:SS') "DateTime: " from dual

/

select banner from v$version

/

SET Heading off

SELECT '------ Database Level Supplemental Log Check - 9i and 10g: ' 

FROM dual;

SET Heading on

SELECT SUPPLEMENTAL_LOG_DATA_MIN MIN, SUPPLEMENTAL_LOG_DATA_PK PK, SUPPLEMENTAL_LOG_DATA_UI UI 

FROM V$DATABASE

/ 

select name, log_mode "LogMode", 

supplemental_log_data_min "SupLog: Min", supplemental_log_data_pk "PK",

supplemental_log_data_ui "UI", force_logging "Forced",

supplemental_log_data_fk "FK", supplemental_log_data_all "All",

to_char(created, 'MM-DD-YYYY HH24:MI:SS') "Created"

from v$database

/

select 

platform_name

from v$database

/

set heading off

SELECT '------ Objects stored in Tablespaces with Compression are not supported in the current release of OGG ' 

FROM dual;

SET Heading on

select

TABLESPACE_NAME,

DEF_TAB_COMPRESSION

from DBA_TABLESPACES

where 

DEF_TAB_COMPRESSION <> 'DISABLED';

set heading off

SELECT '------ Distinct Object Types and their Count in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT object_type, count(*) total

FROM all_objects

WHERE OWNER = :b0

GROUP BY object_type

/

SET Heading off

SELECT '------ Distinct Column Data Types and their Count in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT data_type, count(*) total

FROM all_tab_columns

WHERE OWNER = :b0 

GROUP BY data_type

/

SET Heading off

SELECT '------ Tables With No Primary Key or Unique Index in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT distinct(table_name)

FROM all_tables

WHERE owner = :b0 

MINUS

(SELECT obj1.name

FROM SYS.user$ user1,

SYS.user$ user2,

SYS.cdef$ cdef,

SYS.con$ con1,

SYS.con$ con2,

SYS.obj$ obj1,

SYS.obj$ obj2

WHERE user1.name = :b0 

AND cdef.type# = 2 

AND con2.owner# = user2.user#(+)

AND cdef.robj# = obj2.obj#(+)

AND cdef.rcon# = con2.con#(+)

AND obj1.owner# = user1.user#

AND cdef.con# = con1.con#

AND cdef.obj# = obj1.obj#

UNION

SELECT idx.table_name

FROM all_indexes idx

WHERE idx.owner = :b0 

AND idx.uniqueness = 'UNIQUE')

/

SET Heading off

SELECT '------ Tables with no PK or UI and rowsize > 1M in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT distinct(table_name)

FROM all_tab_columns

WHERE owner = :b0

group by table_name

HAVING sum(data_length) > 1000000

MINUS

(SELECT obj1.name

FROM SYS.user$ user1,

SYS.user$ user2,

SYS.cdef$ cdef,

SYS.con$ con1,

SYS.con$ con2,

SYS.obj$ obj1,

SYS.obj$ obj2

WHERE user1.name = :b0 

AND cdef.type# = 2 

AND con2.owner# = user2.user#(+)

AND cdef.robj# = obj2.obj#(+)

AND cdef.rcon# = con2.con#(+)

AND obj1.owner# = user1.user#

AND cdef.con# = con1.con#

AND cdef.obj# = obj1.obj#

UNION

SELECT idx.table_name

FROM all_indexes idx

WHERE idx.owner = :b0 

AND idx.uniqueness = 'UNIQUE')

/

set heading off

SELECT '------ Tables with NOLOGGING setting ' FROM dual;

SELECT '------ This may cause problems with missing data down stream. Schema: ' ||:b0 FROM dual;

set heading on

select owner, table_name, ' ' "PARTITION_NAME", ' ' "PARTITIONING_TYPE", logging from DBA_TABLES

where logging <> 'YES' and OWNER = :b0

union

select owner, table_name, ' ', partitioning_type, DEF_LOGGING "LOGGING" from DBA_part_tables

where DEF_LOGGING != 'YES' and owner = :b0

union

select table_owner, table_name, PARTITION_NAME, ' ', logging from DBA_TAB_PARTITIONS

where logging <> 'YES' and table_owner = :b0

union

select table_owner, table_name, PARTITION_NAME, ' ', logging from DBA_TAB_SUBPARTITIONS

where logging <> 'YES' and table_owner <> :b0

;

set heading off

SELECT '------ Tables with Deferred constraints.Deferred constraints may cause TRANDATA to chose an incorrect Key ' FROM dual;

SELECT '------ Tables with Deferred constraints should be added using KEYCOLS in the trandata statement. Schema: ' ||:b0 FROM dual;

set heading on

SELECT c.TABLE_NAME,

c.CONSTRAINT_NAME,

c.CONSTRAINT_TYPE,

c.DEFERRABLE,

c.DEFERRED,

c.VALIDATED,

c.STATUS,

i.INDEX_TYPE,

c.INDEX_NAME,

c.INDEX_OWNER

FROM dba_constraints c,

dba_indexes i

WHERE

i.TABLE_NAME = c.TABLE_NAME

AND i.OWNER = c.OWNER

AND c.owner = :b0

AND c.DEFERRED = 'DEFERRED';

set heading off

SELECT '------ Tables Defined with Rowsize > 2M in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT table_name, sum(data_length) row_length_over_2M

FROM all_tab_columns

WHERE OWNER = :b0 

GROUP BY table_name

HAVING sum(data_length) > 2000000

/

SET Heading off

SELECT '------ Tables that will Fail Add Trandata (Only an issue for Oracle versions below Oracle 10G) in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT distinct(table_name)

FROM dba_tab_columns

WHERE owner = :b0 

AND column_id > 32

AND table_name in

(SELECT distinct(table_name)

FROM all_tables

WHERE owner = :b0 

MINUS

(SELECT obj1.name

FROM SYS.user$ user1,

SYS.user$ user2,

SYS.cdef$ cdef,

SYS.con$ con1,

SYS.con$ con2,

SYS.obj$ obj1,

SYS.obj$ obj2

WHERE user1.name = :b0 

AND cdef.type# = 2

AND con2.owner# = user2.user#(+)

AND cdef.robj# = obj2.obj#(+)

AND cdef.rcon# = con2.con#(+)

AND obj1.owner# = user1.user#

AND cdef.con# = con1.con#

AND cdef.obj# = obj1.obj#

UNION

SELECT idx.table_name

FROM all_indexes idx

WHERE idx.owner = :b0 

AND idx.uniqueness = 'UNIQUE'))

/

SET Heading off

SELECT '------ Tables With CLOB, BLOB, LONG, NCLOB or LONG RAW Columns in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE

FROM all_tab_columns

WHERE OWNER = :b0 

AND data_type in ('CLOB', 'BLOB', 'LONG', 'LONG RAW', 'NCLOB')

/

SET Heading off

SELECT '------ Tables With Columns of UNSUPPORTED Datatypes in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE

FROM all_tab_columns

WHERE OWNER = :b0 

AND (data_type in ('ORDDICOM', 'BFILE', 'TIMEZONE_REGION', 'BINARY_INTEGER', 'PLS_INTEGER', 'UROWID', 'URITYPE', 'MLSLABEL', 'TIMEZONE_ABBR', 'ANYDATA', 'ANYDATASET', 'ANYTYPE')

or data_type like 'INTERVAL%')

/

SET Heading off

SELECT '----- Tables with Compressed data is not supported - in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT TABLE_NAME, COMPRESSION

FROM all_all_tables

WHERE OWNER = :b0

AND (COMPRESSION = 'ENABLED');

SELECT TABLE_NAME, COMPRESSION

FROM ALL_TAB_PARTITIONS

WHERE TABLE_OWNER = :b0

AND (COMPRESSION = 'ENABLED');

SET Heading off

SELECT '----- Cluster (DML and DDL supported for 9i or later) or Object Tables (DML supported for 10G or later, no DDL) - in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT TABLE_NAME, CLUSTER_NAME, TABLE_TYPE 

FROM all_all_tables

WHERE OWNER = :b0 

AND (cluster_name is NOT NULL or TABLE_TYPE is NOT NULL)

/

SET Heading off

SELECT '------ IOT (Fully support for Oracle 10GR2 (with or without overflows) using GGS 10.4 and higher) - in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT TABLE_NAME, IOT_TYPE, TABLE_TYPE 

FROM all_all_tables

WHERE OWNER = :b0 

AND (IOT_TYPE is not null or TABLE_TYPE is NOT NULL)

/

SET Heading off

SELECT '------ Tables with Domain or Context Indexes - in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT TABLE_NAME, index_name, index_type 

FROM dba_indexes WHERE OWNER = :b0

and index_type = 'DOMAIN'

/

SET Heading off

SELECT '------ Types of Constraints on the Tables in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT DECODE(constraint_type,'P','PRIMARY KEY','U','UNIQUE', 'C', 'CHECK', 'R', 

'REFERENTIAL') constraint_type_desc, count(*) total

FROM all_constraints

WHERE OWNER = :b0 

GROUP BY constraint_type

/

SET Heading off

SELECT '------ Cascading Deletes on the Tables in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT table_name, constraint_name

FROM all_constraints

WHERE OWNER = :b0 and constraint_type = 'R' and delete_rule = 'CASCADE'

/

SET Heading off

SELECT '------ Tables Defined with Triggers in the Schema: '||:b0 

FROM dual;

SET Heading on

SELECT table_name, COUNT(*) trigger_count

FROM all_triggers

WHERE OWNER = :b0 

GROUP BY table_name

/

SET Heading off

SELECT '------ Performance issues - Reverse Key Indexes Defined in the Schema: '||:b0

FROM dual;

col Owner format a10

col TABLE_OWNER format a10

col INDEX_TYPE format a12

SET Heading on

select 

OWNER, 

INDEX_NAME,

INDEX_TYPE, 

TABLE_OWNER,

TABLE_NAME, 

TABLE_TYPE, 

UNIQUENESS,

CLUSTERING_FACTOR,

NUM_ROWS,

LAST_ANALYZED,

BUFFER_POOL

from dba_indexes

where index_type = 'NORMAL/REV'

And OWNER = :b0

/

SET Heading off

SELECT '------ Sequence numbers: '||:b0

FROM dual;

COLUMN SEQUENCE_OWNER FORMAT a15

COLUMN SEQUENCE_NAME FORMAT a30

COLUMN INCR FORMAT 999

COLUMN CYCLE FORMAT A5

COLUMN ORDER FORMAT A5

SET Heading on

SELECT SEQUENCE_OWNER,

SEQUENCE_NAME,

MIN_VALUE,

MAX_VALUE,

INCREMENT_BY INCR,

CYCLE_FLAG CYCLE,

ORDER_FLAG "ORDER",

CACHE_SIZE,

LAST_NUMBER

FROM DBA_SEQUENCES

WHERE SEQUENCE_OWNER=UPPER(:b0)

/

set linesize 132

col "Avg Log Size" format 999,999,999

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

Prompt Table: Frequency of Log Switches by hour and day

SELECT SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),1,5) DAY, 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'00',1,0)),'99') "00", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'01',1,0)),'99') "01", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'02',1,0)),'99') "02", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'03',1,0)),'99') "03", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'04',1,0)),'99') "04", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'05',1,0)),'99') "05", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'06',1,0)),'99') "06", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'07',1,0)),'99') "07", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'08',1,0)),'99') "08", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'09',1,0)),'99') "09", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'10',1,0)),'99') "10", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'11',1,0)),'99') "11", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'12',1,0)),'99') "12", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'13',1,0)),'99') "13", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'14',1,0)),'99') "14", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'15',1,0)),'99') "15", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'16',1,0)),'99') "16", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'17',1,0)),'99') "17", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'18',1,0)),'99') "18", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'19',1,0)),'99') "19", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'20',1,0)),'99') "20", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'21',1,0)),'99') "21", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'22',1,0)),'99') "22", 

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'23',1,0)),'99') "23" 

FROM V$LOG_HISTORY 

GROUP BY SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),1,5) 

order by 1;

SET Heading off

SELECT '------ Summary of log volume processed by day for last 7 days: '

FROM dual;

SET Heading on

select to_char(first_time, 'mm/dd') ArchiveDate,

sum(BLOCKS*BLOCK_SIZE/1024/1024) LOGMB

from v$archived_log

where first_time > sysdate - 7

group by to_char(first_time, 'mm/dd')

order by to_char(first_time, 'mm/dd');

/

SET Heading off

SELECT '------ Summary of log volume processed per hour for last 7 days: ' 

FROM dual;

SET Heading on

select to_char(first_time, 'MM-DD-YYYY') ArchiveDate, 

to_char(first_time, 'HH24') ArchiveHour,

sum(BLOCKS*BLOCK_SIZE/1024/1024) LogMB

from v$archived_log

where first_time > sysdate - 7

group by to_char(first_time, 'MM-DD-YYYY'), to_char(first_time, 'HH24')

order by to_char(first_time, 'MM-DD-YYYY'), to_char(first_time, 'HH24');

/ 

set heading off

select '* This output may be found in file: schemaCheckOracle.&&schema_name..out' from dual

/

spool off

undefine b0

-- exit

select

  'session_cached_cursors'  parameter,

  lpad(value, 5)  value,

  decode(value, 0, '  n/a', to_char(100 * used / value, '990') || '%')  usage

from

  ( select

      max(s.value)  used

    from

      v$statname  n,

      v$sesstat  s

    where

      n.name = 'session cursor cache count' and

      s.statistic# = n.statistic#

  ),

  ( select

      value

    from

      v$parameter

    where

      name = 'session_cached_cursors'

  )

union all

select

  'open_cursors',

  lpad(value, 5),

  to_char(100 * used / value,  '990') || '%'

from

  ( select

      max(sum(s.value))  used

    from

      v$statname  n,

      v$sesstat  s

    where

      n.name in ('opened cursors current', 'session cursor cache count') and

      s.statistic# = n.statistic#

    group by

      s.sid

  ),

  ( select

      value

    from

      v$parameter

    where

      name = 'open_cursors'

  )

set heading off

set feedback off

set pages 1000

set verify off

spool allcompile.sql

SELECT 

'alter '||DECODE(object_type,'PACKAGE BODY',

'PACKAGE',object_type)||' '||owner||'.'||object_name||

' compile'||

DECODE(object_type,'PACKAGE BODY',' body;',';')

FROM dba_objects

WHERE object_type in ('VIEW','FUNCTION','TRIGGER','PROCEDURE','PACKAGE','PACKAGE BODY')

AND status='INVALID'

ORDER BY owner,decode

(object_type,'VIEW','A','FUNCTION','B',

'TRIGGER','C','PROCEDURE','D',

'PACKAGE','E','PACKAGE BODY','F')

,object_name

/

spool off

set lines 200 pages 200

set pagesize 66

set pause on

set verify off

set feed off

clear screen

column sid heading 'sid' format 999

column username heading 'username' format a8

column pgm heading 'program' format a25

column terminal heading 'terminal' format a8

column pga heading 'PGA session memory' format a11

column uga heading 'UGA session memory' format a11

column pga_sum heading 'SUM PGA mem' format a12

column uga_sum heading 'SUM UGA mem' format a12

column pga_max heading 'Max PGA session memory' format a15

column uga_max heading 'Max UGA session memory' format a15

column pga_m_sum heading 'Sum Max PGA session memory' format a11

column uga_m_sum heading 'Sum Max UGA session memory' format a11

spool sess.txt

ttitle '**********< Program Global Area >**********'

ttitle '1. Current pga, uga session memory'

select a.sid, a.username, substr(a.program, 1, 25) as pgm, a.terminal,

max(decode(c.name, 'session pga memory', trunc(value/1000)||'K', 0)) pga,

max(decode(c.name, 'session uga memory', trunc(value/1000)||'K', 0)) uga

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

where a.sid = b.sid

and b.statistic# = c.statistic#

and c.name like 'session%'

group by a.sid, a.username, substr(a.program, 1, 25), a.terminal;

ttitle '2. Sum of current pga, uga session memory'

select 'Current PGA, UGA session memory SUM:' as sum,

sum(decode(c.name, 'session pga memory', trunc(value/1000),0))||'K' pga_sum,

sum(decode(c.name, 'session uga memory', trunc(value/1000),0))||'K' uga_sum

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

where a.sid = b.sid

and b.statistic# = c.statistic#

and c.name like 'session%';

ttitle '3. Max(peak) pga, pga session memory'

select a.sid, a.username, substr(a.program, 1, 25) as pgm, a.terminal,

max(decode(c.name, 'session pga memory max', trunc(value/1000)||'K', 0)) pga_max,

max(decode(c.name, 'session uga memory max', trunc(value/1000)||'K', 0)) uga_max

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

where a.sid = b.sid

and b.statistic# = c.statistic#

and c.name like 'session%'

group by a.sid, a.username, substr(a.program, 1, 25), a.terminal;

ttitle '4. Sum of max(peak) pga, uga session memory'

select 'Max(peak) PGA, UGA session memory SUM:' as sum,

sum(decode(c.name, 'session pga memory max', trunc(value/1000), 0))||'K' pga_m_sum,

sum(decode(c.name, 'session uga memory max', trunc(value/1000), 0))||'K' uga_m_sum

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

where a.sid = b.sid

and b.statistic# = c.statistic#

and c.name like 'session%';

spool off

exit