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;

Laptop Keyboard disable steps in case you have usb external keyboard

If we uninstall drivers after restart windows install it automatically

So following is trick

Go to My Computer→Right Click→Properties→Device Manager→Keyboards

Under keyboard option,Right Click on the keyboard you want to disable

Click on properties

In Driver tab click on update driver

Select browse my computer option

Select below option and click on next

 Uncheck-”Show compatible hardware”

Select other manufacturer than yours

Select any model and click on next

Message will appear-”Driver updated…”

 Restart and enjoy..

PS: In case you miss your in built keyboard then follow the same process again.

ORA-03137: TTC protocol internal error

Error:

 : [3147] [] [] [] [] [] [] []

Reason

This error is only telling us that the client and server process communication was terminated due to some "event".

Client / server message exchanges use an Oracle specific protocol (TTC). ORA-3137 is reported if something unexpected is seen that does not conform to the expected protocol. The error can be raised by the client or server side of a connection.

Cause 

Oracle database 10g

Oracle ManagedDll 19c

Workaround:

alter system set "_optim_peek_user_binds"=false;

Restart instance and listener services

Solution

Upgrade database to minimum 11g

ORA-31626: job does not exist ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

Error
ERROR at line 1:
ORA-31626: job does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3440
ORA-06512: at "SYS.DBMS_DATAPUMP", line 5296
ORA-06512: at line 7

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31650: timeout waiting for master process response

Action
check streams_pool_size
check ophan job

Cause
SYS datapump object corrupted or missing

Solution

spool d:\ab.txt
@d:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\catproc.sql
@d:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlrp.sql
spool off

or

shutdown immediate;

startup upgrade;

@d:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\catalog.sql

@d:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\catproc.sql

@d:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\utlrp.sql

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

Error
ORA-00604: error occurred at recursive SQL level 1
ORA-01001: invalid cursor
ORA-00600: internal error code, arguments: [qm_get_kusr], [], [], [], [], [], [], [], [], [], [], []
XDB SGA reset to NULL.

Cause

XML corrupted

Solution

uninstall

shutdown immediate;

startup;

@D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\catnoqm.sql

shutdown immediate;

startup upgrade;

spool D:\app\install_xml_db.log

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

shutdown immediate;

startup;

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

Happy Guru Purnima

Cyber Security Precautions

Union to Unpivot convert for performance

SELECT

    DISTINCT Remissierdetail.Scode AS oowncode

  FROM Remissierdetail Remissierdetail

  WHERE Remissierdetail.firmnumber   = 'KSH-000001'

  AND Remissierdetail.Scode !        = Rpad(' ',10)

  UNION

  SELECT

    DISTINCT Remissierdetail.Scode1 AS oowncode

  FROM Remissierdetail Remissierdetail

  WHERE Remissierdetail.firmnumber   = 'KSH-000001'

  AND Remissierdetail.Scode1 !       = Rpad(' ',10)

  UNION

  SELECT

    DISTINCT Remissierdetail.Scode2 AS oowncode

  FROM Remissierdetail remissierdetail

  WHERE Remissierdetail.Firmnumber   = 'KSH-000001'

  AND Remissierdetail.Scode2 !       = Rpad(' ',10)

  UNION

  SELECT

    DISTINCT Remissierdetail.Scode3 AS oowncode

  FROM Remissierdetail Remissierdetail

  WHERE remissierdetail.Firmnumber   = 'KSH-000001'

  AND remissierdetail.Scode3 !       = Rpad(' ',10)

select /* kshitij */ distinct coowncode

from Remissierdetail

unpivot 

(

  coowncode

  for node in (Scode, 

               Scode1, 

               Scode2, 

               Scode3)

) where  Firmnumber   = 'KSH-000001' and coowncode != Rpad(' ',10);

ORA-31626 ORA-31633 ORA-06512 ORA-01950

ORA-31626 ORA-31633 ORA-06512 ORA-01950
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYSTEM.SYS_EXPORT_FULL_09"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-01950: no privileges on tablespace 'SYSTEM'

 

ALTER USER system QUOTA unlimited ON SYSTEM;
ALTER USER system QUOTA unlimited ON USR;
ALTER USER system QUOTA unlimited ON INDX;

GRANT UNLIMITED TABLESPACE TO SYSTEM;

 

ALTER USER ldbo QUOTA unlimited ON SYSTEM;
ALTER USER ldbo QUOTA unlimited ON USR;
ALTER USER ldbo QUOTA unlimited ON INDX;

GRANT UNLIMITED TABLESPACE TO ldbo;