Super-Sizing A Database: Oracle 10g Tablespace Enhancements

Synopsis. Oracle 10g’s daunting array of new features like extended FLASHBACK technology, Automated Storage Management, and Grid Computing sometimes overshadow the more mundane — yet no less impressive! – improvements to its logical storage capabilities. This article illustrates several enhancements to Oracle’s already robust logical storage structures, including the SYSAUX tablespace, BIGFILE tablespaces, tablespace groups, and tablespace renaming.

Oracle’s highest-level logical container for data, the tablespace, is a lot like an orphaned, lonely child. Tablespaces live in that lonely place between the physical raw data contained in their datafiles and the myriad logical objects whose structure they organize, manage, and control: tables, indexes, and LOBs, to name just a few. I was heartened to see that Oracle 10g has made some significant improvements to tablespaces that considerably extend the size, manageability, and cohesiveness of an Oracle database. I will dive right into what I believe is the most impressive new feature in this group: the BIGFILE tablespace.

Size Does Matter: BIGFILE Tablespaces

I fondly remember buying my first IBM XT clone PC in the late 1980s because it came with my first-ever hard drive. I recall telling my co-workers that I would never need to buy another PC because the new machine had a huge amount of storage — 20MB – and had cost me only an extra $400.00. (This machine’s case is now a concrete-filled boat anchor somewhere in northern Wisconsin. I filled up the hard drive in about six months.)

As the quality and quantity of storage media continues to grow, Oracle 10g has kept pace as well. One of the major improvements in Oracle 10g is the introduction of new tablespace storage capacity. A tablespace is limited to a maximum of 65,536 (64K) datafiles, but each datafile’s size had been limited by the maximum OS file size. The new BIGFILE tablespace overcomes this limitation by allowing the creation of a tablespace with only one datafile – but what a datafile!

A BIGFILE tablespace’s datafile can now hold up to 4,294,967,296 (yes, you read that correctly – the multiplier is over 4 billion) blocks of data storage space. This means that a tablespace could conceivably hold up to 128 TB of data if the maximum Oracle tablespace block size of 32K is used. Oracle does recommend that Automatic Segment Space Management (ASSM) should be enabled. In addition, if the BIGFILE tablespace’s datafile is sized at its maximum of 128TB, then the UNIFORM extent management method will most likely yield better performance, especially when a large extent size is chosen.

BIGFILE tablespaces (also known as BFTs) are therefore obviously aimed at Storage Area Network (SAN) environments on which Oracle’s new Automatic Storage Management (ASM) feature has been enabled. (I will cover the use of BIGFILE tablespaces in the ASM environment more extensively in an upcoming article.) The resulting increase in a database’s potential size is staggering: If enough disk space is available, Oracle can now handle up to eight exabytes of data (65,536 BIGFILE datafiles x 128TB per datafile).

Since BIGFILE tablespaces are allowed to own only one datafile, there is now a one-to-one correspondence between tablespace and datafile, and database space management actually becomes much simpler because all space management operations that were only limited to datafiles now extend automatically to BIGFILE tablespaces. For example, I can now resize a BIGFILE tablespace just by issuing the ALTER TABLESPACE <bft_name> RESIZE <new_size>; command – I don’t have to worry about resizing its datafile(s) separately.

Migrating segments to a BIGFILE tablespace is accomplished using the same methods already available to move tables and indexes to different tablespaces. To migrate a table, the DBA only has to issue the ALTER TABLE <table_name> MOVE <bft_name>; command. Issuing the ALTER INDEX <index_name> TABLESPACE <bft_name> REBUILD ONLINE; command will migrate an index to a BIGFILE tablespace.

Finally, note that a pre-Oracle 10g “normal” tablespace is now called a SMALLFILE tablespace, and that by default Oracle will continue to create SMALLFILEs unless otherwise directed. Listing 1.1 shows how to create a BIGFILE tablespace with the maximum number of allowable blocks, as well as change the database to create BIGFILEs instead of SMALLFILEs for all future tablespaces. Moreover, Listing 1.2 shows the changes that have been made to the appropriate dynamic and static data dictionary views so that it is easy to determine the status of BIGFILE vs. SMALLFILE tablespaces.

Database-Level Default Permanent and Temporary Tablespaces

I have had to spend time cleaning up after my colleagues when they have forgotten to assign a default permanent tablespace or (even worse!) a default temporary tablespace to a newly created user account. If this happened in prior releases, Oracle would assign the SYSTEM tablespace as the default and temporary tablespaces if both were omitted during user account creation.

Fortunately, Oracle 10g fills this gap by offering the ability to specify the default permanent and temporary tablespace for any newly-created user accounts. If you are creating a new database using the Database Configuration Assistant (DBCA), Oracle will prompt you for these values and automatically create appropriate default permanent and temporary tablespaces. Of course, these tablespaces can also be specified in database creation scripts; for a converted Oracle 10g database, they can be updated via the appropriate ALTER DATABASE commands. See Listing 1.3 for an example of a database creation script for a new database and examples of modifying these settings for an existing database.

Temporary Tablespace Groups

Temporary tablespaces can be the occasional bane of an Oracle DBA’s existence. Since they are primarily used as sort work areas when there are insufficient resources for sorting a result set directly in memory, it is not uncommon for a database to run out of space in a temporary tablespace at the most inopportune moments.

Oracle also uses temporary tablespaces to create and store an instance of each global temporary table (GTT) for each user session that invokes any PL/SQL using GTTs. I accidentally found this out the hard way when I reduced significantly the amount sort space for a small development database. When one of my developers ran a query that invoked a large sort, the application being tested began to have trouble creating global temporary tables. I found that GTTs were littering the landscape of the database and I was forced to immediately increase the temporary tablespace’s size so that testing could continue. (A much better alternative to using GTTs, of course, is to implement constructs like PL/SQL collections. See my prior article on how this was accomplished.)

Oracle 10g now permits assignment of at least one (but certainly more than one!) temporary tablespaces to a tablespace group. This tablespace group can then be assigned to a user account as if the tablespace group was a normal temporary tablespace. And since a temporary tablespace can also be a BIGFILE tablespace, this significantly lessens the chance that any one user session or process might consume all temporary space during a huge sort – something that is especially useful during initial loading or updates of a data warehouse’s tables via extraction, transformation and loading (ETL) steps.

The nice thing about temporary tablespace groups is that Oracle handles their creation (and destruction!) automatically. When a new tablespace group is first specified, Oracle implicitly creates it; likewise, when the last temporary tablespace is removed from an existing tablespace group, Oracle implicitly drops the tablespace group.

Listing 1.4 shows how to create new temporary tablespace groups, add existing temporary tablespaces to existing tablespace groups, migrate a user account to an existing tablespace group, and how to remove tablespace groups implicitly from an Oracle 10g database.

The SYSAUX Tablespace

One of the major changes to the database in Oracle 10g is probably one of the more unnoticed – unless you have had to convert a pre-Oracle 10g database to 10g, that is! Oracle 10g shifted a majority of schemas and their corresponding database objects to a new tablespace, SYSAUX, that must be created before a 10g conversion can proceed.

Prior to Oracle 10g, a plethora of different tablespaces were created whenever certain special features were requested for inclusion in an Oracle database. These included the database objects to enable capabilities like OLAP, Data Mining, XML Database (XMLDB), Oracle Enterprise Manager (OEM), and the Recovery Catalog. If an Oracle DBA decided to install the optional STATSPACK features, another new schema (PERFSTAT) was created, and Oracle also recommended creating yet another new tablespace to hold the volume of raw data that facilitated STATSPACK reporting from multiple statistical snapshots.

Oracle 10g overcomes this confusion with the SYSAUX tablespace, a centralized repository that stores all metadata and database objects for all special database features. The SYSAUX tablespace is created automatically when using the Database Configuration Assistant (DBCA) to create a new database, and it can be added to an existing pre-10g Oracle database during its upgrade to 10g with the Database Upgrade Assistant (DBUA) as well as via standard SQL commands.

Since Oracle 10g features like Automatic Database Diagnostic Monitor (ADDM), Automatic Workload Repository (AWR), and the various Advisors all require regular and timely collection of database resource usage information, the SYSAUX tablespace facilitates centralized storage of usage statistics. Most importantly, however, is a reduction in the load on the SYSTEM tablespace. Prior to Oracle 10g, many of the optional features actually used the SYSTEM tablespace to store objects and related metadata. With the implementation of SYSAUX, however, the primary functions of the SYSTEM tablespace – handling requests from the cost-based optimizer for data dictionary statistics and the storage of PL/SQL system-level objects – are now no longer encumbered by requests for other non-critical metadata and objects.

One final note: The loss of the SYSAUX tablespace’s datafile is not a critical media failure, by the way. Unlike the loss of either the SYSTEM or UNDO tablespaces’ datafiles, the database will continue to run without SYSAUX. However, since so many utility applications depend on the objects stored within SYSAUX – especially the SYSMAN schema, which contains all of the objects needed to interface to Oracle 10g’s Enterprise Manager “dashboard” – it is important to include SYSAUX in your database disaster recovery planning.

Don’t Recreate That Tablespace, Rename It!

I cannot tell you how long I have waited for this feature! Simply said: It’s about time. Prior to Oracle 10g, if I wanted to move objects from, say, a locally managed tablespace with UNIFORM extent management to one with AUTOMATIC extent management, I had to follow a rather circuitous path:

  • Create a staging tablespace of similar size to the current tablespace.
  • Move all objects from the current tablespace to the staging tablespace.
  • Drop the current tablespace.
  • Recreate the current tablespace with its new name and storage parameters.
  • Move all objects from the staging tablespace back to the newly-recreated tablespace.
  • Drop the staging tablespace.

Oracle 10g has done away with a third of this process with the new tablespace rename command, ALTER TABLESPACE <old_tablespace_name> RENAME TO <new_tablespace_name>. Now my migration path is much easier to tread:

  • Create a new tablespace with the desired storage parameters.
  • Move all objects from the current tablespace to the new tablespace.
  • Drop the current tablespace.
  • Rename the new tablespace to the same name as the original tablespace.

Caveats. Before proceeding with a tablespace rename operation, it is important to remember these restrictions:

  • SYSTEM and SYSAUX. These two tablespaces can never be renamed, for obvious reasons!
  • Offline Datafiles. For a tablespace rename operation to succeed, all of the targeted tablespace’s datafiles must be online.
  • Tablespaces in READ ONLY Mode. A tablespace rename operation does not affect the datafile header of a read-only tablespace. If the tablespace is brought back into READ WRITE mode, however, the rename operation may then proceed.

With these notable exceptions, any tablespace – whether permanent or temporary – can be renamed. Oracle automatically updates any references to the renamed tablespace in the tablespace’s data file headers, in the control file, and in the data dictionary. If the renamed tablespace was assigned as a default or temporary tablespace to a user account, Oracle also renames it for that account. Finally, an online UNDO tablespace can also be renamed; once the UNDO tablespace has been renamed, Oracle also handles the propagation of the renamed undo tablespace’s new name to the server parameter file (SPFILE).


Oracle 10g’s new tablespace features have significantly improved the organization, performance, and manageability of the Oracle database. The new BIGFILE tablespace feature has significantly expanded the maximum database size into the multiple-exabyte range, and the capability to rename a tablespace reduces the amount of work required to reorganize tablespaces. Tablespace groups help to limit (if not finally eliminate) out-of-space conditions for sort work areas during large aggregation and sorting operations. Finally, the mandatory SYSAUX tablespace gathers together under one roof the myriad database objects and metadata that had been scattered throughout a typical pre-Oracle 10g database.

References and Additional Reading

While there is no substitute for direct experience, reading the manual is not a bad idea, either. I have drawn upon the following Oracle 10g documentation for the deeper technical details of this article:

B10750-01 Oracle Database 10g: New Features Guide

B10759-01 Oracle Database 10g: SQL Reference

» See All Articles by Columnist Jim Czuprynski

Jim Czuprynski
Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Latest Articles