DR queries

DOC ID :  (Doc ID 836986.1) --- Steps to restore from incremental backup

Check the Difference :
========================

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;


SET LINES 800
SET PAGESIZE 10000
BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF GAP ON REPORT
select primary.thread#,
       primary.maxsequence primaryseq,
       standby.maxsequence standbyseq,
       primary.maxsequence - standby.maxsequence gap
from ( select thread#, max(sequence#) maxsequence
       from v$archived_log
       where archived = 'YES'
         and resetlogs_change# = ( select d.resetlogs_change# from v$database d )
       group by thread# order by thread# ) primary,
     ( select thread#, max(sequence#) maxsequence
       from v$archived_log
       where applied = 'YES'
         and resetlogs_change# = ( select d.resetlogs_change# from v$database d )
       group by thread# order by thread# ) standby
where primary.thread# = standby.thread#;
===============================================================================================================================================

Primay:

select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread#;

 

Standby:

select thread#,max(sequence#) from gv$archived_log where applied='YES' group by thread#;

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

SELECT dest_id , SEQUENCE# , applied FROM gv$archived_log where SEQUENCE#  > ( SELECT MAX(SEQUENCE#) - 10 FROM  gv$archived_log ) ORDER BY SEQUENCE#;

CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

SELECT NAME, CREATOR, SEQUENCE#, APPLIED, COMPLETION_TIME FROM V$ARCHIVED_LOG where NAME='+RECOC1/rmsprdx/archivelog/2020_10_29/thread_2_seq_602.2162.1055114445';
===============================================================================================================================================

select status,process,sequence# from gv$managed_standby order by 2;
select unique inst_id,group#,thread#,sequence#,members from gv$log;
alter database recover managed standby database disconnect from session;
alter database recover managed standby database cancel;
   
alter database recover managed standby database using current logfile disconnect from session parallel 32;


alter system set log_archive_dest_2='SERVICE="TAIP_DDC",LGWR ASYNC NOAFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="TAIP_DDC" register net_timeout=180 valid_for=(online_logfile,primary_role)';


select NAME,LOG_MODE,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

NAME      LOG_MODE     OPEN_MODE            DATABASE_ROLE    PROTECTION_MODE
--------- ------------ -------------------- ---------------- --------------------
WHESDGD   ARCHIVELOG   MOUNTED              PHYSICAL STANDBY MAXIMUM PERFORMANCE


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

column deleted format a7
column reclaimable format a11
set linesize 120
select applied,deleted,backup_count
 ,decode(rectype,11,'YES','NO') reclaimable,count(*)
 ,to_char(min(completion_time),'dd-mon hh24:mi') first_time
 ,to_char(max(completion_time),'dd-mon hh24:mi') last_time
 ,min(sequence#) first_seq,max(sequence#) last_seq
from v$archived_log left outer join sys.x$kccagf using(recid)
where is_recovery_dest_file='YES'
group by applied,deleted,backup_count,decode(rectype,11,'YES','NO') order by min(sequence#)
/


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

select applied,deleted,decode(rectype,11,'YES','NO') reclaimable
           ,count(*),min(sequence#),max(sequence#)
     from v$archived_log left outer join sys.x$kccagf using(recid)
     where is_recovery_dest_file='YES' and name is not null
     group by applied,deleted,decode(rectype,11,'YES','NO') order by 5
/

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


Datagurad resolve gap using incremental backup : Doc ID 836986.1



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

To know the all the parameter of DG
set sqlbl on
set lines 200
col name for a30
col value for a50
select name,value from v$parameter where name in ('db_file_name_convert','log_file_name_convert',
'db_name','db_unique_name','db_domain','service_names','log_archive_config','log_archive_dest_1',
'log_archive_dest_2','db_recovery_file_dest','db_recovery_file_dest_size',
'fal_server','local_listener','instance_name');


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

SELECT thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

SQL> SELECT thread#, dest_id, gvad.status, error, fail_sequence FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;

SQL> SELECT gvi.thread#, timestamp, message FROM gv$dataguard_status gvds, gv$instance gvi WHERE gvds.inst_id = gvi.inst_id AND severity in ('Error','Fatal') ORDER BY timestamp, thread#;

set num 30
set lines 200 pages 200
col member for a85
select l.group#, l.thread#, l.sequence#, l.first_change#, l.next_change#, l.bytes, f.type, f.member, l.status from v$log l, v$logfile f where l.group#=f.group#
order by f.type, l.group#;


select thread#, max(sequence#) "Last Standby Seq Received"
           from     v$archived_log val, v$database vdb
           where    val.resetlogs_change# = vdb.resetlogs_change#
           group by thread# order by 1;

select thread#, max(sequence#) "Last Standby Seq Applied"
from     v$archived_log val, v$database vdb
where    val.resetlogs_change# = vdb.resetlogs_change#
and      val.applied in ('YES','IN-MEMORY');


select PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS from gv$managed_standby where process like '%MRP%';

tablespace scripts

 

---------- Forwarded message ---------
From: Elakkiya N <elaks2111@gmail.com>
Date: Thu, 7 Jul 2022, 19:44
Subject: Tablespace queries
To: <rajyalaxmi.pabba@gmail.com>

TABLESPACE USAGE:
*****************

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


SELECT A.TABLESPACE_NAME, A.GBYTES "ALLOCATED GB", B.GBYTES "FREE GB", A.GBYTES-B.GBYTES "USED GB"
FROM (SELECT TABLESPACE_NAME,((SUM(BYTES)/1024)/1024)/1024 "GBYTES" FROM SYS.DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME,((SUM(BYTES)/1024)/1024)/1024 "GBYTES"  FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME ORDER BY A.TABLESPACE_NAME;
/


SELECT A.TABLESPACE_NAME, A.GBYTES "ALLOCATED GB", B.GBYTES "FREE GB", A.GBYTES-B.GBYTES "USED GB"
FROM (SELECT TABLESPACE_NAME,((SUM(BYTES)/1024)/1024)/1024 "GBYTES"FROM SYS.DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME,((SUM(BYTES)/1024)/1024)/1024 "GBYTES"  FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME IN ('APPS_TS_TX_DATA', 'APPS_TS_TX_IDX',  'APPS_TS_TX_LG_DATA', 'APPS_TS_TX_LG_IDX') ORDER BY A.TABLESPACE_NAME;

=====================================================================================================================
Check the default tablespace for the user:
===========================================
SELECT distinct tablespace_name FROM dba_ts_quotas where username in ('APPLSYS','GL','HR','AR','INV','ONT');


SELECT DBMS_METADATA.GET_DDL('USER','APPS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', 'APPS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', 'APPS') FROM dual;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', 'APPS') FROM dual;


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


select file_name, bytes/1024/1024/1024/1024,maxbytes/1024/1024/1024/1024,AUTOEXTENSIBLE from dba_data_files where tablespace_name='OBIDWH';

select file_name, bytes/1024/1024/1024,maxbytes/1024/1024/1024,AUTOEXTENSIBLE from dba_data_files where tablespace_name='DATA';

select file_id,file_name,bytes/1024/1024,maxbytes/1024/1024,AUTOEXTENSIBLE from dba_data_files where tablespace_name='DATA';

select file_name, (bytes)/1024/1024,(maxbytes)/1024/1024,AUTOEXTENSIBLE from dba_data_files where tablespace_name='DATA';

select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by from dba_data_files order by file_id;



select name,total_mb/1024/1024,free_mb/1024/1024 from v$asm_diskgroup;

select name,total_mb/1024,free_mb/1024 from v$asm_diskgroup;

alter database datafile '+DATAC1/MOAP1/1F301CF7FCEA9797E053354D840AF738/DATAFILE/system.1028.930032153' autoextend on maxsize 32767M;

alter database datafile '+DATAC1/simdev/datafile/archive_data.3123.1077985245' resize 6g;


alter database datafile '+DATAC1/CHNSINET/23B495A271726812E053354D840A9EA2/DATAFILE/data_error.3980.89487' autoextend on maxsize 1900g

alter tablespace SYSTEM add datafile '+DATAC1' autoextend on maxsize 32767M;


alter tablespace CMX_DATA add datafile '+DATA' size 1g autoextend on maxsize 31g;

select bytes/1024/1024/1024 from sys.sm$ts_avail where tablespace_name='USERS';
select bytes/1024/1024/1024 from sys.sm$ts_free where tablespace_name='USERS';
select bytes/1024/1024/1024 from sys.sm$ts_used where tablespace_name='USERS';


select 'alter database datafile '||file_name||' '||' autoextend on;' from dba_data_files;

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

COLUMN tablespace_name HEADING Tablespace|Name FORMAT a30
COLUMN totalspace HEADING Total|Space FORMAT 99999999
COLUMN free HEADING Free FORMAT 99999999.99
COLUMN Percent_free HEADING Percent|Free FORMAT 99999999.99
set lines 500
set pages 500
select a.tablespace_name ,c.bytes/1024/1024 TOTALSPACE , round((b.bytes/1024/1024),2) FREE,
100-round((b.bytes/c.bytes)*100,2)  Percent_Used, round((b.bytes/c.bytes)*100,2)  Percent_free from
dba_tablespaces a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space GROUP BY tablespace_name ) b,
( select count(1) datafiles, SUM(bytes) bytes,
tablespace_name from dba_data_files GROUP BY tablespace_name ) c
where
b.tablespace_name (+) = a.tablespace_name
AND
c.tablespace_name (+) = a.tablespace_name
--and a.tablespace_name='
--and b.tablespace_name='
--and c.tablespace_name='
--and round((b.bytes/c.bytes)*100,2)<6
ORDER BY ((c.bytes-b.bytes)/c.bytes) DESC
/

select
tablespace_name
,tablespace_size/1024/1024/1024 gb_size
,allocated_space/1024/1024/1024 gb_alloc
,free_space/1024/1024/1024 gb_free
from dba_temp_free_space;

UNDO TABLESPACE USAGE:
=====================

select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB
from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
from dba_data_files a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name
and b.contents = 'UNDO'
group by b.tablespace_name) a,
(select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB
from DBA_UNDO_EXTENTS c
where status <> 'EXPIRED'
group by c.tablespace_name) b
where a.tablespace_name = b.tablespace_name;


select segment_name,
round(nvl(sum(act),0)/(1024*1024*1024),3 ) "ACT GB BYTES",
round(nvl(sum(unexp),0)/(1024*1024*1024),3) "UNEXP GB BYTES",
round(nvl(sum(exp),0)/(1024*1024*1024),3) "EXP GB BYTES",
NO_OF_EXTENTS
from ( select segment_name, nvl(sum(bytes),0) act,00 unexp, 00 exp, count(*) NO_OF_EXTENTS
from DBA_UNDO_EXTENTS
where status='ACTIVE' and tablespace_name = 'UNDOTBS1'
group by segment_name
union
select segment_name,00 act, nvl(sum(bytes),0) unexp, 00 exp , count(*) NO_OF_EXTENTS
from DBA_UNDO_EXTENTS
where status='UNEXPIRED' and tablespace_name = 'UNDOTBS1'
group by segment_name
union
select segment_name, 00 act, 00 unexp, nvl(sum(bytes),0) exp, count(*) NO_OF_EXTENTS
from DBA_UNDO_EXTENTS
where status='EXPIRED' and tablespace_name = 'UNDOTBS1'
group by segment_name
) group by segment_name, NO_OF_EXTENTS order by 5 desc;


select file_id,file_name,bytes/1024/1024,maxbytes/1024/1024,AUTOEXTENSIBLE from dba_temp_files where tablespace_name='WMWHSE1_TEMPDATA';

alter database tempfile'+DATAC1/wmprd/tempfile/wmwhse1_tempdata.1342.1054643185' autoextend on maxsize 31g;

alter tablespace WMWHSE1_TEMPDATA add tempfile'+DATAC1' size 10g autoextend on maxsize 31g;


===================================================================================================================
select owner, table_name, tablespace_name
from dba_tables
where table_name='S_EVT_ACT';


                                                              Tablespace
OWNER                          TABLE_NAME                     Name
------------------------------ ------------------------------ ------------------------------
OBIDWH                         S_EVT_ACT                      OBIDWH
NWCS_ODS                       S_EVT_ACT                      CDMR2
OBIRECON                       S_EVT_ACT                      OBIDWH




select SESSION_ID,INSTANCE_ID,STATUS,TIMEOUT,NAME,ERROR_MSG from dba_resumable where STATUS<>'NORMAL';
=====================================================================================================================

DISPLAY SPACE USAGE of EACH DATAFILE:
=====================================

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN "Tablespace Name" FORMAT A20
COLUMN "File Name" FORMAT A80

SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,80) "File Name",
Round(df.bytes/1024/1024/1024,0) "Size (G)",
decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024/1024,0)) "Used (G)",
decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024/1024,0)) "Free (G)",
decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
FROM DBA_DATA_FILES DF,
(SELECT file_id,
sum(bytes) used_bytes
FROM dba_extents
GROUP by file_id) E,
(SELECT Max(bytes) free_bytes,
file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
ORDER BY df.tablespace_name,
df.file_name
/



Datafile size:
==============

col file_name for a60;
set pagesize 500;
set linesize 500;
SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb, ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

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

clear columns
 column tablespace format a30
 column total_mb format 999,999,999.99
 column used_mb format 999,999,999,999.99
 column free_mb format 999,999,999.99
 column pct_used format 999.99
 column graph format a25 heading "GRAPH (X=5%)"
 column status format a10
 compute sum of total_mb on report
 compute sum of used_mb on report
 compute sum of free_mb on report
 break on report
 set lines 200 pages 100
 select total.ts tablespace,
 DECODE(total.mb,null,'OFFLINE',dbat.status) status,
total.mb total_mb,
 NVL(total.mb - free.mb,total.mb) used_mb,
 NVL(free.mb,0) free_mb,
 DECODE(total.mb,NULL,0,NVL(ROUND((total.mb -  free.mb)/(total.mb)*100,2),100))  pct_used,
 CASE WHEN (total.mb IS NULL) THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'
 ELSE '['|| DECODE(free.mb,
null,'XXXXXXXXXXXXXXXXXXXX',
 NVL(RPAD(LPAD('X',trunc((100-ROUND( (free.mb)/(total.mb) * 100, 2))/5),'X'),20,'-'),
 '--------------------'))||']'
 END as GRAPH
 from
 (select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total,
 (select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free,
dba_tablespaces dbat
 where total.ts=free.ts(+) and
total.ts=dbat.tablespace_name and tablespace_name != 'UNDOTBS1'
 order by 6
 /
title off
 rem clear columns  
===================================================================================================================

clear columns
column tablespace format a20
column total_mb format 999,999,999,999.99
column used_mb format 999,999,999,999.99
column free_mb format 999,999,999.99
column pct_used format 999.99
column graph format a25 heading "GRAPH (X=5%)"
column status format a10
compute sum of total_mb on report
compute sum of used_mb on report
compute sum of free_mb on report
break on report
set lines 200 pages 100
select  total.ts tablespace,
        DECODE(total.mb,null,'OFFLINE',dbat.status) status,
total.mb total_mb,
NVL(total.mb - free.mb,total.mb) used_mb,
NVL(free.mb,0) free_mb,
        DECODE(total.mb,NULL,0,NVL(ROUND((total.mb - free.mb)/(total.mb)*100,2),100)) pct_used,
CASE WHEN (total.mb IS NULL) THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'
ELSE '['|| DECODE(free.mb,
                             null,'XXXXXXXXXXXXXXXXXXXX',
                             NVL(RPAD(LPAD('X',trunc((100-ROUND( (free.mb)/(total.mb) * 100, 2))/5),'X'),20,'-'),
'--------------------'))||']'
         END as GRAPH
from
(select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total,
(select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free,
        dba_tablespaces dbat
where total.ts=free.ts(+) and
      total.ts=dbat.tablespace_name
UNION ALL
select  sh.tablespace_name,
        'TEMP',
SUM(sh.bytes_used+sh.bytes_free)/1024/1024 total_mb,
SUM(sh.bytes_used)/1024/1024 used_mb,
SUM(sh.bytes_free)/1024/1024 free_mb,
        ROUND(SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free)*100,2) pct_used,
        '['||DECODE(SUM(sh.bytes_free),0,'XXXXXXXXXXXXXXXXXXXX',
              NVL(RPAD(LPAD('X',(TRUNC(ROUND((SUM(sh.bytes_used)/SUM(sh.bytes_used+sh.bytes_free))*100,2)/5)),'X'),20,'-'),
                '--------------------'))||']'
FROM v$temp_space_header sh
GROUP BY tablespace_name
order by 1
/
ttitle off
rem clear columns  
 


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

set lines 100
col name format a60


select name,floor(space_limit / 1024 / 1024) "Size MB",ceil(space_used  / 1024 / 1024) "Used MB"from v$recovery_file_dest order by name;


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

select file_name , TABLESPACE_NAME from DBA_TEMP_FILES;

To know temp table space:
************************************************************

select a.tablespace_name "TBL", a."Total SIZE", b."USED",round((b."USED"/a."Total SIZE")*100) "%USED",
round((1-(b."USED"/a."Total SIZE"))*100) "%FREE"  
from (select tablespace_name, sum(bytes)/1024/1024/1024 "Total SIZE" from dba_temp_files where tablespace_name='TEMP'
group by tablespace_name) a,(select tablespace_name, (total_blocks *(select value from v$parameter where name='db_block_size'))/1024/1024/1024 "USED"
from v$sort_segment) b where a.tablespace_name=b.tablespace_name;



SELECT a.tablespace_name,ROUND((c.total_blocks*b.block_size)/1024/1024/1024,2)
"Total Size [GB]",ROUND((a.used_blocks*b.block_size)/1024/1024/1024,2) "Used_size[GB]",
ROUND(((c.total_blocks-a.used_blocks)*b.block_size)/1024/1024/1024,2) "Free_size[GB]",
ROUND((a.max_blocks*b.block_size)/1024/1024/1024,2) "Max_Size_Ever_Used[GB]",            
ROUND((a.max_used_blocks*b.block_size)/1024/1024/1024,2) "MaxSize_ever_Used_by_Sorts[GB]" ,
ROUND((a.used_blocks/c.total_blocks)*100,2) "Used Percentage"
FROM V$sort_segment a,dba_tablespaces b,(SELECT tablespace_name,SUM(blocks)
total_blocks FROM dba_temp_files GROUP by tablespace_name) c
WHERE a.tablespace_name=b.tablespace_name AND a.tablespace_name=c.tablespace_name;



select
tablespace_name
,tablespace_size/1024/1024/1024 gb_size
,allocated_space/1024/1024/1024 gb_alloc
,free_space/1024/1024/1024 gb_free
from dba_temp_free_space;


To check the default temporary tablespace:
*********************************************

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200
SELECT *
FROM database_properties
WHERE property_name like '%TABLESPACE';


To view all the temporary tablespace:
**************************************

select TABLESPACE_NAME "TEMP TBS NAME",bytes/(1024*1024) "SIZE(MBs)",BYTES_USED/(1024*1024) "BYTES USED(MBs)",BYTES_FREE/(1024*1024) "BYTES FREE(MBs)" from sys. V_$TEMP_SPACE_HEADER,v$tempfile;

To get the DDL of temp tablespace:
**************************************
set heading off;
set echo off;
set pages 2000
set long 99999
spool tablespace_temp.sql
select dbms_metadata.get_ddl('TABLESPACE', 'tablespace_name') from dba_temp_files;


Tempfile:
************************************************************
SELECT file_id, file_name, tablespace_name, bytes/1024/1024 MB,autoextensible FROM dba_temp_files where tablespace_name='&a';


select file_name, bytes/1024/1024/1024,maxbytes/1024/1024/1024,AUTOEXTENSIBLE from dba_temp_files where tablespace_name='TEMP';

alter tablespace temp add tempfile '+DATAC1' size 31g;

alter database tempfile '+DATAC1/CODS/1F7D4164EEA249A2E053374D840AD6F3/TEMPFILE/temp.6891.952352201' autoextend on;


****************************************
'Tablespace Freespace Shortage Report':|
****************************************

Temporary Tablespace Sort Usage:
===================================

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
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
/

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

Temporary Tablespace Usage:
============================

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COL TABLESPACE_SIZE FOR 999,999,999,999
COL ALLOCATED_SPACE FOR 999,999,999,999
COL FREE_SPACE FOR 999,999,999,999
 
SELECT *
FROM   dba_temp_free_space
/

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

Sessionwise temp tablespace usage
======================================


col hash_value for a40
col tablespace for a10
col username for a15
set linesize 132 pagesize 1000
 

SELECT s.sid, s.username, u.tablespace, s.sql_hash_value||'/'||u.sqlhash hash_value, u.segtype, u.contents, u.blocks
FROM gv$session s, gv$tempseg_usage u
WHERE s.saddr=u.session_addr
order by u.blocks;

====================================================================================================
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='TEMP2'
ORDER BY b.tablespace, b.blocks;


=============================================================================================================
set lines 200
 col SID_SERIAL for a20
 col USERNAME for a20
 col OSUSER for a20
 col spid for a20
 col module for a40
 col program for a40
 col tablespace for a30

SELECT s.sid
  || ','
  || s.serial# sid_serial,
  s.username,
  s.osuser,
  p.spid,
  s.module,
  s.program,
  SUM (o.blocks) * t.block_size / 1024 / 1024 mb_used,
  o.tablespace,
  COUNT(*) sorts
FROM gv$sort_usage o,
  gv$session s,
  dba_tablespaces t,
  gv$process p
WHERE o.session_addr = s.saddr
AND s.paddr          = p.addr
AND o.tablespace     = t.tablespace_name
and s.username!='SYS'
GROUP BY s.sid,
  s.serial#,
  s.username,
  s.osuser,
  p.spid,
  s.module,
  s.program,
  t.block_size,
  o.tablespace
ORDER BY sid_serial;

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

select inst_id,sid,serial#,username,machine,to_char(logon_time,'DD-MON-YYYY HH24:MI:SS'),prev_sql_id,sql_id,status,machine from gv$session where sid=2884;

Parallel Execution Health-Checks

 Script PXHCDR.SQL: Parallel Execution Health-Checks and Diagnostics Reports (Doc ID 1460440.1)

sid from request id

==============SID from Request sid ============================

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID

session long ops

select SID,SERIAL#,SQL_ID,SQL_HASH_VALUE,TOTALWORK,SOFAR,TIME_REMAINING from v$session_longops where sid in (4892) and nvl(totalwork,0) <> nvl(sofar,0)


Session wait

select s.sid,s.serial#,s.sql_id,s.row_wait_file#,s.row_wait_obj#,o.object_name,s.event,s.seconds_in_wait,s.blocking_Session
from v$session s, dba_objects o
where s.row_wait_obj# = o.object_id(+)  and s.sid=5294;

Last analyzed :

col index_owner for a20
col table_owner for a20
col index_name for a30
col table_name for a30
col column_name for a30
select index_owner,index_name,table_name,table_owner,column_name,column_position
from dba_ind_columns where table_name=upper('OKC_K_LINES_B_N5') order by 2;


 set autot trace exp

Excecution time for given SQLID

select a.instance_number inst_id, a.snap_id,a.plan_hash_value, to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime,
abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour from (end_interval_time-begin_interval_time))*60 + extract(day from (end_interval_time-begin_interval_time))*24*60) minutes,
executions_delta executions, round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) "avg duration (sec)"
from dba_hist_SQLSTAT a, dba_hist_snapshot b
where sql_id='&sql_id' and a.snap_id=b.snap_id
and a.instance_number=b.instance_number
order by snap_id desc, a.instance_number;

AWR PERFORMANCE TUNING 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

Find The Main Database Wait Events In A Particular Time Interval

irst 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;