ORA-14450: attempt to access a transactional temp table already in use

While DDL(alter table ....) on a Global Temporary Table I encountered "ORA-14450".
Then I searched in V$LOCKED_OBJECTS but didn't find anything there(If I am not wrong,V$LOCKED_OBJECTS contains only DML related locks ). The I fired below query to find the session locking my TEMP Table:

SELECT s.INST_ID,
o.object_name,
s.sid,
s.STATUS,
s.serial#,
s.username,
s.osuser,
s.machine,
'alter system kill session ''' || to_char(s.sid) || ',' ||
to_char(s.serial#) || ''';' ks
FROM dba_objects o, gv$lock a, gv$session s
WHERE o.object_name = ''
AND o.owner = ''
AND a.id1 = o.object_id
AND a.type = 'TO'
AND a.sid = s.sid;

For the description of type column we can user below query:

select type,name,description from v$lock_type where type='TO';

Thanks to http://www.oracleoverflow.com/questions/266/alter-temporary-table-throws-ora-14450

Session Trace

Follow steps below to trace the current session or other running session:

1. Find the session info[for current session use v$mystat to get current session info]:
select a.sid, a.serial#, b.spid, a.username
from v$session a, v$process b
where a.username = '[USERNAME]'
and a.sid = [session id]
and a.PADDR = b.ADDR;

2. Start the trace[in this case event = 10046,trace level=12], trace file will be created at user_dump_dest as [DBNAME]_ora_[SPID].trc :
exec dbms_system.set_ev(&sid,&serial,10046,12,'');

3. stop the trace :
exec dbms_system.set_ev(&sid,&serial,10046,0,'');

4. convert the trace to readable format [ommit sys=no is you want sys queries in output file] :
tkprof [trace file name] [output file name] sys=no

**Trace Levels:
0 - No trace. Like switching sql_trace off.
2 - The equivalent of regular sql_trace.
4 - The same as 2, but with the addition of bind variable values.
8 - The same as 2, but with the addition of wait events.
12 - The same as 2, but with both bind variable values and wait events.

**Event Numbers:
10046: enable SQL statement timing
10053: CBO Enable optimizer trace

List of Trace Events:
https://netfiles.uiuc.edu/jstrode/www/oraparm/events.html
http://priitp.wordpress.com/oracle-11g-trace-events/

More about Trace:
http://www.oracle-base.com/articles/10g/SQLTrace10046TrcsessAndTkprof10g.php

spool without trailing white spaces

Below set of sample code will generate a CSV report and the output file will not have any trailing extra white spaces:

sqlplus –s user@DBNAME
set echo off
set feedback off
set pagesize 0
set linesize 2000
set heading off
set trimspool on
spool test.csv
select 'check_list,status,ins_date' from dual;
select t.check_list||','||t.status||','||to_char(t.ins_date,'dd-Mon-yy hh24:mi:ss') from my_tab t;
spool off
exit

Create DB Link in Other Schema

I have tried in several ways but failed and succeed using following set of sql (Thanks to Ritesh Raj Singh) :

SQL> grant create database link to schemauser ;

SQL> create or replace procedure schemauser.zcreatedblink as
begin

execute immediate 'create database link DBLINK_NAME connect to TARGET_USER identified by TARGET_PASSWORD using ''TARGET_TNS''';

end;
/

SQL> exec schemauser.zcreatedblink

SQL> revoke create database link from schemauser ;

SQL> drop procedure schemauser.createdblink;

Please inform if anybody has better idea.

Sql loader: Control Commit Interval

Analyzing the ADDM report we have found below:

Wait class "Commit" was consuming significant database time

It was a DB where an application continuously loads data using sql loader (using default parameters). As a result, huge amount of commits are always going on.

Now we want to control the number of rows between commits.

We changed the sqlldr script by appending ROWS=1000 and found below on sql loader log:

Space allocated for bind array: 495360 bytes(40 rows)

So, commit is occurring after insertion of each 40 rows which uses BIND size of 495360 bytes.

Now we have to calculate the appropriate size for BIND. We calculated as [(495360/40)*1000] bytes.

So, we have to use a new parameter BINDSIZE and append below in the old sqlldr command:

BINDSIZE =12384000 rows=1000

But we have to control BINDSIZE so that it doesn't exceed the max permitted value for READSIZE (read buffer size) for the platform (for 9i database it is 20MB or 20971520 bytes)[http://download.oracle.com/docs/cd/B10501_01/server.920/a96652/ch04.htm#1004855]

Now our sql loader logfile show as below:

Space allocated for bind array: 12384000 bytes(1000 rows)

Read buffer bytes:12384000

So, we are getting exactly 1000 rows interval for commits.

[Thanks to ssunda6: http://www.orafaq.com/forum/t/84903/2/]

Creating a User Defined Context Variable

Creating a user Defined Context Variable

SQL> conn / as sysdba;
SQL> CREATE OR REPLACE CONTEXT my_app_ctx using myuser.ctx_pkg;

Context created

SQL> select * from dba_context where namespace='MY_APP_CTX';

NAMESPACE SCHEMA PACKAGE TYPE
----------- ------- ------- ----------------
MY_APP_CTX MYUSER CTX_PKG ACCESSED LOCALLY

SQL> disc

SQL> conn myuser/password@DBNAME


SQL> CREATE OR REPLACE PACKAGE ctx_pkg IS
PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2);
END;
/
Package created

SQL> CREATE OR REPLACE PACKAGE BODY ctx_pkg IS
PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2) IS
BEGIN
dbms_session.set_context('my_app_ctx',p_name,p_value);
END set_ctx;
END;
/
Package body created

SQL> exec ctx_pkg.set_ctx(p_name => 'VAR1',p_value => 'VAL1');

PL/SQL procedure successfully completed

SQL> SELECT sys_context('my_app_ctx', 'VAR1') var1 FROM dual;

VAR1
------------
VAL1

Configure RMAN for RAC (multiple instances)

On our exadata machine we were planning for RMAN backup satisfying below:

1. Parallelism 14
2. Use more tan 1 instance (instance 3 & 4)
3. Exclude some of the tablespaces [due to limitation]

In order to satisfy 1 & 2 below changes have beed done using rman:

CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT 'sys/password@dw3';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK CONNECT 'sys/password@dw4';
CONFIGURE CHANNEL 3 DEVICE TYPE DISK CONNECT 'sys/password@dw3';
CONFIGURE CHANNEL 4 DEVICE TYPE DISK CONNECT 'sys/password@dw4';
CONFIGURE CHANNEL 5 DEVICE TYPE DISK CONNECT 'sys/password@dw3';
CONFIGURE CHANNEL 6 DEVICE TYPE DISK CONNECT 'sys/password@dw4';
CONFIGURE CHANNEL 7 DEVICE TYPE DISK CONNECT 'sys/password@dw3';
CONFIGURE CHANNEL 8 DEVICE TYPE DISK CONNECT 'sys/password@dw4';
CONFIGURE CHANNEL 9 DEVICE TYPE DISK CONNECT 'sys/password@dw3';
CONFIGURE CHANNEL 10 DEVICE TYPE DISK CONNECT 'sys/password@dw4';
CONFIGURE CHANNEL 11 DEVICE TYPE DISK CONNECT 'sys/password@dw3';
CONFIGURE CHANNEL 12 DEVICE TYPE DISK CONNECT 'sys/password@dw4';
CONFIGURE CHANNEL 13 DEVICE TYPE DISK CONNECT 'sys/password@dw3';
CONFIGURE CHANNEL 14 DEVICE TYPE DISK CONNECT 'sys/password@dw4';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 14;


Now we have our default configuration satisfied for 1 & 2, we are ready to deal with point 3. We can easily do this inside the RMAN script [above can also be done inside the RMAN backup script but in that case password of sys or system will become open]:


FULL LEVEL 0

configure exclude for tablespace 'USERS';
configure exclude for tablespace 'TBS1';
configure exclude for tablespace 'TBS2';
configure exclude for tablespace 'TBS3';
configure exclude for tablespace 'TBS4';

backup incremental level 0 database;

configure exclude for tablespace 'USERS' clear;
configure exclude for tablespace 'TBS1' clear;
configure exclude for tablespace 'TBS2' clear;
configure exclude for tablespace 'TBS3' clear;
configure exclude for tablespace 'TBS4' clear;

SQL 'alter system archive log current';
backup archivelog all DELETE INPUT;
backup current controlfile;

delete noprompt obsolete;
delete noprompt backup of archivelog until time 'sysdate-8';



LEVEL 1 DIFFERENTIAL

configure exclude for tablespace 'USERS';
configure exclude for tablespace 'TBS1';
configure exclude for tablespace 'TBS2';
configure exclude for tablespace 'TBS3';
configure exclude for tablespace 'TBS4';

backup incremental level 1 database;

configure exclude for tablespace 'USERS' clear;
configure exclude for tablespace 'TBS1' clear;
configure exclude for tablespace 'TBS2' clear;
configure exclude for tablespace 'TBS3' clear;
configure exclude for tablespace 'TBS4' clear;

SQL 'alter system archive log current';
backup archivelog all DELETE INPUT;
backup current controlfile;

delete noprompt obsolete;
delete noprompt backup of archivelog until time 'sysdate-8';



LEVEL 1 CUMULATIVE:

configure exclude for tablespace 'USERS';
configure exclude for tablespace 'TBS1';
configure exclude for tablespace 'TBS2';
configure exclude for tablespace 'TBS3';
configure exclude for tablespace 'TBS4';

backup incremental level 1 cumulative database;

configure exclude for tablespace 'USERS' clear;
configure exclude for tablespace 'TBS1' clear;
configure exclude for tablespace 'TBS2' clear;
configure exclude for tablespace 'TBS3' clear;
configure exclude for tablespace 'TBS4' clear;


SQL 'alter system archive log current';
backup archivelog all DELETE INPUT;
backup current controlfile;

delete noprompt obsolete;
delete noprompt backup of archivelog until time 'sysdate-8';


After configuration as above the result summery is as below:

Total Data Size: 6 TB
Excluded Data Size: 4 TB
Total Backed-up Data Size(on DB): 2 TB [90% data using EHCC or compressed for query high]
Backupset Size of one level 0 : 1.25 TB
Backupset Size of one level 1 : 100 GB
Total time for level 0: 6 hours
Total time for level 1: 60 minutes

In order to find backup piece size we have used below query:

select ctime "Date",
decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type,
bsize "Size GB"
from (select trunc(bp.completion_time) ctime,
backup_type,
round(sum(bp.bytes / 1024 / 1024/1024), 2) bsize
from v$backup_set bs, v$backup_piece bp
where bs.set_stamp = bp.set_stamp
and bs.set_count = bp.set_count
and bp.status = 'A'
group by trunc(bp.completion_time), backup_type)
order by 1, 2;
[thanks to ebrian: http://forums.oracle.com/forums/thread.jspa?threadID=1094667]