Oracle TWO_TASK Environment Variable

TWO_TASK variable is for Unix environment, there is a similar variable for Windows - LOCAL.

In the Unix environment there are 2 variables that are generally set to connect to a local database that resides on the same machine - the machine from which you are trying to connect:

ORACLE_HOME
ORACLE_SID

So, if you type 

vish> sqlplus username/password@mydb

or

vish> sqlplus username/password

Both will result in connecting to mydb - considering that the above 2 variables are pointing to mydb.

That was for your very own local database.

If you have to connect to a remote database, you have below options:

1.  TNS Names - Alias to a connect URL
2.  Typing in the entire Connect URL - the one that goes into TNS names file
3.  TWO_TASK variable

First two options you would already be familiar with - and anyways this article is about TWO_TASK so...

TWO_TASK - this is an environment variable so you can set its value similar to above 2 mentioned variables.

This variable expects the connect URL that goes into TNS names file.  For example:

TWO_TASK = <TNS Connect String>
TWO_TASK = (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=port))  (CONNECT_DATA=(SERVICE_NAME=service_name)))

And now when you type below command from your command prompt - it will directly connect to this remote database
vish> export TWO_TASK = <remotedb TNS Connect String>
vish> sqlplus username/password

So, TWO_TASK basically overrides ORACLE_SID parameter and would connect to Remote database that you have mentioned as a value of this variable.

SQLLOADER - Error Field in data file exceeds maximum length

If you do not specify length of a character column in your control file it defaults to CHAR(255).  And because of this reason most of the times you will see above error.  Whenever there is a chance that your number of characters of your column are going to be more than 255 - specify the length explicitly.

Instead of 1 below use 2:
1. load data
   APPEND
   into table t
   fields terminated by '|'
   TRAILING nullcols
   (col1,
    col2,
    col3)

2.  load data
    APPEND
    into table t
    fields terminated by '|'
    TRAILING nullcols
    (col1 CHAR(4000),
    col2,
    col3)

Query to find child tables

A simple select query to find child table of a particular table:


SELECT parent.table_name AS "PARENT TABLE", child.table_name AS "CHILD TABLE", child.R_CONSTRAINT_NAME, cols.column_name 
FROM user_constraints parent, user_constraints child, user_cons_columns cols 
WHERE parent.constraint_type = 'P' AND 
child.constraint_name = cols.constraint_name and 
parent.CONSTRAINT_NAME = child.R_CONSTRAINT_NAME AND  
child.CONSTRAINT_TYPE = 'R' AND  
parent.table_name = <table_name> 

ORA-01654: unable to extend index SYS.I_PLSCOPE_SIG_ACTION$ by 128 in tablespace SYSAUX

I got below error while trying to deploy an already successfully compiled Oracle package from my QA environment to dev environment:

ORA-00604: error occurred at recursive SQL level 1

ORA-01654: unable to extend index SYS.I_PLSCOPE_SIG_ACTION$ by 128 in tablespace SYSAUX

00604. 00000 -  "error occurred at recursive SQL level %s"

*Cause:    An error occurred while processing a recursive SQL statement

           (a statement applying to internal dictionary tables).

*Action:   If the situation described in the next error on the stack

           can be corrected, do so; otherwise contact Oracle Support.

I had taken backup of the original package from my dev environment before I tried to overwrite it with the one from QA – so I tried compiling this old package again and surprisingly it worked just fine.

I got confused – googled around and found that the above error could be solved by setting value of PLSCOPE_SETTINGS to IDENTIFIERS:NONE from IDENTIFIERS:ALL:

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';

So there are couple of oracle data dictionary views related to this:

USER_PLSQL_OBJECT_SETTINGS

USER_IDENTIFIERS

I took a look at these views and found that for the package that I was trying to compile - the PLSCOPE_SETTINGS value was IDENTIFIERS:ALL.

But I was little skeptical of altering the setting since I did not know the impact of that.

I googled more and came across one post mentioning that this issue is just with Oracle SQL Developer – it did not sound believable but then I thought that these settings might be related to PL/SQL environment in which I am trying to compile the package.

So, I tried compiling the package using SQLPLUS command prompt and it worked  - YEAH!!! J

Useful points about Oracle APPEND Hint

Some points to keep in mind while using APPEND hint in ORACLE:

I am listing down some points that I came across and had to consider while using an APPEND hint.  This might not be a complete list - just throwing out which I think are useful.   

It does a direct path insert

Adds rows above high water mark

Parallel hint is always a direct path, if you go parallel, you will be appending.

Insert /*+ append */ will ignore the append hint and use conventional path loading when the table has referential integrity or a trigger.

Only one session can do a direct path load on a table, no other session can do direct path on the same table.  Also no other session could also issue insert, update or delete statements on this table.

Feel free to add more points in the comments section...

Hello blog

Hey Readers,

I am quite new to the blogging world.  I had been thinking since sometime for now to start writing about the technology that I work on - Oracle.  I would love to say that I never got enough time to start writing - but sadly that's not true.  I was (and am) just lazy to do so.  

But here I am shrugging off my laziness and writing the first post...

I will be penning down my experiences with Oracle technology - database in specific.  I am not looking to write neat and sophisticated blogs - but I hope they will be detailed enough to be understood by everyone...

Thanks for visiting..keep reading..