How to Specify nonstandard block sizes for tablespaces - Oracle DBA

ou can create tablespaces with block sizes different from the standard database block size, which is specified by the db_block_size initialization parameter. This feature lets you transport tablespaces with unlike block sizes between databases.

Use the blocksize clause of the create tablespace statement to create a tablespace with a block size different from the database standard block size. In order for the blocksize clause to succeed, you must have already set the db_cache_size and at least one db_nk_cache_size initialization parameter. Further, and the integer you specify in the blocksize clause must correspond with the setting of one db_nk_cache_size parameter setting. Although redundant, specifying a block size equal to the standard block size, as specified by the db_block_size initialization parameter, is allowed.

The following statement creates tablespace lmtbsb, but specifies a block size that differs from the standard database block size (as specified by the db_block_size initialization parameter):

Sql>create tablespace lmtbsb datafile ‘/u02/oracle/data/lmtbsb01.dbf’ size 50m extent management local uniform size 128k

Blocksize 8k;

Controlling the writing of redo records

For some database operations, you can control whether the database generates redo records. Without redo, no media recovery is possible. However, suppressing redo generation can improve performance, and may be appropriate for easily recoverable operations. An example of such an operation is a create table…as select statement, which can be repeated in case of a database or instance failure.

Specify the nologging clause in the create tablespace statement if you wish to suppress redo when these operations are performed for objects within the tablespace. If you do not include this clause, or if you specify logging instead, then the database generates redo when changes are made to objects in the tablespace. Redo is never generated for temporary segments or in temporary tablespaces, regardless of the logging attribute.

The logging attribute specified at the tablespace level is the default attribute for objects created within the tablespace. You can override this default logging attribute by specifying logging or nologging at the schema object level–for example, in a create table statement.

Checkout Oracle DBA Interview Questions

If you have a standby database, nologging mode causes problems with the availability and accuracy of the standby database. To overcome this problem, you can specify a force logging mode. When you include the force logging clause in the create tablespace statement, you force the generation of redo records for all operations that make changes to objects in a tablespace. This overrides any specification made at the object level.

If you transport a tablespace that is in force logging mode to another database, the new tablespace will not maintain the force logging mode.

Altering tablespace availability:

You can take an online tablespace offline so that it is temporarily unavailable for general use. The rest of the database remains open and available for users to access data. Conversely, you can bring an offline tablespace online to make the schema objects within the tablespace available to database users. The database must be open to alter the availability of a tablespace.

To alter the availability of a tablespace, use the alter tablespace statement. You must have the alter tablespace or manage tablespace system privilege.