FranklinFaces.com - Oracle & SQL Server Database Forums for all IT Professionals / Oracle Forum / ORA - Oracle Database Error Codes - Search via specific error codes. / ORA-00000 Through ORA-09989

Getting ora error ORA-00054: resource busy when gathering stats.

Admin — Thu, 15 Jan 2009 01:21:39 GMT

Anyone have an idea of what to do about this?

I have GATHER_STATS_JOB running daily on our production box. Majority of the runs it gives the following error in the alert log (but no error is seen in the job history log):

GATHER_STATS_JOB encountered errors. Check the trace file.
ORA-00054: resource busy

The trace file holds the following:

ORACLE V10.2.0.3.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Release 10.2.0.3.0 - Production
Windows NT Version V5.2 Service Pack 1
CPU : 2 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:732M/2046M, Ph+PgF:2481M/3947M, VA:942M/2047M
Redo thread mounted by this instance: 1
Oracle process number: 15
Windows thread id: 8712, image: ORACLE.EXE (J001)
*** 2007-12-08 06:01:26.781
*** ACTION NAME:(GATHER_STATS_JOB) 2007-12-08 06:01:26.781
*** MODULE NAME:(DBMS_SCHEDULER) 2007-12-08 06:01:26.781
*** SERVICE NAME:(SYS$USERS) 2007-12-08 06:01:26.781
*** SESSION ID:(135.52155) 2007-12-08 06:01:26.781
ORA-00054: resource busy and acquire with NOWAIT specified
*** 2007-12-08 06:01:26.781
GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYS"','"WRI$_ADV_OBJECTS"','""', ...)
ORA-00054: resource busy and acquire with NOWAIT specified

ORA-01788: CONNECT BY clause required in this query block ora error code

Admin — Wed, 16 Jun 2010 11:49:37 GMT

Ora Error ORA-01788: CONNECT BY clause required in this query block

I'm getting following error in oracle 11gR2:

SQL> select max(level) from test;
select max(level) from test
*
ERROR at line 1:
ORA-01788: CONNECT BY clause required in this query block

SQL> select level from dual;
select level from dual
*
ERROR at line 1:
ORA-01788: CONNECT BY clause required in this query block

ORA-01400: cannot insert NULL into (string) ora error ora 01400

Admin — Tue, 15 Jun 2010 16:57:21 GMT

ORA-01400:

cannot insert NULL into (string)

ora 01400

ORA-01400:

cannot insert NULL into (string)

Cause:

An attempt was made to insert a NULL into the column "USER"."TABLE"."COLUMN".

For example, if you enter:

connect scott/tiger create table a (a1 number not null); insert into a values (null);

Oracle returns:

ORA-01400 cannot insert NULL into ("SCOTT"."A"."A1") : which means you cannot insert NULL into "SCOTT"."A"."A1".

Action:

Retry the operation with a value other than NULL.

ORA-00001: Unique constraint violated ora error

Admin — Fri, 11 Jun 2010 11:15:38 GMT

ORA-00001: Unique constraint 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.

Error:

ORA-00001: unique constraint (constraint_name) violated

Cause:

You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index.

Action:

The options to resolve this Oracle error are:

Drop the unique constraint
Change the constraint to allow duplicate values
Modify your SQL so that a duplicate value is not created

If you are not sure which unique constraint was violated, you can run the following SQL:

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

ORA-00942: table or view does not exist oerr ora 00942

Admin — Tue, 15 Jun 2010 16:09:10 GMT

oerr ora 00942 When exporting, we're getting the following error:

EXP-00008: ORACLE error 942 encountered
ORA-00942: table or view does not exist
EXP-00024: Export views not installed, please notify your DBA
EXP-00000: Export terminated unsuccessfully

Regarding this error you want to first make sure that the user you are using has EXP_FULL_DATABASE granted to it.

Then you you must run the following when you create the db and make sure there are no errors:
%ORACLE_HOME%/rdbms/admin/catalog.sql
%ORACLE_HOME%/rdbms/admin/catproc.sql

Hope this helps.

Thanks,

ORA-01756: quoted string not properly terminated ora error code

Admin — Tue, 15 Jun 2010 15:37:25 GMT

ORA-01756:

quoted string not properly terminated

ORA-01756:	quoted string not properly terminated
Cause:	A quoted string must be terminated with a single quote mark (').
Action:	Insert the closing quote and retry the statement.

ora error code ORA-01219: Database not open: queries allowed on fixed tables/views only

Admin — Tue, 15 Jun 2010 10:21:40 GMT

Ora Error Code ORA-01219: Database not open: queries allowed on fixed tables/views only

You are trying to select from a dynamic/data dictionary table but the database is not yet open.

SQL> startup mount
ORACLE instance started.

Total System Global Area  126951228 bytes
Fixed Size                   454460 bytes
Variable Size             109051904 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
Database mounted.

SQL> select * from dba_users;
select * from dba_users
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

The data dictionary views are stored inside the Oracle database's datafiles (SYSTEM tablespace).
To be able to query the data dictionary views, the database need to be open.

Check the mode of the database by issuing following select, and if it is not open, open the database:

SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> alter database open;

Database altered.
SQL> select * from dba_users;
..

ora error ORA-00119: invalid specification for system parameter LOCAL_LISTENER

Admin — Tue, 15 Jun 2010 10:05:49 GMT

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

What are the symptoms?

The local_listener parameter has been set, the listener is running, but when attempting to start the instance an ORA-00119 is reported:

SQL> startupORA-00119: invalid specification for system parameter LOCAL_LISTENERORA-00132: syntax error or unresolved network name 'LIST_ANDY'

Why is this happening?

Oracle only checks for listeners running on the default port (1521). It would have to spend all day trying every possible port number otherwise. You'll need to give it some help to find your listener.

How do I fix it?

Simply add an entry to the servers tnsnames.ora pointing at the listener. For example my listener 'LIST_ANDY' is running on port 3801, so I would add the following entry to my tnsnames:

LIST_ANDY = (ADDRESS = (PROTOCOL = TCP)(HOST = fermat)(PORT = 3801))

Test it with tnsping:

tnsping <listener_name>

Then try starting your instance again - it should work now.

ORA-00201: control file version 10.2.0.0.0 incompatible with ORACLE version 10.1.0.0.0

Admin — Sun, 22 Feb 2009 10:33:13 GMT

ORA-00201 ora error when starting up database. Here is possible solution.

SQL> startup
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size 2084264 bytes
Variable Size 805306968 bytes
Database Buffers 754974720 bytes
Redo Buffers 14692352 bytes
ORA-00201: control file version 10.2.0.0.0 incompatible with ORACLE version 10.1.0.0.0
ORA-00202: control file: '/u01/genesis/oradata/control01.dbf'

Most likely you started this db after upgrade or patch install . The compatible parameter needs to be updated so that it reflects what the control file got upgraded to.

Although it can be different, your best bet is to correct it by updating your pfile/spfile.

00201, 00000, "controlfile version %s incompatible with ORACLE version %s"
// *Cause: The controlfile was created by incompatible software.
// *Action: Either restart with a compatible software release or use
// CREATE CONTROLFILE to create a new controlfile that is
// compatible with this release.

Some helpful steps:

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> create pfile='/tmp/genesisprod.ora' from spfile;

At this point update the parameter compatible in this init file and then recreate your spfile:

#sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Feb 22 09:16:53 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL>create spfile='/opt/dbms/oracle/product/ora10204_64/dbs/spfilegenisisprod.ora' from pfile='/tmp/genesisprod.ora';

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size 2084264 bytes
Variable Size 805306968 bytes
Database Buffers 754974720 bytes
Redo Buffers 14692352 bytes
Database mounted.
Database opened.
SQL> exit

Hope this helps.

Thanks,

ORA-02248: invalid option for ALTER SESSION

Admin — Tue, 15 Jun 2010 09:45:30 GMT

ORA-02248:	invalid option for ALTER SESSION
Cause:	Obvious.
Action:	see SQL Language Manual for legal options

ORA-00998: must name this expression with a column alias

Admin — Tue, 15 Jun 2010 09:40:11 GMT

ORA-00998: must name this expression with a column alias

 This error is typical of a situation where you are attempting to create a view without 
specifying the column names after the view name, but you are relying on the column name 
or the column alias specified in the query, as in this example:
 
 
CREATE VIEW test_v AS
SELECT owner || '.' || table_name
FROM all_tables
WHERE owner = USER;

ORA-00998: must name this expression with a column alias
The error is easily fixed as follows:


 
CREATE OR REPLACE VIEW test_v AS
SELECT owner || '.' || table_name AS qualified_table_name
FROM all_tables
WHERE owner = USER;
or in this other equivalent way:
CREATE OR REPLACE VIEW test_v (qualified_table_name)
AS
SELECT owner || '.' || table_name
FROM all_tables
WHERE owner = USER;

ORA-00054: resource busy and acquire with NOWAIT specified

Admin — Mon, 14 Jun 2010 15:59:01 GMT

ORA-00054: resource busy and acquire with NOWAIT specified

If you administer a busy database you will see this error from time to time. It is nothing to worry about, but it can be quite annoying when it stops you from doing something.

It simply means that session has a lock on an object that your session is trying to update (or lock). More specifically, your session has asked for a lock on the same object, but has specified the 'nowait' clause. The nowait clause, upon finding something locked, returns an error, rather than waiting for the lock to be released.

Your options are as follows:

Make a cup of tea and try again later. Hopefully whatever was locking the object will have finished, and you can carry on.
Attempt to find out who/what is locking the object and kill it off. Use a to find the culprit.

Show sessions that are blocking each other

select 'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking

from v$lock l1, v$lock l2

where l1.block =1

and l2.request > 0

and l1.id1=l2.id1

and l1.id2=l2.id2

Show locked objects
set lines 100 pages 999
col username  format a20
col sess_id  format a10
col object format a25
col mode_held format a10
select oracle_username || ' (' || s.osuser || ')' username, 
s.sid || ',' || s.serial# sess_id, owner || '.' || object_name object, 
object_type, decode( l.block , 0, 'Not Blocking' , 1, 'Blocking' , 2, 'Global') status, 
decode(v.locked_mode , 0, 'None' , 1, 'Null' , 2, 'Row-S (SS)' , 3, 'Row-X (SX)' , 4, 'Share' , 5, 'S/Row-X (SSX)' , 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v, dba_objects d, v$lock l, v$session s
where  v.object_id = d.object_id
and  v.object_id = l.id1
and  v.session_id = s.sidorder by oracle_username, session_id
/
 
 
Show which row is locked
select do.object_name, 
row_wait_obj#, row_wait_file#, 
row_wait_block#, row_wait_row#, 
dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#,     
ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)
from v$session s, dba_objects do
where sid=&sidand  s.ROW_WAIT_OBJ# = do.OBJECT_ID
/
 
 
List locks
column lock_type format a12
column mode_held format a10
column mode_requested format a10
column blocking_others format a20
column username format a10
SELECT session_id, 
lock_type, 
mode_held, 
mode_requested, 
blocking_others, 
lock_id1
FROM dba_lock l
WHERE  lock_type NOT IN ('Media Recovery', 'Redo Thread')
/

ORA-06550: line num, column num: str ora error code 6550

Admin — Mon, 14 Jun 2010 15:46:33 GMT

Ora Error Code ORA-06550: line num, column num: str

Error:	ORA-06550: line num, column num: str
Cause:	You tried to compile a block of PLSQL code, but a compilation error occurred.
Action:	The options to resolve this Oracle error are: Refer to the line and column numbers (in the error message) to find the compilation error and correct it. Then try recompiling your code.

ORA-4031: Unable to allocate bytes of shared memory

Admin — Mon, 14 Jun 2010 15:24:34 GMT

ORA-4031: Unable to allocate bytes of shared memory

ORA-4031 is a very common error that many dba's face in their day to day activities. This error can commonly occur due to the SHARED POOL SIZE. This error can be due to an inadquate sizing of the SHARED POOL or due to fragmentation of the shared pool.

Oracle Shared Pool Memory Inadequate Sizing: The first thing is determining if the ORA-04031 error is a result of lack of contiguous space in the library cache. This can be veriried by validating the following from V$SHARED_POOL_RESERVED

1. REQUEST_FAILURES >0 and LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC

2. REQUEST_FAILURES=0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC

If the above cases are true, consider allowing the database to put more objects in shared pool reserved space by lowering SHARED_POOL_RESERVED_MIN_ALLOC. If the problem is still not resolved, consider increasing the SHARED_POOL_SIZE

Oracle Shared Pool Memory Fragmentation: The issue can be attributed to fragmentation if REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is > SHARED_POOL_RESERVED_MIN_ALLOC.

To resolve this issue, you should increase the SHARED_POOL_RESERVED_MIN_ALLOC to reduce the number of objects being cached into the shared pool reserved space. Also increase the SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE to incrase the available memory in the shared pool reserved space. It is recommended that the reserved space be sized to be 10% of the total shared pool area.

ORA-03114: not connected to ORACLE ora error

Admin — Mon, 14 Jun 2010 15:16:38 GMT

ora error

ORA-03114:

not connected to ORACLE

ORA-03114:	not connected to ORACLE
Cause:	A call to Oracle was attempted when no connection was established. Usually this happens because a user-written program has not logged on. It may happen if communication trouble causes a disconnection. In addition, this message could occur when ALTER SYSTEM KILL SESSION or ALTER SYSTEM DISCONNECT SESSION were issued with the IMMEDIATE qualifier because, in those cases, the client's connection to the database is terminated without waiting for the client to issue a request.
Action:	Try again. If the message recurs and the program is user written, check the program.

ORA-00911: Oracle/PLSQL ora error

Admin — Mon, 14 Jun 2010 15:07:45 GMT

Oracle/PLSQL: ORA-00911 Error

Error:

ORA-00911: invalid character

Cause:

You tried to tried to execute an SQL statement that included a special character.

Action:

The options to resolve this Oracle error are:

This error occurs when you try to use a special character in an SQL statement. If a special character other than $, _, and # is used in the name of a column or table, the name must be enclosed in double quotations.
This error may occur if you've pasted your SQL into your editor from another program. Sometimes there are non-printable characters that may be present. In this case, you should try retyping your SQL statement and then re-execute it.
This error occurs when a special character is used in a WHERE clause and the value is not enclosed in single quotations.

For example, if you had the following SQL statement:
SELECT * FROM suppliers
WHERE supplier_name = ?;

You would receive the following error message:
ORA-00911: invalid character
You could correct this error by enclosing the ? in single quotations as follows:.
SELECT * FROM suppliers
WHERE supplier_name = '?';

ora error 02063 ORA-02063: preceding string string from string string

Admin — Mon, 14 Jun 2010 12:47:13 GMT

02063, 00000, "preceding %s%s from %s%s"
// *Cause: an Oracle error was received from a remote database link.
// *Action: refer to the preceding error message(s)

Cause: an Oracle error was received from a remote database link.

Action: refer to the preceding error message(s)

ORA-02063: preceding string string from string string

When the error is generated at the remote database it is accompanied with an

ORA-2063. In this case the parameter distributed_transactions must be increased

at the remote database.

If there is no ORA-2063 the parameter distributed_transactions must be

increased at the local database.

In a distributed environment, if a select is issued in a session without the

SET TRANSACTION READ ONLY command, the select query is considered a

distributed transaction.

If the parameter distributed_transactions is set too low, this too will result in an ORA-2042 and ORA-2063

It is important to note that ORA-02063 typically appears with ORA-02042

ORA-03120 two-task conversion routine: integer overflow

Admin — Mon, 14 Jun 2010 12:03:07 GMT

ORA-03120 two-task conversion routine: integer overflow

ORA-03120 two-task conversion routine: integer overflow

Cause: An integer value in an internal Oracle structure overflowed when being sent or received over a heterogeneous connection. This can happen when an invalid buffer length or too great a row count is specified. It usually indicates a bug in the user application.

Action: Check parameters to Oracle calls. If the problem recurs, reduce all integer parameters, column values not included, to less than 32767.

ora error ORA-03001: unimplemented feature

Admin — Mon, 14 Jun 2010 11:32:20 GMT

ORA-03001: unimplemented feature

Cause: This feature is not implemented.

Action: None.

ora 3136 ORA-3136 - WARNING: Inbound connection timed out. ora error

Admin — Wed, 24 Dec 2008 02:17:01 GMT

The "WARNING: inbound connection timed out (ORA-3136)" in the alert log indicates that the client was not able to complete it's authentication within the period of time specified by parameter SQLNET.INBOUND_CONNECT_TIMEOUT.

You may also witness ORA-12170 without timeout error on the database server sqlnet.log file.
This entry would also have the clinet address which failed to get authenticated.

From 10.2 onwards the default value of this parameter is 60 seconds, hence if the client is not able authenticate within 60 secs , the warning would appear in the alert log and the client connection will be terminated.

This timeout restriction was introduced to combat Denial of Service (DoS) attack whereby malicious clients attempt to flood database servers with connect requests that consumes resources.

If its taking longer period, then its worth checking all the below points before going for the workadound:

1. Check whether local connection on the database server is sucessful & quick.

2. If local connections are quick ,then check for underlying network delay with the help of your network administrator.

3. Check whether your Database performance has degraded by anyway.

4. Check alert log for any critical errors for eg, ORA-600 or ORA-7445 and get them resolved first.

You can do the following as workarounds:

Set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername
to the value more than 60.

In server side sqlnet.ora file add SQLNET.INBOUND_CONNECT_TIMEOUT

SQLNET.INBOUND_CONNECT_TIMEOUT = 120
In listener.ora file INBOUND_CONNECT_TIMEOUT_listenername

INBOUND_CONNECT_TIMEOUT_LISTENER = 110

More on meta link note 465043.1 and 345197.1

ora error 01986 ORA-01986: OPTIMIZER_GOAL is obsolete

Admin — Mon, 14 Jun 2010 10:57:26 GMT

ORA-01986:	OPTIMIZER_GOAL is obsolete
Cause:	An obsolete parameter, OPTIMIZER_GOAL, was referenced.
Action:	Use the OPTIMIZER_MODE parameter.

ora 2291 - ORA-02291: integrity constraint violated - parent key not found ora error

Admin — Fri, 11 Jun 2010 15:52:17 GMT

ora error: ORA-02291: integrity constraint violated - parent key not found

02291, 00000,"integrity constraint (%s.%s) violated - parent key not found"
// *Cause: A foreign key value has no matching primary key value.
// *Action: Delete the foreign key or add a matching primary key.

In a master-child relationship, the master record has to exist before the child can reference it. If not, an ORA-02291 error will be raised.

ORA-00955 ora error name is already used by an existing object

Admin — Fri, 11 Jun 2010 12:20:25 GMT

00955, 00000, "name is already used by an existing object"
// *Cause:
// *Action:

I get this error when trying to install statspack in 9i.

If this script is automatically called from spcreate (which is
the supported method), all STATSPACK segments will be created in
the PERFSTAT user's default tablespace.

Using perfstat tablespace to store Statspack objects

... Creating STATS$SNAPSHOT_ID Sequence
create sequence STATS$SNAPSHOT_ID
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

any help is appreciated.

ORA-06502 character string buffer too small ora error

Admin — Fri, 11 Jun 2010 12:08:14 GMT

ora 06502 character string buffer too small

Oracle 10g:

06502, 00000, "PL/SQL: numeric or value error%s"
// *Cause:
// *Action:

Oracle 11g:

06502, 00000, "PL/SQL: numeric or value error%s"
// *Cause: An arithmetic, numeric, string, conversion, or constraint error
// occurred. For example, this error occurs if an attempt is made to
// assign the value NULL to a variable declared NOT NULL, or if an
// attempt is made to assign an integer larger than 99 to a variable
// declared NUMBER(2).
// *Action: Change the data, how it is manipulated, or how it is declared so
// that values do not violate constraints.

ORA-01013 user requested cancel of current operation ora error

Admin — Fri, 11 Jun 2010 11:48:57 GMT

ORA-01013 user requested cancel of current operation

01013, 00000, "user requested cancel of current operation"
// *Cause:
// *Action:

Cause: The user interrupted an Oracle operation by entering CTRL-C, Control-C, or another canceling operation. This forces the current operation to end. This is an informational message only.

Action: Continue with the next operation.

ORA-1580: error creating control backup file

Admin — Fri, 11 Jun 2010 11:29:30 GMT

After all the backup is done, I have a script that makes copies of the control file. The first copy of the control file copy command works, but the second gives me an ora error like I have below:

alter database backup controlfile to trace;
database altered

alter database backup controlfile to '/backups/PRODDB/proddb_control_file_copy'
ORA-1580 signalled during: alter database backup controlfile to '/backups/PROD...

Does any one know why this error occurs even though it's removed before the back starts?

ora 04030 error ORA-04030:

Admin — Fri, 11 Jun 2010 11:23:27 GMT

ORA-04030:

04030, 00000, "out of process memory when trying to allocate %s bytes (%s,%s)"
// *Cause: Operating system process private memory has been exhausted
// *Action:

You may look for Note:233869.1 Titled "Diagnosing and Resolving ORA-4030 errors" at Metalink Site.

This error indicates that the oracle server process is unable to allocate more memory from the operating system.This memory consists of the PGA (Program Global Area) and its contents depend upon the server configuration.For dedicated server processes it contains the stack and the UGA (User Global Area) which holds user session data, cursor information and the sort area. In a multithreaded configuration (shared server), the UGA is allocated in the SGA (System Global Area) and will not be responsible for ORA-4030 errors.

The ORA-4030 ora error thus indicates the process needs more memory (stack UGA or PGA) to perform its job.

Check your kernal parameters and ulimit settings as well:

We changed the setting of “ulimit” as follows to resolve the issue:

ulimit –a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 4194302
memory(kbytes) unlimited
coredump(blocks) unlimited
nofiles(descriptors) 4096

Hope this helps.

ORA-00600: internal error code - ora error

Admin — Fri, 11 Jun 2010 11:10:16 GMT

ORA-600 is an internal ora error generated by the generic kernel code of the Oracle RDBMS software. It is different from other Oracle errors in many ways. The following is a list of these differences:

1. An ORA-600 ora error may or may not be displayed on the screen. Therefore, screen output should not be relied on for capturing information on this error. Information on ORA-600 errors are found in the database alert and trace files. We recommend that you check these files frequently for database errors. (See the Alert and Trace Files section for more information.)

2. Each ORA-600 ora error comes with a list o***uments They usually enclosed in square brackets and follow the error on the same line for example:

ORA-00600 [14000][51202][1][51200][][]

Each argument has a specific meaning which can only be interpreted by an Oracle support analyst. The arguments may also change meaning from version to version therefore customers are not advised to memorize them.

3. Every occurrence of an ORA-600 should be reported to Oracle Support. Unlike other errors, you can not find help text for these errors. Only Oracle technical support should diagnose and take actions to prevent or resolve damage to the database.

4. Each ORA-600 error generates a database trace file.

ORA-02304: invalid object identifier literal: ORA-39083: Object type TYPE failed to create with error:

Admin — Tue, 29 Dec 2009 15:06:01 GMT

Hi,

When I was expording a schema and importing it to another schema in the same database i was getting the following ora error import message:

Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 29 December, 2009 14:05:41

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_02" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_02": parfile=/oracle/dba/scripts/imp_daily_TABLES.par directory=DAILY_TABLES_REFRESH dumpfile=james_expdp_%U.dmp logfile=daily_refresh_impdp.log REMAP_SCHEMA=JAMES:SCOTT TABLE_EXISTS_ACTION=SKIP
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/passWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "SCOTT"."SYS_PLSQL_210109_DUMMY_1" OID '6E6497DF899A51E7E0440003BA2F18B2' as table of number;
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "SCOTT"."SYS_PLSQL_210109_9_1" OID '6E6497DF899451E7E0440003BA2F18B2' as object (CUSTID CHAR(25),
PARTYID CHAR(4),
RATINGEQUIVALENCY NUMBER(3));
ORA-39082: Object type TYPE:"SCOTT"."SYS_PLSQL_11945_34_1" created with compilation warnings
ORA-39082: Object type TYPE:"SCOTT"."SYS_PLSQL_11945_34_1" created with compilation warnings
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "SCOTT"."SYS_PLSQL_210109_34_1" OID '6E6497DF899E51E7E0440003BA2F18B2' as table of SENTRY."SYS_PLSQL_210109_9_1";
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/CLUSTER/CLUSTER
Processing object type SCHEMA_EXPORT/CLUSTER/INDEX
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

I couldn't figure it out at first. This was my par file / example of my impdp statement:

impdp parfile=/oracle/dba/scripts/imp_daily.par directory=DAILY_TABLES_REFRESH dumpfile=james_expdp_%U.dmp logfile=daily_refresh_impdp.log REMAP_SCHEMA=JAMES:SCOTT TABLE_EXISTS_ACTION=SKIP PARALLEL=5

It was related to the type. I couldn't figure it out untill i saw someone metioned something about TRANSFORM option. I read it up again in metalink and it worked. Here is what I did:

impdp parfile=/oracle/dba/scripts/imp_daily.par directory=DAILY_TABLES_REFRESH dumpfile=james_expdp_%U.dmp logfile=daily_refresh_impdp.log REMAP_SCHEMA=JAMES:SCOTT TABLE_EXISTS_ACTION=APPEND PARALLEL=5 TRANSFORM=oid:n

I highlighted what i did in red. I changed it to APPEND because I was dropping the user before importing it so it was ok. The TRANSFORM option will take the existing OID and assign it a new id when you provide it with :n option. I hope this helps. This option is only available in 10gR2.

Here is some info from oracle:

Hope it helps:

Applies to:

Enterprise Manager for RDBMS - Version: 10.1.0 to 10.2.0
Oracle Server - Enterprise Edition - Version: 10.1.0 to 10.2.0
Oracle Server - Personal Edition - Version: 10.1.0 to 10.2.0
Oracle Server - Standard Edition - Version: 10.1.0 to 10.2.0
This problem can occur on any platform.

Symptoms

The example below is based on the directory object my_dir that refers to an existing directory on the server where the Oracle RDBMS is installed. Example:

Windows:
CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS 'D:\export';
GRANT read, write ON DIRECTORY my_dir TO public;
Unix:
CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS '/home/users/export';
GRANT read, write ON DIRECTORY my_dir TO public;

A schema that owns object types needs to be duplicated from schema U1 to schema U2 in the same Oracle10g database. Example:

CONNECT system/manager
CREATE USER u1 IDENTIFIED BY u1 DEFAULT TABLESPACE users;
CREATE USER u2 IDENTIFIED BY u2 DEFAULT TABLESPACE users;
ALTER USER u1 QUOTA UNLIMITED ON users;
ALTER USER u2 QUOTA UNLIMITED ON users;
GRANT create session, create table, create type TO u1;
GRANT create session, create table, create type TO u2;

CONNECT u1/u1
CREATE TYPE my_coltype AS OBJECT (nr NUMBER, txt VARCHAR2(10))
/
CREATE TYPE my_tabtype AS OBJECT (nr NUMBER, txt VARCHAR2(10))
/
CREATE TABLE my_reltab (nr NUMBER, col1 my_coltype);
CREATE TABLE my_objtab OF my_tabtype;
INSERT INTO my_reltab VALUES (1, my_coltype(1, 'Line 1'));
INSERT INTO my_objtab VALUES (1, 'Line 1');
COMMIT;

The schema U1 can be exported successfully. E.g.:

expdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_u1.dmp LOGFILE=expdp_u1.log SCHEMAS=u1

The import into schema U2 fails though with the following errors:

impdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_u1.dmp LOGFILE=impdp_u1.log 
REMAP_SCHEMA=u1:u2

...
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "U2"."MY_COLTYPE"   OID 'DDB334945FA24A41AC0099E457715B62' as ...
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "U2"."MY_TABTYPE"   OID 'EEC16EAE6DF34B4FA755DBB448EC4F78' as ...
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39117: Type needed to create table is not included in this operation. Failing sql is:
CREATE TABLE "U2"."MY_RELTAB" ...
ORA-39117: Type needed to create table is not included in this operation. Failing sql is:
CREATE TABLE "U2"."MY_OBJTAB" OF "U2"."MY_TABTYPE"  ...
...

Similar errors with the original export and import utilities are:

...
IMP-00017: following statement failed with ORACLE error 2304:
 "CREATE TYPE "MY_COLTYPE" TIMESTAMP '2006-01-04:12:36:27' OID 'F1C8AAABCB114"
 "6B0B2672663397B0156'   as object (nr number, txt varchar2(10))"
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
IMP-00017: following statement failed with ORACLE error 2304:
 "CREATE TYPE "MY_TABTYPE" TIMESTAMP '2006-01-04:12:36:28' OID '317DF88365654"
 "C6784C17B0FE14C4610'   as object (nr number, txt varchar2(10))"
IMP-00003: ORACLE error 2304 encountered
ORA-02304: invalid object identifier literal
IMP-00063: Warning: Skipping table "U2"."MY_OBJTAB" because object type "U2"."MY_TABTYPE"
cannot be created or has different identifier
IMP-00063: Warning: Skipping table "U2"."MY_RELTAB" because object type "U2"."MY_COLTYPE"
cannot be created or has different identifier
Import terminated successfully with warnings.
...

Cause

The object types MY_COLTYPE and MY_TABTYPE already exist in the source schema U1. When the types are exported, we also export the object_identifier (OID) of the types. Within the current archictecture, the object-identifier needs to be unique in the database.

During import (into the same database), we try to create the same object types in the U2 schema. As the OID of the types already exists in the source schema U1, the types cannot be created in the target schema U2. Import will fail due to:

ORA-02304: invalid object identifier literal

Solution

1. In Oracle10g Release 1 (10.1.0.x.y), you can pre-create the object types and the tables in the target schema, and run an import with the TABLE_EXISTS_ACTION parameter. Example:

CONNECT u2/u2
CREATE TYPE my_coltype AS OBJECT (nr NUMBER, txt VARCHAR2(10))
/
CREATE TYPE my_tabtype AS OBJECT (nr NUMBER, txt VARCHAR2(10))
/
CREATE TABLE my_reltab (nr NUMBER, col1 my_coltype);
CREATE TABLE my_objtab OF my_tabtype;

impdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_u1.dmp LOGFILE=impdp_u1.log
REMAP_SCHEMA=u1:u2 TABLE_EXISTS_ACTION=append

...
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-31684: Object type TYPE:"U2"."MY_COLTYPE" already exists
ORA-31684: Object type TYPE:"U2"."MY_TABTYPE" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "U2"."MY_OBJTAB"                         6.132 KB       1 rows
. . imported "U2"."MY_RELTAB"                         6.226 KB       1 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 3 error(s) at 13:13

Note that in Oracle10g Release 2, this will fail for the table MY_RELTAB with the errors:

...
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-31684: Object type TYPE:"U2"."MY_COLTYPE" already exists
ORA-31684: Object type TYPE:"U2"."MY_TABTYPE" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39152: Table "U2"."MY_RELTAB" exists. Data will be appended to existing table
but all dependent metadata will be skipped due to table_exists_action of append
ORA-39152: Table "U2"."MY_OBJTAB" exists. Data will be appended to existing table
but all dependent metadata will be skipped due to table_exists_action of append
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "U2"."MY_OBJTAB"                         6.125 KB       1 rows
ORA-31693: Table data object "U2"."MY_RELTAB" failed to load/unload and is being skipped 
due to error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
ORA-39779: type "U2"."MY_COLTYPE" not found or conversion to latest version is not possible
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 6 error(s) at 12:39:54

2. In Oracle10g Release 2 (10.2.0.x.y), there is no need to pre-create the object types and the tables in the target schema. Instead, you can use the Import DataPump parameter TRANSFORM which enables you to alter object creation DDL for the types. The value N (= No) for the transform name OID implies that a new OID is assigned. Example:

impdp system/manager DIRECTORY=my_dir DUMPFILE=expdp_u1.dmp LOGFILE=impdp_u1.log
REMAP_SCHEMA=u1:u2 TRANSFORM=oid:n

...
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "U2"."MY_OBJTAB"                         6.125 KB       1 rows
. . imported "U2"."MY_RELTAB"                         6.218 KB       1 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 12:38:56

Note that the remaining error is an ORA-31684 "Object type USER:"U2" already exists", which was reported earlier at the import. This error is not related. The ORA-31684 can be ignored if the target schema already exists in the target database.

Also note that the transform name OID for the Import DataPump parameter TRANSFORM has been introduced in Oracle10g Release 2 (10.2.0.x.y). If you use this value in Oracle10g Release 1, the Import DataPump job will fail with:

...
ORA-39001: invalid argument value
ORA-39042: invalid transform name OID

Remark 1: There are certain schema references that Import DataPump cannot find when the REMAP_SCHEMA parameter has been specified. For example, Import DataPump won't find schema references embedded within the body of definitions of types, views, procedures, and packages. Example: User U1 has created the following types:

CREATE TYPE my_vartype AS OBJECT (nr NUMBER)
/
CREATE TYPE my_var AS VARRAY (10000) OF u1.my_vartype
/
CREATE TYPE my_type2 AS OBJECT (nr NUMBER, col2 u1.my_vartype)
/
CREATE TABLE my_objtab2 OF u1.my_type2
/

Note that the type bodies of types MY_VAR and MY_TYPE2 have a reference to the schema name 'U1'. Import DataPump REMAP_SCHEMA won't be able to replace the schema name 'U1' within the type body, and the import logfile will show the errors:

ORA-39082: Object type TYPE:"U2"."MY_VAR" created with compilation warnings
ORA-39082: Object type TYPE:"U2"."MY_TYPE2" created with compilation warnings
ORA-39117: Type needed to create table is not included in this operation. Failing sql is:
CREATE TABLE "U2"."MY_OBJTAB2" OF "U2"."MY_TYPE2" ...

These errors are expected behavior because the type bodies contain an explicit reference to a schema name. Possible solutions:
1. Do not explicitly specify the schema name of the owner in the type body if this is the same owner who has created the type and if you intend to transfer the data to a different schema (e.g. REMAP_SCHEMA during Import DataPump). E.g.:

CONNECT u1/u1
CREATE TYPE my_vartype AS OBJECT (nr NUMBER)
/
CREATE TYPE my_var AS VARRAY (10000) OF my_vartype
/
CREATE TYPE my_type2 AS OBJECT (nr NUMBER, col2 my_vartype)
/
CREATE TABLE my_objtab2 OF my_type2
/

or:

2. Prior to Import DataPump have user u1 grant the execute privilege on the type that is referenced:

CONNECT u1/u1
GRANT execute ON my_vartype TO u2;

Remark 2: With the original export and import clients, a similar parameter is the TOID_NOVALIDATE parameter. If you have pre-created the object types and tables in the target schemas U2, you can import with:

imp system/manager FILE=exp_u1.dmp LOG=imp_u1.log FROMUSER=u1 TOUSER=u2 IGNORE=y 
TOID_NOVALIDATE=u2.my_coltype, u2.my_tabtype

The TOID_NOVALIDATE parameter cannot be used to skip validation of object type tables. Therefore the original import will still show the errors:

...
IMP-00071: Object identifier of imported table mismatches object identifier of existing table
 "CREATE TABLE "MY_OBJTAB" OF "MY_TABTYPE" OID '358A184D9B2E4896907E8D7A1902B"
 "F77' OIDINDEX (PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FRE"
 "ELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS")  PCTFRE"
 "E 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 F"
 "REELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
. . importing table                    "MY_RELTAB"          1 rows imported
Import terminated successfully with warnings.

This is expected behavior. For workarounds, see:
Note 1066139.6 "IMP-17 IMP-3 ORA-2304 IMP-63 FROMUSER/TOUSER Import of Table With Object Column"

References

BUG:2978711 - Allow Multiple User Imports To Succeed With User Defined Datatypes
BUG:4156453 - When Import Xmltype Table Into Another User, Ora-2304 Occurs
NOTE:1066139.6 - IMP-17 IMP-3 ORA-2304 IMP-63 FROMUSER/TOUSER Import of Table With Object Column

ORA-01031 Insufficient privileges

Admin — Thu, 09 Jul 2009 14:20:34 GMT

I had a user that was getting the following ora error message while creating a table in another user's schema:

ORA-01031 Insufficient privileges

When i looked it up this is what oracle suggests:

01031, 00000, "insufficient privileges"
// *Cause: An attempt was made to change the current username or password
// without the appropriate privilege. This error also occurs if
// attempting to install a database without the necessary operating
// system privileges.
// When Trusted Oracle is configure in DBMS MAC, this error may occur
// if the user was granted the necessary privilege at a higher label
// than the current login.
// *Action: Ask the database administrator to perform the operation or grant
// the required privileges.
// For Trusted Oracle users getting this error although granted the
// the appropriate privilege at a higher label, ask the database
// administrator to regrant the privilege at the appropriate label.

However, this didn't solve my problem. I gave the user the following privileges and still didn't work:

grant select any table to <user>;

grant drop any table to <user>;

grant create any table to <user>;

After doing this, still the user was having issues when logging in through his application via odbc connection.

After many tries, and research I granted all to the tables he was using :

grant all on <schema>.<table_name> to <user>;

and then also did the following:

grant references on <schema>.<table_name> to <user>;

That did the trick. I hope this helps you out as well.

If that didn't work my last resort was to grant dba to the user. Which I didn't want to do because that was just way too much privilege for him.

Thanks,

ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist

Admin — Mon, 11 May 2009 11:03:22 GMT

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

Cause:

Oracle was not started up. Possible causes include the following:

- The SGA requires more space than was allocated for it.

- The operating-system variable pointing to the instance is improperly defined.

An ORA-01034 ora error occurs when Oracle is not started or you don't have the proper environmnet variables setup. If you shut the database down with "NORMAL" option and there are active sessions you may keep getting this message when you try and start the instance. If you are trying to login you may need to set the TWO_TASK variable.

If you get a ORA-01034 when you issue the login command (in sqlplus, imp, exp,..) without giving the DB name, check the value of the TWO_TASK environment parameter. This is the database to which a user connects by default. Maybe the TWO_TASK points to a database which is down or does not exists at all. Try export TWO_TASK=sid.

Older versions of Oracle used to leave a lock file in the $ORACLE_HOME/dbs directory - like lkSIDNAME. The way to remove this file is to:

Fixing the issue:

For older versions of oracle:

#export ORACLE_SID=<yoursid>

#export ORACLE_HOME=<your_oracle_home>

#export PATH=$PATH:$ORACLE_HOME/bin

svrmgrl
connect internal
shutdown abort;
startup

For newer versions of oracle:

#export ORACLE_SID=<yoursid>

#export ORACLE_HOME=<your_oracle_home>

#export PATH=$PATH:$ORACLE_HOME/bin

#sqlplus '/ as sysdba'

SQL> shutdown abort;
SQL> startup

Also if you still encounter this problem, check your sqlnet.ora file:

If you see an entry like SQLNET.AUTHENTICATION_SERVICES=(NTS), change it on trial to SQLNET.AUTHENTICATION_SERVICES=(NONE) . If this helps, for some reason you cannot use NTS (Windows Native Authentication) and it seems you have a windows domain related problem.

Bottom line:

A)For Local Connections:
----------------------------
1)Verify the ORACLE_SID and ORACLE_HOME is set properly. You can check the variable in UNIX by,
# echo $ORACLE_SID

# echo $ORACLE_HOME

B)For Remote Connections:
-------------------------------
1)Verify the ORACLE_HOME is set correctly for all listed Oracle databases. If the ORACLE_HOME points to the different oracle version software, but the database was created using a different version, then this error can occur.

2)Review the listener.ora file and it is properly set. Like if you set ORACLE_HOME path inside listener ended by slash(/) then the error will come.

Incorrect:
(ORACLE_HOME = /opt/oracle/product/10.2.0/)
Correct:
(ORACLE_HOME = /u01/oracle/product/10.2.0)

3)If listener.ora use SID then verify that you have set properly ORACLE_SID. Also be sure about listener services by issuing lsnrctl services.

4)Verify the database is running on server machine while you connect as normal user.

Hope this helps.

ORA-01624: log 9 needed for crash recovery of instance PRODDB (thread 1) ora error

Admin — Thu, 30 Apr 2009 09:49:57 GMT

Hi,

I'm getting the following ora error message:

SQL> alter database drop logfile group 9;
alter database drop logfile group 9
*
ERROR at line 1:
ORA-01624: log 9 needed for crash recovery of instance proddb (thread 1)
ORA-00312: online log 9 thread 1: '+SHARED_DATA_DG03/proddb/onlinelog/group_9.273.685470789'

At first I couldn't figure out why it was giving me this message even thought I switched my logs several times. My database is open and the current active log was group 2 and when i tried to drop group 9 this is the error that I got. Then after looking at oerr ora 01624 it immediately occurred to me to do a checkpoint so this is what i did:

SQL> alter system checkpoint;

System altered.

After I issued this statement, it allowed me to drop the group:

SQL> alter database drop logfile group 9;

Database altered.

Hope this helps.

Thanks,

ORA-01034: ORACLE not available

Admin — Thu, 23 Apr 2009 10:23:20 GMT

Hi,

I am getting the following ora error message when using sqlplus to login to oracle. I am logged in as my own unix account and not oracle.

gguser@prodhost:/u01/home/gguser > sqlplus ggadmin

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Apr 18 13:23:00 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
Error: 13: Permission denied

Here is the solution to this ora error message:

# cd $ORACLE_HOME/bin

# ls -ltr oracle

-rwxr-xrwx 1 oracle dba 74053740 Sep 04 2003 oracle

The file permissions for "oracle" executable should be "-rwsr-s--x", so that others can access the oracle environment from the same host.

You need to shutdown the oracle database and then issue the command:

cd $ORACLE_HOME/bin

chmod 6751 oracle
ls -lr oracle

rwsr-s--x 1 oracle dba 74053740 Sep 04 2003 oracle

After you do this, startup the oracle database and then try to connect . This should have resolved the issue.

Hope this helps.

ORA-00019 maximum number of session licenses exceeded ora error message

Admin — Thu, 05 Mar 2009 11:06:44 GMT

[b]When you try to connect to the database you get the following ora error message:[/b]

SQL> conn ecom/
ERROR:
ORA-00019: maximum number of session licenses exceeded

Oracle Error :: ORA-00019
maximum number of session licenses exceeded
[b]Cause[/b] : All licenses are in use.
[b]Action[/b] : Increase the value of the LICENSE MAX SESSIONS initialization parameter.

[u][b]Here is how to fix it:[/b][/u]

Login to the database as DBA account:

SQL> connect / as sysdba;
Connected.

[b]You need to check your parameter called: license_max_sessions:[/b]
SQL> show parameter license_max_sessions

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
license_max_sessions integer 5

[b]Increase the value of this parameter to say something like 150:[/b]
SQL> alter system set license_max_sessions=150;
System altered.

[b]Test the connection to the user again:[/b]
SQL> connect ecom/
Connected.

That's all folks.

This is just to immediately fix your problem. You need to take a look in depth at your environment and set the appropriate value. Oracle do***entation states that:

[i]LICENSE_MAX_SESSIONS specifies the maximum number of concurrent user sessions allowed. When this limit is reached, only users with the RESTRICTED SESSION privilege can connect to the database. Users who are not able to connect receive a warning message indicating that the system has reached maximum capacity.

A zero value indicates that concurrent usage (session) licensing is not enforced. If you set this parameter to a nonzero number, you might also want to set LICENSE_SESSIONS_WARNING (see "LICENSE_SESSIONS_WARNING").

Do not enable both concurrent usage licensing and user licensing. Set either LICENSE_MAX_SESSIONS or LICENSE_MAX_USERS to zero.[/i]

I hope this topic helps.

ORA-00020: maximum number of processes (%s) exceeded ora error

Admin — Fri, 27 Feb 2009 14:36:01 GMT

00020, 00000, "maximum number of processes (%s) exceeded"
// *Cause: All process state objects are in use.
// *Action: Increase the value of the PROCESSES initialization parameter.

What this means is that you have too many database connections. It also means that you as a DBA cannot connect internal to do something about it! When you try to connect to 'sys as sysdba' it gets you to the SQL prompt but you won't be able to do anything.

Here's a couple of things you can do to fix this:
* You need to ask a couple of users to log out of the database, or
* You need to kill some sessions at the OS level.

Once you managed to connect after some sessions have been cleared, see if you can spot anything unordinary making abnormal numbers of connections. If you can't find anything directly you'll need to increase the init parameter 'processes'. Unfortunately, this will require a restart of the database to take effect in some cases where spfile is not used.

SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 100

SQL> select count(*) from v$process;

COUNT(*)
----------
94

SQL> alter system set processes=500 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 500

In my case I had 94 processes which about 5 of them got cleaned up which allowed me to login. The maximum processes alllowed in the db is 1 - the max init parameter called processes. So if you set your parameter to be 100 then max that can login is 99.

When you reach this you can occasionally get a TNS ora error connecting via TNS when the database is out of processes:

ORA-12516: TNS:LISTENER could not find available handler with matching protocol stack

This is another indication that possible max processes have been reached.

ORA-00903: invalid table name

Admin — Fri, 27 Feb 2009 13:59:46 GMT

00903, 00000, "invalid table name"
// *Cause:
// *Action:

This is very simple ora error to tackle. Infact, the cause maybe that you tried to execute an SQL statement that included an invalid table name or the table name does not exist. Revisit your sql statement.

Sometimes when you login as sys and create a table, you don't realize it but you ended up creating a table in the sys schema. Majority of the time, you want to make sure the schema name is entered:

[b]Example:[/b]
select * from inv.customer;

In many other cases you tend to copy and paste and you didn't realize that the word from got copied too and type it in again:

SQL> select * from FROM;
select * from FROM
*
ERROR at line 1:
ORA-00903: invalid table name

Also there are ORACLE reserved words that you cannot use as a table name. Check to see if you are trying to create or select from something that doesn't exist because it's a reserved word. Here are some guidelines:

•The valid table name must be less than or equal to 30 characters.

•The table or cluster name must begin with a letter and may contain only alphanumeric characters and the special characters $, _, and #.

•The table name cannot be a reserved word.

Hope this helps.

Oracle/PLSQL: ORA-06508 ora Error

Admin — Thu, 19 Feb 2009 16:47:11 GMT

Recently we did an upgrade from 9.2 to 10g. After the upgrade we started seeing intermittent ORA-06508 along with ORA-06512 ora error codes.

We know everywhere we saw it shows the following:
We even opened up the TAR to find out what was going on back then and they weren't able to help at the time.

Error:
ORA-06508: PL/SQL: could not find program unit being called

Cause:
You tried to call a stored procedure, but the stored procedure could not be found.

Action:
The options to resolve this Oracle error are:

1. The stored procedure may have been dropped. Try re-creating the stored procedure.
2. The stored procedure may have compiled with errors. Try fixing the errors and re-compiling.

After doing some research and even search metalink we came across a solution from the oracle do***entation.

To resolve it you have to follow these steps:

Shutdown your oracle database.
Startup in migrate mode (9i) or upgrade mode(10g).

Then Run the following script:

SQL> @?/rdbms/admin/utlirp

The utlirp script is in the $ORACLE_HOME/rdbms/admin directory.

Restart the database in normal and continue the activity. If any invalid objects still exist in the database, please also run:

SQL> @?/rdbms/admin/utlrp

This operation should be done because utlirp recompiles the plsql objects and set their status to the status of the current release.

Hope this helps. Now this is all do***ented in metalink : 370137.1 for those of you that have metalink accounts and for those that don't. Stick around
so we can help you in many other ways without having to pay.

Thanks,

ORA-01114 and ORA-27063 ora error code

Admin — Thu, 01 Jan 2009 07:09:23 GMT

You will see following ora error :

ORA-01114: IO error writing block to file 202 (block # 458633)
ORA-27063: skgfospo: number of bytes read/written is incorrect
SVR4 Error: 28: No space left on device
Additional information: -1
Additional information: 122880

or

ORA-01114: IO error writing block to file 202 (block # 458633)
ORA-27063: skgfospo: number of bytes read/written is incorrect
SVR4 Error: 28: No space left on device
Additional information: -1
Additional information: 122880

This means that a datafile cannot extend anymore because a disk (or mount point / raw device / or any storage device) is full.

Specifically, the high numbering of the file (in blue), refers to a temporary file.

If the number is in the range between 1 - DB_FILES, where DB_FILES is an initialization parameter whose value can be retrieved either with:

select * from v$parameter
where name = 'db_files';

or in SQL*Plus with

show parameter db_files

This means that we are talking of an ordinary datafile.

In this case DB_FILES was set to 200, which means that 202 refers to a temporary file and tempfiles are numbered starting from DB_FILES + 1.

If the temporary file is set to AUTOEXTEND ON, then the root cause of the problem can be a huge query on a view requiring a big sort operation or the insertion of lots of rows into a temporary table that made the temporary tablespace grow larger than usual until all the space on the device was used up.

One of things you want to do is check if temporary files are not sitting in the same filesystem as your datafiles. If you see this, then take the necessary steps to drop the tempfiles from the shared location and put them all in a single filesystem separate from the datafiles. Make sure that you have enough filesystem space on the new filesystem where the temp files will reside. Of course this is application dependant.

ORA-01652: unable to extend temp segment by 64 in tablespace TEMP

Admin — Sun, 18 Jan 2009 10:24:38 GMT

Hi,

Anyone can help on this issue. I have a procedure in this procedure one statement

INSERT INTO prod.temp select * from .........

This statement is giving the unable to extend temp segment by 64 in tablespace TEMP error.

This is a big query and when we run that procedure in development and testing environment it's running fine but in staging database it's given the trouble.

ORA-06502 during a procedure which uses Bulk collect feature and nested tab

Admin — Sun, 18 Jan 2009 10:18:31 GMT

Hello Friends,

I have created one procedure which uses Bulk collect and nested table to hold the bulk data. This procedure was using one cursor and a nested table with the same type as the cursor to hold data fetched from cursor. Bulk collection technique was used to collect data from cursor to nested table. But it is giving ORA-06502 ora error.

I reduced code of procedure to following to trace the error point. But still the ora error is coming. Please help us to find the cause and solve it.

Script which is giving error:
declare
v_Errorflag BINARY_INTEGER;
v_flag number := 1;
CURSOR cur_terminal_info Is
SELECT distinct
'a' SettlementType
FROM
dual;
TYPE typ_cur_terminal IS TABLE OF cur_terminal_info%ROWTYPE;
Tab_Terminal_info typ_cur_Terminal;
BEGIN
v_Errorflag := 2;
OPEN cur_terminal_info;
LOOP
v_Errorflag := 4;
FETCH cur_terminal_info BULK COLLECT INTO tab_terminal_info LIMIT 300;
EXIT WHEN cur_terminal_info%rowcount <= 0;
v_Errorflag := 5;
FOR Y IN Tab_Terminal_Info.FIRST..tab_terminal_info.LAST
LOOP
dbms_output.put_line(v_flag);
v_flag := v_flag + 1;
end loop;
END LOOP;
v_Errorflag := 13;
COMMIT;
END;

I have updated script as following to change datatype as varchar2 for nested table, but still same error is
comming..

declare
v_Errorflag BINARY_INTEGER;
v_flag number := 1;
CURSOR cur_terminal_info Is
SELECT distinct
'a' SettlementType
FROM
dual;
TYPE typ_cur_terminal IS TABLE OF varchar2(50);
Tab_Terminal_info typ_cur_Terminal;
BEGIN
v_Errorflag := 2;
OPEN cur_terminal_info;
LOOP
v_Errorflag := 4;
FETCH cur_terminal_info BULK COLLECT INTO tab_terminal_info LIMIT 300;
EXIT WHEN cur_terminal_info%rowcount <= 0;
v_Errorflag := 5;
FOR Y IN Tab_Terminal_Info.FIRST..tab_terminal_info.LAST
LOOP
dbms_output.put_line(v_flag);
v_flag := v_flag + 1;
end loop;
END LOOP;
v_Errorflag := 13;
COMMIT;

I could not find the exact reason of error.
Please help us to solve this error.

Thanks and Regards..