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

ORA-28000: the account is locked ora error

Admin — Fri, 11 Jun 2010 11:20:00 GMT

ORA-28000: the account is locked

Cause: The user has entered wrong password consequently for maximum number of times specified by the user"s profile parameter FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account

Action: Wait for passWORD_LOCK_TIME or contact DBA

When you get this ora error and, if you want to unlock account called SCOTT follow these steps.

From your command prompt, type
sqlplus "/ as sysdba"

Once logged in as SYSDBA, you need to unlock the SCOTT account

SQL> alter user scott account unlock;

20005 - ORA-20005: object statistics are locked ora 20005 object statistics are locked stattype all

Admin — Wed, 27 May 2009 08:23:58 GMT

I got the following error:

ora 20005 object statistics are locked stattype all

SQL error. Function: SQLExec
Error Position: 0
Return: 20005 - ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 13437 ORA-06512: at "SYS.DBMS_STATS", line 13457 ORA-06512: at line 1
Statement: EXECUTE SYS.DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SYSADM', TABNAME => 'SS_JAMES', ESTIMATE_PERCENT => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE , DEGREE => 4, CASCADE => TRUE)
Original Statement: Execute sys.dbms_stats.gather_table_stats(OwnName => 'SYSADM', TabName => 'SS_JAMES', Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE , Degree => 4, Cascade => TRUE)

SQL error in Exec. (2,280) SCRTY_SJTUPD.MAIN.GBL.default.1900-01-01.UPDSTATS.OnExecute PCPC:1535 Statement:4

Process 16318 ABENDED at Step SCRTY_SJTUPD.MAIN.UPDSTATS (PeopleCode) -- RC = 8 (108,524)

Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s
Application Engine Program ended with a return code of 100
AE Program: scrty_sjtupd Failed!
Wed May 20 12:15:04 CDT 2009 /prod/bin/scrty_sjtupd.sh ERROR : psae.sh FAILED, APPL=scrty_sjtupd
Wed May 20 12:15:04 CDT 2009 (/prod/bin/alert_email.sh) BEGIN : PID = 16318, APPL = scrty_sjtupd, RETURN_CODE = 9017

To fix this error I did the following :

exec DBMS_STATS.UNLOCK_TABLE_STATS('SYSADM','SS_JAMES');

This unlocks the statistics on the table. The table SS_JAMES has its statistics locked and this particular job issues the following command : exec DBMS_STATS.UNLOCK_TABLE_STATS('SYSADM','SS_JAMES');

After unlocking it. It resolved the error.

Hope this helps .

Do let me know if it does.

Thanks,

ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes

Admin — Thu, 10 Jun 2010 11:38:18 GMT

I kept getting this error and couldn't figure it out.

Oracle Error ORA-20000

Error: ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 121, Batch 2 Line 1 Col 1

Oracle basically says this:

20000, 00000, "%s"
// *Cause: The stored procedure 'raise_application_error'
// was called which causes this error to be generated.
// *Action: Correct the problem as described in the error message or contact
// the application administrator or DBA for more information.

It doesn't really help.

I finally found the answer to increase the output characters using:

DBMS_OUTPUT.ENABLE()

By default, you can output 2000 characters, the maximum is 1,000,000

DBMS_OUTPUT.ENABLE(1000000);

However, If the code excedes the limit you will get the same error.

You can also also use the following to fix the error message:

set serveroutput on size 1000000

Hope this helps.

Thanks,

ORA-22929: invalid or missing directory name and ORA-06564: object Xyz does not exist

Admin — Mon, 14 Jun 2010 09:55:25 GMT

ORA-22929: invalid or missing directory name and ORA-06564: object Xyz does not exist

You can see this type of ora error when executing a command like:

ALTER TABLE CUSTOMER_SERVICE
DEFAULT DIRECTORY 'IMP_DIR';

ORA-22929: invalid or missing directory name

The problem is in the single quotes surrounding the directory object name.
The same error is also returned when using a CREATE TABLE statement.

The correct syntax requires either double quotes for case sensitive names or no quotes at all for case insensitive names.

ALTER TABLE IMP_BAD_BOXES
DEFAULT DIRECTORY "IMP_DIR";

ALTER TABLE IMP_BAD_BOXES
DEFAULT DIRECTORY IMP_DIR;

Note that when using case sensitive names, if you mistype the name you'll get:

ORA-06564: object Import_Dir does not exist

ORA-29701: unable to connect to Cluster Manager

Admin — Mon, 16 Nov 2009 14:42:06 GMT

When I try to start the ASM instance,(RHEL 5 and 10gR2 -10.2.0.4) i'm getting the following error message:

ORA-29701: unable to connect to Cluster Manager

There are couple of reasons why this errors occurs. You need to do the following things:

As root run the following from the db $ORACLE_HOME/bin

localconfig reset

and you can also try this if it doesn't successfully run:

localconfig add

Finally, after running reset option, you need to run root.sh .

Hopefully this helps resolve your issue. It worked for me.

Thanks,

ORA-27123: unable to attach to shared memory segment

Admin — Thu, 23 Apr 2009 10:24:22 GMT

Hi,

I am getting the following 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 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-24237: object id argument passed to DBMS_UTILITY.INVALIDATE is not legal

Admin — Fri, 08 Jan 2010 18:56:38 GMT

I wanted to compile schema as sys and was getting this error . Couldn't figure it out and then finally i did after some research.

SQL> conn / as sysdba

Connected.

SQL> exec dbms_utility.compile_schema('SERVICEACCT');

BEGIN dbms_utility.compile_schema('SERVICEACCT'); END;

ERROR at line 1:

ORA-20000:

ORA-06512: at "SYS.DBMS_UTILITY", line 347

ORA-24237: object id argument passed to DBMS_UTILITY.INVALIDATE is not legal

ORA-06512: at line 1

Cause

SYS does not have the required privileges to run the procedure.

Solution

To check whether SYS has the required privileges, run the following query connected as SYS:

SQL> select username, privilege from user_sys_privs order by privilege;

If SYS does not have the following privileges, then grant them to SYS directly:

CREATE ANY DIMENSION
CREATE ANY EVALUATION CONTEXT
CREATE ANY INDEX
CREATE ANY INDEXTYPE
CREATE ANY LIBRARY
CREATE ANY MATERIALIZED VIEW
CREATE ANY OPERATOR
CREATE ANY PROCEDURE
CREATE ANY RULE
CREATE ANY RULE SET
CREATE ANY SYNONYM
CREATE ANY TRIGGER
CREATE ANY TYPE
CREATE ANY VIEW

hope this helps

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

Admin — Wed, 06 Jan 2010 15:51:54 GMT

I was exporting using expdp and i got the following error message.

(OS: Linux 2.6.18-128.1.6.el5 #1 SMP Tue Mar 24 12:05:57 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux)

(DB: Oracle Enterprise 10.2.0.4 )

#>/ora01/EXPORTS[PRODDB] expdp content=metadata_only schemas=scott dumpfile=scott.dmp logfile=scott.log directory=expdir

Export: Release 10.2.0.4.0 - 64bit Production on Wednesday, 06 January, 2010 14:21:22

Username: / as sysdba

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
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/ora01/EXPORTS/scott.dmp"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3

At first I couldn't figure out the exact message and why because I was able to read write successfully as the oracle account to /ora01/EXPORT directory. No issues. But i realized that oracle checks the mount permisssions now. I had the unix SA update the mtab file show that the word "hard" is placed correctly and update the mtab file. Here is the before and after:

BEFORE:

cat /etc/mtab

host:/vol/dbdump_col/proddb_prd1 /ora01 nfs rw,nfsvers=3,tcp,intr,rsize=4096,wsize=4096,addr=192.168.1.2 0 0

AFTER:

cat /etc/mtab

host:/vol/dbdump_col/proddb_prd1 /ora01 nfs rw,bg,hard,rsize=32768,wsize=32768,nfsvers=3,nointr,timeo=600,proto=tcp,addr=192.168.1.2 0 0

Hope this helps.

Thanks,

ORA-28030: Server encountered problems accessing LDAP directory service

Admin — Mon, 26 Oct 2009 14:21:23 GMT

I am getting the following error messages:

H:\>sqlplus produser@proddb

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Oct 26 13:58:12 2009

Enter password:
ERROR:
ORA-28030: Server encountered problems accessing LDAP directory service

My sqlnet.ora and ldap.ora shows the following:

sqlnet.ora

NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES)

ldap.ora

DIRECTORY_SERVERS= (oid1p.iam.com:389:636,oid2p.iam.com:389:636,oid3p.iam.com:389:636)

DEFAULT_ADMIN_CONTEXT = "dc=iam,dc=com"

DIRECTORY_SERVER_TYPE = OID

I couldn't figure out what was going on. Every userid that was idnetified GLOBALLY was having this same error message:

Enter password:
ERROR:
ORA-28030: Server encountered problems accessing LDAP directory service

I was able to login to other remote servers from the database server using ldap entries. Didn't make sense.

Finally after numerous attempts, this is what I did to fix it. I had started up the database with the wrong ORACLE_BASE set. It was set to following : ORACLE_BASE=/oracle It should have been set to : ORACLE_BASE=/u01/oracle

Then I shutdown and restart the database and the user was able to login. The reason why this was the issue was because the wallet was under /u01/oracle/admin/<sid>/wallet directory and it couldn't find it.

Now you can also set something similar in your sqlnet.ora if possible so you prob. don't have to restart your database:

WALLET_LOCATION=
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=/u01/oracle/admin/wallets)))

You can also run this command to check if your wallet is open:

select * from system.is_wallet_open;

You'll get something like :

WALLET_STATUS
-------------
1

Hope this helps.

Thanks,

ORA-22615 attribute is not a collection

Admin — Tue, 08 Sep 2009 14:40:51 GMT

Error Message:

ORA-22615 attribute is not a collection

Error Cause:
Collection routine is invoked upon a non-collection attribute. 12-52 Oracle9i Database Error Messages

Action:
Make sure attribute is a collection.

ORA-23421: job number 141 is not a job in the job queue

Admin — Sun, 24 May 2009 13:18:35 GMT

Hi,

I was logged in as SYS and trying to remove a job but was getting the following error :

SQL> execute DBMS_JOB.REMOVE (141);
BEGIN DBMS_JOB.REMOVE (141); END;

*
ERROR at line 1:
ORA-23421: job number 141 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 529
ORA-06512: at "SYS.DBMS_JOB", line 171
ORA-06512: at line 1

Then after some reseach, I found how to remove the job that is owned by another user:

SQL> EXECUTE SYS.DBMS_IJOB.REMOVE (141);

PL/SQL procedure successfully completed.

Hope this helps.

Thanks,

ORA-27092: skgfofi: size of file exceeds file size limit of the process

Admin — Wed, 11 Mar 2009 01:46:35 GMT

[b]I recently saw this in the alert log:[/b]

Errors in file /u8oracle/local/QA/bdump/qa_dbw0_164816.trc:
ORA-01157: cannot identify/lock data file 15 - see DBWR trace file
ORA-01110: data file 15: '/u8oraindex1/QA/libraryx_01.dbf'
ORA-27092: size of file exceeds file size limit of the process
Additional information: 131071
Additional information: 281601
Tue Mar 10 22:13:06 2009
Errors in file /u8oracle/local/QA/bdump/qa_dbw0_164816.trc:
ORA-01157: cannot identify/lock data file 18 - see DBWR trace file
ORA-01110: data file 18: '/ux228oradata1/QA/masterd_01.dbf'
ORA-27092: size of file exceeds file size limit of the process
Additional information: 131071
Additional information: 704001
Tue Mar 10 22:13:06 2009
Errors in file /u8oracle/local/QA/bdump/qa_dbw0_164816.trc:
ORA-01157: cannot identify/lock data file 19 - see DBWR trace file
ORA-01110: data file 19: '/u8oraindex1/QA/masterx_01.dbf'
ORA-27092: size of file exceeds file size limit of the process

[b]Cause:[/b]

The account that is used to run the stored procedure or simple SQL query could yield the above errors. The issue was related to the ULIMIT set to the user account.

[b]Solution:[/b]

Have UNIX administrator reset the ULIMIT to a higher value or "unlimited" .

I checked my environment and it was set to unlimited:

proddbserv01: /u8oracle/local/QA/bdump/> ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 4194304
memory(kbytes) unlimited
coredump(blocks) 2097151
nofiles(descriptors) 2000

and still saw in the alert log the error message. Then I realized that the database was not started using the oracle account which has the unlimited settings. It was started by another user. I then logged in to sqlplus and shutdown immediate and then issued startup.

This worked and the database started up successfully.

Hope this helps.

Thanks,

ORA-29339: tablespace block size 16384 does not match configured block sizes

Admin — Thu, 26 Feb 2009 02:42:19 GMT

Current Database Block Size: 8192

You want to try to add a tablespace that is of block size 16k (16384).

You get an error message:
ORA-29339: tablespace block size 16384 does not match configured block sizes

SQL> create tablespace PRODDATA datafile '/oradata1/PRODDATA/oradata/proddata03_01.dbf' size 10000m blocksize 16384;
create tablespace PRODDATA datafile '/oradata1/PRODDATA/oradata/proddata03_01.dbf' size 10000m blocksize 16384
*
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes

Here is the solution to it:
In the newer databases you can have tablespaces with different block sizes but you need to make sure your cache parameters are configured properly.

At the sqlplus prompt type in:
SQL> show parameter cache

show parameter cache

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_cache_advice string ON
db_cache_size big integer 3568M
db_keep_cache_size big integer 0
db_recycle_cache_size big integer 0
object_cache_max_size_percent integer 10
object_cache_optimal_size integer 102400
session_cached_cursors integer 150

If you have enough memory you should not need to reduce anything else but in case you don't you will need to reduce your db_cache_size. Normally you don't.

In this case we're trying to add 16k tablespace so we will need to adjust the db_16k_cache_size:

SQL> alter system set db_16k_cache_size=32M;

This basically allows us to allocate 16k buffers inside our sga. This way we can you non-standard blocksizes in the database.

Now it will allow us to create the tablespace with 16k block size:

SQL> create tablespace PRODDATA datafile '/oradata1/PRODDATA/oradata/proddata03_01.dbf' size 10000m blocksize 16384;

Tablespace created.

Hope this helps.

Regards,