Kamran Agayev A.
If the database has multiple tablespaces, you can separate
data of one application from the data of another to prevent multiple
applications from being affected if a tablespace must be taken
offline. You can store different datafiles of different tablespaces
on different disk drives to reduce I/O contention and backup
Create only enough tablespaces to fulfill your needs, and
create these tablespaces with as few files as possible. If you need
to increase the size of a tablespace, add one or two large
datafiles, or create datafiles with autoextension enabled, rather
than creating many small datafiles
SYSAUX tablespace, which acts as an auxiliary tablespace
to the SYSTEM tablespace, is also always created when you create a
database. It contains information about and the schemas used by
various Oracle products and features, so that those products do not
require their own tablespaces.
Locally managed tablespaces track all extent information
in the tablespace itself by using bitmaps. It provides enhanced
performance, and user reliance on the data dictionary is reduced,
because the necessary information is stored in file headers and
Create a locally managed tablespace by specifying LOCAL
in the EXTENT MANAGEMENT vlause of the CREATE TABLESPACE statement.
This is the default for new permanent tbalespaces, but you must
specify the EXTENT MANAGEMENET LOCAL clause if you want to specify
either the AUTOALLOCATE clause or the UNIFORM clause. You can have
the database manage extents for you automatically with the
AUTOALLOCATE clause (the default) or you can specify that the
tablespace is managed with uniform extents of a specific size
AUTOALLOCATE means that the extent size are managed by
Oracle. Oracle will choose the next optimal size for the extents
starting with 64KB. As the segments grow and more extents are
needed, oracle will start allocating larger and larger sizes ranging
from 1Mb to eventually 64Mb extents.
UNIFORM T option tells the database to allocate and
deallocate extents in the tablespace with the same unvarying size
that you can specify or let extents default to 1Mb.
In a locally managed tablespace, there are two methods
that Oracle Database can use to manage segment spaceL AUTOMATIC and
MANUAL. Manual segment space management uses linked lists called
“freelists” to manage free space in the segment, while automatic
segment management uses bitmaps. Automatic segment space management
is the more efficient method, and is the default for all new
permanent, locally managed tablespaces.
To create a bigfile tablespace, run CREATE BIGFILE
TABLESPACE command. Oracle automatically creates a locally managed
tablespace with automatic segment space management.
If default tablespace type was set to BIGFILE at database
creation, but you want to create a traditional (smallfile)
tablespace, then specify a CREATE SMALLFILE TABLESPACE statement to
override the default tablespace that you are creating.
Within a temporary tablespace, all sort operations for a
given instance and tablespace share a single sort segment. The sort
segment is created by the first statement that uses a temporary
tablespace for sorting, after startup, and is released only at
You can view the allocation and deallocation of space in
a temporary tablespace sort segment using the V$SORT_SEGMENT view.
When you create a temporary table, its rows are stored in
your default temporary tablespace
Use CREATE BIGFILE TEMPORARY TABLESPACE statement to
create a single tempfile tablespace.
To rename a tempfile, you take the tempfile offline, use
operating system commands to rename or relocate the tempfile, and
then use the ALTER DATABASE RENAME FILE command to update the
Using temporary tablespace group, rather than a single
remporary tablespace, can alleviate problems caused where one
tablespace is inadequate to hold the results of a sort, particularly
on a table that has many partitions.
You create at tablespace group implicitily when you
include the TABLESPACE GROUP clause in the CREATE TEMPORARY
TABLESPACE or ALTER TABLESPACE statement and the specified
tablespace group does not currently exist.
ALTER TABLESPACE tmp_tbs TABLESPACE GROUP group2;
The view DBA_TABLESPACE_GROUPS lists tablespace groups
and their member tablespaces
To assign tablespace group as a default temporary
tablespace, use the following statement:
ALTER DATABASE db_name DEFAULT TEMPORARAY TABLESPACE group2;
You can create tablespaces with block sizes different
from the standard database block size, which is specified by the
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 parameter. Further, and the integer you specify
in the BLOCKSIZE clause must correspond with the setting of one DB_nK_CACHE_SIZE parameter.
CREATE TABLESPACE tbs DATAFILE ‘/datafile/’ SIZE 10M EXTENT
MANAGEMENT LOCAL UNIFORM SIZE 128K BLOCKSIZE 8K;
You can’t take SYSTEM, UNDO and TEMPORARY tablespaces
When you take tablespace to OFFLINE usint IMMEDIATE
clause, media recovery for the tablespace is required before the
tablespace can be brought online.
If you must take tablespace offline, use the NORMAL
clause (the default) if possible. This setting guarantees that the
tablespace will not require recovery to come back online, even if
after incomplete recovery you reset the redo log sequence using an
ALTER DATABASE OPEN RESETLOGS statement .
Specify TEMPORARY only when you cannot take tablespace
offline normally. In this case, only the files taken offline
because of errors need to be recovered before the tablespace can be
brought online. Specify IMMEDIATE only after trying both the normal
and temporary settings
You can drop items, such as tables or indexes, from a
read-only tablespace, but you cannot create or alter objects in a
Before you can make a tablespace read-only, the
tablespace must be online, can’t be active undo tablespace or SYSTEM
When there’re transactions waiting to commit, the ALTER
TABLESPACE .. READ ONLY statement does not return immediately. It
waits for all transactions started before you issued the ALTER
TABLESPACE statement to either commit or rollback.
When substantial portions of a very large database are
stored in read-only tablespaces that are located on slow-access
devices or hierarchical storage you should consider setting the
READ_ONLY_OPEN_DELAYED parameter to TRUE. This speed certain
operations, primary opening the databsae, by causing datafiles in
readonly tablespaces to be accessed for the first time only when an
attempt is made to read data stored within them
You can’t rename the tablespace if :
- The tablespace is SYSTEM or SYSAUX tablespace
- If any datafile in the tablespacea is offline or if the
tablespace is offline
Use the CASCADE CONSTRAINTS clause to drop all
referential integrity constraints from tables outside the tablespace
that refer to primary and unique keys of tables inside the
Use INCLUDING CONTENTS AND DATAFILES to delete physical
datafiles from OS. It an OS error prevents the deletion of a file,
the DROP TABLESPACE statement still succeeds, but a message
describing the error is written to the alert log
If SYSAUX tablespace becomes unavailable, core database
functionality will remain operational.
You can monitor the occupants of the SYSAUX tablespace
using the V$SYSAUX_OCCUPANTS view.
Moving data using transportable tablespaces is much
faster than performing either an export/import or unload/load of the
same data. This is because the datafiles containing all of the
actual data are just copied to the destination location, an you use
an export/import utility to transfer only the metadata of the
tablespace objects to the new database
Starting with Oracle Database 10g, you can transport
tablespaces across platforms.
You can query the V$TRANSPORTABLE_PLATFORM view to see
the platfors that are supported, and to determine each platform’s
endian format(byte ordering)
If the source platform and the target platform are of
different endianness, then an additional step must be done on either
the source or target platform to convert the tablespace being
transported to the target format. If they are of the same
endianness, then no conversion is necessary and tablespaces can be
transported as if they were on the same platform.
To transport tablespace between database follow this procedure:
- Check the endian format of both platforms by querying the
- Pick a self contained set of tablespaces
- Generate a transportable tablespace set. It consists of
datafiles for the set of tablespaces being transported and an
export file containing structural information (metadata) for the
set of tablespaces.
- Transport the tablespace set
- Import the tablespace set
There may be logical or physical dependiencies between
objects in the transportable set and those outside of the set. You
can only transport a set of tablespaces that is self-contained. In
this context “self-containted” means that there are no references
from inside the set of tablespaces pointing outside of the
tablespaces. To check whether the tablespace is self-containted, you
can invoke the TRANSPORT_SET_CHECK procedure in the Oracle supplied
package DBMS_TTS. After invoking this procedure, you can see all
violations by selecting from the TRANSPORT_SET_VIOLATIONS view.
To convert tablespaces from one endian format to
another, login to RMAN and run the following command:
RMAN> CONVERT TABLESPACE sales_1, sales_2 TO
PLATFORM ‘Microsoft Windows NT’ FORMAT ‘/tmp/%U’;
Then transport both the datgafiles and the export file
of the tablespaces to a place that is accessible to the target
Use RMAN to convert the necessary transported
datafiles to the endian format of the destination host format and
deposit the results in /orahome/dbs as shown in the following
TO PLATFORM =”Solaris[tm] OE (32-bit)”
FROM PLATFORM=”HP Tru64 UNIX”
Get the Complete
Oracle Tuning Details
The landmark book
Tuning: The Definitive Reference Second Edition" has been
updated with over 1,150 pages of expert performance tuning tips.
It's packed with scripts and tools to hypercharge Oracle 11g
performance and you can
buy it for 40% off directly from the publisher.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA
1996 -2011 by Burleson Enterprises. All rights reserved.
Oracle® is the registered trademark of Oracle
Corporation. SQL Server® is the registered trademark of Microsoft
Many of the designations used by computer vendors to distinguish their
products are claimed as Trademarks