Oracle Database tablespace Query

Check Tablespace size

scriptname : tbsn.sql

set heading on

set linesize 180

set pagesize 100

col TABLESPACE_NAME for a25

col bytes_gb for 99,999,999.999

col FREE_bytes_gb for 99,999,999.999

col ACTUAL_USED_MB for 99,999,999.999

col FILE_USED_PTC for a8

col MAXbytes_gb for 99,999,999.999

col BAL_MAX_EXT_MB for 99,999,999.999

col max_ext_used_pt for a8

col AUTO_EXTEND for a3

set feedback off

select name from v$database;

SELECT a.tablespace_name

     , round(a.bytes_gb,3) bytes_gb

     , round(b.free_bytes_gb,3) free_bytes_gb

     , round((a.bytes_gb - b.free_bytes_gb),3) actual_used_mb

     , round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) || '%' file_used_ptc

     , round(a.maxbytes_gb,3) maxbytes_gb

     , round((a.maxbytes_gb - (a.bytes_gb - b.free_bytes_gb)),3) bal_max_ext_mb

     , round(((a.bytes_gb - b.free_bytes_gb)*100/a.maxbytes_gb),3) || '%' max_ext_used_pt

     , decode(a.autoext,0,'No','Yes') auto_extend

  FROM (SELECT   dbf.tablespace_name

               , SUM (dbf.bytes)/1024/1024/1024 bytes_gb

               , SUM (decode(dbf.maxbytes,0,dbf.bytes,dbf.maxbytes))/1024/1024/1024 maxbytes_gb

               , sum (dbf.maxbytes) autoext

            FROM dba_data_files dbf

        GROUP BY tablespace_name) a

     , (SELECT   tablespace_name

               , SUM (BYTES)/1024/1024/1024 free_bytes_gb

            FROM dba_free_space

        GROUP BY tablespace_name) b

 WHERE a.tablespace_name = b.tablespace_name

-- and   round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) > 85

 order by round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) desc ;

set heading off

 SELECT a.tablespace_name

     , round(a.bytes_gb,3) bytes_gb

     , round(b.free_bytes_gb,3) free_bytes_gb

     , round((a.bytes_gb - b.free_bytes_gb),3) actual_used_mb

     , round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) || '%' file_used_ptc

     , round(a.maxbytes_gb,3) maxbytes_gb

     , round((a.maxbytes_gb - (a.bytes_gb - b.free_bytes_gb)),3) bal_max_ext_mb

     , round(((a.bytes_gb - b.free_bytes_gb)*100/a.maxbytes_gb),3) || '%' max_ext_used_pt

     , decode(a.autoext,0,'No','Yes') auto_extend

  FROM (SELECT   dbf.tablespace_name

               , SUM (dbf.bytes)/1024/1024/1024 bytes_gb

               , SUM (decode(dbf.maxbytes,0,dbf.bytes,dbf.maxbytes))/1024/1024/1024 maxbytes_gb

               , sum (dbf.maxbytes) autoext

            FROM dba_temp_files dbf

        GROUP BY tablespace_name) a

     , (SELECT   tablespace_name

               , SUM (BYTES)/1024/1024/1024 free_bytes_gb

            FROM dba_temp_files

        GROUP BY tablespace_name) b

 WHERE a.tablespace_name = b.tablespace_name

-- and   round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) > 85

 order by round(((a.bytes_gb - b.free_bytes_gb)*100/a.bytes_gb),3) desc ;

==> OLD SQL

set linesize 120
set pagesize 100
col TABLESPACE_NAME for a25
col BYTES_MB for 99,999.999
col FREE_BYTES_MB for 99,999.999
col ACTUAL_USED_MB for 99,999.999
col FILE_USED_PTC for a8
col MAXBYTES_MB for 999,999.999
col BAL_MAX_EXT_MB for 999,999.999
col max_ext_used_pt for a8
col AUTO_EXTEND for a3
SELECT a.tablespace_name
     , round(a.bytes_mb,3) bytes_mb
     , round(b.free_bytes_mb,3) free_bytes_mb
     , round((a.bytes_mb - b.free_bytes_mb),3) actual_used_mb
     , round(((a.bytes_mb - b.free_bytes_mb)*100/a.bytes_mb),3) || '%' file_used_ptc
     , round(a.maxbytes_mb,3) maxbytes_mb
     , round((a.maxbytes_mb - (a.bytes_mb - b.free_bytes_mb)),3) bal_max_ext_mb
     , round(((a.bytes_mb - b.free_bytes_mb)*100/a.maxbytes_mb),3) || '%' max_ext_used_pt
     , decode(a.autoext,0,'No','Yes') Auto_extend
  FROM (SELECT   dbf.tablespace_name
               , SUM (dbf.bytes)/1024/1024 bytes_mb
               , SUM (decode(dbf.maxbytes,0,dbf.bytes,dbf.maxbytes))/1024/1024 maxbytes_mb
               , sum (dbf.maxbytes) autoext
            FROM dba_data_files dbf
        GROUP BY tablespace_name) a
     , (SELECT   tablespace_name
               , SUM (BYTES) / 1024 / 1024 free_bytes_mb
            FROM dba_free_space
        GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name
-- and   round(((a.bytes_mb - b.free_bytes_mb)*100/a.bytes_mb),3) > 85
 order by round(((a.bytes_mb - b.free_bytes_mb)*100/a.bytes_mb),3) desc ;


Get Datafile in Table Space


set linesize 120
col file_name for a60
select file_name,bytes/1024/1024 bytes,maxbytes/1024/1024 maxbytes,AUTOEXTENSIBLE from dba_data_files where tablespace_name = '&TB';

Temporary Tablespace

set linesize 120

col tablespace_name for a10

col file_name for a60

select tablespace_name,file_name,bytes/1024/1024 bytes,maxbytes/1024/1024 maxbytes,AUTOEXTENSIBLE from dba_temp_files order by 1;

Add datafile

alter tablespace APPS_UNDOTS1 add datafile '/db02/PROD/proddata/APPS_UNDOTS2.dbf' SIZE 1024M autoextend on NEXT 16M MAXSIZE 4096M;

Resize tablespace

ALTER DATABASE DATAFILE '/u02/oracle/PROD/db/apps_st/data/sysaux05.dbf' AUTOEXTEND ON NEXT 16M MAXSIZE 4096M;