Importent Queries to monitor Oracle Database

col NAME for a20
col OPEN_MODE for a20
col INSTANCE_NAME for a15
col STATUS for a10
select * from
(select name,open_mode from v$database) a,
(select instance_name,host_name,status,startup_time from v$instance);

**********************************************************************
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a10
select name DB_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,version DB_VERSION,DATABASE_STATUS,DATABASE_ROLE,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

############################
datafile creation time

SELECT NAME, TO_CHAR(CREATION_TIME,'DD-MON-YYYY HH24:MI:SS') FROM V$DATAFILE;


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

set lines 300 pages 3000
select /*+ RULE CURSOR_SHARING_EXACT */
  substrb(vs1.username,1,16) "DB_USER_BLOCKING",
  decode(vs1.lockwait,null,substrb(vs1.status,1,3),'WAI') "STA",
  substrb(ltrim(to_char(vs1.inst_id,'9')||substrb(vs1.server,1,1)||substrb(to_char(vs1.sid),1,4)),1,6)
"I-SID",
  substrb(to_char(vs1.serial#),1,5) "SRL",
  substrb(vp1.spid,1,8) spid,
  substrb(vs2.username,1,16) "DB_USER_WAITING",
  decode(vs2.lockwait,null,substrb(vs2.status,1,3),'WAI') "STA",
  substrb(ltrim(to_char(vs2.inst_id,'9')||substrb(vs2.server,1,1)||substrb(to_char(vs2.sid),1,4)),1,6)
"I-SID",
  substrb(to_char(vs2.serial#),1,5) "SRL",
  substrb(vp2.spid,1,8) spid,
  lpad(substrb(decode(trunc(vl2.ctime/86400),0,
    to_char(to_date(vl2.ctime,'SSSSS'),'HH24:MI:SS'),
    to_char(trunc(vl2.ctime/86400),'B999999')),1,10),8) "WT-D/HMS"
from gv$session vs1, gv$session vs2, gv$process vp1, gv$lock vl1, gv$lock vl2,
gv$process vp2 where vl1.block != 0 and vl1.sid = vs1.sid and vl1.inst_id =
vs1.inst_id and vl1.id1 = vl2.id1 and vs1.paddr = vp1.addr(+) and vs1.inst_id =
vp1.inst_id(+) and vl2.block = 0 and vl2.sid = vs2.sid and vl2.inst_id = vs2.inst_id
and vs2.paddr = vp2.addr(+) and vs2.inst_id = vp2.inst_id(+);

col STATUS for a14
col USERNAME for a14
col OSUSER for a14
col SERVER for a14
col MACHINE for a28
col PROGRAM for a28
col MODULE for a28
col EVENT for a30
set lines 300 pages 3000
select
p.Spid,
s.SID,
s.SERIAL#,
s.STATUS,
s.USERNAME,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
s.MODULE,
to_char(s.LOGON_TIME,'DD-MM-YYYY HH24:MI:SS') Logon_Time,
s.LAST_CALL_ET/60,
s.EVENT,
s.SQL_ID,
s.seq#
from v$session s,v$process p where s.paddr=p.addr and p.spid=&spid;

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
/

select
  blocking_session B_SID,
  blocking_instance B_Inst
from v$session
where sid = &SID;


Locked Tables:
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   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;


*******************
RMAN Backup:

set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
  j.session_recid, j.session_stamp,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;


col GB for9,999
col START_TIME for a20
col end_TIME for a20
col LEVEL for 99
col operation for a10
col status for a10
select stamp,ROW_LEVEL "LEVEL",OPERATION,status,(MBYTES_PROCESSED/1024) "GB",
 to_char(START_TIME,'DD-MON-YYYY HH24:MI:SS') "START_TIME", to_char(end_TIME,'DD-MON-YYYY HH24:MI:SS') "END_TIME", object_type from v$RMAN_STATUS where OPERATION='BACKUP'  and start_time  > (sysdate - 12)
order by stamp; 


col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;


SELECT SID, SERIAL#,OPNAME,CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR  != TOTALWORK;

***************************************
set lines 300
col job_name for a33
col owner for a13
col status for a13
col ACTUAL_START_DATE for a23
col additional_info for a60
select JOB_NAME,OWNER,STATUS,additional_info,to_char(ACTUAL_START_DATE,'dd-mm-yyyy hh24:mi:ss') ACTUAL_START_DATE from dba_scheduler_job_run_details where job_name='&Job_Name' order by 4;

set lines 300 pages 3000
col what for a63
select JOB,what,BROKEN,FAILURES,to_char(LAST_DATE,'dd-mm-yyyy hh24:mi:ss') LAST_DATE,to_char(NEXT_DATE,'dd-mm-yyyy hh24:mi:ss') NEXT_DATE from dba_jobs where job=&job_id;


select client_name, status from dba_autotask_client;
col WINDOW_NAME for a23
col JOB_STATUS for a15
col JOB_INFO for a56
select distinct client_name, window_name, job_status, job_info from dba_autotask_job_history where job_status <> 'SUCCEEDED' order by 1,2;


exec dbms_scheduler.run_job('REFRESH_EAC_RAWRECIPIENT_MV',TRUE);

EXEC DBMS_JOB.BROKEN(52,TRUE);
EXEC DBMS_JOB.RUN(52);
EXEC DBMS_JOB.BROKEN(52,FALSE);
EXEC DBMS_iJOB.BROKEN(52,FALSE); => You remove the broken status


select dbms_metadata.get_ddl('PROCOBJ', '&JOB_NAME') from dual;

**************************************
col tablespace for a12

SELECT   A.tablespace_name tablespace, D.gb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024/1024  Gb_used,
         D.gb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 /1024  gb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 /1024 gb_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.gb_total;


Session using temporary space:
set lines 300 pages 3000
col SID_SERIAL for a12
col USERNAME for a12
col OSUSER for a8
col SPID for a12
col MODULE for a23
col program for a23
SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid,S.sql_id,
         S.module, S.program,S.machine,
         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.sql_id,S.module,
         S.program, S.machine,TBS.block_size, T.tablespace
ORDER BY sid_serial;

********************************************

**********************
SELECT o.name object_name, u.name object_owner FROM obj$ o, user$ u WHERE o.obj# = &job_id and o.owner# = u.user#;

*********************
FRA Space Check:

SELECT
  ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS TOTAL_GB,
  ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS USED_GB,
  ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS RECLAIMABLE_GB,
  SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED
FROM
  V$RECOVERY_FILE_DEST A,
  V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
  SPACE_LIMIT,
  SPACE_USED ,
  SPACE_RECLAIMABLE ;
*********************
Get Value of Hidden Parameter
col "Parameter" for a23
col "Session Value" for a26
col "Instance Value" for a34
SELECT a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '%_b_tree_bitmap_plans%';

OR

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300

COLUMN ksppinm FORMAT A50
COLUMN ksppstvl FORMAT A50

SELECT
  ksppinm,
  ksppstvl
FROM
  x$ksppi a,
  x$ksppsv b
WHERE
  a.indx=b.indx
AND
  substr(ksppinm,1,1) = '_'
ORDER BY ksppinm
/
***********************************************
check object details

set lines 300
col object_name for a25
col object_type for a18
col owner for a16
col status for a19

select object_name,object_type,owner,status from dba_objects where object_name='&OBJ';
***********************************************
Find Multiple SQL Plans for a SQL statement:

The following script will identify those SQL statement that have more than one execution plan in the library cache.  Note that this SQL also looks into historical SQL plans (using dba_hist_sqlstat).  This is an important report for showing the effect of DDL and statistics changes on the execution plans of SQL in a controlled, production environment.

select
   vs.sid,
   vs.sql_id,
   vs.last_call_et,
   sq.plan_hash_value
from
   v$session vs,
   v$sql     sq
where
   vs.sql_id=sq.sql_id
and
   vs.sql_child_number=sq.child_number
and
   sq.plan_hash_value not in
   (select
      ss.plan_hash_value
   from
      dba_hist_sqlstat ss
   where
      ss.sql_id=sq.sql_id) and
   0 <
      (select
           count(ss.plan_hash_value)
       from
           dba_hist_sqlstat ss
   where
      ss.sql_id=sq.sql_id);
***********************************************
Materialized last refresh date :

SELECT owner, mview_name, to_char(LAST_REFRESH_DATE, 'yyyy-mm-dd hh24:mi:ss') last_refresh_date FROM dba_mviews WHERE mview_name='PROMOTION_COMBINATIONS_MV'

exec dbms_mview.refresh('&owner.&mview','F');

***********************************************
Latency :


To check the current latency use the below query :
select apply_name, trunc(((APPLY_TIME-APPLIED_MESSAGE_CREATE_TIME)*24*60)) latency
from dba_apply_progress order by 1 ;

APPLY_NAME       LATENCY
------------- ----------
CDC$A_PSTHR            3
           
To check the status of the apply process us the below query:
COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Apply|Process|Queue' FORMAT A15
COLUMN RULE_SET_NAME HEADING 'Positive|Rule Set' FORMAT A15
COLUMN STATUS HEADING 'Apply|Process|Status' FORMAT A15
COLUMN ERROR_NUMBER HEADING ‘Error Number’ FORMAT 99999999
COLUMN ERROR_MESSAGE HEADING ‘Error Message’ FORMAT A40
col ERROR_MESSAGE for a26

SELECT APPLY_NAME, QUEUE_NAME, RULE_SET_NAME ,STATUS ,ERROR_NUMBER, ERROR_MESSAGE FROM DBA_APPLY;

Apply           Apply                           Apply
Process         Process         Positive        Process
Name            Queue           Rule Set        Status          ERROR_NUMBER ERROR_MESSAGE
--------------- --------------- --------------- --------------- ------------ -------------------------
CDC$A_PSTHR     CDC$Q_PSTHR     RULESET$_336    ENABLED


To check the status of the capture process us the below query:
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Capture|Process|Queue' FORMAT A15
COLUMN RULE_SET_NAME HEADING 'Positive|Rule Set' FORMAT A15
COLUMN STATUS HEADING 'Capture|Process|Status' FORMAT A15
COLUMN ERROR_NUMBER HEADING ‘Error Number’ FORMAT 99999999
COLUMN ERROR_MESSAGE HEADING ‘Error Message’ FORMAT A40
SELECT CAPTURE_NAME, QUEUE_NAME, RULE_SET_NAME, STATUS, ERROR_NUMBER, ERROR_MESSAGE FROM DBA_CAPTURE;

In case if the apply or capture process is errored out and disabled, you can restart the process, by using the below mentioned code. But before that try to correct the condition that has caused the process to fail.

i) To restart the apply process :
SQL > exec dbms_apply_adm.start_apply(apply_name=> ‘APPLY_PROCESS_NAME’);
ii) To restart the capture process:
SQL> exec dbms_capture_adm.start_capture(capture_name=>’CAPTURE_PROCESS_NAME’);

**********************************************************************************************
Execute the below command to get the current utilization of SGA

select decode(pool,null,name,pool),round(sum(bytes)/1024/1024,4) MB from v$sgastat group by decode(pool,null,name,pool) order by 2 desc;

You can find the free space in Shared pool with the query:

SELECT POOL,NAME, ROUND(BYTES/(1024*1024),2) FREE_MB FROM V$SGASTAT WHERE POOL='shared pool' AND NAME='free memory' ORDER BY BYTES DESC;
select * from ( select name, bytes/1024/1024/1024 from v$sgastat where pool ='shared pool' order by 2 desc ) where rownum <16;

**********************************************************************************************
RMAN backup check

col STATUS format a9
col hrs format 999.99
select SESSION_KEY, INPUT_TYPE, STATUS,to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS order by session_key;

**********************************************************************************************
To get procedure names in the package...

SELECT distinct procedure_name FROM dba_procedures WHERE procedure_name in('GET_TZR','LOCALE_TS','TS_WITH_TZD','GET_TZD','SESSIONTIMEZONE_TS','LOG_ERROR','SEND_MAIL','ISNULL','ISNULLRETURNINT','UPDATE_INTERFACE_XML','LOG_INTERFACE_XML','GET_REFERENCE_FROM_OBJECT_TYPE','CALCULATE_DISTANCE','GET_CODE_FROM_STATUS_ID','DELIMSTRING_TO_TABLE','TABLE_TO_DELIMSTRING','WRAP_TEXT','DELIMSTRING_TO_VARRAY','BOOL_TO_STRING');

**********************************************************************************************
To check locked objects in the database...

select c.owner,c.object_name,c.object_type,b.sid,b.serial#,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;

**********************************************************************************************
Reclaim unused space from datafiles:

set lines 168 pages 5000
SELECT
  '/* '||to_char(CEIL((f.blocks-e.hwm)*(f.bytes/f.blocks)/1024/1024),99999999)||' Freeable MB */ ' ||'alter database datafile '''||file_name||''' resize '||CEIL(e.hwm*(f.bytes/f.blocks)/1024/1024)||'M;' SQL FROM DBA_DATA_FILES f,
  SYS.TS$ t,
  (SELECT ktfbuefno relative_fno,ktfbuesegtsn ts#,
  MAX(ktfbuebno+ktfbueblks) hwm FROM sys.x$ktfbue GROUP BY ktfbuefno,ktfbuesegtsn) e
WHERE
  f.relative_fno=e.relative_fno and t.name=f.tablespace_name and t.ts#=e.ts#
  and f.blocks-e.hwm > 1000
ORDER BY f.blocks-e.hwm DESC;

**********************************************************************************************
Kill Inactive Session:
select 'alter system disconnect session '||''''||sid||','||serial#||'''' ||' immediate;'  from v$session where status='INACTIVE' and  username='NEWGUEST' and last_call_et/60/60>2;
**********************************************************************************************
SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB",shared_pool_size_factor "Size Factor",estd_lc_time_saved "Time Saved in sec" FROM v$shared_pool_advice;

select * from  V$LIBRARY_CACHE_MEMORY;

SELECT POOL,NAME, ROUND(BYTES/(1024*1024),2) FREE_MB FROM V$SGASTAT WHERE POOL='shared pool' AND NAME='free memory'  ORDER BY BYTES DESC;

select * from ( select name, bytes/1024/1024/1024 from v$sgastat where pool ='shared pool' order by 2 desc ) where rownum <16;

select COMPONENT,INITIAL_SIZE/1024/1024 "Initial_MB",TARGET_SIZE/1024/1024 "Target_MB",FINAL_SIZE/1024/1024 "Final_MB",STATUS, to_char(start_time,'dd-mon-yyyy hh24:mi:ss') start_time from v$sga_resize_ops order by start_time;



--- Shared pool advise script
set lines  100
set pages  999
column        c1     heading 'Pool |Size(M)'
column        c2     heading 'Size|Factor'
column        c3     heading 'Est|LC(M)  '
column        c4     heading 'Est LC|Mem. Obj.'
column        c5     heading 'Est|Time|Saved|(sec)'
column        c6     heading 'Est|Parse|Saved|Factor'
column    c7     heading 'Est|Object Hits'   format 999,999,999


SELECT
   shared_pool_size_for_estimate  c1,
   shared_pool_size_factor        c2,
   estd_lc_size                   c3,
   estd_lc_memory_objects         c4,
   estd_lc_time_saved                    c5,
   estd_lc_time_saved_factor             c6,
   estd_lc_memory_object_hits            c7
FROM
   v$shared_pool_advice;

http://blog.tanelpoder.com/files/scripts/sgastatx.sql

*****************************************
Get old sql executions plan hash value details:

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','&sql_id')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
*****************************************
To get execute privilege on the particular package to user
COLUMN object FORMAT a25
COLUMN grantee FORMAT a15
SELECT b.owner || '.' || b.table_name object,b.privilege what_granted, b.grantable, a.username FROM dba_users a, dba_tab_privs b WHERE a.username = b.grantee AND privilege = 'EXECUTE' and b.grantee='&grantee' and rownum < 50 ORDER BY   1,2,3;

*****************************************
Historical table & index growth reports:
select * from
(SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE ,
    t.NAME "Tablespace", s.growth/(1024*1024*1024) "Growth in MB",
    (SELECT sum(bytes)/(1024*1024*1024)
    FROM dba_segments
    WHERE segment_name=o.object_name) "Total Size(GB)"
FROM DBA_OBJECTS o,
    ( SELECT TS#,OBJ#,
        SUM(SPACE_USED_DELTA) growth
   FROM DBA_HIST_SEG_STAT
    GROUP BY TS#,OBJ#
    HAVING SUM(SPACE_USED_DELTA) > 0
    ORDER BY 2 DESC ) s,
    v$tablespace t
WHERE s.OBJ# = o.OBJECT_ID
AND s.TS#=t.TS#
ORDER BY 6 DESC) where rownum<20;

*****************************************
PGA Memory Details....

SQL> select sum(PGA_MAX_MEM/1024/1024/1024) from v$process;

SQL> select sum(PGA_ALLOC_MEM/1024/1024/1024) from v$process;

SQL>  select sum(PGA_USED_MEM/1024/1024/1024) from v$process;

SQL> select sum(PGA_FREEABLE_MEM/1024/1024/1024) from v$process;

To check the processes which are allocating more PGA memory
set lines 500
set pages 1000
col program format a20
col module format a20
col machine format a40

select a.spid OS_PROCESS_ID,b.sid,b.serial#,b.username,b.schemaname,b.osuser,b.machine,b.status,b.server,
b.program,b.module,round(a.PGA_ALLOC_MEM/1024/1024,2) "Currecn PGA Allocated",round(a.PGA_USED_MEM/1024/1024,2)"Currect PGA memory in use"
from v$process a,v$session b where  a.addr=b.PADDR
order by a.PGA_ALLOC_MEM;

*****************************************
Check Hugr Transactions using rollback seg:(STATE=RECOVERING, TOGO=127935)

select                                                             
      inst_id,                                                         
      state,                                                           
      undoblocksdone,                                                     
      undoblockstotal,                                                     
      to_char((undoblocksdone/greatest(1,undoblockstotal))*100,'999.99') pct_done                                                     
    from                                               
      gv$fast_start_transactions                                                 
      where state<>'RECOVERED'                                               
    order by 1,2;   
*****************************************
To check locked object in the database:
SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name FROM V$Locked_Object A, All_Objects B WHERE A.Object_ID = B.Object_ID;
*****************************************
Session using Undo tablespace

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,NVL(s.username, 'None') orauser,s.program,r.name undoseg,t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo" FROM sys.v_$rollname r,sys.v_$session s,sys.v_$transaction t,sys.v_$parameter x WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size' order by 5 asc;
*****************************************
Query Execution:
set lines 200
col sql_profile format a35
col PARSING_SCHEMA_NAME format a15
col ROWS_PROCESSED_PER_EXECUTION heading rppe format 9999
col INSTANCE_NUMBER heading ins# format 9999
col PARSING_SCHEMA heading schema format a12
SELECT snap_id,
(SELECT to_char(begin_interval_time,'DD-MON-RRRR HH24:MI:SS') FROM dba_hist_snapshot
WHERE instance_number = a.instance_number AND snap_id = a.snap_id) begin_time,
(SELECT to_char(end_interval_time,'DD-MON-RRRR HH24:MI:SS') FROM dba_hist_snapshot
WHERE instance_number = a.instance_number AND snap_id = a.snap_id) end_time,
instance_number, parsing_schema_name, sql_id, plan_hash_value, sql_profile,
executions_delta,
ROUND ((elapsed_time_delta / DECODE (executions_delta, 0, 1, executions_delta)) /1000000,2) per_elapsed_sec,ROUND ((rows_processed_delta / DECODE (executions_delta, 0, 1, executions_delta)),2) rows_processed_per_execution FROM dba_hist_sqlstat a WHERE sql_id = 'djw1z4ynqbj4w' and snap_id in (select snap_id from dba_hist_snapshot where begin_interval_time between
to_date('01-JUN-2015 05:01:00','DD-MON-YYYY HH24:MI:SS') and to_date('09-AUG-2016 15:10:10','DD-MON-YYYY HH24:MI:SS'))
ORDER BY snap_id ASC
/
*****************************************
select 'alter system kill session '||''''||sid||','||serial#||'''' ||' immediate;'  from v$session  where username like '%OV_DEV% and username not in(' ','SYS','SYSTEM') and status='INACTIVE';
*****************************************
UNDO Advisory Query:
col "ACTUAL UNDO SIZE [MByte]" for 999999999
col "UNDO RETENTION [Sec]" for a20
col "OPTIMAL UNDO RETENTION [Sec]" for 999999999
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MB]",SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",(TO_NUMBER(e.value) * TO_NUMBER(f.value) *g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE [MB]" FROM (SELECT SUM(a.bytes) undo_size FROM v$datafile a,v$tablespace b,dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts#) d,v$parameter e, v$parameter f,(SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size';

SELECT tablespace_name, retention FROM dba_tablespaces;

https://tamimdba.wordpress.com/tag/undo_retention/
*****************************************
select sum(undoblks)*8192/1024/1024 sum_blks from v$undostat where begin_time >= sysdate -1;
*****************************************
Undo Tablespace:

column sum_in_mb format 999999.99;
select tablespace_name tablespace, status, sum(bytes)/1024/1024 sum_in_mb, count(*) counts from dba_undo_extents group by tablespace_name, status order by 1,2;
*****************************************
select dbms_metadata.get_ddl( 'USER', 'APPS_READ' ) || '/' from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'APPS_READ' ) || '/' from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'APPS_READ' ) || '/' from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'APPS_READ' ) || '/' from dual;
select 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||decode(ADMIN_OPTION,'YES',' WITH ADMIN OPTION','')||';' from DBA_ROLE_PRIVS where grantee='PAYROLL';
select 'GRANT '||PRIVILEGE||' TO '||GRANTEE|| decode(ADMIN_OPTION,'YES',' WITH ADMIN OPTION','')||';' from DBA_SYS_PRIVS where grantee='PAYROLL';
select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE|| decode(GRANTABLE,'YES',' WITH GRANT OPTION','')||';' from DBA_TAB_PRIVS where grantee='PAYROLL';

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','KINTANARML') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','KINTANARML') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','KINTANARML') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','KINTANARML') FROM DUAL;
set line 200 pages 444
set long 9999
set longchuncksize 99999999
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'KINTANARML' ) || '/' from dual;

*****************************************
Heavy Archivelog Generation:
SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'9999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'9999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'9999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'9999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'9999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'9999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'9999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'9999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'9999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'9999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'9999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'9999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'9999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'9999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'9999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'9999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'9999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'9999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'9999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'9999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'9999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'9999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'9999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'9999') "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)
/

set pages 1000
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated from v$archived_log
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

set pages 1000
select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;
*****************************************
UNDO Stat:
set lines 300 pages 3000
select
   to_char(begin_time,'MM/DD HH24:MI') begin_time,
   undoblks,
   maxquerylen,
   unxpstealcnt,
   expstealcnt,
   nospaceerrcnt,
   ssolderrcnt
from
   v$undostat;
 
select
   sql_text
from
   dba_hist_sqltext
where
  sql_id in
  (select distinct maxquerysqlid from
    (select * from
      (select
        snap_id,
        maxquerylen,
        undoblks,
        maxquerysqlid,
        to_char(begin_time,'&START_TIME_2016/11/24_11:30') begin,
        to_char(end_time,'&END_TIME_2016/11/26_11:30') end
       from
        dba_hist_undostat
       order by
        undoblks desc,
        maxquerylen desc
      )
   where rownum<11
  )
);
*****************************************

HIDDEN PARAMETERS-

/* hidden parameters */

select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode
(a.ksppity, 1,
‘boolean’, 2,
‘string’, 3,
‘number’, 4,
‘file’, a.ksppity) type,
a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm like ‘\_%’ escape ‘\’
order by
name

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


TABLESPACE SCRIPT---


set linesize 300 pages 3000
col TABLESPACE for a24
col status for a7
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
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
FROM v$temp_space_header sh
GROUP BY tablespace_name
order by 6
/

***************************************************************
W/Max QUERY-

set lines 2000
col TABLESPACE_NAME format a32
col CURRENT_SIZE_MB format 999,999,999
col USED_SPACE_MB format 999,999,999
col MAX_AUTOEXT_SIZE_MB format 99,999,999,999
col TOTAL_FREE_SPACE_MB format 99,999,999,999

break on report
compute sum of CURRENT_SIZE_MB on report
compute sum of USED_SPACE_MB on report
compute sum of MAX_AUTOEXT_SIZE_MB on report
compute sum of TOTAL_FREE_SPACE_MB on report

select a.tablespace_name, Current_Size_MB, (Current_Size_MB - Free_Space_MB) Used_Space_MB, Max_AutoExt_Size_MB,
       (Max_AutoExt_Size_MB - (Current_Size_MB - Free_Space_MB)) Total_Free_Space_MB,
       round((decode(Current_Size_MB - Free_Space_MB, 0, 1, (Current_Size_MB - Free_Space_MB) )/(Max_AutoExt_Size_MB))*100) Pct_Used
    from
    (select tablespace_name, round(sum(user_bytes)/(1024*1024)) Current_Size_MB, round(sum(decode(autoextensible, 'NO', bytes, maxbytes)/(1024*1024))) Max_AutoExt_Size_MB
     from dba_data_files
    group by tablespace_name
    ) a,
    (select tablespace_name, round(sum(bytes)/(1024*1024)) Free_Space_MB
    from dba_free_space
   group by tablespace_name) b
   where a.tablespace_name = b.tablespace_name;



***********************************************************************************************************************

set pages 200
set line 180
col file_name for a60
col TABLESPACE_NAME for a25

SELECT A.TABLESPACE_NAME,round(SUM(A.TOTS)/1024/1024) "Tot size MB",
round(SUM(A.SUMB)/1024/1024) "Tot Free MB",
round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%FREE",
100-round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%USED",
round(SUM(A.LARGEST)/1024/1024) MAX_FREE,SUM(A.CHUNKS) CHUNKS_FREE
FROM (
SELECT TABLESPACE_NAME,0 TOTS,SUM(BYTES) SUMB,
MAX(BYTES) LARGEST,COUNT(*) CHUNKS
FROM SYS.DBA_FREE_SPACE A
GROUP BY TABLESPACE_NAME
UNION
SELECT TABLESPACE_NAME,SUM(BYTES) TOTS,0,0,0
FROM SYS.DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A, V$INSTANCE B
where A.TABLESPACE_NAME=upper('&ts')
GROUP BY UPPER(B.INSTANCE_NAME),A.TABLESPACE_NAME
/


set lines 300 pages 3000
col file_name for a65
col to_char(CREATION_TIME,'DD-MON-YYYY HH24:MI:SS') for a25
select a.file_name,to_char(CREATION_TIME,'DD-MON-YYYY HH24:MI:SS'), a.file_id, a.bytes/1024/1024/1024, AUTOEXTENSIBLE from dba_data_files a,v$datafile b where b.file#=a.file_id and a.tablespace_name='MES_USERS' order by CREATION_TIME ;

step7:- Decide whether we need to add the datafile to the tablespace or resizing the existing datafile to the size which is standard size .

alter database datafile '/u1000/oradata/SFSALES/datafile/o1_mf_lob_01_892r49g0_.dbf' resize 30g;

(or) (and)
****************************************************
select name,total_mb,free_mb from v$asm_diskgroup where name=';
******************************************************

***********************************************************************************
alter tablespace name_of_DF add datafile size 4g autoextend on next 4g maxsize 32767m;
***************************************************************************************
alter tablespace name_of_DF add datafile size 32767m;

**************************************************************************
FOR TEMP TABLESPACE

alter tablespace name_of_TS add tempfile size g

SELECT tablespace_name, file_name, bytes/1024/1024 FROM dba_temp_files WHERE tablespace_name = 'TEMP'

select FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE from dba_temp_files;

select tablespace_name,sum(bytes)/1024/1024/1024 from dba_free_space group by tablespace_name where tablespace_name=' ;


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


Standby Logs Queries-



col NAME for a20
col OPEN_MODE for a20
col INSTANCE_NAME for a15
col STATUS for a10
select * from
(select name,open_mode from v$database) a,
(select instance_name,host_name,status,startup_time from v$instance);

**********************************************************************
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a10
select name DB_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,version DB_VERSION,DATABASE_STATUS,DATABASE_ROLE,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;

############################
datafile creation time

SELECT NAME, TO_CHAR(CREATION_TIME,'DD-MON-YYYY HH24:MI:SS') FROM V$DATAFILE;


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

set lines 300 pages 3000
select /*+ RULE CURSOR_SHARING_EXACT */
  substrb(vs1.username,1,16) "DB_USER_BLOCKING",
  decode(vs1.lockwait,null,substrb(vs1.status,1,3),'WAI') "STA",
  substrb(ltrim(to_char(vs1.inst_id,'9')||substrb(vs1.server,1,1)||substrb(to_char(vs1.sid),1,4)),1,6)
"I-SID",
  substrb(to_char(vs1.serial#),1,5) "SRL",
  substrb(vp1.spid,1,8) spid,
  substrb(vs2.username,1,16) "DB_USER_WAITING",
  decode(vs2.lockwait,null,substrb(vs2.status,1,3),'WAI') "STA",
  substrb(ltrim(to_char(vs2.inst_id,'9')||substrb(vs2.server,1,1)||substrb(to_char(vs2.sid),1,4)),1,6)
"I-SID",
  substrb(to_char(vs2.serial#),1,5) "SRL",
  substrb(vp2.spid,1,8) spid,
  lpad(substrb(decode(trunc(vl2.ctime/86400),0,
    to_char(to_date(vl2.ctime,'SSSSS'),'HH24:MI:SS'),
    to_char(trunc(vl2.ctime/86400),'B999999')),1,10),8) "WT-D/HMS"
from gv$session vs1, gv$session vs2, gv$process vp1, gv$lock vl1, gv$lock vl2,
gv$process vp2 where vl1.block != 0 and vl1.sid = vs1.sid and vl1.inst_id =
vs1.inst_id and vl1.id1 = vl2.id1 and vs1.paddr = vp1.addr(+) and vs1.inst_id =
vp1.inst_id(+) and vl2.block = 0 and vl2.sid = vs2.sid and vl2.inst_id = vs2.inst_id
and vs2.paddr = vp2.addr(+) and vs2.inst_id = vp2.inst_id(+);

col STATUS for a14
col USERNAME for a14
col OSUSER for a14
col SERVER for a14
col MACHINE for a28
col PROGRAM for a28
col MODULE for a28
col EVENT for a30
set lines 300 pages 3000
select
p.Spid,
s.SID,
s.SERIAL#,
s.STATUS,
s.USERNAME,
s.OSUSER,
s.MACHINE,
s.PROGRAM,
s.MODULE,
to_char(s.LOGON_TIME,'DD-MM-YYYY HH24:MI:SS') Logon_Time,
s.LAST_CALL_ET/60,
s.EVENT,
s.SQL_ID,
s.seq#
from v$session s,v$process p where s.paddr=p.addr and p.spid=&spid;

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
/

select
  blocking_session B_SID,
  blocking_instance B_Inst
from v$session
where sid = &SID;


Locked Tables:
select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   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;


*******************
RMAN Backup:

set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
  j.session_recid, j.session_stamp,
  to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
  to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;


col GB for9,999
col START_TIME for a20
col end_TIME for a20
col LEVEL for 99
col operation for a10
col status for a10
select stamp,ROW_LEVEL "LEVEL",OPERATION,status,(MBYTES_PROCESSED/1024) "GB",
 to_char(START_TIME,'DD-MON-YYYY HH24:MI:SS') "START_TIME", to_char(end_TIME,'DD-MON-YYYY HH24:MI:SS') "END_TIME", object_type from v$RMAN_STATUS where OPERATION='BACKUP'  and start_time  > (sysdate - 12)
order by stamp; 


col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;


SELECT SID, SERIAL#,OPNAME,CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR  != TOTALWORK;

***************************************
set lines 300
col job_name for a33
col owner for a13
col status for a13
col ACTUAL_START_DATE for a23
col additional_info for a60
select JOB_NAME,OWNER,STATUS,additional_info,to_char(ACTUAL_START_DATE,'dd-mm-yyyy hh24:mi:ss') ACTUAL_START_DATE from dba_scheduler_job_run_details where job_name='&Job_Name' order by 4;

set lines 300 pages 3000
col what for a63
select JOB,what,BROKEN,FAILURES,to_char(LAST_DATE,'dd-mm-yyyy hh24:mi:ss') LAST_DATE,to_char(NEXT_DATE,'dd-mm-yyyy hh24:mi:ss') NEXT_DATE from dba_jobs where job=&job_id;


select client_name, status from dba_autotask_client;
col WINDOW_NAME for a23
col JOB_STATUS for a15
col JOB_INFO for a56
select distinct client_name, window_name, job_status, job_info from dba_autotask_job_history where job_status <> 'SUCCEEDED' order by 1,2;


exec dbms_scheduler.run_job('REFRESH_EAC_RAWRECIPIENT_MV',TRUE);

EXEC DBMS_JOB.BROKEN(52,TRUE);
EXEC DBMS_JOB.RUN(52);
EXEC DBMS_JOB.BROKEN(52,FALSE);
EXEC DBMS_iJOB.BROKEN(52,FALSE); => You remove the broken status


select dbms_metadata.get_ddl('PROCOBJ', '&JOB_NAME') from dual;

**************************************
col tablespace for a12

SELECT   A.tablespace_name tablespace, D.gb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024/1024  Gb_used,
         D.gb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 /1024  gb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 /1024 gb_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.gb_total;


Session using temporary space:
set lines 300 pages 3000
col SID_SERIAL for a12
col USERNAME for a12
col OSUSER for a8
col SPID for a12
col MODULE for a23
col program for a23
SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid,S.sql_id,
         S.module, S.program,S.machine,
         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.sql_id,S.module,
         S.program, S.machine,TBS.block_size, T.tablespace
ORDER BY sid_serial;

********************************************
1)      Log into whsedbrfa01

2)      su – appflu/y0u3uck#

3)      Set profile . ./profile.appflu

4)      cd $APPFLUENT_HOME/bin

5)      execute: analyzer_daemon status

6) execute: analyzer_daemon restart

**********************
SELECT o.name object_name, u.name object_owner FROM obj$ o, user$ u WHERE o.obj# = &job_id and o.owner# = u.user#;

*********************
FRA Space Check:

SELECT
  ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS TOTAL_GB,
  ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS USED_GB,
  ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS RECLAIMABLE_GB,
  SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED
FROM
  V$RECOVERY_FILE_DEST A,
  V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
  SPACE_LIMIT,
  SPACE_USED ,
  SPACE_RECLAIMABLE ;
*********************
Get Value of Hidden Parameter
col "Parameter" for a23
col "Session Value" for a26
col "Instance Value" for a34
SELECT a.ksppinm "Parameter",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c
WHERE a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '%_b_tree_bitmap_plans%';

OR

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300

COLUMN ksppinm FORMAT A50
COLUMN ksppstvl FORMAT A50

SELECT
  ksppinm,
  ksppstvl
FROM
  x$ksppi a,
  x$ksppsv b
WHERE
  a.indx=b.indx
AND
  substr(ksppinm,1,1) = '_'
ORDER BY ksppinm
/
***********************************************
check object details

set lines 300
col object_name for a25
col object_type for a18
col owner for a16
col status for a19

select object_name,object_type,owner,status from dba_objects where object_name='&OBJ';
***********************************************
Find Multiple SQL Plans for a SQL statement:

The following script will identify those SQL statement that have more than one execution plan in the library cache.  Note that this SQL also looks into historical SQL plans (using dba_hist_sqlstat).  This is an important report for showing the effect of DDL and statistics changes on the execution plans of SQL in a controlled, production environment.

select
   vs.sid,
   vs.sql_id,
   vs.last_call_et,
   sq.plan_hash_value
from
   v$session vs,
   v$sql     sq
where
   vs.sql_id=sq.sql_id
and
   vs.sql_child_number=sq.child_number
and
   sq.plan_hash_value not in
   (select
      ss.plan_hash_value
   from
      dba_hist_sqlstat ss
   where
      ss.sql_id=sq.sql_id) and
   0 <
      (select
           count(ss.plan_hash_value)
       from
           dba_hist_sqlstat ss
   where
      ss.sql_id=sq.sql_id);
***********************************************
Materialized last refresh date :

SELECT owner, mview_name, to_char(LAST_REFRESH_DATE, 'yyyy-mm-dd hh24:mi:ss') last_refresh_date FROM dba_mviews WHERE mview_name='PROMOTION_COMBINATIONS_MV'

exec dbms_mview.refresh('&owner.&mview','F');

***********************************************
Latency :


To check the current latency use the below query :
select apply_name, trunc(((APPLY_TIME-APPLIED_MESSAGE_CREATE_TIME)*24*60)) latency
from dba_apply_progress order by 1 ;

APPLY_NAME       LATENCY
------------- ----------
CDC$A_PSTHR            3
           
To check the status of the apply process us the below query:
COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Apply|Process|Queue' FORMAT A15
COLUMN RULE_SET_NAME HEADING 'Positive|Rule Set' FORMAT A15
COLUMN STATUS HEADING 'Apply|Process|Status' FORMAT A15
COLUMN ERROR_NUMBER HEADING ‘Error Number’ FORMAT 99999999
COLUMN ERROR_MESSAGE HEADING ‘Error Message’ FORMAT A40
col ERROR_MESSAGE for a26

SELECT APPLY_NAME, QUEUE_NAME, RULE_SET_NAME ,STATUS ,ERROR_NUMBER, ERROR_MESSAGE FROM DBA_APPLY;

Apply           Apply                           Apply
Process         Process         Positive        Process
Name            Queue           Rule Set        Status          ERROR_NUMBER ERROR_MESSAGE
--------------- --------------- --------------- --------------- ------------ -------------------------
CDC$A_PSTHR     CDC$Q_PSTHR     RULESET$_336    ENABLED


To check the status of the capture process us the below query:
COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Capture|Process|Queue' FORMAT A15
COLUMN RULE_SET_NAME HEADING 'Positive|Rule Set' FORMAT A15
COLUMN STATUS HEADING 'Capture|Process|Status' FORMAT A15
COLUMN ERROR_NUMBER HEADING ‘Error Number’ FORMAT 99999999
COLUMN ERROR_MESSAGE HEADING ‘Error Message’ FORMAT A40
SELECT CAPTURE_NAME, QUEUE_NAME, RULE_SET_NAME, STATUS, ERROR_NUMBER, ERROR_MESSAGE FROM DBA_CAPTURE;

In case if the apply or capture process is errored out and disabled, you can restart the process, by using the below mentioned code. But before that try to correct the condition that has caused the process to fail.

i) To restart the apply process :
SQL > exec dbms_apply_adm.start_apply(apply_name=> ‘APPLY_PROCESS_NAME’);
ii) To restart the capture process:
SQL> exec dbms_capture_adm.start_capture(capture_name=>’CAPTURE_PROCESS_NAME’);

**********************************************************************************************
Execute the below command to get the current utilization of SGA

select decode(pool,null,name,pool),round(sum(bytes)/1024/1024,4) MB from v$sgastat group by decode(pool,null,name,pool) order by 2 desc;

You can find the free space in Shared pool with the query:

SELECT POOL,NAME, ROUND(BYTES/(1024*1024),2) FREE_MB FROM V$SGASTAT WHERE POOL='shared pool' AND NAME='free memory' ORDER BY BYTES DESC;
select * from ( select name, bytes/1024/1024/1024 from v$sgastat where pool ='shared pool' order by 2 desc ) where rownum <16;

**********************************************************************************************
RMAN backup check

col STATUS format a9
col hrs format 999.99
select SESSION_KEY, INPUT_TYPE, STATUS,to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS order by session_key;

**********************************************************************************************
To get procedure names in the package...

SELECT distinct procedure_name FROM dba_procedures WHERE procedure_name in('GET_TZR','LOCALE_TS','TS_WITH_TZD','GET_TZD','SESSIONTIMEZONE_TS','LOG_ERROR','SEND_MAIL','ISNULL','ISNULLRETURNINT','UPDATE_INTERFACE_XML','LOG_INTERFACE_XML','GET_REFERENCE_FROM_OBJECT_TYPE','CALCULATE_DISTANCE','GET_CODE_FROM_STATUS_ID','DELIMSTRING_TO_TABLE','TABLE_TO_DELIMSTRING','WRAP_TEXT','DELIMSTRING_TO_VARRAY','BOOL_TO_STRING');

**********************************************************************************************
To check locked objects in the database...

select c.owner,c.object_name,c.object_type,b.sid,b.serial#,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;

**********************************************************************************************
Reclaim unused space from datafiles:

set lines 168 pages 5000
SELECT
  '/* '||to_char(CEIL((f.blocks-e.hwm)*(f.bytes/f.blocks)/1024/1024),99999999)||' Freeable MB */ ' ||'alter database datafile '''||file_name||''' resize '||CEIL(e.hwm*(f.bytes/f.blocks)/1024/1024)||'M;' SQL FROM DBA_DATA_FILES f,
  SYS.TS$ t,
  (SELECT ktfbuefno relative_fno,ktfbuesegtsn ts#,
  MAX(ktfbuebno+ktfbueblks) hwm FROM sys.x$ktfbue GROUP BY ktfbuefno,ktfbuesegtsn) e
WHERE
  f.relative_fno=e.relative_fno and t.name=f.tablespace_name and t.ts#=e.ts#
  and f.blocks-e.hwm > 1000
ORDER BY f.blocks-e.hwm DESC;

**********************************************************************************************
Kill Inactive Session:
select 'alter system disconnect session '||''''||sid||','||serial#||'''' ||' immediate;'  from v$session where status='INACTIVE' and  username='NEWGUEST' and last_call_et/60/60>2;
**********************************************************************************************
SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB",shared_pool_size_factor "Size Factor",estd_lc_time_saved "Time Saved in sec" FROM v$shared_pool_advice;

select * from  V$LIBRARY_CACHE_MEMORY;

SELECT POOL,NAME, ROUND(BYTES/(1024*1024),2) FREE_MB FROM V$SGASTAT WHERE POOL='shared pool' AND NAME='free memory'  ORDER BY BYTES DESC;

select * from ( select name, bytes/1024/1024/1024 from v$sgastat where pool ='shared pool' order by 2 desc ) where rownum <16;

select COMPONENT,INITIAL_SIZE/1024/1024 "Initial_MB",TARGET_SIZE/1024/1024 "Target_MB",FINAL_SIZE/1024/1024 "Final_MB",STATUS, to_char(start_time,'dd-mon-yyyy hh24:mi:ss') start_time from v$sga_resize_ops order by start_time;



--- Shared pool advise script
set lines  100
set pages  999
column        c1     heading 'Pool |Size(M)'
column        c2     heading 'Size|Factor'
column        c3     heading 'Est|LC(M)  '
column        c4     heading 'Est LC|Mem. Obj.'
column        c5     heading 'Est|Time|Saved|(sec)'
column        c6     heading 'Est|Parse|Saved|Factor'
column    c7     heading 'Est|Object Hits'   format 999,999,999


SELECT
   shared_pool_size_for_estimate  c1,
   shared_pool_size_factor        c2,
   estd_lc_size                   c3,
   estd_lc_memory_objects         c4,
   estd_lc_time_saved                    c5,
   estd_lc_time_saved_factor             c6,
   estd_lc_memory_object_hits            c7
FROM
   v$shared_pool_advice;

http://blog.tanelpoder.com/files/scripts/sgastatx.sql

*****************************************
Get old sql executions plan hash value details:

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','&sql_id')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
*****************************************
To get execute privilege on the particular package to user
COLUMN object FORMAT a25
COLUMN grantee FORMAT a15
SELECT b.owner || '.' || b.table_name object,b.privilege what_granted, b.grantable, a.username FROM dba_users a, dba_tab_privs b WHERE a.username = b.grantee AND privilege = 'EXECUTE' and b.grantee='&grantee' and rownum < 50 ORDER BY   1,2,3;

*****************************************
Historical table & index growth reports:
select * from
(SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE ,
    t.NAME "Tablespace", s.growth/(1024*1024*1024) "Growth in MB",
    (SELECT sum(bytes)/(1024*1024*1024)
    FROM dba_segments
    WHERE segment_name=o.object_name) "Total Size(GB)"
FROM DBA_OBJECTS o,
    ( SELECT TS#,OBJ#,
        SUM(SPACE_USED_DELTA) growth
   FROM DBA_HIST_SEG_STAT
    GROUP BY TS#,OBJ#
    HAVING SUM(SPACE_USED_DELTA) > 0
    ORDER BY 2 DESC ) s,
    v$tablespace t
WHERE s.OBJ# = o.OBJECT_ID
AND s.TS#=t.TS#
ORDER BY 6 DESC) where rownum<20;

*****************************************
PGA Memory Details....

SQL> select sum(PGA_MAX_MEM/1024/1024/1024) from v$process;

SQL> select sum(PGA_ALLOC_MEM/1024/1024/1024) from v$process;

SQL>  select sum(PGA_USED_MEM/1024/1024/1024) from v$process;

SQL> select sum(PGA_FREEABLE_MEM/1024/1024/1024) from v$process;

To check the processes which are allocating more PGA memory
set lines 500
set pages 1000
col program format a20
col module format a20
col machine format a40

select a.spid OS_PROCESS_ID,b.sid,b.serial#,b.username,b.schemaname,b.osuser,b.machine,b.status,b.server,
b.program,b.module,round(a.PGA_ALLOC_MEM/1024/1024,2) "Currecn PGA Allocated",round(a.PGA_USED_MEM/1024/1024,2)"Currect PGA memory in use"
from v$process a,v$session b where  a.addr=b.PADDR
order by a.PGA_ALLOC_MEM;

*****************************************
Check Hugr Transactions using rollback seg:(STATE=RECOVERING, TOGO=127935)

select                                                             
      inst_id,                                                         
      state,                                                           
      undoblocksdone,                                                     
      undoblockstotal,                                                     
      to_char((undoblocksdone/greatest(1,undoblockstotal))*100,'999.99') pct_done                                                     
    from                                               
      gv$fast_start_transactions                                                 
      where state<>'RECOVERED'                                               
    order by 1,2;   
*****************************************
To check locked object in the database:
SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name FROM V$Locked_Object A, All_Objects B WHERE A.Object_ID = B.Object_ID;
*****************************************
Session using Undo tablespace

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,NVL(s.username, 'None') orauser,s.program,r.name undoseg,t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo" FROM sys.v_$rollname r,sys.v_$session s,sys.v_$transaction t,sys.v_$parameter x WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size' order by 5 asc;
*****************************************
Query Execution:
set lines 200
col sql_profile format a35
col PARSING_SCHEMA_NAME format a15
col ROWS_PROCESSED_PER_EXECUTION heading rppe format 9999
col INSTANCE_NUMBER heading ins# format 9999
col PARSING_SCHEMA heading schema format a12
SELECT snap_id,
(SELECT to_char(begin_interval_time,'DD-MON-RRRR HH24:MI:SS') FROM dba_hist_snapshot
WHERE instance_number = a.instance_number AND snap_id = a.snap_id) begin_time,
(SELECT to_char(end_interval_time,'DD-MON-RRRR HH24:MI:SS') FROM dba_hist_snapshot
WHERE instance_number = a.instance_number AND snap_id = a.snap_id) end_time,
instance_number, parsing_schema_name, sql_id, plan_hash_value, sql_profile,
executions_delta,
ROUND ((elapsed_time_delta / DECODE (executions_delta, 0, 1, executions_delta)) /1000000,2) per_elapsed_sec,ROUND ((rows_processed_delta / DECODE (executions_delta, 0, 1, executions_delta)),2) rows_processed_per_execution FROM dba_hist_sqlstat a WHERE sql_id = 'djw1z4ynqbj4w' and snap_id in (select snap_id from dba_hist_snapshot where begin_interval_time between
to_date('01-JUN-2015 05:01:00','DD-MON-YYYY HH24:MI:SS') and to_date('09-AUG-2016 15:10:10','DD-MON-YYYY HH24:MI:SS'))
ORDER BY snap_id ASC
/
*****************************************
select 'alter system kill session '||''''||sid||','||serial#||'''' ||' immediate;'  from v$session  where username like '%OV_DEV% and username not in(' ','SYS','SYSTEM') and status='INACTIVE';
*****************************************
UNDO Advisory Query:
col "ACTUAL UNDO SIZE [MByte]" for 999999999
col "UNDO RETENTION [Sec]" for a20
col "OPTIMAL UNDO RETENTION [Sec]" for 999999999
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MB]",SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",(TO_NUMBER(e.value) * TO_NUMBER(f.value) *g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE [MB]" FROM (SELECT SUM(a.bytes) undo_size FROM v$datafile a,v$tablespace b,dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts#) d,v$parameter e, v$parameter f,(SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size';

SELECT tablespace_name, retention FROM dba_tablespaces;

https://tamimdba.wordpress.com/tag/undo_retention/
*****************************************
select sum(undoblks)*8192/1024/1024 sum_blks from v$undostat where begin_time >= sysdate -1;
*****************************************
Undo Tablespace:

column sum_in_mb format 999999.99;
select tablespace_name tablespace, status, sum(bytes)/1024/1024 sum_in_mb, count(*) counts from dba_undo_extents group by tablespace_name, status order by 1,2;
*****************************************
select dbms_metadata.get_ddl( 'USER', 'APPS_READ' ) || '/' from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'APPS_READ' ) || '/' from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'APPS_READ' ) || '/' from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'APPS_READ' ) || '/' from dual;
select 'GRANT '||GRANTED_ROLE||' TO '||GRANTEE||decode(ADMIN_OPTION,'YES',' WITH ADMIN OPTION','')||';' from DBA_ROLE_PRIVS where grantee='PAYROLL';
select 'GRANT '||PRIVILEGE||' TO '||GRANTEE|| decode(ADMIN_OPTION,'YES',' WITH ADMIN OPTION','')||';' from DBA_SYS_PRIVS where grantee='PAYROLL';
select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO '||GRANTEE|| decode(GRANTABLE,'YES',' WITH GRANT OPTION','')||';' from DBA_TAB_PRIVS where grantee='PAYROLL';

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','KINTANARML') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','KINTANARML') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','KINTANARML') FROM DUAL;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','KINTANARML') FROM DUAL;
set line 200 pages 444
set long 9999
set longchuncksize 99999999
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'KINTANARML' ) || '/' from dual;

*****************************************
Heavy Archivelog Generation:
SELECT to_date(first_time) DAY,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'9999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'9999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'9999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'9999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'9999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'9999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'9999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'9999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'9999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'9999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'9999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'9999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'9999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'9999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'9999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'9999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'9999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'9999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'9999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'9999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'9999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'9999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'9999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'9999') "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)
/

set pages 1000
select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated from v$archived_log
group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

set pages 1000
select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;
*****************************************
UNDO Stat:
set lines 300 pages 3000
select
   to_char(begin_time,'MM/DD HH24:MI') begin_time,
   undoblks,
   maxquerylen,
   unxpstealcnt,
   expstealcnt,
   nospaceerrcnt,
   ssolderrcnt
from
   v$undostat;
 
select
   sql_text
from
   dba_hist_sqltext
where
  sql_id in
  (select distinct maxquerysqlid from
    (select * from
      (select
        snap_id,
        maxquerylen,
        undoblks,
        maxquerysqlid,
        to_char(begin_time,'&START_TIME_2016/11/24_11:30') begin,
        to_char(end_time,'&END_TIME_2016/11/26_11:30') end
       from
        dba_hist_undostat
       order by
        undoblks desc,
        maxquerylen desc
      )
   where rownum<11
  )
);
*****************************************

HIDDEN PARAMETERS-

/* hidden parameters */

select a.ksppinm name,
b.ksppstvl value,
b.ksppstdf deflt,
decode
(a.ksppity, 1,
‘boolean’, 2,
‘string’, 3,
‘number’, 4,
‘file’, a.ksppity) type,
a.ksppdesc description
from
sys.x$ksppi a,
sys.x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm like ‘\_%’ escape ‘\’
order by
name

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


TABLESPACE SCRIPT---


set linesize 300 pages 3000
col TABLESPACE for a24
col status for a7
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
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
FROM v$temp_space_header sh
GROUP BY tablespace_name
order by 6
/

***************************************************************
W/Max QUERY-

set lines 2000
col TABLESPACE_NAME format a32
col CURRENT_SIZE_MB format 999,999,999
col USED_SPACE_MB format 999,999,999
col MAX_AUTOEXT_SIZE_MB format 99,999,999,999
col TOTAL_FREE_SPACE_MB format 99,999,999,999

break on report
compute sum of CURRENT_SIZE_MB on report
compute sum of USED_SPACE_MB on report
compute sum of MAX_AUTOEXT_SIZE_MB on report
compute sum of TOTAL_FREE_SPACE_MB on report

select a.tablespace_name, Current_Size_MB, (Current_Size_MB - Free_Space_MB) Used_Space_MB, Max_AutoExt_Size_MB,
       (Max_AutoExt_Size_MB - (Current_Size_MB - Free_Space_MB)) Total_Free_Space_MB,
       round((decode(Current_Size_MB - Free_Space_MB, 0, 1, (Current_Size_MB - Free_Space_MB) )/(Max_AutoExt_Size_MB))*100) Pct_Used
    from
    (select tablespace_name, round(sum(user_bytes)/(1024*1024)) Current_Size_MB, round(sum(decode(autoextensible, 'NO', bytes, maxbytes)/(1024*1024))) Max_AutoExt_Size_MB
     from dba_data_files
    group by tablespace_name
    ) a,
    (select tablespace_name, round(sum(bytes)/(1024*1024)) Free_Space_MB
    from dba_free_space
   group by tablespace_name) b
   where a.tablespace_name = b.tablespace_name;



***********************************************************************************************************************

set pages 200
set line 180
col file_name for a60
col TABLESPACE_NAME for a25

SELECT A.TABLESPACE_NAME,round(SUM(A.TOTS)/1024/1024) "Tot size MB",
round(SUM(A.SUMB)/1024/1024) "Tot Free MB",
round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%FREE",
100-round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%USED",
round(SUM(A.LARGEST)/1024/1024) MAX_FREE,SUM(A.CHUNKS) CHUNKS_FREE
FROM (
SELECT TABLESPACE_NAME,0 TOTS,SUM(BYTES) SUMB,
MAX(BYTES) LARGEST,COUNT(*) CHUNKS
FROM SYS.DBA_FREE_SPACE A
GROUP BY TABLESPACE_NAME
UNION
SELECT TABLESPACE_NAME,SUM(BYTES) TOTS,0,0,0
FROM SYS.DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A, V$INSTANCE B
where A.TABLESPACE_NAME=upper('&ts')
GROUP BY UPPER(B.INSTANCE_NAME),A.TABLESPACE_NAME
/


set lines 300 pages 3000
col file_name for a65
col to_char(CREATION_TIME,'DD-MON-YYYY HH24:MI:SS') for a25
select a.file_name,to_char(CREATION_TIME,'DD-MON-YYYY HH24:MI:SS'), a.file_id, a.bytes/1024/1024/1024, AUTOEXTENSIBLE from dba_data_files a,v$datafile b where b.file#=a.file_id and a.tablespace_name='MES_USERS' order by CREATION_TIME ;

step7:- Decide whether we need to add the datafile to the tablespace or resizing the existing datafile to the size which is standard size .

alter database datafile '/u1000/oradata/SFSALES/datafile/o1_mf_lob_01_892r49g0_.dbf' resize 30g;

(or) (and)
****************************************************
select name,total_mb,free_mb from v$asm_diskgroup where name=';
******************************************************

***********************************************************************************
alter tablespace name_of_DF add datafile size 4g autoextend on next 4g maxsize 32767m;
***************************************************************************************
alter tablespace name_of_DF add datafile size 32767m;

**************************************************************************
FOR TEMP TABLESPACE

alter tablespace name_of_TS add tempfile size g

SELECT tablespace_name, file_name, bytes/1024/1024 FROM dba_temp_files WHERE tablespace_name = 'TEMP'

select FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE from dba_temp_files;

select tablespace_name,sum(bytes)/1024/1024/1024 from dba_free_space group by tablespace_name where tablespace_name=' ;