ORA-02449: unique/primary keys in table referenced by foreign keys

When you hit this error, it means that you can not drop(modify) current table (or index) because there are some foreign keys defined on some other tables referencing unique/primary keys of the current table. You should drop these foreign keys in order to drop/modify current table/index.

SQL script to find out these foreign keys:

select * from dba_constraints where R_CONSTRAINT_NAME in (select CONSTRAINT_NAME from dba_constraints where owner='MYOWNER' and table_name='MYTABLE') and CONSTRAINT_TYPE='R';

How to Create Function Based Index

Here is an example of creating function based index setting null values to default values:

CREATE INDEX ERKAN.MYTABLE_FUNC_IX on ERKAN.MYTABLE (COL1, COL2,nvl(COL3,COL1), nvl(COL4,to_date('01/01/2009 00:00:00','DD/MM/YYYY hh24:mi:ss')), nvl(COL5,0))
tablespace MYTABLESPACE;

How to trace session(s)

Using DBMS_SUPPORT package

Starting Trace:
Alter system set max_dump_file_size=unlimited;
exec sys.dbms_support.START_TRACE_IN_SESSION(sid,serial#,waits=>TRUE, binds=>TRUE ) ;

Stopping Trace:
Exec sys.DBMS_SUPPORT.STOP_TRACE_IN_SESSION( sid , null )
Alter system set max_dump_file_size=102400;

Generating Start-Stop Trace script for sessions (trace.sql):

ACCEPT USERNAME char prompt 'Enter the User Name > '
variable b0 varchar2(50)
exec :b0 := upper('&USERNAME');

select '------------ START TRACE ------------' TRACE_SCRIPT from dual
union all
select 'Alter system set max_dump_file_size=unlimited;' TRACE_SCRIPT from dual
union all
select 'exec sys.dbms_support.START_TRACE_IN_SESSION('||sid||','||serial#||',waits=>TRUE,binds=>TRUE ) ;' TRACE_SCRIPT from v$session where username=:b0
union all
select '------------ STOP TRACE ------------' TRACE_SCRIPT from dual
union all
select 'Exec sys.DBMS_SUPPORT.STOP_TRACE_IN_SESSION('||sid||',null);' TRACE_SCRIPT from v$session where username=:b0
union all
select 'Alter system set max_dump_file_size=102400;' TRACE_SCRIPT from dual
/

Using Oradebug

Find server process id of the session:

select spid from v$session s ,v$process p where s.paddr=p.addr and s.sid=&sid

Starting Trace:
sqlplus '/ AS SYSDBA'
SQL> oradebug setospid {spid}
Oracle pid: {pid}, Unix process pid: {spid}, image: oracle@... (TNS V1-V3)
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.

Stopping Trace:
SQL> oradebug event 10046 trace name context off
Statement processed.

How to create new service name

In order to define a new service name:

1-) Modify service_names parameter and add new service name:
if spfile is used then
alter system set service_names='..., {new_service_name}' scope=both;
if init ora file is used then

• alter system set service_names='..., {new_service_name}';
• modify init ora file and add {new_service_name} to service_names parameter
  

2-) Add following SID description to listener configuration (listener.ora)
SID_LIST_... =
( SID_LIST=
(SID_DESC =
(ORACLE_HOME =...)
(SID_NAME = ...))
....
(SID_DESC =
(GLOBAL_DBNAME={new_service_name})
(SID_NAME = {oracle_sid})
(ORACLE_HOME ={oracle_home}))
)

3-) Restart listener.

Now you may connect to database using following tnsnames.ora configuration:

{tnsname}.WORLD = (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(Host = {host_name})(Port = {port}))(CONNECT_DATA =(SERVICE_NAME = {new_service_name})))

After login if you query v$session system view, you will see new service name in SERVICE_NAME column for your session.