How to Flush Single SQL Plan out of Shared Pool in Oracle

Todays let ‘s have look at the query which we use to resolve this issue ” How to Flush Single SQL Plan out of Shared Pool”. 

 

 

Find the address and the hash value

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='495fdyn7cd34r';

ADDRESS HASH_VALUE

---------------- ----------

00000005ODJD9BC0 247122679

Execute the purge procedure

SQL> exec DBMS_SHARED_POOL.PURGE ('00000005ODJD9BC0,247122679','C');

PL/SQL procedure successfully completed.

Thank you for giving your valuable time to read the above information.

(more...)

Check Materialize View Refresh Schedule in Oracle

This post is about the view which we use in Oracle.

What is  Materialize View?

The view which we use to make a replica of a target master from a single point in a time is known materialize view.

As we know why do we need materialized view in Oracle?

  • redesign the system and eliminate those “tough” queries
  • cache the results of such queries
  • using materialized views.

Let’s have a look at the query which we use (more...)

Startup Procedure in Oracle

We are going to have look at the most basic but most important part of Database. It is the first step of Database. We can call it initial stage of Database. Let’s have look at this image. It indicates some hidden simple and most meaningful message. Ladders are to meant to climb only it shows we have to go step by step towards our goal we can’t skip any single step to reach the top.

In (more...)

Progress status of index rebuild in Oracle

 

The game never ends after the creation of anything. It starts when it comes to the phase of maintenance. Today we are going to have look on the query which is used to lookProgress status of index rebuild/creation or long operation”. 

 

 

Let’s have look on the query.

select sid, target, to_char(start_time,'hh24:mi') start_time, elapsed_seconds/60 elapsed, round(time_remaining/60,2) "min_remaining",
message from v$session_longops where time_remaining > 0;

v$session_longops will show you the number of blocks scans out of the total block. Once (more...)

Change User Password in Oracle

In the world of technology, we use “USERNAME and PASSWORD “ multiple time in a day. As per nature, rule “CHANGE is a must.  Oracle Database also provides the option to change user’s password. The only superuser has a privilege to change the password.

Let’s have look at the steps which we use to change the password.

The user can able to change the password by login

#sqlplus username_1@service_name_1

SQL> password
Old password:
New  (more...)