Script To Check Tablespace Data File Location

Script To Check Tablespace Data  File Location

select FILE_NAME,sum(bytes/1024/1024/1024) from dba_data_files where TABLESPACE_NAME='UNDOTBS1' group by FILE_NAME;

Scripts To Check Invalids

Scripts To Check Invalids

Number Of Invalid Objects

select object_name,status,owner from dba_objects where status='INVALID';

select owner,object_type,count(object_name) INVALIDS from dba_objects where status='INVALID' group by owner,object_type order by 1;

select count(*) from dba_objects where status='INVALID';

Script to check GSS Percentage

Script to check GSS Percentage

set lines 200
set pages 200
col SID for a10
col OPNAME for a30
col START_TIME for a25
col EST_COMPL for a25
select sid||','||serial# SID,opname,sofar,totalwork,round((sofar/totalwork)*100,2)
perc_compl, to_char(START_TIME,'DD-MON-YY HH24:MI:SS') Start_time, to_char(sysdate
+TIME_REMAINING/60/60/24,'DD-MON-YY HH24:MI:SS') est_compl from gv$session_longops where opname
like '%Statistics%' and sofar <> totalwork and totalwork !=0 order by est_compl;

Script To Check EXPDP & IMPDP Status

Script To Check EXPDP  & IMPDP Status

SELECT
OPNAME,
SID,
SERIAL#,
CONTEXT,
SOFAR,
TOTALWORK,
    ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM
V$SESSION_LONGOPS
WHERE
OPNAME in
(
select
d.job_name
from
v$session s,
v$process p,
dba_datapump_sessions d
where
p.addr=s.paddr
and
s.saddr=d.saddr
)
AND
OPNAME NOT LIKE '%aggregate%'
AND
TOTALWORK != 0
AND
SOFAR <> TOTALWORK;

_______________________________________________________-

IMPDP STATUS:

SELECT
OPNAME,
SID,
SERIAL#,
CONTEXT,
SOFAR,
TOTALWORK,
    ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM
V$SESSION_LONGOPS
WHERE
OPNAME in
(
select
d.job_name
from
v$session s,
v$process p,
dba_datapump_sessions d
where
p.addr=s.paddr
and
s.saddr=d.saddr
)
AND
OPNAME NOT LIKE '%aggregate%'
AND
TOTALWORK != 0
AND
SOFAR <> TOTALWORK;

Script to check DB Growth Size

SET LINESIZE 200
SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"
FROM    (SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE
GROUP BY FREE.P;
_________________________________________________________________________________________________

How to find database growth on a Monthly





select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB
from   v$datafile
group by  to_char(CREATION_TIME,'RRRR'),   to_char(CREATION_TIME,'MM')
order by   1, 2;

YEAR MO              GB
---- -- ---------------
2011 0              75
2012 6            4578
2012 7             334
2012 8             525
2012 9             659

Scripts To Check Total Tablespace Available in DB

Scripts To Check Total Tablespace Available in DB

set feed off
column "Graph" heading "Graph" format a11

column "tablespace_name" heading "Tablespace | Name" format a20
column "FileCount" heading "File | Count" format 999999
column "Size(MB)" heading "Size | (MB)" format 999,999,999.99
column "Free(MB)" heading "Free | (MB)" format 999,999,999.99
column "Used(MB)" heading "Used | (MB)" format 999,999,999.99
column "Max Ext(MB)" heading "Max Ext | (MB)" format 999,999,999
column "%Free" heading "% | Free" format 999.99
column "%Free Ext" heading "% | Free Ext" format 999.99
column tablespace_name heading "Tablespace | Name" format a20

SELECT
     ts.tablespace_name, "File Count",
     TRUNC("SIZE(MB)", 2) "Size(MB)",
     TRUNC(fr."FREE(MB)", 2) "Free(MB)",
     TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
     df."MAX_EXT" "Max Ext(MB)",
     100-((fr."FREE(MB)" / df."SIZE(MB)") * 100) "% USED",
     RPAD('*', TRUNC(CEIL((fr."FREE(MB)" / df."SIZE(MB)") * 100)/10), '*')    "Graph"
  FROM
     (SELECT tablespace_name,
     SUM (bytes) / (1024 * 1024*1024) "FREE(MB)"
     FROM dba_free_space
      GROUP BY tablespace_name) fr,
  (SELECT tablespace_name, SUM(bytes) / (1024 * 1024*1024) "SIZE(MB)", COUNT(*)
  "File Count", SUM(maxbytes) / (1024 * 1024*1024) "MAX_EXT"
  FROM dba_data_files
  GROUP BY tablespace_name) df,
  (SELECT tablespace_name
  FROM dba_tablespaces) ts
  WHERE fr.tablespace_name = df.tablespace_name (+)
  AND fr.tablespace_name = ts.tablespace_name (+)
  ORDER BY "% USED" desc
  /

Script To Check DB Size

Script To Check DB Size

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

select sum (bytes) /1024 /1024 /1024 from dba_data_files where tablespace_name not in ('UNDOTBS1');

Script to check Locks & Inactive Sessions

Script to check Locks & Inactive Sessions

sql>select s.blocking_session, s.sid, s.serial#, s.STATUS,s.seconds_in_wait from v$session s where blocking_session IS NOT NULL;


Blocking session  locks
****************
set lines 200
set pages 200
SELECT inst_id,DECODE(request,0,'Holder: ','---> Waiter: ')||sid sess,
id1, id2, lmode, request, type,ctime/60 mts FROM gV$LOCK WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM gV$LOCK WHERE request>0) ORDER BY id1, request;

locks on objects
*****************
SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name,PROCESS,SESSION_ID fROM
V$Locked_Object A, All_Objects B WHERE A.Object_ID = B.Object_ID and
B.Object_Name='MTL_TXN_REQUEST_LINES';

Inactive Users
 *********
SQL> select username,command,status,server,osuser,machine,terminal,program,module,action from v$session where status'INACTIVE';

Inactive sessions
*****************
SET HEADING ON
SET LINE 300
SET PAGES 9999
COL program FOR a23 TRUNC
COL process FOR a7
COL pid FOR a7
COL status FOR a8
COL login FOR a15
COL action FOR a25
COL module FOR a25


  SELECT p.program, p.process AS PID, p.sid,p.serial#,
         p.status, p.client_identifier AS LOGIN,p.action,
         p.module, p.logon_time FROM (SELECT DISTINCT b.sid, b.serial#, b.status,
                 b.program, b.process, b.client_identifier,   b.action,    b.module, TO_CHAR (b.logon_time, 'dd-MON-yyyy hh24:mi:ss')
                     logon_time,
                 TRUNC (SYSDATE - b.logon_time) "Dy",
                 TRUNC (MOD ((SYSDATE - b.logon_time) * 24, 24)) "Hr",
                 TRUNC (MOD ((SYSDATE - b.logon_time) * 24 * 60, 60))  "Mi",
                 TRUNC (MOD ((SYSDATE - b.logon_time) * 24 * 60 * 60, 60))  "Sec"
            FROM v$access a, v$session b, v$process c  WHERE     a.sid = b.sid
                 AND b.paddr = c.addr
                 AND b.status = 'INACTIVE'
                 AND (   b.action LIKE '%FRM%'  OR b.action LIKE '%frm%'   OR b.program LIKE '%TOAD%'  OR b.program LIKE '%toad%' OR b.program LIKE 'SQL%'   OR b.program LIKE '%sql%'  OR b.program LIKE '%FRM%'  OR b.program LIKE '%frm%' OR b.action LIKE 'SQL%'   OR b.action LIKE 'sql%'   OR b.action LIKE 'TOAD%'
                      OR b.action LIKE 'toad%')
                 AND (   TRUNC (MOD ((SYSDATE - b.logon_time) * 24, 24)) >= 12
                      OR TRUNC (SYSDATE - b.logon_time) >= 1)) p
ORDER BY p.logon_time;

Data Gaurd Scripts

Script To Check Log Gap:

SET LINESIZE 100;
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;



 Start MRP Process:
==================
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;



Stop MRP Process:
==================

 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Tablespace Usage Script

Tablespace Usage Script

SELECT
   ts.tablespace_name, "File Count",
   TRUNC("SIZE(MB)", 2) "Size(MB)",
   TRUNC(fr."FREE(MB)", 2) "Free(MB)",
   TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
   df."MAX_EXT" "Max Ext(MB)",
   100-((fr."FREE(MB)" / df."SIZE(MB)") * 100) "% USED",
   RPAD('*', TRUNC(CEIL((fr."FREE(MB)" / df."SIZE(MB)") * 100)/10), '*')    "Graph"
FROM
   (SELECT tablespace_name,
   SUM (bytes) / (1024 * 1024*1024) "FREE(MB)"
   FROM dba_free_space
    GROUP BY tablespace_name) fr,
(SELECT tablespace_name, SUM(bytes) / (1024 * 1024*1024) "SIZE(MB)", COUNT(*)
"File Count", SUM(maxbytes) / (1024 * 1024*1024) "MAX_EXT"
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name
FROM dba_tablespaces) ts
WHERE fr.tablespace_name = df.tablespace_name (+)
AND fr.tablespace_name = ts.tablespace_name (+)
ORDER BY "% USED" desc
/

ablespace                                Size            Free            Used      Max Ext
 Name                File Count            (MB)            (MB)            (MB)         (MB)     % USED Graph
-------------------- ---------- --------------- --------------- --------------- ------------ ---------- -----------
SDCD    48        1,440.00          331.37        1,108.62          596 76.9881736 **
SYT                        3           13.00            3.19            9.80            9 75.4230206 **
SYSAUX                        3           51.37           12.73           38.64           62 75.2138295 **