private dblink

DB ldccm

User ldccm

Private Dblink cmldlink connect to LD database

Now how user will access ldfibs table of other database using private link.

Create view vwldfibs as select * from ldfibs@cmldlimk;

Grant view vwldfibs to user1;

Calculating ages in years

Calculating ages in years isn't as easy as

( current date - birth date ) / 365

@connor_mc_d has a solution - convert the dates to numbers:

trunc ((

 to_number (to_char ( sysdate, 'YYYYMMDD' )) - 

 to_number (to_char ( birth, 'YYYYMMDD' ))

 ) / 10000

)

https://t.co/gVoEijf874 https://t.co/6iSyzBXdUh

Great thanks Connor Sir

Gather Stats of Table Partition Latest/Current Month Partition

 

BEGIN

 FOR CUR_REC IN (select * from (select table_owner,table_name,partition_name,partition_position,last_analyzed from dba_tab_partitions where table_name  ='POWEROFATTORNEYSTOCKS' order by partition_position desc) where rownum=1) LOOP

    BEGIN

 EXECUTE IMMEDIATE 'begin dbms_stats.gather_table_stats(ownname =>''LDBO'' ,Tabname =>''' || cur_rec.table_name || ''',Partname =>''' || cur_rec.partition_Name || ''',cascade => true, DEGREE=>DBMS_STATS.DEFAULT_DEGREE); end;';

    EXCEPTION

      WHEN OTHERS THEN

        NULL;

    END;

  END LOOP;

END;

/

Kill Third Party Tool Like PLSQL Developer Session

BEGIN

 FOR CUR_REC IN (select ss.username,ss.terminal,ss.sid,ss.serial#,ss.inst_id from gv$session ss where module='PL/SQL Developer') LOOP

  BEGIN

 EXECUTE IMMEDIATE 'alter system disconnect session''' || CUR_REC.sid || ',' || CUR_REC.serial# || ',@' || CUR_REC.inst_id || ''' immediate';

 EXCEPTION

      WHEN OTHERS THEN

        NULL; 

    END;

  END LOOP;

END;

/

ORA-12018: following error encountered during code generation for ..ORA-00979: not a GROUP BY expression

 ORA-12018 ORA-00979 ORA-06512

When we create Materialized report view it is successfully created and reports are coming properly.

But when we do complete refresh using below mentioned command it is showing an error

SQL> exec dbms_mview.refresh('mv_rkcapitaldashboard','C') ;

begin dbms_mview.refresh('mv_rkcapitaldashboard','C'); end;

ORA-12018: following error encountered during code generation for "LDBO"."MV_RKCAPITALDASHBOARD"

ORA-00979: not a GROUP BY expression

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2809

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3025

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994

 

Bug

Oracle Version 11.2.0.4

Solution

alter session set "_complex_view_merging"=false;

or add following in query

/*+ opt_param('_complex_view_merging','false') */

or try following in SQL

/*+ NO_QUERY_TRANSFORMATION */ 

exec dbms_mview.refresh('mv_rkcapitaldashboard','C') ;

The message could not be dispatched because the service at the endpoint address 'net.pipe://localhost/ServiceName' is unavailable for the protocol of the address

Getting the error, The message could not be dispatched because the service at the endpoint address 'net.pipe://localhost/ServiceName' is unavailable for the protocol of the address.

If you use net.pipe protocol, you should check if "Net.Pipe Listener Adapter" service is running in Services management console.

Then, try using ServiceModelReg.exe -r to repair all WCF components.

And Check whether you enable net.pipe protocol in IIS.

give all permissions for the service files and folders to the account under which the app pool for the website is configured to run.

https://www.google.com/amp/s/rohitguptablog.wordpress.com/2011/06/16/configuring-wcf-service-with-nettcpbinding/amp/

Oracle 19c Security parameters Cyber security

IIS Web Security | Cyber Security | VAPT | Best Practices

1) Don't use default location c: to host application

2) disable default page

3) disable directory browsing

4) Disable the Http OPTIONS Method

5) Enable Dynamic IP Address Restrictions

The Dynamic IP to prevent DoS attacks.

6) Ensure that you keep up to date with the latest updates and security patches. The majority of hacks affecting the web server occur on unpatched servers.

7) Disabling-IIS-Web-Banner-And-Other-IIS-Headers

Please make sure following IIS security settings should be configured at your public hosting customer facing application.

Remove the default page or stop/disable from the IIS server

Open IIS Manager

Click the server name

Double click on Default Document

On the right side, click “Disable”

 Or redirect default page to application login page

Using the Web site redirect function build in IIS Manager.

1. Start IIS Manager from Administrator Tools.

2. Expand the ServerName, and then expand the Sites

3. Double Click on the Default Web Site to open up the Default Web Site properties.

4. Under section"IIS", double click on "HTTP Redirect".

5. Check "Redirect requests to this destination", and fill in the Web Application link in the box. For example "Https://abc.com/ldclientlevelH"

6. Check Only redirect requests to content in this directory (not subdirectories)"

7. On the right side pan, click Apply.

8. Since the you Websites are under Default Web Sites, this change will be applied to any websites that's under Default Websites. Please go to each website and do the HTTP Redirect, make sure None of the box is checked.

9. Reset IIS.

Disabling IIS Directory Browsing or Listing

The attacker can display the whole list of files in the directories. These directories include sensitive files such as password files, database files, FTP logs etc. It is obvious that this information was not intended for public view.

1. Go to RUN

2. Type inetmgr and click Enter to open IIS console

3. Select Application Directory under Default website or other Website.

4. In the right hand side panel double click on the "Directory Browsing" option.

5. Click on the Disable button

How do you stop users directly accessing files on a website in IIS in that directory

Directory is not accessible now after disabling directory browsing but if hacker knows the file name then file can be accessed.

URL Rewrite

Or

If anyone directly come to any page it should be redirect to authentication page 

Disabling-IIS-Web-Banner-And-Other-IIS-Headers

For security purposes, it may be desirable to disable the X-ASPNET-VERSION and X-Powered-By HTTP Headers.

 

The HTTP header "X-Powered-By" reveals the version of IIS being used on the server. This can be disabled by:

1. Open the IIS Manager

2. Select the website that website Server is running under.

3. Select "HTTP Response Headers"

4. Select the "X-Powered-By" HTTP Header and select "Remove"

The Http Header "X-ASPNET-VERSION" reveals the version of ASP.NET being used by the website Server application pool. This can be disabled by:

 

1. Open the web.config file for website Server (located in the root directory for the website).

2. Just after the <system.web> tag add this: <httpRuntime enableVersionHeader="false" />

3. Save the file.

 

Note: The SERVER header variable should not be removed as it will cause certain functionality within website Server to break. 

Disable the OPTIONS Method

The OPTIONS method provides a list of methods that are supported by the web server. Although this might seem beneficial, it also provides useful information to the attacker at the reconnaissance stage. Therefore, we recommend that you disable the OPTIONS method completely. This can be done by denying the OPTIONS verb in HTTP verb request filtering rules in IIS.

Open the IIS Manager

Select the name of the machine to configure this globally (or change to the specific web site for which you need to configure this)

Double click on Request Filtering

Change to the HTTP Verbs tab

From the Actions pane, select Deny Verb

Insert OPTIONS in the Verb field and click on OK to save changes

Enable Dynamic IP Address Restrictions

The Dynamic IP Restrictions module helps to block access to IP addresses that exceed a specified number of requests and thus helps prevent denial-of-service (DoS) attacks. This module will inspect the IP address of each request sent to the web server and will filter these requests in order to temporarily deny IP addresses that follow a particular attack pattern.

The Dynamic IP Restrictions module can be configured to block IP addresses after a number of concurrent requests or to block IP addresses that perform a number of requests over a period of time. Depending on your IIS version you will need to enable either the IP Security feature or the IP and Domain Restrictions 

To set or modify dynamic IP restrictions:

Open the IIS Manager

Select the name of the machine to configure this globally (or change to the specific web site for which you need to configure this)

Double click on IP Address and Domain Restrictions

From the Actions pane, select Edit Dynamic Restriction Settings

Modify and set the dynamic IP restriction settings as needed and click on OK to save changes

ora-01843 not valid month

ora-01843 not valid month

Date which is passing in query has wrong format.

Check server date format

Or

Web.config (.NET application)

Add following or do setting in IIS

<globalization culture="en-GB"/>

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.

&#xD;

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;

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;

 

ORA-12170: TNS:Connect timeout occurred

If error is occur at time of software login, plz check tnsping for db service at local / App server.
if error is occur at time of process, plz check the db links.

Tnsping 172.168.1.7:1521/kshitij

Telnet 172.168.1.7 1521

ORA-01536: space quota exceeded for tablespace 'USR'

ALTER USER LDBO QUOTA unlimited ON USR;

 

ORA-14400 inserted partition key does not map to any partition

Alter table LDBO.POWEROFATTORNEYSTOCKS add PARTITION SAUDA_APR2019 values LESS THAN (TO_DATE('01-MAY-2019','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M );

 

 

Alter table LDBO.TBLSNPRISKDETAILS add PARTITION SAUDA_APR2019 values LESS THAN (TO_DATE('01-MAY-2019','DD-MON-YYYY'))  TABLESPACE USR STORAGE (INITIAL 100M NEXT 100M );

 

Oracle 19c Features for Developer and DBA

Features which can help us

Row limiting : Oracle 12c onwards introduces sql syntax for row limiting.  This makes it easier to retrieve records in sets for display or processing.

 

Example :-

 

create table employee (id integer primary key, name varchar2(10));

insert into employee values (1,'Adam');

insert into employee values (2,'Ben');

insert into employee values (3,'Colin');

insert into employee values (4,'Dean');

insert into employee values (5,'Evan');

insert into employee values (6,'Frank');

insert into employee values (7,'Greg');

insert into employee values (8,'Hank');

insert into employee values (9,'Ian');

insert into employee values (10,'Jack');

commit;

 

SQL> select * from employee order by id fetch first 3 rows only;

 

        ID NAME

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

         1 Adam

         2 Ben

         3 Colin

 

SQL> select * from employee order by id offset 3 rows fetch next 3 rows only;

 

        ID NAME

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

         4 Dean

         5 Evan

         6 Frank

 

SQL> select * from employee order by id fetch first 50 percent rows only;

 

        ID NAME

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

         1 Adam

         2 Ben

         3 Colin

         4 Dean

         5 Evan

 

Extended Datatypes VARCHAR2 32767 bytes

it is now possible to create VARCHAR2, NVARCHAR2, and RAW attributes of size 32767 bytes.

 

Limitations to RESOURCE, SELECT ANY DICTIONARY

Dictionary tables containing password hashes (DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, XS$VERIFIERS) are no longer included in the SELECT ANY DICTIONARY system privilege.  This makes it safer to give developers access to dictionary tables for tuning and debugging, without giving them the chance to run brute force attacks ...

 

Data Redaction via DBMS_REDACT SECURE SENSTIVE DATA(MASKING) which I shared previously

 

some bulk transactions like "create table as" and "insert into select from" will automatically collect statistics. No need to analyze

 

Identity columns

In previous versions of Oracle there was no implicit relationship between a primary key and the sequence that maintained it's value, so developers needed to implement this relationship via insert triggers or application code.  Oracle 12c removes that requirement for custom coding by implementing the relationship in the CREATE TABLE statement using the IDENTITY clause.

 

 

SQL> CREATE TABLE TEST_TABLE (ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY, TEXT VARCHAR2(100));

 

Table created.

 

When we insert into the table the ID column is automatically populated.

 

SQL> insert into test_table (text) values ('aaa');

1 row created.

 

SQL>commit;

 

Commit complete.

 

SQL> select * from test_table;

 

     ID TEXT

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

      1 aaa

 

When we created the table a sequence was also created.

 

SQL> select object_name, object_type from dba_objects where owner = 'TEST';

 

OBJECT_NAME     OBJECT_TYPE

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

SYS_C009854     INDEX

ISEQ$$_91602    SEQUENCE

TEST_TABLE      TABLE

 

sequence.nextval as default and identity columns

The new Oracle 12c now allows to define a table with the sequence.nextval directly in the in-line column definition:

 

SQL> create table bar (

2 id number generated as identity,

3 foo varchar2(50) not null,

4 constraint bar_pk primary key (id)

5 );

 

Table created.

 

SQL> insert into bar (foo) values ('baz');

 

1 row created.

 

SQL> insert into bar (foo) values ('test');

 

1 row created.

 

SQL> insert into bar (foo) values ('whoo');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select * from bar;

 

ID FOO

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

1 baz

2 test

3 whoo

 

Concurrent Execution of UNION and UNION ALL Branches Oracle Database 12C release 1

 

UNION or UNION ALL consists of many queries(branches) that in pre 12C releases were executed one by one.

 

<BRANCH1>

UNION

<BRANCH2>

 

<BRANCH1>

UNION ALL

<BRANCH2>

 

 

So in pre 12C first  is processed BRANCH1 then BRANCH2. Of course each individual query(branch) can be processed in serial or in parallel but only one branch at time.

 

Oracle 12C allows to run branches(statement) of UNION or UNION ALL concurrently. It means that BRANCH2 can be processed together with BRANCH1 :) . It can even return data faster than BRANCH1.

 

This feature is turned on automatically and entire UNION or UNION ALL is processed in parallel

 

 

Enhancement allows PLSQL declarations in the WITH clause

https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1

 

JSON compatiabilty

Already shared

 

SQL Plus Last Login Time

select USERNAME,LAST_LOGIN from DBA_USERS where LAST_LOGIN is not null;

 

sequence reset

alter sequence ldbo.FWDBROKUNIQUENUMBER restart start with 1;

SELECT ldbo.FWDBROKUNIQUENUMBER.NEXTVAL FROM dual;

 

default on null

There is new feature: default on null. For example:

 

CREATE TABLE tab1 (

  col1        NUMBER DEFAULT 5,

  col2        NUMBER DEFAULT ON NULL 7,

  description VARCHAR2(30)

);

So when you try to INSERT null in col2, this will automatically be 7.

 

Oracle Default User

select * from dba_users where oracle_maintained='Y';

 

READ ANY TABLE

GRANT READ ANY TABLE TO psmith;

As with the READ object privilege, the READ ANY TABLE system privilege does not enable users to lock tables in exclusive mode nor select tables for update operations. Conversely, the SELECT ANY TABLE system privilege enables users to lock the rows of a table, or lock the entire table, through a SELECT ... FOR UPDATE statement, in addition to querying any table.

 

PRIVATE TEMPORARY TABLES

Private temporary tables enables Oracle 19c users to create temporary database objects that are automatically dropped at the end of a transaction or a session.

 

You can create temporary tables with these characteristics:

– NAME must be prefixed by “ORA$PTT_” (or the current value of PRIVATE_TEMP_TABLE_PREFIX initialization parameter)

– DATA is automatically deleted at the end of the transaction (just like GLOBAL TEMPORARY TABLES created with ON COMMIT DELETE ROWS clause)

– DEFINITION is automatically dropped at the end of the transaction (ON COMMIT DELETE DEFINITION) or at the end of the session (ON COMMIT PRESERVE DEFINITION)

This feature is useful for developers as well as DBAs, because it could reduce the number of temporary or “test” tables created in the database.

Example:

CREATE PRIVATE TEMPORARY TABLE ora$ptt_testptt (testcol NUMBER)

ON COMMIT DROP DEFINITION;

 

 

SQL CANCELLING

A new command is introduced for DBAs to cancel blocking or too expensive statements instead of kill the originating session.

ALTER SYSTEM CANCEL SQL ‘sid,serial#’;

ALTER SYSTEM CANCEL SQL ‘sid,serial#,sqlid’;

ALTER SYSTEM CANCEL SQL ‘sid,serial#,@inst#’;

ALTER SYSTEM CANCEL SQL ‘sid,serial#,@inst#,sqlid’;

 

 

APPROX_COUNT(), APPROX_SUM() and APPROX_RANK() are introduced with Oracle 18c, which can calculate 99% accurate results in very high speed.

select oowncode, approx_count(*) 

from sauda

group by oowncode

having approx_rank(partition by oowncode order by approx_count(*) desc) <= 1

order by 1;

 

DBMS_LOCK.SLEEP is depreciated in Oracle 18c and DBMS_SESSION.SLEEP is introduced, and is available with no additional grants needed.

 

 

DBMS_LOB UTL directory checking ORA-22288 file or LOB operation FILEOPEN failed File does not exists or user does not have read or write privileges

alter system set "_kolfuseslf" = true scope=spfile sid='*';

 

restart both node services

 

select inst_id,name,value from gv$parameter where name like '%kolfuseslf%';

 

Virtual Private Database VPD to hide/secure table column data | Not Encryption

CREATE OR REPLACE

FUNCTION FN_LDVPD (obj_owner IN VARCHAR2, obj_name IN VARCHAR2)

RETURN VARCHAR2

AS

BEGIN

RETURN 'rowid = ''0''';

END FN_LDVPD;

/

 

begin

      dbms_rls.drop_policy (

          object_schema     => 'LDBO',

          object_name      => 'ACCOUNTADDRESSDETAIL',

          policy_name      => 'LD_PAN_POLICY'

      );

END;

/

 

 

SELECT OOWNCODE,PANGIR FROM ACCOUNTADDRESSDETAIL WHERE TRIM(PANGIR) IS NOT NULL;

 

BEGIN

DBMS_RLS.ADD_POLICY(object_schema=> 'LDBO',

object_name=> 'ACCOUNTADDRESSDETAIL',

policy_name=> 'LD_PAN_POLICY',

function_schema=> 'LDBO',

policy_function=> 'FN_LDVPD',

statement_types   => 'SELECT',

sec_relevant_cols=> 'PANGIR',

policy_type => DBMS_RLS.SHARED_STATIC,

sec_relevant_cols_opt=> dbms_rls.ALL_ROWS);

END;

/

 

SELECT OOWNCODE,PANGIR FROM ACCOUNTADDRESSDETAIL WHERE TRIM(PANGIR) IS NOT NULL;

ODAC for Visual Studio Download Link Oracle.ManagedDataAccess.dll

https://www.oracle.com/technetwork/developer-tools/visual-studio/downloads/index.html

 

install it at Visual Studio machine to select the dll in project

 

 

DLL location

Oracle.ManagedDataAccess.dll

C:\Program Files (x86)\Oracle Developer Tools for VS2017\odp.net\managed\common

 

Ora-24247 Add User

Exec dbms_network_acl_admin.add_privilege ('utl_http.xml','KSHITIJ',TRUE,'connect',null,null);  

Exec dbms_network_acl_admin.add_privilege ('utl_http.xml','KSHITIJ',TRUE,'resolve',null,null);  

commit; 

Oracle Error Exception Handling User Defined Message

ORA-01652

Temporary tablespace or server space is full. Please contact to your IT/Server Team.

 

ORA-01653

Database tablespace or server space is full. Please contact to your IT/Server Team.

 

ORA-01691

Database tablespace or server space is full. Please contact to your IT/Server Team.

 

ORA-12801

Check the Server Resources. Please contact to your IT/Server Team.

 

ORA-12805

Check the Server Resources. Please contact to your IT/Server Team.

 

 

ORA-03111

Network/Firewall timeout between application and server. Please contact to your IT/Network Team.

 

ORA-03135

Network/Firewall timeout between application and server. Please contact to your IT/Network Team.

               

ORA-00054

Resource is busy. Please try after sometimes.

 

ORA-12805: parallel query server died unexpectedly

Error
ORA-12805: parallel query server died unexpectedly

Cause
The Oracle parallel query server is unable to handle the degree of parallelism specified either due to a conflict or because

the number of parallel progress is too many for the resources in the environment on which it runs.

Solution
Check alert.log

Increase Resources(Server Space/Add more temp files/oracle memory) where you are running that query/report.
This query/report is working fine in production.


for Details please raise the SR to Oracle Global Support.

ORA-08103 object no longer exists

Error

ORA-08103 object no longer exists

Cause

1:  Database corruption of a header block.

2:  Accidental delete of the target table. (check the recyclebin)

3: Data file I/O error (check alert log)

4: corruption in UNDO log (drop and re-create)

5. An index is disabled or is offline.

6. Rebuild index job is running for tables and indexes used in error raised query.

Solution

6. Check process/report/query after completed rebuild index.

For other cause contact to Production DBA team, they will contact to Oracle global support.

Ora-03111 break received on communication channel

Error: Ora-03111 break received on communication channel

Cause:

a. Network/Firewall timeout between application server and database server.

eg. If firewall timeout is 1 min then query takes more than 1 min to give result to application, then it will throw that

error.

b. SQL Query cross the specified timeout value from web services like IIS connection.

Analysis

run same query from plsql developer/Toad and Application at machine or server at Data Center.

Action/Solution

Increase the Network/Firewall timeout if any.

Increase any IIS timeout if any.

if issue still exists, then please raise the SR to Oracle Global Support.

ORA-01758: table must be empty to add mandatory (NOT NULL) column

Command

Alter Table Tblpledgeemaster add nFundingallowed number(16,4) not null /* Contains Total Funding allowed */ ;

 

Error

ORA-01758: table must be empty to add mandatory (NOT NULL) column

 

 

Solution

 

1) provide a default value for the column along with alter table add column command.

 

 

2) Add the column without the NOT NULL constraint then update then modify not null

 

 

Alter Table Tblpledgeemaster add nFundingallowed number(16,4) /* Contains Total Funding allowed */ ;

 

UPDATE Tblpledgeemaster set nFundingallowed= 0;  /* Please contact to business team or developer for default value */

 

commit;

 

ALTER TABLE Tblpledgeemaster MODIFY (nFundingallowed NOT NULL);

 

3)  Empty the table, apply the NOT NULL and add the data back to the table