When writing posts on performance tuning I felt that there needs to be a my-version of description of Oracle Wait Events along with Access method and Process & Memory Architecture. Thus this series is to start the Oracle Wait events which are common in any Database Environment BUT has a lot of story. db file…
DB File scattered read wait event occurs when an oracle process waits for a physical read of multiple oracle blocks from the disk. This wait identifies that a full scan (Full Table Scan or Index fast full scan) is occurring BUT during this read the blocks are read into memory locations that are not physically…
DB file sequential reads wait event comes under the User I/O wait class. In any busy system this is one of the most common wait event and normally appears in the top 5 wait events. In this post we’ll understand whats happening during this wait event. In simple words, Db file sequential reads wait event…
When writing posts on performance tuning I felt that there needs to be a my-version of description of Oracle Wait Events along with Access method and Process & Memory Architecture. Thus this series is to start the Oracle Wait events which are common in any Database Environment BUT has a lot of story. Direct Path…
There can be several situations where you want to test the impact of dropping a column from a table, till 11gR2 Oracle provided an option to mark a column as UNUSED before dropping a column, and do ALTER TABLE … DROP UNUSED COLUMNS at a later time but marking a column UNUSED is a irreversible…
Until 11gR2 to move a table to different tablespace there were two available methods, first one was ALTER TABLE .. MOVE TABLESPACE but during this the table was locked for DML, thus downtime and the second one was DBMS_REDEFINITION which will allow to create a new table on the desired tablespace and sync the data…
With 12cR1 you can now move table partitions and sub-partitions online. There are lot of reasons for which you might want to move a table partition or sub-partition such as:- Re-cluster data and reduce fragmentation. Move a partition to another tablespace. Modify create-time attributes. Store the data in compressed format using table compression. The problem…
After the introduction of multitenant architecture with Oracle 12cR1, the flashback database feature became irrelevant as it was limited to the root container which resulted in affecting all the PDBs associated with that root container. But with the new release 12cR2, flashback now supports pluggable databases as well. Now we’ll demonstrate how to flashback a…
This is a quick note to cover ORA-12547: TNS:lost contact error, I would try to include all possible causes of ORA-12547: TNS:lost contact error so that this post itself could assist you in sorting the issue. ORA-12547: TNS:lost contact error could be due to any of the following reasons:- Incorrect permissions on the ORACLE executable.…
All DBAs & Developers would have always wondered that why Oracle has the identifier limit of 30 bytes whereas other rdbms SQL Server, MySQL etc have much bigger limit. Well honestly even I don’t know that, it has been this way ever since despite multiple request from customers/developers to Oracle to increase the identifier limit,…
Oracle Database Internal Mechanism 