Data Files

The Following Tablespaces exists in Oracle 10g Database:

§ SYSTEM Tablespace

§ SYSAUX Tablespace

§ TEMPORARY Tablespace (TEMP)

§ UNDO Tablespace (UNDOTBS1)

§ USERS Tablespace (default users tablespace created)

SYSTEM Tablespace:

All major database systems include something called a Data Dictionary. A Data Dictionary describes the contents of the database. Which tables do I own? What columns are in those tables? Do I have permissions to view other tables? Do these tables have indexes? Where these tables are physically located? What code runs when I execute a stored procedure? The Data Dictionary contains the answer to all of these questions and more. System Tablespace contain these data dictionary.

SYSAUX Tablespace:

SYSAUX is the name of the compulsory tablespace, introduced in Oracle 10g, to support optional database components (called occupants) like AWR, Statspack, Oracle Streams, etc.

TEMPORARY Tablespace:

Temporary Tablespace are used to store short life span data. For example: sort result.

UNDO Tablespace:

UNDO Tablespace used for capturing “old imeage” date for rollback.

USER Tablespace:

A default user tablespace allows defining a tablespace that will be the default tablespaces for users created objects.

Creating and Managing Tablespace:

In oracle 10g big file tablespaces can be created. Bigfile tablespaces are built on a single datafile (or temp file), which can be as many as 2^32data blocks in size. So, a bigfile tablespace that uses 8KB data blocks can be as much as 32TB in size.

It is usually use in very large database. When there is very large database with thousand of datafile, then it will take long time for updating datafile header, such as checkpoint.

SQL> create bigfile tablespace users2

datafile 'C:\oracle\product\10.2.0\oradata\dba12\USERS02.DBF' size 25G;

In smallfile tablespace Each datafile can be as many as 2^22 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 Tablespace created.

SQL> create tablespace users1

datafile 'C:\oracle\product\10.2.0\oradata\dba12\USERS03.DBF' size 25M;

Tablespace created.

Locally Managed Tablespace:

Using Locally Managed Tablespace, each tablespace manages its own free and used space within a bitmap structure stored in one of the tablespace's data files.

When creating a locally managed tablespace, you can specify the extent allocation method to be used.

AUTOALLOCATE - means that the extent sizes are managed by Oracle.

This might help conserve space but will lead to fragmentation. This is usually recommended for small tables or in low managed systems.

SQL> CREATE TABLESPACE test

2 DATAFILE 'C:\oracle\product\10.2.0\oradata\dba12\TEST01.DBF' SIZE 2M

3 EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

UNIFORM - specifies that the extent allocation in the tablespace is in a fixed uniform size. The extent size can be specified in M or K. The default size for UNIFORM extent allocation is 1M. Using uniform extents usually minimizes fragmentation and leads to better overall performance

SQL> CREATE TABLESPACE test1

2 DATAFILE 'C:\oracle\product\10.2.0\oradata\dba12\TEST02.DBF' SIZE 2M

3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100K;

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

· Local management of extents tracks adjacent free space, eliminating coalescing free extents.

· Reliance on data dictionary is reduced. This minimizes access to the data dictionary, potentially, improving performance and availability.

Dictionary Managed Tablespace:

For a tablespace that uses the data dictionary to manage its extents, the Oracle server updates the appropriate tables in the data dictionary whenever an extent is allocated or dealloated.

CREATE TABLESPACE USERDATA

DATAFILE 'C:\oracle\product\10.2.0\oradata\dba12\TEST03.DBF' SIZE 100M EXTENT MANAGEMENT DICTIONARY

DEFAULT STORAGE (initial 1M NEXT 1M);

Segment in dictionary managed tablespaces can have a customized storage, this is more flexible than locally managed tablespaces but much less efficient,

Changing the storage settings:

The storage setting for dictionary managed tablespaces can be altered but locally managed tablespace can not be altered.

ALTER TABLESPACE userdata MINIMUM EXTENT 2M;

ALTER TABLESPACE userdata

DEFAULT STORAGE (

INITIAL 2M

NEXT 2M

MAXEXTENT 999);

Creating UNDO Tablespace:

CREATE UNDO TABLESPACE undo01

DATAFILE ‘/u01/oradata/undo101.dbf’ SIZE 50M;

Creating TEMPORARY Tablespace:

SQL> create temporary tablespace temp2

2 tempfile 'C:\oracle\product\10.2.0\oradata\dba12\TEMP02.DBF' SIZE 5M;

SQL> select username,temporary_tablespace from dba_users where username='SYS';

USERNAME TEMPORARY_TABLESPACE

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

SYS TEMP

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

ALTER DATABSE DEFAULT TEMPORARY TABLESPACE temp2;

Restrictions of temporary tablespace:

  1. You can not drop a default temporary tablespcae.

  2. You can not take offline default temporary tablespace.

  3. Temporary tablespace can not be make a permanent type tablespace.

Taking Tablespace ONLINE/OFFLINE:

Taking a Tablespace OFFLINE:

ALTER TABLESPACE userdata OFFLINE;

Taking a Tablespace ONLINE:

ALTER TABLESPACE userdata ONLINE;

Some Tablespace can not be taken OFFLINE:

  1. SYSTEM Tablespace

  2. SYSAUX Tablespae

  3. Default Temporary Tablespace

  4. Tablespace with active undo segment

Taking a Tablespace READ ONLY:

SQL>ALTER TABLESPACE userdata READ ONLY;

Taking a Tablespace READ WRITE:

SQL>ALTER TABLESPACE userdata READ WRITE;

Dropping Tablespaces:

SQL>DROP TABLESPACE userdata

INCLUDING CONTENTS AND DATAFILE;

Changing the size of the Datafile

SQL> ALTER DATABASE

DATAFILE 'C:\oracle\product\10.2.0\oradata\dba12\USERS02.DBF' RESIZE 15M;

Enable Automatic Extension

SQL> CREATE TABLESPACE test3

DATAFILE 'C:\oracle\product\10.2.0\oradata\dba12\TEST03.DBF' SIZE 10M

AUTOEXTEND ON NEXT 5M MAXSIZE 30M;

Adding DATA Files to a Tablespace

SQL> ALTER tablespace test

ADD DATAFILE 'C:\oracle\product\10.2.0\oradata\dba12\USERS02B.DBF' SIZE 10M;

Changing Location of Data Files

  1. Take the tablespace offline

  2. Copy the files or use the operating system command to move the file

  3. Execute the following command:

ALTER TABLESPACE userdata

RENAME

DATAFILE ‘/u01/oradata/userdata01.dbf’

TO ‘/u01/oradata/userdata/userdata01.dbf’;

  1. Bring the tablespace online

  2. Delete the past location file if mandatory.

Or

  1. Shut down the databse.

  2. Copy the files or use the operating system command to move the file

  3. Mount the Database

  4. Execute the following command:

ALTER DATABSE RENAME

FILE ‘/u01/oradata/system01.dbf’

TO ‘/u03/oradata/system01.dbf’;

  1. Open the database.

Convert Dictionary Managed Tablespace to Locally Managed Tablespace:

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('USERS');

Obtaining Tablespace Information

    • Tablespace Information

Ø DBA_TABLESPACES

Ø V$TABLESPACES

Ø DBA_USERS

    • Data file Information

Ø DBA_DATA_FILES

Ø V$DATAFILE

    • Temp file Information

Ø DBA_TEMP_FILES

Ø V$TEMPFILE