Recently after I configured database on testing machine and configure the forms to run on middle tier on testing machine (all windows environment), I came across the error ORA-12638.

After little bit of brains storming and checking online, I found out that one of the way to solve this error is to disable the Oracle Advanced Security Authentication and let database use the supplied user name and password to authenticate.
It can be disable by updating the sqlnet.ora file with the following entry:
SQLNET.AUTHENTICATION_SERVICES = (NONE)

Note : Default entry in my case was SQLNET.AUTHENTICATION_SERVICES= (NTS)
Guys do check out other method and let me […]

[Read more →]

Recently while doing R&D, I came across the ADR Command Interpreter (ADRCI).
Introduced in Oracle Database Release 11g, it is a command-line tool that one can use to manage diagnostic data.
Diagnostic data includes incident and problem descriptions, trace files, dumps, health monitor reports, alert log entries, and more.
ADRCI has a rich command set, and can be used in interactive mode or within scripts.
In addition, ADRCI can execute scripts of ADRCI commands in the same way that SQL*Plus executes scripts of SQL and PL/SQL commands.
It enables us to:

View diagnostic data within the Automatic Diagnostic Repository (ADR).
View Health Monitor reports.
Package […]

[Read more →]

Yesterday I was doing some r&d and tried to find trace file for my session.
After lot of trouble I was able to find them.
Last time its was a simple task, I just need to look at $ORACLE_BASE/SID/udump directory of the database server.
But now trace files are stored in the the trace directory under Automatic Diagnostic Repository (ADR) home.
To get the location of individual trace files we can use data dictionary views as follow:
To find the trace file for your current session:

SELECT value
FROM v$diag_info
WHERE name = ‘Default Trace File’;
This query will return the full path to the trace file.

To find all trace files […]

[Read more →]

As per my experience when working with PL/SQL code, DBMS_OUTPUT is one of the most frequently used package to display debugging information.
But frequently when using 10G R1 and earlier versions, we come across something like as follows:
ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
Single line limit of 255 bytes exceeded.
ORA-20000: ORU-10027: buffer overflow, limit of bytes
Maximum amount of 1 million bytes per session exceeded.
10G R2 increases the single length line limit to 32,767 bytes and the overall session limit is removed altogether.
Note: Now myself and my team use our own procedure called LOGIT for debugging, I will […]

[Read more →]

Check out following:
SQL> select * from test;
COL1
———-
1
1
1
1
1
SQL> alter table test add constraint test_idx primary key(col1) disable;
Table altered.
SQL> create index test_idx on test(col1);
Index created.
SQL> alter table test enable novalidate constraint test_idx;
Table altered.
SQL> insert into test values(1);
insert into test values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.TEST_IDX) violated

Bookmark […]

[Read more →]

When working with virtual columns recently I came across the following error:
ORA-30553: The function is not deterministic
Cause: The function on which the index is defined is not deterministic
Action: If the function is deterministic, mark it DETERMINISTIC. If it is not deterministic (it depends on package state, database state, current time, or anything other than the function inputs) then do not create the index. The values returned by a deterministic function should not change even when the function is rewritten or recompiled.
So I though of writing about DETERMINISTIC Functions as I find it very important piece of information when working with […]

[Read more →]

For last few days I was busy packing my things plus doing some important task related to my Australia trip but at last got time to write about something new in Oracle 11g called Virtual Columns.
First lets go through one situation, we have a sales table with Gross_Amount and Discount_Amount.
Now
Net_Amount := Gross_Amount - Discount Amount;
We can tell our guys to use this formula everywhere. Now if formula changes due to some reason, then we will need to change the formula everywhere.
Other way to tackle such situation is to create the separate view (formula embedded in it) and everybody use that […]

[Read more →]

Today I came up with this challenge of creating UNIQUE index on a table with exiting non unique value. After lot of r&d I came up with following very interested solution:
I am using scott schema to show my solution.
SQL> create table test as select deptno from emp;
Table created.
SQL> select deptno from test;
DEPTNO
———-
20
30
30
20
30
30
10
20
10
30
20
DEPTNO
———-
30
20
10
14 rows selected.
SQL> alter table test add constraint unique_index unique(deptno) disable;
Table altered.
SQL> create index unique_index on test(deptno);
Index created.
SQL> alter table test enable novalidate constraint unique_index;
Table altered.
SQL> insert into test values (20);
insert into test values (20)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UNIQUE_INDEX) violated

Bookmark this post

[…]

[Read more →]

After what I find yesterday (Enhancements in Oracle 11g PL/SQL), I research more about Oracle 11g. Found out few more things about it.
Database 11g beta includes 482 new features designed to address a range of database issues, from the need to retain more information to data compression to the handling of parallel upgrades, said Chuck Rozwat, executive vice president of Oracle server technologies, during his keynote speech annual users conference in San Francisco this year (2006).
The key theme of 11g is to provide “change assurance”.
The following are some of the new features:

Compression technology that can reduce the amount of storage […]

[Read more →]

Before Oracle 10g Release 2, Life cycle of a transaction was as follows:
- Transaction Starts When User Issues the First DML, Oracle generates redo entries corresponding to the DML and write it to buffer in memory.
- While user is issuing DML, Oracle generates redo entries corresponding to the DML and write it to buffer in memory.
- Transaction Ends When User Issues a COMMIT (can be explicit or implicit), Oracle immediately writes this buffered redo to disk.
Main point to understand here is that once COMMIT is issued, Oracle does not return the control to the user until the redo entries corresponding […]

[Read more →]