Finding What’s Consuming the Most Undo ======================================= Use the following query to find out which SQL statement has run for the longest time in your database. SQL> select s.sql_text from…
Read MoreDetermining the Optimal Undo Retention Period ============================================== You need to determine the optimal length of time for undo retention in your database. Solution You can specify the length of time…
Read MoreENABLE_DDL_LOGGING In Oracle 12c This ENABLE_DDL_LOGGING parameter has been introduced in oracle 12c. Enable the parameter: SQL> show parameter enable_ddl_logging NAME TYPE VALUE ———————————— ———– —————————— enable_ddl_logging boolean FALSE…
Read MoreHow To Drop SQL Baselines In Oracle 1.Get the sql_handle and sql_baseline name of the sql_id: SELECT sql_handle, plan_name FROM dba_sql_plan_baselines WHERE signature IN ( SELECT exact_matching_signature FROM gv$sql WHERE…
Read MoreOrapwd Tool For Password File In Oracle orapwd tool is used to create and manage password files. DEFAULT LOCATION FOR PWD FILE – $ORACLE_HOME/dbs Usage: orapwd file= entries= force=<y/n> asm=<y/n>…
Read MoreHow To Export And Import Statistics In Oracle For Table: PROD> exec dbms_stats.export_table_stats(ownname=>’SCOTT’, tabname=>’TEST’, stattab=>’STAT_TEST’, cascade=>true); PL/SQL procedure successfully completed. SQL> set lines 200 SQL> set pagesize 200 SQL>…
Read MoreHow To Add A Tempfile In Primary Database In Dataguard Adding tempfiles to TEMP tablespaces in primary database, will not automatically create on standby database. Because no redo is generated,…
Read MoreFlashback Primary Database In Dataguard Environment 1. Make sure both primary and standby databases have flashback enabled. – PRIMARY DB SQL> select name,database_role,flashback_on from v$database; NAME DATABASE_ROLE FLASHBACK_ON ——— —————-…
Read More