What you need to know about create tablespace statement in Oracle

Last updated on May 17th, 2019 at 05:53 pm

oracle create tablespace

We will be discussing Oracle create tablespace statement/oracle create tablespace 11g in detail in this post. I will through light on all the option on create tablespace statement.I would also be discussing  how to check tablespace in oracle,how to alter the tablespace to read-only, offline the tablespace,how to move tablespace to another location,drop the tablespace. Apart from that, how you can OMF for managing the tablespace. I hope you will like this post. I will be looking forward for  feedback on this post

 

Table of content
What is Oracle Tablespace and its use
What is Oracle Datafiles
Create Tablespace statement
How to modify the existing Datafile  auto extend characteristics in a Tablespace
How to alter the tablespace to offline
How to alter the tablespace to read-only
How to drop the Oracle tablespace
How to increase the size of the Oracle tablespace
How to move tablespace/datafile to another location
How to create datafile/tablespace using OMF
How to assign tablespaces to users
Dictionary views for Viewing Tablespace Information

 

What is  Oracle Tablespace?

Tablespace is logical storage unit in Oracle Database. Oracle Tablespace consists of one or more datafiles.

Tablespace is further divided in to logical units Segments, Segment is divided in to Extent and Extent in to Block

Various type of Tablespace are BIGFILE, SYSTEM, SYSAUX, and UNDO

Basics About Segment/Extent/Data block

Segment

It is a space allocated for a specific logical structure (table/index, partition etc.), cannot span tablespaces but it can span datafiles belonging to tablespace. It is made up of one or more extents

Extent

It is a set of contiguous data blocks, cannot span a datafile (must exist in only one). When the segment grows more extents are allocated.

Data Block

The smallest unit of data in Oracle server, one or more blocks corresponds to one or more operating system blocks (should be a multiple of operating system block size), initial size determined by DB_BLOCK_SIZE parameter in the parameter file

 We will checking oracle create tablespace  statement later in this post

oracle database logical structure

What are Datafiles in Oracle?

-It is physical structure to store oracle data
-One or more physical datafile are logically grouped together to make a tablespace
-A Datafile can be associated with only one tablespace

 

oracle tablespace and datafiles

Oracle Create Tablespace statement

The tablespace can be created by the user having sysdba privilege to hold various tables and index objects.

 

Complete Syntax for Create tablespace statement.

CREATE [TEMPORARY / UNDO] TABLESPACE <tablespace_name>
DATAFILE / TEMPFILE       ‘<datafile and Path where file to create>’ SIZE <integer M>
BLOCKSIZE  <DB_BLOCK_SIZE parameter /2k/4k/8k/16k/32k >
AUTOEXTEND { [OFF/ON (NEXT <integer K/M >  MAXSIZE<integer K/M >) / UNLIMITED] }
LOGGING/NOLOGGING (LOGGING default)FORCE LOGGING {ON/OFF(default)}
ONLINE/OFFLINE (Online default)
EXTENT MANAGEMENT { [DICTIONARY] /
[LOCAL Default (AUTOALLOCATE / UNIFORM SIZE <integer K/M >)] }
AUTO SEGMENT MANAGEMENT { AUTO/MANUAL}
PERMANENT  / TEMPORARY (Permanent default)
MINIMUM EXTENT
DEFAULT STORAGE  {    [INITIAL <integer K/M >]
[NEXT <integer K/M >]
[PCTINCREASE <integer K/M >]
[MINEXTENTS <integer>]
[MAXEXTENTS <integer> / UNLIMITED]
[FREELISTS <integer>]
[FREELIST GROUPS <integer>]
[OPTIMAL <integer>/NULL]
[BUFFER_POOL < DEFAULT/KEEP/RECYCLE >] }

Various Option of Create tablespace statement are explained below

UNDO/TEMPORARY

Temp or temporary tablespaces are used to store data with short lifespan (transient data)

Undo tablespaces are used to store “before image” data that can be used to undo transactions

If nothing is given then it is a default tablespace which can store normal table/index segments

DATAFILE

Specifies the location and name of datafile

SIZE

specifies the size of datafile in Kb/Mb

BLOCK-SIZE

specified the block size for the tablespace

LOGGING/NOLOGGING

specifies if writing to the redo log is done

FORCE LOGGING

If it is On, even no logging operation in any segment  in the tablespace are written to redo

ONLINE/OFFLINE

It specifies if tablespace will be placed online after creation

AUTOEXTEND

ON means datafile can be auto extended. Off means no auto extension

TEMPORARY/PERMANENT

It specifies if tablespace holds permanent or temporary objects

DEFAULT STORAGE

specifies storage parameters for all segments created in the dictionary managed tablespace

MINIMUM EXTENT

specifies the minimum size for any extent in the tablespace

EXTENT MANAGEMENT

There are 2 types of space management

LOCALLY MANGED Locally managed tablespaces manage all extent allocations in the datafile header using a bitmap. The advantages of using locally managed tablespaces are less space in system tablespace (extent management not saved in data dictionary), reduce contention on data dictionary tables, eliminates the need to coalesce free extents, do not generate redo

DICTIONARY-MANAGED Dictionary managed tablespace records all extent allocations in the data dictionary tables.

One can specify the EXTENT MANAGEMENT clause in the CREATE TABLESPACE command and specify the management type (DICTIONARY/LOCAL).

If  LOCAL option is selected, one can specify the extent size using UNIFORM SIZE n Kb/Mb clause. The extent size and allocation table are kept in the datafile header.

Altering storage parameters for locally managed tablespace is not allowed. In order to change storage parameters, one needs to create a new tablespace with new storage parameters and move all segments to newly created tablespace.

Segment Space management

There are two options

Manual

Manual  you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management because of the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.

MANUAL is the default.

Auto

AUTO enable the  use of bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management

Various Example of Create tablespace statements/oracle create tablespace 11g

Permanent tablespaces

Permanent tablespaces are used to store user data and user created objects like tables, indexes and materialized views.

Single datafile
CREATE TEMPORARY TABLESPACE EXAMPLE  DATAFILE ‘/u01/oracle/TEST/oradata/example_1.dbf’ SIZE 1000M;
Multiple tempfile
CREATE TABLESPACE EXAMPLE  DATAFILE
‘/u01/oracle/TEST/oradata/example_1.dbf’ SIZE 1000M
‘/u01/oracle/TEST/oradata/example_2.dbf’ SIZE 1000M;

Temporary tablespace in Oracle

Temp or temporary tablespaces are used to store data with short lifespan (transient data), for example: global temporarily tables or sort results.

Single tempfile
CREATE TEMPORARY TABLESPACE TEMP  TEMPFILE ‘/u01/oracle/TEST/oradata/temp_1.dbf’ SIZE 1000M;
Multiple tempfile
CREATE TABLESPACE TEMP  TEMPFILE
‘/u01/oracle/TEST/oradata/temp_1.dbf’ SIZE 1000M
‘/u01/oracle/TEST/oradata/temp_2.dbf’ SIZE 1000M
;

Undo tablespace in oracle

Undo tablespaces are used to store “before image” data that can be used to undo transactions.

Single datafile
CREATE UNDO TABLESPACE UNDO_TBS1  DATAFILE ‘/u01/oracle/TEST/oradata/undo_1.dbf’ SIZE 1000M;
Multiple datafile
CREATE UNDO TABLESPACE UNDO_TBS1  DATAFILE
‘/u01/oracle/TEST/oradata/undo_1.dbf’ SIZE 1000M
‘/u01/oracle/TEST/oradata/undo_2.dbf’ SIZE 1000M
;

Other examples

Tablespace created with extent management local  of uniform size 1M and segment space management auto

CREATE TABLESPACE TEST  DATAFILE ‘/u01/oracle/TEST/oradata/test_1.dbf’ SIZE 1000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

 

Tablespace created with extent management local  of uniform size 1M and no automatic segment space management

CREATE TABLESPACE TEST  DATAFILE ‘/u01/oracle/TEST/oradata/test_1.dbf’ SIZE 1000MEXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created with extent management local  of Auto allocate size and no automatic segment space management

CREATE TABLESPACE TEST  DATAFILE ‘/u01/oracle/TEST/oradata/test_1.dbf’ SIZE 1000M
EXTENT MANAGEMENT LOCAL Autoallocate;

 

Tablespace created with dictionary extent management

CREATE TABLESPACE TEST  DATAFILE ‘/u01/oracle/TEST/oradata/test_1.dbf’ SIZE 1000M
EXTENT MANAGEMENT dictionary;

 

Tablespace created with local extent management  and OMF with Oracle ASM diskgroups

CREATE TABLESPACE TEST  DATAFILE ‘+DATA’    SIZE 1000M
EXTENT MANAGEMENT local;

File name is automatically managed by Oracle

Various Tablespace alteration options:

How to Add Datafile in a Oracle Tablespace

 

How to modify the existing Datafile  auto extend characteristics in a Tablespace


you can modify datafile  auto extend   using alter database datafile option

Syntax
ALTER DATABASE DATAFILE <location_of_datafile> AUTOEXTEND ON|OFF NEXT <size> MAXSIZE <size>;
Example
ALTER DATABASE DATAFILE ‘/u01/oracle/TEST/oradata/test_4.dbf’ AUTOEXTEND ON NEXT 50M MAXSIZE 2400M;

How to alter the tablespace to offline:

-Taking a tablespace offline/online is done using the ALTER TABLESPACE ‘name’ OFFLINE/ONLINE clause.
There are several options for taking tablespace offline:

Normal It flushes all data blocks that belong to the tablespace from the SGA
Temporary It performs checkpoint for the selected tablespace only
Immediate It does not perform checkpoint or flush data blocks – requires media recovery
For recover It is used for tablespace point in time recovery (TSPITR)

 

How to alter the tablespace to read-only:

It allows read only operations on tablespace (no DML), objects can be dropped from the tablespace

Syntax
ALTER TABELSPACE ‘name’ READ ONLY;
Example
ALTER TABELSPACE TEST READ ONLY;

Here is what happens when you issue statement for alter tablespace read only

1) First the tablespace is put into a transitional read-only mode
2) Now the ALTER command waits for existing transactions to complete by committing or by rolling back. No further DML operations are allowed to the tablespace, and if a DML statement attempts further changes, then an error is returned.

The  statement waits for the transactions which have pending or uncommitted changes to the tablespace and that were started before you issued the statement to either commit or roll back .

If statement is hung,Here is what you can do
a) First you need to identify the transaction entry for the ALTER TABLESPACE…READ ONLY statement and displays its session address (saddr):

SELECT SQL_TEXT, SADDR
FROM V$SQLAREA,V$SESSION
WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS
AND SQL_TEXT LIKE ‘alter tablespace%’;

SQL_TEXT SADDR


alter tablespace exampl read only 800473470

b) All transactions with smaller start SCN, which indicates an earlier execution, can potentially hold up the quiesce and subsequent read-only state of the tablespace.

SELECT SES_ADDR, START_SCNB
FROM V$TRANSACTION
ORDER BY START_SCNB;

SES_ADDR START_SCNB


800453470 7621 –> waiting on this txn
800467547 7623 –> waiting on this txn
800473470 7628 –> this is the ALTER TABLESPACE statement

You can now find the owners of the blocking transactions.

SELECT T.SES_ADDR, S.USERNAME, S.MACHINE
FROM V$SESSION S, V$TRANSACTION T
WHERE T.SES_ADDR = S.SADDR
ORDER BY T.SES_ADDR

SES_ADDR USERNAME MACHINE


800453470 USER1 MACH1
800467547 USER2 MACH2

We can contact both the users and terminates the sessions.

After making the tablespace read-only, it is advisable to back it up immediately. As long as the tablespace remains read-only, no further backups of the tablespace are necessary, because no changes can be made to it.

How to drop the tablespace

Dropping the tablespace  means tablespace is removed from data dictionary, contents are removed from data dictionary (optional), and datafiles are deleted (optional),

When the tablespace does not  have any contents
Syntax
DROP  TABELSPACE ‘name’;
Example
DROP  TABELSPACE ‘TEST’;
When the tablespace  have any contents
Syntax
DROP  TABELSPACE ‘name’ INCLUDING CONTENTS ;
Example
DROP  TABELSPACE ‘TEST’ INCLUDING CONTENTS ;
When the tablespace  have any contents and datafiles from OS also need to deleted
Syntax
DROP  TABELSPACE ‘name’ INCLUDING CONTENTS and DATAFILES ;
Example
DROP  TABELSPACE ‘TEST’ INCLUDING CONTENTS and DATAFILES ;

How to increase/autoextend the size of the tablespace

The increase can be done automatically or manually.

Automatically – using the AUTOEXTEND ON MAXSIZE n Kb/Mb clause.

Syntax
ALTER DATABASE DATAFILE <location_of_datafile> AUTOEXTEND ON|OFF NEXT <size> MAXSIZE <size>;
Example
ALTER DATABASE DATAFILE ‘/u01/oracle/TEST/oradata/test_4.dbf’ AUTOEXTEND ON NEXT 50M MAXSIZE 2400M;

Manually – ALTER TABLESPACE ‘name’ ADD DATAFILE ‘name’ clause  or
ALTER DATABASE DATAFILE  ‘DATAFILE NAME’  resize <bigger size>;

Syntax
ALTER TABLESPACE <tablespace_name> ADD DATAFILE <location_of_datafile> ;
Example
ALTER TABLESPACE TEST ADD DATAFILE ‘/u01/oracle/TEST/oradata/test_4.dbf’ SIZE 1000M
ALTER DATABASE DATAFILE ‘/u01/oracle/TEST/oradata/test_4.dbf’ RESIZE 2000M;

How to move tablespace/datafile to another location

In case of single or multiple datafile move in a tablespace,  steps are

  • Bring the tablespace offline
  • Move all the datafiles using OS utility
  • Rename the datafile using  alter tablespace command

ALTER TABLESPACE ‘name’ RENAME ‘file_name’ TO ‘file_name’

  • Bring the tablespace online

 

1) Alter tablespace TEST offline;
2) mv /u01/oracle/TEST/oradata/test_4.dbf   /u02/oracle/TEST/oradata/test_4.dbf
3) ALTER TABLESPACE ‘TEST’ RENAME /u01/oracle/TEST/oradata/test_4.dbf  ‘ TO ‘/u02/oracle/TEST/oradata/test_4.dbf  ‘;
4) Alter tablespace TEST online;

In case of multiple datafile of different tablespace  ,if we don’t  want to bring all tablespace to offline,we can use the alter datafile command are

  • Bring the datafile offline( This is valid if the database is in archive log mode else we need to start the database in mount state)
  • Move all the datafiles using OS utility
  • Rename the datafile using  alter database command
  • Alter datafile RENAME DATAFILE ‘file_name’ TO ‘file_name’ ;
  • Recover the datafile and bring it online

 

Alter database datafile  ‘/u01/oracle/TEST/oradata/test_4.dbf’   offline;
mv /u01/oracle/TEST/oradata/test_4.dbf   /u02/oracle/TEST/oradata/test_4.dbf
ALTER database datafile RENAME /u01/oracle/TEST/oradata/test_4.dbf  ‘ TO ‘/u02/oracle/TEST/oradata/test_4.dbf  ‘;
Recover datafile ‘/u02/oracle/TEST/oradata/test_4.dbf  ‘;
Alter database datafile  ‘/u02/oracle/TEST/oradata/test_4.dbf’   online;

This move has become online from 12c

Online move of active datafile in 12c

 

How to create datafile/tablespace using OMF

OMF stands for Oracle managed file.It has following features

a)Database files are easily distinguishable from all other files.

 

  1. b) Files of one database type are easily distinguishable from other database types.

 

  1. c) Files are clearly associated with important attributes specific to the file type. For example, a datafile name may include the tablespace name to allow for easy association of datafile to tablespace, or an archived log name may include the thread, sequence, and creation date.

We need to set  DB_CREATE_FILE_DEST parameter in the database. Once it is set datafiles are created using OMF

Structure of OMF

<DB_CREATE_FILE_DEST>/<db_unique_name>/<datafile>/o1_mf_%t_%u_.dbf

Tablespace /datafile creation using OMF

CREATE TABLESPACE TEST size 800M;
Alter tablespace add datafile  size 800M;

 

How to assign tablespaces to users

Users cannot create objects in a tablespace (even it’s their default tablespace) unless they have a quota on it (or UNLIMITED TABLESPACE privilege).

 

Grant user  PER access to use all space in the APPS_TX tablespace:

ALTER USER PER QUOTA UNLIMITED ON APPS_TX;

Dictionary views for Viewing Tablespace Information

Data dictionary for Oracle tablespace

 

Related Articles

Oracle tablespace documentation

Oracle documentation

How to Drop a datafile from Tablespace in Oracle

How to add datafile to tablespace in Oracle

Tablespace Name ,Size ,Free space,datafiles ,growth in Oracle Database

How to shrink the datafile in Oracle

ORA-01652: unable to extend temp segment: Solutions

 

Leave a Reply