Oracle Performance Tuning Tools( Oracle Explain Plan,Auto-trace,tkprof)

We are presenting here All information about Oracle tuning tools like Oracle Explain Plan,Auto-trace,tkprof. We will take a deep dive into each of them Oracle Explain Plan -Oracle database internally creates a query execution plan in order to fetch the desired data from the physical tables. The query execution plan is nothing but a set of methods on how the database will access the data from … Continue reading Oracle Performance Tuning Tools( Oracle Explain Plan,Auto-trace,tkprof) »

How to rebuild WF_DEFERRED_TABLE_M Queue

Last updated on December 17th, 2017 at 05:31 pmSome times we need to rebuild the WF_DEFERRED_TABLE_M queue due to corruption. Here are the steps to perform it. First take a pre snapshot of all the objects related to that queue Login to server and source the EBS environment spool WF_DEFERRED_TABLE_M_objects_pre.lst select object_name, object_type, status, owner from dba_objects where object_name like ‘%WF_DEFERRED_TABLE_M%’; spool off 2) Now we … Continue reading How to rebuild WF_DEFERRED_TABLE_M Queue »

Oracle Case Statement Explained with Tips and Examples

Last updated on December 24th, 2017 at 02:08 pmWe have seen working of Oracle Decode processing in the previous post Oracle sql decode processing Now let us see Case statement processing Table of content Case statement in Oracle Simple case statement Searchable Case statement Nested Oracle Case statement Important points about Simple and searchable Case statement difference between Case and Decode statement   Case statement … Continue reading Oracle Case Statement Explained with Tips and Examples »

Difference between v$system_parameter and v$parameter

In this post, we will discuss the Difference between v$system_parameter and v$parameter and other parameter view available in Oracle database. Lets first start with the definition of each of them V$PARAMETER It displays the information about initialization parameters that are currently in effect for the session. V$SYSTEM_PARAMETER is the view which shows instance level parameters (and these are what all new sessions inherit) V$SPPARAMETER It displays … Continue reading Difference between v$system_parameter and v$parameter »

How to change Hostname for a Grid Infrastructure Oracle Restart Standalone Configuration

We have a Grid Infrastructure Oracle Restart  configurtaion on one database server and Now we want to change the hostname of the server. Here we will be seeing the steps required to perform it without any loss of the database. Configuration Old Host Name: techgo New Host name:techgo-new Stop all the services $> <11.2 Grid Infrastructure Oracle Home>/bin/crsctl stop has Change the Hostname from techgo to … Continue reading How to change Hostname for a Grid Infrastructure Oracle Restart Standalone Configuration »

How to use google translate URL in Oracle plsql

Last updated on November 14th, 2017 at 04:46 amWe all know google translate which can help translate from one language to another. We will explore option to use google functionality in oracle plsql in oracle database to get translation done. First we need to create Network access list which is required from Oracle 11g   BEGIN DBMS_NETWORK_ACL_ADMIN.CREATE_ACL( acl => ‘google_translatorapi.xml’, description => ‘Google Translator Access’, … Continue reading How to use google translate URL in Oracle plsql »

How to find the waitevent History of the Oracle session

In this post , we will look at the different queries to retrieve the waitevent history of the Oracle session. History of wait events in a specific session from start can be found using below query set lines 120 trimspool on col event head “Waited for” format a30 col total_waits head “Total|Waits” format 999,999 col tw_ms head “Waited|for (ms)” format 999,999.99 col aw_ms head “Average|Wait … Continue reading How to find the waitevent History of the Oracle session »

How to run sql tuning advisor for particular sql -id in the Cursor cache

Last updated on November 14th, 2017 at 04:49 amMany times a sql may be running long and we may need to run sql tuning advisor to get the recommendation to resolve the long running issue.In this post, we would be looking at the steps to achieve it Steps for running  sql tuning  advisor on the particular sql -id in the Cursor cache Create sql Tuning … Continue reading How to run sql tuning advisor for particular sql -id in the Cursor cache »

What you need to know about Oracle Flashback query

Last updated on November 14th, 2017 at 04:53 am Oracle has introduced Flashback Technology  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. With flashback features, you can do the following: Perform queries that return past data Perform queries that return metadata that shows a detailed history of changes to the … Continue reading What you need to know about Oracle Flashback query »

Recover drop/truncate/delete table on primary using flashback on standby database

Many times because of human error, Table accidentally get dropped/truncated or partially deleted in the primary database.Such errors can result in “logical” data corruption or cause one or more components of the IT infrastructure to cease operating We will discuss about recovering from this error by enabling Flashback technology on the standby database.we will be able to recover database without any interruption the primary database … Continue reading Recover drop/truncate/delete table on primary using flashback on standby database »

What is current view APPL_TOP snapshots

What is APPL_TOP snapshots? 1) It is record the current set of files and file versions in your APPL_TOP 2) Current view snapshots are created once and updated when appropriate to maintain a consistent view of the APPL_TOP content 3) Snapshots are current views of your system: they are created once, and then updated when appropriate to maintain a consistent view. There are two types of … Continue reading What is current view APPL_TOP snapshots »

Recover Database after loosing FRA diskgroup

Sometime back ,I  hit a problem where FLASH diskgroup of my ASM cluster went bad and it was set  to external  redundancy . My all database went down and it created lot of panic. I had to go through the below steps to Recover Database after loosing FRA diskgroup Let’s first see what all things are present in FLASH or FRA Second Control-file Multiplex redo … Continue reading Recover Database after loosing FRA diskgroup »