Datafiles and Tablespaces

Datafiles

Every datafile has two associated file numbers:

An absolute file number uniquely identifies a datafile in the database, Arelative file number uniquely identifies a datafile within a tablespace

At least one datafile is required for the SYSTEM tablespace of a database. You can add datafiles to tablespaces, subject to the datafile limits:

Operating system limit, Oracle system limit, Control file upper bound, Instance or SGA upper bound.

The first datafile must be at least 7M to contain the initial data dictionary and rollback segment. Tablespace location is determined by the physical location of the datafiles that constitute that tablespace. Datafiles should not be stored on the same disk drive that stores the database's redo log files. When creating a tablespace, you should estimate the potential size of the database objects and add sufficient files or devices accordingly. To add datafiles to a tablespace, you use the ALTER TABLESPACE...ADD DATAFILE statement. You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed. This will:

Reduce the need for immediate intervention when a tablespace runs out of space. Ensure applications will not halt because of running out of space.

You may find out if a datafile is auto-extensible by querying DBA_DATA_FILES. You can specify automatic file extension by specifying an AUTOEXTEND ON clause when you create datafiles. You can enable or disable automatic file extension for existing datafiles using the SQL statement ALTER DATABASE. You can manually resize a datafile using the SQL statement ALTER DATABASE. Offline datafiles cannot be accessed. Files of a read-only tablespace can independently be taken online or offline using the DATAFILE option of the ALTER DATABASE statement. If you want to bring a datafile online or take it offline, you must have the ALTER DATABASE system privilege.

To bring a datafile online or take it offline, the database must be opened in exclusive mode.

Tablespaces

You may use the SQL statement CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE to create a new tablespace You may use the ALTER TABLESPACE or ALTER DATABASE statements to alter the tablespace With Oracle8i, you can create locally managed tablespaces Locally managed tablespaces use bitmaps but NOT SQL dictionary tables to track used and free space. temporary tablespaces:

• improve the concurrence of multiple sort operations,
• reduce multiple sort operations overhead,
• avoid Oracle space management operations.

All tablespaces are initially created as read-write you may use the READ ONLY keywords in the ALTER TABLESPACE statement to change a tablespace to read-only you may use the READ WRITE keywords in the ALTER TABLESPACE SQL statement to change a tablespace to allow write operations SYSTEM tablespace can never be made read-only. Read-only tablespace:

prevents updates on all tables in the tablespace regardless of a user's update privilege level, eliminate the need to perform backup and recovery of large, static portions of a database, provide a means of completely protecting historical data.

Any tablespace except the SYSTEM tablespace can be dropped. Once a tablespace has been dropped, the tablespace's data is not recoverable. Back up the database completely immediately before and after dropping a tablespace When dropping a tablespace, only the file pointers in the control files are dropped. To free previously used disk space, delete the datafiles of the dropped tablespace using the appropriate operating system commands Partitioned Tables cannot be moved via transportable tablespaces when only a subset of the partitioned table is contained in the set of tablespaces.

Storage Parameters

PCTFREE

set the percentage of a block to be reserved for possible updates to rows that already are contained in that block, after the free space in a data block reaches PCTFREE, no new rows are inserted in that block until the percentage of space used falls below PCTUSED, can specify any integer between 0 and 99 (inclusive) for PCTUSED, the sum of PCTUSED and PCTFREE should not exceed 100.

smaller PCTFREE:

Reserves less room for updates, Allows inserts to fill the block more completely, suitable for a segment that is rarely changed.

larger PCTFREE:

Reserves more room for future updates, May require more blocks for the same amount of inserted data, May improve update performance, suitable for segments that are frequently updated.

PCTUSED

smaller PCTUSED: Reduces processing costs incurred during UPDATE and DELETE, Increases unused space.

larger PCTUSED: Improves space efficiency, Increases processing cost during INSERTs and UPDATEs.

Other parameters:

INITIAL - size, in bytes, of the first extent allocated when a segment is created

NEXT- size, in bytes, of the next incremental extent to be allocated for a segment

PCTINCREASE – percentage by which each incremental extent grows over the last incremental extent allocated for a segment 0 means all incremental extents are the same size it cannot be negative.

MINEXTENTS - total number of extents to be allocated when the segment is created

MAXEXTENTS - total number of extents, including the first, that can ever be allocated for the segment FREELIST GROUPS - number of groups of free lists for the database object you are creating FREELISTS - number of free lists for each of the free list groups for the schema object

OPTIMAL – for use with rollback segments

BUFFER_POOL – defines a default buffer pool (cache) for a schema object Not valid for tablespaces Not valid for rollback segments.Rollback Segments

Private rollback segment: acquired explicitly by an instance when the instance opens the database if it is named in the ROLLBACK_SEGMENTS parameter in the initialization parameter file, form a pool of rollback segments for use by that any instance requiring a rollback segment, database with the Parallel Server option can have only public segments, public and private rollback segments are identical on database with no Parallel Server option, When an instance starts, it acquires TRANSACTIONS/TRANSACTIONS_PER_ROLLBACK_SEGMENT rollback segments by default, To ensure that the instance acquires particular rollback segments that have particular sizes or particular tablespaces, specify the rollback segments by name via the ROLLBACK_SEGMENTS parameter. Recommended:
create one tablespace specifically to hold all rollback segments, separated with the data.