My daily checks reveiled that tablespace D_LARGE3 had only 1% free (NB: these are manual extend).
I listed the current datafile’s to see the path and name, and to see how large the current ones were …
<code>col tablespace_name for a20
col file_name for a40
select tablespace_name, file_name, bytes/1024/1024
from dba_data_files
where tablespace_name = ‘D_LARGE3’
order by file_name;
TABLESPACE FILE_NAME BYTES/1024/1024———- —————————————- —————D_LARGE3 /db010/oradata/CIX/d_large3_01.dbf 1985D_LARGE3 /db010/oradata/CIX/d_large3_02.dbf 1985D_LARGE3 /db010/oradata/CIX/d_large3_03.dbf 1985D_LARGE3 /db010/oradata/CIX/d_large3_04.dbf 1985D_LARGE3 /db010/oradata/CIX/d_large3_05.dbf 1985D_LARGE3 /db010/oradata/CIX/d_large3_06.dbf 1985D_LARGE3 /db010/oradata/CIX/d_large3_07.dbf 1985D_LARGE3 /db010/oradata/CIX/d_large3_08.dbf 1985D_LARGE3 /db010/oradata/CIX/d_large3_09.dbf 1985D_LARGE3 /db010/oradata/CIX/d_large3_10.dbf 1985D_LARGE3 /db010/oradata/CIX/d_large3_11.dbf 1985
TABLESPACE FILE_NAME BYTES/1024/1024———- —————————————- —————D_LARGE3 /db010/oradata/CIX/d_large3_12.dbf 1985D_LARGE3 /db010/oradata/CIX/d_large3_13.dbf 1985D_LARGE3 /db010/oradata/CIX/d_large3_14.dbf 1985D_LARGE3 /db010/oradata/CIX/d_large3_15.dbf 1985D_LARGE3 /db010/oradata/CIX/d_large3_16.dbf 1985D_LARGE3 /db010/oradata/CIX/d_large3_17.dbf 1985D_LARGE3 /db010/oradata/CIX/d_large3_18.dbf 1985D_LARGE3 /db013/oradata/CIX/d_large3_19.dbf 1000</code>
… and noticed the last one was only 1GB in size. Therefore I didn’t need to add a new datafile, just expand it …
<code>alter database datafile ‘/db013/oradata/CIX/d_large3_19.dbf’ resize 2000m
</code>
I confirmed this had worked with the ts-free script (attached) …
<code>column owner format a10
column “%free” format 999
select a.tsname, a.total “Tot/Kb”, a.total – b.free “Used/Kb”,
b.free “Free/K”, (b.free * 100) / a.total “%free”
from
(select tablespace_name tsname, sum(bytes)/1024 total
from dba_data_files
group by tablespace_name ) a, (select tablespace_name tsname, sum(bytes)/1024 free from dba_free_space group by tablespace_name ) b where a.tsname = b.tsname(+)/
TSNAME Tot/Kb Used/Kb Free/K %free—————————— ———- ———- ———- —–D_LARGE1 58312704 56477696 1835008 3D_LARGE3 56929280 56142848 786432 2</code>
job done :-))
btw <a href=”/node/179″>here’s</a> a similar incident were I had to add a datafile
Database Consultant 