Investigating Disk Fullness

To further diagnose issues (such as running low on disk space), you need to log in directly to the remote server. Typically, you will need to log in as the owner of the Oracle software (usually the oracle OS account). When first logging in to a box, one issue that will cause a database to hang or have problems is a full mount point. The df command with the human readable -h switch assists with verifying disk fullness:

$ df -h

Any mount point that is full needs to be investigated. If the mount point that contains ORACLE_HOME becomes full, then you’ll receive errors such as this when connecting to the database:

Linux Error: 28: No space left on device

To fix issues with a full mount point, first identify files that can be either moved or removed. Generally, I start by looking for old trace files; often, there are old files that can be safely removed.

ORACLE_BASE/diag/rdbms/LOWER(<db_unique_name>)/<instance_name>/trace

SQL> select value from v$diag_info where name = 'Diag Trace';

alert_<ORACLE_SID>.log

$ cd $ORACLE_BASE
$ find . -name alert_<ORACLE_SID>.log

function bdump {
  if [ "$ORACLE_SID" = "O12C" ]; then
    cd /orahome/app/oracle/diag/rdbms/o12c/O12C/trace
  elif [ "$ORACLE_SID" = "O11R2" ]; then
    cd /orahome/app/oracle/diag/rdbms/o11r2/O11R2/trace
  fi
}

$ ls -altr *.tr*

$ ls -altr <file_name>

$ rm <file_name>

$ find . -type f -mtime +2 -name "*.tr*"

$ find . -type f -mtime +2 -name "*.tr*" | xargs rm

$ cp listener.log /u01/backups

$ cat /dev/null > listener.log

Inspecting the Alert Log

When dealing with database issues, the alert.log file should be one of the first files you check for relevant error messages. You can use either OS tools or the ADRCI utility to view the alert.log file and corresponding trace files.

$ tail -50 alert_<SID>.log

$ tail -f alert_<SID>.log

$ vi alert_<SID>.log

#-----------------------------------------------------------#
# view alert log
  function valert {
  if [ "$ORACLE_SID" = "O12C" ]; then
    view /orahome/app/oracle/diag/rdbms/o12c/O12C/trace/alert_O12C.log
  elif [ "$ORACLE_SID" = "O11R2" ]; then
    view /orahome/app/oracle/diag/rdbms/o11r2/O11R2/trace/alert_O11R2.log
  fi
  } # valert
#-----------------------------------------------------------#

$ valert

Errors in file /orahome/app/oracle/diag/rdbms/o12c/O12C/trace/O12C_ora_5665.trc

$ adrci

adrci>

adrci> show alert

adrci> set editor emacs

adrci> show alert -tail 50

DIA-48449: Tail alert can only apply to single ADR home

adrci> show homes

diag/rdbms/o12c/O12C
diag/rdbms/o12cp/O12CP

adrci> set homepath  diag/rdbms/o12c/O12C

adrci> show alert -tail -f

adrci> show alert -p "MESSAGE_TEXT LIKE '%ORA-27037%'"

Identifying System Bottlenecks

Whenever there are application performance issues or availability problems, seemingly (from the DBA’s perspective), the first question asked is, “What’s wrong with the database?” Regardless of the source of the problem, the onus is often on the DBA to establish whether the database is behaving well. I usually approach this issue by determining which system-wide resources are being consumed. There are two Linux/Unix OS tools that are particularly useful for displaying system-wide resource usage:

• vmstat
• top

The vmstat (virtual memory statistics) tool is intended to help you quickly identify bottlenecks on your server. The top utility provides a dynamic, real-time view of system resource usage. These two utilities are discussed in the next two sections.

$ vmstat
procs  -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache    si   so    bi    bo   in    cs us sy id wa
14  0  52340  25272   3068 1662704    0    0    63    76    9    31 15  1 84  0

$ vmstat <interval in seconds> <number of intervals>

$ vmstat 2 10

$ vmstat 5 60 > vmout.perf

$ watch –n 5 –d vmstat
Every 5.0s: vmstat                                    Thu Aug  9 13:27:57 2007
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache  si   so    bi    bo   in    cs us sy id wa
 0  0    144  15900  64620 1655100   0    0     1     7   16     4  0  0 99  0

$ vmstat –S m

$ top

top - 13:34:32 up 19 min,  2 users,  load average: 0.05, 0.16, 0.24
Tasks: 176 total,   1 running, 175 sleeping,   0 stopped,   0 zombie
Cpu(s):  2.0%us,  0.7%sy,  0.0%ni, 91.6%id,  5.7%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:    787028k total,   748744k used,    38284k free,     1836k buffers
Swap:  1605624k total,    31896k used,  1573728k free,   377668k cached
  
PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
4683 root      20   0  279m  14m 7268 S  2.0  1.9   0:01.61 gnome-terminal
3826 root      20   0  218m 9944 4200 S  1.7  1.3   0:02.80 Xorg
3592 oracle    -2   0  601m  18m  15m S  0.7  2.4   0:09.36 ora_vktm_o12c

$ top –b > tophat.out

$ top –u oracle –d 5 –n 25

Mapping an Operating System Process to an SQL Statement

When identifying OS processes, it’s useful to view which processes are consuming the greatest amount of CPU. If the resource hog is a database process, it’s also useful to map the OS process to a database job or query. To determine the ID of the processes consuming the most CPU resources, use a command such as ps, like so:

$ ps -e -o pcpu,pid,user,tty,args | sort -n -k 1 -r | head

Here is a snippet of the output:

14.6 24875 oracle   ?        oracleo12c (DESCRIPTION=(LOCAL=YES)(ADDRESS=...
 0.8 21613 oracle   ?        ora_vktm_o12c
 0.1 21679 oracle   ?        ora_mmon_o12c

From the output, you can see that the OS session 24875 is the top consumer of CPU resources. The output also indicates that the process is associated with the o12c database. With that information in hand, log in to the appropriate database, and use the following SQL statement to determine what type of program is associated with the OS process 24875:

select
  'USERNAME : ' || s.username|| chr(10) ||
  'OSUSER   : ' || s.osuser  || chr(10) ||
  'PROGRAM  : ' || s.program || chr(10) ||
  'SPID     : ' || p.spid    || chr(10) ||
  'SID      : ' || s.sid     || chr(10) ||
  'SERIAL#  : ' || s.serial# || chr(10) ||
  'MACHINE  : ' || s.machine || chr(10) ||
  'TERMINAL : ' || s.terminal
from v$session s,
     v$process p
where s.paddr = p.addr
and   p.spid  = &PID_FROM_OS;

When you run the command, SQL*Plus will prompt you for the value to use in place of &PID_FROM_OS. In this example, you’ll enter 24875. Here is the relevant output:

USERNAME : MV_MAINT
OSUSER   : oracle
PROGRAM  : sqlplus@speed2 (TNS V1-V3)
SPID     : 24875
SID      : 111
SERIAL#  : 899
MACHINE  : speed2
TERMINAL : pts/4

In this output the PROGRAM value indicates that an SQL*Plus session is the program consuming the inordinate amount of resources on the server. Next, run the following query to display the SQL statement associated with the OS PID (in this example, the server process identifier [SPID] is 24875):

select
  'USERNAME : ' || s.username || chr(10) ||
  'OSUSER   : ' || s.osuser   || chr(10) ||
  'PROGRAM  : ' || s.program  || chr(10) ||
  'SPID     : ' || p.spid     || chr(10) ||
  'SID      : ' || s.sid      || chr(10) ||
  'SERIAL#  : ' || s.serial#  || chr(10) ||
  'MACHINE  : ' || s.machine  || chr(10) ||
  'TERMINAL : ' || s.terminal || chr(10) ||
  'SQL TEXT : ' || sa.sql_text
from v$process p,
v$session s,
v$sqlarea sa
where p.addr = s.paddr
and s.username is not null
and s.sql_address = sa.address(+)
and s.sql_hash_value = sa.hash_value(+)
and p.spid= &PID_FROM_OS;

The results show the resource-consuming SQL as part of the output, in the S QL TEXT column. Here is a snippet of the output:

USERNAME : MV_MAINT
OSUSER   : oracle
PROGRAM  : sqlplus@speed2 (TNS V1-V3)
SPID     : 24875
SID      : 111
SERIAL#  : 899
MACHINE  : speed2
TERMINAL : pts/4
SQL TEXT : select a.table_name from dba_tables a,dba_indexes b,dba_objects c...

When you run multiple databases on one server and are experiencing server performance issues, it can sometimes be difficult to pinpoint which database and associated process are causing the problems. In these situations, you have to use an OS tool to identify the top resource-consuming sessions on the system.

In a Linux/Unix environment, you can use utilities such as ps, top, or vmstat to identify top-consuming OS processes. The p s utility is handy because it lets you identify processes consuming the most CPU or memory. The previous ps command identified the top-consuming CPU processes. Here, it’s used to identify the top Oracle memory–using processes:

$ ps -e -o pmem,pid,user,tty,args | grep -i oracle | sort -n -k 1 -r | head

Once you have identified a top-consuming process associated with a database, you can query the data dictionary views, based on the SPID, to identify what the database process is executing.

Monitoring Real-Time SQL Execution Statistics

If you’re using Oracle Database 11g or higher, you can use the following query to select from the V$SQL_MONITOR to monitor the near real-time resource consumption of SQL queries:

select * from (
select a.sid session_id, a.sql_id
,a.status
,a.cpu_time/1000000 cpu_sec
,a.buffer_gets, a.disk_reads
,b.sql_text sql_text
from v$sql_monitor a
    ,v$sql b
where a.sql_id = b.sql_id
order by a.cpu_time desc)
where rownum <=20;

The output for this query doesn’t fit easily onto a page. Here is a subset of the output:

SESSION_ID SQL_ID        STATUS      CPU_SEC BUFFER_GETS  DISK_READS SQL_TEXT
---------- ------------- --------- ---------- ----------- ---------- ---------------
       139 d07nngmx93rq7 DONE          331.88        5708       3490 select count(*)
       130 9dtu8zn9yy4uc EXECUTING      11.55        5710        248 select task_name

In the query an inline view is used to first retrieve all records and organize them by CPU_TIME, in descending order. The outer query then limits the result set to the top 20 rows, using the R OWNUM pseudocolumn. You can modify the previous query to order the results by the statistic of your choice or to display only the queries that are currently executing. For example, the next SQL statement monitors currently executing queries, ordered by the number of disk reads:

select * from (
select a.sid session_id, a.sql_id, a.status
,a.cpu_time/1000000 cpu_sec
,a.buffer_gets, a.disk_reads
,substr(b.sql_text,1,35) sql_text
from v$sql_monitor a
    ,v$sql b
where a.sql_id = b.sql_id
and   a.status='EXECUTING'
order by a.disk_reads desc)
where rownum <=20;

The statistics in V$SQL_MONITOR are updated every second, so you can view resource consumption as it changes. These statistics are gathered by default if an SQL statement runs in parallel or consumes more than 5 seconds of CPU or I/O time.

The V$SQL_MONITOR view includes a subset of statistics contained in the V$SQL, V$SQLAREA, and V$SQLSTATS views. The V$SQL_MONITOR view displays real-time statistics for each execution of a resource-intensive SQL statement, whereas V$SQL, V$SQLAREA, and V$SQLSTATS contain cumulative sets of statistics resulting from several executions of an SQL statement.

Once the SQL statement execution ends, the runtime statistics are not immediately flushed from V $SQL_MONITOR. Depending on activity in your database, the statistics can be available for some time. If you have a very active database, however, the statistics can potentially be flushed soon after the query finishes.

You can also query views such as V$SQLSTATS to determine which SQL statements are consuming an inordinate amount of resources. For example, use the following query to identify the ten most resource-intensive queries, based on CPU time:

select * from(
select s.sid, s.username, s.sql_id
,sa.elapsed_time/1000000, sa.cpu_time/1000000
,sa.buffer_gets, sa.sql_text
from v$sqlarea sa
    ,v$session s
where s.sql_hash_value = sa.hash_value
and   s.sql_address    = sa.address
and   s.username is not null
order by sa.cpu_time desc)
where rownum <= 10;

In the prior query an inline view is used to first retrieve all records and sort the output by CPU_TIME, in descending order. The outer query then limits the result set to the top ten rows, using the R OWNUM pseudocolumn. The query can be easily modified to sort by a column other than CPU_TIME. For instance, if you want to report resource usage by BUFFER_GETS, simply substitute BUFFER_GETS for CPU_TIME in the ORDER BY clause. The CPU_TIME column is calculated in microseconds; to convert it to seconds, it is divided by 1,000,000.

Running Oracle Diagnostic Utilities

Oracle provides several utilities for diagnosing database performance issues:

• Automatic workload repository (AWR)
• Automatic database diagnostic monitor (ADDM)
• Active session history (ASH)
• Statspack

AWR, ADDM, and ASH were introduced many years ago, in Oracle Database 10g. These tools provide advanced reporting capabilities that allow you to troubleshoot and resolve performance issues. These new utilities require an extra license from Oracle. The older Statspack utility is free and requires no license.

All these tools rely heavily on the underlying V$ dynamic performance views. Oracle maintains a vast collection of these views, which track and accumulate metrics of database performance. For example, if you run the following query, you’ll notice that for Oracle Database 12c, there are approximately 700 V$ views:

SQL> select count(*) from dictionary where table_name like 'V$%';
  
  COUNT(*)
----------
       714

The Oracle performance utilities rely on periodic snapshots gathered from these internal performance views. Two of the most useful views, with regard to performance statistics, are the V $SYSSTAT and V $SESSTAT views. The V$SYSSTAT view offers more than 400 types of database statistics. This V$SYSSTAT view contains information about the entire database, whereas the V$SESSTAT view contains statistics on individual sessions. A few of the values in the V$SYSSTAT and V$SESSTAT views represent the current usage of the resource. These values are

• opened cursors current
• logins current
• session cursor cache current
• work area memory allocated

The rest of the values are cumulative. The values in V $SYSSTAT are cumulative for the entire database, from the time the instance was started. The values in V $SESSTAT are cumulative per session, from the time the session was started. Some of the more important performance-related cumulative values are

• CPU used
• consistent gets
• physical reads
• physical writes

For the cumulative statistics the way to measure periodic usage is to note the value of a statistic at a starting point, then note the value again at a later point in time and capture the delta. This is the approach used by the Oracle performance utilities, such as AWR and Statspack. Periodically, Oracle will take snapshots of the dynamic wait interface views and store them in a repository.

The following sections detail how to access AWR, ADDM, ASH, and Statspack via the SQL command line.

SQL> @?/rdbms/admin/awrrpt

SQL ordered by CPU Time                  DB/Inst: O12C/o12c  Snaps: 1668-1669
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> %Total - CPU Time      as a percentage of Total DB CPU
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for   3.0E+03% of Total CPU Time (s):               0
-> Captured PL/SQL account for  550.9% of Total CPU Time (s):               0
  
    CPU                   CPU per           Elapsed
  Time (s)  Executions    Exec (s) %Total   Time (s)   %CPU    %IO    SQL Id
---------- ------------ ---------- ------ ---------- ------ ------ -------------
       3.2            1       3.24  930.5       10.5   30.9   74.9 93jktd5vtxb98

SQL> @?/rdbms/admin/awrsqrpt.sql

SQL> @?/rdbms/admin/addmrpt

FINDING 2: 29% impact (65043 seconds)
-------------------------------------
SQL statements consuming significant database time were found.
  
   RECOMMENDATION 1: SQL Tuning, 6.7% benefit (14843 seconds)
      ACTION: Investigate the SQL statement with SQL_ID "46cc3t7ym5sx0" for

SQL> @?/rdbms/admin/ashrpt

Top SQL with Top Events           DB/Inst: O12C/o12c  (Jan 30 14:49 to 15:14)
  
                                                        Sampled #
                 SQL ID             Planhash        of Executions     % Activity
----------------------- -------------------- -------------------- --------------
Event                          % Event Top Row Source                    % RwSrc
------------------------------ ------- --------------------------------- -------
          dx5auh1xb98k5           1677482778                    1          46.57
CPU + Wait for CPU               46.57 HASH JOIN                           23.53

SQL> @?/rdbms/admin/spcreate.sql

SQL> @ ?/rdbms/admin/spauto.sql

SQL> @?/rdbms/admin/spreport.sql

SQL ordered by CPU  DB/Inst: O12C/o12c  Snaps: 30-31
-> Total DB CPU (s):           1,432
-> Captured SQL accounts for  100.5% of Total DB CPU
-> SQL reported below exceeded  1.0% of Total DB CPU
  
    CPU                  CPU per             Elapsd                     Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
   1430.41            1    1430.41   99.9    1432.49             482  690392559
Module: SQL*Plus
select a.table_name from my_tables

Determining if Undo Is Correctly Sized

Suppose you have a long-running SQL statement that is throwing an ORA-01555: snapshot too old error, and you want to determine if adding space to the undo tablespace might help alleviate the issue. Run this next query to identify potential issues with your undo tablespace. The query checks for issues that have occurred within the last day:

select to_char(begin_time,'MM-DD-YYYY HH24:MI') begin_time
,ssolderrcnt    ORA_01555_cnt, nospaceerrcnt  no_space_cnt
,txncount       max_num_txns, maxquerylen    max_query_len
,expiredblks    blck_in_expired
from v$undostat
where begin_time > sysdate - 1
order by begin_time;

Here is some sample output. Part of the output has been omitted to fit this on the page:

BEGIN_TIME       ORA_01555_CNT NO_SPACE_CNT MAX_NUM_TXNS BLCK_IN_EXPIRED
---------------- ------------- ------------ ------------ ---------------
01-31-2013 08:21             0            0           51               0
01-31-2013 08:31             0            0            0               0
01-31-2013 12:11             0            0          629             256

The ORA_01555_CNT column indicates the number of times your database has encountered the ORA-01555: snapshot too old error. If this column reports a nonzero value, you need to do one or more of the following tasks:

• Ensure that code does not contain COMMIT statements within cursor loops.
• Tune the SQL statement throwing the error so that it runs faster.
• Ensure that you have good statistics (so that your SQL runs efficiently).
• Increase the UNDO_RETENTION initialization parameter.

The NO_SPACE_CNT column displays the number of times space was requested in the undo tablespace. In this example there were no such requests. If the NO_SPACE_CNT is reporting a nonzero value, however, you may need to add more space to your undo tablespace.

A maximum of 4 days’ worth of information is stored in the V$UNDOSTAT view. The statistics are gathered every 10 minutes, for a maximum of 576 rows in the table. If you’ve stopped and started your database within the last 4 days, this view will only contain information from the time you last started your database.

Another way to get advice on the undo tablespace sizing is to use the Oracle Undo Advisor, which you can invoke by querying the PL/SQL D BMS_UNDO_ADV package from a SELECT statement. The following query displays the current undo size and the recommended size for an undo retention setting of 900 seconds:

select
  sum(bytes)/1024/1024                  cur_mb_size
 ,dbms_undo_adv.required_undo_size(900) req_mb_size
from dba_data_files
where tablespace_name =
  (select
    value
   from v$parameter
   where name = 'undo tablespace'),

Here is some sample output:

CUR_MB_SIZE REQ_MB_SIZE
----------- -----------
      36864       20897

The output shows that the undo tablespace currently has 36.8GB allocated to it. In the prior query, you used 900 seconds as the amount of time to retain information in the undo tablespace. To retain undo information for 900 seconds, the Oracle Undo Advisor estimates that the undo tablespace should be 20.8GB. In this example the undo tablespace is sized adequately. If it were not sized adequately, you would have to either add space to an existing data file or add a data file to the undo tablespace.

Here is a slightly more complex example of using the Oracle Undo Advisor to find the required size of the undo tablespace. This example uses PL/SQL to display information about potential issues and recommendations for fixing the problem:

SET SERVEROUT ON SIZE 1000000
DECLARE
 pro    VARCHAR2(200);
 rec    VARCHAR2(200);
 rtn    VARCHAR2(200);
 ret    NUMBER;
 utb    NUMBER;
 retval NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE(DBMS_UNDO_ADV.UNDO_ADVISOR(1));
  DBMS_OUTPUT.PUT_LINE('Required Undo Size (megabytes): ' || DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE (900));
  retval := DBMS_UNDO_ADV.UNDO_HEALTH(pro, rec, rtn, ret, utb);
  DBMS_OUTPUT.PUT_LINE('Problem:   ' || pro);
  DBMS_OUTPUT.PUT_LINE('Advice:    ' || rec);
  DBMS_OUTPUT.PUT_LINE('Rational:  ' || rtn);
  DBMS_OUTPUT.PUT_LINE('Retention: ' || TO_CHAR(ret));
  DBMS_OUTPUT.PUT_LINE('UTBSize:   ' || TO_CHAR(utb));
END;
/

If no issues are found, a 0 will be returned for the retention size. Here is some sample output:

Finding 1:The undo tablespace is OK.
Required Undo Size (megabytes): 64
Problem:   No problem found
Advice:
Rational:
Retention: 0
UTBSize:   0

Viewing SQL That Is Consuming Undo Space

Sometimes, a piece of code does not commit properly, which results in large amounts of space being allocated in the undo tablespace and never being released. Sooner or later, you’ll get the ORA-30036 error, indicating that the tablespace can’t extend. Usually, the first time a space-related error is thrown, I simply increase the size of one of the data files associated with the undo tablespace.

However, if an SQL statement continues to run and fills up the newly added space, then the issue is probably with a poorly written application. For example, a developer may not have appropriate commit statements in the code.

In these situations it’s helpful to identify which users are consuming space in the undo tablespace. Run this query to report on basic information regarding space allocated on a per-user basis:

select s.sid, s.serial#, s.osuser, s.logon_time
,s.status, s.machine
,t.used_ublk, t.used_ublk*16384/1024/1024 undo_usage_mb
from v$session     s
    ,v$transaction t
where t.addr = s.taddr;

If you want to view the SQL statement associated with a user consuming undo space, then join to V$SQL, as shown:

select s.sid, s.serial#, s.osuser, s.logon_time, s.status
,s.machine, t.used_ublk
,t.used_ublk*16384/1024/1024 undo_usage_mb
,q.sql_text
from v$session     s
    ,v$transaction t
    ,v$sql         q
where t.addr = s.taddr
and s.sql_id = q.sql_id;

If you need more information, such as the name and status of the rollback segment, run a query that joins to the V$ROLLNAME and V$ROLLSTAT views, like so:

select s.sid, s.serial#, s.username, s.program
,r.name undo_name, rs.status
,rs.rssize/1024/1024 redo_size_mb
,rs.extents
from v$session     s
    ,v$transaction t
    ,v$rollname    r
    ,v$rollstat    rs
where s.taddr = t.addr
and t.xidusn  = r.usn
and r.usn     = rs.usn;

The prior queries allow you to pinpoint which users are responsible for space allocated within the undo tablespace. This can be especially useful when there is code that is not committing at appropriate times and that is excessively consuming undo space.

Determining if Temporary Tablespace Is Sized Correctly

The temporary tablespace is used as a sorting area on disk when a process has consumed the available memory and needs more space. Operations that require a sorting area include

• index creation
• SQL sorting operations
• temporary tables and indexes
• temporary LOBs
• temporary B-trees

There is no exact formula for determining if your temporary tablespace is sized correctly. It depends on the number and types of queries, index build operations, and parallel operations and on size of your memory sort space (PGA). You’ll have to monitor your temporary tablespace while there is a load on your database to establish its usage patterns. If you are using Oracle Database 11g or higher, run the following query to show both the allocated and free space within the temporary tablespace:

select tablespace_name
,tablespace_size/1024/1024 mb_size
,allocated_space/1024/1024 mb_alloc
,free_space/1024/1024      mb_free
from dba_temp_free_space;

Here is some sample output:

TABLESPACE_NAME    MB_SIZE   MB_ALLOC    MB_FREE
--------------- ---------- ---------- ----------
TEMP                   200        200        170

If the FREE_SPACE (MB_FREE) value drops to near 0, there are SQL operations in your database consuming most of the available space. The FREE_SPACE (MB_FREE) column is the total free space available, including space currently allocated and available for reuse.

If you are using an Oracle Database 10g database, run this query to view the space being used in your temporary tablespace:

select tablespace_name
,sum(bytes_used)/1024/1024 mb_used
from v$temp_extent_pool
group by tablespace_name;

Here is some sample output:

TABLESPACE_NAME    MB_USED
--------------- ----------
TEMP                   120

If the amount used is getting near your current allocated amount, you may need to allocate more space to the temporary tablespace data files. Run the following query to view the temporary data file names and allocated sizes:

SQL> select name, bytes/1024/1024 mb_alloc from v$tempfile;

Here is some typical output:

NAME                                       MB_ALLOC
---------------------------------------- ----------
/u01/dbfile/o12c/temp01.dbf                     400
/u01/dbfile/o12c/temp02.dbf                     100
/u01/dbfile/o12c/temp03.dbf                     100

When first creating a database, if I have no idea as to “correct” size of the temporary tablespace, I’ll usually size this tablespace at approximately 2GB. If I’m building a data warehouse–type database, I might size the temporary tablespace at approximately 20GB. You’ll have to monitor your temporary tablespace with the appropriate SQL and adjust the size as necessary.

Viewing SQL That Is Consuming Temporary Space

When Oracle throws the ORA-01652: unable to extend temp error, this may be an indication that your temporary tablespace is too small. However, Oracle may throw that error if it runs out of space because of a one-time event, such as a large index build. You’ll have to decide whether a one-time index build or a query that consumes large amounts of sort space in the temporary tablespace warrants adding space.

To view the space a session is using in the temporary tablespace, run this query:

SELECT s.sid, s.serial#, s.username
,p.spid, s.module, p.program
,SUM(su.blocks) * tbsp.block_size/1024/1024 mb_used
,su.tablespace
FROM v$sort_usage    su
    ,v$session       s
    ,dba_tablespaces tbsp
    ,v$process       p
WHERE su.session_addr = s.saddr
AND   su.tablespace   = tbsp.tablespace_name
AND   s.paddr         = p.addr
GROUP BY
 s.sid, s.serial#, s.username, s.osuser, p.spid, s.module,
 p.program, tbsp.block_size, su.tablespace
ORDER BY s.sid;

If you determine that you need to add space, you can either resize an existing data file or add a new one. To resize a temporary tablespace data file, use the ALTER DATABASE TEMPFILE...RESIZE statement. The following command resizes a temporary data file to 12GB:

SQL> alter database tempfile '/u01/dbfile/o12c/temp02.dbf' resize 12g;

You can add a data file to a temporary tablespace, as follows:

SQL> alter tablespace temp add tempfile '/u02/dbfile/o12c/temp04.dbf' size 2g;