Home  >  Blog  >   Oracle DBA

How to Specify nonstandard block sizes for tablespaces - Oracle DBA

Rating: 4

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;

Want To Get DBA Training From Experts? Enroll Now For Free Demo On Oracle DBA Training.

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.

MindMajix YouTube Channel

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.


Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!


Join our newsletter

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
Oracle DBA TrainingMar 02 to Mar 17View Details
Oracle DBA TrainingMar 05 to Mar 20View Details
Oracle DBA TrainingMar 09 to Mar 24View Details
Oracle DBA TrainingMar 12 to Mar 27View Details
Last updated: 04 Apr 2023
About Author


Technical Content Writer

read more
Recommended Courses

1 / 15