Table Spaces

Managing Tablespaces Creating Tablespaces •

Before you can create a tablespace, you must create a database to contain it. The primary tablespace in any database is the SYSTEM tablespace, which contains information basic to the functioning of the database server, such as the data dictionary and the system rollback segment. The SYSTEM tablespace is the first tablespace created at database creation. It is managed as any other tablespace, but requires a higher level of privilege and is restricted in some ways. For example, you cannot rename or drop the SYSTEM tablespace or take it offline

•

The steps for creating tablespaces vary by operating system, but the first step is always to use your operating system to create a directory structure in which your datafiles will be allocated. On most operating systems, you specify the size and fully specified filenames of datafiles when you create a new tablespace or alter an existing tablespace by adding datafiles. Whether you are creating a new tablespace or modifying an existing one, the database automatically allocates and formats the datafiles as specified.

•

To create a new tablespace, use the SQL statement CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE. You must have the CREATE TABLESPACE system privilege to create a tablespace. Later, you can use the ALTER TABLESPACE or ALTER DATABASE statements to alter the tablespace. You must have the ALTER TABLESPACE or ALTER DATABASE system privilege, correspondingly

•

You can also use the CREATE UNDO TABLESPACE statement to create a special type of tablespace called an undo tablespace, which is specifically designed to contain undo records. These are records generated by the database that are used to roll back, or undo, changes to the database for recovery, read consistency, or as requested by a ROLLBACK statement.

Chapter 6 | Managing Tablespaces and Datafiles

Creating a Locally Managed Tablespace •

All tablespaces, including the SYSTEM tablespace, can be locally managed

The following statement creates a locally managed tablespace named scc and specifies AUTOALLOCATE: CREATE TABLESPACE scc DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

The following example creates a tablespace with uniform 128K extents. (In a database with 2K blocks, each extent would be equivalent to 64 database blocks). Each 128K extent is represented by a bit in the extent bitmap for this file. CREATE TABLESPACE ceet DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

Altering a Locally Managed Tablespace

You can use the ALTER TABLESPACE statement on locally managed tablespaces for some operations, including the following: •

Adding a datafile. For example:

•

ALTER TABLESPACE scc ADD DATAFILE '/u02/oracle/data/lmtbsb02.dbf' SIZE 1M;

• •

•

Altering tablespace availability (ONLINE/OFFLINE).

•

Making a tablespace read-only or read/write..

•

Renaming a datafile, or enabling or disabling the autoextension of the size of a datafile in the tablespace

•

Because space management is much simpler and more efficient in locally managed tablespaces, they are ideally suited for temporary tablespaces. Locally managed temporary tablespaces use tempfiles, which do not modify data outside of the temporary tablespace or generate any redo for temporary tablespace data. Because of this, they enable you to perform on-disk sorting operations in a read-only or standby database.

Creating a Locally Managed Temporary Tablespace

CREATE TEMPORARY TABLESPACE scc2 TEMPFILE '/u02/oracle/data/lmtemp01.dbf' SIZE 20M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

Chapter 6 | Managing Tablespaces and Datafiles

Dictionary-Managed Tablespaces The default for extent management when creating a tablespace is locally managed. However, you can explicitly specify a dictionary-managed tablespace. For dictionary-managed tablespaces, the database updates the appropriate tables in the data dictionary whenever an extent is allocated or freed for reuse. Creating a Dictionary-Managed Tablespace The following statement creates the dictionary-managed tablespace yemen: CREATE TABLESPACE yemen DATAFILE '/u02/oracle/data/tbsa01.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

The tablespace has the following characteristics: •

The data of the new tablespace is contained in a single datafile, 50M in size.

•

The tablespace is explicitly created as a dictionary-managed tablespace by specifying EXTENT MANAGEMENT DICTIONARY.

•

The default storage parameters for any segments created in this tablespace are specified.

The parameters specified in the preceding example determine segment storage allocation in the tablespace. These parameters affect both how long it takes to access data stored in the database and how efficiently space in the database is used. They are referred to as storage parameters, and are described in the following table: Storage Parameter

Description

INITIAL

Defines the size in bytes of the first extent in the segment

NEXT

Defines the size in bytes of the second and subsequent extents

PCTINCREASE

Specifies the percent by which each extent, after the second (NEXT) extent, grows

MINEXTENTS

Specifies the number of extents allocated when a segment is first created in the tablespace

MAXEXTENTS

Specifies the maximum number of extents that a segment can have. Can

Chapter 6 | Managing Tablespaces and Datafiles

Altering Tablespace Availability An ALTER TABLESPACE statement includes, but is not limited to: •

Coalescing free space in a tablespace.

•

Altering tablespace availability (ONLINE/OFFLINE).

•

Making a tablespace read-only or read/write.

•

Adding or renaming a datafile, or enabling/disabling the autoextension of the size of a datafile in the tablespace

Taking Tablespaces Offline/Online You cannot take the following tablespaces offline: •

SYSTEM

•

The undo tablespace

•

Temporary tablespaces

ALTER TABLESPACE scc1 OFFLINE ALTER TABLESPACE scc1 ONLINE;

Making a Tablespace Read-Only/Read Write All tablespaces are initially created as read/write. Use the READ ONLY clause in the ALTER TABLESPACE statement to change a tablespace to read-only. You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege ALTER TABLESPACE scc1 READ ONLY; ALTER TABLESPACE scc1 READ WRITE;

Chapter 6 | Managing Tablespaces and Datafiles

Renaming Tablespaces Using the RENAME TO clause of the ALTER TABLESPACE, you can rename a permanent or temporary tablespace. For example, the following statement renames the users tablespace: ALTER TABLESPACE scc RENAME TO yemen;

Dropping Tablespaces You can drop a tablespace and its contents (the segments contained in the tablespace) from the database if the tablespace and its contents are no longer required. You must have the DROP TABLESPACE system privilege to drop a tablespace. DROP TABLESPACE yemen INCLUDING CONTENTS; DROP TABLESPACE yemen INCLUDING CONTENTS AND DATAFILES;

Chapter 6 | Managing Tablespaces and Datafiles

Managing Datafiles Creating Datafiles and Adding Datafiles to a Tablespace The following example enables automatic extension for a datafile added to the users tablespace: ALTER TABLESPACE users ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M AUTOEXTEND ON NEXT 512K MAXSIZE 250M;

The next example disables the automatic extension for the datafile. ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' AUTOEXTEND OFF;

Manually Resizing a Datafile You can manually increase or decrease the size of a datafile using the ALTER DATABASE statement. This enables you to add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' RESIZE 100M;

Altering the Availability of All Datafiles Clauses of the ALTER TABLESPACE statement allow you to change the online or offline status of all of the datafiles or tempfiles within a tablespace. Specifically, the statements that affect online/offline status are: •

ALTER TABLESPACE

... DATAFILE {ONLINE|OFFLINE}

To bring an individual datafile online, issue the ALTER DATABASE statement and include the DATAFILE clause.The following statement brings the specified datafile online: ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;

To take the same file offline, issue the following statement: ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;

Renaming and Relocating Datafiles Procedure for Renaming Datafiles To rename datafiles in a single tablespace, complete the following steps: Chapter 6 | Managing Tablespaces and Datafiles

1. Take the tablespace that contains the datafiles offline. The database must be open. For example: ALTER TABLESPACE yemen OFFLINE NORMAL;

2. Rename the datafiles using the operating system. 3. Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the

filenames within the database. For example, the following statement renames the datafiles /u02/oracle/rbdb1/user1.dbf and /u02/oracle/rbdb1/user2.dbf to/u02/oracle/rbdb1/users01.dbf and /u02/oracle/rbdb1/users02.dbf, respectively: ALTER TABLESPACE yemen RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf', '/u02/oracle/rbdb1/user2.dbf' TO '/u02/oracle/rbdb1/users01.dbf', '/u02/oracle/rbdb1/users02.dbf';

Always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile name exactly as it appears in the DBA_DATA_FILES view of the data dictionary. 4. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Dropping Datafiles There is no SQL statement that specifically drops a datafile. The only means of dropping a datafile is to drop the tablespace that contains the datafile. For example, if you want to remove a datafile from a tablespace, you could do the following: 1. Create a new tablespace 2. Move the data from the old tablespace to the new one 3. Drop the old tablespace

Chapter 6 | Managing Tablespaces and Datafiles

HOME AND LAB EXERCISES Creating locally managed Tablespaces Creating dictionary managed Tablespaces with default storage clause and give new value for all storage parameter Add datafile to tablespace Resize the datafile manually Resize the datafile automatically Drop the tablespce

Chapter 6 | Managing Tablespaces and Datafiles