Tablespaces Creation steps

Tablespaces Creation Steps:

Locally managed tablespaces track all extent information in the tablespace itself by using bitmaps, resulting in the following benefits:

Concurrency and speed of space operations is improved, because space allocations and deallocations modify locally managed resources (bitmaps stored in header files).
Performance is improved, because recursive operations that are sometimes required during dictionary-managed space allocation are eliminated
To create a locally managed tablespace give the following command

SQL> CREATE TABLESPACE ravi DATAFILE '/u02/oracle/ravi/ravi01.dbf' SIZE 100M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
             

AUTOALLOCATE causes the tablespace to be system managed with a minimum extent size of 64K.

The alternative to AUTOALLOCATE is UNIFORM. which specifies that the tablespace is managed with extents of uniform size. You can specify that size in the SIZE clause

of UNIFORM. If you omit SIZE, then the default size is 1M. The following example creates a Locally managed tablespace with uniform extent size of 256K

SQL> CREATE TABLESPACE ravi DATAFILE '/u02/oracle/ravi/ravi01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

 Create Dictionary Managed Tablespace

SQL> CREATE TABLESPACE ravi_lmt DATAFILE '/u02/oracle/ravi/ravi01.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY;

Bigfile Tablespaces (Introduced in Oracle Ver. 10g)
A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks) datafile. Traditional smallfile tablespaces, in contrast, can contain multiple

datafiles, but the files cannot be as large. Bigfile tablespaces can reduce the number of datafiles needed for a database.

 create a bigfile tablespace given the below inputs.

SQL> CREATE BIGFILE TABLESPACE ravi_bigtbs
    DATAFILE '/u02/oracle/ravi/bigtbs01.dbf' SIZE 30G;

Process 1:

Re- Size the tablespace in below inputs.

To increasing the size of an existing datafile in below given inputs.

SQL> alter  database ravi datafile ‘/u01/oracle/data/ravi01.dbf’ resize 100M;


Process 2:

To resize of a tablespace by adding a new datafile to a tablespace. Please find the below inputs for resie the tablespace.

SQL> alter tablespace add datafile  ‘/u02/oracle/ravi/ravitbs02.dbf’ size 100M;


Process 3:


Oracle will automatravilly increase the size of a datafile whenever space is required. You can specify by how much size the file should increase and Maximum size to

which it should extending.

Existing datafile auto extendable in below given inputs.

SQL> alter database datafile ‘/u01/oracle/ravi/ravitbs01.dbf’ auto extend ON next 5M maxsize 500M;

Extendable while creating a new tablespace in below given inputs.

SQL> create tablespace ravi datafile ‘/u01/oracle/ravi/ravitbs01.dbf’ size 50M auto extend ON next 5M maxsize 500M;

 Decrease the size of a tablespace:

Decrese the size of tablespace by decreasing the datafile associated with it. Decrease a datafile only up to size of empty space in it.

SQL> alter database datafile ‘/u01/oracle/ravi/ravitbs01.dbf’  resize 30M;


Making tablespaces Offline or Online:

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.

Alter the availability of a tablespace, use the ALTER TABLESPACE statement. You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.

 Make a Tablespace Offline in below given inputs.

SQL>alter tablespace ravi offline;

Make a bring it back online in below given inputs.

SQL>alter tablespace ravi online;

Make individual datafile offline type the following command

SQL>alter database datafile ‘/u01/oracle/ravi/ravi_tbs01.dbf’ offline;

Again to bring it back online give the following command

SQL> alter database datafile ‘/u01/oracle/ravi/ravi_tbs01.dbf’ online;

Note: You can’t take individual datafiles offline it the database is running in NOARCHIVELOG mode.  If the datafile has become corrupt or missing when the database is

running in NOARCHIVELOG mode then you can only drop it by giving the following command

SQL>alter database datafile ‘/u01/oracle/ravi/ravi_tbs01.dbf’ offline for drop;

Making a Tablespace Read only.
Making a tablespace read-only prevents write operations on the datafiles in the tablespace. The primary purpose of read-only tablespaces is to eliminate the need to

perform backup and recovery of large, static portions of a database. Read-only tablespaces also provide a way to protecting historravil data so that users cannot modify

it. Making a tablespace read-only prevents updates on all tables in the tablespace, regardless of a user's update privilege level.

To make a tablespace read only

SQL>alter tablespace ravi read only

Again to make it read write

SQL>alter tablespace ravi read write;

Renaming Tablespaces
Using the RENAME TO clause of the ALTER TABLESPACE, you can rename a permanent or temporary tablespace. For example, the following statement renames the users

tablespace:

ALTER TABLESPACE users RENAME TO usersts;


The following affect the operation of this statement:

The COMPATIBLE parameter must be set to 10.0 or higher.
If the tablespace being renamed is the SYSTEM tablespace or the SYSAUX tablespace, then it will not be renamed and an error is raised.
If any datafile in the tablespace is offline, or if the tablespace is offline, then the tablespace is not renamed and an error is raised.
Dropping Tablespaces
You can drop a tablespace and its contents (the segments contained in the tablespace) from the database if the tablespace and its contents are no longer required. You

must have the DROP TABLESPACE system privilege to drop a tablespace.

Caution: Once a tablespace has been dropped, the data in the tablespace is not recoverable. Therefore, make sure that all data contained in a tablespace to be dropped

will not be required in the future. Also, immediately before and after dropping a tablespace from a database, back up the database completely

To drop a tablespace give the following command.

SQL> drop tablespace ravi;

This will drop the tablespace only if it is empty. If it is not empty and if you want to drop it anyhow then add the following keyword

SQL>drop tablespace ravi including contents;

This will drop the tablespace even if it is not empty. But the datafiles will not be deleted you have to use operating system command to delete the files.

But If  you include datafiles keyword then, the associated datafiles will also be deleted from the disk.

SQL>drop tablespace ravi including contents and datafiles;

Temporary Tablespace
Temporary tablespace is used for sorting large tables. Every database should have one temporary tablespace. To create temporary tablespace give the following command.

SQL>create temporary tablespace temp tempfile ‘/u01/oracle/data/ravi_temp.dbf’ size 100M
   extent management local  uniform size 5M;

The extent management clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size.  The

AUTOALLOCATE clause is not allowed for temporary tablespaces.

Increasing or Decreasing the size of a Temporary Tablespace
You can use the resize clause to increase or decrease the size of a temporary tablespace. The following statement resizes a temporary file:

SQL>ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;

The following statement drops a temporary file and deletes the operating system file:

SQL> ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP  INCLUDING DATAFILES;

Tablespace Groups
A tablespace group enables a user to consume temporary space from multiple tablespaces. A tablespace group has the following characteristics:


Using a tablespace group, rather than a single temporary 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. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary

tablespaces.

The view DBA_TABLESPACE_GROUPS lists tablespace groups and their member tablespaces.

Creating a Temporary Tablespace Group
You create a tablespace group implicitly 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.

For example, if neither group1 nor group2 exists, then the following statements create those groups, each of which has only the specified tablespace as a member:

CREATE TEMPORARY TABLESPACE ravi_temp2 TEMPFILE '/u02/oracle/ravi/ravi_temp.dbf'
     SIZE 50M TABLESPACE GROUP group1;

ALTER TABLESPACE ravi_temp2 TABLESPACE GROUP group2;


Assigning a Tablespace Group as the Default Temporary Tablespace
Use the ALTER DATABASE ...DEFAULT TEMPORARY TABLESPACE statement to assign a tablespace group as the default temporary tablespace for the database. For example:

ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2;