pga_aggregate_target & workarea_size_policy and Some Hidden Parameters

With "workarea_size_policy='AUTO'" oracle internally manages amount memory needed to sort and other task.
But using below hidden parameters oracle puts limit for the amount of memory for a single process, so that, a single process cannot eat up all tha PGA.

"_smm_max_size" maximum work area size in auto mode (serial)
"_pga_max_size" Maximum size of the PGA memory for one process

Value of above parameter depends on the size of "pga_aggregate_target".
The default value varies for versions and for value of "pga_aggregate_target" (or "memory_target" for 11g, if AMM in enabled).

We can use below queries to find values for above parameters:

1. Find the current value for "_smm_max_size"
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm = '_smm_max_size';

2. Find the current value for "_pga_max_size"
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm = '_pga_max_size';

3. Calculate value of "_smm_max_size" & "_pga_max_size" from "pga_aggregate_target"
select a.name, a.value, a.DESCRIPTION,a.value*.20 "_pga_max_size",(a.value*.20)*.5/1024 "_smm_max_size"
from v$parameter a
where name = 'pga_aggregate_target';

I have tested above calculation with default values for 10.2.0.1 & 11.2.0.1. (I don't know the accurate calculation It may vary).
Below is the result:

Oracle version : 10.2.0.1 :
pga_aggregate_target=2447M
_smm_max_size 250570
_pga_max_size 513167360

Calculated value from Query 3:
_pga_max_size _smm_max_size
------------- -------------
513173094 250572.8

Oracle version : 11.2.0.1 :

pga_smm_max_size=419430
_smm_max_size 419430
_pga_max_size 858992640

Calculated value from Query 3:
_pga_max_size _smm_max_size
------------- -------------
858993459.2 419430.4


For most of the systems default configuration works fine.
Under some very special cases if anybody needs more memory (to avoid disk sort), below can be used at session level:

alter session set workarea_size_policy = 'MANUAL';
alter session set sort_area_size=512000;

But be careful, if too many sessions use this and also in case of parallel query, each slave/process consumes memory up to SORT_AREA_SIZE for each sort.

Below links are very useful regarding this issue:
http://christianbilien.wordpress.com/2007/05/01/two-useful-hidden-parameters-_smm_max_size-and-_pga_max-size/
http://hoopercharles.wordpress.com/2009/12/20/sort_area_size-affecting-sorts-to-disk-with-pga_aggregate_target-set/

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.