Manage Tablespaces

Query to Monitor Tablespace.

This query could be executed through the database SYS user.

SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB, fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM (bytes) TOTAL_SPACE,
ROUND( SUM (bytes) / 1048576) TOTAL_SPACE_MB
FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM (bytes) FREE_SPACE, ROUND( SUM (bytes) / 1048576) FREE_SPACE_MB
FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+) ORDER BY ROUND(100 * (fs.free_space / df.total_space),2)


Manage Tablespaces Filled
 
Whenever a particular tablespace has been filled or it is reaching its maximum size there are two options that could be carried out to provide an uninterrupted service to users through Oracle EBS.

1. Resize the datafile of the particular tablespace.

ALTER DATABASE DATAFILE '/d01/oracle/VIS/db/apps_st/data/data01.dbf' RESIZE 2048M;

2. Add a datafile to a tablespace

First get the TS# of a particular tablespace below query.
SELECT TS# FROM V$TABLESPACE WHERE NAME=UPPER('APPS_TS_TX_DATA');  Then get the last created datafile from second query by passing TS#.
SELECT CREATION_TIME,NAME,BYTES/(1024*1024)
FROM V$DATAFILE WHERE TS# = '20'
ORDER BY CREATION_TIME DESC
Then add a new datafile to the particular tablespace.
ALTER TABLESPACE APPS_TS_TX_DATA ADD DATAFILE '/d01/oracle/VIS/db/apps_st/data/data02.dbf' SIZE 1024M;


Set Default Temporary Tablespace

Users who are not explicitly assigned a temporary tablespace use the database default temporary tablespace, which for new installations is TEMP. The default temporary tablespace for the database could be changed with the following command:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;

To determine the current default temporary tablespace for the database, run the following query through SYS user:

SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE
PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
-------------------------- ------------------------------
DEFAULT_TEMP_TABLESPACE TEMP



Viewing Space Usage for Temporary Tablespaces

The DBA_TEMP_FREE_SPACE dictionary view contains information about space usage for each temporary tablespace. The information includes the space allocated and the free space.

SELECT * from DBA_TEMP_FREE_SPACE;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
----------------------------------- --------------- --------------- ----------
TEMP 250609664 250609664 249561088

Also dba_temp_files and v$tempfile can be used to get the temporary tablespace information.

Some important queries for Temporary Tablespaces

SELECT * FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

SELECT TABLESPACE_NAME, BYTES_USED, BYTES_FREE FROM V$TEMP_SPACE_HEADER;

SELECT file_name, tablespace_name, bytes, blocks, autoextensible, increment_by FROM dba_temp_files;

SELECT * FROM DBA_TABLESPACE_GROUPS;

Add and Resize Tempfiles

ALTER TABLESPACE temp1 ADD TEMPFILE '/u01/findb/db/apps_st/data/temp01a.dbf' SIZE 4096M; 

ALTER DATABASE TEMPFILE '/u01/findb/db/apps_st/data/temp01a.dbf' RESIZE 4096M;

Check Autoextend for the datafiles.

select tablespace_name, file_name, bytes/1048576 File_Size_MB, autoextensible, increment_by
from dba_data_files order by file_id

 Get the objects consumed by SYSAUX tablespace

SELECT * FROM V$SYSAUX_OCCUPANTS


Queries for UNDO Calculation 

select
    ( select sum(bytes)/1024/1024 from dba_data_files
       where tablespace_name like 'UND%' )  allocated,
    ( select sum(bytes)/1024/1024 from dba_free_space
       where tablespace_name like 'UND%')  free,
    ( select sum(bytes)/1024/1024 from dba_undo_extents
       where tablespace_name like 'UND%') USed
from dual

SELECT (UR * (UPS * DBS)) AS "Bytes"
     FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
          (SELECT undoblks/((end_time-begin_time)*86400) AS UPS
           FROM v$undostat
           WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
          (SELECT block_size AS DBS
           FROM dba_tablespaces
           WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));


SELECT undoblks/((end_time-begin_time)*86400) "Peak Undo Block Generation" FROM v$undostat WHERE undoblks=(SELECT MAX(undoblks) FROM v$undostat);


We can calculate size of UNDO_RETENTION in one query:
SELECT
d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM
(
SELECT
SUM(a.bytes) undo_size
FROM
v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE
c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT
MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec
FROM
v$undostat
) g
WHERE
e.name = 'undo_retention'
AND f.name = 'db_block_size';


Another single query to find optimum UNDO_RETENTION period;

SELECT d.undo_size/(1024*1024) as UNDO_SIZE,
       SUBSTR(e.value,1,25) as UNDO_RETENTION,
       ROUND((d.undo_size / (to_number(f.value) *
       g.undo_block_per_sec))) as OPTIMUM_UNDO_RETENTION
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) d,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
              undo_block_per_sec
         FROM v$undostat
       ) g
WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size';

To find UNDO tablespace size according to the UNDO RETENTION period;

SELECT d.undo_size/(1024*1024) as UNDO_SIZE,
       SUBSTR(e.value,1,25) as UNDO_RETENTION,
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      as NEEDED_UNDO_SIZE
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size';
How To Size UNDO Tablespace For Automatic Undo Management (Doc ID 262066.1)
Master Note for ORA-1555 Errors (Doc ID 1307334.1)