Temporarily Temporary Tablespace

Oracle uses temporary tablespace to perform any sorting required by the queries that have clauses mainly like order by, distinct, group by, union, interset and minus. Index creation and certain correlated subqueries will also use the temporary tablespace.

By default, if the temporary tablespace is not explicitly defined, oracle will use SYSTEM tablespace as the temporary tablespace.

Here is an example to create a temporary tablespace :

SQL> create temporary tablespace TEMP tempfile ‘/ora0001/oradata/temp_1.dbf’ size 500 M extent management local uniform size 256k;

A temporary tablespace can be made as default temporary tablespace for all the users in the database or can be assigned to specific users exclusively as the default one.

To make temp tablespace as default temporary tablespace for all the users in the database :
SQL> alter database default temporary tablespace temp;

To alter a user (e.g.scott) to use temp tablespace as temporary tablespace :
SQL> alter user scott temporary tablespace temp;

To specify temporary tablespace clause while creating a new user :
SQL> create user john identified by changeme
default tablespace users temporary tablespace temp1;

If you omit the TEMPORARY TABLESPACE clause, the temporary segments default to the SYSTEM tablespace. DBA can resize the temporary tablespace depending on the requirement for the temp space usage.

To resize the temp tablespace by adding new tempfile :
SQL> alter tablespace temp add tempfile ‘/ora0001/oradata/temp_2.dbf’ size 512m;

To resize an existing tempfile :
SQL> alter database tempfile ‘/ora0001/oradata/temp_1.dbf’ resize 1024M;

Sometimes, shrinking a temp tablespace may error out with
Error: ORA 3297 : file contains blocks of data beyond requested RESIZE value

In such cases, it may be advisable to create a new temp tablespace with the required temp size and drop the old one using drop tablespace command. If the old tablespace is the default temporary, reassign the new tablespace as default one before dropping the old one.

The command for drop tablespace is :
SQL> drop tablespace temp including contents and datafiles;

Normally, sort operation will be done in memory based on the SORT_AREA_SIZE parameter size in the init.ora file. Any sorts exceeding the SORT_AREA_SIZE limit will acquire temporary segments.

Since temporary tablespaces do not contain any permanent objects, these tablespaces need not be backed up. Infact, tempfile information is not recorded in control file and tempfiles can be recreated any time.

If needed, DBA can make the tempfile offline or even can drop the tempfile as shown below :
SQL> alter database tempfile ‘/ora0001/oradata/temp1.dbf’ offline;

SQL> alter database tempfile ‘/ora0001/oradata/temp1.dbf’ drop including datafiles;

Monitoring Temp Space Usage

The following is a very useful script to monitor temp space usage inside the database.

#!/usr/bin/ksh
#set up the Oracle environment.
ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE
ORACLE_HOME=/oracle/app/oracle/product/9.2.0; export ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH; export
LD_LIBRARY_PATH
tmp_output=/tmp/temp_usage.log

script_name=${0##*/}
echo “”
echo “Script: $script_name”
echo ” started on: `date`”
echo ” by user: `id`”
echo ” on machine: `uname -n`”
echo “”
echo “This script is designed to capture temp space usage information “
echo “”

${ORACLE_HOME}/bin/sqlplus -s <$ORACLE_PASSWD
whenever sqlerror exit 3 rollback
whenever oserror exit 4 rollback
SET SERVEROUTPUT ON
SET FEEDBACK OFF
insert into temp_usage
select (select instance_name from v\$instance) instance_name ,
s.sid ,
s.username,
u.tablespace,
substr(a.sql_text, 1, 1000) sql_text,
round(((u.blocks*p.value)/1024/1024),2) size_mb ,sysdate
from v\$sort_usage u,
v\$session s,
v\$sqlarea a,
v\$parameter p
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and p.name = ‘db_block_size’
/
exit;
EOF_TEMP
if [[ $? -ne 0 ]]; then
echo “*** ERROR: The ${script_name} did not complete successfully.”
exit 1
else
echo “The ${script_name} script appears to have completed successfully on `date`.”
exit 0
fi

RESUMABLE

From 9i, you can run long running operations/processes that run out of space, using RESUME feature. Basically, the transaction runs in resumable mode and if there are any space issues, the transaction will not fail. It will be suspended until the problem is fixed and will resume automatically after the space is available.

You will need to grant resumable to user, before the user can start a session in resumable mode

Examples:

Altering the session to resumable mode
Grant RESUMABLE to user1;
Alter session enable resumable timeout 3600 name ‘SPACE-ISSUE’;

Disabling resumable
Alter session disable resumable;

Monitoring the suspended sessions :
You can query dba_resumable view to monitor any suspended transactions.

SELECT * FROM dba_resumable;

Purging STATSPACK snapshots

STATSPACK provides a purge utility to remove the older snapshots. sppurge.sql script can be invoked to remove specific snapshots.
You will need to specify begin and end snapid for the script or you can modify it to select all the snapids for a snap_time date range.
You can setup a small batch job to run daily to purge the snapshots older than certain time period, to free up perfstat tablespace.

$ORACLE_HOME/bin/sqlplus -s “perfstat/perfstat” << EOF
TTITLE OFF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGES 0
SET SERVEROUTPUT ON SIZE 1000000
SET VERIFY OFF
WHENEVER SQLERROR EXIT 8
WHENEVER OSERROR EXIT 9
spool $HOME/logs/sp_purge_db1.log
@$HOME/scripts/statspack/sp_purge.sql
commit;
spool off
exit;
EOF
if [ `wc -l ${HOME}/logs/sp_purge_db1.log awk '{print $1}'` -gt 0 ]
then
/bin/mail -s “Error” email_dba < ${HOME}/logs/sp_purge_db1.log
fi

sp_purge.sql can modified to specify the required date range for the snapshots to be purged
———————-
———-
——————
column min_snap_id new_val LoSnapId
column max_snap_id new_val HiSnapId
select min(s.snap_id) min_snap_id, max(s.snap_id) max_snap_id
from stats$snapshot s
, stats$database_instance di
where s.dbid = :dbid
and di.dbid = :dbid
and s.instance_number = :inst_num
and di.instance_number = :inst_num
and di.startup_time = s.startup_time
and s.snap_time < sysdate – 60;

–
– Post warning

prompt
prompt
prompt Warning
prompt ~~~~~~~
prompt sppurge.sql deletes all snapshots ranging between the lower and
prompt upper bound Snapshot Id’s specified, for the database instance
prompt you are connected to.
prompt
prompt You may wish to export this data before continuing.
prompt

–
– Obtain snapshot ranges

prompt
prompt Specify the Lo Snap Id and Hi Snap Id range to purge
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt Using &&LoSnapId for lower bound.
prompt
prompt Using &&HiSnapId for upper bound.

variable lo_snap number;
variable hi_snap number;
begin
:lo_snap := &losnapid;
:hi_snap := &hisnapid;
end;
/

set termout off
–
– Get begin and end snapshot times – these are used to delete undostat
column btime new_value btime
column etime new_value etime
select to_char(snap_time, ‘YYYYMMDD HH24:MI:SS’) btime
from stats$snapshot b
where b.snap_id = :lo_snap
and b.dbid = :dbid
and b.instance_number = :inst_num;
select to_char(snap_time, ‘YYYYMMDD HH24:MI:SS’) etime
from stats$snapshot e
where e.snap_id = :hi_snap
and e.dbid = :dbid
and e.instance_number = :inst_num;

variable btime varchar2(25);
variable etime varchar2(25);
begin
:btime := ‘&btime’;
:etime := ‘&etime’;
end;
/
set termout on

set heading off

select ‘WARNING: LoSnapId or HiSnapId specified does not exist in STATS$SNAPSHOT’
from dual
where not exists
(select null
from stats$snapshot
where instance_number = :inst_num
and dbid = :dbid
and snap_id = :lo_snap)
or not exists
(select null
from stats$snapshot
where instance_number = :inst_num
and dbid = :dbid
and snap_id = :hi_snap);

set heading on
———————–
——————
———

READ access to everyone on Trace files

By default, non-dba users will not have access to the tracefiles generated under user_dump_dest directory. It may not be compliant with audit procedures, if we give access to these files in production. But, atleast in development, it will be very helpful for the developers, if they can see their tracefiles generated.

DBA will have to modify permissions on these tracefiles, inorder to give the developers access to read. Or, the better approach may be by setting the following undocumented parameter in the init.ora file and restarting the database instance.

_trace_files_public = true

Exporting/Importing Large Data

When exporting Large data, you can specify multiple files in the export command. This can be used from Oracle 8i version.

$> exp testuser1/testuser1 files=/oraexport/data/tab1.dmp, /oraexport/data/tab2.dmp filesize=1024m log=large_exp.log

Please note that the export files need to be specified in the same order, when you import the data.

Or else, you can write the export data to a pipe and compress the data before writing to a file.
for export :
$PIPE=/tmp/exp_ora9i.dmp
$MAXSIZE=1024m
$ExportFile=employees.dmp
$ExportLog=exp_employees.log
$mknod $PIPE p
$ORACLE_USER=testuser1/testuser1
$ParamString=”compress=n feedback=10000 tables=employees log=$ExportLog statistics=none”
$( gzip < $PIPE ) split -b $MAXSIZE – $ExportFile. & $exp $ORACLE_USER buffer=2000000 constraints=n triggers=n grants=n indexes=n file=$PIPE $ParamString

To import the data exported as above, you can uncompress the data to a pipe and use the same pipe to import.

$PIPE=/tmp/imp_ora9i.dmp
$mknod $PIPE p
$ORACLE_USER=testuser2/testuser2
$cat `echo employees.* sort` gunzip > $PIPE &
$imp $ORACLE_USER file=$PIPE ignore=y