We will be discussing Oracle create tablespace statement/oracle create tablespace 11g/12c/19c in detail in this post. I will through light on all the option on create tablespace statement.I would also be discussing ,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
What is Oracle Tablespace?
Tablespace is a logical storage unit in Oracle Database. Oracle Tablespace consists of one or more data files.
Tablespace is further divided into logical unit Segments, Segment is divided into Extent and Extent into Block
Various types 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 data files belonging to the tablespace. It is made up of one or more extents
Extent
It is a set of contiguous data blocks, that 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 the 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 check Oracle Create tablespace statement later in this post
What are Datafiles in Oracle?
-It is a physical structure to store oracle data
-One or more physical datafiles are logically grouped to make a tablespace
-A Datafile can be associated with only one tablespace
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 statements.
CREATE [TEMPORARY / UNDO] TABLESPACE <tablespace_name>
BIGFILE | SMALLFILE
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)}
ENCRYPTION ENCRYPT | DECRYPT
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 >] }
For Container Database starting from 12c, please login to the corresponding PDB to create tablespace
alter session set container='TESTPDB';
CREATE [TEMPORARY / UNDO] TABLESPACE <tablespace_name>
BIGFILE | SMALLFILE
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 Options of Create tablespace statements are explained below
BIGFILE /SMALLFILE
Use this clause to determine whether the tablespace is a bigfile or smallfile tablespace. This clause overrides any default tablespace type setting for the database.
A bigfile tablespace contains only one data file or temp file, which can contain up to approximately 4 billion (232) blocks. The minimum size of the single data file or temp file is 12 megabytes (MB) for a tablespace with 32K blocks and 7MB for a tablespace with 8K blocks. The maximum size of the single data file or temp file is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.
A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 data files or temp files, each of which can contain up to approximately 4 million (222) blocks.
If you omit this clause, then Oracle Database uses the current default tablespace type of permanent or temporary tablespace that is set for the database. If you specify BIGFILE for a permanent tablespace, then the database by default creates a locally managed tablespace with automatic segment-space management.
Restriction on Bigfile Tablespaces
You can specify only one data file in the DATAFILE clause or one temp file in the TEMPFILE clause.
UNDO/TEMPORARY
Temp or temporary tablespaces are used to store data with a 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 that can store normal table/index segments
DATAFILE
Specifies the location and name of the 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 is written to redo
ONLINE/OFFLINE
It specifies if tablespace will be placed online after the creation
AUTO-EXTEND
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 MANAGED 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 the data dictionary), reduced contention on data dictionary tables, eliminate the need to coalesce free extents
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 the LOCAL option is selected, one can specify the extent size using the 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. To change storage parameters, one needs to create a new tablespace with new storage parameters and move all segments to the 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, free lists, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.
MANUAL is the default.
Auto
AUTO enables 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
ENCRYPTION ENCRYPT | DECRYPT
Use this clause to specify whether to create an encrypted or unencrypted tablespace. If you create an encrypted tablespace, then Transparent Data Encryption (TDE) is applied to all data files of the tablespace.
Specify ENCRYPT to create an encrypted tablespace. Specify DECRYPT to create an unencrypted tablespace.
If you omit this clause, then the value of the ENCRYPT_NEW_TABLESPACES initialization parameter determines whether the tablespace is encrypted upon creation.
Before issuing this clause, you must already have loaded the TDE master key into database memory
Various Examples of Create Tablespace statements/oracle create Tablespace 19c
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 a short lifespan (transient data), for example, global temporary 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
Creating a Bigfile Tablespace
To create a bigfile tablespace, the syntax is similar, but you use the BIGFILE keyword:
CREATE TABLESPACE TEST DATAFILE '/u01/oracle/TEST/oradata/test_1.dbf' SIZE 25G
AUTOEXTEND ON NEXT 2G MAXSIZE 400G
LOGGING;
Tablespace with encryption
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "wallet_password"; CREATE TABLESPACE encrypt_ts DATAFILE '/u01/oracle/TEST/oradata/test_1.dbf' SIZE 1M ENCRYPTION USING 'AES256' ENCRYPT;
How to verify the tablespace creation
SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME = 'your_tablespace_name';
Various Tablespace alteration options:
How to modify the existing Datafile auto extend characteristics in a Tablespace
you can modify the datafile auto-extend using the 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 is used for tablespace point-in-time recovery (TSPITR) |
| 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 state “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 that have pending or uncommitted changes to the tablespace and that were started before you issued the statement to either commit or rollback.
If the 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 users and terminate 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 the data dictionary, contents are removed from the data dictionary (optional), and data files 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 data files move in a tablespace, steps are
- Bring the tablespace offline
- Move all the datafiles using the OS utility
- Rename the datafile using the 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 data files 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 the OS utility
- Rename the datafile using the 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 onwards
Online move of active datafile in 12c
How to create datafile/tablespace using OMF
OMF stands for Oracle managed file. It has the following features
a)Database files are easily distinguishable from all other files.
- b) Files of one database type are easily distinguishable from other database types.
- c) Files are 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 the datafile to the tablespace, or an archived log name may include the thread, sequence, and creation date.
We need to set the 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 if 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:
Dictionary views for Viewing Tablespace Information
Related Articles
Oracle tablespace documentation
Oracle documentation
How to Drop a datafile from Tablespace in Oracle
add datafile to tablespace :Check out this post on How to add datafile to tablespace in Oracle, add tempfile to temporary tablespace,how to add datafile in ASM
How to check Tablespace in Oracle : Learn about How to Check Tablespace in Oracle, tablespace free space,tablespace growth information,tablespace size ,associated datafiles ,tablespace size
shrink datafile in Oracle : Check out how to shrink the datafile and reclaim space on the filesystem. How to resolve ORA-03297
ORA-01652 :ORA-01652 error usually because when the tablespace does not have free space in Permanent and Temporary tablespace in oracle database.Check out how to resolve it