Grow your tablespace in 3 easy steps

This is a rather simple script, but I use it so often that it must be good for something.

We don’t use auto-extending tablespaces, because using auto-extending tablespaces puts you at a risk of running out of diskspace. When you run out of diskspace for data files, the database will have a nasty crash and you’ll probably need to recover it. If you run out of tablespace, on the other hand, you just get some errors. We did the math and put some strict limits on the tablespace. This means that when a customer decides to add a significant amount of data without telling us first, we get alerts that we are running out of table space.

We check that we have disk space to grow and at this point we do the following:

1. Using this query, I find the tablespace that is running out of space:

SELECT a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc

2. Lets assume that TBS_009 is out of space. Using this query, I decide if I want to grow one of the existing data files in the table space, or add a new one:

select * from dba_data_files where tablespace_name= 'TBS_009'

3. If I want to grow an existing data file, I run:

ALTER DATABASE DATAFILE '/voloradata/ora6410g/oradata/dev/tbs_009_01.dbf'
RESIZE 268435456;

And if I want to add a new one, I run:

alter tablespace TBS_009
add datafile '/voloradata/ora6410g/oradata/dev/tbs_009_02.dbf' size 4M autoextend off;

Tip: Even if you want to create a data file that has 10G space, start by creating a very small data file, see that it is created successfully and then grow it to the size you really need. We had a client that crushed while we were creating a large data file on 10.1.0.3, and it ended up creating a corrupted file, which we had to offline and drop. Oracle confirmed that this is indeed a bug, so try to avoid creating large files.

Advertisements

One Comment on “Grow your tablespace in 3 easy steps”

  1. Tim says:

    Thank you for providing this script. it was exactly what i needed.


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s