Default sqlplus prompt is very simple once you connect to a database.

sqlplus / as sysdba
SQL>

This article shows how you can enhance it using sqlplus command SET SQLPROMPT and predefined user  variables.

sqlplus / as sysdba
SQL> set sqlprompt "DBAORA.COM> "
DBAORA.COM>

Continue reading →

If your database was started in nomount mode you can encounter following error

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 16 12:39:28 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: sys@ora12c as sysdba
Enter password:
ERROR:
ORA-12528: TNS:listener: all appropriate instances 
are blocking new connections

To solve this problem you need to make static registration of your service in listener.ora.

Settings before solving the error

tnsnames.ora

ORA12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = laptop)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORA12C)
    )
  )

listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\oracle\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\oracle\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = laptop)(PORT = 1521))
    )
  )

New settings

listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\oracle\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\oracle\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORA12C)
      (ORACLE_HOME = D:\app\oracle\product\12.1.0\dbhome_1)
      (SID_NAME = ORA12C)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = laptop)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = D:\app\oracle\product\12.1.0\dbhome_1\log

You need to restart listener to make it effective. Once it’s done you should be able to connect to your database which is working in nomount.

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 16 12:50:15 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: sys@ora12c as sysdba
Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit
Production With the Partitioning, OLAP, Advanced Analytics 
and Real Application Testing options

SQL>

Have a fun

Tomasz

ORA-01000: maximum open cursors exceeded – It’s typical error encountered in Oracle database. It happens when too much cursors are opened on an oracle instance.

Maximum number of opened cursors for an instance is limited by parameter

open_cursors

usually for small databases it’s about 300 and can be very huge for bigger ones. To increase this parameter it requires to bounce database instance.

To find out who opened many cursors on instance use following query

SELECT SID, count(*) ses_cursors, sum(count(*)) OVER() total_cursors
FROM v$open_cursor
GROUP BY SID
ORDER BY 2 DESC;

       SID ses_cursors total_cursors 
---------- ----------- -------------
       572         115           837 
      2835          95           837 
...
      1987           1           837 
      1417           1           837 

 41 rows selected

then you can investigate a given session why so many cursors are opened.

Have a fun 🙂

Tomasz

This article presents undocumented Oracle function SYS_OP_MAP_NONNULL that is another way to compare NULL values in Oracle.

It’s worth to read following articles to get understanding how Oracle treats NULL value in Oracle database:

• NULL in SQL and PL/SQL
• Create table as select and NULL columns in views

Continue reading →

This article shows simple method to duplicate records based on value in a column.

For example following query returns 3 records

select 'a' letter, 2 num from dual
union all
select 'b', 4 from dual
union all
select 'c', 4 from dual

LETTER        NUM
------ ----------
a               2 
b               4 
c               4

to duplicate records based on column NUM you can do following transformation using CONNECT BY

with a as (
select 'a' letter, 2 num from dual
union all
select 'b', 4 from dual
union all
select 'c', 4 from dual
)
select a.*, b.column_value 
from a,
     table(cast(multiset(
                 select level from dual
                 connect by level <= num ) 
           as sys.odciNumberList)) b;

LETTER        NUM COLUMN_VALUE
------ ---------- ------------
a               2            1 
a               2            2 
b               4            1 
b               4            2 
b               4            3 
b               4            4 
c               4            1 
c               4            2 
c               4            3 
c               4            4

You can use above method CONNECT BY to transform complex string (comma separated string) in a column into multiple records and simple string(split based on comma)

select 'a,b,c' letter from dual
union all
select 'g,h' from dual
union all
select 'm,k,l' from dual

LETTER
------
a,b,c  
g,h    
m,k,l

and now simple

with a as (                         
select 'a,b,c' letter from dual
union all
select 'g,h' from dual
union all
select 'm,k,l' from dual
)
select a.*, b.column_value 
  from a,
     table(cast(multiset(
                  select regexp_substr(letter,'[^,]+',1,level) 
                    from dual
                  connect by level <= regexp_count(letter, ',')+1)
           as sys.odciVarchar2List)) b;

LETTER COLUMN_VALUE    
------ ----------------
a,b,c  a               
a,b,c  b               
a,b,c  c               
g,h    g               
g,h    h               
m,k,l  m               
m,k,l  k               
m,k,l  l

Have a fun 🙂
Tomasz

If you want to add RELY DISABLE constraint to child table that references a master table with primary key constraint with NORELY flag you can encounter following error:

ORA-25158: Cannot specify RELY for foreign key if the associated primary key is NORELY

Example:

create table master_tbl
(id number primary key);

select constraint_name, constraint_type, table_name, rely
from user_constraints
where table_name='MASTER_TBL';

CONSTRAINT_NAME     CONSTRAINT_TYPE TABLE_NAME    RELY
------------------- --------------- ------------- ----
SYS_C0012428928                   P MASTER_TBL        
create table child_tbl
(id number);

alter table child_tbl add constraint child_tbl_fk
foreign key (id) references master_tbl rely disable;

ORA-25158: Cannot specify RELY for foreign key 
           if the associated primary key is NORELY

First option is to modify primary key constraint of table master_tbl to RELY but there is another workaround – just add DISABLE RELY constraint in two steps to child_tbl.

alter table child_tbl add constraint child_tbl_fk
foreign key (id) references master_tbl disable;

alter table child_tbl
modify constraint child_tbl_fk rely;

select constraint_name, constraint_type, table_name, rely
from user_constraints
where table_name in ('MASTER_TBL', 'CHILD_TBL');

CONSTRAINT_NAME   CONSTRAINT_TYPE TABLE_NAME     RELY
----------------- --------------- -------------- ----
CHILD_TBL_FK      R               CHILD_TBL      RELY
SYS_C0012428928   P               MASTER_TBL

Have a fun 🙂

Tomasz