Tablespace Management

Tablespace Management

Extended ROWID’s and Tablespace Sizing
The new Oracle 8 ROWID format is 10 bytes and has a different format from the Oracle 7 ROWID. The Oracle 8 ROWID type is known as extended and the Oracle 7 ROWID type is known as restricted.
The restricted ROWID format is BRF: 1) dB block number, 2) row in dB block, and 3) datafile number.
The extended ROWID format is OFBR: 1) data object number, 2) relative datafile number, 3) dB block number, and 4) row in dB block.
The data object number refers to the tablespace, which contains the row.
The relative datafile number refers to the datafile within the given object. While all datafile numbers must be unique within a given object, they can be the same in different tablespaces (i.e., objects).
The database block number refers to the dB block within the given datafile, which contains the row in question.
The row number refers to the specific row within the given dB block.
The extended ROWID format consists of 18 Base 64 characters (Base 64 format: A-Z, a-z, 0-9, +, /).

Locally-Managed Tablespaces
Locally managed tablespaces perform their own extent management, as opposed to dictionary managed tablespaces where the extent mgmt is performed by the data-dictionary.
You can not change the extent management method of a tablespace after it has been created.
Locally managed tablespaces use bitmaps in each datafile to keep track of the status of their data blocks. There is a bitmap value for each block or group of blocks in the tablespace.
No rollback is generated as a result of extents being allocated or freed for reuse.
You do not specify NEXT, MINEXTENTS, MAXEXTENTS, PCTINCREASE or DEFAULT STORAGE for locally managed tablespaces.
Advantages and Rules:
-Reduces space mgmt operations associated with maintaining rollback segments and updating the data dictionary.
-Takes away the need to coalesce free extents.
-Extent sizes can be managed by the system or all have the same size. Use the UNIFORM or AUTOALLOCATE (system-managed) options to specify. If using AUTOALLOCATE, you can specify the initial extent size and then Oracle will determine the size of additional extents (minimum and default additional extent size is 64K). When using UNIFORM, the default is 1M unless you specify a value. All locally managed temporary tablespaces must have UNIFORM specified.
-Can create the system tablespace EXTENT MANAGEMENT LOCAL as part of the CREATE DATABASE command. When system tablespace is managed locally you can still create dictionary managed tablespaces. However, you must create all rollback segments in locally managed tablespaces.
-Can use EXTENT MANAGEMENT LOCAL as past of the CREATE TABLE command for permanent tablespaces, other than system.
-Can specify EXTENT MANAGEMENT LOCAL as part of the CREATE TEMPORARY TABLESPACE command.
Example of permanent locally managed tablespace with non-default uniform extent size:
CREATE TABLESPACE tablespace_name ...
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2M;

Transportable Tablespaces
Use to copy or move tablespaces between Oracle databases that have the same block size, character set, and are on the same hardware platform.
Faster than export and import. Transporting a tablespace requires only a copy of the datafiles and the integration of the tablespace metadata into the data dictionary.
Can also transport indexes so they don’t need to be rebuilt.
The tablespace must be fully “self-contained”. This means that none of the following conditions must be true:
- There is an index inside of the tablespace set that is based upon a table that is not contained in the tablespace set.
- A partitioned table does not have all of its partitions located within the tablespace set. In other words, some of the table’s partitions live in a different tablespace set.
- There is an LOB column in one of the tables within the tablespace set that points to LOB’s outside of the tablespace set.
Perform the following to create a self-contained transportable set:
- ALTER TABLESPACE tablespace_name READ ONLY; for all tablespaces in the tablespace set.
- Use the EXPORT command with TRANSPORT_TABLESPACE=Y and TABLESPACES= ts1, ts2...tsn. Where ts1, ts2 are the tablespace names in the self-contained set. Use the TRIGGERS=Y/N, CONSTRAINTS=Y/N, GRANTS=Y/N, and FILE=filename parameters as well. Note that the FILE value specifies the name of the output file that will contain the structural information. Note that Y is the default setting for the Y/N value parameters specified.
When transporting from an OLTP system to a data-warehouse environment specify GRANTS = Y, TRIGGERS = Y, CONSTRAINTS = Y and TRANSPORT TABLESPACE = Y option in the export command to be sure the objects and all associated table information is brought over. However, if the Oracle exam asks you which 2 parameters you are most likely NOT need, that is set to N, answer TRIGGERS and CONSTRAINTS.
The TRIGGERS export parameter is new to Oracle 8i and is there to control the transport of TRIGGERS related to the transportable tablespace set.
Specify the DATAFILES import parameter to name the datafiles that belong to the transferred tablespace being imported.

Read-Only Tablespace Enhancements
Use ALTER TABLESPACE tablespace_name READ ONLY command to place tablespaces in read-only mode. Oracle will wait for any active transactions to complete and then put the tablespace in read-only mode.
Query the V$TRANSACTION view to identify long running transactions that may be keeping a tablespace from becoming READ-ONLY. You can then kill those transactions and place the tablespace in READ-ONLY mode.