Creating Tablespaces- Tablespaces and Data Files
You use the CREATE TABLESPACE statement to create tablespaces.
In most scenarios you need to use only a few of the features available, namely, locally managed extent allocation and automatic segment space management.
The following code snippet demonstrates how to create a tablespace that employs the most common features:
You need to modify this script for your environment.
For example, the directory path or ASM, data file size, and uniform extent size should be changed per environment requirements.
In previous releases, there were quite a few storage parameters such as NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT.
These parameters needed to be set until locally managed tablespaces with EXTENT MANAGEMENT LOCAL, which uses a bitmap in the data file to efficiently determine whether an extent is in use.
A locally managed tablespace with uniform extents must be minimally sized for at least five database blocks per extent.
As you add data to objects in tablespaces, Oracle automatically allocates more extents to an associated tablespace data file as needed to accommodate the growth.
You can give it a size, or you can specify an AUTOALLOCATE clause, especially when you think objects in one tablespace will be varying sizes. Again, this removes the need to have the different tablespaces LARGE, SMALL, and so on.
The SEGMENT SPACE MANAGEMENT AUTO clause instructs Oracle to manage the space within the block.
When you use this clause, there is no need to specify parameters. Using AUTO vastly reduces the number of parameters you need to configure and manage.
When a data file fills up, you can instruct Oracle to increase the size of the data file automatically, with the AUTOEXTEND feature. Using AUTOEXTEND allows for processes to run without needing DBA intervention when getting close to running out of space. MAXSIZE is optional, but it will not allow the tablespace to grow beyond that size so that the mount point disk doesn’t fill up.
However, you must monitor tablespace growth and plan for additional storage space. This includes watching for processes that might load a large amount of data.
Manually adding space might limit having a runaway SQL process that accidentally grows a tablespace until it has consumed all the space on a mount point, but a load process that is large one month over another might be rolled back if it fails on additional space requirements.
Using the parameter RESUMABLE_TIMEOUT in the database, you will be allowed to set a time to be able to respond to tablespace issues.
If you inadvertently fill up a mount point that contains a control file of the Oracle binaries, you can hang or freeze your database so no other transactions can occur.
Using Automatic Storage Management (ASM) will also help here to be able to add another disk to the diskgroup to avoid filling up a mount point, and when used with RESUMABLE_TIMEOUT, it provides the time to manage.
Monitoring and planning for storage and growth are still the best methods for managing tablespaces sizing to be able to proactively add the needed space.
In using ASM, the CREATE TABLESPACE command becomes even easier, allowing DBAs to plan on overall growth and additional storage space monitoring. It takes the defaults of the disk group and parameters that are set to use ASM. We will discuss ASM administration in a later chapter, but here is a quick example:
For security, tablespaces can be transparently encrypted. Transparent means that the application does not need to change to use the encrypted tablespaces.
This will allow for data at rest in the data files to be encrypted, and when the database is open, the tablespaces are decrypted using the encryption key in the database wallet to be able to see the data through queries.
Using encryption makes it so that the data files cannot be viewed in plain text, which is the same for backups of the data files.
As already stated, there are many options for creating tablespaces, and this security option does require management of the encryption key, which can be centrally located or locally with the database.
The create tablespace command is simple enough:
If you ever need to verify the SQL required to re-create an existing tablespace, you can do so with the DBMS_METADATA package. First, set the LONG variable to a large value:
Next, use the DBMS_METADATA package to display the CREATE TABLESPACE data definition language (DDL) for all tablespaces within the database:
Tip You can also use data pump to extract the ddl from database objects. see Chapter 13 for details.