HOW TO

How to change flash recovery area location

Below are the steps for changing the flash recovery area(FRA destination). 1. Check the current FRA destination.

2. Change db_recovery_file_dest parameter.

From onwards new files will be created in this new location. However, the flashback logs will be created under the old location. For the FLASHBACK logfiles to be able to pick up […]

How to enable flash recovery area in oracle database

The flash recovery area(FRA) is an Oracle-managed destination( either FILE SYSTEM or ASM ) for centralized backup and recovery files. It simplifies the backup management. The following recovery-related files are stored in the flash recovery area: — Current control file — Online redo logs — Archived redo logs — Flashback logs — Control file auto […]

How to drop SQL baselines in oracle

Below is the steps for dropping a sql baseline from the database.   1.Get the sql_handle and sql_baseline name of the sql_id:

2. Drop the baseline:

An sql_handle can have multiple sql baselines tagged, So if you want to drop all the sql baselines of that handle, then drop the sql handle itself. […]

How to get the DDL of an dbms scheduler job

The easy way to get the ddl of an dbms scheduler job is : select dbms_metadata.get_ddl(‘PROCOBJ’,’JOB_NAME’,’JOB_OWNER’) from dual;

But what if the job owner is SYS? Will the same syntax work?

Throwing error!!!! Well we have an workaround for this. Lets copy the scheduler job to another schema.

Now we have the […]

How to modify awr snapshot interval setting

We can change the snap_interval and retention period for the automatic awr snapshot collection, using modify_snapshot_settings function. The default settings for ‘interval’ and ‘retention’ are 60 minutes and 8 days . DEFAULT SETTING:

Modify the snapshot setting:( snap_interval 30 min and retention 30 days(60*24*30) The values for both ‘interval’ and ‘retention’ are expressed in […]

How to create awr snapshot manually

Automatic Workload Repository (AWR) is a collection of database statistics owned by the SYS user. By default snapshot are generated once every 60min . But In case we wish to generate awr snapshot manually, then we can run the below script.  This is usually useful, when we need to generate an awr report for a non-standard window with smaller interval. Lets say, we want to generate a report […]

How to generate AWR report in oracle

The Automatic Workload Repository (AWR) collects and maintains statistics of the database. We can generate awr report for a particular time frame in the past using the script awrrpt.sql ( located under $ORACLE_HOME/rdbms/admin) script – @$ORACLE_HOME/rdbms/admin/awrrpt.sql

For rac databases, the script awrrpt.sql script need to be run again each instance.   For NON-SYSDBA USERS, […]

How to flush a sql statement from shared pool

                                  If you flush the shared pool, all the statements in cursor will be flushed. So if you want a sql query to do hard parsing, then you can flush the particular sql statement from shared pool. STEPS: 1. […]

How to get the bind values of a sql query

                   If you have the sql_id of the sql query , then you can get the bind values of the bind variables, from v$sql_bind_capture. Script:

How to move lob segment to another tablespace

Follow below steps from moving lob segment from one tablespace to another. SQL> ;   1*  select table_name,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME from dba_lobs where OWNER=’DBACLASS’ SQL> / TABLE_NAME     COLUMN_NAME  SEGMENT_NAME                       TABLESPACE_NAME ————– ———— ———————————- —————————— FD_BREL_AUDIT PAYLOAD      SYS_LOB0000100201C00011$$          WEBMDATA   SQL> alter table DBACLASS.FD_BREL_AUDIT move lob (PAYLOAD) store as SYS_LOB0000100201C00011$$ ( tablespace USERS); Table altered.   SQL>  select […]

Page 1 of 41234
Skip to toolbar