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.

Commit from a Procedure/Function Executed Through a Database Link, ORA-02064: distributed operation not supported

It is not supported to issue a commit from a procedure with OUT parameters or a function with return value, when the procedure/function is executed through a database link.

Here is a test case, working fine:

1-) At remote database create following procedure:

create or replace procedure erkan_trunc

is

begin

insert into XXX values(1,1,'erkan saka'); -- Create a transaction at remote db

EXECUTE IMMEDIATE 'TRUNCATE TABLE ERKAN.XXX'; -- This is a DDL! which also autocommits

end;

/

2-) At source database create following procedure

create or replace procedure erkan_dene

is

begin

insert into erkan.A values(1); -- Create a transaction at local (source) db

erkan_trunc@{db_link}; -- remote procedure call

rollback; -- End local transaction

end;

/

3-) Working fine:

SQL>exec erkan_dene

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Here is an other test case with ORA-02064: distributed operation not supported:

1-) At remote database create following procedure:

create or replace function erkan_trunc2

RETURN NUMBER  -- This is the reason causing ORA-02064

is

begin

insert into XXX values(1,1,'erkan saka');

EXECUTE IMMEDIATE 'TRUNCATE TABLE ERKAN.XXX';

RETURN (0);

end;

/

2-) At source run following PL/SQL

SQL>declare

  2     de_return number;

  3  begin

  4     de_return := erkan_trunc2@{db_link};

  5             dbms_output.put_line('de_return='||de_return);

  6  end;

  7  /

declare

*

ERROR at line 1:

ORA-02064: distributed operation not supported

ORA-06512: at "ERKAN.ERKAN_TRUNC2", line 6

ORA-06512: at line 4

Elapsed: 00:00:00.01

ORA-02064: distributed operation not supported

If a commit/rollback or autocommit(DDL) operation is executed within a procedure or function which is called remotely via database link then ORA-02064 may occur. Exact explanation is:

It is not supported to issue a commit from a procedure with OUT parameters or a function with return value, when the procedure/function is executed through a database link.

I tested following two senarios. First works fine while second fails with ORA-02064:

Senario 1-)

* Create following procedure at remote database:

create or replace procedure erkan_trunc

is

begin

insert into XXX values(1,1,'erkan'); -- in order to create a local transaction at remote database

EXECUTE IMMEDIATE 'TRUNCATE TABLE ERKAN.XXX'; -- autocommits

end;

/

* Create following procedure at source database:

create or replace procedure erkan_dene

is

begin

insert into erkan.A values(1); --in order to create a local transaction in source database

BEGIN

erkan_trunc@remote_tns;

EXCEPTION

       WHEN OTHERS

       THEN

dbms_output.put_line('Error while truncate: ' || SUBSTR (SQLERRM, 1, 100););

END;

rollback;

end;

/

* Works fine:

SQL>exec erkan_dene

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

if i hold lock on table XXX at remote database...

SQL>exec erkan_dene

Error while truncate: ORA-00054: resource busy and acquire with NOWAIT specified

ORA-06512: at "ERKAN.ERKAN_TRUNC", line 5

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Senario 2-)

* Create following function at remote database:

create or replace function erkan_trunc2

RETURN NUMBER

is

begin

insert into XXX values(1,1,'erkan');

EXECUTE IMMEDIATE 'TRUNCATE TABLE ERKAN.XXX';

RETURN (0);

end;

/

* Run following pl/sql script at source database:

SQL>declare

  2     de_return number;

  3  begin

  4     de_return := erkan_trunc2@remote_tns;

  5             dbms_output.put_line('de_return='||de_return);

  6  end;

  7  /

declare

*

ERROR at line 1:

ORA-02064: distributed operation not supported

ORA-06512: at "ERKAN.ERKAN_TRUNC2", line 6

ORA-06512: at line 4

Elapsed: 00:00:00.01

ORA-38029: object statistics are locked

If this error occurs while trying to analyze a table then run following to unlock the statistics:

exec DBMS_STATS.UNLOCK_TABLE_STATS('{owner}','{table name}');

You can see list of all locked tables by running following query:

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM');

or

select 'exec DBMS_STATS.UNLOCK_TABLE_STATS('''||owner||''','''||table_name||''');' from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM')

For further detail, refer to metalink doc id 433240.1

LOCALLY MANAGED TABLESPACE with SPACE MANAGEMENT AUTO

CREATE TABLESPACE DATA_1M DATAFILE
'/data01/dbfs/data_1m01.dbf' SIZE 2048M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
/

alter tablespace DATA_1M add datafile '/data01/dbfs/data_1m02.dbf' SIZE 1024M AUTOEXTEND ON NEXT 512M MAXSIZE 2048M;


alter database datafile '/data01/dbfs/data_1m02.dbf' AUTOEXTEND ON NEXT 1024M MAXSIZE 3072M;