How to resize tablespaces
Thursday, January 18, 2007 09:46 AM
Bob Watkins shows you how to resize BIGFILE tablespaces directly using ALTER TABLESPACE.
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.
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.
Play video
There are currently no comments for this post.