ENABLE_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 Moreselect name, floor(space_limit/1024/1024) “Size MB”,ceil(space_used/1024/1024) “Used MB” from v$recovery_file_dest order by name;
Read Moreselect inst_id,count(*) from gv$session where username is not null group by inst_id;
Read Moreselect owner, count(1), object_type from dba_objects group by owner, object_type having owner in (select username from dba_users where default_tablespace = ‘USERS’) and object_type = ‘TABLE’;
Read Moreselect OWNER,sum(bytes)/1024/1024/1000 “SIZE_IN_GB” from dba_segments group by owner order by owner;
Read More