How to check users, roles and privileges in Oracle
How to check high resource intensive SQL in Oracle
How to check execution plan of a query
How to backup archivelog for specific sequence RMAN
How to check last CPU applied in Oracle
How to check biggest table in Oracle
How to check database backups via sqlplus
How to display date and time in query output
How to check scheduler jobs in Oracle
How to check datapump export progress
How to drop all schema objects in Oracle
How to find memory used by Oracle
How to check last user login Oracle
How to check CPU cores in Linux
How to delete files older than X days in Linux
How to analyze wait events in Oracle
How to set DISPLAY variable in Linux
Crontab error - Permission Denied
How to check FRA location utilization in Oracle
How to check last modified table in Oracle
How to check single table size in oracle
How to check database PITR after refresh
How to check archive generation in Oracle
How to disable firewall in Linux 7
How to check database lock conflict in Oracle
How to check database size in Oracle
How to configure yum server in linux
How to check query plan change in oracle
How to force users change password on first login Linux
How to check datafile utilization in Oracle
How to estimate flashback destination space
How to check temp tablespace utilization



===================== How to check users, roles and privileges in Oracle ==============================

============ Query to check the granted roles to a user=======================

SELECT *
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = '&USER';

========= Query to check privileges granted to a user ==================

SELECT *
FROM DBA_TAB_PRIVS
WHERE GRANTEE = 'USER';

========= Privileges granted to a role which is granted to a user =================

SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE IN
(SELECT granted_role FROM DBA_ROLE_PRIVS WHERE GRANTEE = '&USER') order by 3;

========= Query to check if user is having system privileges ==============

SELECT *
FROM DBA_SYS_PRIVS
WHERE GRANTEE = '&USER';

============ Query to check permissions granted to a role =====================

select * from ROLE_ROLE_PRIVS where ROLE = '&ROLE_NAME';
select * from ROLE_TAB_PRIVS where ROLE = '&ROLE_NAME';
select * from ROLE_SYS_PRIVS where ROLE = '&ROLE_NAME';




===============  How to check high resource intensive SQL in Oracle =========================

col Rank for a4
SELECT *
FROM (SELECT RANK () OVER
(PARTITION BY "Snap Day" ORDER BY "Buffer Gets" + "Disk Reads" DESC) AS "Rank", i1.*
FROM (SELECT TO_CHAR (hs.begin_interval_time, 'MM/DD/YY' ) "Snap Day",
SUM (shs.executions_delta) "Execs",
SUM (shs.buffer_gets_delta) "Buffer Gets",
SUM (shs.disk_reads_delta) "Disk Reads",
ROUND ( (SUM (shs.buffer_gets_delta)) / SUM (shs.executions_delta), 1 ) "Gets/Exec",
ROUND ( (SUM (shs.cpu_time_delta) / 1000000) / SUM (shs.executions_delta), 1 ) "CPU/Exec(S)",
ROUND ( (SUM (shs.iowait_delta) / 1000000) / SUM (shs.executions_delta), 1 ) "IO/Exec(S)",
shs.sql_id "Sql id",
REPLACE (CAST (DBMS_LOB.SUBSTR (sht.sql_text, 50) AS VARCHAR (50) ), CHR (10), '' ) "Sql"
FROM dba_hist_sqlstat shs INNER JOIN dba_hist_sqltext sht
ON (sht.sql_id = shs.sql_id)
INNER JOIN dba_hist_snapshot hs
ON (shs.snap_id = hs.snap_id)
HAVING SUM (shs.executions_delta) > 0
GROUP BY shs.sql_id, TO_CHAR (hs.begin_interval_time, 'MM/DD/YY'),
CAST (DBMS_LOB.SUBSTR (sht.sql_text, 50) AS VARCHAR (50) )
ORDER BY "Snap Day" DESC) i1
ORDER BY "Snap Day" DESC)
WHERE "Rank" <= 5 AND "Snap Day" = TO_CHAR (SYSDATE, 'MM/DD/YY');


==============  How to check execution plan of a query

First get the sql ID and then you can use below command to generate
execution plan of a query in oracle


SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('2t3nwk8h97vph',0));


In case you have more IDs, use below command to supply sql id every time
you run the query

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id',0));


================How to backup archivelog for specific sequence RMAN ==

When you issue archive backup commands via RAMN, it will backup all the
archive logs. Sometimes, you might need to backup only a particular
archive log sequence. Below command will help you backup archive logs
between specific sequence

RMAN> BACKUP ARCHIVELOG FROM SEQUENCE 288 UNTIL SEQUENCE 388 DELETE INPUT;


The above command will backup archive logs from 288 to 388 sequence
number.


================== How to check last CPU applied in Oracle ===


Query to Check Last CPU Applied on a Database:

col VERSION for a15;
col COMMENTS for a50;
col ACTION for a10;
set lines 500;
select ACTION,VERSION,COMMENTS,BUNDLE_SERIES from registry$history;

=================== How to check biggest table in Oracle ======

Query to check top 10 largest tables in Oracle

SELECT * FROM
(select
SEGMENT_NAME,
SEGMENT_TYPE,
BYTES/1024/1024/1024 GB,
TABLESPACE_NAME
from
dba_segments
order by 3 desc ) WHERE
ROWNUM <= 10

=================== How to check database backups via sqlplus =====

Query to check database backup status

set linesize 500
col BACKUP_SIZE for a20
SELECT
INPUT_TYPE "BACKUP_TYPE",
--NVL(INPUT_BYTES/(1024*1024),0)"INPUT_BYTES(MB)",
--NVL(OUTPUT_BYTES/(1024*1024),0) "OUTPUT_BYTES(MB)",
STATUS,
TO_CHAR(START_TIME,'MM/DD/YYYY:hh24:mi:ss') as START_TIME,
TO_CHAR(END_TIME,'MM/DD/YYYY:hh24:mi:ss') as END_TIME,
TRUNC((ELAPSED_SECONDS/60),2) "ELAPSED_TIME(Min)",
--ROUND(COMPRESSION_RATIO,3)"COMPRESSION_RATIO",
--ROUND(INPUT_BYTES_PER_SEC/(1024*1024),2) "INPUT_BYTES_PER_SEC(MB)",
--ROUND(OUTPUT_BYTES_PER_SEC/(1024*1024),2) "OUTPUT_BYTES_PER_SEC(MB)",
--INPUT_BYTES_DISPLAY "INPUT_BYTES_DISPLAY",
OUTPUT_BYTES_DISPLAY "BACKUP_SIZE",
OUTPUT_DEVICE_TYPE "OUTPUT_DEVICE"
--INPUT_BYTES_PER_SEC_DISPLAY "INPUT_BYTES_PER_SEC_DIS",
--OUTPUT_BYTES_PER_SEC_DISPLAY "OUTPUT_BYTES_PER_SEC_DIS"
FROM V$RMAN_BACKUP_JOB_DETAILS
where start_time > SYSDATE -10
and INPUT_TYPE != 'ARCHIVELOG'
ORDER BY END_TIME DESC
/

 Query to check archive Backup status

In the 3rd last line and INPUT_TYPE != 'ARCHIVELOG', just remove '!' to
get archivelog backup details



================= How to display date and time in query output ============

By default, when you query a date column, oracle will only display dates
and not time. Below query enables Oracle to display both date and time for
a particular session

alter session set nls_date_format='dd-Mon-yyyy hh:mi:sspm';



================ How to check scheduler jobs in Oracle ===============

Below command will help you check Scheduler jobs which are con??gured
inside database

SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS where job_name='RMAN_BACKUP';

Query to check currently running scheduler jobs

SELECT * FROM ALL_SCHEDULER_RUNNING_JOBS;

All the DBA Scheduler jobs create logs. You can query below and check the
details of job logs

select log_id, log_date, owner, job_name
from ALL_SCHEDULER_JOB_LOG
where job_name like 'RMAN_B%' and log_date > sysdate-2;
select log_id,log_date, owner, job_name, status, ADDITIONAL_INFO
from ALL_SCHEDULER_JOB_LOG
where log_id=113708;

=====================  How to check datapump export progress =============

Sometimes when you run datapump export, it might take a lot of time.
Meanwhile client might ask you for the % of export completed. Use below
query to get the details of how much % export is done

SELECT SID, SERIAL#, USERNAME, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR <> TOTALWORK;


====================  How to drop all schema objects in Oracle ================================

SET SERVEROUTPUT ON SIZE 1000000
set verify off
BEGIN
FOR c1 IN (SELECT OWNER,table_name, constraint_name FROM dba_constraints
WHERE constraint_type = 'R' and owner=upper('&shema_name')) LOOP
EXECUTE IMMEDIATE
'ALTER TABLE '||' "'||c1.owner||'"."'||c1.table_name||'" DROP CONSTRAINT ' || c1.constraint_name;
END LOOP;
FOR c1 IN (SELECT owner,object_name,object_type FROM dba_objects
where owner=upper('&shema_name')) LOOP
BEGIN
IF c1.object_type = 'TYPE' THEN
EXECUTE IMMEDIATE 'DROP '||c1.object_type||' "'||c1.owner||'"."'||c1.object_name||'" FORCE';
END IF;
IF c1.object_type != 'DATABASE LINK' THEN
EXECUTE IMMEDIATE 'DROP '||c1.object_type||' "'||c1.owner||'"."'||c1.object_name||'"';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
EXECUTE IMMEDIATE('purge dba_recyclebin');
END;
/


============================= How to find memory used by Oracle ============================

select decode( grouping(nm), 1, 'total', nm ) nm, round(sum(val/1024/1024)) mb
from
(
select 'sga' nm, sum(value) val
from v$sga
union all
select 'pga', sum(a.value)
from v$sesstat a, v$statname b
where b.name = 'session pga memory'
and a.statistic# = b.statistic#
)
group by rollup(nm);


============================ How to check last user login Oracle ===========

select username, timestamp, action_name from dba_audit_session
where action_name='LOGON' and
rownum<10 and username not in ('SYS','DBSNMP','DUMMY','SYSTEM','RMAN');


=========================== How to check CPU cores in Linux  ===========

=Command to check CPU info on Linux

cat /proc/cpuinfo|grep processor|wc -l
OR
nproc --all
OR
getconf _NPROCESSORS_ONLN


=Command to check CPU info on Solaris

psrinfo -v|grep "Status of processor"|wc -l


=Command to check CPU info on AIX

lsdev -C|grep Process|wc -l


=Command to check CPU info on HP/UX

ioscan -C processor | grep processor | wc -l



============================ How to delete files older than X days in Linux =====

Find ??les older than X days and save ouput into a
file
The below Linux command will help you to ??nd ??les older than 35 days in a
specific directory path and save the ouput in backupfiles.log
Here the directory we are searching is /backup/logs and -mtime speci??es
the modi??ed time of a ??le. We are saving the list of all the ??les which are
older than 35 days in backupfiles.log

find /backup/logs -type f -mtime +35 -print > backupfiles.log &


Find ??les older than 7 days and print output on
screen

find /backup/logs -type f -mtime +7 -print

Find ??les in current directory older than 28 days
and remove them

Below linux command will ??nd all the ??les under current location (as we
have speci??ed . dot), search ??le name starting with arc h and ending with
log. check file create time with -ctime older than 28 days and then remove
those files using rm -f

find . -name arch\*log -ctime +28 -exec rm -f {} \;

================================  How to analyze wait events in Oracle  =====================

User below query to get the top wait classes in Oracle database

Select wait_class, sum(time_waited), sum(time_waited)/sum(total_waits)
Sum_Waits
From v$system_wait_class
Group by wait_class
Order by 3 desc;

From the above query, supply each wait class into below query to get the
top wait events in database with respect to particular wait class

Select a.event, a.total_waits, a.time_waited, a.average_wait
From v$system_event a, v$event_name b, v$system_wait_class c
Where a.event_id=b.event_id
And b.wait_class#=c.wait_class#
And c.wait_class = '&Enter_Wait_Class'
order by average_wait desc;

================================  How to set DISPLAY variable in Linux  ========================

Use below command to enable Linux GUI interface at command prompt as
root user
# xhost +

Sometimes, even after issuing above command, you wont be able to
invoke GUI because of “DISPLAY not set” error. In such case, you must
export the display environmental variable:
# echo $DISPLAY
# export DISPLAY=:0.0;


================================  Crontab error - Permission Denied  ============================

When you try to schedule backups under corntab as Oracle user, you
might encounter crontab permission error

[oracle@plcdbprod ~]$ crontab -l
cron/oracle: Permission denied

The error is because of permission issues on /usr/bin/crontab ??le. Login as
root user and find the crontab permissions on /usr/bin/crontab

[root@plcdbprod ~]# ls -l /usr/bin/crontab
-rwxr-xr-x 1 root root 315432 Jul 15 2008 /usr/bin/crontab

Give the below permissions to /usr/bin/crontab file
[root@plcdbprod ~]# chmod 4755 /usr/bin/crontab
[root@plcdbprod ~]# ls -l /usr/bin/crontab
-rwsr-xr-x 1 root root 315432 Jul 15 2008 /usr/bin/crontab

================================ How to check FRA location utilization in oracle =========


Below script gives you Flash Recovery Area utilization details

set linesize 500
col NAME for a50
select name, ROUND(SPACE_LIMIT/1024/1024/1024,2) "Allocated Space(GB)",
round(SPACE_USED/1024/1024/1024,2) "Used Space(GB)",
round(SPACE_RECLAIMABLE/1024/1024/1024,2) "SPACE_RECLAIMABLE (GB)" ,
(select round(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024,2)
from V$FLASHBACK_DATABASE_LOG) "Estimated Space (GB)"
from V$RECOVERY_FILE_DEST;

============================== How to check last modified table in Oracle =====

set linesize 500;
select TABLE_OWNER, TABLE_NAME, INSERTS, UPDATES, DELETES,
to_char(TIMESTAMP,'YYYY-MON-DD HH24:MI:SS')
from all_tab_modifications
where table_owner<>'SYS' and
EXTRACT(YEAR FROM TO_DATE(TIMESTAMP, 'DD-MON-RR')) > 2010
order by 6;

========================== How to check single table size in oracle ===========

Once you run the query, it will ask your table name. Enter the table name in
the format of owner.tablename. Eg - scott.emp

select segment_name,segment_type, sum(bytes/1024/1024/1024) GB
from dba_segments
where segment_name='&Your_Table_Name'
group by segment_name,segment_type;

========================== How to check database PITR after refresh ========
Database refresh is common task for a DBA. But after every database
refresh, you must check the PITR date and time. This should be checked
before you issue OPEN RESETLOGS command

Query to Check PITR – Issue it before OPEN RESETLOGS
select distinct to_char(checkpoint_time,'dd/mm/yyyy hh24:mi:ss') checkpoint_time
from v$datafile_header ;
DBA

======================== How to check archive generation in Oracle ==============

The below query gives results of archive generation in oracle database. Use
below query to ??nd the archive space requirements and you can use it to
estimate the archive destination size perfectly well.

SELECT A.*,
Round(A.Count#*B.AVG#/1024/1024/1024) Daily_Avg_gb
FROM
(SELECT
To_Char(First_Time,'YYYY-MM-DD') DAY,
Count(1) Count#,
Min(RECID) Min#,
Max(RECID) Max#
FROM v$log_history
GROUP
BY To_Char(First_Time,'YYYY-MM-DD')
ORDER
BY 1 DESC
) A,
(SELECT
Avg(BYTES) AVG#,
Count(1) Count#,
Max(BYTES) Max_Bytes,
Min(BYTES) Min_Bytes
FROM
v$log ) B;



====================== How to check database lock conflict in Oracle ==============

== Checking Lock Conflicts in 10g and Above:

select a.SID "Blocking Session", b.SID "Blocked Session"
from v$lock a, v$lock b
where a.SID != b.SID and a.ID1 = b.ID1 and a.ID2 = b.ID2 and
b.request > 0 and a.block = 1;


== Query to Check Lock is Table Level or Row Level ========

col session_id head 'Sid' form 9999
col object_name head "Table|Locked" form a30
col oracle_username head "Oracle|Username" form a10 truncate
col os_user_name head "OS|Username" form a10 truncate
col process head "Client|Process|ID" form 99999999
col owner head "Table|Owner" form a10
col mode_held form a15
select lo.session_id,lo.oracle_username,lo.os_user_name,
lo.process,do.object_name,do.owner,
decode(lo.locked_mode,0, 'None',1, 'Null',2, 'Row Share (SS)',
3, 'Row Excl (SX)',4, 'Share',5, 'Share Row Excl (SSX)',6, 'Exclusive',
to_char(lo.locked_mode)) mode_held
from gv$locked_object lo, dba_objects do
where lo.object_id = do.object_id
order by 5
/

=====  How to check database size in Oracle ========

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
/

=============  How to check query plan change in oracle =====

If you would like to ??nd out change in SQL plan of a query, below script will
help you ??nd the SQL plan ID for previous executions and check if there is
any change in SQL plan ID.

set pagesize 1000
set linesize 200
column begin_interval_time format a20
column milliseconds_per_execution format 999999990.999
column rows_per_execution format 999999990.9
column buffer_gets_per_execution format 999999990.9
column disk_reads_per_execution format 999999990.9
break on begin_interval_time skip 1
SELECT
to_char(s.begin_interval_time,'mm/dd hh24:mi')
AS begin_interval_time,
ss.plan_hash_value,
ss.executions_delta,
CASE
WHEN ss.executions_delta > 0
THEN ss.elapsed_time_delta/ss.executions_delta/1000
ELSE ss.elapsed_time_delta
END AS milliseconds_per_execution,
CASE
WHEN ss.executions_delta > 0
THEN ss.rows_processed_delta/ss.executions_delta
ELSE ss.rows_processed_delta
END AS rows_per_execution,
CASE
WHEN ss.executions_delta > 0
THEN ss.buffer_gets_delta/ss.executions_delta
ELSE ss.buffer_gets_delta
END AS buffer_gets_per_execution,
CASE
WHEN ss.executions_delta > 0
THEN ss.disk_reads_delta/ss.executions_delta
ELSE ss.disk_reads_delta
END AS disk_reads_per_execution
FROM wrh$_sqlstat ss
INNER JOIN wrm$_snapshot s ON s.snap_id = ss.snap_id
WHERE ss.sql_id = '&sql_id'
AND ss.buffer_gets_delta > 0
ORDER BY s.snap_id, ss.plan_hash_value


============= How to force users change password on first login Linux ==================

The option -d 0 will mark the password expired and hence, user will be
forced to change password.


# chage -d 0 oracle

=======================  How to check datafile utilization in Oracle ==============

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


========================  How to estimate flashback destination space ===============

==Sometimes application team will ask DBA to enable ??ashback for x
number of days. In such case, a DBA needs to estimate the ??ashback
space required for x number of days in order to store the ??ashback logs.
The flashback log size is same as archive log size generated in a database.
Check the archive generation size via below query
Take the average per day size of archives generated
Multiply the average archive size with x number of days
Ask storage team to add the required space for flashback file system
Check archive generation size via below query:

select to_char(COMPLETION_TIME,'DD-MON-YYYY') Arch_Date,count(*) No#_Logs,
sum((BLOCKS*512)/1024/1024/1024) Arch_LogSize_GB
from v$archived_log
where to_char(COMPLETION_TIME,'DD-MON-YYYY')>=trunc(sysdate-7) and DEST_ID=1
group by to_char(COMPLETION_TIME,'DD-MON-YYYY')
order by to_char(COMPLETION_TIME,'DD-MON-YYYY');


=========================  How to check temp tablespace utilization   ================

set lines 200
select TABLESPACE_NAME, sum(BYTES_USED/1024/1024),sum(BYTES_FREE/1024/1024)
from V$TEMP_SPACE_HEADER group by TABLESPACE_NAME;



SELECT A.tablespace_name tablespace, D.GB_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 /1024 GB_used,
D.mb_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;


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