Lost SYS User Password

Make sure that the database and listener services are started. Then open a command prompt and log in as / as sysdba. Then change the sys password.
c:> sqlplus /nolog
SQL> connect / as sysdba
SQL> alter user sys identified by ,new password>;

If you get asked for a password when you log in as / you need to create or recreate the password file using orapw. Check the $ORACLE_HOME/database directory for a file called orapw<SID>. That is the password file for the database. Create (or recreate) it using:
c:\> orawpd file=$ORACLE_HOME/database/orapwd<SID> password=<password> entries=5

Explaining Explain Plan - 1

• When an SQL statement is passed to the server the Cost Based Optimizer (CBO) uses database statistics to create an execution plan which it uses to navigate through the data. This plan is used to execute the statement and the results are achieved.
• If you've highlighted a problem query the first thing you should do is EXPLAIN the statement to check the execution plan that the CBO has created. This will often reveal that the query is not using the relevant indexes, or indexes to support the query are missing.
• The explain plan process stores data in the PLAN_TABLE. The UTLXPLAN.SQL contains the plan_table syntax.

Ques1) How to get the explain plan?

Ans)

There are various ways to get the Explain Plan.

       1) AutoTrace Option: - This option exeutes the statement, gives the output and then gives the explain plan. For e.g.

SQL> set autotrace on
SQL> select * from test;

        C1 C2         C3
---------- ---------- ----------
         1 ABC        X
         2 DEF        X
         3 PQR        X
         4 PQR        X
         5 PQR        Y
         6 PQR        Y

6 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     6 |    36 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |     6 |    36 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        201  recursive calls
          0  db block gets
         45  consistent gets
          0  physical reads
          0  redo size
        623  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          6  rows processed

The drawback of this query is if the statement takes lot of time to execute then we have to wait for that long to get the plan.

       2) Explain Plan: - This options doesn't executes the statement but stores the plan in the PLAN_TABLE. Later the plan table can be queried to get the results. following screenshots shows that

SQL> explain plan for select * from test;

Explained.

To retrieve the plan you can simply query the plan table. If you need the output in a formatted way use the following statment

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     6 |    36 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |     6 |    36 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    21   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |       |            |          |
---------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
         13  recursive calls
         12  db block gets
         41  consistent gets
          0  physical reads
          0  redo size
        963  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          8  rows processed

Ques 2) What is DBMS_XPLAN.DISPLAY ?

Ans:

DBMS_XPLAN.DISPLAY is a function to display the execution plan in formatted way. The function can accept 3 optional parameters:

  1) table_name - Name of the PLAN_TABLE, default value 'PLAN_TABLE'.
  2) statement_id - Statement id of the plan to be displayed. The default value is NULL, which displays the most recent execution plan in the PLAN_TABLE.
  3) format - Controls the level of detail displayed, default value 'TYPICAL'. Other values include 'BASIC', 'ALL', 'SERIAL','ADVANCED'.

If we put in all the 3 options the statement would look like

select * from table(dbms_xplan.display('PLAN_TABLE','1','BASIC')

Ques 3) What is statement_id?

Ans:

The Plan table stores only the recent execution plan. To keep a history of the execution plans you should use STATEMENT_ID while generating the explain plan. syntax would be like

explan plan set statement_id = <abc> FOR <query>

Ques 4) What if i have a different table to store the explain plan.

Ans:

By default explain plan for statement stores the plan in the plan_table created by oracle itself. If you have a different table then the syntax would be like

explain plan into <table_name> <sql>

Now, we know how to get the explain plan. Lets understand about the plan in the next blog.

Oracle Not Available

This post puts in the steps to bring back the oracle server. The syntax are more generic to versions.

1. go to the command prompt
2. sqlplus /nolog
3. conn sys/<pwd> as sysdba -- This would connect as ideal instance.
4. shutdown immediate
5. startup

The database will get mount properly and also open up.

My First Audience

Soon after joining Oracle, I wanted to do something outside the normal routine work. I had no clue on what I want to do. One fine day, a mail came from Oracle University on some program called BarCamp. It mentioned a point that there is no ppt required to conduct sessions. This very point made me interesting to participate. But, what do I speak there, what topic to discuss, how much time to discuss, all were questions with no answers. Then finally decided to speak on "Post Indendepended India and Me". I took this topic not because I had no other topics to talk on, but to emphasis the ME factor.

 I went to the hall along with my friend Girish, thinking that there would be atleast 5-6 people and later some more might join. But to my shock there were none. I felt very sad, and consoled myself to wait for few minutes. After 3-4 minutes 3 people came near the hall but only 2 entered. Finally, for my first session I had 3 audience.

All four of us discussed many factors of development, and importance of ME in this independent India and to surprisingly, the time ran for 60 minutes without any of our notice. At the end of the session, We all carried the message that it is not just sufficient to contribute indireclty ( like donating to a NGO)  for the growth of the nation but directly involve myself in some field which interests ME.

I felt happy, satisfied and more importantly understood the sentence " Its important to participate rather than Winning"...

Database Link

When you wanted to have tables of different database server then go for creating a database link. The syntax for creating the link is

Create database link <linkname>
connect to <user> identified by <pwd>
using 'service name';

The above is for creating a private fixed link. The service name is the alias name given in the TNS entry file. If in case it is not able to read the host properly then copy the description part of the TNS instead of service name.

About PLS_INTEGER Data Type

• When declaring an integer variable, PLS_INTEGER is the most efficient numeric datatype because its values require less storage than INTEGER or NUMBER values, which are represented internally as 22-byte Oracle numbers.
• PLS_INTEGER operations use machine arithmetic, so they are faster than BINARY_INTEGER, INTEGER, or NUMBER operations, which use library arithmetic.
• Use of the PLS_INTEGER data type in PL/SQL involves less internal instructions to process, thus increasing performance. The PLS_INTEGER is useful for counters and integer operations

View Procedure Definition in SQL Plus

To view the procedure definition in SQL* Plus, do the following

1) If ownere of the procedure then execute
      Select text from user_source where name = <proc_name>;

2) If your are not the owner of the procdure then execute
      Select text from all_source where name = <proc_name>;