Similar to this page where I manually extended a tablespace that had only 1% free.
But this time I needed to create a new datafile as all the current datafile’s were at there max (2GB for this customer).
1) Confirmed tablespace ‘I_Large5’ has only 1% free.
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 3
I_LARGE5 56929280 56142848 786432 1
2) I listed the I_LARGE5 datafile’s to see how large the current ones are, and to get the path and name of the latest one.
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 = 'I_LARGE5'
order by file_name;
3) looked in the path to make sure there was enough space for a new 1GB file.
df -k /db01/oradata/CIX/
4) Then created a new datafile, incrementing the name by one …
alter tablespace I_LARGE5 add datafile '/db01/oradata/CIX/i_large5_14.dbf' size 500m;
5) lastly, as the datafiles of this database are copied to a standby server every night I needed to amend the copy scripts to include this newly created datafile.
Database Consultant 