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;