ORA-06502: PL/SQL: Numeric Or Value Error: Character String Buffer Too Small

ORA-06502: PL/SQL: Numeric Or Value Error: Character String Buffer Too Small

Sometimes you get this error if you insert a character/string in a numeric column or vice versa.

It happens when the field size is smaller than the length of the string you try to store.

Check the length of each column which is going to insert. There is a column having length less than the value you are going to insert.

ORA-00600 internal error code, arguments [qkebCreateConstantOpn1]

Query

UPDATE TBLNONPOASTOCKS SET NQUANTITY=0,NUSABLEPLEDGESTOCK=0 WHERE TBLNONPOASTOCKS.CFIRMNUMBER=ICFIRMNUMBER AND TBLNONPOASTOCKS.CPERSONALDPID=ICDPID AND 

TBLNONPOASTOCKS.DDTOFTRAN=IDTRANDATE ;

Error

ORA-00600 internal error code, arguments [qkebCreateConstantOpn1], [], [], [], [], [], [], [], [], [], [], []

Solution

Drop and recreate Table

ORA-22992 cannot use LOB locators selected from remote tables

SELECT * FROM TBLHTMLSCANNEDIMAGE@LNK_PREVIOUSYEARBALANCE;

ORA-22992: cannot use LOB locators selected from remote tables

both 1920 and 2021 in single db. but through link Oracle is not suppporting this

select * from ldccm.TBLCLREQUESTDESCRIPTION@Lnk_Ccm

Workaround

SELECT ( select cmodifymatter from ldccm.TBLCLREQUESTDESCRIPTION@Lnk_Ccm where nrequestno=4)   FROM DUAL;

select pscannedimage From tblscannedimage@lnk_digital;

Workaround

SELECT (select pscannedimage From tblscannedimage@lnk_digital) as pscannedimage  FROM DUAL

SELECT * FROM TBLHTMLSCANNEDIMAGE@LNK_PREVIOUSYEARBALANCE;

Workaround

CREATE MATERIALIZED VIEW MV_TBLHTMLSCANNEDIMAGE AS SELECT * FROM TBLHTMLSCANNEDIMAGE@LNK_PREVIOUSYEARBALANCE;

SELECT * FROM MV_TBLHTMLSCANNEDIMAGE;

Workaround

create temp table in database and insert into it then select

select *

from test@dblink;

 create global temporary table testglobal

on commit delete rows

as select * from test@dblink;

select * from testglobal;

 insert into testglobal

select y from test@dblink;

select * from testglobal ;

ORA-65510: Distributed LOB operations are not supported on pre-12.2 databases.

Summary

from LD , I m trying to save Blob image in ccm's table with link

select * from ldccm.TBLCLREQUESTDESCRIPTION@Lnk_Ccm;

Error

01~Error Description : ORA-65510: Distributed LOB operations are not supported on pre-12.2 databases.



Method : UpdateEkycdataLine : 46406~0~

Investigation

select * from ldccm.TBLCLREQUESTDESCRIPTION@Lnk_Ccm;

check db version of both database 

lnk_ccm(Distrubted DB) is of 11g

source db in 19c

Reason

Distributed LOB operations are not supported on pre-12.2 databases(11.2.0.3).

19c db should be connected through 19c db to support this.

Solution

upgrade DB.

ORA-30926 unable to get a stable set of rows in the source tables

ORA-30926: unable to get a stable set of rows in the source tables

 

Cause: A stable set of rows could not be got because of large dml activity or a non-deterministic where clause.

 

getting more than one records to update single row

***there is a restriction that multiple updates to the same row in the destination table is not allowed and will raise ORA-30926.

Action: Remove any non-deterministic where clauses and reissue the dml.

Solution 

use distinct in select query

check duplicate record

Another Issue Cause

Merge Parallel is not supporting when query is using index.

Check explain plan. It is not using Parallel hint as using index.

If use full hint with paralel then it start using Parallel.

that time getting this error  ORA-30926 unable to get a stable set of rows in the source tables

Solution

Remove Parallel hint

ORA-00600 internal error code, arguments [13013] [5001]

ORA-600 [13013] [a] [b] [c] [d] [e] [f]

This format relates to Oracle Server 8.0.3 to 10.1

Arg [a] Passcount

Arg [b] Data Object number

Arg [c] Tablespace Relative DBA of block containing the row to be updated

Arg [d] Row Slot number

Arg [e] Relative DBA of block being updated (should be same as [c])

Arg [f] Code

ORA-00600: internal error code, arguments: [13013], [5001], [268], [8447338], [5], [8447338], [17], [], [], [], [], []

RnD

Select object_name,object_type,owner from dba_objects where data_object_id=268;

Analyze table <owner>.<table name> validate structure cascade online ;

Rebuild Table and Index

**Index is created as column desc 

Issue

getting this issue in Merge Parallel 

Workaround

after removing parallel hint it is working fine.

Issue 

Table issue. May be bug

Solution 

Drop and recreate table

Disable trigger based on some condition

/* following command will disable the RK Trigger at NON RK Client only */

declare lchost varchar2(100); 

begin

select host into lchost from dba_db_links where db_link='LNK_RAKSHAK'; 

if ( trim(upper(lchost)) like '%RAKSHAK%' ) 

THEN 

  BEGIN

  FOR cur_rec IN (SELECT trigger_name 

                  FROM   dba_triggers WHERE trigger_name like 'TG_RK%') LOOP

    BEGIN

      EXECUTE IMMEDIATE 'alter trigger ldbo.' || cur_rec.trigger_name || ' DISABLE' ;

    END;

  END LOOP;

END;

END IF ;

END;

/

Merge is not using Parallel hint

if you are using table alias then use parallel(alias) not parallel(table_name)

also if query is using index in plan then it will not use parallelism

if you want to use parallel forcefully then use full(alias) along with parallel hint.

DBCA Ora-03113

Check OS level

Resource

RAM

copy column from same table

Table 1

Col1

Col2

Col3

New column Col4 added

Col4 has same data col2

Method

Update table1 set Col4=col2

Create table as 

Insert into table2( col1,col2,col3,col2)

Rename

oracle communication: failed to connect to server or failed to parse connect string

Tnsping

Antivirus and firewall exclusion

Oracle database and application driver version compatibility

ODAC

ODP.NET

oracle client

oracle.manageddataaccess.dll

Oracle bug

Update millions of rows in very huge table Performance

/*Use set value also in where clause.

Eg. Set nUsablePledgestock=1

Where nUsablePledgestock !=0

"/

Update PowerofAttorneystocks set nUsablePledgestock=Quantity Where nUsablepledgestock!=Quantity ;

Commit ;

/* split the query to month-wise */

Following will not increase undo in very high amount.

set timing on

spool d:\poaupdate.txt

Update PowerofAttorneystocks set nUsablePledgestock=Quantity Where firmnumber='CUB-000001' and dtoftran <='01-APR-2019' and nUsablepledgestock!=Quantity; 

Commit ;

Update PowerofAttorneystocks set nUsablePledgestock=Quantity Where firmnumber='CUB-000001' and dtoftran >'01-APR-2019' and dtoftran <='01-MAY-2019' and nUsablepledgestock!=Quantity; 

Commit ;

Update PowerofAttorneystocks set nUsablePledgestock=Quantity Where firmnumber='CUB-000001' and dtoftran >'01-MAY-2019' and dtoftran <='01-JUN-2019' and nUsablepledgestock!=Quantity; 

Commit ;

Update PowerofAttorneystocks set nUsablePledgestock=Quantity Where firmnumber='CUB-000001' and dtoftran >'01-JUN-2019' and dtoftran <='01-JUL-2019' and nUsablepledgestock!=Quantity; 

Commit ;

Update PowerofAttorneystocks set nUsablePledgestock=Quantity Where firmnumber='CUB-000001' and dtoftran >'01-JUL-2019' and dtoftran <='01-AUG-2019' and nUsablepledgestock!=Quantity; 

Commit ;

Update PowerofAttorneystocks set nUsablePledgestock=Quantity Where firmnumber='CUB-000001' and dtoftran >'01-AUG-2019' and dtoftran <='01-SEP-2019' and nUsablepledgestock!=Quantity; 

Commit ;

Update PowerofAttorneystocks set nUsablePledgestock=Quantity Where firmnumber='CUB-000001' and dtoftran >'01-SEP-2019' and dtoftran <='01-OCT-2019' and nUsablepledgestock!=Quantity; 

Commit ;

Update PowerofAttorneystocks set nUsablePledgestock=Quantity Where firmnumber='CUB-000001' and dtoftran >'01-OCT-2019' and dtoftran <='01-NOV-2019' and nUsablepledgestock!=Quantity; 

Commit ;

Update PowerofAttorneystocks set nUsablePledgestock=Quantity Where firmnumber='CUB-000001' and dtoftran >'01-NOV-2019' and dtoftran <='01-DEC-2019' and nUsablepledgestock!=Quantity; 

Commit ;

Update PowerofAttorneystocks set nUsablePledgestock=Quantity Where firmnumber='CUB-000001' and dtoftran >'01-DEC-2019' and dtoftran <='01-JAN-2020' and nUsablepledgestock!=Quantity; 

Commit ;

Update PowerofAttorneystocks set nUsablePledgestock=Quantity Where firmnumber='CUB-000001' and dtoftran >'01-JAN-2020' and dtoftran <='01-FEB-2020' and nUsablepledgestock!=Quantity; 

Commit ;

Update PowerofAttorneystocks set nUsablePledgestock=Quantity Where firmnumber='CUB-000001' and dtoftran >'01-FEB-2020' and dtoftran <='01-MAR-2020' and nUsablepledgestock!=Quantity; 

Commit ;

Update PowerofAttorneystocks set nUsablePledgestock=Quantity Where firmnumber='CUB-000001' and dtoftran >'01-MAR-2020' and dtoftran <='01-APR-2020' and nUsablepledgestock!=Quantity; 

Commit ;

Update PowerofAttorneystocks set nUsablePledgestock=Quantity Where firmnumber='CUB-000001' and dtoftran >'01-APR-2020' and dtoftran <='01-MAY-2020' and nUsablepledgestock!=Quantity; 

Commit ;

Update PowerofAttorneystocks set nUsablePledgestock=Quantity Where firmnumber='CUB-000001' and dtoftran >'01-MAY-2020' and dtoftran <='01-JUN-2020' and nUsablepledgestock!=Quantity; 

Commit ;

Update PowerofAttorneystocks set nUsablePledgestock=Quantity Where firmnumber='CUB-000001' and dtoftran >'01-JUN-2020' and dtoftran <='01-JUL-2020' and nUsablepledgestock!=Quantity; 

Commit ;

Update PowerofAttorneystocks set nUsablePledgestock=Quantity Where firmnumber='CUB-000001' and dtoftran >'01-JUL-2020' and nUsablepledgestock!=Quantity; 

Commit ;

...

....

...

.....

....

spool off

select max from large table performance

Select max(ddate) from transactions where code='KSH';

Change to 

Select ddate from ( select ddate from transactions where code='KSH' order by ddate desc) where rownum=1;

**** If that code is not exist in table then first query takes very much time but second one comes faster.

Also can change into subquery

ORA-22816: unsupported feature with RETURNING clause in trigger

Restrictions

The following restrictions apply to the RETURNING clause:

The expr is restricted as follows:

Each expr must be a simple expression or a single-set aggregate function expression. You cannot combine simple expressions and single-set aggregate function expressions in the same returning_clause.

Single-set aggregate function expressions cannot include the DISTINCT keyword.

You cannot specify the returning_clause for a multitable insert.

You cannot use this clause with parallel DML or with remote objects.

You cannot retrieve LONG types with this clause.

You cannot specify this clause for a view on which an INSTEAD OF trigger has been defined.

 

ORA-22816: unsupported feature with RETURNING clause

ORA-06512: at "LDBO.TG_RKPOAPLEDGEUPDATE", line 36

ORA-04088: error during execution of trigger 'LDBO.TG_RKPOAPLEDGEUPDATE'

Problematic Command

update tblrktodel@lnk_Rakshak set nPoastocks=nPoastocks+........

        Where cClientcode=lcTerminalcode and cIsincode=lcIsincode and nSegmenttype=lnCommodity and nSourcetable=0 returning nHaircut,nT2Tscrip into lnHaircut,lnT2TScrip ;

Solution

Begin

           Select tblRktodel.nHaircut,tblRktodel.nT2Tscrip into lnHaircut,lnT2Tscrip From tblrktodel@lnk_Rakshak Where dTransactiondate=:Old.Dtoftran and cClientcode=lcTerminalcode and cIsincode=lcIsincode and nSegmenttype=lnCommodity and nSourcetable=0 ;

        Exception 

            When NO_DATA_FOUND then 

                lnHaircut:=0 ;

                lnT2Tscrip:=0 ;

        End ;           

ora-02055 ora-02069 ora-06512 ora-04088 select Sequence Remote table

Error in executing Trigger 

/* problem */

can not use Sq_Rakshakfreshpledge.nextval direct in remote table

Insert into tblRkfreshpledge@lnk_Rakshak (dTransactiondate,nTransactionno,nSegmenttype,cClientcode,cIsincode,nT2Tscrip,nHaircut,nPledgestock,nMarketvalue,dEntrytime,

        cTransmitted,dTransmittime) Values (:Old.Dtoftran,Sq_Rakshakfreshpledge.nextval,lnCommodity,lcTerminalcode,lcIsincode,lnT2Tscrip,lnHaircut,

        (:new.nUsablepledgestock-:Old.nUsablepledgestock),(:new.nUsablepledgestock-:Old.nUsablepledgestock)*lnMarketrate*lnHaircut,Sysdate,'N',null) ;

Solution

Select Ldbo.Sq_Rakshakfreshpledge.nextval into lnTransactionno From dual ;

        Insert into tblRkfreshpledge@lnk_Rakshak (dTransactiondate,nTransactionno,nSegmenttype,cClientcode,cIsincode,nT2Tscrip,nHaircut,nPledgestock,nMarketvalue,dEntrytime,

        cTransmitted,dTransmittime) Values (:Old.Dtoftran,lnTransactionno,lnCommodity,lcTerminalcode,lcIsincode,lnT2Tscrip,lnHaircut,

        (:new.nUsablepledgestock-:Old.nUsablepledgestock),(:new.nUsablepledgestock-:Old.nUsablepledgestock)*lnMarketrate*lnHaircut,Sysdate,'N',null) ;

Trigger Row Level :OLD and :NEW

Row level triggers can access new and existing values of columns using the ":NEW.column-name" and ":OLD.column-name" 

references, bearing in mind the following restrictions.

Row-level INSERT triggers : Only ":NEW" references are possible as there is no existing row.

Row-level UPDATE triggers : Both ":NEW" and ":OLD" references are possible. ":NEW" represents the new value presented in the 

DML statement that caused the trigger to fire. ":OLD" represents the existing value in the column, prior to the update being 

applied.

Row-level DELETE triggers : Only ":OLD" references are possible as there is no new data presented in the triggering 

statement, just the existing row that is to be deleted.

******************

******************

old value in column nUsablepledgestock=100

operation =100+100

new value in column nUsablepledgestock after update (stored)=200

then

:old.nUsablepledgestock=100

:new.nUsablepledgestock=200

******************

******************

Database Decommission Steps

1)Before stopping the database double check with customer.also take backup of database for rollback plan

2)Stop database

3)Drop asm diskgroup

4)Stop asm instance

5)Deinstall oracle home

6)Deinstall grid home

7) Maintain database backup  couple of weeks.Confirm with customer and delete

Merge Statement on same table

MERGE INTO CLEMAILDETAIL t2

USING dual

on (t2.firmnumber = :firm and t2.oowncode= :code)

WHEN MATCHED THEN

  UPDATE SET

        t2.cclientlevelpassword = :pass

;

MERGE INTO CLEMAILDETAIL t2

USING dual

on (t2.oowncode= '10002')

WHEN MATCHED THEN

  UPDATE SET

        t2.cclientlevelpassword = 'abc'

;

-------------for better index use -------------------

MERGE INTO CLEMAILDETAIL t2

USING

(

  SELECT firmnumber,oowncode       

  FROM CLEMAILDETAIL where oowncode='10002'

) t1

ON (t2.firmnumber = t1.firmnumber and t2.oowncode=t1.oowncode)

WHEN MATCHED THEN

  UPDATE SET

        t2.cclientlevelpassword = :password

;

eg.

MERGE INTO CLEMAILDETAIL t2

USING

(

  SELECT firmnumber,oowncode       

  FROM CLEMAILDETAIL where oowncode='10002'

) t1

ON (t2.firmnumber = t1.firmnumber and t2.oowncode=t1.oowncode)

WHEN MATCHED THEN

  UPDATE SET

        t2.cclientlevelpassword = 'abc'

;

MERGE INTO to tbltest i

USING (select 1 from dual) ii

ON (i.id = :id AND i.url = :url)

WHEN MATCHED THEN

UPDATE SET

title = :title

WHEN NOT MATCHED THEN

INSERT (i.id, i.url, i.title)

VALUES (:id, :url, :title)

MERGE INTO Employee USING dual ON ( "id"=20200714) WHEN MATCHED THEN UPDATE SET "last"="agarwal" , "name"="kshitij" WHEN NOT MATCHED THEN INSERT ("id","last","name") VALUES ( 20200714,"agarwal", "kshitij" )

Performance Tips for select count update insert

Use Merge command

Avoid select count statement.

Always minimize and simplify your query.

Don't make it complex. 

Minimize the execution of same statement

10.2.0.4 to 11.2.0.4 upgrade Manually

Take a full backup of the database.

E:\oracle\product\10.2.0\db_1\BIN\expdp.exe ldbo\bold@apx1213srv full=Y dumpfile=apx1213expdp.dmp logfile=apx1213expdp.log

Install 11.2.0.3 into a separate E:\app\Administrator\product\11.2.0\dbhome_1.

After Installing 11.2.0.3 , Go to 10.2.0.4 database and run :

Connect as sys

spool e:\temp\pre_upgrade.log

@E:\app\Administrator\product\11.2.0\dbhome_1\rdbms\admin\utlu112i.sql

spool off 

To check Data Dictionary 

Connect as sys

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

Note: Ignore if dbms_stats.gather_dictionary_stats was run once.

To Fix invalid Object in the database :

connect as sys

SQL> @E:\app\Administrator\product\11.2.0\dbhome_1\rdbms\admin\utlrp.sql

Run pre-upgrade diagnostic utility dbupgradiag.sql

sqlplus "\ as sysdba"

SQL> spool e:\temp\pre_dbupgdiag.log

SQL>@@E:\app\Administrator\product\11.2.0\dbhome_1\rdbms\admin\dbupgdiag.sql

SQL>spool off  

if the dbupgdiag.sql script reports any invalid objects, 

run @E:\app\Administrator\product\11.2.0\dbhome_1\rdbms\admin\utlrp.sql  

Configure the 10.2.0.4 database environment for 11.2.0.3 Upgrade 

Copy the following Files to New Home 11.2.0.3 :

Spfile\Pfile\initapx1213.ora

orapwd

tnsnames.ora

Listener.ora

OC4J Home (E:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\OC4J_DBConsole_) Only if you have enterprise manager

Set The environment to 11.2.0.3

export E:\app\Administrator\product\11.2.0\dbhome_1\u01\app\oracle\prouct\11.2.0.3

export ORACLE_SID=apx1314

Note : Replace the Path , LD_Library_Path to new Home .

-------------------

E:\oracle\product\10.2.0\db_1\BIN\oradim.exe -delete -sid apx1213

e:\app\Administrator\product\11.2.0\dbhome_1\BIN\oradim.exe -new -sid apx1213 -SRVC OracleServiceapx1213 -intpwd oracle -MAXUSERS 5 -STARTMODE auto -PFILE E:\app\Administrator\product\11.2.0\dbhome_1\database\initapx1213.ora

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\alert

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\cdump

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\hm

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\incident

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\incpkg

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\ir

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\lck

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\metadata

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\stage

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\sweep

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\trace

mkdir E:\app\Administrator\admin\apx1213\adump

-------------------

Upgrade 10.2.0.4 database to 11.2.0.3

sqlplus  \ as sysdba

spool e:\temp\upgrade.log

startup upgrade

set echo on

@E:\app\Administrator\product\11.2.0\dbhome_1\rdbms\admin\catupgrd.sql;

spool off

shutdown immediate

startup

@E:\app\Administrator\product\11.2.0\dbhome_1\rdbms\admin\utlu112s.sql;

@E:\app\Administrator\product\11.2.0\dbhome_1\rdbms\admin\catuppst.sql;

@E:\app\Administrator\product\11.2.0\dbhome_1\rdbms\admin\utlrp.sql;

select comp_name,version,status from dba_registry;

select owner,count(*) from dba_objects where status != 'VALID' group by owner;

SQL> shutdown immediate;

SQL> startup;

select * from v$version;

select * from dba_registry;

select version from v$timezone_file;

And Please Make Sure the listener has been started from the new home not the old one .

--------------------XML Installation

shutdown immediate;

startup upgrade;

spool e:\app\install_xml_db.log

@e:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\admin\catqm.SQL xdb sysaux temporary YES

shutdown immediate;

startup;

@e:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\admin\utlrp.sql

-------------------------Manual

You need to perform an out of place upgrade.

1. Install 11.2.0.3 on a different home

2. run the pre-upgrade tool on the existing 10.2.0.4 database

3. peform the pre-requisities mentioned

4. Bring down the database from 10.2.0.4 and start the database in upgrade mode from 11.2.0.3 home by copying the pfile\spfile of 10.2.0.4 database to $11.2.0.3_HOME\dbs path 

5. startup upgrade and run the catupgrd.sql

Make sure you look out for the invalid objects.

Refer *Complete checklist for out-of-place manual upgrade from previous 11.2.0.N version to the latest 11.2.0.N patchset. [ID 1276368.1]*

E:\oracle\product\10.2.0\db_1\BIN\oradim.exe -delete -sid apx1213

e:\app\Administrator\product\11.2.0\dbhome_1\BIN\oradim.exe -new -sid apx1213 -SRVC OracleServiceapx1213 -intpwd oracle -MAXUSERS 5 -STARTMODE auto -PFILE E:\app\Administrator\product\11.2.0\dbhome_1\database\initapx1213.ora

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\alert

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\cdump

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\hm

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\incident

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\incpkg

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\ir

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\lck

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\metadata

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\stage

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\sweep

mkdir E:\app\Administrator\diag\rdbms\apx1213\apx1213\trace

mkdir E:\app\Administrator\admin\apx1213\adump

select * from v$version;

select * from dba_registry;

select version from v$timezone_file;