1 of 48

Managing Tablespaces and Datafiles

8

2 of 48

Objectives

After completing this lesson, you should be able to do the following:

Define the purpose of tablespaces and datafiles
Create tablespaces
Manage tablespaces
Create and manage tablespaces using Oracle Managed Files (OMF)

8-2

3 of 48

Tablespaces and Datafiles

Oracle stores data logically in tablespaces and physically in datafiles.

Tablespaces:

Can belong to only one database at a time
Consist of one or more datafiles
Are further divided into logical units of storage

Datafiles:

Can belong to only one �tablespace and one database
Are a repository for schema �object data

Database

Tablespace

Datafiles

8-3

4 of 48

Types of Tablespaces

SYSTEM tablespace

Created with the database
Contains the data dictionary
Contains the SYSTEM undo segment

Non-SYSTEM tablespace

Separate segments
Eases space administration
Controls amount of space allocated to a user

8-4

5 of 48

Creating Tablespaces

A tablespace is created using the command:

CREATE TABLESPACE

CREATE TABLESPACE userdata

DATAFILE '/u01/oradata/userdata01.dbf' SIZE 100M

AUTOEXTEND ON NEXT 5M MAXSIZE 200M;

8-5

6 of 48

8-6

Creating Tablespaces (continued)

where:

Tablespace: This is the name of the tablespace to be created

DATAFILE: This specifies the datafile or datafiles that make up the tablespace

MINIMUM EXTENT: This ensures that every used extent size in the tablespace is

a multiple of the integer. Use K or M to specify this size in kilobytes or megabytes.

BLOCKSIZE: BLOCKSIZE specifies a nonstandard block size for the tablespace. In order to specify this clause, you must have the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE parameter set, and the integer you specify in this clause must correspond with the setting of one DB_nK_CACHE_SIZE parameter setting.

LOGGING: This specifies that, by default, all tables, indexes, and partitions within the tablespace have all changes written to redo. LOGGING is the default.

NOLOGGING: This specifies that, by default, all tables, indexes, and partitions within the tablespace do not have all changes written to redo. NOLOGGING affects only some DML and DDL commands, for example, direct loads.

DEFAULT: DEFAULT specifies the default storage parameters for all objects created in the tablespace creation.

OFFLINE: This makes the tablespace unavailable immediately after creation.

PERMANENT: This specifies that the tablespace can be used to hold permanent objects.

TEMPORARY: This specifies that the tablespace be used only to hold temporary objects; for example, segments used by implicit sorts caused by an ORDER BY clause. Cannot specify EXTENT MANAGEMENT LOCAL or the BLOCKSIZE clause.

extent_management_clause: This clause specifies how the extents of the tablespace are managed. This clause is discussed in a subsequent section of this lesson.

segment_management_clause: This is relevant only for permanent, locally managed tablespaces. It lets you specify whether Oracle should track the used and free space in the segments in the tablespace using free lists or bitmaps.

datafile_clause:== filename [SIZE integer[K|M] [REUSE]

[ autoextend_clause ]

filename: This is the name of a datafile in the tablespace.

SIZE: This specifies the size of the file. Use K or M to specify the size in kilobytes or megabytes.

REUSE: This allows the Oracle server to reuse an existing file.

autoextend_clause: This clause enables or disables automatic extension of the datafile.

NEXT: This specifies the size in bytes of the next increment of disk space to be allocated automatically when more extents are required.

7 of 48

8-7

8 of 48

8-8

9 of 48

Space Management in Tablespaces

Locally managed tablespace:

Free extents managed in the tablespace
Bitmap is used to record free extents
Each bit corresponds to a block or group of blocks
Bit value indicates free or used

Dictionary-managed tablespace:

Free extents are managed by the data dictionary
Appropriate tables are updated when extents are allocated or deallocated

8-9

10 of 48

Locally Managed Tablespaces

Reduced contention on data dictionary tables
No undo generated when space allocation or deallocation occurs
No coalescing required

CREATE TABLESPACE userdata

DATAFILE '/u01/oradata/userdata01.dbf' SIZE 500M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

8-10

Locally Managed Tablespaces

The LOCAL option of the EXTENT MANAGEMENT clause specifies that a tablespace is to be locally managed. By default a tablespace is locally managed.

extent_management_clause:

[ EXTENT MANAGEMENT [ DICTIONARY | LOCAL

[ AUTOALLOCATE | UNIFORM [SIZE integer[K|M]] ] ] ]

where:

DICTIONARY: Specifies that the tablespace is managed using dictionary tables.

LOCAL: Specifies that the tablespace is locally managed via bitmaps. If you specify LOCAL, you cannot specify DEFAULT storage_clause, MINIMUM EXTENT, or TEMPORARY.

AUTOALLOCATE: Specifies that the tablespace is system managed. Users cannot specify an extent size. This is the default.

UNIFORM: Specifies that the tablespace is managed with uniform extents of SIZE bytes. Use K or M to specify the extent size in kilobytes or megabytes. The default size is 1 MB.

11 of 48

8-11

Locally Managed Tablespaces (continued)

The EXTENT MANAGEMENT clause can be used in various CREATE commands:

For a permanent tablespace other than SYSTEM, you can specify EXTENT MANAGEMENT LOCAL in the CREATE TABLESPACE command.

For a temporary tablespace, you can specify EXTENT MANAGEMENT LOCAL in the CREATE TEMPORARY TABLESPACE command.

Advantages of locally managed tablespaces:

Locally managed tablespaces have the following advantages over dictionary-managed tablespaces:

Local management avoids recursive space management operations. This can occur in dictionary-managed tablespaces if consuming or releasing space in an extent results in another operation that consumes or releases space in an undo segment or data dictionary table.

Because locally managed tablespaces do not record free space in data dictionary tables, they reduce contention on these tables.

Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.

The sizes of extents that are managed locally can be determined automatically by the system.

Changes to the extent bitmaps do not generate undo information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).

12 of 48

Dictionary-Managed Tablespaces

Extents are managed in the data dictionary
Each segment stored in the tablespace can have a different storage clause
Coalescing required

CREATE TABLESPACE userdata

DATAFILE '/u01/oradata/userdata01.dbf'

SIZE 500M EXTENT MANAGEMENT DICTIONARY

DEFAULT STORAGE

(initial 1M NEXT 1M PCTINCREASE 0);

8-12

13 of 48

Undo Tablespace

Used to store undo segments
Cannot contain any other objects
Extents are locally managed
Can only use the DATAFILE and EXTENT MANAGEMENT clauses

CREATE UNDO TABLESPACE undo1

DATAFILE '/u01/oradata/undo01.dbf' SIZE 40M;

8-13

14 of 48

Temporary Tablespaces

Used for sort operations
Cannot contain any permanent objects
Locally managed extents recommended

CREATE TEMPORARY TABLESPACE temp

TEMPFILE '/u01/oradata/temp01.dbf' SIZE 500M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;

8-14

15 of 48

8-15

16 of 48

8-16

17 of 48

Default Temporary Tablespace

Specifies a database-wide default temporary tablespace
Eliminates using SYSTEM tablespace for storing temporary data
Can be created by using:

CREATE DATABASE

Locally managed

ALTER DATABASE

ALTER DATABASE

DEFAULT TEMPORARY TABLESPACE temp;

8-17

Default Temporary Tablespace

When creating a database without a default temporary tablespace the SYSTEM tablespace is assigned to any user created without a TEMPORARY TABLESPACE clause. Also a warning is placed in the alert_sid.log stating that the SYSTEM tablespace is the default temporary tablespace. Creating a default temporary tablespace during database creation prevents the SYSTEM tablespace from being used for temporary space.

After database creation, a default temporary tablespace can be set by creating a temporary tablespace and then altering the database.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Once defined, users who are not explicitly assigned to a temporary tablespace are assigned to the default temporary tablespace.

The default temporary database can be changed at any time by using the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE command. When the default temporary tablespace is changed, all users assigned the default temporary tablespace are reassigned to the new default.

18 of 48

Creating a Default Temporary Tablespace

During database creation:

�

CREATE DATABASE DBA01

LOGFILE

GROUP 1 ('/$HOME/ORADATA/u01/redo01.log') SIZE 100M,

GROUP 2 ('/$HOME/ORADATA/u02/redo02.log') SIZE 100M,

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXLOGHISTORY 1

MAXDATAFILES 100

MAXINSTANCES 1

DATAFILE '/$HOME/ORADATA/u01/system01.dbf' SIZE 325M

UNDO TABLESPACE undotbs

DATAFILE '/$HOME/ORADATA/u02/undotbs01.dbf' SIZE 200

DEFAULT TEMPORARY TABLESPACE temp

TEMPFILE '/$HOME/ORADATA/u03/temp01.dbf' SIZE 4M

CHARACTER SET US7ASCII

8-18

19 of 48

Creating a Default Temporary Tablespace

After database creation:

To find the default temporary tablespace for the database query DATABASE_PROPERTIES

ALTER DATABASE

DEFAULT TEMPORARY TABLESPACE default_temp2;

SELECT * FROM DATABASE_PROPERTIES;

8-19

20 of 48

8-20

21 of 48

Restrictions on Default Temporary Tablespace

Default temporary tablespaces cannot be:

Dropped until after a new default is made available
Taken offline
Altered to a permanent tablespace

8-21

Restrictions on Default Temporary Tablespace

Dropping a Default Temporary Tablespace

You cannot drop the default temporary tablespace until after a new default is made available. The ALTER DATABASE command must be used to change the default temporary tablespace to a new default. The old default temporary tablespace is then dropped only after a new default temporary tablespace is made available. Users assigned to the old default temporary tablespace are automatically reassigned to the new default temporary tablespace.

Changing the Type of a Default Temporary Tablespace

Because a default temporary tablespace must be either the SYSTEM tablespace or a temporary tablespace, you cannot change the default temporary tablespace to a permanent type.

Taking Default Temporary Tablespace Offline

Tablespaces are taken offline to make that part of the database unavailable to other users (for example, an offline backup, maintenance, or making a change to an application that uses the tablespace). Because none of these situations apply to a temporary tablespace, you cannot take a default temporary tablespace offline.

22 of 48

Read Only Tablespaces

Use the following command to place a tablespace in read only mode

Causes a checkpoint
Data available only for read operations
Objects can be dropped from tablespace

ALTER TABLESPACE userdata READ ONLY;

8-22

Read Only Tablespaces

The ALTER TABLESPACE [tablespace]READ ONLY command places the tablespace in a transitional read only mode. In this transitional state, no further write operations can occur in the tablespace except for the rollback of existing transactions that previously modified blocks in the tablespace. After all of the existing transactions have been either committed or rolled back, the read only command completes, and the tablespace is placed in read only mode.

You can drop items, such as tables and indexes, from a read only tablespace, because these commands affect only the data dictionary. This is possible because the DROP command updates only the data dictionary, but not the physical files that make up the tablespace. For locally managed tablespaces, the dropped segment is changed to a temporary segment, to prevent the bitmap from being updated. To make a read only tablespace writable, all of the datafiles in the tablespace must be online. Making tablespaces read only causes a checkpoint on the datafiles of the tablespace.

23 of 48

8-23

24 of 48

8-24

25 of 48

Taking a Tablespace Offline

Not available for data access
Tablespaces that cannot be taken offline:

SYSTEM tablespace
Tablespaces with active undo segments
Default temporary tablespace

To take a tablespace offline:

To bring a tablespace online:

ALTER TABLESPACE userdata OFFLINE;

ALTER TABLESPACE userdata ONLINE;

8-25

Taking a Tablespace Offline

A tablespace is normally online so that the data contained within it is available to database users. However, the database administrator might take a tablespace offline to:

Make a portion of the database unavailable, while allowing normal access to the remainder of the database

Perform an offline tablespace backup (although a tablespace can be backed up while online and in use)

Recover a tablespace or datafile while the database is open

Move a datafile while the database is open

Offline Status of a Tablespace

When a tablespace goes offline, Oracle does not permit any subsequent SQL statements to reference objects contained in that tablespace. Users trying to access objects in a tablespace that is offline receive an error.

When a tablespace goes offline or comes back online, the event is recorded in the data dictionary and in the control file. If a tablespace is offline when you shut down a database, the tablespace remains offline and is not checked when the database is subsequently mounted and reopened.

26 of 48

8-26

Taking a Tablespace Offline (continued)

The Oracle instance automatically switches a tablespace from online to offline when certain errors are encountered (for example, when the database writer process, DBWn, fails in several attempts to write to a datafile of the tablespace). The different error situations are covered in more detail in the course Oracle9i Database Administration Fundamentals II.

Taking Tablespaces Offline

Whenever the database is open, a database administrator can take any tablespace, except the SYSTEM tablespace or any tablespace with active undo segments or temporary segments, offline. When a tablespace is taken offline, the Oracle server takes all the associated datafiles offline.

ALTER TABLESPACE tablespace

{ONLINE |OFFLINE [NORMAL|TEMPORARY|IMMEDIATE|FOR RECOVER]}

where:

NORMAL: Flushes all blocks in all datafiles in the tablespace out of the SGA. This is the default. You need not perform media recovery on this tablespace before bringing it back online. Use the NORMAL clause whenever possible.

TEMPORARY: Performs a checkpoint for all online datafiles in the tablespace even if some files could not be written. Any offline files may require media recovery.

IMMEDIATE: Does not ensure that tablespace files are available and does not perform a checkpoint. You must perform media recovery on the tablespace before bringing it back online.

FOR RECOVER: Takes tablespaces offline for tablespace point-in-time recovery.

27 of 48

8-27

28 of 48

Changing Storage Settings

Using ALTER TABLESPACE command to change storage settings:

Storage settings for locally managed tablespaces cannot be altered.

ALTER TABLESPACE userdata MINIMUM EXTENT 2M;

ALTER TABLESPACE userdata

DEFAULT STORAGE (INITIAL 2M NEXT 2M

MAXEXTENTS 999);

8-28

29 of 48

8-29

30 of 48

Resizing a Tablespace

A tablespace can be resized by:

Changing the size of a datafile:

Automatically using AUTOEXTEND
Manually using ALTER TABLESPACE

Adding a datafile using ALTER TABLESPACE

8-30

31 of 48

Enabling Automatic Extension �of Datafiles

Can be resized automatically with the following commands:

CREATE DATABASE
CREATE TABLESPACE
ALTER TABLESPACE … ADD DATAFILE

Example:

Query the DBA_DATA_FILES view to determine whether AUTOEXTEND is enabled.

CREATE TABLESPACE user_data

DATAFILE

'/u01/oradata/userdata01.dbf' SIZE 200M

AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

8-31

32 of 48

8-32

Enabling Automatic Extension of Datafiles (continued)

Specifying AUTOEXTEND for a New Datafile (continued)

Use the ALTER DATABASE command to modify a datafile and enable automatic extension:

ALTER DATABASE DATAFILE filespec [autoextend_clause]

autoextend_clause:== [ AUTOEXTEND { OFF|ON[NEXT integer[K|M]] [MAXSIZE UNLIMITED | integer[K|M]] } ]

where:

AUTOEXTEND OFF: Disables the automatic extension of the datafile

AUTOEXTEND ON: Enables the automatic extension of the datafile

NEXT: Specifies the disk space to allocate to the datafile when more extents are required

MAXSIZE: Specifies the maximum disk space allowed for allocation to the datafile

UNLIMITED: Sets no limit on allocating disk space to the datafile

Specifying AUTOEXTEND for an Existing Datafile

Use the SQL command ALTER DATABASE to enable or disable automatic file extension for existing datafiles:

ALTER DATABASE [database]

DATAFILE 'filename'[, 'filename']... autoextend_clause

Determining if AUTOEXTEND is Enabled or Disabled

Query DBA_DATA_FILES view to determine whether AUTOEXTEND is enabled and examine the AUTOEXTENSIBLE column.

SQL> select tablespace_name, file_name, autoextensible

2 from dba_data_files;

TABLESPACE_NAME FILE_NAME AUTOEXTENSIBLE

--------------- ------------------------ --------------

SYSTEM /home/dba01/ORADATA/u01/system01.dbf YES

DATA01 /home/dba01/ORADATA/u04/data01.dbf NO

USERS /home/dba01/ORADATA/u03/users01.dbf NO

INDX /home/dba01/ORADATA/u06/indx01.dbf NO

SAMPLE /home/dba01/ORADATA/u02/sample01.dbf YES

DATA02 /home/dba01/ORADATA/u03/data02.dbf NO

INDEX01 /home/dba01/ORADATA/u06/index01.dbf YES

UNDO2 /home/dba01/ORADATA/u01/UNDO2.dbf NO

8 rows selected.

33 of 48

8-33

34 of 48

Manually Resizing a Datafile�

Manually increase or decrease a datafile size using ALTER DATABASE
Resizing a datafile adds more space without adding more datafiles
Manual resizing of a datafile reclaims unused space in database
Example:

ALTER DATABASE

DATAFILE '/u03/oradata/userdata02.dbf'

RESIZE 200M;

8-34

35 of 48

Adding Datafiles to a Tablespace

Increases the space allocated to a tablespace by adding additional datafiles
ADD DATAFILE clause is used to add a datafile
Example:

ALTER TABLESPACE user_data

ADD DATAFILE '/u01/oradata/userdata03.dbf'

SIZE 200M;

8-35

36 of 48

8-36

37 of 48

Methods for Moving Datafiles�

ALTER TABLESPACE

Tablespace must be offline
Target datafiles must exist

Steps to rename a datafile:

Take the tablespace offline.
Use an OS command to move or copy the files.
Execute the ALTER TABLESPACE RENAME DATAFILE command.
Bring the tablespace online.
Use an OS command to delete the file if necessary.

ALTER TABLESPACE userdata RENAME

DATAFILE '/u01/oradata/userdata01.dbf'

TO '/u02/oradata/userdata01.dbf';

8-37

38 of 48

Methods for Moving Datafiles�

ALTER DATABASE

Database must be mounted
Target datafile must exist

ALTER DATABASE RENAME

FILE '/u01/oradata/system01.dbf'

TO '/u03/oradata/system01.dbf';

8-38

39 of 48

8-39

40 of 48

Dropping Tablespaces

Cannot drop a tablespace if it:

Is the SYSTEM tablespace
Has active segments

INCLUDING CONTENTS drops the segments
INCLUDING CONTENTS AND DATAFILES deletes datafiles
CASCADE CONSTRAINTS drops all referential integrity constraints

DROP TABLESPACE userdata

INCLUDING CONTENTS AND DATAFILES;

8-40

41 of 48

8-41

Dropping Tablespaces (continued)

Guidelines:

A tablespace that still contains data cannot be dropped without the INCLUDING CONTENTS option. This option may generate a lot of undo when the tablespace contains many objects.

After a tablespace has been dropped, its data is no longer in the database.

When a tablespace is dropped, only the file pointers in the control file of the associated database are dropped. The operating system files still exist and must be deleted explicitly using the appropriate operating system command unless the AND DATAFILES clause is used or datafiles are OMF.

Even if a tablespace is switched to read only, it can still be dropped, along with segments within it.

It is recommended that you take the tablespace offline before dropping it to ensure that no transactions access any of the segments in the tablespace.

42 of 48

8-42

43 of 48

Managing Tablespaces Using OMF�

Define the DB_CREATE_FILE_DEST parameter in one of the following ways:

Initialization parameter file
Set dynamically using ALTER SYSTEM command

When creating the tablespace:

Datafile is automatically created and located in DB_CREATE_FILE_DEST
Default size is 100 MB
AUTOEXTEND is set to UNLIMITED

ALTER SYSTEM SET

db_create_file_dest = '/u01/oradata/dba01';

8-43

44 of 48

Managing Tablespaces with OMF

Creating an OMF tablespace:

Adding an OMF datafile to an existing tablespace:

Dynamically changing default file location:

Dropping a tablespace includes deleting OS files:

CREATE TABLESPACE text_data DATAFILE SIZE 20M;

ALTER TABLESPACE text_data ADD DATAFILE;

ALTER SYSTEM SET

db_create_file_dest = '/u01/oradata/dba01';

8-44

Managing Tablespaces with OMF

Creating OMF Tablespaces

Creating tablespaces with OMF does not require a DATAFILE clause. Tablespaces omitting the DATAFILE clause take the defaults of a 100 MB datafile set to AUTOEXTEND with an unlimited MAXSIZE. Optionally a file size may be specified.

CREATE TABLESPACE tablespace

[ DATAFILE [ filename ] [ SIZE integer [K|M] ] ];

Adding Datafiles to OMF Tablespaces

A datafile can be added to an existing tablespace. The ADD DATAFILE command no longer requires file specification.

Dynamically Changing Default File Locations

DB_CREATE_ONLINE_LOG_DEST_n should be set to prevent log files and control files from being placed with datafiles. The destination can be changed dynamically using the ALTER SYSTEM SET command.

Dropping OMF Tablespaces

Datafiles from OMF created tablespaces are deleted at the OS level when the associated tablespace is dropped.

45 of 48

Obtaining Tablespace Information

Obtaining tablespace and datafile information can be obtained by querying the following:

Tablespaces:

DBA_TABLESPACES
V$TABLESPACE

Datafile information:

DBA_DATA_FILES
V$DATAFILE

Temp file information:

DBA_TEMP_FILES
V$TEMPFILE

8-45

46 of 48

Summary

In this lesson, you should have learned how to:

Use tablespaces to separate data
Create various types of tablespaces
Manage tablespaces
Manage tablespaces using OMF

8-46

47 of 48

Practice 8 Overview

This practice covers the following topics:

Creating tablespaces
Modifying tablespaces
Configuring for and creating a tablespace using OMF

8-47

48 of 48

8-48

Practice 8: Managing Tablespaces and Datafiles

1 Create permanent tablespaces with the following names and storage:

a DATA01 data dictionary-managed.

b DATA02 locally managed with uniform sized extents. Ensure that every used extent

size in the tablespace is a multiple of 100 KB.

c INDEX01 locally managed with uniform sized extents of 4K. Enable automatic extension of 500 KB when more extents are required with a maximum size of 2 MB. d RONLY for read only tables with the default storage. DO NOT make the tablespace

read only at this time.

e Display the information from the data dictionary.

Hint: Information about tablespaces can be viewed using any of the following queries.

- DBA_TABLESPACES

- V$TABLESPACE

- V$DATAFILE

2 Allocate 500 KB more disk space to tablespace DATA02. Verify the result.

3 Relocate tablespace INDEX01 to subdirectory u06. Verify relocation and status of INDEX01.

Hints:

- Take the INDEX01 tablespace offline.

- Use V$DATAFILE to verify status.

- Use operating system move command to move the tablespace to u06.

- Use ALTER TABLESPACE to relocate the tablespace.

- Place the INDEX01 tablespace online.

- Use V$DATAFILE to verify status.

4 a Create a table in tablespace RONLY. Make tablespace RONLY read only. Run a query to verify it.

b Attempt to create an additional table TABLE2. Drop the first created table, TABLE1. What happens?

5 Drop tablespace RONLY and the associated datafile. Verify it.

6 Set DB_CREATE_FILE_DEST to $HOME/ORADATA/u05 in memory only. Create

tablespace DATA03 size 5 MB. Do not specify a file location. Verify the creation of the

datafile.