1.Creating Tablespace

CREATE TABLESPACE tablespace
[DATAFILE clause]
[MINIUM EXTENT integer[K|M]]
[BLOCKSIZE integger [K]]
[LOGGING|NOLOGGING]
[DEFAULT storage_clause]
[ONLINE|OFFLINE]
[PERMANENT|TEMPORARY]

datafile_clause == filename
[SIZE intger[K|M] [REUSE] | REUSE] [autoextend_clause]
where filename is the name of a datafile in the tablespace
SIZE specifies the size of the file.Use K or M to specifies the size in kilobytes or megabytes.
REUSE allows the Oracle server to ensure an existing file
autoextentd_clause enables or disables the automatic extension of the datafile.
This clause is discussed in a subsequent section of this lesson.

2.LMT(Locally Management Tablespace)
.Reuced contention on data dictionary tables
.No undo gemerated when space allocation or deallocatoin occurs
.No coalescing required

The LOCAL option of the EXTENT MANAGEMENT clause specifies that a tablespace is to be locally managed.By default
a tablespace is locally managed.
extent_namagement_clause ==
[EXTENT MANAGEMENT
[DICTIONARY | LOCAL
[AUTOALLOCATE | UNIFORM [ SIZE integre [K|M] ] ] ]
where DICTIONARY specifies that the tablespace is managed using dictionary tables.
LOCAL specifies that tablespace is locally managed with a bitmap.If you
specify LOCAL,you cannot specify DEFAULT storage_clause,MINIUM EXTENT,
or TEMPORARY.
AUTOALLOCATE specifies that the tablespace is system managed.Users cannot specify
an extent size.This is the default.
UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes.
Use K or M to specify the extent size in kilobytes or megabytes.
The default size is 1 megabyte.

The EXTENT MANAGEMENT clause can be used in various CREATE commands:
.For a permanent tablespace other than SYSTEM,you can specify EXTENT MANAGE LOCAL in CREATE TABLESPACE command.
.For a temporary tablespace,you can specify EXTENT MANAGEMENT LOCAL in the CREATE TEMPORARY TABLESPACE command.
eg:
CREATE TABLESPCE userdata
DATAFILE ‘/u01/oradata/userdata01.dbf’ size 1921M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

3.DMT(Dictionary Managed Tablespaces)
.Extents are managed in the data dictionary
.Each segment stored in the tablespace can have a different storage clause
.Coalescing required

Setments in dictionary managed tablespaces can have a customized storage,this is
more flexible than locally managed tablespaces but much less efficient.

eg:
CREATE TABLESPACE userdata
DATAFILE ‘/u01/oradata/userdata_01.dbf’ size 1921M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE(initial 1M NEXT 1M);

Changing Default Storage Settings
Use the ALTER TABLESPACE command to alter the default storage definition of a tablespace.
The storage setting for locally managed tablespaces cannot be altered.
ALTER TABLESPACE tablespace
[MINIUM EXTENT integer[K|M] |DEFAULT storage_caluse]

eg:
ALTER TABLESPACE userdata
MINIUM EXTENT 2M;

ALTER TABLESPACE userdata
DEFAULT STORAGE(
INITIAL 2M
NEXT 2M
MAXEXTENTS 999);

4.Undo Tablespace
.Used to store undo segments
.Cannot contain any other objects
.Extents are locally managed
.Can only use the DATAFILE and EXTENT MANAGEMENT clauses of the CREATE TABLESPACE command

An undo tablespace is used with automatic undo management.Unlike other talespaces ,
the undo tablespace is limited to the DATAFILE.

CREATE UNDO TABLESPACE tablespace
[DATAFILE clause]

5.Temporary Tablespace
.Used for sort operations
.Cannot contain any permanent objects
.Locally managed extents recommended

Locally managed temporary tablespace have temporary data files(tempfiles),
which are similar to oridiary data file except that:
.Tempfiles are always set to NOLOGING mode
.You cannot make a tempfile read-only
.You cannot rename a tempfile
.You can not create a tempfile with the ALTER DATABASE command.
.Tempfiles are required for read-onlyr database.
.Media recovery dose not recover tempfiles.
.BACKUP CONTROLFILE dose not generate any information for tempfiles.
.CREATE CONTROLFILE cannot specify any informatino about tempfiles.

To optimize the performance of a sort in a temporary tablespace,
set the UNIFORM SIZE to be a multiple of the parameter SORT_AREA_SIZE.

Alther the ALTER/CREATE TABLESPACE…TEMPORARY command can be used to create a temproary tablespace,
it is recommended that the CREATE TEMPORARY TABLESPACE command be used

eg:
CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘/u01/oradata/temp_01.dbf’ size 1921M AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

6.Default Temporary Tablespace
.Allows you to specify a databasewide default temporary tablespace
.Eliminates the use of the SYSTEM tablespace for storing temporary data
.Can be created using the CREATE DATABASE or ALTER DATABASE command.
.When created with the CREATE DATABASE command, the default temporary tablespace
is locally managed
.It can not be dropped until after a new default is made available
.It cannot be taken offline
.You cannot alter the default temporary tablespace to a permanent tablespace

7.Tablespace Status
(1)Offline Status
.Offline tablespace is not available for data access
.Some tablespace must be online:
—SYSTEM
—Tablespace with active undo segments
—Temporary tablespace
eg:
To take a tablespace offline:ALTER TABLESPACE userdata offline;
To take a tablespace online:ALATER TABLESPACE userdata online;
The oracle instance automatically switches a tablespace from online to offline
when certain errors are encountered(eg:when the Database Writer process,DBW0,
fails in several attemps to write to a data file of the tablespace).

When a tablespace is taken offline,oracle server takes all the associated datafiles offline.

ALTER TABLESPACE tablespace
{ONLINE
OFFLINE [NORMAL|TEMPORARY|IMMEDIATE|FOR RECOVER]}
where NORMAL flushes all blocks in all data files in the tablespace out of SGA.
This is the default.You need not perform media recovery on this
tablespace before bring it back online.
Use the NORMAL clause whenever possible.
TEMPORARY performs a checkpoint for all online data files in the tablespace
only.Any offline files may require media recovery.
IMMEDIATE dose not ensure that the tablespace files are available and dose
not perform a checkpoint.You must perform media recovery on the
tablespace before bringing it back online.
FOR RECOVERY takes tablespce offline for tablespace point-in-tiime recovery.

(2)Read-only Tablespaces
.Tablespace available only for read operations
.Object can be dropped from tablespace
.To create a read-only tablespace on a removable media drive:
- ALTER TABLESPACE…READ ONLY;
- Move the data file to the WORM drive
- ALTER TABLESPACE…RENAME DATAFILE;

In this transitional state,no further write operatoiins can table place in the tablespace except for
the rollback of existing transactions have been either cammitted or rolled back,the command completes,
and the tablespace is placed in read-only mode.
You can drop items,such as tables and indexes,from a read-only tablespace,because these commands effec only
the data dictionary.This is possible because the DROP command updates only the data dictionary,but not
the physical files that make up the tablespace.For locally managed tablespaces,the dropped segment is changed
to a temporary segment,to prevent the bitmap from being updated.
To make a read-only tablespace writable,all of the data files in the tablespace must be online.Making tablespaces
read-only causes a checkpoint on the data files of the tablespaces.

8.Dropping Tablespaces
DROP TABLESPACE tablespace
[INCLUDING CONTENTS [AND DATAFILES] [CASCADE CONSTRAINTS]]
where tablespace
INCLUDING CONTENTS drops all the segments in the tablespace
AND DATAFILES deletes the associated operatiing system files
CASCADE CONSTRAINTS drop referential integrity constraints from
tables outside the tablespace that refer to
primary and unique keys in the tables in the
dropped tablespace

Guidelines
. A tablespace that still contains data cannot be dropped without INCLUDING CONTENTS option.
This option may generate a lot of undo when the tablespace contains many objects
. After a tablespace has been dropped, its data is no longer in the database.
. When a tablespace is dropped ,only the file pointers in the control file of the associated
database are dropped.The operating system files still exist and must be deleted explicitly
using the appropriate operating system command unless the AND DATAFILES clause is used.
. Even if a tablespace is switched to read-only ,it can still be dropped,along with segments within it.
. It is recommended taht you take the tablespace offline before dropping it to ensure that
no transactions access any of the segments in the tablespace.

9.Resizing a Tablespace
. Add a data file
. Change the size of a data file:
- Automatically
- Manually

Specifying AUTOEXTEND for a New Data File
. CREATE DATABASE
. CREATE TABLESPACE … DATAFILE
. ALTER TABLESPACE … ADD DATAFILE

(1)Use the ALTER DATABASE command to modify a data file and enable automatic extention:
ALTER DATABASE DATAFILE filespec [autoextend_clause]

autoextend_clause :== [ AUTOEXTEND { OFF|ON[NEXT integer[K|M]]
[MAXSIZE UNLIMITED | integer [K|M]} ]
where: AUTOEXTEND OFF|ON disables|enable the automatic extension of the data file
NEXT specifies the disk space to allocated to the data file
when more extents are required
MAXSIZE specifies the maximum disk space allowed for allocation to the data file
UNLIMITED sets no limit on allocating disk space to the data file

(2)Specifying AUTOEXTEND for an Existing Data File

ALTER DATABASE [database]
DATAFLE ‘filename’[, ‘filename’]… autoexted_clause

(3)Changing the Size of Data Files Manually

ALTER DATABASE [database]
DATAFILE ‘filename’[, ‘filename’]…
RESIZE integer[K|M]

Note:
If there are database objects stored above the specified size,then the data file size is
decreased only to tha last block of the last objects in the data file.

(4)Adding Data Files to a Tablespace
ALTER TABLESPACE tablespace
ADD DATAFILE
filespec [autoextend_clause]
[, filespec [autoextend_clause]]…

10.Moveing Data Files:ALTER TABLESPACE
. The tablespace must be offline.
. The target data files must exist.

ALTER TABLESPACE tablespace
RENAME DATAFILE ‘filename’[, ‘ filename’]…
TO ‘filename’[, ‘ filename’]…

This command is appliced only to data files in a non-SYSTEM tablespace
that dose not contain active undo or temporary segments.

Use the following process to rename a data file:
1. Take the tablespace offline.
2. Use an operating system command to move or copy the files.
3. Execute the ALTER TABLESPACE RENAME DATAFILE command.
4. Bring the tablespace online
5. Use an operating system commamd to delete the file if necessary.

eg:
ALTER TABLESPACE userdata
RENAME
DATAFILE ‘/u01/oradata/userdata01.dbf’
TO ‘/u01/oradata/userdata01.dbf’;

11.Moving Data Files:ALTER DATABASE

. The database must be mounted
. The target data file must exist

The ALTER DATABASE command can be used to move any type of data file:
ALTER DATABASE [database]
RENAME FILE ‘filename’[, ‘filename’]…
TO ‘filename’[, ‘filename’]…

Because the SYSTEM tablespace cannot be taken offline,you must use this method to move
data files in the SYSTEM tablespace.

1. Shut down the database
2. Use an operation system command to move the files.
3. Mount the database
4. Execute the ALTER DATBASE RENAME FILE command
5. Open the database.

12 Obtaining Tablespace information
. Tablespace information
- DBA_TABLESPACES
- V$TABLESPACE
.Data file information
- DBA_DATA_FILES
- V$DATAFILE
.Temp file information
- DBA_TEMP_FILES
- V$TEMPFILE

Popularity: 21% [?]