Monthly Archives: July 2019

What are Critical Patch Updates (CPUs)?

Critical Patch Updates are sets of patches containing fixes for security flaws in Oracle products. The Critical Patch Update program (CPU) was introduced in January 2005 to provide security fixes on a fixed, publicly available schedule to help customers lower their security management costs.

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 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 355 UNTIL SEQUENCE 479 DELETE INPUT; The above command will backup archive […]

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 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 […]

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 […]