Chapter 6 : MANAGING TABLESPACES AND DATAFILES

Chapter 6 : MANAGING TABLESPACES AND DATAFILES

CHAPTER # 6

MANAGING TABLESPACES AND DATAFILES

LOGICAL STRUCTURE OF DATABASE

DIAGRAM

DATABASE

TABLESPACES  -->  DATAFILES

SEGMENTS

EXTENTS

ORACLE BLOCK / DATA BLOCK / LOGICAL BLOCK

OPERATING SYSTEM BLOCK

When we creates a segments (means TABLE, INDEX, CLUSTER) at least one extent is created

TABLESPACES:

1. The data in an oracle database is stored in tablespaces
2. An oracle database can be logically grouped into small logical areas of spaces known as tablespaces.
3. A tablespace can belongs to only one database at a time
4. Each tablespace consist of one or more operating system file, which are called data files.
5. A tablespace may contain one or more segments
6. tablespace can be brought online while the database is running

SEGMENTS:

1. A segment is a space allocated for a specific logical storage structure within a tablespace.
2. Each segment is made up of one or more extents.
3. A segment cannot be span tablespaces. However segments can span multiple data files that belong to the same tablespace.

EXTENTS:

1. Space is allocated to a segment by extents.
2. One or more extent make up a segment.
3. When segment is created it consist at least one extent. As the segment grows extents are added to the segments.
4. An extents is a set of contiguous oracle blocks.
5. An extent cannot span data files and there fore it must exist in one datafile.

DATABLOCKS:

1.      The oracle server manages the storage space in the data files in unit called ORACLE BLOCK / DATABLOCK

2.      At the finest level of granularity, the data in an oracle database is stored in the data blocks.

3.      Oracle data blocks are the smallest unit of storage that the oracle server can allocate read or write

4.      One data block correspond to one or more operating system block, allocated from an existing data file.

5.      The standard data block size for an oracle database is specified by the DB_BLOCK_SIZE initialization parameter when the database is created.

6.      Data block is not less than operating system block it can be either EQUAL or MULTIPLE of operating system block to avoid un necessary I/O.

7.      Minimum oracle block size = 64K

TABLESPACES AND DATA FILES

Oracle stores data logically in tablespaces and physically in data files.

• TABLESPACES:

- Can belongs to only one database at a time

- Consist of one or more data files

- Are further divided into logical storage of unit

• DATA FILES:

- Can belongs to only one tablespace and one database

      - Are repository (where information is stored) for schema object data.

DATABASE, TABLESPACES AND DATAFILES

Are closely related, but they have important differences.

• An ORACLE DATABASE consists of one or more logical storage unit called tablespaces, which collectively store all of the database’s data.
• Each TABLESPACE in an oracle database consists of one or more files called data files, which are physical structures that conform to the operating system in which oracle is running.
• A database’s data is collectively stored in the DATA FILES that constitute each tablespace of the database

For example

The simplest oracle database would have one tablespace and one data file.

Another database can have three tablespaces, each consisting of two data files (for a total of six data files)

TYPES OF TABLESPACES

There are two types of table spaces

1. System Tablespaces
2. Non-system Tablespaces

SYSTEM TABLESPACES:

• Created with the database
• Required in all database
• Contains the data dictionary, including stored program units.
• Contains the SYSTEM undo segment
• Should not contain user data, although it is allowed

NON-SYSTEM TABLESPACES:

·         Enable more flexibility in database administration

·         Separate UNDO, TEMPORARY, APPLICATION DATA,  & APPLICATION INDEX segments.

·         Separate data by backup requirements

·         Separate dynamic and statistics data

·         Control the amount of space allocated to the user’s objects.

CREATING A TABLESPACE

PRACTICAL #

SQL > CREATE TABLESPACE ABC

            DATAFILE 'D:\ORACLE\ORADATA\ORCL\ABC01.DBF' SIZE 20M

When we create any tablespace we also define optional clause like

1.      MINIMUM EXTENT:

This ensure that every used extent size in the tablespace. Size define in K kilobytes and M megabytes

2.      BLOCKSIZE:

Specifies a non standard block size for the tablespace.

3.      LOGGING | NOLOGGING

This specifies that, by default all tables, indexes, and partition within the tablespace have all changes written to online redo log files. LOGGING IS THE DEFAULT.

Nologging means do not write all changes to online redo log files.

4.      DEFAULT:

Specifies the default storage parameter for all object created in the tablespace creation.

5.      OFFLINE:

This makes the tablespace unavailable immediately after creation.

SEE MORE DETAIL IN   PAGE   8-6

SPACE MANGEMENT IN TABLESPACES

Tablespace allocate space in extents. Tablespace can be created to use one of the following two different methods of keeping track of free and used space.

• Locally managed tablespaces
• Dictionary managed tablespaces

LOCALLY MANAGED TABLESPACES:

The extents are managed within the tablespace via bitmaps.

Each bit in the bitmap corresponds to a block or a group of blocks.

When an extent is allocated or freed for reuse, the oracle manages the bitmap values to show the new status of the blocks.

Locally managed is the default beginning with ORACLE9i

In locally managed tablespace

Reduce contention on data dictionary tables

No undo generated when space allocation or deallocation occurs

No coalescing required

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

By default a tablespace is locally managed.

PRACTICAL #

CREATE TABLESPACE ABCD

DATAFILE ‘D:\ORACLE\ORADATA\ORCL\ABCD01.DBF’ SIZE 50M

EXTENT MANAGEMENT [ DICTIONARY | LOCAL]

[AUTOALLOCATE | UNIFORM SIZE integer K|M ] ]

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

LOCAL: Specifies tablespace is locally managed via bitmap

               NOTE: If you specify LOCAL you cannot specify DEFAULT storage_clause, 

                            MINIMUM EXTENT, or TEMPORARY

AUTOALLOCATE: specifies that the TB is system manages. This is DEFAULT

UNIFORM: Specifies that the TB is managed with uniform extent size. The default size

                     100 MB

ADVANTAGES OF LOCALLY MANAGED TABLESPACES:

Locally managed tablespaces have the following advantages over dictionary managed tablespaces.

1.      Local management avoid recursive space management operations.

2.      Locally managed tablespace do not record free space in the data dictionary tables, they reduce contention on these tables.

3.      the size of extents are managed locally can be determined automatically by the system.

4.      Changes to the extent bitmaps do not generate undo information.

PRACTICAL #

SQL > CREATE TABLESPACE ABCD

            DATAFILE 'D:\ORACLE\ORADATA\ORCL\ABCD01.DBF' SIZE 50M

            EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

DICTIONARY MANAGED TABLESPACE:

The extents are managed by the data dictionary.

The oracle server updates the appropriates table in the data dictionary whenever an extent is allocated or deallocated

Each segments stored in the tablespace can have a different storage clause. This storage is more flexible but much less efficient.

Coalescing is required.

We can not create dictionary managed tablespace in single PC.

PRACTICAL #

CREATE TABLESPACE USERDATA

DATAFILE ‘D:\ORACLE\ORADATA\ORCL\USERDATA)!.DBF’ SIZE 50M

EXTENT MANAGEMENT DICTIONARY

DEFAULT STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0);

MIGRATING A DICTIONARY MANAGED SYSTEM TABLESPACE

Use the following steps to convert your SYSTEM tablespace from dictionary managed to locally manage:

PRACTICAL #

1. STEP # 1:

Make a complete backup of your database.

2. STEP # 2:

Ensure that the databse has a default temporary tablespace that is not SYSTEM. The temporary tablespace is created using the CREATE TEMPORARY TABLESPACE command.

3. STEP # 3:

Eliminate any undo (rollback ) segments in dictionary-managed tablespace.

4. STEP # 4:

There should be atleast one online undo segment in a locally managed tablespace, or an undo tablespace should be online.

5. STEP #  5:

All tablespace other than the tabl;espace containing the undo space and the default temporary tablespace should be placed in READ ONLY mode.

6. STEP # 6

Startup the instance in restriceted mode.

7. Migrate the SYSTEM tablespace by using:

EXECUTE

DBMS_SPACE_ADMIN.

TABLESPACE_MIGRATE_TO_LOCAL(‘SYSTEM’);

NOTE : Any NON-SYSTEM dictionary managed tablespace that is not migrated to locally managed prior to migrate the SYSTEM tablespace cannot be altered to READ WRITE. The tablespace will remain READ ONLY.

Only locally managed tablespace can be altered to READ WRITE after the migration of the SYSTEM tablespace.

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

PRACTICAL #

CREATE UNDO TABLESPACE UNDO1

DATAFILE ‘D:\ORACLE\ORADATA\ORCL\UNDO101.DBF’ size 4M;

TEMPORARY TABLESPACE

Used for sort operation

Can be shared by multiple users

Cannot contain any permanent objects

Locally managed extents recommended

Temporary tablespace provide performance improvements when you have multiple sorts that are too large to fit into the memory.

NOTE: Nonstandard block size cannot be specified when creating temporary tablespace.

PRACTICAL #

CREATE TEMPORARY TABLESPACE TEMP1

TEMPFILE 'D:\ORACLE\ORADATA\ORCL\TEMP101.DBF' SIZE 20M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;

NOTE: Locally managed temporary tablespace have temporary data files (temp files), which are similar to ordinary data files except that:

• Temp files are always set to NOLOGGING mode
• You cannot make a temp file read only
• You cannot rename a temp file
• You cannot create a temp file wit the ALTER DATABASE command.
• Temp files are required for read-only databases.
• Media recovery does not recover temp file.

DEFAULT TEMPORARY TABLESPACE:

When creating a database without a default temporary tablespace the SYSTEM tablespace is assigned to any user created without TEMPORARY TABLESPACE clause. Also a warning is placed in 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.

PRACTICAL #

See practical in chapter no 4   database creation MANUALLY

AFTER DATABASE CREATION

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

PRACTICAL #

STEP # 1:      Create temporary tablespace

CREATE TEMPORARY TABLESPACE TEMP1

TEMPFILE 'D:\ORACLE\ORADATA\ORCL\TEMP101.DBF' SIZE 20M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;

STEP # 2:   Assign temporary tablespace by altering the database

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

To find the default temporary tablespace for the database query DATABASE_PROPERTIES.

Select * from DATABASE_PROPERTIES;

RESTRICTIONS ON DEFAULT TEPORARY TABLESPACE

DROPPING A DEFAULT TEMPORARY TABLE SPACE:

You cannot drop the default temporary table space until after a new default ismade available.

CHANGING THE TYPE OF 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:

Tablespace 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 ). Because none of these situations applies to a temporary tablespace, that sway you cannot take a default temporary tablespace offline.

READ ONLY TABLESPACE:

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

ALTER TABLESPACE TOOLS READ ONLY;

In read-only mode data is available only for read operations no further write operation 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, or the tablespace is placed in read-only mode.

You can drop items, such as tables, indexes, from a read-only tablespace, because these command effect only the data dictionary, not the physical files that makes up the tablespace.

For locally managed tablespaces, the drop segment is changed to temporary segment, to prevent the bitmap from being updated.

To make a read-only tablespace writable, all the data files in the tablespace must be online.

TAKING A TABLESPACE OFFLINE

A tablespace can be set OFFLINE means tablespace not available for data access.

However a DBA might take a tablespace offline to perform some tasks

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

·         Perform an offline tablespace backup

·         Recover a tablespace or data file while the database is opened

·         Move a data file while the database is opened

NOTE:  Tablespace that cannot be offline

·         SYSTEM tablespace

·         Tablespace with active undo segments

·         Default temporary tablespace

When a tablespace goes offline and comeback online the event is recorded in the data dictionary and in the control file.

If a tablespace is offline when you shutdown a database, the tablespace remain offline and is not checked when database is open.

SEE MORE DETAIL IN PAGE 8-27

CHANGING STORAGE SETTINGS

Using alters tablespace command to change storage settings:

PRACTICAL #

ALTER TABLESPACE USERS MINIMUN EXTENT 2M;

ALTER TABLESPACE TOOLS

DEFAULT STORAGE (INITIAL 2M  NEXT 2M   MAXEXTENTS 999);

                                                                       

NOTE:  Storage setting for locally managed tablespaces can not be altered.

RESIZING A TABLESPACE

 A TABLESPACE CAN BE RESIZE

PRACTICAL #

1. Automatically during tablespace creation

CREATE TABLESPACE ABC

DATAFILE 'D:\ORACLE\ORADATA\ORCL\ABC01.DBF' SIZE 5M

AUTOEXTEND ON NEXT 2M MAXSIZE 200M;

2. By specifying AUTOEXTEND after tablespace creation

            ENABLING AUTOMATIC EXTENSION OF DATAFILES

 QUERY the DBA_DATA_FILE view to determine whether AUTOEXTEND is ENABLED.

  

STEP # 1: Create a tablespace

CREATE TABLESPACE XYZ

DATAFILE 'D:\ORACLE\ORADATA\ORCL\XYZ01.DBF' SIZE 2M

STEP # 2:  Alter tablespace by ALTER DATABASE command

ALTER DATABASE

DATAFILE 'D:\ORACLE\ORADATA\ORCL\XYZ01.DBF'

AUTOEXTEND ON NEXT 2M;

Benefits of using auto extend clause:

• Reduces need for immediate intervention when a tablespace runs out of space
• Ensures application will not halt because of failure to allocate extents

3. MANUALLY BY RESIZING THE EXISTING DATA FILE

Manually increase or decrease a data file size using ALTER DATABASE command

Benefit is that increase more space in data file without adding new data file.

ALTER DATABASE

DATAFILE 'D:\ORACLE\ORADATA\ORCL\XYZ01.DBF' RESIZE 10M;

IMPORTANT NOTE: If there are database objects stored above the specified size, then the data file size is decreased only to the last block of the last object in the data file.

4. ADD DATA FILE TO A TABLESPACE

Increase the space allocated to a tablespace by adding additional data file.

Add data file clause is used to add a data file.

ALTER TABLESPACE XYZ

ADD DATAFILE 'D:\ORACLE\ORADATA\ORCL\XYZ01B.DBF' SIZE 5M

METHOD FOR MOVING DATAFILES:

Depending on the type of tablespace, the DBA can move data files using one of the following two methods:

ALTER TABLESPACE COMMAND:

The following ALTER TABLESPACE command is applied only to data files in a non-system tablespace that does not contain

Active undo and temporary segments

For doing this   à Tablespace must be offline

                         à Target data files must exist.

PRACTICAL #

STEP # 1:    Take the tablespace offline

ALTER TABLESPACE XYZ OFFLINE;

STEP # 2:    Use operating system command to move or copy the file

STEP # 3:    Execute alter tablespace rename data file command

ALTER TABLESPACE XYZ RENAME

DATAFILE ‘D:\ORACLE\ORADATA\ORCL\XYZ01B.dbf’

TO

‘D:\ORACLE\ORADATA\XYZ01.DBF

STEP # 4: Bring tablespace online

ALTER TABLESPACE XYZ ONLINE;

STEP # 5:  use OS command to delete the file if necessary

ALTER DATABASE COMMAND:

The following ALTER DATABASE command is applied to move any type of data files.

Because system tablespace cannot be taken offline there fore you must use this method to move data files in the system tablespace 

For doing this   à Database must be mounted

                         à Target data files must exist.

PRACTICAL #

STEP # 1:    shutdown the database

SQL > SHUTDOWN;

STEP # 2:    Use operating system command to move or copy the file

STEP # 3:    Mount the database

STEP # 4: Execute alter database rename file command.

ALTER DATABASE RENAME

FILE ‘D:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF’

TO

‘D:\ORACLE\ORADATA\SYSTEM01.DBF

STEP # 4: Open the database

SQL > ALTER DATABASE OPEN;

DROPPING TABLESPACE:

You cannot drop a tablespace if it:

• SYSTEM tablespace
• Has active UNDO SEGMENTS

PRACTICAL #

A table space can be drop by using the command

SQL > DROP TABLESPACE XYZ ;

Using including contents you can drop any table with all segments

SQL > DROP TABLESPACE ABC

            INCLUDING CONTENTS;

 Using including contents and data files you can drop any table with OS files

SQL > DROP TABLESPACE ABC

            INCLUDING CONTENTS AND DATAFILES;

Using cascade constraint you can drop referential integrity constraints from table outside the tablespace.

SQL > DROP TABLESPACE ABC

            CASCADE CONSTRAINTS;

IMPORTANT NOTE :

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

MANAGING TABLESPACE USING OMF

Define the DB_CREATE_FILE_DEST parameter in one of the following ways:

1. Initialization parameter file
2. Set dynamically using ALTER SYSTEM command

ALTER SYSTEM SET 
DB_CREATE_FILE_DEST = ‘D:\ORACLE\ORADATA\ORCL\DB01.DBF’

When creating the tablespace

• Data file is automatically created and located in

DB_CREATE_FILE_DEST

• Default size is 100 MB
• Auto extend is set to unlimited

CREATING AN OMF TABLESPACE:

SQL > CREATE TABLESPACE ABCD DATAFILE SIZE 40M;

Adding an OMF data file in existing tablespace

SQL > ALTER TABLESPACE ABCD ADD DATAFILE;

DYNAMICALLY CHANGE DEFAULT LOCATION:

SQL > ALTER SYSTEM SET

                        DB_CREATE_FILE_DEST = ‘D:\ORACLE\ORADATA\DBA01’;

DROPPING A TABLESPACE INCLUDING DELETING OS FILES 

SQL > DROP TABLESPACE ABCD;

OBTAINING INFORMATION ABOUT TABLESPACES

DBA_TABLESPACES

V$TABLESPACE

OBTAINING INFORMATION ABOUT DATAFILES

DBA_DATAFILES

V$DATAFILE

OBTAINING INFORMATION ABOUT TEMPFILE

DBA_TEMPFILE

V$TEMPFILE

--------------------------------  END OF CHAPTER 06  -----------------------------

.