When you work in a RAC environment with a list of ASM disks, sometimes you might want to figure out which ASM disk is mapped to which physical device. Below is a simple script that will help you find out ASM disks that are mapped to Linux...
Query to check the granted roles to a user
WHERE GRANTEE = '&USER';
Query to check privileges granted to a user
WHERE GRANTEE = 'USER';
Privileges granted to a role which is granted to a user
SELECT * FROM DBA_TAB_PRIVS...
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...
Generally if you have one single database install then checking the database inventory will give you the latest patch details. But! if we have multiple database in single oracle home then it might not give correct results. There might be a chance that one DB is applied with latest patches and...
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';
Note – this is only session level query.
The below script will drop all the objects owned by a schema. This will not delete the user but only deletes the objects
SET SERVEROUTPUT ON SIZE 1000000
set verify off
FOR c1 IN (SELECT OWNER,table_name, constraint_name FROM dba_constraints WHERE constraint_type = 'R' and...
select decode( grouping(nm), 1, 'total', nm ) nm, round(sum(val/1024/1024)) mb
select 'sga' nm, sum(value) val
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);
While performing database audits, you might need to check who logged in last into the database. The query will help you find out last user who logged in to database
select username, timestamp, action_name from dba_audit_session
where action_name='LOGON' and
rownum<10 and username not in...
Command to check CPU info on Linux
cat /proc/cpuinfo|grep processor|wc -l
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...
Find files older than X days and save ouput into a file
The below Linux command will help you to find files 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 specifies the modified time of a file....