Frequently used Tablespace Queries

Below are some of the simple but frequently used database tablespace related queries used by DBAs:

Queries Related to Temporary Tablespace

Check Temporary tablespace temp files size

set lines 132
col FILE_NAME format a60
select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024 from dba_temp_files;

Add new Temp file to TEMP tablepspace

ALTER TABLESPACE TEMP1 ADD TEMPFILE '/fh18/CGT3DA/oradata/temp1_03.dbf' SIZE 16384M;

Check what is using TEMP tablespace

col osuser format a8
col username format a8
col tablespace format a8
col PROGRAM format a26
set lines 132
SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM v$session a
       , v$sort_usage b
       , v$process c
       , v$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
ORDER BY b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

TO SWITCH TEMP TABLESPACE

create alternate temporary tablespace

CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '+DB_DATA2' SIZE 16384M;

Link Database to new Temporary tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

Drop Old Tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

CONCEPT OF TEMPORARY TABLESPACE GROUP

A temporary tablespace group consists of only temporary tablespace, and has the following properties:

It contains one or more temporary tablespaces.

It contains only temporary tablespace.

It is not explicitly created. It is created implicitly when the first temporary tablespace is assigned to it, and is deleted when the last temporary tablespace is removed from the group.

Temporary tablespace group has the following benefits:

It allows multiple default temporary tablespaces to be specified at the database level.

It allows the user to use multiple temporary tablespaces in different sessions at the same time.

It allows a single SQL operation to use multiple temporary tablespaces for sorting.

Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.

SQL> select tablespace_name, group_name from DBA_TABLESPACE_GROUPS;
 
TABLESPACE_NAME                GROUP_NAME
------------------------------ ------------------------------
TEMP1                          TEMP
TEMP2                          TEMP

Queries Related to Normal database Tablespaces

Check Normal tablespace data files size

set lines 132
col FILE_NAME format a60
select FILE_NAME,BYTES/1024/1024,MAXBYTES/1024/1024,AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='&TS_NAME';

Extend DB file

Example:

alter database datafile '+DB_DATA/sysaux02.dbf' resize 2048M;

Add new DB file to tablespace

Example:

ALTER TABLESPACE APPS_TS_ARCHIVE ADD DATAFILE '+DB_DATA/a_archive02.dbf' SIZE 4096m;
ALTER TABLESPACE XXDBA_DATA ADD DATAFILE '/oradb/u03/oracle/dev2/db/apps_st/data/xxdba_data03.dbf' SIZE 8192m AUTOEXTEND ON MAXSIZE 32760M;

GET TABLESPACE CREATION SCRIPT FOR EXISTING TABLESPACES

select dbms_metadata.get_ddl('TABLESPACE','TEMP_OBIEE') from dual;

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Frequently used Tablespace Queries

Queries Related to Temporary Tablespace

Queries Related to Normal database Tablespaces

Related posts: