Making Oracle simple for you!

Simplifying Oracle learning by sharing knowledge with professionals across the globe

How to find ASM disks mapping to physical disks

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...

READ MORE

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...

READ MORE

How to check high resource intensive SQL in Oracle

Database performance is a major concern for a DBA. SQLs are the ones which needs proper DB management in order to execute well. At times the application team might tell you that the database is running slow. You can run below query to get the top 5 resource intensive SQL with SQL ID and then give...

READ MORE

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...

READ MORE

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...

READ MORE

How to check last CPU applied in Oracle

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...

READ MORE

How to check biggest table in Oracle

As a DBA, you must keep an eye on the largest tables in the database. There are many things that get impacted with the largest objects like DB performance, growth, index rebuild etc. The below query gives you the top 10 largest tables in oracle database. Query to check top 10 largest tables in...

READ MORE

How to check database backups via sqlplus

Checking Database backups are one of the main focus areas of a DBA. Time to time, DBA needs to check database backup status and see if its completed, failed, running etc. Also, DBA must be able to get the backup start time, end time and even the backup size for reference purpose. The below query...

READ MORE

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'; Note – this is only session level query.

READ MORE

How to check scheduler jobs in Oracle

Below command will help you check Scheduler jobs which are configured 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....

READ MORE