Tablespace Scripts

When a tablespace is getting filled up, action need to be taken (Preference-wise)

1. Resize the datafile.
2. If AUTOEXTEND ON,then resize the maxsize of the datafile.
3. Add a new datafile to the tablespace.

Note:-
Check the availability of the free space on the disk at OS level.
$df -h (Linux,AIX)
$df -gt

oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon

Set the environment for the database
------------------------------------
uname

cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab

export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin

Database Details
----------------
sqlplus "/as sysdba"

set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME, INSTANCE_NAME, HOST_NAME, DATABASE_ROLE,
OPEN_MODE, version DB_VERSION, LOGINS, 
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"
from v$database,gv$instance;

Tablespace Utilization Script
-----------------------------
set pages 9999 lines 300

col tablespace_name format a30
col TABLESPACE_NAME heading "Tablespace|Name"
col Allocated_size heading "Allocated|Size(MB)" form 99999999.99
col Current_size heading "Current|Size(MB)" form 99999999.99
col Used_size heading "Used|Size(MB)" form 99999999.99
col Available_size heading "Available|Size(MB)" form 99999999.99
col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99

select a.tablespace_name
        ,a.alloc_size/1024/1024 Allocated_size
        ,a.cur_size/1024/1024 Current_Size
        ,(u.used+a.file_count*65536)/1024/1024 Used_size
        ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024 Available_size
        ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
from     dba_tablespaces t
        ,(select t1.tablespace_name
        ,nvl(sum(s.bytes),0) used
        from  dba_segments s
        ,dba_tablespaces t1
         where t1.tablespace_name=s.tablespace_name(+)
         group by t1.tablespace_name) u
        ,(select d.tablespace_name
        ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
        ,sum(d.bytes) cur_size
        ,count(*) file_count
        from dba_data_files d
        group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and t.tablespace_name=a.tablespace_name
order by t.tablespace_name
/

Tablespace Utilization Script (Detailed)
-----------------------------
set pages 9999 lines 300

set heading off;
set feedback off;
col tablespace_name for a30
select 'Database Name : ' || a.name , 'Host : ' || b.host_name from v$database a,gv$instance b;
select ' '  from dual;
set heading on;
set feedback on;

break on report
compute sum of "TOTAL SIZE in MB" on report
compute sum of "ALLOCATED SIZE in MB" on report
compute sum of "USED %" on report
compute sum of "FREE %" on report
compute sum of "SPACE_NEED_ON_OS_FOR_MAXSIZE" on report

select a.tablespace_name,
round(nvl(a.total,0)) "TOTAL SIZE in MB",
round(nvl(a.asize,0)) "ALLOCATED SIZE in MB",
round(nvl(a.asize-nvl(f.free,0),0)) "USED",
round(nvl(a.total-a.asize+f.free,0)) "FREE",
nvl(f.maxfree,0) "MAX_FREE",
round(((a.total-nvl(a.total-a.asize+f.free,0))/a.total)*100) "USED %",
round((nvl(a.total-a.asize+f.free,0)/a.total)*100) "FREE %",
round(a.total - a.asize) "SPACE_NEED_ON_OS_FOR_MAXSIZE"
from (select tablespace_name, sum(bytes)/1024/1024 "ASIZE",sum(case when maxbytes > bytes
then maxbytes else bytes end)/1024/1024 total from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free,round(max(bytes)/1024/1024) maxfree
from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by round((nvl(a.total-a.asize+f.free,0)/a.total)*100)
/

Datafiles of a particular TableSpace
-------------------------------------
set pages 9999 lines 300

col tablespace_name for a30
col file_name for a80

select tablespace_name, file_name, bytes/1024/1024 SIZE_MB, autoextensible,
maxbytes/1024/1024 MAXSIZE_MB
from dba_data_files
where tablespace_name='&tablespace_name' order by 1,2;

Database file sizes and locations
---------------------------------
set pages 9999 lines 300

set VERIFY OFF FEEDBACK OFF

COLUMN file_name         FORMAT A51        HEADING 'File Name'
COLUMN tablespace_name   FORMAT A15        HEADING 'Tablespace'
COLUMN meg               FORMAT 99,999.90  HEADING 'Megabytes'
COLUMN status            FORMAT A10        HEADING 'Status'
COLUMN autoextensible    FORMAT A3         HEADING 'Auto Extend'
COLUMN maxmeg            FORMAT 99,999     HEADING 'Max|Megabytes'
COLUMN Increment_by      FORMAT 9,999      HEADING 'Inc|By'

SPOOL ${ORACLE_SID}_datafile.lst
BREAK ON tablespace_name SKIP 1 ON REPORT
COMPUTE SUM OF meg ON tablespace_name
COMPUTE SUM OF meg ON REPORT

SELECT tablespace_name,file_name,bytes/1048576 meg,status,autoextensible,maxbytes/1048576 maxmeg,increment_by
FROM dba_data_files
UNION
SELECT tablespace_name,file_name,bytes/1048576 meg,status,autoextensible,maxbytes/1048576 maxmeg,increment_by
FROM dba_temp_files
ORDER BY tablespace_name
/
SPOOL OFF

SET VERIFY ON FEEDBACK ON

TABLESPACE DDL
--------------
set pagesize 0
set long 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;

To resize a datafile
--------------------
alter database datafile '&datafile_name' resize 4096M;

If AUTOEXTEND ON
------------------------
alter database datafile '&datafile_name' autoextend on maxsize 8G;

To add a new datafile in a tablespace
--------------------------------------
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile
select tablespace_name,file_name from dba_data_files where file_name like '%&datafile_name%';

alter tablespace <TABLESPACE_NAME> add datafile '/datafile/path/datafilename.dbf' size 4G;

If AUTOEXTEND ON
----------------
alter tablespace <TABLESPACE_NAME> add datafile '/datafile/path/datafilename.dbf' size 1G autoextend on maxsize unlimited;

To Create a new tablespace
-----------------------------
create tablespace <TABLESPACE_NAME> datafile '/datafile/path/datafilename.dbf' size 4096m;

If AUTOEXTEND ON
----------------
create tablespace <TABLESPACE_NAME> datafile '/datafile/path/datafilename.dbf' size 1G autoextend on maxsize unlimited;

To Create a new tablespace with multipple datafiles
----------------------------------------------------
create tablespace <TABLESPACE_NAME> datafile '/datafile/path/datafilename_01.dbf' size 4096m;

alter tablespace <TABLESPACE_NAME> add datafile '/datafile/path/datafilename_02.dbf' size 4096m;

Schemas in a Tablespace
-----------------------
set pages 9999 lines 300

col "size MB" format 999,999,999
col "Objects" format 999,999,999

select   obj.owner "Owner"
,         obj_cnt "Objects"
,         decode(seg_size, NULL, 0, seg_size) "Size in MB"
from     (select owner, count(*) obj_cnt from dba_objects group by owner) obj
,        (select owner, ceil(sum(bytes)/1024/1024) seg_size
         from dba_segments group by owner) seg
where     obj.owner  = seg.owner(+)
order    by 3 desc ,2 desc, 1
/

All schema object details in a Tablespace
-----------------------------------------
set pages 9999 lines 300

col owner format a15
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999

select  owner
,       tablespace_name
,       segment_name
,       segment_type
,       PARTITION_NAME
,       ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from    dba_segments
where   tablespace_name like '&tablespace_name'
group   by segment_name
order   by ceil(sum(bytes) / 1024 / 1024) desc
/

Total space allocated by Owner
------------------------------
set pages 9999 lines 300

col    owner format a15
col    segment_name format a30
col    segment_type format a15
col     tablespace_name format a20
col    mb format 999,999,999

select  owner
,       segment_name
,       segment_type
,       tablespace_name
,       mb
from    (
        select    owner
        ,    segment_name
      ,    segment_type
        ,    tablespace_name
        ,    bytes / 1024 / 1024 "SIZE in MB"
        from    dba_segments
        order    by bytes desc
        )
/