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

 

how to enable silverlight in chrome latest version

To work around Silverlight Chrome compatibility issue, 

Please add Chrome Extension: IE Tab in Chrome browser.

https://chrome.google.com/webstore/detail/ie-tab/hehijbfgiekmjfkfjpbkbammjbdenadd?hl=en

Receiving POP email error on send and receive - POP mailbox is locked.

Symptom

Receiving POP email error on send and receive - POP mailbox is locked. Cannot receive new messages.

Cause

A mail client is attempting to access a locked POP account.

Solution

Close the mail client and reboot. Wait at least 10 minutes before opening the mail client again. (The reboot may not be needed but ensures that the application thoroughly quits.)  If this does not resolve the issue please contact the Technology Support Center.


Additional Info

When a mail client connects to the POP server it locks the POP account. When the mail client has completed its tasks it issues a QUIT command to the POP server which unlocks the account. If no QUIT signal is issued and the connection remains idle for 10 minutes, the account is unlocked automatically. If mutliple computers are trying to access the POP account at the same time, this could cause the mailbox to be locked and inaccessible. Also, if the refresh rate for send/receive is more frequent than 10 minutes and a message hangs for some reason, then the server will remain locked for 10 minutes. Likewise if a client accesses the mailbox and the receive is cancelled prior to completion, the server may remain locked. The server will unlock the account automatically after 10 minutes and at that time the mail client should be able to reconnect.

ORA-00204 ORA-00202 ORA-27070 OSD-04006

recreate pfile

remove corrupted controlfile

and startup using pfile

LD Some menu options are not visible also exist button

TBLTEMPOPERATORMENURIGHTS should be blank

Getting Error Running Sql ID ORA-01722 Invalid Number

SYMPTOMS

In Oracle Balance & Control, you get the following error when running a SQL ID:

ORA-01722: invalid number

As a result, the expected data is not updated by the SQL ID.

CAUSE

Null values exist in columns accessed by the code in the SQL ID.

SOLUTION

Review all columns accessed by the code in the SQL ID for null values.  Update NULL values to zero or another appropriate number value.

Example:

select count(*) from mortgages where origination_fee is NULL;
update mortgages set origination_fee=0 where origination_fee is NULL;


1. We should try to avoid implicit conversion.
2. We should compare with same datatypes.
3. While comparing different datatypes, we should first do explicit conversion.
4. We should not store numbers in varchar2 fields.
5. We should convert the expression to number before comparing it with number.

ORA-02055 ORA-20014 ORA-06502

declare
  lncount number;
begin
  -- Call the procedure
  sp_impcodweb(oresult         => :oresult,
               imuact          => :imuact,
               infirmnumber    => :infirmnumber,
               icfilename      => :icfilename,
               icfiledirectory => :icfiledirectory,
               icsrcfile       => :icsrcfile,
               icfiletime      => :icfiletime,
               icoutfile       => :icoutfile);
  dbms_output.put_line(:oresult);
  Select Count(*)
    into lncount
    From Tbltempcoddetails
   Where Nfirmnumber = :infirmnumber
     And Ninstructiontype = 904
     And Ntransactiontype = -99;
  dbms_output.put_line(lncount);

  Merge Into Tbl904offmarket T1
  Using (Select  Nfirmnumber,
                Cclientboid,
                Dtransactiondate,
                Nnsdldpmtransactionno,
                Cisincode,
                Ctranstatus
           From Tbltempcoddetails
          Where Nfirmnumber = :infirmnumber
            And Ninstructiontype = 904
            And Ntransactiontype = -99) t
  On (T1.Nfirmnumber = t.Nfirmnumber And T1.Cclientboid = t.Cclientboid And T1.Dexecutiondate = t.Dtransactiondate
  And T1.Cisincode = t.Cisincode  And To_Number(Trim(T1.Cinstructionreferenceno)) = t.Nnsdldpmtransactionno)
  When Matched Then
    Update Set T1.Ctranstatus = t.Ctranstatus;
  Dbms_Output.Put_Line(To_Char(Sql%Rowcount) || ' rows merged. Sudi');
end;
/

Error invalid number at line -- Merge Into Tbl904offmarket T1


1) solution

select t1.Cinstructionreferenceno from Tbl904offmarket t1  WHERE REGEXP_LIKE(t1.Cinstructionreferenceno, '[[:alpha:]]')

select to_number(t1.Cinstructionreferenceno0 from Tbl904offmarket t1  WHERE REGEXP_LIKE(t1.Cinstructionreferenceno, '[[:alpha:]]')

2)

CREATE OR REPLACE FUNCTION IS_NUMBER (p_input IN VARCHAR2) RETURN NUMBER
AS
BEGIN
  RETURN TO_NUMBER (p_input);
EXCEPTION
  WHEN OTHERS THEN RETURN NULL;
END IS_NUMBER;
/


CREATE OR REPLACE FUNCTION IS_NUMBER (p_input IN VARCHAR2) RETURN NUMBER
AS
BEGIN
  RETURN TO_NUMBER (p_input);
EXCEPTION
  WHEN OTHERS THEN RETURN 0;
END IS_NUMBER;
/

procedure must be declared

in application ldbo. is missing before procedure

role is missing for that procedure


Alternative: if you create public synonym for that it will work