Index analyzing :
clear columns;
clear breaks;
--***********************************************************(
-- NEW VERSION
--***********************************************************(
-- This script will check indexes to find candidates for rebuilding.
-- Run this script in SQL*Plus as a user with SELECT ANY TABLE
-- privileges.
-- ('PS_CM_ACCTG_LINE',
-- 'PS_BUS_UNIT_TBL_IN',
-- 'PS_BU_ITEMS_INV',
-- 'PS_TRANSACTION_INV',
-- 'PS_JRNL_HEADER',
-- 'PSPRCSRQST',
-- 'PS_IN_DEMAND',
-- 'PS_STOR_LOC_INV',
-- 'PS_SET_CNTRL_REC',
-- 'PS_ITEM_MFG',
-- 'PS_IFS_ITM_STD_TBL',
-- 'PS_IFS_ITM_OVR_TBL',
-- 'PS_MASTER_ITEM_TBL',
-- 'PS_ITM_VENDOR',
-- 'PS_INV_ITEM_UOM',
-- 'PS_REASON_CD');
/* 'PSXLATITEM'); */
/* and TABLE_NAME = '&1'; */
clear columns;
clear breaks;
set linesize 999
set pagesize 999;
column OWNER format a20
column TABLE_NAME format a45
column TABLESPACE_NAME format a20
column SEGMENT_TYPE format a12
column EXTENTS format 9999999
column INITIAL_IN_MB format 999,999
column NEXT_IN_MB format 999,999
column CALCULATED_SIZE format 999999.9
column actual_size format 999999.9
column WASTED_SPACE_MB format 999999.9
column PERCENT_DIFF format 9999.9
SELECT a.owner,
a.table_name,
a.tablespace_name,
a.partitioned,
b.segment_type,
case when round((a.num_rows*a.avg_row_len/1048576),1) < round((b.bytes/1048576),1)
then round((b.bytes/1048576),1)-round((a.num_rows*a.avg_row_len/1048576),1)
else 0
end wasted_space_mb,
case when round((a.num_rows*a.avg_row_len/1048576),1) < round((b.bytes/1048576),1)
then ((round((b.bytes/1048576),1)-round((a.num_rows*a.avg_row_len/1048576),1) ) / round((b.bytes/1048576),1) ) *100
else 0
end percent_diff
FROM dba_tables a,
dba_segments b
WHERE a.owner NOT IN ('SYS',
'SYSTEM',
'DBSNMP',
'OSMMON',
'PERFSTAT',
'CTXSYS',
'MDSYS',
'ORDSYS',
'SYSMAN',
'SQLTXPLAIN',
'WMSYS',
'XBOLGSS',
'XDB')
AND a.last_analyzed is NOT NULL
AND b.owner = a.owner
AND b.segment_name = a.table_name
AND b.segment_type = 'TABLE'
AND (b.bytes/1048576) > 200
AND ((round((b.bytes/1048576),1)-round((a.num_rows*a.avg_row_len/1048576),1) / round((b.bytes/1048576),1) * 100 > 50
AND round((b.bytes/1048576),2)-round((a.num_rows*a.avg_row_len/1048576),2) > 100)
OR round((b.bytes/1048576),2)-round((a.num_rows*a.avg_row_len/1048576),2) > 100)
ORDER BY case when round((a.num_rows*a.avg_row_len/1048576),1) < round((b.bytes/1048576),1)
then round((b.bytes/1048576),1)-round((a.num_rows*a.avg_row_len/1048576),1)
else 0
end DESC,
a.tablespace_name,
a.table_name;
--*****************************************************
-- Index Analysis SQL
--*****************************************************
set serveroutput on size 100000
alter session set ddl_lock_timeout = 600;
prompt
prompt Creating holding table
prompt
drop table jab_hold_indx_stats;
create table jab_hold_indx_stats as select * from index_stats where 1=0;
prompt Done.
prompt
accept v_tableowner prompt 'Enter table owner you wish to analyze (cr=SYSADM): ' default 'SYSADM'
accept v_tablename prompt 'Enter tablename you wish to analyze (cr=Top 20 largest): ' default 'ALL'
column owner format a10;
column index_name format a25;
column index_type format a25;
DECLARE
vOwner dba_indexes.owner%TYPE; /* Index Owner */
vIdxName dba_indexes.index_name%TYPE; /* Index Name */
vIdxType dba_indexes.index_type%TYPE; /* Index Type */
vStatus dba_indexes.status%TYPE; /* Index Status */
vAnalyze VARCHAR2(100); /* String of Analyze Stmt */
vCursor NUMBER; /* DBMS_SQL cursor */
vNumRows INTEGER; /* DBMS_SQL return rows */
vHeight index_stats.height%TYPE; /* Height of index tree */
vLfRows index_stats.lf_rows%TYPE; /* Index Leaf Rows */
vDLfRows index_stats.del_lf_rows%TYPE; /* Deleted Leaf Rows */
vComprRat index_stats.opt_cmpr_count%TYPE; /* Height of index tree */
vDLfPerc NUMBER; /* Del lf Percentage */
vMaxHeight NUMBER; /* Max tree height */
vMaxDel NUMBER; /* Max del lf percentage */
vMaxPctSave NUMBER; /* Max PCT Saved */
vCmprPctSave NUMBER; /* Pct of Space saved with OPT compression */
CURSOR cGetIdx IS
SELECT owner,
index_name,
index_type,
status
FROM dba_indexes
WHERE OWNER = 'SYSADM'
and TABLE_OWNER = '&&v_tableowner'
and INDEX_TYPE <> 'LOB'
and INDEX_NAME NOT IN (SELECT DISTINCT NAME
FROM jab_hold_indx_stats)
and TABLE_NAME = '&&v_tablename';
BEGIN
/* Define maximums. This section can be customized. */
vMaxHeight := 3;
vMaxDel := 20;
vMaxPctSave := 19;
/* For every index, validate structure */
OPEN cGetIdx;
LOOP
FETCH cGetIdx INTO vOwner,vIdxName,vIdxType,vStatus;
EXIT WHEN cGetIdx%NOTFOUND;
IF NOT vIdxType = 'LOB' THEN /* handle case where div by zero */
vAnalyze := ('ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE');
DBMS_OUTPUT.PUT_LINE('Analyzing INDEX ' || vOwner || '.' || vIdxName || ', type= ' || vIdxType || ', status= ' || vStatus);
/* Open DBMS_SQL cursor */
vCursor := DBMS_SQL.OPEN_CURSOR;
/* Set up dynamic string to validate structure */
DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
vNumRows := DBMS_SQL.EXECUTE(vCursor);
/* Close DBMS_SQL cursor */
DBMS_SQL.CLOSE_CURSOR(vCursor);
/* Does index need rebuilding? */
/* If so, then generate command */
BEGIN
SELECT height,lf_rows,del_lf_rows,opt_cmpr_count,opt_cmpr_pctsave INTO vHeight,vLfRows,vDLfRows,vComprRat,vCmprPctSave FROM INDEX_STATS;
EXCEPTION
WHEN NO_DATA_FOUND THEN
vHeight := 0;
vLfRows := 0;
vDLfRows := 0;
vComprRat := 0;
vCmprPctSave :=0;
END;
/* Record the analysis */
INSERT INTO jab_hold_indx_stats (select * from index_stats);
commit;
/* Sleep for 5 seconds */
dbms_lock.sleep(5);
IF vDLfRows = 0 THEN /* handle case where div by zero */
vDLfPerc := 0;
ELSE
vDLfPerc := (vDLfRows / vLfRows) * 100;
END IF;
IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) OR (vCmprPctSave > vMaxPctSave) THEN
DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || ' REBUILD compress '|| vComprRat || ';');
END IF;
/* DBMS_OUTPUT.PUT_LINE(' Index Height = ' || vHeight); */
/* DBMS_OUTPUT.PUT_LINE(' Max Index Height = ' || vMaxHeight); */
/* DBMS_OUTPUT.PUT_LINE(' Leaf Rows = ' || vLfRows); */
/* DBMS_OUTPUT.PUT_LINE(' Deleted Leaf Rows = ' || vDLfRows); */
/* DBMS_OUTPUT.PUT_LINE(' Deleted Leaf PCT = ' || vDLfPerc); */
/* DBMS_OUTPUT.PUT_LINE(' Max DEL PCT = ' || vMaxDel); */
ELSE
DBMS_OUTPUT.PUT_LINE('-- Cannot Analyze INDEX ' || vOwner || '.' || vIdxName || ', type= ' || vIdxType || ', status= ' || vStatus);
END IF;
END LOOP;
CLOSE cGetIdx;
END;
/
clear columns;
clear breaks;
set pagesize 999;
set linesize 268;
set echo off;
-- set feedback off;
set time on;
column name heading 'Index' format a30;
column most_repeated_key heading 'Num|repeating|keys' format 999,999,999;
column distinct_keys heading 'distinct|keys' format 999,999,999;
column lf_rows heading 'leaf|rows|mils' format 999.99;
column del_lf_rows heading 'num|deleted|leaf rows|mils' format 999.99;
column del_rows_pct heading 'Deleted|%' format 999.99;
column height heading 'Height' format 999,999,999;
column blks_gets_per_access heading 'blks|per|access' format 999,999,999;
column opt_cmpr_count heading 'opt|compr|ratio' format 9,999;
column opt_cmpr_pctsave heading 'compr|pct|saved' format 9,999;
column blocks heading 'blks|alloc|2 segmt' format 999,999,999;
column rebuild heading 'rebuild|compress|index?' format a8;
column calc_pct_used heading 'calc|PCT|used' format 9999;
column pct_used heading 'PCT|used' format 9999;
select stats.name,
most_repeated_key,
stats.distinct_keys,
(lf_rows/1000000) lf_rows,
(del_lf_rows/1000000) del_lf_rows,
case when del_lf_rows < lf_rows then (del_lf_rows/lf_rows)*100
else 100 end del_rows_pct,
height,
blks_gets_per_access,
opt_cmpr_count,
opt_cmpr_pctsave,
blocks,
stats.pct_used,
-- indx.pct_free,
-- (100 - indx.pct_free - 10) calc_pct_used,
case when pct_used < (100 - indx.pct_free - 10) then 'Y'
when opt_cmpr_pctsave > 10 then 'Y'
when del_lf_rows >= lf_rows then 'Y'
when 10 < (del_lf_rows/lf_rows*100) then 'Y'
else 'N' end rebuild
from jab_hold_indx_stats stats,
dba_indexes indx
WHERE name=INDEX_NAME
order by substr(name,4,26),
substr(name,1,3) DESC;
--*****************************************************
-- Index Analysis SQL
--*****************************************************
Procss scheduler Jobs :
rem # PURPOSE:This sql script will look report PeopleSoft Process Scheduluer jobs
rem #
rem #
rem # USAGE: @find_job.sql
rem #
rem # INPUT PARAMETERS: status - Status of the process itself
rem # job - Process JOB Name
rem # prcs - Process Name
rem # runcntl - Run Control ID
rem # srch_begin_date - Process Start Date
rem # srch_end_date - Process End Date
rem # srch_duration - Look for durations greater than x
rem # showquelist - Report the list of queued jobs/processes? (N)
rem # srch_duration - Report scheduler setup information? (N)
clear columns;
clear breaks;
SET LINESIZE 300;
SET PAGESIZE 999;
COLUMN today new_value curdate NOPRINT;
COLUMN today_1 new_value curdate_1 NOPRINT;
COLUMN today_120 new_value curdate_120 NOPRINT;
SELECT to_char(sysdate-1,'YYYY-MM-DD') today_1 FROM dual;
SELECT to_char(sysdate-120,'YYYY-MM-DD') today_120 FROM dual;
SELECT to_char(sysdate,'YYYY-MM-DD') today FROM dual;
prompt ' '
prompt ' Run Status: '
prompt ' 1 = Cancel '
prompt ' 2 = Deleted '
prompt ' 3 = Error '
prompt ' 4 = Hold '
prompt ' 5 = Queued '
prompt ' 6 = Initiated '
prompt ' 7 = Processing '
prompt ' 8 = Cancelled '
prompt ' 9 = Success '
prompt ' 10 = Not Successful '
prompt ' 11 = Posted '
prompt ' 12 = Unable to Post '
prompt ' 13 = Resend '
prompt ' 14 = Posting '
prompt ' 15 = Content Generated '
prompt ' 16 = Pending '
prompt ' 17 = Success with Warning '
prompt ' 18 = Blocked '
prompt ' 19 = Restart '
prompt ' 99 = All '
prompt ' '
break on START_DATE skip page
accept status default 99 prompt 'Enter status you wish to retrieve or <cr> for all : '
accept prcs_instance default '0' prompt 'Enter process_instance number you wish to examine (ALL) : '
accept job prompt 'Enter job name you wish to examine (%) : '
accept prcs prompt 'Enter process name you wish to examine (%) : '
accept runcntl prompt 'Enter the run control name you wish to examine (%) : '
accept srch_begin_date date format 'yyyy-mm-dd' default '&curdate_1' prompt 'Enter the Begin DATE (&&curdate_1) (%) : '
accept srch_end_date date format 'yyyy-mm-dd' default '&curdate' prompt 'Enter the End DATE (&&curdate) : '
accept srch_duration default 0 prompt 'Anything greater than a duration of ### minutes? (0) : '
accept showquelist default 'N' prompt 'Report the list of queued jobs/processes? (N) : '
accept show_scheduler_setup default 'N' prompt 'Report scheduler setup information? (N) : '
column which_script_to_call new_value which_script_to_call noprint;
select case when upper('&show_scheduler_setup') = 'Y'
then 'scheduler_details'
else 'filler'
end which_script_to_call
from dual a;
column start_date head "Start|Date" format a10;
column prcsinstance head "Instance" format 9999999999
column trace_enabled head "Trace|Enabled" format a10;
column prcsjobname head "Prcs|Job|Name" format a15;
column mainjobname head "Main|Job|Name" format a15;
column prcsname head "Process|Name" format a15;
column oprid head "Operator|Name" format a15;
column server_name head "Server|Name" format a6;
column run_control head "Run|Control" format a25;
column start_time head "Start|Date/Time" format a16;
--column rundttm head "Run|Date/Time" format a16;
column end_time head "End|Date/Time" format a16;
column request_time head "Reqst|Time" format a5;
column run_time head "Run|Time" format a5;
column status head "Process|Status" format a20;
column prcstype head "Process|Type" format a7;
column duration head "Run|Duration|Minutes" format 99999.99;
select case when a.begindttm is not null
then to_date((to_char(a.begindttm, 'YYYY-MM-DD')),'YYYY-MM-DD')
when a.begindttm is null and a.rundttm is not null
then to_date((to_char(a.rundttm, 'YYYY-MM-DD')),'YYYY-MM-DD')
when a.begindttm is null and a.rqstdttm is not null
then to_date((to_char(a.rqstdttm, 'YYYY-MM-DD')),'YYYY-MM-DD')
else to_date((to_char(a.begindttm, 'YYYY-MM-DD')),'YYYY-MM-DD')
end start_date,
-- to_date((to_char(a.begindttm, 'YYYY-MM-DD')),'YYYY-MM-DD') start_date,
a.prcsinstance,
case when instr(upper(b.ORIGPARMLIST),'-TRACE',1) > 1 and a.prcstype = 'Application Engine'
then 'TRUE(' || regexp_substr(regexp_substr(substr(upper(ORIGPARMLIST),(instr(upper(ORIGPARMLIST),'-TRACE')),200),'[^_/,.]+',1,1),'[^ ]+',1,2) || ')'
when instr(upper(b.ORIGPARMLIST),'-DEBUG',1) > 1 and a.prcstype = 'SQR Process'
then '** TRUE **'
when instr(upper(b.ORIGPARMLIST),'-DEBUG',1) > 1 and a.prcstype = 'SQR Report'
then '** TRUE **'
when instr(upper(b.ORIGPARMLIST),'-TRACE',1) > 1 and a.prcstype = 'COBOL SQL'
then 'TRUE(' || regexp_substr(regexp_substr(substr(upper(ORIGPARMLIST),(instr(upper(ORIGPARMLIST),'-TRACE')),200),'[^_/,.]+',1,1),'[^ ]+',1,2) || ')'
when instr(upper(b.ORIGPARMLIST),'-TRACE',1) > 1
then 'TRUE(' || regexp_substr(regexp_substr(substr(upper(ORIGPARMLIST),(instr(upper(ORIGPARMLIST),'-TRACE')),200),'[^_/,.]+',1,1),'[^ ]+',1,2) || ')'
else ' '
end trace_enabled,
a.prcsjobname,
a.mainjobname,
case when prcsjobname = ' ' or a.prcstype = 'PSJob'
then a.prcsname
else '->' || a.prcsname
end prcsname,
substr(decode(a.prcstype,'Application Engine', 'AE',
'COBOL SQL', 'COBOL',
'Crystal', 'CRYSTAL',
'PSJob', 'PSJOB',
'SQR Process', 'SQR',
'SQR Report', 'SQR',
'XML Publisher', 'XML', 'UNKN'), 1, 7) prcstype,
case when a.servernamerun > ' '
then a.servernamerun
else a.servernamerqst
end server_name,
a.oprid,
substr(a.runcntlid, 1, 25) run_control,
-- case when a.rqstdttm is null
-- then ' '
-- else substr(to_char(a.rqstdttm, 'HH24:MI'), 1, 16)
-- end request_time,
-- case when a.rundttm is null
-- then ' '
-- else substr(to_char(a.rundttm, 'HH24:MI'), 1, 16)
-- end run_time,
-- substr(to_char(a.rundttm, 'YYYY-MM-DD HH24:MI'), 1, 16) rundttm,
case when a.begindttm is not null
then substr(to_char(a.begindttm, 'YYYY-MM-DD HH24:MI'), 1, 16)
when a.begindttm is null and a.rundttm is not null
then substr(to_char(a.rundttm, 'YYYY-MM-DD HH24:MI'), 1, 16)
when a.begindttm is null and a.rqstdttm is not null
then substr(to_char(a.rqstdttm, 'YYYY-MM-DD HH24:MI'), 1, 16)
else substr(to_char(a.begindttm, 'YYYY-MM-DD HH24:MI'), 1, 16)
end start_time,
-- substr(to_char(a.begindttm, 'YYYY-MM-DD HH24:MI'), 1, 16) start_time,
substr(to_char(a.enddttm, 'YYYY-MM-DD HH24:MI'), 1, 16) end_time,
substr(decode(a.runstatus, 1, 'Cancel',
2, 'Deleted',
3, 'Error',
4, 'Hold',
5, 'Queued',
6, 'Initiated',
7, 'Processing',
8, 'Cancelled',
9, 'Success',
10, 'Not Success',
11, 'Posted',
12, 'Unable to post',
13, 'Resend',
14, 'Posting',
15, 'Generated',
16, 'Pending',
17, 'Success with Warning',
18, 'Blocked',
19, 'Restart','Unknown'), 1, 20) Status,
to_number(cast(a.enddttm as date) - cast(a.begindttm as date)) * 1440 duration
from sysadm.psprcsrqst a
left outer join sysadm.psprcsparms b on b.prcsinstance = a.prcsinstance
where ( a.prcsinstance = to_number(&prcs_instance)
or &prcs_instance = 0)
and a.prcsname like upper('%&prcs%')
and a.prcsjobname like upper('%&job%')
and a.runcntlid like '%&runcntl%'
and ((&srch_duration > 0
and (to_number(cast(a.enddttm as date) - cast(a.begindttm as date)) * 1440) >= &srch_duration)
or &srch_duration = 0)
and (( a.runstatus = &status
and &status < 99 )
or ( a.runstatus <> 5
and &status = 99
and '&showquelist' = 'N' )
or ( &status = 99
and '&showquelist' = 'Y' ))
and (a.begindttm between to_date('&srch_begin_date 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('&srch_end_date 23:59:59','YYYY-MM-DD HH24:MI:SS')
or a.enddttm between to_date('&srch_begin_date 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('&srch_end_date 23:59:59','YYYY-MM-DD HH24:MI:SS')
or a.rundttm between to_date('&srch_begin_date 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('&srch_end_date 23:59:59','YYYY-MM-DD HH24:MI:SS')
or a.rqstdttm between to_date('&srch_begin_date 00:00:00','YYYY-MM-DD HH24:MI:SS') and to_date('&srch_end_date 23:59:59','YYYY-MM-DD HH24:MI:SS'))
order by 1,
a.prcsinstance,
11,
substr(to_char(a.enddttm, 'YYYY-MM-DD HH24:MI'), 1, 16),
a.rundttm desc;
prompt
prompt ************************************************************************************************************
prompt * Note: PSJOB is a wrapper around multiple processes. Look for '->' for individual routines consuming time.
prompt * PSJOB duration is the accumulation of all it's child processes.
prompt ************************************************************************************************************
prompt
@@&which_script_to_call..sql
psoft_login:
rem # PURPOSE:This sql script will examine tables with STALE statistics
rem #
rem #
rem # USAGE: @psoft_logins.sql
rem #
clear columns;
clear breaks;
SET LINESIZE 500;
SET PAGESIZE 100;
set echo off;
set feedback off;
set termout off;
set termout off;
column search_date new_value search_date
select to_char(sysdate,'YYYY-MM-DD') search_date
from sys.dual;
set termout on
prompt
ACCEPT v_search_date DATE format 'YYYY-MM-DD' DEFAULT '&&search_date' PROMPT 'ENTER Search Date (Default: &&search_date) > '
clear columns;
column LOGIN_DATE HEAD "Login|Date" format a10;
column LOGIN_DAY_OF_WEEK HEAD "Login|Day|of|Week" format a06;
column LOGIN_HOUR_OF_DAY HEAD "Login|Hour|of|Day" format a06;
column H00 HEAD "00" format 99999;
column H01 HEAD "01" format 99999;
column H02 HEAD "02" format 99999;
column H03 HEAD "03" format 99999;
column H04 HEAD "04" format 99999;
column H05 HEAD "05" format 99999;
column H06 HEAD "06" format 99999;
column H07 HEAD "07" format 99999;
column H08 HEAD "08" format 99999;
column H09 HEAD "09" format 99999;
column H10 HEAD "10" format 99999;
column H11 HEAD "11" format 99999;
column H12 HEAD "12" format 99999;
column H13 HEAD "13" format 99999;
column H14 HEAD "14" format 99999;
column H15 HEAD "15" format 99999;
column H16 HEAD "16" format 99999;
column H17 HEAD "17" format 99999;
column H18 HEAD "18" format 99999;
column H19 HEAD "19" format 99999;
column H20 HEAD "20" format 99999;
column H21 HEAD "21" format 99999;
column H22 HEAD "22" format 99999;
column H23 HEAD "23" format 99999;
break on LOGIN_DATE skip page
SELECT LOGIN_DATE,
LOGIN_DAY_OF_WEEK,
LOGIN_HOUR_OF_DAY,
sum(H00) H00,
sum(H01) H01,
sum(H02) H02,
sum(H03) H03,
sum(H04) H04,
sum(H05) H05,
sum(H06) H06,
sum(H07) H07,
sum(H08) H08,
sum(H09) H09,
sum(H10) H10,
sum(H11) H11,
sum(H12) H12,
sum(H13) H13,
sum(H14) H14,
sum(H15) H15,
sum(H16) H16,
sum(H17) H17,
sum(H18) H18,
sum(H19) H19,
sum(H20) H20,
sum(H21) H21,
sum(H22) H22,
sum(H23) H23
FROM (SELECT OPRID,
LOGIPADDRESS,
PT_SIGNON_TYPE,
TO_CHAR(logindttm, 'YYYY-MM-DD') LOGIN_DATE,
TO_CHAR(logindttm, 'DY') LOGIN_DAY_OF_WEEK,
TO_CHAR(logindttm, 'HH24') LOGIN_HOUR_OF_DAY,
TO_CHAR(logoutdttm, 'YYYY-MM-DD') LOGOUT_DATE,
TO_CHAR(logoutdttm, 'DY') LOGOUT_DAY_OF_WEEK,
TO_CHAR(logoutdttm, 'HH24') LOGOUT_HOUR_OF_DAY,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '00' AND TO_CHAR(logindttm, 'HH24') <= '00' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '00' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H00,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '01' AND TO_CHAR(logindttm, 'HH24') <= '01' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '01' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H01,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '02' AND TO_CHAR(logindttm, 'HH24') <= '02' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '02' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H02,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '03' AND TO_CHAR(logindttm, 'HH24') <= '03' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '03' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H03,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '04' AND TO_CHAR(logindttm, 'HH24') <= '04' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '04' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H04,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '05' AND TO_CHAR(logindttm, 'HH24') <= '05' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '05' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H05,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '06' AND TO_CHAR(logindttm, 'HH24') <= '06' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '06' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H06,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '07' AND TO_CHAR(logindttm, 'HH24') <= '07' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '07' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H07,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '08' AND TO_CHAR(logindttm, 'HH24') <= '08' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '08' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H08,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '09' AND TO_CHAR(logindttm, 'HH24') <= '09' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '09' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H09,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '10' AND TO_CHAR(logindttm, 'HH24') <= '10' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '10' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H10,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '11' AND TO_CHAR(logindttm, 'HH24') <= '11' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '11' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H11,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '12' AND TO_CHAR(logindttm, 'HH24') <= '12' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '12' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H12,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '13' AND TO_CHAR(logindttm, 'HH24') <= '13' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '13' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H13,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '14' AND TO_CHAR(logindttm, 'HH24') <= '14' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '14' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H14,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '15' AND TO_CHAR(logindttm, 'HH24') <= '15' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '15' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H15,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '16' AND TO_CHAR(logindttm, 'HH24') <= '16' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '16' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H16,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '17' AND TO_CHAR(logindttm, 'HH24') <= '17' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '17' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H17,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '18' AND TO_CHAR(logindttm, 'HH24') <= '18' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '18' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H18,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '19' AND TO_CHAR(logindttm, 'HH24') <= '19' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '19' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H19,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '20' AND TO_CHAR(logindttm, 'HH24') <= '20' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '20' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H20,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '21' AND TO_CHAR(logindttm, 'HH24') <= '21' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '21' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H21,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '22' AND TO_CHAR(logindttm, 'HH24') <= '22' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '22' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H22,
CASE WHEN TO_CHAR(logoutdttm, 'HH24') >= '23' AND TO_CHAR(logindttm, 'HH24') <= '23' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
WHEN TO_CHAR(logindttm, 'HH24') = '23' AND TO_CHAR(logindttm, 'YYYY-MM-DD') = TO_CHAR(logoutdttm, 'YYYY-MM-DD')
THEN 1
ELSE 0 END H23
FROM PSACCESSLOG A
-- WHERE PT_SIGNON_TYPE = '1'
where A.LOGINDTTM >= to_date('&v_search_date 00:00:00','YYYY-MM-DD HH24:MI:SS')
and A.LOGOUTDTTM <= to_date('&v_search_date 23:59:59','YYYY-MM-DD HH24:MI:SS')
--- where ((A.LOGINDTTM >= trunc(sysdate - 1)
--- AND A.LOGINDTTM <= sysdate)
--- OR (A.LOGOUTDTTM >= trunc(sysdate - 1)
--- AND A.LOGOUTDTTM <= sysdate))
and upper(A.OPRID) not like '%TEST%'
and upper(A.OPRID) not like '%TIDAL%'
and upper(A.OPRID) not like '%BATCH%'
and upper(A.OPRID) not like '%GUEST%'
and upper(A.OPRID) not like '%PUBLIC%'
and upper(A.OPRID) not like 'RIT%' -- Rochester
and upper(A.OPRID) not like '%TRAIN%'
and A.OPRID not in ('AHSITESCOPE',
'AH_ATPAR',
'AH_FS_BATCH',
'AH_HR_BATCH',
'AH_SCM_BATCH',
'BATCH_PROCESSOR',
'CBEScheduler',
'CBE_APPLICANT',
'CONVERSION',
'ELM100',
'FABATCH',
'FSGRP',
'GNW',
'JDICANDIDATE',
'JDICandIDATE',
'JDI_BATCHUSR',
'JDI_GLOBALHRIS',
'MC_ERP_AZURE',
'MC_ADM_APPL_NODE_USER',
'MC_AD_SWITCHUSER',
'MC_NELNET',
'MC_ST_PORTAL_NODE_USER',
'MD_CE',
'MD_ONLAPP',
'OAMPSFT',
'OIMUM',
'OOD_ADM',
'OOD_IB',
'OOD_PSAPPS',
'OPC_PSAPPS',
'PS',
'PSAPPS',
'PSBATCH',
'PSEM',
'PS_SOLARWINDS',
'PTWEBSERVER',
'RIT_PUBLIC_USER',
'ROBOREG',
'SAPRCS',
'OTRPRCS',
'SAMPLE',
'STDNTGRP',
'KEL2074810',
'TOU2128621',
'UMCONV',
'UM_GUEST',
'UM_INBOUND_WS_SECURITY_ID',
'UM_NELNET',
'UM_ONPLANU',
'URESET',
'UNEXTERNAL',
'UNGUEST',
'VERIFYDB',
'VP1',
'WSU_CS_GUEST',
'WSU_TANK',
'W_PROCESS_SCHEDULER',
'adm_ntid_batch',
'cfpadm',
'dco_batch',
'dpwnca',
'gl2_cutover',
'ood_adm',
'rit_svc_nelnet')) LOGIN_DATA
group by LOGIN_DATE,
LOGIN_DAY_OF_WEEK,
LOGIN_HOUR_OF_DAY
order by LOGIN_DATE,
LOGIN_DAY_OF_WEEK,
LOGIN_HOUR_OF_DAY;
Sessions :
clear columns;
clear breaks;
SET LINESIZE 300;
SET PAGESIZE 999;
ttitle skip 8 'Detailed Session Analysis (V$SESSION)' skip 5;
break on CI_OPRID nodup on OPER_DESC nodup on CI_MACHINE nodup on CI_DOMAIN;
column OPER_DESC format a30;
column CI_OPRID format a15;
column CI_DOMAIN format a20;
column CI_MACHINE format a20;
column USERNAME format a15;
column ACTION format a30;
column MODULE format a50;
column STATUS format a10;
column LIFE format 999999.99;
select B.OPRID CI_OPRID,
nvl(B.OPRDEFNDESC,'No information') OPER_DESC,
a.USERNAME USERNAME,
(sysdate - logon_time) *1440 LIFE,
lower(replace((substr(A.CLIENT_INFO, (instr(A.CLIENT_INFO,',',1,2)+1), (instr(A.CLIENT_INFO,',',1,3) - (instr(A.CLIENT_INFO,',',1,2)+1) ))),'.oracleoutsourcing.com',' ')) CI_MACHINE,
case when (instr(A.CLIENT_INFO,',',1,3) + 1) = (instr(A.CLIENT_INFO,',',1,4)) THEN
(substr(A.CLIENT_INFO, (instr(A.CLIENT_INFO,',',1,4)+1), (instr(A.CLIENT_INFO,',',1,5) - (instr(A.CLIENT_INFO,',',1,4)+1) )))
else (substr(A.CLIENT_INFO, (instr(A.CLIENT_INFO,',',1,3)+1), (instr(A.CLIENT_INFO,',',1,4) - (instr(A.CLIENT_INFO,',',1,3)+1) )))
end "CI_DOMAIN",
count(1),
a.STATUS,
a.ACTION,
A.MODULE
from V$SESSION A,
SYSADM.PSOPRDEFN B
where A.USERNAME is not null
and substr(A.CLIENT_INFO, 1, (instr(A.CLIENT_INFO,',',1,1) -1)) = B.OPRID
-- and A.STATUS='ACTIVE'
group by B.OPRID,
nvl(B.OPRDEFNDESC,'No information'),
(sysdate - logon_time) *1440,
lower(replace((substr(A.CLIENT_INFO, (instr(A.CLIENT_INFO,',',1,2)+1), (instr(A.CLIENT_INFO,',',1,3) - (instr(A.CLIENT_INFO,',',1,2)+1) ))),'.oracleoutsourcing.com',' ')),
A.USERNAME,
a.STATUS,
a.ACTION,
a.MODULE,
case when (instr(A.CLIENT_INFO,',',1,3) + 1) = (instr(A.CLIENT_INFO,',',1,4)) THEN
(substr(A.CLIENT_INFO, (instr(A.CLIENT_INFO,',',1,4)+1), (instr(A.CLIENT_INFO,',',1,5) - (instr(A.CLIENT_INFO,',',1,4)+1) )))
else (substr(A.CLIENT_INFO, (instr(A.CLIENT_INFO,',',1,3)+1), (instr(A.CLIENT_INFO,',',1,4) - (instr(A.CLIENT_INFO,',',1,3)+1) ))) end
order by B.OPRID,
OPER_DESC,
A.USERNAME,
LIFE,
CI_MACHINE,
a.status,
a.ACTION,
a.MODULE,
CI_DOMAIN;
Find Stale stats :
clear columns;
clear breaks;
SET LINESIZE 300;
SET PAGESIZE 999;
ttitle off;
ttitle skip 8 'Stale STATS report by SCHEMA and TABLE' skip 5;
-- break on owner nodup on table_name nodup;
break on owner nodup
column owner head "Table|Owner" format a30;
column table_name head "Table|Name" format a30;
column partitioned head "Partitioned|Table" format a11;
column stale_stats head "Stale|Stats" format a5;
column truncated head "Table|has|been|Trunc?" format a6;
column transactions head "Adds|Deletes|Updates|Transactions" format 99999999999;
column num_rows head "Current|Row|Count" format 99999999999;
column change_percent head "Change|Percent" format 99999.9;
column timestamp head "Last|Modified|Date" format a15;
column days_since head "Days|Since|Last|Data|Change" format 999999;
column last_analyzed head "Last|Analyzed|Date" format a15;
column sample_percent head "Last|Sample|Pct" format 999999;
column stattype_locked head "Locked|Stats" format a6;
select stats.owner,
stats.table_name,
'NO' partitioned,
stats.stale_stats,
atm.truncated,
atm.updates + atm.inserts + atm.deletes transactions,
stats.num_rows,
case when stats.num_rows > (atm.deletes + atm.updates + atm.inserts)
then (atm.deletes + atm.updates + atm.inserts) / stats.num_rows * 100
else 100.1
end change_percent,
trunc((sysdate - atm.timestamp),1) days_since,
to_char(atm.timestamp, 'mm-dd-yy hh24:mi') timestamp,
to_char(stats.last_analyzed, 'mm-dd-yy hh24:mi') last_analyzed,
case when stats.sample_size > 0 and stats.num_rows > 0
then stats.sample_size*100/stats.num_rows
else 0
end sample_percent,
stats.stattype_locked
from dba_tab_statistics stats,
dba_tab_modifications atm
where (round ( (atm.deletes + atm.updates + atm.inserts) / stats.num_rows * 100) >= 3
or stats.stale_stats = 'YES'
or atm.truncated = 'YES')
-- and atm.subpartition_name is null
-- and atm.partition_name is null
and stats.num_rows > 0
and stats.owner = atm.table_owner
and stats.table_name = atm.table_name
and stats.object_type = 'TABLE'
and stats.table_name not like 'BIN$%'
and stats.table_name not like 'DR$%'
and stats.table_name not like 'DR#%'
and stats.owner not in ('SYS','ANONYMOUS','APEX_PUBLIC_USER','APPQOSSYS','AUDSYS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','FLOWS_020200','FLOWS_FILES','LBACSYS','MDDATA','MDSYS','MGDSYS','MGMT_VIEW','MIGREP','OLAPSYS','ORACLE_OCM','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','PERFSTAT','QUALYS_SCAN','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SQLTXPLAIN','STRMADMIN','SYSMAN','SYSTEM','TSMSYS','WKPROXY','WKSYS','WK_TEST','WMSYS','XDB','XDBSYSMAN')
and (stats.owner,stats.table_name) not in (select /*+ unnest */
x.owner,
x.table_name
from dba_tables x
where x.partitioned = 'YES')
and (stats.owner,stats.table_name) not in (select /*+ unnest */
x1.SCHEMA_NAME,
x1.table_name
from XBOLGSS.OOD_STATS_PSFT_WRK_TABLE x1)
union
select /*+ unnest */
distinct
STATS.owner,
STATS.table_name,
STATS.partitioned,
STATS.stale_stats,
MODS.truncated,
MODS.transactions,
STATS.num_rows, /*+ This number is exactly the same as sys.dba_tables (NUM_ROWS) */
case when MODS.transactions is not null and MODS.transactions > 0 and MODS.transactions >= STATS.num_rows
then 100.1
when MODS.transactions is not null and MODS.transactions > 0
then MODS.transactions/STATS.num_rows*100
else 0
end change_percent,
trunc((sysdate - MODS.timestamp),1) days_since,
to_char(MODS.timestamp, 'mm-dd-yy hh24:mi') timestamp,
to_char(STATS.last_analyzed, 'mm-dd-yy hh24:mi') last_analyzed,
case when STATS.sample_size > 0 and STATS.num_rows > 0
then STATS.sample_size*100/STATS.num_rows
else 0
end sample_percent,
STATS.stattype_locked
from (
select /*+ unnest */
tab.owner,
tab.table_name,
max(mod.timestamp) timestamp,
max(mod.truncated) truncated,
max(nvl(tab.num_rows, 1)) tab_num_rows,
max(MOD.updates + MOD.inserts + MOD.deletes) transactions
from sys.dba_tab_modifications MOD,
sys.dba_tables tab
where MOD.table_name = tab.table_name
and MOD.table_owner = tab.owner
and tab.partitioned = 'YES'
and tab.table_name not like 'BIN$%'
and tab.table_name not like 'DR$%'
and tab.table_name not like 'DR#%'
and tab.owner not in ('SYS','ANONYMOUS','APEX_PUBLIC_USER','APPQOSSYS','AUDSYS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','FLOWS_020200','FLOWS_FILES','LBACSYS','MDDATA','MDSYS','MGDSYS','MGMT_VIEW','MIGREP','OLAPSYS','ORACLE_OCM','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','PERFSTAT','QUALYS_SCAN','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SQLTXPLAIN','STRMADMIN','SYSMAN','SYSTEM','TSMSYS','WKPROXY','WKSYS','WK_TEST','WMSYS','XDB','XDBSYSMAN')
group by tab.owner,
tab.table_name
having max(MOD.updates + MOD.inserts + MOD.deletes) > 0
) MODS,
(
select /*+ unnest */
distinct
tab.owner,
tab.table_name,
tab.partitioned,
stat.stattype_locked,
max(stat.stale_stats) stale_stats,
max(stat.last_analyzed) last_analyzed,
max(nvl(stat.sample_size,1)) sample_size,
max(nvl(stat.num_rows, 1)) num_rows
from sys.dba_tab_statistics stat,
sys.dba_tables tab
where (stat.stale_stats = 'YES' or stat.partition_position is null)
and stat.table_name = tab.table_name
and stat.owner = tab.owner
--- Statistics records contain both TABLE, PARTITION and SUBPARTITION records
--- I only want any record flagged as STALE or the TABLE level row, the MAX
--- ensures I only receive 1 row per TABLE
--- Even if someone deletes table stats (DBMS_STATS.DELETE_TABLE_STATS('<odner>','<table_name>'))
--- The DBA_TAB_STATISTICS record will exist.
---
--- This design will tell us if any SubPartition or Partition was 1) Truncated, 2) changed
--- enough to warrant a GSS, or 3) Changed or 4) Has not been changed at all
---
and tab.partitioned = 'YES'
and tab.table_name not like 'BIN$%'
and tab.table_name not like 'DR$%'
and tab.table_name not like 'DR#%'
and tab.owner not in ('SYS','ANONYMOUS','APEX_PUBLIC_USER','APPQOSSYS','AUDSYS','CTXSYS','DBSNMP','DIP','DMSYS','EXFSYS','FLOWS_020200','FLOWS_FILES','LBACSYS','MDDATA','MDSYS','MGDSYS','MGMT_VIEW','MIGREP','OLAPSYS','ORACLE_OCM','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','PERFSTAT','QUALYS_SCAN','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SQLTXPLAIN','STRMADMIN','SYSMAN','SYSTEM','TSMSYS','WKPROXY','WKSYS','WK_TEST','WMSYS','XDB','XDBSYSMAN')
group by tab.owner,
tab.table_name,
tab.partitioned,
stat.stattype_locked
) STATS
where STATS.owner = MODS.OWNER
and STATS.table_name = MODS.TABLE_NAME
order by 1,2
/
Size of the table and index structure :
SET VERIFY OFF;
SET AUTOTRACE OFF;
set pagesize 500
set linesize 300
set long 500
ttitle off;
column which_script_to_call new_value which_script_to_call noprint;
select case when objects_found < 6
then 'index_autonomous'
else 'index_normal'
end which_script_to_call
from dual a,
(select count(*) objects_found
from user_objects t
where t.object_name in ('ATTRCOL$',
'COL$',
'ICOL$',
'IND$',
'OBJ$',
'USER$')
and t.object_type = 'TABLE');
prompt
prompt **<table_name> means that compression is enabled for this table.
prompt **<index_name> means that compression is enabled for this index.
prompt ** &which_script_to_call will be called
prompt
column owner head "Owner" format a12;
column table_name head "Table|Name" format a40;
column tablespace_name head "TableSpace|Name" format a15;
column partition_name head "Partition|Name" format a15;
column stale_stats head "Stale|Stats" format a06;
column partitioned head "Partitioned|Flag" format a11;
column num_rows head "Row|Count" format 99,999,999,999;
column extents head "Number|Of|Extents" format 999,999,999;
column initial_in_mb head "Initial|Size|(mb}" format 999,999;
column next_in_mb head "Next|Size|(mb}" format 999,999;
column total_size_in_gb head "Total|Size|(gb}" format 999,999.99;
column degree head 'Parlel|Degree' format a6;
column last_analyzed head "Last|Analyzed" format a18;
select a.owner,
case when a.compression = 'ENABLED'
then '**' || a.table_name
else a.table_name
end table_name,
a.tablespace_name,
to_char(a.last_analyzed,'YYYY/MM/DD HH24:MM') last_analyzed,
case when degree is null
then '1'
else trim(degree)
end degree,
c.stale_stats,
a.partitioned,
a.num_rows,
SUM(b.bytes/1024/1024/1024) total_size_in_gb,
MAX(b.initial_extent/1024/1024) initial_in_mb,
MAX(b.next_extent/1024/1024) next_in_mb,
SUM(b.extents) extents
from dba_tables a
left outer join dba_segments b
on b.owner = a.owner
and b.segment_name = a.table_name
and b.segment_type like 'TABLE%'
left outer join dba_tab_statistics c
on c.owner = a.owner
and c.table_name = a.table_name
and c.partition_name is null
where a.table_name = upper('&1')
group by a.owner,
case when a.compression = 'ENABLED'
then '**' || a.table_name
else a.table_name
end,
a.tablespace_name,
a.degree,
a.last_analyzed,
c.stale_stats,
a.partitioned,
a.num_rows;
@@&which_script_to_call..sql &1
SET VERIFY on;
SET AUTOTRACE OFF;
