Press "Enter" to skip to content

Resizing A Bigfile Tablespace Using Alter Tablespace in Oracle

A bigfile tablespace can have only one datafile. I will describe a few details about how a bigfile tablespace can be resize. Most of the parameters are available for changing the tablespace’s data file, such as the maximum size, whether it can extend at all, and the size of the extents are now modifiable at the tablespace level. Let’s start with a bigfile tablespace created as follows:
Create bigfile tablespace bigtblspc 
datafile ‘/u05/oradata/bigtblspc.dbf’ size 300m
autoextend on next 50m maxsize unlimited
extent management local
segment space management auto;
Operations that are valid only at the datafile level with smallfile tablespaces can be used with bigfile tablespaces at the tablespace level:
SQL> alter tablespace bigtblspc
       2       resize 1000m;
Tablespace altered.
Although using alter database with the datafile specification for the bigtblspc tablespace will work, the advantage of the alter tablespace syntax is obvious: you don’t have to or need to know where the datafile is stored. As you might suspect, trying to change datafile parameters at the tablespace level with smallfile tablespaces is not allowed.
If a bigfile tablespace runs out of space because its single datafile cannot extend on the disk, you need to relocate the datafile to another volume.