For more exciting blogs on Oracle, please click the LINK

1. Kill Multiple Inactive sessions in Oracle

BEGIN
  FOR r IN (select sid,serial# from v$session where username='user' and status'INACTIVE')
  LOOP
      EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid  || ',' 
        || r.serial# || ''' immediate';
  END LOOP;
END;
/

 

2. IDENTIFY DATABASE SID BASED ON OS PROCESS ID

col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and b.spid='&spid'
order by b.spid;

 

3. Get full size of a database

SELECT a.data_size + b.temp_size + c.redo_size + d.controlfile_size 
"total_size in GB" 
FROM (SELECT SUM (bytes) / 1024 / 1024/1024 data_size FROM dba_data_files) a, 
(SELECT NVL (SUM (bytes), 0) / 1024 / 1024/1024 temp_size 
FROM dba_temp_files) b, 
(SELECT SUM (bytes) / 1024 / 1024/1024 redo_size FROM sys.v_$log) c, 
(SELECT SUM (BLOCK_SIZE * FILE_SIZE_BLKS) / 1024 / 1024/1024 
controlfile_size 
FROM v$controlfile) d;

 

4. Check Audit options

SELECT * FROM dba_stmt_audit_opts union SELECT * FROM dba_priv_audit_opts;

 

5. To check Database Startup Time

select HOST_NAME, INSTANCE_NAME, STATUS, LOGINS, ACTIVE_STATE, VERSION,DATABASE_STATUS, to_char
(STARTUP_TIME, 'dd-Mon-yy hh24:mi') startup from v$instance;

 

6. Check Shared pool free memory

select pool,name,bytes from v$sgastat where name='free memory' and pool='shared pool';

 

7. To find and delete files 1 day back. This 1 day can be changed from +1 to the number of days back we want

find /opt/tech/oracle/admin/INFRA/udump -type f -name "*.trc" -mtime +1  -exec rm {} \; 

 

8. To check CPU utilization

top

sar -u 3 3

 

9. To set vi terminal in wide screen

stty columns 120

 

10. To check resource utilization

set lines 150
select * from v$resource_limit;

 

11. To check which patches have been applied to database

/d01/oracle/product/v12.2/OPatch/opatch lsinv|grep -i applied

 

12. Check whether database is in Archive log mode

SQL> select name,log_mode,open_mode from v$database;

NAME      LOG_MODE     OPEN_MODE
--------- ------------ ----------
INFRA      NOARCHIVELOG READ WRITE

 

13. To delete files from a particular date

ls -lrt |grep -v "Mar 25"|grep -v alert|awk '{print $NF}'|xargs rm –rf

 

14. To check whether the DB is in RAC or not

select inst_id from gv$instance;

 

15. To check for Database uptime

select a.name,a.open_mode,b.instance_name,b.logins,b.archiver,to_char(b.STARTUP_TIME, 'MM-DD-YYYY-HH24:MI')UPTIME,rtrim(b.host_name) hostname  from v$database a,v$instance b;

 

16. Determine if the instance is part of an RAC database:

select parallel from v$instance;

 
This will return NO if it is a single-instance database.

17. Determine if the database is protected against data loss by a standby database:

select protection_level from v$database;

 
This will return UNPROTECTED if the database is indeed unprotected.

18. Determine if Streams has been configured in the database:

select * from dba_streams_administrator;

 
This will return no rows, if Streams has never been configured.

19. To check patch applied dates
a.

$ORACLE_HOME/OPatch/opatch lsinventory |grep "Patch" | grep "applied on"

 

b.

"col instance_name format a15 heading ""Instance|Name""
col host_name format a15 heading ""Hostname""
col version format a10 heading ""Version""
col comments format a20 heading ""Patch|Applied""
col id heading ""Patch|Number""
col to_char(r.action_time,'DD-Mon-YYYY@HH24:mi:ss') format a20 heading ""Date""
rem ******************

prompt MISC: APPLIED CPU HISTORY BY DATE

select distinct i.instance_name, i.host_name, i.version, r.comments, r.id, to_char(r.action_time,'DD-Mon-YYYY@HH24:mi:ss')
from v$instance i, sys.registry$history r
where r.comments like 'CPU%' or r.comments like 'PSU%'
order by to_char(r.action_time,'DD-Mon-YYYY@HH24:mi:ss')
/"

 

20. To check where audit file resides :

SQL> select owner,table_name,tablespace_name from dba_tables where table_name='AUD$' and owner='SYS';

OWNER TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
SYS AUD$ SYSTEM

 

21. Purge Audit Files

select count(*) from sys.aud$ where timestamp# < (trunc(sysdate)-30);
delete from sys.aud$ where timestamp# < (trunc(sysdate)-90);
delete from sys.aud$ where timestamp# < (trunc(sysdate)-90) 
BEGIN
loop -- keep looping
--do the delete 4999 in each iteration
delete from sys.aud$ where timestamp# < (trunc(sysdate)-90) and rownum < 5000;
-- exit the loop when there where no more 5000 reccods to delete.
exit when SQL%rowcount < 1;
-- commit to clear the rollback segments.
commit; -- Each 4999 records deletion
end loop;
commit; -- commit the last delete
END;

 

22. Check distributed transactions

Select * from dba_2pc_pending;

 

23. Dataguard status check

SELECT A.THREAD#,
 B.LAST_SEQ,
 A.APPLIED_SEQ,
 to_char(A.LAST_APP_TIMESTAMP ,'DD-MON-YYYY HH24:MI:SS') "Applied Time",
 B.LAST_SEQ - A.APPLIED_SEQ ARC_DIFF
 FROM ( SELECT THREAD#,
 MAX (SEQUENCE#) APPLIED_SEQ,
 MAX (NEXT_TIME) LAST_APP_TIMESTAMP
 FROM GV$ARCHIVED_LOG
 WHERE APPLIED = 'YES'
 GROUP BY THREAD#) A,
 ( SELECT THREAD#, MAX (SEQUENCE#) LAST_SEQ
 FROM GV$ARCHIVED_LOG
 GROUP BY THREAD#) B
 WHERE A.THREAD# = B.THREAD#; 

 

24. Find and delete files in Unix

find -name "crs" -mtime +5 -exec rm -rf {} \;

find /d01/oracle/diag/rdbms/infradb/INFRADB/trace -name "*.trc" -mtime +10  -exec rm -rf {} \;

 

25. Checking Blocked sessions:

set pages 500;
set lines 150;
col SCHEMANAME format a12;
col USERNAME format a12;
col STATUS format a10;
col BLOCKING_SESSION_STATUS format a23;
col PROGRAM format a20;
col LOGON_TIME format a10;
select sid,serial#,SCHEMANAME,USERNAME,STATUS,BLOCKING_SESSION,BLOCKING_SESSION_STATUS,PROGRAM,LOGON_TIME,SECONDS_IN_WAIT/60 "WAIT_TIME(HR)" from gv$session where BLOCKING_SESSION>0
order by 10;

 

26. To check details of a particular SID

select sid,serial#,SCHEMANAME,USERNAME,STATUS,PROGRAM,SQL_ID,machine,logon_time from v$session where SID in (&list);

 

27. To check for Long running sessions

a.

select * from
(
  select
     sid,
     INST_ID,
     opname,
     start_time,
     target,
     sofar,
     totalwork,
     units,
     elapsed_seconds,
     message
   from
        gv$session_longops
  order by start_time desc
)
where rownum <=1;

 

b.

select inst_id,sid,opname,totalwork,sofar,time_remaining from gv$session_longops where time_remaining>0;

 

c.

COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A20
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
SELECT s.sid,
       s.serial#,
       s.machine,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   v$session s,
       v$session_longops sl
WHERE  s.sid = sl.sid
AND    s.serial# = sl.serial#
and sl.TIME_REMAINING >0 order by SID;

 

28. To Check for Locked tables

SELECT session_id,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;

 

29. Check historical information of a particular session

select sessid, serial#, snap_id, to_char(begin_time,'dd-mon-yyyy hh24:mi:ss') begin_time, to_char(end_time,'dd-mon-yyyy hh24:mi:ss') from DBA_HIST_SESSMETRIC_HISTORY order by sessid;

select a.snap_id, b.startup_time, sql_id, session_id, session_serial# from DBA_HIST_ACTIVE_SESS_HISTORY a, DBA_HIST_SNAPSHOT b  where session_id=1539 and a.snap_id=b.snap_id order by snap_id, sql_id;

 

30. Check Current logons and Max Sessions connected:

SELECT RPAD(C.NAME||':',11)||RPAD(' current logons='||
   (TO_NUMBER(B.SESSIONS_CURRENT)),20)||'  maximum connected='||
   B.SESSIONS_HIGHWATER INFORMATION 
   FROM V$LICENSE B, V$DATABASE C;

 

31. Concurrency diagnosis :

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 eq_name, eq_type, req_reason from v$enqueue_statistics  where eq_type = 'TX'  and req_reason = 'row lock contention';

select event, total_waits from v$system_event where event = 'enq: TX - row lock contention';

select event, total_waits from v$session_event where event = 'enq: TX - row lock contention';

 

32. Check NLS Parameters

select VALUE from nls_database_parameters where parameter='NLS_CHARACTERSET';

 

33. Check invalid counts

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

 

34. Check object count for all schemas :

select obj.owner "Owner", obj_cnt "Objects",decode(seg_size, NULL, 0, seg_size) "size MB" from ( select owner, count(*)obj_cnt from dba_objects group by owner) obj,( select owner, ceil(sum(bytes)/1024/1024) seg_size from dba_segments group by owner) segment where obj.owner = segment.owner(+)order by 3 desc, 2 desc, 1;

 

35. Check object count for a particular schema

select owner,count(object_type) "count", object_type from DBA_OBJECTS where owner = 'INFRA' group by owner, object_type;

 

36. Check privileges assigned to an User

select 'grant '||granted_role||' to '||grantee||';' from dba_role_privs where grantee ='INFRA';

select 'grant '||privilege||' on '||owner||'.'||table_name|| ' to '||grantee||';' from dba_tab_privs where grantee ='INFRA';

select 'grant '||privilege||' to '||grantee||';' from dba_sys_privs where grantee='INFRA order by privilege;

 

37. Check privileges assigned to a role

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',role) FROM ROLE_ROLE_PRIVS WHERE ROLE='INFRA_ROLE' group by ROLE;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',role) FROM ROLE_SYS_PRIVS WHERE ROLE='INFRA_ROLE' group by ROLE;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',role) FROM ROLE_SYS_PRIVS WHERE ROLE='INFRA_ROLE' group by ROLE;

select * from role_sys_privs where ROLE = 'INFRA_ROLE' order by 1;

 

38. To copy a job from SYS

begin  
 dbms_scheduler.copy_job('SYS.TEST_TO_DEV_COPY','Testing.test_job');  
end;  

 

39. Gives a ready sql-statement for changing password back

select 
 'alter user "'||username||'" identified by values '''||extract(xmltype(dbms_metadata.get_xml('USER',username)),'//USER_T/PASSWORD/text()').getStringVal()||''';'  old_password from
 dba_users where username='INFRA';

 

40. Check a jobs status

select status,run_duration,actual_start_date,additional_info from dbA_scheduler_job_run_details where job_name='INFRA_PORT_COPY';

 

41. Check database total size and free space

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select     round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
,     round(sum(used.bytes) / 1024 / 1024 / 1024 ) - 
     round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
,     round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
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
/

 

42. Query to check the time between log switches.

select  b.recid,
        to_char(b.first_time, ’dd-mon-yy hh:mi:ss’) start_time, 
        a.recid,
        to_char(a.first_time, ’dd-mon-yy hh:mi:ss’) end_time,
        round(((a.first_time-b.first_time)*25)*60,2) minutes
from    v$log_history a, v$log_history b
where   a.recid = b.recid + 1
order   by a.first_time asc

 

43. Get remote Client IP Address

select SID,USERNAME,MACHINE,utl_inaddr.get_host_address(substr(machine,instr(machine,'\')+1)) IP from v$session;