Below are few commonly used scripts by Oracle DBAs
Pick up files which have been modified in the last 1 day
##################################################################################### #### Start of script for EBS 12.1.x ##################################################################################### ( # pick up files which have been modified in the last 1 day only HowManyDaysOld=1 echo "Picking up files which have been modified in the last ${HowManyDaysOld} days" set -x find $LOG_HOME/ora/10.1.3 -type f -mtime -${HowManyDaysOld} > m.tmp find $LOG_HOME/appl/admin -type f -mtime -${HowManyDaysOld} >> m.tmp find $LOG_HOME/appl/rgf -type f -mtime -${HowManyDaysOld} >> m.tmp zip -r AppsLogFiles_`hostname`_`date '+%m%d%y'`.zip -@ < m.tmp rm m.tmp ) 2>&1 | tee mzLogZip.out ##################################################################################### #### End of script #####################################################################################
List of EBS users connected to the application in last 3 hours
SELECT user_name username, description name, to_char(b.last_connect,'MM/DD/RR HH24:MI') lastconnect FROM apps.fnd_user a, (SELECT MIN (first_connect) first_connect, MAX (last_connect) last_connect, last_updated_by user_id FROM apps.icx_sessions GROUP BY last_updated_by) b WHERE a.user_id = b.user_id AND last_connect>SYSDATE-3/12 ORDER BY 3 DESC /
Locked objects in the DB
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.CLIENT_IDENTIFIER,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id order by b.client_identifier;
Number of User sessions connected on each application nodes
select icx.node_id,fnd.node_name,'Number of user sessions : ' || count( distinct icx.session_id) How_many_user_sessions
from icx_sessions icx, fnd_nodes fnd where icx.disabled_flag != 'Y'
and icx.PSEUDO_FLAG = 'N'
and icx.node_id=fnd.node_id
and (icx.last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,icx.limit_time, 0,
icx.limit_time,FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate and counter < icx.limit_connects
group by icx.node_id,fnd.node_name;
Tablespace Size
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMB from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space from dba_free_space group by tablespace_name) a, (select tablespace_name, sum(bytes)/1024/1024 as tbs_size from dba_data_files group by tablespace_name) b where a.tablespace_name(+)=b.tablespace_name ORDER BY 2 DESC;
DB Link Scripts
/*Get the details about an existing dblink
Note: Please test this in test machine before use in production
*/
dblink_details.sql
set verify off
set pages 1200
set lines 132
column owner format a20
column db_link format a30
column username format a30
column host format a20
accept link_name char Prompt 'Enter Database Link Name : '
select owner, db_link, username, host , to_char(created,'MM/DD/YYYY HH24:MI:SS') creation_date
from dba_db_links
where upper(db_link) like '%'||upper('&&link_name')||'%'
order by owner
;
exit;
clone_user.sql
set pages 0 feed off veri off lines 500
accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
accept psw prompt "Enter new user's password: "
-- Create user...
select 'create user &&newname identified by &&psw'||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile '||
profile||';'
from sys.dba_users
where username = upper('&&oldname');
-- Grant Roles...
select 'grant '||granted_role||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');
-- Grant System Privs...
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');
-- Grant Table Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname');
-- Grant Column Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');
-- Tablespace Quotas...
select 'alter user &&newname quota '||
decode(max_bytes, -1, 'UNLIMITED', ceil(max_bytes / 1024 / 1024) || 'M') ||
' on '||tablespace_name||';'
from sys.dba_ts_quotas
where username = upper('&&oldname');
-- Set Default Role...
select 'alter user &&newname default role '|| granted_role ||';'
from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES';
set pages 500 feed on veri on lines 500
SQL> SELECT sid FROM v$mystat WHERE rownum = 1;
SQL> SELECT sid FROM V$SESSION WHERE audsid = userenv('sessionid');
SQL> SELECT sid FROM V$SESSION WHERE audsid = SYS_CONTEXT('userenv','sessionid');
select sys_context('USERENV','SID') from dual;
select sid from v$mystat where rownum <=1;
select to_number(substr(dbms_session.unique_session_id,1,4),'XXXX') mysid from dual;
select distinct sid from v$mystat;
For RAC Database with Instance ID
select username,inst_id, sid, serial# FROM gV$SESSION WHERE audsid = userenv('sessionid');
DBA_DATAPUMP_JOBS This view will show the active Data Pump jobs, their state, degree of parallelism, and the number of sessions attached. col OWNER_NAME for a12 col JOB_NAME for a20 col OPERATION for a10 col JOB_MODE for a10 select * from dba_datapump_jobs; DBA_DATAPUMP_SESSIONS This view give gives the SADDR that assist in determining why a Data Pump session may be having problems. Join to the V$SESSION view for further information. SELECT * FROM DBA_DATAPUMP_SESSIONS; V$SESSION_LONGOPS This view helps determine how well a Data Pump export is doing. Basically gives you a progress indicator through the MESSAGE column. col USERNAME for a10 col opname for a20 col target_desc a20 col target_desc for a20 col MESSAGE for a50 select username,opname,target_desc,sofar,totalwork,message from V$SESSION_LONGOPS;
Frequency of Oracle log switches
Get details about how frequently redo log switching
COL DAY FORMAT a15;
COL HOUR FORMAT a4;
COL TOTAL FORMAT 999;
SELECT TO_CHAR(FIRST_TIME,’YYYY-MM-DD’) DAY,
TO_CHAR(FIRST_TIME,’HH24') HOUR,
COUNT(*) TOTAL
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,’YYYY-MM-DD’),TO_CHAR(FIRST_TIME,’HH24')
ORDER BY TO_CHAR(FIRST_TIME,’YYYY-MM-DD’),TO_CHAR(FIRST_TIME,’HH24')
ASC;
Tablespace
-- All Tablespace in database with each datafile size set line 1000 set pages 5000 col tablespace_name for a30 col file_name for a80 col free_space for 9999999 compute sum of total_space on report compute sum of free_space on report compute sum of MAX_SPACE on report break on tablespace_name on report nodup select c.tablespace_name,a.autoextensible,a.file_name,a.total_space,b.free_space, round(b.free_space/a.total_space *100,2) "Free%",a.max_space from (select file_id,file_name,sum(bytes)/1024/1024 total_space,sum(MAXBYTES)/1024/1024/1024 max_space,autoextensible from dba_data_files group by file_id,file_name,autoextensible) a,(select file_id,nvl(sum(bytes)/1024/1024,0) free_space from dba_free_space group by file_id) b, (select tablespace_name,file_id from dba_data_files) c where a.file_id=b.file_id(+) and a.file_id=c.file_id order by tablespace_name; -- Tablespace Verification set line 1000 col tablespace_name for a30 col file_name for a80 col free_space for 9999999 compute sum of total_space on report compute sum of free_space on report compute sum of max_space on report break on tablespace_name on report nodup select c.tablespace_name,a.autoextensible,a.file_name,a.total_space,b.free_space, round(b.free_space/a.total_space *100,2) "Free%",a.max_space from (select file_id,file_name,nvl(sum(bytes)/1024/1024,0) total_space,nvl(sum(MAXBYTES)/1024/1024/1024,0) max_space,autoextensible from dba_data_files group by file_id,file_name,autoextensible) a, (select file_id,nvl(sum(bytes)/1024/1024/1024,0) free_space from dba_free_space group by file_id) b, (select tablespace_name,file_id from dba_data_files where tablespace_name='&DEFAULT_TABLESPACE' ) c where a.file_id=b.file_id(+) and a.file_id=c.file_id order by tablespace_name; -- TBS with Allocation Total/Physical set linesize 132 tab off trimspool on set pagesize 105 set pause off set echo off set feedb on column "TOTAL ALLOC (MB)" format 9,999,990.00 column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00 column "USED (MB)" format 9,999,990.00 column "FREE (MB)" format 9,999,990.00 column "% USED" format 990.00 select a.tablespace_name, a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)", a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)", nvl(b.tot_used,0)/(1024*1024) "USED (MB)", (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED" from ( select tablespace_name, sum(bytes) physical_bytes, sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc from dba_data_files group by tablespace_name ) a, ( select tablespace_name, sum(bytes) tot_used from dba_segments group by tablespace_name ) b where a.tablespace_name = b.tablespace_name (+) --and (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10 --and a.tablespace_name in ('NONEED') --and a.tablespace_name not like 'UNDO%' and a.tablespace_name='&tbs' --- like 'Noneed%' order by 1 --order by 5 / -- check free space of particular tablespace -- save the below script in file "free_space.sql" and then execute set lines 132 set pages 100 verify off clear break clear compute ttitle center 'Space Usage on Tablespaces' skip 1 center '*****************************************' skip 2 accept tablespace_name char prompt 'Enter Tablespace Name :' select a.TABLESPACE_NAME, round(a.bytes_used/(1024*1024),2) TOTAL_SPACE_IN_MB, round(b.bytes_free/(1024*1024),2) FREE_SPACE_IN_MB, round(b.smallest/(1024*1024),2) min_size_in_MB, round(b.largest/(1024*1024),2) max_size_in_MB, round(((a.bytes_used-b.bytes_free)/a.bytes_used)*100,2) percent_used from ( select TABLESPACE_NAME, sum(bytes) bytes_used from dba_data_files group by TABLESPACE_NAME ) a, ( select TABLESPACE_NAME, sum(BYTES) bytes_free, min(BYTES) smallest, max(BYTES) largest from dba_free_space group by TABLESPACE_NAME ) b where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+) and a.tablespace_name = decode('&tablespace_name',null,a.tablespace_name,'&tablespace_name') order by ((a.BYTES_used-b.BYTES_free)/a.BYTES_used) desc; exit; eg: SQL> @free.sql Enter Tablespace Name :SYSTEM Space Usage on Tablespaces ***************************************** TABLESPACE_NAME TOTAL_SPACE_IN_MB FREE_SPACE_IN_MB MIN_SIZE_IN_MB MAX_SIZE_IN_MB PERCENT_USED ------------------------------ ----------------- ---------------- -------------- -------------- ------------ SYSTEM 710 9.38 .38 9 98.68 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@rac2 ~]$ -- Check all tablespace size in the database set linesize 150 column tablespace_name format a20 heading 'Tablespace' column sumb format 999,999,999 column extents format 9999 column bytes format 999,999,999,999 column largest format 999,999,999,999 column Tot_Size format 999,999 Heading 'Total| Size(Mb)' column Tot_Free format 999,999,999 heading 'Total Free(MB)' column Pct_Free format 999.99 heading '% Free' column Chunks_Free format 9999 heading 'No Of Ext.' column Max_Free format 999,999,999 heading 'Max Free(Kb)' set echo off PROMPT FREE SPACE AVAILABLE IN TABLESPACES select a.tablespace_name,sum(a.tots/1048576) Tot_Size, sum(a.sumb/1048576) Tot_Free, sum(a.sumb)*100/sum(a.tots) Pct_Free, sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free from ( select tablespace_name,0 tots,sum(bytes) sumb, max(bytes) largest,count(*) chunks from dba_free_space a group by tablespace_name union select tablespace_name,sum(bytes) tots,0,0,0 from dba_data_files group by tablespace_name) a group by a.tablespace_name order by pct_free; eg: Tablespace (TBS) Name File Count Size(MB) Free(MB) Used(MB) Max Ext(MB) % Free ---------------------------------------- ---------- ---------- ---------- ---------- ----------- ---------- SYSAUX 1 540 29 511 32767.9844 5.37037037 SYSTEM 1 710 9.37 700.62 32767.9844 1.32042254 UNDOTBS1 1 30 6.06 23.93 32767.9844 20.2083333 USERS 1 1024 846.37 177.62 32767.9844 82.6538086 SQL>
col allocated for 999,999.999
col free for 999,999.999
col used for 999,999.999
select
( select sum(bytes)/1024/1024 from dba_data_files
where tablespace_name like 'UND%' ) Toal_allocated_in_MB,
( select sum(bytes)/1024/1024 from dba_free_space
where tablespace_name like 'UND%') free_in_MB,
( select sum(bytes)/1024/1024 from dba_undo_extents
where tablespace_name like 'UND%') USed_in_MB
from dual
/
eg:
TOAL_ALLOCATED_IN_MB FREE_IN_MB USED_IN_MB
-------------------- ---------- ----------
110 31.0625 77.9375
script 1: column used_MBytes format 999,999 column free_Mbytes format 999,999 column total_MBytes format 999,999 column collect_time format A15 select to_char(sysdate,'DD-MON-RR:HH24:MI') collect_time ,round(used_blocks*8192/1024/1024,0) used_Mbytes ,round(free_blocks*8192/1024/1024,0) free_Mbytes ,round(total_blocks*8192/1024/1024,0) total_Mbytes from V$sort_segment where tablespace_name like '%TEMP%' / eg: COLLECT_TIME USED_MBYTES FREE_MBYTES TOTAL_MBYTES --------------- ----------- ----------- ------------ 17-JUL-16:17:23 5 24 29 script 2: set lines 180 col FreeSpaceGB format 999,999 col UsedSpaceGB format 999,999 col TotalSpaceGB format 999,999 col host_name format a30 col tablespace_name format a30 select tablespace_name, (free_blocks*8)/1024 FreeSpaceMB, (used_blocks*8)/1024 UsedSpaceMB, (total_blocks*8)/1024 TotalSpaceMB, i.instance_name,i.host_name from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where tablespace_name='&TEMPTBS' and contents='TEMPORARY') and i.inst_id=ss.inst_id; eg: TABLESPACE_NAME FREESPACEMB USEDSPACEMB TOTALSPACEMB INSTANCE_NAME HOST_NAME ------------------------------ ----------- ----------- ------------ ---------------- ---------------------- TEMP 24 5 29 orcl rac1.rajasekhar.com script 3: SELECT TABLESPACE_NAME, TABLESPACE_SIZE/1024/1024 as TABLESPACE_SIZE_MB, ALLOCATED_SPACE/1024/1024 as ALLOCATED_SPACE_MB, FREE_SPACE/1024/1024 as FREE_SPACE_MB FROM dba_temp_free_space; TABLESPACE_NAME TABLESPACE_SIZE_MB ALLOCATED_SPACE_MB FREE_SPACE_MB ------------------------------ ------------------ ------------------ ------------- TEMP 30 30 29 Script 4: -- Temp TBS size for each datafile. compute sum of Max on report break on tablespace_name on report nodup select tablespace_name,file_name,autoextensible,(maxbytes)/1024/1024/1024 Max,sum(bytes)/1024/1024/1024 from dba_temp_files where tablespace_name='&TEMP_TBS' group by tablespace_name,file_name,autoextensible,maxbytes order by tablespace_name; Script 5: Which session using how much TEMP space COLUMN tablespace FORMAT A25 COLUMN temp_size FORMAT A20 COLUMN sid_serial FORMAT A20 COLUMN username FORMAT A20 COLUMN program FORMAT A40 SET LINESIZE 200 SELECT a.sid||','||a.serial# AS sid_serial,b.sql_id,b.segtype, NVL(a.username, '(oracle)') AS username, a.program, b.tablespace,ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size FROM gv$session a, gv$sort_usage b, gv$parameter p WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr and b.TABLESPACE='&TEMP_TABLESPACE' ORDER BY b.tablespace, b.blocks / -- Database default temp SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; col file_name for a45 select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files order by file_name; select username,default_tablespace,temporary_tablespace from dba_users order by temporary_tablespace; Temp tablespace used by user select srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks, a.sid, a.serial#, a.username, a.osuser, a.status from v$session a, v$sort_usage srt where a.saddr = srt.session_addr order by srt.tablespace, srt.segfile#, srt.segblk#, srt.blocks; Add temp file select FILE_NAME, TABLESPACE_NAME from dba_temp_files; alter tablespace temp add tempfile '' size 1800M; alter tablespace temp add tempfile '+DATAX' size 1800M; resize tempfile select FILE_NAME, TABLESPACE_NAME from dba_temp_files; alter database tempfile '' resize 500M; alter database tempfile '' autoextend on maxsize 1G; Verify Alert log show parameter background cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace ls -ltr | tail view alert_orcl.log shift + G ---> to get the tail end... ?ORA-1652 ---- to search of the error... shift + N ---- to step for next reported error...
Identify OS process ID based on database SID
- PID : Oracle process identifier - SPID: Operating system process identifier col sid format 999999 col username format a20 col osuser format a15 select a.sid, a.serial#,a.username, a.osuser, b.spid from gv$session a, gv$process b where a.paddr= b.addr and a.sid='&sid' order by a.sid;
Identify database SID based on OS Process ID
- PID : Oracle process identifier - SPID: Operating system process identifier col sid format 999999 col username format a20 col osuser format a15 select b.spid,a.sid, a.serial#,a.username, a.osuser from gv$session a, gv$process b where a.paddr= b.addr and b.spid='&spid' order by b.spid;
11. Find out Index Name and Column Name for Table
DBA_INDEXES gives just index names associated with the table and DBA_IND_COLUMNS gives the associated columns and their order. SQL> column table_owner format a20 column table_name format a25 column index_name format a25 column column_name format a25 select owner, table_owner,TABLE_NAME,index_name from dba_indexes where table_name='COSTS';SQL> SQL> SQL> SQL> 2 3 OWNER TABLE_OWNER TABLE_NAME INDEX_NAME ------------------------------ -------------------- ------------------------- ------------------------- EOPS EOPS COSTS COSTS_PROD_BIX EOPS EOPS COSTS COSTS_TIME_BIX SH SH COSTS COSTS_TIME_BIX SH SH COSTS COSTS_PROD_BIX SQL> Select TABLE_OWNER, table_name, index_name, column_name, COLUMN_POSITION FROM dba_ind_columns Where TABLE_OWNER='SH' AND table_name='COSTS' AND INDEX_NAME='COSTS_PROD_BIX' Order by TABLE_OWNER, table_name, column_name; 2 3 4 5 6 TABLE_OWNER TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION -------------------- ------------------------- ------------------------- ------------------------- --------------- SH COSTS COSTS_PROD_BIX PROD_ID 1 SQL>
Find out total file size of a Oracle database SQL> select b.name, ( select sum(bytes)/1024/1024/1024 DF_size from dba_data_files ) + ( select nvl(sum(bytes),0)/1024/1024/1024 TF_size from dba_temp_files ) + 2 3 4 ( select sum(bytes)/1024/1024/1024 RLF_size from sys.v_$log ) + 5 ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 CF_size from v$controlfile) "Size in GB" 6 from dual, (select name from v$database ) b; NAME Size in GB --------- ---------- CAT 5.36999512 SQL> Find out the data occupied size for an Oracle database SQL> SELECT name,sum(bytes)/1024/1024/1024 AS "Size in GB" FROM dba_segments, (select name from v$database) group by name; 2 NAME Size in GB --------- ---------- CAT 2.37426758 SQL>
13. SQL Query to Get Hostname, IP Address and Terminal from Oracle Database
A. Using sys_context SQL> SELECT SYS_CONTEXT ('USERENV', 'SERVER_HOST') from dual; SYS_CONTEXT('USERENV','SERVER_HOST') ----------------------------------------- rac2 SQL> SELECT SYS_CONTEXT('USERENV','HOST') FROM dual; SYS_CONTEXT('USERENV','HOST') ---------------------------------------- rac2.rajasekhar.com SQL> B. Using v$instance SQL> select host_name from v$instance; HOST_NAME --------------------------------------- rac2.rajasekhar.com SQL> C. UTL_INADDER.get_host_name SQL> SELECT UTL_INADDR.get_host_name FROM dual; GET_HOST_NAME -------------------------------------------------------------------------------- rac2.rajasekhar.com SQL> D. UTL_INADDR.get_host_address SQL> SELECT UTL_INADDR.get_host_address from dual; GET_HOST_ADDRESS ------------------------------------- 192.168.2.102 SQL> E. Query to find out TERMINAL SQL> SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual; SYS_CONTEXT('USERENV','TERMINAL') ----------------------------------- pts/2 SQL>
14. Archive Log Generation Report Thread Wise Daily
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
SELECT TRUNC(completion_time) "Generation Date" , thread#,count(*)
FROM gv$archived_log
GROUP BY TRUNC(completion_time),thread#
ORDER BY TRUNC(completion_time);
select trunc(completion_time) rundate
,count(*) logswitch
,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)"
from gv$archived_log
group by trunc(completion_time)
order by 1;
SELECT Start_Date, Start_Time, Num_Logs,
Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),2) AS Mbytes, Vdb.NAME AS Dbname
FROM (SELECT To_Char(Vlh.First_Time, 'YYYY-MM-DD') AS Start_Date,
To_Char(Vlh.First_Time, 'HH24') || ':00' AS Start_Time, COUNT(Vlh.Thread#) Num_Logs
FROM gv$log_History Vlh
GROUP BY To_Char(Vlh.First_Time, 'YYYY-MM-DD'),
To_Char(Vlh.First_Time, 'HH24') || ':00') Log_Hist,
gv$log Vl , gv$database Vdb
WHERE Vl.Group# = 1
ORDER BY Log_Hist.Start_Date, Log_Hist.Start_Time;
17. Hourly archivelog switches
SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
v$log_history
where to_date(first_time) > sysdate -8
GROUP by
to_char(first_time,'YYYY-MON-DD'), to_date(first_time)
order by to_date(first_time)
/
18. Generating Service based ASH Report
-- Generating Service based ASH Report
select output from table(
DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML(
l_dbid =>(select dbid from v$database),
l_inst_num =>(select 1 from v$instance), --pls. replace with appropriate instance number
l_btime =>to_date('28-JAN-2016 07:05:00','dd-mon-yyyy hh24:mi:ss'),
l_etime =>to_date('28-JAN-2016 07:15:00','dd-mon-yyyy hh24:mi:ss'),
/* l_options IN NUMBER DEFAULT 0,
l_slot_width IN NUMBER DEFAULT 0,
l_sid IN NUMBER DEFAULT NULL,
l_sql_id IN VARCHAR2 DEFAULT NULL,
l_wait_class IN VARCHAR2 DEFAULT NULL,*/
l_service_hash =>(select name_hash from dba_services where name=upper(''))));
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1; 
