I used to  kick a test job from sql * plus or Pl/sql developer during the late evening. And i will check the result next day.  But next day when i see the Sql * plus window or any editor window i usually see the error “ORA-03113: END- OF -FILE ON communication channel“.

So what this error is all about and how to resolve this ?

 It is not that critical error as a programmer  you should be concerned with ? Basically this error may rise in Four situations.

1.  Your DBA might have  set a maximum connection time limit on your Oracle session. if your session reaches maximum connection time to the server, your session will automatically disconnected from the server ( your session will be automatically timed out explicitly by the Oracle server). And you will see the above error.

Continue Reading

Have you ever faced this error ora-01652 ?

The above error is also similar to

ora-01652 : Unable to extend temp segment by 256 in tablespace

Every Oracle professional might have faced this error serveral times. In thi post i will discuss about the error , Why the error “Ora-01652 ” occurs and how to solve this problem .

Most propably if you faced the this error while inserting , updating the data , then this might be a space issue.
Oracle allocates the space required for a table expansion in the form of extents. if there is insufficient space in the data files that contribute your tablespace,then oracle throws this error.

If you faced this problem while inserting or updating the data , try to look at the free space available in each tablespace.

 If the tot_pct_used is nearing 98%, then you need to add some space to your tablespace in the form of data files.

You can achieve this by the following statement

ALTER TABLESPACE users
ADD DATAFILE 'c:\oraexp\oradata\users23.dbf' SIZE 100M;

If you face the Ora-01652 error while running a query then there is a different twist to the story.If your query uses heavy sorting, huge joins then this error can also occur.

Continue Reading

Renaming a table is not possible until Oracle 8i. From Oracle 8i you can rename a table , all the correspoding dependencies would be automatically updated by Oracle.

Rename table in Oracle

  can be done by two ways.
Either using the Rename statement or Alter Table statement.

RENAME statement syntax

Rename old_table_name to New_table_name.

Ex:

Rename emp to emp_temp

Rename table by using ALTER TABLE:

syntax :

Alter table (tab_name_old) rename to (tab_name_new);

Ex:

Alter table emp rename to emp_temp;

The NVl, NVl2, COALESCE functions are used to check a column, a variable, or an expression for NULL value.  In oracle database NULL value is identified as “No value” or “Nothing” or “undefined”.  Some records in the tables permit NULL values. In earlier versions of oracle database 7.3, NVL function is mainly used to check whether any column in a particular record is NULL or not.

NVL FUNCTION accepts two arguments.

Syntax :

NVL( arg1, arg2)

if argument1 is NULL , the NVL function returns argument2 else NVL function returns argument1.

if arg1 is NULL THEN

RETURN ARG2

ELSE

RETURN ARG1

END IF

Continue Reading

Until Oracle 8i any Oracle session can handle only a single transaction at any given point of time. If a transaction “A” is started, no other transaction can be executed until the transaction “A” is commited or Rollbacked. But by the Autonomous transaction feature provided by Oracle, another transaction “B” can be completed suspending the Transaction “A”  and resuming it after the completion of transaction “B”.

Transaction “A” can be termed as main transaction. Transaction “B” can be termed as Autonomous transaction.

An Autonomous transaction is executed as independent and out of the scope of main transaction.An autonomous transaction is a transaction that is started with in context of another transaction. An autonomous transaction can be committed or rolled back regardless of the main transaction.

An Autonomous transaction has to be defined in a  Pl/sql block other than the block where main transaction is executed. You can define any Pl/sql block  like anonymous block, procedure, function, package procedure, package function, database trigger as an autonomous transaction.

To define a Pl/sql block as an autonomous transaction , you can use the statement below in the declaration section of a PL/SQL block.

PRAGMA AUTONOMOUS_TRANSACTION;

You can put the autonomous transaction pragma anywhere in the declaration section of PL/SQL block. The above PRAGMA directs the pl/sql compiler to consider this block as an  AUTONOMOUS TRANSACTION block.

Continue Reading

Using DBMS_STATS to Manage Statistics

DBMS_STATS package  provides critical functionality for the cost-based optimizer, including  the analyzing process extended to PL/SQL programs, allowing statistics to be modified, reverting back to previous statistics, and copying statistics from one schema (or database) to another.

By USING DBMS_STATS package you can

• You can collects statistics on tables and Indexes
• Modify Statistics on tables.
• Save statitstics at a certain point of time
• Revert back statistics to the Previously saved statistics.
• Copy Statistics from One Schema to another Schema.
• Import statistics from one database to another

To Collect Statistics on a table faster Use DBMS_STATS

DBMS_STATS offers two powerful ways of speeding up the analyze process. First, you can analyze tables (not indexes) in parallel. Second, you can analyze only tables and their associated indexes that have had more than 10% of their rows modified through INSERT, UPDATE, or DELETE operations.

Continue Reading

The Explain Plan command displays the execution plan selected by the Oracle OPtimizer for DML statements. The execution Plan is sequence of Operations Oracle performs when it runs the SQL statement.EXPLAIN PLAN  can provide the execution path of an sql statement. This statement inserts a row describing each step of the execution plan into a specified table. If you are using cost-based optimization, this statement also determines the cost of executing the statement

The EXPLAIN PLAN provides the infomation

1. List of all tables involved in the query.
2. The access method against each table
3. Join methods for tables involved in Join operations.
4. Filter and Sort Operations.
5. The cost of Each Operation.

Using EXPLAIN PLAN

Before You can use the explain plan command , you must have a PLAN_TABLE in the current schema using the table definition available utlxplan.sql provided by Oracle. The script is found in $ORACLE_HOME/rdbms/admin.

Run the utlxplan.sql in the Sql* Plus  Prompt. Once the PLAN_TABLE  table has been built, you can use the explain plan command on select statements.

Example of explain Plan Usage

EXPLAIN PLAN
SET STATEMENT_ID= 'MY_FIRST_EXP'
FOR
SELECT * FROM EMP WHERE EMP_NO=34030;

To check the execution plan you can query PLAN_TABLE

select * from PLAN_TABLE WHERE statement_id='MY_FIRST_EXP'

The output from the above query would not result in showing the  Execution steps in a clear and understandable format.

Use the following query to get execution path in an clear format

SELECT LPAD( ' ', 2 * ( LEVEL - 1 ) ) ||
  OPERATION || ' ' ||
  OPTIONS || ' ' ||
  OBJECT_NAME || ' ' ||
  OBJECT_TYPE PLAN,
  COST
  FROM PLAN_TABLE
 WHERE STATEMENT_ID = 'MY_FIRST_EXP'
CONNECT BY PRIOR ID = PARENT_ID
  AND PRIOR STATEMENT_ID = 'MY_FIRST_EXP'
 START WITH ID =1;

Columns in the PLAN_TABLE

STATEMENT_ID: user-defined with STATEMENT_ID when executing EXPLAIN PLAN
TIMESTAMP: Date and time when the EXPLAIN PLAN statement was issued.
OPERATION: Name of the internal operation performed in this step.
OBJECT_INSTANCE: the ordinal position of the object as it appears in the original SQL statement.
OBJECT_TYPE: descriptive information about the object.
OPTIMIZER: Optimizer mode
ID: The number assigned to this step in the execution plan.
PARENT_ID: The ID of the next execution step that operates on the output of the this row’s ID. This is the linked-list key.
POSITION: For the first row of output, indicates the CBO’s cost of the statement. For the other rows, indicates the position relative to the other children of the same parent.

USERENV function returns information about user environment. USERENV returns information of VARCHAR2 datatype about the current session.you cannot use USERENV in the condition of a CHECK constraint.

To find session id you can use the following query

 SELECT USERENV('SESSIONID') FROM DUAL

or

You can also use it in an PL SQL anonymous block as

declare
 v_sess varchar2(50);
 begin
 v_sess:=USERENV('SESSIONID');
 dbms_output.put_line(v_sess);
 end;
 /

To Find the Language and character Set of User environment

SELECT USERENV('LANGUAGE') FROM DUAL

To Find the Whether has been authenticated as having DBA privileges, Below query will return true if user is authenticated as SYSDBA.

SELECT USERENV('ISDBA') FROM DUAL

USERENV is a legacy function that is retained for backward compatibility. You can also use the SYS_CONTEXT function to get the session id .SYS_CONTEXT returns the value of parameter associated with the context namespace.SYS_CONTEXT function can be used in both SQL AND PL/SQL staements.

Syntax of SYS_CONTEXT function

SYS_CONTEXT(NAMESPACE, attribute,[length])

Oracle9i Provides a built-in namespace called USERENV Which describes the current session.

SELECT SYS_CONTEXT ('USERENV', 'SESSIONID') FROM DUAL

To Find the Session User name by using SYS_CONTEXT function

SELECT SYS_CONTEXT('USERENV','SESSION_USER') FROM DUAL

SESSION_USER indicates the Database user name by which the current user is authenticated. This value remains the same throughout the duration of the session.

How to view source code of a Function ?
How to view source code of a Prodcedure ?
How to view source code of a Package ?
How to view source code of a Trigger ?

you can view the source code of a function or a procedure by using data dictionary views. All the  PL SQL objects like types, procedure, functions, Packages code can be viewed from  a view called “USER_SOURCE“.

User_source data dictionary view provides the source code of all the PL SQL objects.
Each line of code represents a single row in this dictionary view.
If you want to view the source of PL SQL OBJECTS created by you then use dictionary view “USER_SOURCE“.

If you want to view the source of PL SQL OBJECTS created by others(other users) but have an execute permisssion on the OBJECTS, then use dictionary view “ALL_SOURCE“.

IF you have ‘SELECT ANY DICTIONARY’ Previliege,  then  you can  view the source of all the pl/sql objects  from the dictionary view DBA_SOURCE.

want to view the code of a FUNCTION in oracle ?

select * from user_source where type='FUNCTION' AND NAME='FUNCTION_NAME' ORDER BY LINE;

want to view the code of a PROCEDURE in oracle ?

select * from USER_SOURCE where type='PROCEDURE' AND NAME='PROCEDURE_NAME' ORDER BY LINE;

want to view the code of a PACKAGE SPECIFICATION in oracle ?

select * from USER_SOURCE where type='PACKAGE' AND NAME='PACKAGE_NAME' ORDER BY LINE;

want to view the code of a PACKAGE BODY in oracle ?

select * from USER_SOURCE where type='PACKAGE BODY' AND NAME='PACKAGE_NAME' ORDER BY LINE;

want to view the code of a TRIGGER in oracle ?

select * from USER_SOURCE where type='TRIGGER' AND NAME='TRIGGER_NAME' ORDER BY LINE;

want to view source code of a procedure created by another user?

select * from user_source where type='PROCEDURE' and NAME='PROCEDURE_NAME' and owner='USERS_NAME' order by line;

want to view the source code of a Function created by another user in oracle ?

select * from user_source where type='FUNCTION' AND OWNER='USERS_NAME' AND NAME='FUNCTION_NAME' ORDER BY LINE;

Some body says ” i have given a hint  in oracle. But it is not working”. Some says ” My hints are Ignored in Oracle”.

The Basic reasons for the above cause can be