Oracle Tablespaces And Datafiles

Contents of this Post

1. Check Tablespace Size Details
2. DataFile AUTOEXTENSIBLE Info
3. Check Max/Min Size Of DataFile In Tablespace
4. Tablespace & Datafile Creation Time
5. Tablespace Report With Space MARK
6. TEMPORARY Tablespace
7. UNDO Tablespace

Permanent Tablespace Size

set lines 200 pages 200
col TABLESPACE_NAME for a25
col "FREE%" for a10
col "USED%" for a10
col STATUS for a7

Select a.TABLESPACE_NAME,round(a.BYTES/1073741824,2) AVAIL_GB,round(b.BYTES/1073741824,2) USED_GB,round(c.BYTES/1048576,2) FREE_MB,
round((b.BYTES*100)/a.BYTES,2)||'%' "USED%", round((c.BYTES*100)/a.BYTES,2)||'%' "FREE%"
,NVL2(to_char(a.BYTES),'ONLINE','OFFLINE') STATUS
from sm$ts_avail a,sm$ts_used b,sm$ts_free c
where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+)
and a.TABLESPACE_NAME=c.TABLESPACE_NAME(+);

OR

select TABLESPACE_NAME, sum(BYTES)/(1024*1024) SIZE_MB from dba_free_space group by TABLESPACE_NAME;

select sum(BYTES)/(1024*1024) SIZE_MB from dba_free_space where TABLESPACE_NAME='UNDOTBS';

Datafile Location
column FILE_NAME for A55
select FILE_ID,FILE_NAME, bytes/(1024*1024) Size_MB from dba_data_files where TABLESPACE_NAME='UNDOTBS' order by FILE_ID;

Resize or Add Datafile to a Tablespace
ALTER TABLESPACE DATA_B ADD DATAFILE '/u02/oradata/SOA/PROD_soainfra14.dbf' SIZE 2G; (9i DB doesn't support size in GB)

ALTER DATABASE DATAFILE '/u02/oradata/SOA/PROD_soainfra13.dbf' RESIZE 5G;


DataFile + AUTOEXTENSIBLE Info

col TABLESPACE_NAME for a15
col "File Name" for a60
col "AutoEx Remaining GB" for a19
col STATUS for a10
col "AutoEx" for a6

SELECT to_char(FILE_ID,99)||') '||FILE_NAME "File Name",TABLESPACE_NAME,round(BYTES/1024/1024,2) "Size MB", STATUS, AUTOEXTENSIBLE "AutoEx"
,round(USER_BYTES/1024/1024,2) "UsrMB",(INCREMENT_BY*8192)/1024/1024 "AutoEx By Mb"
,round(MAXBYTES/1024/1024/1024,2) "AutoEx MaxGB", DECODE(AUTOEXTENSIBLE,'NO','N/A'
,round((MAXBYTES-BYTES)/1024/1024/1024,2)) "AutoEx Remaining GB"
from dba_data_files
--where TABLESPACE_NAME='VWSE_DATA'
--Where AUTOEXTENSIBLE ='YES' -- in ('DST_ADMIN_DATA','DST_ADMIN_INDEX')
Order by TABLESPACE_NAME,FILE_ID;

Identify Max/Min Size Of DataFile In Tablespace

col FILE_NAME for a50
col ID for 9999
col TABLESPACE_NAME for a20

select TO_CHAR(FILE_ID,99) ID

,FILE_NAME,TABLESPACE_NAME,Tot_Gb,AUTOEXTENSIBLE

,INCREMENT_BY,Max_Gb,Usr_Mb
,DECODE(Min_File_Size,1,'Min Size DataFile','Max Size DataFile') "File_Desc"
from (
select FILE_ID,FILE_NAME,TABLESPACE_NAME,BYTES/1073741824 Tot_Gb, AUTOEXTENSIBLE,INCREMENT_BY,
MAXBYTES/1073741824 Max_Gb,USER_BYTES/1048576 Usr_Mb
,ROW_NUMBER () OVER(PARTITION BY TABLESPACE_NAME ORDER BY BYTES) Min_File_Size
,ROW_NUMBER () OVER(PARTITION BY TABLESPACE_NAME ORDER BY BYTES Desc) Max_File_Size
from dba_data_files
--WHERE TABLESPACE_NAME in ('DATA','INDEX','EX_DATA'))
WHERE Min_File_Size = 1                                        -- Enable To Select Minimun Size Of Datafile
--WHERE Max_File_Size=1                                      -- Enable To Select Maximun Size Of Datafile
ORDER BY TABLESPACE_NAME;


Tablespace & Datafile Creation Time

SET LINE 150 pages 200
col FileNo for 99999
col "Tablespace Name" for a20
col "Datafile Name" for a50
col "DataFile Size" for a13
col "Size In MB" noprint
BREAK ON "Tablespace_Created"
TTITLE CENTER 'T A B L E S P A C E  A N D  D A T A F I L E  C R E A T I O N  T I M E' SKIP 1 CENTER ===================================================================== SKIP 1

SELECT FILE# FileNo,b.NAME "Tablespace Name"

,to_char(c.Tblsp_Creation_Time,'ddth-Mon-YY hh24:mi') "Tablespace_Created",
a.NAME "Datafile Name"

, TO_CHAR(CREATION_TIME,'ddth-Mon-YY hh24:mi') "Datafile_Created"

, BYTES/1024/1024 "Size In MB",
decode(sign(length(BYTES)-10),-1,lpad(BYTES/1024/1024,3)||' MB'

,lpad(BYTES/1024/1024/1024,3)||' GB') "DataFile Size",
STATUS,ENABLED
from v$datafile a, v$tablespace b,
    (select b.NAME Tablespace

    ,min(CREATION_TIME) Tblsp_Creation_Time from v$datafile a

    ,v$tablespace b where a.TS#=b.TS# group by b.NAME) c
WHERE a.TS#=b.TS#
AND   b.NAME=c.Tablespace
order by b.NAME,CREATION_TIME;
clear breaks;

Data File Status And Creation Time

col TABLESPACE for a12
Select    a.NAME

             ,b.NAME TABLESPACE

             ,TO_CHAR(CREATION_TIME,'dd-Mon-yy hh24:mi:ss') CREATION_TIME

             ,STATUS,ENABLED

             ,LAST_TIME

             ,ONLINE_TIME
From    v$datafile a, v$tablespace b

Where  a.TS# = b.TS#

And      b.NAME ='UNDOTBS';


Tablespace Report With Space MARK

set lines 200 pages 200
col TABLESPACE_NAME for a25
col "FREE%" for a10
col "USED%" for a10
col STATUS for a7

Select a.TABLESPACE_NAME

,round(a.BYTES/1073741824,2) AVAIL_GB

,round(b.BYTES/1073741824,2) USED_GB

,round(c.BYTES/1048576,2) FREE_MB

,round((b.BYTES*100)/a.BYTES,2)||'%' "USED%"

, round((c.BYTES*100)/a.BYTES,2)||'%' "FREE%"
,NVL2(to_char(a.BYTES),NULL,'OFFLINE') STATUS

,DECODE(sign((TO_NUMBER(c.BYTES)/1048576) -100) ,-1,'<<==== '||c.TABLESPACE_NAME,NULL) LOW_SPACE_MARK
from sm$ts_avail a,sm$ts_used b,sm$ts_free c
where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+)
and a.TABLESPACE_NAME=c.TABLESPACE_NAME(+)
Order by LOW_SPACE_MARK;


Temporary Tablespace (Check separate Post for TEMPORARY Tablespace)

select
FILE_NAME
,TABLESPACE_NAME,BYTES/1024/1024/1024 "GB"
,STATUS
,AUTOEXTENSIBLE
,(INCREMENT_BY*8192)/1024/1024 INCREMENT_Mb
,MAXBYTES/1024/1024/1024 "MaxGB"
,USER_BYTES/1024/1024/1024 "UsrGB"
from dba_temp_files where TABLESPACE_NAME = 'DST_TEMP';

Check For Auto extendable
select tablespace_name, substr(file_name,1,60) file_name,  status,  bytes, AUTOEXTENSIBLE
FROM dba_data_files where tablespace_name ='STATSPACK';


UNDO TABLESPACE (Check separate Post for UNDO Tablespace)

select * from dba_data_files where tablespace_name like '%UNDO%';

select sum(BYTES)/1024/1024/1024 "UNDO DATAFILE In GB"

from dba_data_files where tablespace_name ='UNDO';

Unset UNDO Tablespace
alter system set undo_tablespace = '';
alter system set undo_tablespace = '' scope=memory;

Drop UNDO Tablespace
DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

drop tablespace undo_old including contents;

Create/Manage UNDO Tablespace

create undo tablespace UNDO datafile '/apps/data/undo02.dbf' size 3072m;
alter tablespace UNDO add datafile '/apps/data/undo02.dbf' size 3072m;

alter database datafile '/apps/data/undo06.dbf' offline drop;
alter database datafile '/apps/data/undo06.dbf' online;
recover datafile '/apps/data/undo06.dbf';