Monitoring and Maintainence of Jobs

Display database jobs and schedulder jobs:-

set lines 200
Set pages 200
col WHAT for a40
select JOB,WHAT,BROKEN,FAILURES from dba_jobs;


col OWNER for a15
col STATE for a10
col FAL for 999
select OWNER,JOB_NAME,STATE,FAILURE_COUNT FAL from dba_scheduler_jobs;


Displays scheduler information about job programs.

SET LINESIZE 250

COLUMN owner FORMAT A20
COLUMN program_name FORMAT A30
COLUMN program_action FORMAT A50
COLUMN comments FORMAT A40

SELECT owner,
       program_name,
       program_type,
       program_action,
       number_of_arguments,
       enabled,
       comments
FROM   dba_scheduler_programs
ORDER BY owner, program_name;


Displays scheduler information about job schedules

SET LINESIZE 250

COLUMN owner FORMAT A20
COLUMN schedule_name FORMAT A30
COLUMN start_date FORMAT A35
COLUMN repeat_interval FORMAT A50
COLUMN end_date FORMAT A35
COLUMN comments FORMAT A40

SELECT owner,
       schedule_name,
       start_date,
       repeat_interval,
       end_date,
       comments
FROM   dba_scheduler_schedules
ORDER BY owner, schedule_name;


Displays scheduler information for running jobs.

SET LINESIZE 200

COLUMN owner FORMAT A20

SELECT owner,
       job_name,
       running_instance,
       elapsed_time
FROM   dba_scheduler_running_jobs
ORDER BY owner, job_name;

select * from dba_jobs_running;

--To check stats job history for 10G

set linesize 300
col JNM for a30
col JST for a25
col RUN_DURATION for a15
col SLAVE_PID for 99999
col STATUS for a10
select TO_CHAR(ACTUAL_START_DATE, 'YYYY-MM-DD_HH24:MI:SS') jst, ERROR#, JOB_NAME jnm, STATUS,SESSION_ID,RUN_DURATION,SLAVE_PID
from DBA_SCHEDULER_JOB_RUN_DETAILS  where JOB_NAME = 'GATHER_STATS_JOB' order by ACTUAL_START_DATE;

--To check stats job history for 11G

set linesize 300
col CNM for a32
col JOB_DURATION for a15
col jst for a20
col JOB_STATUS for a13

select CLIENT_NAME, STATUS from dba_autotask_client where client_name = 'auto optimizer stats collection';

select TO_CHAR(JOB_START_TIME, 'YYYY-MM-DD_HH24:MI:SS') jst, JOB_ERROR, CLIENT_NAME cnm, JOB_STATUS,JOB_DURATION
from dba_autotask_job_history  where client_name = 'auto optimizer stats collection' order by job_start_time;

Full Database Refresh Using Export Dump

1. Input from Requester :

1.1 Source and Target database Name.

If not present, check with requester.

1.2        Export dump timestamp.

2. Pre-Check :

2.1        Check for the existence/In-Scope of the Source and Target Databases in PAC.

2.2        Check for existence of Export dump in Source Database.

Check for successful completion of Export from export log.

2.3        Check for Database size in Source Database.

“Select tablespace_name,sum(bytes)/1024/1024 MB from dba_segments group by tablespace_name;”

2.4        Check for Space availability in Target Database.

( Available Space for refresh =  Total allotted space in Tablespace(s) ) check this file

Note : If there is not enough free space for refresh then tablespace(s)

can be resized. Disk space can also be increased by Opening GRS request to SA.

                                   

3. Activities in Source Database/Server:

3.1        Scp export dump from Source server to target server

scp target_unix_user@target_hostname:<$BACKUP/export/hcl/

     

4. Activities  in Target Database/Server:

( Default activity location $backup/export/hcl )

4.1        Restart database in Restricted Mode.

SQL> alter system enable restricted session;

System altered.

4.2        Take Structure export of the database. Take same script for schema refresh

R:\DBA\paresh\DB-TASKS\Database-Refresh\precheck_details_main_for_schema_SCRIPT.sql

exp  /  file=${ORACLE_SID}_norows.dmp log=${ORACLE_SID}_norows.log full=y rows=n statistics=none

4.3        Take backup of passwords of all the users.

Set line 160

Set pages 1000

spool user_passwd.sql

select 'alter user ' ||u.username|| ' identified by values '''||u.password||''';'

from   sys.dba_users u;

spool off

4.4        Drop all Schema except SYS,SYSTEM,OUTLN,RMAN,DBSNMP,

  SCOTT,PERFSTAT,FOGLIGHT,WMSYS AND EXTERNAL USER.

Set line 160

Set pages 1000

Spool drop_users.sql

select 'drop user '||username||' cascade;' from dba_users where username not in 

('SYS','SYSTEM','OUTLN','RMAN','DBSNMP','SCOTT',

'PERFSTAT','FOGLIGHT','WMSYS') and username not like 'OPS%';

spool off

Set line 160

Set pages 1000

Spool drop_users.sql

select 'drop user '||username||' cascade;' from dba_users

where username not in ('SYS','SYSTEM','OUTLN','RMAN',

'DBSNMP','ACCT_ADMIN','ACC_RELEASE','AUDTASK','AUD_OWNER','CM_PROD',

'CTXSRV','DBA_MON','DMG','SCOTT','PERFSTAT',

'FOGLIGHT','WMSYS','ANONYMOUS','CTXSYS','DB_TOOLS','DIP',

'ESMDBA','EXFSYS','HIDS_AUDITOR','MDDATA','MDSYS',

'MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDPLUGINS','ORDSYS',

'OUTLN','SI_INFORMTN_SCHEMA','SYSMAN','TSMSYS','WKSYS',

'XDB','HEALTH_CHECKS','OLAP_USER','ORACLE','PUBLIC','QUEST_CENTRAL',

'TRACESVR','WEBSYS''WKUSER') and

username not like 'OPS%';

spool off

4.5        Make Sure sufficient space available for Full refresh ( step 2.3 and 2.4)

4.6        Start import

mknod imp_pipe  p

uncompress < > imp_pipe &

imp / file=imp_pipe buffer=10000000 full=y  log=imp_full_SID_mmddyy.log  feedback=10000

4.7        Moniter import ( tail –f  nohup.out )

4.8        Check import log for errors (if any) and resolve.

4.9        Compile invalid objects by running utlrp.sql as sysdba.

@?/rdbms/admin/utlrp.sql

4.10      Drop all database link.

set serveroutput on size 100000

spool drop_link.sql

 declare v_paswd  varchar2(30) ;

 begin

 for i in ( select distinct owner  from dba_db_links where owner != 'PUBLIC' ) loop

 select password into v_paswd from dba_users where username = i.owner;

 dbms_output.put_line('connect / ' ) ;

 dbms_output.put_line ( ' alter user '||i.owner||' identified by a; ') ;

 dbms_output.put_line('connect '||i.owner||'/a' ) ;

 dbms_output.put_line(' alter user '||i.owner||' identified by values '''||v_paswd||''' ;' );

    for ii in ( select db_link from dba_Db_links where owner=i.owner ) loop

     dbms_output.put_line(' drop database link '||ii.db_link||' ; ');

   end loop;

 end loop;

 end;

/

spool off

@drop_link.sql

4.11      Apply password from backup ( step 4.3).

4.12      Change database to normal mode.

                        Alter system disable restricted session;

OEM Unreachable

a.     Log on to the server where the database in concern is running.

b.    Check if the OEM agent is running by typing the following command:

$ ps –ef|grep emagent

c.     If step b shows that the emagent is running, do the following:

                                          i.    Change dir to /bin, which could be found out from previous step, the stand dir for is /data/oracle/product/agent10g . There are some non-standard installation that was installed before the standard is a available.

                                         ii.    Run following command to find out file system usage:

$ df –k

                                        iii.    If the percentage in use is more than or equal to 98%, add space

d.    If step b shows that there is no emagent running, do the following

                                        iv.    Find out . The standard is

/data/oracle/product/agent10g

If the OEM client is not installed under standard , use following os command to find out the agent10g under /data/oracle/product:

$ find  . –name agent10g

Change dir to /bin and run following command to start up agent manually:

                                        $ ./emctl start agent

Flashback Area Full

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE        PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
----------------        ------------------------------------ ------------------------------------------------  ------------------------------
CONTROLFILE                   0                                                      0                                                    0
ONLINELOG                       0                                                      0                                                    0
ARCHIVELOG                     0                                                      0                                                    0
BACKUPPIECE                   0                                                      0                                                    0
IMAGECOPY                       0                                                      0                                                    0
FLASHBACKLOG             99.79                                                 0                                                 151
NOTE: The above example shows that the flashback area ONLY contains flashback logs and is currently 99.79% full.


SQL> select * from v$restore_point;


NAME                                 guarantee_flashback_database
=============================================================
BEFORE                                           YES

NOTE: If there are NO restore points created the above query will return NO rows.  The column guarantee_flashback_database shows whether it is a guaranteed restore point or just a normal restore point.  Both should be highlighted to the regional DBA team on transfer of the issue.

SQL> col FLASHBACK_SIZE format 9999999999999
SQL> col ESTIMATE_FLASHBACK_SIZE format 99999999999
SQL> select FLASHBACK_SIZE,RETENTION_TARGET, ESTIMATED_FLASHBACK_SIZE from v$flashback_database_log;

FLASHBACK_SIZE RETENTION_TARGET ESTIMATED_FLASHBACK_SIZE
-------------- ---------------- ------------------------
   85717303296             1440             346175422464


NOTE: Current DB standards are for a 24 hour flashback retention policy. The above shows that the flashback size is 85Gb but the required flashback area size is actually 85Gb+346Gb, so the flashback area / allocation is too small and flashback retention is not being met.  This requires further investigation.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
alter system set db_flashback_retention_target = 6;
alter system set db_recovery_file_dest_size =20g;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

For example if the db_flashback_retention_target is set to 60 and db_recovery_file_dest_size is set to 20 Gb and the Flashback area is almost full, reduce the db_flashback_retention_target to 5 minutes and db_recovery_file_dest_size to 16G to relieve the database. Use the syntax as below:

Max sessions/processes limit Reaching/Reached

Confirm max usage of sessions/process:-

SELECT * FROM V$RESOURCE_LIMIT where RESOURCE_NAME='processes';

select count(*) from v$process;

select count(*) from v$session;

################################################################################
################################################################################

List details of session/process per user and status:-

select username,count(*) from v$session group by username;

select username,status,count(*) from v$session group by username,status;

################################################################################
################################################################################

List Details about current database sessions sqls:-


SELECT  s.username,
         s.sid||':'||s.serial# sid,
         p.spid,
         s.logon_time,
        DECODE(sa.sql_text,NULL,'NOT RUNNING ACTIVE SQL',sa.sql_text) sql_text,
         s.program PROGRAM1,
         p.program PROGRAM2,
         s.terminal||':'||s.machine term_mach,
         s.osuser
FROM    v$session s,
        v$process p,
   v$sqlarea       sa
WHERE   s.paddr(+)=p.addr
AND        s.sql_address = sa.address(+)
AND        s.sql_hash_value = sa.hash_value(+)
ORDER BY 2;

#################################################################################
#################################################################################

Database Availability Alert Checks

1.1.1.   Instance/Database Down or Hanging or bouncing the database

Alert Format

·         A new connection to instance failed: ORA-01034: ORACLE not available ORA-27101: shared memory realm

·         Database <SID> on is hanging!!!

Action

1. If the host is alive, login to host otherwise raise a ticket with UNIX team to check server availability and escalate to Global Oracle DBA Team.
2. Check if cold backup is going on (ps –eaf | grep dbbackup | grep <SID>). If backup is running, check the backup.log for verification of cold backup.   In this case, keep it pending till the cold backup is completed and escalate to Global Oracle DBA Team to check why alert was raised during cold backup. Ideally it should not raise alert as dbbackup put database in maintenance mode before starting cold backup.

Check alert.log to locate any abnormal lines which caused database down or hanging. Confirm all SID associated file systems are mounted. If any file system is missing, raise a ticket with Unix Team immediately 

1.1.2.  Listener Down

Alert Format

• A new connection to instance failed: ORA-12541: TNS: no listener.

Action

1. Extract listener name and listener password, if any, from $TNS_ADMIN/admin/listener.ora if $TNS_ADMIN exists. Otherwise, extract them from $ORACLE_HOME/network/admin/listener.ora.
2. Check listener status, provide listener password if required.
3. If listener is down, start listener and inspect listener log file to find out any errors.

If listener is up, reload listener. If reload hangs check log/trace files.

Procedure to add space to tablespace

1. Check current freespace and list all the datafiles which belong to tablespace:-

set lines 120
set pages 200
col file_name for a70

select sum(bytes)/1024/1024 from dba_free_space where tablespace_name='&1';

select file_name,bytes/1024/1024 from dba_data_files where tablespace_name='&1';

2. Before adding new datafile ensure that there is no datafile already exists with same name in the database.

Following query may be used to identify the duplicate name.
SELECT name AS full_name,
       SUBSTR( name, INSTR(name,'/',-1)+1 ) AS  base_name
  FROM v$datafile
 WHERE SUBSTR( name, INSTR(name,'/',-1)+1  ) =  :BaseFileName

3. ":BaseFileName" should be the bound or replaced with the  base filename to be added ( not pathed ) .
e.g. LNSRDBP1_SYSAUX_01.dbf – in exactly the case to be user *( upper/lower as appropriate)


4. Add datafile to tablespace with below sample command:-

Alter tablespace add datafile '' size ;

History of Past of Sessions

select T2.username,count(T2.username)
FROM gv$active_session_history t
,dba_users t2
WHERE
t.SAMPLE_TIME between to_timestamp('02/22/2014 20:30:00','MM/DD/YYYY HH24:MI:SS') and
to_timestamp('02/22/2014 20:35:00','MM/DD/YYYY HH24:MI:SS')
and t.user_id = t2.user_id
group by T2.username
order by count(T2.username)
;

Undo Tablespace Management

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

UNDO Tablespace filling up - pre 10G

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

1. This ticket will be raised as a result of an UNDO tablespace apparently having space pressure. This may be a false alarm. 

2. Run the query below :

a. select  ssolderrcnt "Snapshot Too Old Errors",
nospaceErrCnt "Out of Space Errors"
from  v$undostat
where ( ssolderrcnt != 0 or  nospaceErrCnt != 0 );

3. If no rows are returned, no actions are required, check source of alert.

4. If this returns one or more rows, we may need to Tune the undo retention and/or increase the size of the UNDO tablespace.

5. Your should set the UNDO_RETENTION to    NO LESS THAN     than  the result of the query below:

a. select round(( max( maxQueryLen )* 1.2 ),0) 
as "Max Query Length in Seconds"

from v$undostat;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

UNDO Tablespace filling up - 10g and later

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
col status format a15
col description format a40 word_wrap
set serverout on size 999999 lines 150 verify off pages 50 echo off trimspool on
break on report
compute Sum LABEL SUM of undosize_MB on report
compute Sum LABEL SUM of block_count on report

col undo_alloc new_value undo_alloc noprint
col undo_pct format 9999
spool undo_info.log
SELECT SUM(bytes)/1024/1024 undo_alloc
FROM DBA_data_files
WHERE tablespace_name =
(SELECT UPPER(value)
FROM v$parameter
WHERE name = 'UNDO');

ttitle left '*********** REPORT 1 - Undo Block Status and Free Space Analysis ********************' skip 2

col Description format a40 word_wrap
col undo_pct format a5 head "UNDO|PCTGE"
SELECT status,
DECODE(status,'ACTIVE','UNDO BEING USED','EXPIRED','UNDO AVAILABLE FOR USE','UNEXPIRED','UNDO BEING RETAINED TO SUPPORT UNDO RETENTION. CAN BECOME EXPIRED IF SPACE BECOMES TIGHT') Description,
count(*) block_count,
round(sum(bytes)/1024/1024) undosize_MB,
round((sum(bytes)/1024/1024)/&undo_alloc*100)||'%' undo_pct
FROM dba_undo_extents
GROUP BY status
UNION
SELECT 'FREE SPACE',
'UNDO FREE SPACE AVAILABLE FOR USE',
COUNT(*),
ROUND(SUM(bytes)/1024/1024),
round((sum(bytes)/1024/1024)/&undo_alloc*100)||'%'
FROM dba_free_space
WHERE tablespace_name =
(SELECT value
FROM v$parameter
WHERE name = 'undo_tablespace')
GROUP BY 1
/

ttitle off
ttitle left '*********** REPORT 2 - Undo Health Check ********************' skip 2
DECLARE
tablespaceName varchar2(30);
tablespaceSize number;
autoExtend boolean;
autoExtendtf char(5);
undoRetention number;
retentionGuarantee boolean;
retentionGuaranteetf char(5);
autotuneEnabled boolean;
autotuneEnabledtf char(5);
longestQuery number;
longestQueryFormatted varchar2(30);
requiredRetention number;
requiredRetentionFormatted varchar2(20);
bestPossibleRetention number;
bestPossibleRetentionFormatted varchar2(20);
requireUndoSize number;
--
problem varchar2(100);
recommendation varchar2(100);
rationale varchar2(100);
retention number;
utbsize number;
nbr number;
undoAdvisor varchar2(100);
instanceNumber number;
ret boolean;
rettf char(5);
undoRetentionFormatted varchar2(50);
Recommended_undo_size number;
--
--
BEGIN

ret := sys.dbms_undo_adv.undo_info (tableSpaceName, tableSpaceSize, autoExtend, undoRetention, retentionGuarantee);

if ret
then rettf := 'TRUE';
else rettf := 'FALSE';
end if;

if autoextend
then autoextendtf := 'TRUE';
else autoextendtf := 'FALSE';
end if;

if retentionguarantee
then retentionguaranteetf := 'TRUE';
else retentionguaranteetf := 'FALSE';
end if;

SELECT to_char(trunc( max(undoRetention)/(60*60))||' hrs ')
|| trunc(to_char( ( max(undoRetention) - (3600 * trunc(max(undoRetention)/3600) ) )/60)) ||' mins '
INTO undoRetentionFormatted
FROM Dual;

longestquery := dbms_undo_adv.longest_query(sysdate-1,sysdate);
SELECT to_char(trunc( max(longestQuery)/(60*60))||' hrs ')
|| trunc(to_char( ( max(longestQuery) - (3600 * trunc(max(longestQuery)/3600) ) )/60)) ||' mins '
INTO longestQueryFormatted
FROM Dual;

-- dbms_output.put_line(' ');
-- dbms_output.put_line('--------------------------------------------------');
-- dbms_output.put_line('* UNDO Health *');
-- dbms_output.put_line
dbms_output.put_line( '*********** REPORT 2 - Undo Health Check ********************');
dbms_output.put_line(' ');
dbms_output.put_line(' ');
nbr := dbms_undo_adv.undo_health (problem, recommendation, rationale, retention, utbsize);

SELECT DECODE(utbsize,0,tableSpaceSize,utbsize)
INTO Recommended_undo_size
FROM dual;

dbms_output.put_line (RPAD('Problem',35,CHR(0))||' : '||problem);
dbms_output.put_line (RPAD('Recommendation',35,CHR(0))||' : '||recommendation);
dbms_output.put_line (RPAD('Rationale',35,CHR(0))||' : '||rationale);
dbms_output.put_line (RPAD('undo_retention (secs)',35,CHR(0))||' : '||undoRetention);
dbms_output.put_line (RPAD('undo_retention (hrs/mins)',35,CHR(0))||' : '||undoRetentionFormatted);
dbms_output.put_line (RPAD('Guaranteed Retention',35,CHR(0))||' : '||retentionGuaranteetf);
dbms_output.put_line (RPAD('Longest Run Query (secs)',35,CHR(0))||' : '||longestQuery );
dbms_output.put_line (RPAD('Longest Run Query (hrs/mins)',35,CHR(0))||' : '||longestQueryFormatted );
dbms_output.put_line (RPAD('Recommended Undo T/S Size (MB)',35,CHR(0))||' : '||Recommended_undo_size);
-- dbms_output.put_line (RPAD('Recommended Undo T/S Size (MB)',35,CHR(0))||' : '||utbsize);
dbms_output.put_line (RPAD('Current Undo T/S Size (MB)',35,CHR(0))||' : '||tableSpaceSize);
END;
/

col SSOLDERRCNT format 999999999 HEAD "SNAPSHOT|TOO OLD|ERROR|COUNT"
col NOSPACEERRCNT format 9999999 HEAD "NOSPACE|ERROR|COUNT"
ttitle off
ttitle left '*********** REPORT 3 - Current Undo Stats ********************' skip 2

SELECT BEGIN_TIME,
END_TIME,
UNDOBLKS,
MAXQUERYLEN,
MAXQUERYID,
SSOLDERRCNT,
NOSPACEERRCNT,
TUNED_UNDORETENTION
FROM v$undostat
WHERE BEGIN_TIME> sysdate-.090
ORDER BY 1;

ttitle off
ttitle left '*********** REPORT 4 - Undo Datafiles ********************' skip 2
col filename format a65
col TSPACENAME format a12
col CURRENT_SIZE_MB format 999,999 head 'CURRENT|SIZE(MB)'
col AUTOEXTEND_UP_TO_SIZE format 9,999,999 head 'AUTOEXTEND UP|TO SIZE(MB) '

SELECT d.file_name "FILENAME",
d.bytes/1024/1024 CURRENT_SIZE_MB,
d.maxbytes/1024/1024 AUTOEXTEND_UP_TO_SIZE,
t.tablespace_name "TSPACENAME",
CASE
WHEN d.autoextensible='YES' AND d.bytes>=d.maxbytes THEN 'WORKROUND IN PLACE'
WHEN d.autoextensible='YES' AND d.bytes WHEN d.autoextensible='NO' THEN 'NO AUTOEXTEND'
END AUTOEXTEND
FROM dba_data_files d,
dba_tablespaces t,
v$parameter p
WHERE d.tablespace_name = t.tablespace_name
AND d.tablespace_name = UPPER(p.value)
AND p.name = 'undo_tablespace'
/
ttitle off

ttitle left '*************** REPORT 5 - Current Undo Activity **************' skip 2
col dummy noprint
SELECT * from dual;

col userdet heading "OSUSER : |USERNAME" format A15
col procid heading "SID:SERIAL - |SPID" format A15
col terminal heading "TTY#" format A15
col program heading "PROGRAM NAME" format A20
col status heading "STATUS" format A10
col name heading "UNDO|SEGMENT" format a15
col sql_text heading "CURRENT SQL STATEMENT" format a100 word_wrap
col used_ublk heading "USED|UNDO" format a10
col start_date heading "START DATE"
SELECT unique
RPAD(vs.osuser,13,' ')||': '|| vs.username userdet,
RPAD(vs.sid||':'|| vs.serial#,13,' ') ||'- '|| vp.spid procid,
-- vs.terminal ,
vs.program ,
vs.status ,
vr.name ,
vt.used_ublk * TO_NUMBER(ts.block_size)/1024||'K' used_ublk,
vt.start_date ,
vsql.sql_text
FROM v$rollname vr,
v$transaction vt,
v$sql vsql,
v$process vp,
v$session vs,
(
SELECT dt.block_size
FROM v$parameter vp,
dba_tablespaces dt
WHERE vp.value = dt.tablespace_name
AND vp.name = 'undo_tablespace'
) ts
WHERE vs.paddr = vp.addr
-- AND NVL(vs.sql_id,vs.prev_sql_id) = vsql.sql_id
AND vs.sql_id = vsql.sql_id(+)
AND vs.taddr = vt.addr
AND vt.xidusn = vr.usn;
ttitle off

spool off

Temporary Tablespace Management

-----------------Total/Used/Free space by TEMP TABLESPACE----------------------

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

---------------- TEMP SEGMENTS UTLIZATION///Sort Space Usage by Session in case of TEMP TABLESPACE--------

col SID_SERIAL for a20
col PROGRAM for a20
set lin 200
col OSUSER for a10
col MODULE for a10
col MODULE for a20
col TABLESPACE for a10
col USERNAME for a10
col SID_SERIAL for a10
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) sort_ops
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
S.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

----------------- SOP TEMP UTILIZATION -------------------

set lines 300
set pagesize 20000
col USERNAME format a12 word_wrap
col OSUSER format a10 word_wrap
col TABLESPACE format a10 word_wrap
col sql_text format a50 word_wrap
break on tablespace on report
compute Sum LABEL SUM of temp_size_kb on tablespace
SELECT ses.username, ses.sid, ses.serial#, ses.osuser, tu.tablespace, (tu.blocks*temp_tspace.block_size)/1024 temp_size_kb, sql.sql_text
FROM v$session ses, v$tempseg_usage tu, v$sqlarea sql,
(
SELECT tablespace_name,
block_size
FROM dba_tablespaces
WHERE contents = 'TEMPORARY'
) temp_tspace
WHERE ses.saddr = tu.session_addr
AND sql.address= ses.sql_address
AND sql.hash_value = ses.sql_hash_value
AND temp_tspace.tablespace_name = tu.tablespace
ORDER BY tu.tablespace, tu.blocks
/

------------ TEMP FILE-------------

set lin 200
col file_name for a60
select file_name , file_id, tablespace_name , status from dba_temp_files;

-----------TEMP TABLESPACE UTILIZATION--------
SELECT tablespace_name, SUM(bytes_used/1024/1024) "USED MB", SUM(bytes_free/1024/1024) "FREE MB"
FROM V$temp_space_header
GROUP BY tablespace_name;

alter database tempfile '/data/oracle/LNDBIBP1/temp01/temp/LNDBIBP1_TEMP_01.dbf' resize 6900M;

Connected Sessions:Details

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
column COMM_KILL for a60
column INS format 99
column MACHINE for a8
column USERNAME for a8
column STATUS for a8
column SID heading 'SID' for a6
column SERIAL# heading 'SERIAL#' for a7
column SQL_ID for a15
column OS_PID for a6
column RUN_SECS for a7
column SQL_TEXT for a16
column PLAN_HASH_VALUE for a10 for 9999999999999
column LOGON_TIME for a9
column OSUSER for a6
column PROGRAM for a10
column MODULE for a10
column TERMINAL for a10
column MACHINE for a10
column EVENT for a6
col PGA_MB for a8 for 99999999
col CPU_SECS for a5 for 99999
col UNDO_PCT_READ for a4 for 99999
col TEMP_MB for a8 for 99999999
column REDO_MB for a6 for 999999
column BLOCK_CHANGES for a8 for 99999999
set lines 250
set pages 100
--first line sql text for current sessions in RAC
--SELECT 'ALTER SYSTEM KILL SESSION '''||t2.SID || ',' || t2.SERIAL# || ',@ '||t2.INST_ID || ''' immediate;' COMM_KILL
--'ALTER SYSTEM KILL SESSION '''||t1.SID || ',' || t1.SERIAL# || ''' immediate;' COMM_KILL
--,
SELECT * FROM ( SELECT
SUBSTR(T3.SQL_TEXT,1,100) SQL_TEXT,T1.INST_ID INS,T1.MACHINE,T1.USERNAME
,T1.STATUS,TO_CHAR(T1.SID) SID,TO_CHAR(T1.SERIAL#) SERIAL#,T1.SQL_ID--,T3.PLAN_HASH_VALUE
,(SELECT TO_CHAR(P.SPID) OS_PID FROM GV$PROCESS P WHERE T1.PADDR=P.ADDR AND P.INST_ID = T1.INST_ID) OS_PID
--,PROCESS
,TO_CHAR(T1.LOGON_TIME,'YYYYMMDD HH24:MI:SS') LOGON_TIME
,TO_CHAR(DECODE(T1.STATUS,'ACTIVE',T1.LAST_CALL_ET,0)) RUN_SECS
,(SELECT A.VALUE/(1024*1024) PGA_MB FROM GV$SESSTAT A,GV$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#
AND UPPER(B.NAME) LIKE 'SESSION PGA MEMORY' AND A.INST_ID = B.INST_ID AND B.INST_ID = T1.INST_ID AND A.SID=T1.SID ) PGA_MB
,(SELECT SUM(ROUND (value/100)) FROM gv$sesstat ss, gv$statname st WHERE name LIKE '%CPU used by this session%'
AND ss.statistic# = st.statistic# AND ss.INST_ID = st.INST_ID AND ss.sid = T1.SID and ss.INST_ID = T1.INST_ID) CPU_SECS
,(SELECT CEIL(DECODE(CONSISTENT_CHANGES,0,0,CONSISTENT_CHANGES*100/CONSISTENT_GETS)) FROM GV$SESS_IO SIO WHERE SIO.INST_ID = T1.INST_ID AND SIO.SID=T1.SID) UNDO_PC_RD
,NVL((select TEMP_MB FROM (SELECT SID,INST_ID, SUM(MB_USED) TEMP_MB FROM (
SELECT S.sid, SUM (su.blocks) * TBS.block_size/1024/1024 mb_used,TBS.tablespace_name,S.INST_ID
FROM gv$sort_usage su, gv$session S, dba_tablespaces TBS
WHERE su.session_addr = S.saddr AND su.tablespace = TBS.tablespace_name and su.INST_ID = S.INST_ID
GROUP BY S.sid,TBS.block_size,TBS.tablespace_name,S.INST_ID ) GROUP BY SID,INST_ID ) A WHERE A.SID = T1.SID AND A.INST_ID = T1.INST_ID),0) TEMP_MB
,(SELECT CEIL(VALUE/1024/1024) FROM GV$STATNAME N, GV$SESSTAT S
WHERE UPPER(N.NAME) = 'REDO SIZE' AND S.STATISTIC# = N.STATISTIC# AND S.INST_ID = T1.INST_ID AND S.SID = T1.SID) REDO_MB
--,(SELECT (CEIL(I.BLOCK_CHANGES)) FROM GV$SESS_IO I WHERE I.INST_ID = T1.INST_ID AND I.SID = T1.SID) BLOCK_CHANGES
,T1.OSUSER,T1.EVENT--,SQL_CHILD_NUMBER
--,T1.PROGRAM--,T1.MODULE--,T1.TERMINAL
FROM
GV$SESSION T1
,GV$SQL T3
WHERE
T1.SID != SYS_CONTEXT('USERENV','SID') AND T1.INST_ID = T3.INST_ID(+) AND T1.USERNAME IS NOT NULL AND T1.SQL_ID = T3.SQL_ID(+)
AND T1.SQL_CHILD_NUMBER = T3.CHILD_NUMBER(+)
--AND T1.SQL_ID = any( '5tztp1g1p2s6b') --AND T1.PROGRAM = 'JDBC THIN CLIENT'--AND T1.MACHINE = 'SUNAMERICA-LA\WHPWNORA2'
--AND T1.LAST_CALL_ET > 0
--AND T1.STATUS = 'ACTIVE'
--AND T1.SID = ANY(678)
--and T1.USERNAME is not null
--AND UPPER(T1.USERNAME) = ('DEPOTHIST01')
--AND UPPER(MACHINE) LIKE '%LONINDAPNP18%'--AND UPPER(T1.OSUSER) LIKE '%DIBYAJOTI%'
--AND T1.PROGRAM LIKE '%SQLPLUS%'--AND UPPER(T3.SQL_TEXT) LIKE '%CREATE%'--AND T3.SQL_TEXT IS NOT NULL
)

Tablespace: Free space management

set pagesize 100

set linesize 200
break on report

compute sum of free_mb on report
compute sum of act_size on report
compute sum of used_mb on report
col %used format 999.99
col %Free format 999.99
col act_size format 99,99,999.99
col free_mb format 99,99,999.99
col used_mb format 99,99,999.99
col largest_extent_MB format 99,99,999.99
col tablespace for a25

select SUBSTR(a.tablespace_name,1,20) "TABLESPACE",act_size,free_mb,
free_mb*100/act_size "%FREE",
act_size-free_mb used_MB,((act_size-free_mb)*100)/act_size "%USED",largest_extent_MB
from (SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FREE_MB,
max(bytes)/1024/1024 largest_extent_MB
FROM DBA_FREE_SPACE group by tablespace_name) a,
(SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 ACT_SIZE FROM
DBA_DATA_FILES group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name
and a.tablespace_name in ('IOB_DATA_2013')
order by 6
/

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

select file_name,sum(bytes)/1024/1024 from dba_data_files where tablespace_name='&tablespace_name' group by file_name

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
set lines 120
col FILE_NAME for a70
select file_name,bytes/1024/1024,autoextensible,MAXBYTES/1024/1024 from dba_data_files
where tablespace_name in ('IOB_DATA_2013');

alter tablespace TDM_INDX01 add datafile '/data/oracle/LNSMISP1/data37/LNSMISP1_TDM_INDX01_50.dbf' size 6G autoextend off;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
alter database datafile '/export/data/oracle/NYARADP1/data01/NYARADP1_sysaux_01.dbf' resize 9000m;

ASH and AWR Performance Tuning Scripts

Listed below are some SQL queries that are based on the Active Session History V$ View to get a current perspective of performance and the DBA_HIST_* AWR history tables for obtaining performance data pertaining to a period of time in the past.
I would like to add that these queries that have been used and customised by me based on SQL scripts.

Top Recent Wait Events

col EVENT format a60 

select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum < 6
/

Top Wait Events Since Instance Startup

col event format a60

select event, total_waits, time_waited
from v$system_event e, v$event_name n
where n.event_id = e.event_id
and n.wait_class !='Idle'
and n.wait_class = (select wait_class from v$session_wait_class
 where wait_class !='Idle'
 group by wait_class having
sum(time_waited) = (select max(sum(time_waited)) from v$session_wait_class
where wait_class !='Idle'
group by (wait_class)))
order by 3;

List Of Users Currently Waiting

col username format a12
col sid format 9999
col state format a15
col event format a50
col wait_time format 99999999
set pagesize 100
set linesize 120

select s.sid, s.username, se.event, se.state, se.wait_time
from v$session s, v$session_wait se
where s.sid=se.sid
and se.event not like 'SQL*Net%'
and se.event not like '%rdbms%'
and s.username is not null
order by se.wait_time;

Find The Main Database Wait Events In A Particular Time Interval

First determine the snapshot id values for the period in question.
In this example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.

select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;

set verify off
select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from dba_hist_active_sess_history active_session_history
where event is not null
and SNAP_ID between &ssnapid and &esnapid
group by active_session_history.event
order by 2 desc)
where rownum

Top CPU Consuming SQL During A Certain Time Period

Note – in this case we are finding the Top 5 CPU intensive SQL statements executed between 9.00 AM and 11.00 AM

select * from (
select
SQL_ID,
 sum(CPU_TIME_DELTA),
sum(DISK_READS_DELTA),
count(*)
from
DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
s.snap_id = a.snap_id
and s.begin_interval_time > sysdate -1
and EXTRACT(HOUR FROM S.END_INTERVAL_TIME) between 9 and 11
group by
SQL_ID
order by
sum(CPU_TIME_DELTA) desc)
where rownum

Which Database Objects Experienced the Most Number of Waits in the Past One Hour

set linesize 120
col event format a40
col object_name format a40

select * from 
(
  select dba_objects.object_name,
 dba_objects.object_type,
active_session_history.event,
 sum(active_session_history.wait_time +
  active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
    dba_objects
 where 
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
 group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
 order by 4 desc)
where rownum < 6;

Top Segments ordered by Physical Reads

col segment_name format a20
col owner format a10 
select segment_name,object_type,total_physical_reads
 from ( select owner||'.'||object_name as segment_name,object_type,
value as total_physical_reads
from v$segment_statistics
 where statistic_name in ('physical reads')
 order by total_physical_reads desc)
 where rownum

Top 5 SQL statements in the past one hour

select * from (
select active_session_history.sql_id,
 dba_users.username,
 sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
 dba_users
where 
active_session_history.sample_time between sysdate -  1/24  and sysdate
  and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
 group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username
 order by 4 desc )
where rownum

SQL with the highest I/O in the past one day

select * from 
(
SELECT /*+LEADING(x h) USE_NL(h)*/ 
       h.sql_id
,      SUM(10) ash_secs
FROM   dba_hist_snapshot x
,      dba_hist_active_sess_history h
WHERE   x.begin_interval_time > sysdate -1
AND    h.SNAP_id = X.SNAP_id
AND    h.dbid = x.dbid
AND    h.instance_number = x.instance_number
AND    h.event in  ('db file sequential read','db file scattered read')
GROUP BY h.sql_id
ORDER BY ash_secs desc )
where rownum

Top CPU consuming queries since past one day

select * from (
select 
 SQL_ID, 
 sum(CPU_TIME_DELTA), 
 sum(DISK_READS_DELTA),
 count(*)
from 
 DBA_HIST_SQLSTAT a, dba_hist_snapshot s
where
 s.snap_id = a.snap_id
 and s.begin_interval_time > sysdate -1
 group by 
 SQL_ID
order by 
 sum(CPU_TIME_DELTA) desc)
where rownum

Find what the top SQL was at a particular reported time of day

First determine the snapshot id values for the period in question.
In thos example we need to find the SNAP_ID for the period 10 PM to 11 PM on the 14th of November, 2012.

select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='14-NOV-2012'
and EXTRACT(HOUR FROM begin_interval_time) between 22 and 23;

select * from
 (
select
 sql.sql_id c1,
sql.buffer_gets_delta c2,
sql.disk_reads_delta c3,
sql.iowait_delta c4
 from
dba_hist_sqlstat sql,
dba_hist_snapshot s
 where
 s.snap_id = sql.snap_id
and
 s.snap_id= &snapid
 order by
 c3 desc)
 where rownum < 6 
/

Analyse a particular SQL ID and see the trends for the past day

select
 s.snap_id,
 to_char(s.begin_interval_time,'HH24:MI') c1,
 sql.executions_delta c2,
 sql.buffer_gets_delta c3,
 sql.disk_reads_delta c4,
 sql.iowait_delta c5,
sql.cpu_time_delta c6,
 sql.elapsed_time_delta c7
 from
 dba_hist_sqlstat sql,
 dba_hist_snapshot s
 where
 s.snap_id = sql.snap_id
 and s.begin_interval_time > sysdate -1
 and
sql.sql_id='&sqlid'
 order by c7
 /

Do we have multiple plan hash values for the same SQL ID – in that case may be changed plan is causing bad performance

select 
  SQL_ID 
, PLAN_HASH_VALUE 
, sum(EXECUTIONS_DELTA) EXECUTIONS
, sum(ROWS_PROCESSED_DELTA) CROWS
, trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS
, trunc(sum(ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS
from DBA_HIST_SQLSTAT 
where SQL_ID in (
'&sqlid') 
group by SQL_ID , PLAN_HASH_VALUE
order by SQL_ID, CPU_MINS;

Top 5 Queries for past week based on ADDM recommendations

/*
Top 10 SQL_ID's for the last 7 days as identified by ADDM
from DBA_ADVISOR_RECOMMENDATIONS and dba_advisor_log
*/

col SQL_ID form a16
col Benefit form 9999999999999
select * from (
select b.ATTR1 as SQL_ID, max(a.BENEFIT) as "Benefit" 
from DBA_ADVISOR_RECOMMENDATIONS a, DBA_ADVISOR_OBJECTS b 
where a.REC_ID = b.OBJECT_ID
and a.TASK_ID = b.TASK_ID
and a.TASK_ID in (select distinct b.task_id
from dba_hist_snapshot a, dba_advisor_tasks b, dba_advisor_log l
where a.begin_interval_time > sysdate - 7 
and  a.dbid = (select dbid from v$database) 
and a.INSTANCE_NUMBER = (select INSTANCE_NUMBER from v$instance) 
and to_char(a.begin_interval_time, 'yyyymmddHH24') = to_char(b.created, 'yyyymmddHH24') 
and b.advisor_name = 'ADDM' 
and b.task_id = l.task_id 
and l.status = 'COMPLETED') 
and length(b.ATTR4) > 1 group by b.ATTR1
order by max(a.BENEFIT) desc) where rownum < 6;

List Objects being accessed by a particular SID

set pagesize 300
select sid,serial#,username,status from v$session
WHERE USERNAME IS NOT NULL
order by STATUS DESC;
col sid format 999
col owner format a10
col object format a20
col type format a10
set linesize 300
/* ENTER THE SID TO CHECK */

select sid,owner,object,type
from v$access where sid = &sid
order by owner,type
;

List all recent DDL modifications

set pagesize 200 colsep ' '
col Owner format a20
col Object_Name format a30
col Last_DDL format a20
break on Owner
select Owner, Object_type
Object_Name,
Timestamp Last_DDL
from DBA_OBJECTS
where  SysDate - TO_DATE(TimeStamp,'YYYY-MM-DD:HH24:MI:SS') < 1
order by Owner, Object_Name
/

Size of db

select

(select sum(bytes)/1024/1024/1024 data_size from dba_data_files)+

(select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files)+

(select sum(bytes)/1024/1024/1024 redo_size from sys.v$log)+

(select sum(BLOCK_SIZE+FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile)"Size in GB"

from dual

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

select

(select sum(bytes)/1024/1024/1024 data_size from dba_segments)+

(select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files)+

(select sum(bytes)/1024/1024/1024 redo_size from sys.v$log)+

(select sum(BLOCK_SIZE+FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile)"Size in GB"

from dual

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

set pagesize 100

set linesize 200

break on report

compute sum of free_mb on report

compute sum of act_size on report

compute sum of used_mb on report

col %used format 999.99

col %Free format 999.99

col act_size format 99,99,999.99

col free_mb format 99,99,999.99

col used_mb format 99,99,999.99

col file_name for a60

col largest_extent_MB format 99,99,999.99

col tablespace for a25

select SUBSTR(a.tablespace_name,1,20) "TABLESPACE",act_size,free_mb,

free_mb*100/act_size "%FREE",

act_size-free_mb used_MB,((act_size-free_mb)*100)/act_size "%USED",largest_extent_MB

from (SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 FREE_MB,

max(bytes)/1024/1024 largest_extent_MB

FROM DBA_FREE_SPACE group by tablespace_name) a,

(SELECT TABLESPACE_NAME,SUM(BYTES)/1024/1024 ACT_SIZE FROM

DBA_DATA_FILES group by tablespace_name) b

where a.tablespace_name(+)=b.tablespace_name

order by 1

/

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~