col GRANTOR format a24 col PRIVILEGE format a14 col TABLE_NAME format a32 select GRANTOR,PRIVILEGE, TABLE_NAME from DBA_TAB_PRIVS where grantee = ‘APPLSYSPUB’ and privilege in (‘SELECT’,’INSERT’,’EXECUTE’,’DELETE’) and…
Read MoreHow to enable Oracle apps Diagnostics-> Examine, for certain users? Steps 1 Navigate to System Administrator responsibility> Profile> System> Steps 2 Enter profile name: Utilities:Diagnostics Enter Application User for whom…
Read Morequery to find all the select statement which is using Full Table Scan SELECT SQL_ID,OPERATION,OPTIONS,OBJECT_NAME,OBJECT_OWNER FROM V$SQL_PLAN WHERE OPTION like ‘%FULL%’ AND OPERATION like ‘%TABLE%’; –Then find the Query Text…
Read MoreHow many users per application in Backend select application_name, count(user_name) from (SELECT fu.user_name, application_name, count(application_name) FROM fnd_responsibility_tl t, …
Read MoreDBMS_SCHEDULER in Oracle Database Scheduler in the Oracle Database is done by DBMS_SCHEDULER package. This package can be used to create different types of jobs :- 1. Jobs having Program…
Read MoreDATAGUARD MONITORING ==================== Check DB role(PRIMARY/STANDBY): ================================ SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE; Monitor standby background process: =================================== SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM…
Read MoreOEM CONTROL COMMANDS: ===================== Stop/start and status oms in cloud control =============================== cd $ORACLE_HOME/bin emctl stop oms emctl start oms emctl status oms stop/start agent in oem cloud control =====================================…
Read MoreYou want to view an alert log by using ADRCI commands. To view an alert log with ADRCI, follow these steps: Invoke ADRCI. $ adrci Set the ADR home with…
Read MoreUse the following query to find out which SQL statement has run for the longest time in your database. SQL> select s.sql_text from v$sql s, v$undostat u where u.maxqueryid=s.sql_id; You…
Read MoreYou need to determine the optimal length of time for undo retention in your database. Use the following formula to calculate the optimal value of the UNDO_RETENTION parameter: OPTIMAL UNDO_RETENTION…
Read More