Oracle 10g introduced a new type of tablespace
called BIGFILE. Unlike traditional tablespaces that
are composed of up to 1,022 datafiles, a BIGFILE tablespace stores its data in a single datafile
with a much larger capacity. BIGFILE tablespaces can
grow as large as 32-128 terabytes (TB), depending on the chosen block size.
Changing the size of a traditional tablespace
meant resizing its datafiles individually using the
ALTER DATABASE statement and identifying each datafile
to resize by its complete operating system pathname or internal file number:
ALTER DATABASE
DATAFILE '/u07/oracle/oradata/train/media01.dbf'
RESIZE 1G;
Similarly, setting the autoextend
properties of a tablespace was done by setting each datafile individually:
ALTER DATABASE
DATAFILE '/u07/oracle/oradata/train/media01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
For large tablespaces with
hundreds of datafiles, this can be a major chore. But
because BIGFILE tablespaces have only one datafile, there is no need to identify that datafile when they need to be resized. The resize can be
done much more conveniently at the tablespace level,
using the ALTER TABLESPACE statement:
ALTER TABLESPACE media
RESIZE 1G;
Likewise, a datafile’s AUTOEXTEND
properties can be changed for the tablespace as a whole
for BIGFILE tablespaces:
ALTER TABLESPACE media
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
This is especially beneficial if you’re using ASM disk
groups with Oracle Managed Files, as you may not even know the exact name of
the datafile because it was autogenerated.
However, BIGFILE tablespaces
should not be used just for this feature. BIGFILE tablespaces
are designed to be used with storage area networks (SAN), RAID arrays, ASM, and
similar storage solutions that provide striping of data across multiple
devices. Because they can only have one datafile,
there is no opportunity to load balance among separate datafiles
or add an additional datafile as the tablespace grows if these technologies are not used.
Miss a tip?
Check out the Oracle archive, and catch up on our most recent Oracle tips.
TechRepublic’s free Oracle newsletter, delivered each Wednesday, covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!
Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob’s site.
