Tablespace has only 1% free and cannot extend any datafiles

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, "Tot/Kb", - "Used/Kb", "Free/K", ( * 100) / "%free"
(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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s