Create and Manage Tablespaces

Tablespaces are The highest level of logical storage of Oracle.Tablespaces group schema objects for administration convenience. They bridge physical structures, such as datafiles or extents, and logical structures, such as tables and indexes. Tablespaces can store zero or more segments. Segments are schema objects that require storage outside the data dictionary. Tables and indexes are examples of segments. Constraints and sequences are examples of schema objects that do not store data outside the data dictionary and are therefore not segments. Some common tablespaces of Oracle 10g are : SYSTEM,SYSAUX,TEMP,USER

You must make several choices when creating a tablespace:

1. Tablespace data block size.
   
2. whether to make the tablespace bigfile or smallfile.
3. whether to manage extents locally or with the dictionary.
4. Whether to manage segment space automatically or manually.

Data Block Size : The SYSTEM and SYSAUX tablespaces have the database’s standard data block size, defined at creation time by the initialization parameter db_block_size. Other tablespaces can have different data block sizes, defined at tablespace creation time. The standard data block size are 2K,4K,8K,16K and 32K. You may choose any non-standard data block size but it must be multiple of your OS block size.

Tablespace Type: There are two type of tablespaces :

1. Bigfile Tablespace: are New to Oracle10g and are built on a single datafile (or temp file), which can be as many as 2³² data blocks in size. So, a bigfile tablespace that uses 8KB data blocks can be as much as 32TB in size. Bigfile tablespaces are intended for very large databases. When a very large database has thousands of read/write datafiles, operations that must update the datafile headers, such as checkpoints, can take a relatively long time. If you reduce the number of datafiles, these operations can complete faster.
2. Smallfile Tablespace: Smallfile tablespace is the new name for the old Oracle tablespace datafile option. With a smallfile tablespace, you can have multiple datafiles for a tablespace. Each datafile can be as many as 2²² data blocks in size. So datafiles in a smallfile tablespace that uses 8KB data blocks are limited to 32GB. The smallfile tablespace can have as many as 1,022 datafiles, limiting the 8KB data block tablespace to slightly less than 32TB—about the same as a bigfile tablespace.The SYSTEM and SYSAUX tablespaces are always created as smallfile tablespaces.
   

Extents Management: You can use tablespaces with either local extent management or dictionary extent management . With dictionary extent management, the database tracks free and used extents in the data dictionary, changing the FET$ and UET$ tables with recursive SQL. With local extent management, the database tracks extents through the use of bitmaps, eliminating the recursive SQL. Local extent management is the default if not specified and is generally the preferred technique.

With locally managed tablespaces, you have two options for how extents are allocated: UNIFORM or AUTOALLOCATE. The UNIFORM option tells the database to allocate and deallocate extents in the tablespace with the same unvarying size that you can specify or let extents default to 1MB. UNIFORM is the default for temporary tablespaces and cannot be specified for undo tablespaces. AUTOALLOCATE, on the other hand, tells the database to vary the size of extents for each segment.For example, on Windows and Linux with 8KB data blocks, each segment starts out with 64KB extents for the first 16 extents, and then extents increase in size to 1MB for the next 63 extents. The size then increases to 8MB for the next 120 extents, then 64MB, and so on as the segment grows. This algorithm allows small segments to remain small and large segments to grow without gaining too many extents. AUTOALLOCATE is best used for a general-purpose mixture of small and large tables.


Segment Space Management: For tablespaces that have local extent management, you can use either manual or automatic segment space management. Manual segment space management exists for backward compatibility and uses free block lists to identify the data blocks available for inserts together with the parameters PCT_FREE and PCT_USED, which control when a block is made available for inserts. After each INSERT or UPDATE, the database compares the remaining free space in that data block with the segment’s PCT_FREE setting. If the data block has less than PCT_FREE free space (meaning that it is almost full), it is taken off the free block list and is no longer available for inserts. The remaining free space is reserved for update operations that may increase the size of rows in that data block. After each UPDATE or DELETE, the database compares the used space in that data block with that segment’s PCT_USED setting. If the data block has less than PCT_USED used space, the data block is deemed empty enough for inserts and is placed on the free block list. When automatic segment space management is specified, bitmaps are used instead of free lists to identify which data blocks are available for inserts. The parameters PCT_FREE and PCT_USED are ignored for segments in tablespaces with automatic segment space management. Automatic segment space management is available only on tablespaces configured for local extent management; it is not available for temporary or system tablespaces. Automatic segment space management performs better and reduces your maintenance tasks, making it the preferred technique.

General Syntax:

CREATE [SMALLFILE>] TABLESPACE
DATAFILE ''SIZE
[REUSE] AUTOEXTEND BLOCKSIZE
[<LOGGING | NOLOGGING | FILESYSTEM_LIKE_LOGGING>]
[FORCE LOGGING][ENCRYPTION USING ''
IDENTIFIED BY [NO] SALT]
[DEFAULT OPERATIONS] | NOCOMPRESS>]
[<ONLINE | OFFLINE>]
EXTENT MANAGEMENT LOCAL >
[SEGMENT SPACE MANAGEMENT ]
[FLASHBACK ];


Example:

CREATE TABLESPACE TEST
DATAFILE '/oradata/datafiles/test_01.dbf'
SIZE 500M AUTOEXTEND ON
NEXT 200M MAXSIZE UNLIMITED
BLOCKSIZE 8K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M
SEGMENT SPACE MANAGEMENT AUTO ONLINE ;


Add A Datafile To A Tablespace:

ALTER TABLESPACE TEST
ADD DATAFILE '/oradata/datafiles/test_02.dbf'
SIZE 800M AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;


Remove a Tablespace: Remove a tablespace from the database, use the DROP TABLESPACE statement. The optional clause INCLUDING CONTENTS recursively removes any segments (tables, indexes, and so on) in the tablespace, like this: DROP TABLESPACE dba_sandbox INCLUDING CONTENTS;

Dropping a tablespace does not automatically remove the datafiles from the file system. Use the additional clause INCLUDING CONTENTS AND DATAFILES to remove the underlying datafilesas well as the stored objects, like this: DROP TABLESPACE hr_data INCLUDING CONTENTS AND DATAFILES;

Put A Tablespace into backup Mode : ALTER TABLESPACE system BEGIN BACKUP; Use the keywords END BACKUP to take a tablespace out of backup mode, like this: ALTER TABLESPACE system END BACKUP;

Read-only Tablespace: When a tablespace is read-only, it does not have to be backed up with the nightly or weekly database backups. One backup after being marked read-only is all that is needed for future recoveries. Tables in a read-only tablespace can only be selected from; their rows cannot be inserted, updated, or deleted. To do this: ALTER TABLESPACE test READ ONLY;
go back to read-write mode: ALTER TABLESPACE test READ WRITE;