In this chapter we will discuss about Database storage system and involved Scheme objects. User, Schema and instance are synonmous. Schema objects stand for the objects created by user and in other words, the objects contained in a Schema.
The Oracle 10g database stores tables and other objects in tablespaces.
A Tablespace consists of one or more Segments
A Segment consists of one or more Extents
An Extent is composed of a contiguous set of data blocks.
A Data Block is a fixed number of bytes of disk space.
Database storage objects are called Segments. Tables are one type of Segments. Other Segment Types are:
Rollback Segments: - Special segment used to maintain read consistency during user transactions and perform transaction recovery.
Partition Segments: Divide a table into smaller, more manageable pieces for performance purposes.
Each Oracle segment is made up of contiguous chunks of storage space in the database called Extents.
A segment will have one extent to 2 billion extents. An Extentis a collection of Oracle database blocks.
An Extent will have up to 5 DB Blocks. Default database block size is set at database creation. Multiple block sizes can be used in Oracle 10g - means different Extents can have different DB Block sizes. The database block sizes are 2KB, 4KB, 8KB, and 16KB. Each database block is in turn composed of one or more operating system blocks. The size of an operating system block depends on the operating system, but most are 512 bytes to 2KB in size.
In simpler terms,
Segment = n x Extents
Extent = n x DB Blocks (2KB, 4KB, 8KB, and 16KB)
DB Block = n x OS blocks(512 bytes to 2KB)
Related objects such as Tables and Indexes are placed in the same tablespace.
Tablespaces can be taken offline for recovery and other purposes.
They can be moved to another Database.
They can be made read-only to ensure that no changes are applied to that tablespace.
They can be placed on different disk drives to reduce any disk contention. For example, tablespaces containing tables on one disk drive and tablespaces containing indexes on another disk drive to reduce disk contention.
The SYSTEM tablespace is used for the data dictionary. The SYSAUX tablespace is used for schema objects associated with Oracle-provided features, such as the spatial data option, XMLDB etc.
Add BIGFILE keyword in the Tablespace creation statement to create a BIGFILE tablespace as follows:
CREATE BIGFILE TABLESPACE
notice tablespacename is not put in single quotes, but datafilename is placed in single quotes.
Space management in tablespace is organised through a concept called Extent management. Extent management can be either Local Extent Management or Dictionary Extent Management.
In Local Extent Management, Extents are allocated in two ways: AUTOALLOCATE
The UNIFORM option is used to have equal size of Extents in a tablespace. UNIFORM is default option and its default value is 1MB, if not otherwise specified. (Cannot be specified for undo tablespaces).
To create consistent 100MB extents, use the clause EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100M in the CREATE TABLESPACE statement.
AUTOALLOCATE option is used to have varying size of extents for each segment. For example, on Windows and Linux with 8KB data blocks, each segment starts out with 64KB extents for the first 16 extents, and then extents increase in size to 1MB for the next 63 extents. The size then increases to 8MB for the next 120 extents, then 64MB, and so on as the segment grows. This algorithm allows small segments to remain small and large segments to grow without gaining too many extents. AUTOALLOCATE is used to have a combination of small and large tables.
You can convert a tablespace from dictionary extent management to local extent management and back with the Oracle-supplied PL/SQL package DBMS_SPACE_ADMIN. The SYSTEM tablespace and any temporary tablespaces, however, cannot be converted from local to the older style dictionary management.
Manual segment space management exists for backward compatibility and uses free block lists to identify the data blocks available for inserts.
To specify manual segment space management, use the SEGMENT SPACE MANAGEMENT MANUAL clause of the CREATE TABLESPACE statement, or simply omit the SEGMENT SPACE MANAGEMENT clause. Although Oracle strongly recommends AUTOMATIC segment space management for permanent, locally managed tablespaces, the default behavior of Oracle 10g is MANUAL.
When automatic segment space management is specified, bitmaps are used instead of free lists to identify which data blocks are available for inserts. The parameters PCT_FREE and PCT_USED are ignored for segments in tablespaces with automatic segment space management. Automatic segment space management is available only on tablespaces configured for local extent management; it is not available for temporary or system tablespaces. Automatic segment space management performs better and reduces your maintenance tasks, making it the preferred technique.
To specify automatic segment space management, use the SEGMENT SPACE MANAGEMENT AUTO clause of the CREATE TABLESPACE statement.
Although the name segment space management sounds similar to extent management, it is quite different and can be more accurately regarded as free space management. Moreover, because the default behavior of the database differs from Oracle’s recommended implementation, be sure that you understand these options.
Create a temporary tablespace:
(a) at database creation time with the DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement (or)
(b) after the database is created with the CREATE TEMPORARY TABLESPACE statement.
Temp files are only available with temporary tablespaces, never need to be backed up, and do not log data changes in the redo logs.
Although it is always a good practice to create a separate temporary tablespace, it is required when the system tablespace is locally managed. Temporary tablespaces are created using temp files instead of datafiles. Temp files are allocated slightly differently than datafiles. Although datafiles are completely allocated and initialized at creation time, temp files are not always guaranteed to allocate the disk space specified. This means that on some Unix systems a temp file will not actually allocate disk space until a sorting operation requires it.
To create an undo tablespace at database creation time, set the initialization parameter UNDO_MANAGEMENT=AUTO and include an UNDO TABLESPACE clause in your CREATE DATABASE statement.
Undo tablespace can be created after database creation with the CREATE UNDO TABLESPACE statement.
Take the receivables tablespace offline: ALTER TABLESPACE receivables OFFLINE; Use an operating system program to physically move the file, such as Copy in Microsoft Windows or cp in Unix. Tell the database about the new location: ALTER TABLESPACE receivables RENAME DATAFILE 'H:\ORACLE\ORADATA\ORA10\RECEIVABLES02.DBF' TO 'G:\ORACLE\ORADATA\ORA10\RECEIVABLES02.DBF' ; Bring the tablespace back online: ALTER TABLESPACE receivables ONLINE;
Use a READ ONLY clause with an ALTER TARLESPACE statement to mark a tablespace read-only.
If you need to make changes to a table in a read-only tablespace, make it read writable again with the key words READ WRITE.
While a tablespace is in backup mode, some additional information is written to the redo logs to assist with recovery, if needed.
Some companies perform backups by splitting a third mirror, mounting these mirrored file systems onto another server, and then copying them to tape. To safely split the mirror, alter all your tablespaces into backup mode, make the split, and then alter all the tablespaces out of backup mode. Put them into backup mode.
Use the keywords END RACKUP to take a tablespace out of backup mode.
If you forget to take a tablespace out of backup mode, the next time you bounce your data- base, it will see that the checkpoint number in the control file is later than the one in the datafile headers and report that media recovery is required.
The DBA_TABLESPACES view has one row for each tablespace in the database and includes the following information: The tablespace block size The tablespace status: online, offline, or read-only The contents of the tablespace: undo, temporary, or permanent Whether it uses dictionary or locally managed extents Whether the segment space management is automatic or manual Whether it is a bigfile or smallfile tablespace
A database can have as many as five data block sizes. SYSTEM and SYSAUX tablespaces have the database’s standard data block size, defined at creation time. The data block size is 8KB to 32KB and must be a multiple of the physical block size of the storage device.
This size is defined using initialization parameter DB_ BLOCK_SIZE. Other tablespaces can have different data block sizes.
The V$TABLESPACE view also has one row per tablespace, but it includes some information other than DBA_TABLESPACES, such as whether the tablespace participates in database flashback operations:
The DBA_DATA_FILES and DBA_TEMP_FILES views contain information on datafiles and temp files, respectively. This information includes the tablespace name, filename, file size, and autoextend settings.
In addition to the data dictionary, tablespace information can be obtained from several sources. Some of these sources are the DDL and the Enterprise Manager.
To use the Database Control, follow these steps:
Edit the File Size field, increasing it to 300 MB. The change will be applied when you click Continue.
|Local Management||Dictionary Extent Management|
|Local extent management is the default if not specified.|
|With local extent management, the database tracks extents through the use of bitmaps, eliminating the recursive SQL.||With dictionary extent management, the database tracks free and used extents in the data dictionary, changing the FET$ and UET$ tables with recursive SQL.|
|Once defined, cannot be converted to Dictionary Managed.||Once defined, Dictionary Extent Management can be converted to Locally Managed.|