ORA-01422: exact fetch returns more than requested number of rows

ORA-01422: exact fetch returns more than requested number of rows

Cause: The number specified in exact fetch is less than the rows returned.

Action: Rewrite the query or change number of rows requested

---

With Oralce ORA-01422 error the cause would be you tried to execute a SELECT INTO statement and more than one row was returned. This error is on oracle PL/SQL lanugauge.

Here are the option that you could do.
1. rewrite your sql statement so it return only one record.
2. use an exception in case of unexpected scenario.
The exception syntax would look like this
Syntax:
BEGIN

your sql statement here
ie. select a, b, c into d, e, f from i;
EXCEPTION
WHEN exception1 [OR exception2...]] THEN
...
[WHEN exception3 [OR exception4...] THEN
...]
[WHEN OTHERS THEN
...]

your code need to be something like

BEGIN

your sql statement here
ie. select a, b, c into d, e, f from i;
EXCEPTION
WHEN TOO_MANY_ROWS THEN
do something ie. raise error
WHEN OTHERS THEN
do something
END;

3. use cursor for more information on cursor take a look at
http://www.oracle-base.com/articles/8i/UsingRefCursorsToReturnRecordsets.php.
http://www.psoug.org/reference/ref_cursors.html

Read More

ORA-00001: unique constraint (string.string) violated

ORA-00001: unique constraint (string.string) violated

Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level.

Action: Either remove the unique restriction or do not insert the key.

---

the sample message might be as below

insert into student(pk_id, fieldname) values(1, '')
*
ERROR at line 1:
ORA-00001: unique constraint (PROMIS.SYS_C0012876) violated

With the example above we knew that it must be at table student. But if the query is more complex it might be hard to locate the problem constraint. If you already know where the prob is go to step 2 otherwise go to step 1

Step 1.> Locate the problem
To locate where might be the problem you may need to use the following query to find which table is your problem.
select distinct table_name
from all_indexes
where index_name = 'CONSTRAINT_NAME';

replace the CONSTRAINT_NAME with your constraint code (as the example above it is the text with the underline, SYS_C0012876. So the query would be:

select distinct table_name
from all_indexes
where index_name = 'SYS_C0012876';

and you will get the table name which cause the problem.

Step 2.> solve the problem
The options to resolve this Oracle ORA-00001 error are:

1. Modify your SQL so that a duplicate value is not created.
2. Change the constraint to allow duplicate values.
3. Diable the unique constraint.
4. Drop the unique constraint.

from 1-4 I would say it is likely that the first one is quite the right solution for me, as the constraint itself had the reason to be there from the first place. I suggest stick to number 1 to be on the save side, unless you know what you are doing.

Read More

ORA-00936: missing expression

ORA-00936: missing expression

Cause: A required part of a clause or expression has been omitted. For example, a SELECT statement may have been entered without a list of columns or expressions or with an incomplete expression. This message is also issued in cases where a reserved word is misused, as in SELECT TABLE (see case 2 below).

Action: Check the statement syntax and specify the missing component.


1. if you are using prepare statement to connect to oracle with vb or asp you might as get this error as using named parameters. The reason is that OLEDB doesn't support named parameters. e.g.


here i have the example of how to do it

Dim objCmd

Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn

objCmd.CommandType = adCmdText


objCmd.CommandText = "{CALL procedurename(?,?,?,?,?,?)}"

objCmd.Parameters.Append objCmd.CreateParameter("@a", adVarChar, adParamInput,16)
objCmd.Parameters.Append objCmd.CreateParameter("@b", adVarChar, adParamInput,32)
objCmd.Parameters.Append objCmd.CreateParameter("@c", adDouble, adParamOutput)
objCmd.Parameters.Append objCmd.CreateParameter("@d", adVarChar, adParamOutput,255)
objCmd.Parameters.Append objCmd.CreateParameter("@e", adDouble, adParamOutput)
objCmd.Parameters.Append objCmd.CreateParameter("@f", adDouble, adParamOutput)

objCmd("@a") = sString
objCmd("@b") = sString2

objCmd.Execute

If ERR = 0 Then

o_errnum = objCmd("@c")
o_errStr = objCmd("@d")
o_usertype = objCmd("@e")
o_userid = objCmd("@f")

isValidLogin = True

End If
Set objCmd = Nothing

with this example you can use procedure to pass the paremeter to oracle and at the same time get the output parameters. The keyword is adParamOutput and adParamInput plus you need to specified parameter type make sure they are the same with your oracle procedure. Otherwise you will get an error.

2. If you are not running procedure. The other possibly cause of the error "ORA-00936: missing expression" might be that you tried to execute a SELECT statement and forget to the list of the columns in the SELECT statement.
i.e.

select
from tablename;

this also would raise you the same error

So the solution would be "Check the statement syntax and specify the missing component". If you are calling procedure using ? in your preparestatement not @ as I had already said "OLEDB doesn't support named parameters"

Read More

EXP-00056: ORACLE error 6550 encountered

EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 41:
PLS-00302: component 'SET_NO_OUTLINES' must be declared
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored
EXP-00000: Export terminated unsuccessfully

You would get this error when you are trying to export your database with exp binary that is not compatible with your database
The cause of this error is, as the above message had already mentioned, that you are tring to export your database from client with oracle version higher
than the server version ie. using oracle 10g to export from oracle database 9i.



The solution are
- first option: you need exp program with the same version (or lower one) as your database, which mean
you need to find the client machine that have the same oracle version or lower installed
or
you may need to reinstall your oracle client.

- second option: if you have an access to your server, physically or remotely, simply just logon and use the exp command from the command line.

Read More

Step by Step Installing Red Hat Linux on VmWare

Installing Red Hat over VmWare - part 1




Installing Red Hat over VmWare - part 2





Installing Red Hat over VmWare - part 3




Installing Red Hat over VmWare - part 4

Read More

SUN'S REFERENCE ARCHITECTURE FOR ORACLE 11g GRID

Sun's Reference Architecture for Oracle 11g Grid is a portfolio of Sun and third-party products designed to reduce the risks, uncertainty, and costs associated with implementing an Oracle database within a grid computing environment. Designed, tested, implemented, and tuned at the Sun Competency Center for Oracle in Menlo Park, California, the reference architecture consists of recommended, integrated hardware and software stacks for a proven grid database solution. Along with this architecture, Sun provides a best practices framework for obtaining optimal performance, availability, and resiliency.

The most compelling benefits of Sun's Reference Architecture for Oracle 11g Grid come not from the individual components, but from the ability to integrate complementary elements - whether they come from Sun or another vendor. The combination of Sun Fire x64 servers with AMD Opteron processors and Cisco InfiniBand technology ensures higher database throughput, while the adoption of Solaris Cluster Advanced Edition for RAC enhances the reliability and availability of Oracle RAC 11g. The result is a solution that can help deliver the highest service levels and satisfy the most demanding business requirements of a database grid.

Get this white paper at:
http://searchoracle.bitpipe.com/data/document.do?res_id=1200499879_537

Read More

Oracle 10g step by step installation video on windows platform

I've just found a good tutorial video on how to install Oracle 10g step by step on windows platform from youtube.com. There are 4 videos altogether. These 4 video are a a basic oracle installation good for oracle newbie. Have a look for yourself.


Step by step guide on installation oracle 10g database part I



Step by step guide on installation oracle 10g database part II





Step by step guide on installation oracle 10g database part III




Step by step guide on installation oracle 10g database part IV



Reference:youtube.com

Read More

Testing Oracle 10g RAC Scalability

I have just found a good video on youtube about Oracle 10g RAC Scalability, This video is very informative. If you are considering of deploying Oracle RAC on your organization this is a cool one to watch. Below is the detail I grab from youtube video description:

Oracle Real Application Clusters (RAC) offer businesses the ability to create an environment where more than one database server, or node, can be setup to manage an Oracle database. Organizations often face multiple challenges when implementing RAC and maintaining RAC configurations, including:

Determining how many database servers are needed for sustained optimal
performance in a cluster
Defining the proper testing methodology of a cluster configuration for load testing, scalability and availability
This Webcast will provide you with:

A proven methodology for determining the most optimal configuration for your Oracle RAC environment.
A real-world scenario in which Quest's solutions were leveraged for Oracle RAC to conduct diagnostic and benchmark tests on Oracle Real Application Clusters.

and the video is here you don't have to leave to youtube to watch it, just click on the play icon.

Video Title: Testing Oracle 10g RAC Scalability.
Time: 35.49 minutes.

Read More

Ora Tip: How much did you assign your memory for oracle sga and pga?

If you want to know how much does your oracle database taking up memory for SGA and PGA just login to your oracle database with the following command on the command prompt

if you can access to the server physically or remotely
sqlplus "/ as sysdba"

otherwise use this command

sqlplus "sys@ora_service as sysdba"

SQL>show SGA
you would see something like the following lines but difference on number.

Total System Global Area 209235968 bytes
Fixed Size 1298920 bytes
Variable Size 109055512 bytes
Database Buffers 96468992 bytes
Redo Buffers 2412544 bytes

this is how your system Global Area taking your memory space every time your database is running.

to see what is your SGA size just run the following command

SQL>show parameter pga

and you would get the folloing result

pga_aggregate_target big integer 75M

this is how much your pga consume your memory.

Read More

TNS-12535: TNS:operation timed out

There might be sometime that when you try to connect to Oracle Server and you get the error "TNS-12535: TNS:operation timed out". It seem that the server does not exist or the listener does not start or both of them. The first thing you need to have a look is
the tnsnames.ora which could be found at
- $ORACLE_HOME/network/admin for unix like OS ie. every distribute of Linux, Solaris etc.
- ..\oracle\product\10.2.0\ for Windows Platform

in this tnsnames.ora will look like this

ora_service_name =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORA_Server)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORA_SID)
)
)

there are 2 things you need to have a look there.

The first one is to check if your Ora_SID is correct, if you don't know please contact your dba admin and ask for it.

The Second thing you need to take a look at is the HOST part host/ip parth (HOST=....) this is where your oracle server is.

If both Ora_SID and HOST are correct then try to ping the server to see if you can reach the server (there might be the case that the network admin turn the ping port of for security reason though, in this case you are behind firewall).

To make sure if you are really behind the firewall try telnet the server with this syntax
telnet server_IP 1521 ( or whatever your Oracle listening port is). If you get the following error

Connecting To your server_ipd...Could not open connection to the host, on port 1521:
Connect failed

then 90% you are behind firewall, please contact your network administrator to turn the firewall on port 1521 on.

Read More

ORA-00600: internal error code, arguments: [19004],

If you ever come across the error code "ORA-00600: internal error code, arguments: [19004], [], []...." while running query ralated to any table, view, index
here is the solution for it

login to your oracle database using pl/sql tool

run the below command on the command prompt

1. in case of error on querying table

ANALYZE TABLE table_name delete statistics;

2. in case of error on querying view

put the command above with related tables in the view. i.e run analyze tables for each table that form that view.

reference:http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4005.htm

Read More

Oracle RAC Test on VMWare Linux + openfiler SAN/NAS storage

For people who don't want to spend much money on SAN storage just for the purpose of testing Oracle RAC feature.
One of the option you can do is installing VMWare on your computer and set up 2 Linux virtual servers on it. You will also need another virtual server to set up as openfiler to act as SAN storage. The hardware requirement you need to have are 3 GB of memory, 50 GB of hard disk (excluding your host OS space).
For more information on how to do this you can drop in to http://www.oracledba.ca/ there are step by step + image guide for you.

Read More

Runing Oracle on Linux + VMWare

There are many website talked about oracle installation on Linux which is running on VMWare.
There are many comments I come across mentioned that this must be for the testing or developing purpose only.

I'd myself rather think differently. If you have computer with huge enough resources you can set it up as your virtual servers and running on your network along with physical servers. It depend of the art of tuning your VMWare , Linux, Oracle, with new oracle improved functionality it could be easier to manage. I myself, used to train my 10+ clients and had them connected to my laptop (at the time 2GB Memory), runing VMWare with Linux and Oracle installed, it worked just fine.

The thing is, it's on the art of put the right tools/machines for the right task. With well configuration of your Oracle, VMWare, Linux plus your host resources, your virtual servers will work like a charm.

Read More

FIX EXP-00008: ORACLE error 604 encountered while exporting full database

if you had the below error (Error 1) while exporting full database
login as sys user and execute the following sql command at sqlplus prompt

GRANT EXECUTE ON sys.lt_export_pkg TO exp_full_database;

and if after running the above sql command and you got the an error (Error 2).
login as sys user and then execute the file
$ORACLE_HOME/rdbms/admin/owminst.plb to validated LT_EXPORT_PKG .
at sqlplus prompt
>@$ORACLE_HOME/rdbms/admin/owminst.plb
ie.
>@$D:\oracle\ora90\rdbms\admin\rdbms\admin\owminst.plb


Error 1
EXP-00008: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 8
ORA-06550: line 1, column 13:
PLS-00201: identifier 'SYS.LT_EXPORT_PKG' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_i
nfo_exp

Error 2
EXP-00008: ORACLE error 4068 encountered
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "SYS.LT_EXPORT_PKG" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling SYS.LT_EXPORT_PKG.schema_info_exp

Read More

Oracle RAC Test on VMWare Linux + openfiler SAN/NAS storage

For some of you who don't want to spend much money on SAN storage just for the purpose of testing Oracle RAC feature.
One of the option you can do is install VMWare to set up Linux on it. And you need openfiler to act as SAN storage. for more information on how to do this you can drop in to http://www.oracledba.ca/ there are step by step + image guide for you.

Read More