What happens in the background in Rebuild Index

I must thank my fellow DBA Franky Weber Faust for his publication in his blog. I’ll show you what happens during an online rebuild of an index. Few DBAs realize this, but during this operation an auxiliary index (among other things) is created to store the transient data while the application continues to run normally. Note that for […]

Read More

Create an Oracle SQL TUNING TASK with DBMS_SQLTUNE

You can create an SQL TUNING TASK manually adhoc with the following simple steps. Step1: Find the sql_id of the oracle session you would like to analyze. Usually the AWR has the top sql_ids. In case this is a current sql running use the v$session. select sql_id from v$session where sid = 😡 Step2: Login […]

Read More

How to extract SQL from SQLT

SQLTXPLAIN gets installed into separate schema called SQLTXPLAIN. It can be installed on RAC and on any version greater than 9i. When it is installed, it will ask for application schema so make sure that installation schema has SELECT_CATALOG_ROLE privilege. In order to run the script to create SQLTXPLAIN schema, one need to connect as […]

Read More

ORA-16179 incremental changes to “log_archive_dest_10” not allowed with SPFILE

You might encounter ORA-16179 error while changing the archive destination. SQL> alter system set log_archive_dest_10=’d:\oracle\arc\dbtest\’; alter system set log_archive_dest_10=’d:\oracle\arc\dbtest\’ * ERROR at line 1: ORA-32017: failure in updating SPFILE ORA-16179: incremental changes to “log_archive_dest_10” not allowed with SPFILE There is syntax error in above command . It should be like log_archive_dest_n [n is a integer […]

Read More

Recover a datafile from copy

Let’s recover if some datafile is unavailable. Let’s delete users01.dbf datafile which is in USERS tablespace. [oracle@node214 DB11G]$ pwd /u01/app/oracle/oradata/DB11G [oracle@node214 DB11G]$ [oracle@node214 DB11G]$ mv users01.dbf users01.dbf.bak #as backup file. [oracle@node214 DB11G]$ Let’s try to create a table name A in tablespace Users. SQL> create table a(id number) tablespace USERS * ERROR at line 1: […]

Read More

Recover controlfile

Controlfiles saved at least in 2 locations. If one of them is corrupted then you can copy from other one after shutdown database. In this case I will show you how to recover from autobackup via RMAN. So you need CONFIGURE CONTROLFILE AUTOBACKUP ON; in RMAN and you should have full backup database. It can […]

Read More

ESTIMATE in Oracle Expdp

The value for parameter ESTIMATE  is either BLOCKS (default) or STATISTICS. BLOCKS: The estimated space used is calculated by multiplying the number of database blocks used by the target objects with the appropriate block sizes. It is  the least accurate because of: The table was created with a much bigger initial extent size than was needed for […]

Read More

ESTIMATE_ONLY in Oracle Expdp

This parameter is useful when you wanted get the approximate size of the dump file. Suppose you have only limited space available in the disk and if you wanted to check whether your dump will be fit into the available space then you can use this parameter to check or estimate the size of the […]

Read More

Flashback Technology

Flashback Technology in Oracle : Oracle Flashback Technology is a group of Oracle Database features that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery. Today we will see following feature of Flashback Technology : 1. Oracle Flashback Query . 2. Flashback […]

Read More

Changing password securely in Oracle

I must thank my fellow DBA Franky Weber Faust for his publication in his blog. Introduction: – We are living in the world of username and password life. We all have countless password due to technology’s development. Password plays an important role in our life. Oracle Database leads the industry in security. To maximize the security […]

Read More