Using the Temporary Tablespace

Oracle try to use sort and hash operation in memory(PGA). Suppose if operation is too  long, it  use temporary tablespace. Even single  transaction try to use full temporary tablespace.  Once temp tablespace is full other session ‘s SQL also try to use  temp tablespace.

So we can get ORA error ORA-01652: unable  to extend temp  segment error.

     1. New Sessions may not be able to connect.

     2. Queries can sometimes hang.

     3. Users may  not able to issue new query.

If temp tablespace is full, transaction won’t   complete. We can find error into alert log file also.

Use below query to fine out which SQL statment is using Temporay Tablespace.

 select s.sid || ',' || s.serial# sid_serial, s.username,

o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace,

o.sqladdr address, h.hash_value, h.sql_text

from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t

where o.session_addr = s.saddr and o.sqladdr = h.address (+) and o.tablespace = t.tablespace_name  order by s.sid;

Below query find out which session are using space in the memory.

select s.sid || ',' || s.serial# sid_serial, s.username, s.osuser, p.spid,

s.module,s.program,sum (o.blocks) * t.block_size / 1024 / 1024 mb_used, o.tablespace,count(*) sorts from v$sort_usage o, v$session s, dba_tablespaces t, v$process p where o.session_addr = s.saddr

and s.paddr = p.addr and o.tablespace = t.tablespace_name group by s.sid, s.serial#, s.username, s.osuser, p.spid, s.module,s.program, t.block_size, o.tablespace  order by sid_serial;

Find Blocks in the Buffer cache

Use below  below query to find how many blocks  for each segment are currently in the buffer Cache.

RAC Database:


select o.owner, o.object_name,v.inst_id, count(*) number_of_blocks
from dba_objects o, gv$bh v
where o.data_object_id = v.objd
and o.owner !='SYS'
group by o.owner,o.object_name,v.inst_id
order by o.object_name,v.inst_id,count(*);

Non-RAC Database:


select o.owner, o.object_name, count(*) number_of_blocks
from dba_objects o, v$bh v
where o.data_object_id = v.objd
and o.owner !='SYS'
group by o.owner,o.object_name
order by o.object_name,count(*);

Excluding Tablespace from RMAN Backup

You have a tablespace with test data that you don't need to backup. You can exclude such tablespaces from a whole backup of the database.

First find out any tablespace are already configured to be excluded from backups:

RMAN> show exclude;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TEST are:
RMAN configuration has no stored or default parameters

Use following command to exclude tablespace from whole database.

RMAN> configure exclude  for tablespace users;

Tablespace USERS will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored

To make confirm tablespace exclude in your RMAN backup

RMAN>  show exclude;

RMAN configuration parameters for database with db_unique_name TEST are:
CONFIGURE EXCLUDE FOR TABLESPACE 'USERS';

Now you want to include a previously excluded tablespace in your backup by using the following command.

RMAN> configure exclude  for tablespace users clear;


Tablespace USERS will be included in future whole database backups
old RMAN configuration parameters are successfully deleted

RMAN> show exclude;
RMAN configuration parameters for database with db_unique_name TEST are:
RMAN configuration has no stored or default parameters



You want include all tablespace even you configured exclude tablespace. You use the 'noexclude' option as part of a backup database command.

RMAN>backup database noexclude;

Find Bind value in Oracle

Here are below  query that help you to find the value of bind variable.

1. From V$sql_bind_capture:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING, WAS_CAPTURED, LAST_CAPTURED
FROM v$sql_bind_capture WHERE sql_id='8v176wjqww15y';

2. From RAC:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING,WAS_CAPTURED, LAST_CAPTURED
FROM gv$sql_bind_capture WHERE sql_id='8v176wjqww15y' and inst_id=1;

3. From DBA_HIST_SQLBIND:

SELECT NAME,POSITION,DATATYPE_STRING,VALUE_STRING,WAS_CAPTURED, LAST_CAPTURED
FROM DBA_HIST_SQLBIND WHERE SQL_ID='8v176wjqww15y' and SNAP_ID='12345';