Oracle tablespaces

Oracle Tablespace is a logical storage unit for oracle Database, every tablespace consist of one or more physical data file, each data file belongs to one and only one tablespace.
Oracle tablespace contains objects like Table, Index and any other objects and they should belong to only one tablespace.

Tablespace types

Oracle Database consist of many different Tablespace types:
1- Permanent Tablespace.
2- Temporary Tablespace.
3- Undo Tablespace.

Oracle, automatically creates some tablespaces during setup like SYSTEM, SYSAUX, TEMP and UNDO tablespace.

Create Tablespace

1 2	CREATE TABLESPACE MYAPP_TS DATAFILE 'c:\data\myapp_ts.dbf' SIZE 100M

This statement creates a tablespace named "MYAPP_TS" with the datafile 'myapp_ts.dbf' of a size 100 megabytes, on C: drive, under the directory '\data\'.

Options for Tablespace:

EXTENT MANAGEMENT LOCAL	record extent allocation in the tablespace header.
EXTENT MANAGEMENT DICTIONARY	record extent allocation in the dictionary.

AUTOALLOCATE	specifies that extent sizes are system managed
UNIFORM SIZE 128K	specifies that the tablespace is managed with uniform extents of SIZE bytes

SEGMENT SPACE MANAGEMENT AUTO

AUTO managed segments space for a tablespace.

Example for create Tablespace:

1 2 3 4 5

CREATE TABLESPACE MYAPP_TS DATAFILE 'c:\data\myapp_ts.dbf' SIZE 100M ONLINE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Options for Datafiles:

REUSE

reuse the datafile if its already exist.

AUTOEXTEND OFF	no extend when datafile is full
AUTOEXTEND ON NEXT 2M MAXSIZE 20M	when datafile is full still extend 2M as needed until reach 20M
AUTOEXTEND ON NEXT 2M MAXSIZE UNLIMITED	when datafile is full still extend 2M as needed with no limit.

Dropping A Tablespace

1	DROP TABLESPACE MYAPP_TS;

1	DROP TABLESPACE MYAPP_TS INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Note that dropping a tablespace produces a structural change to the database that cannot be reversed without a backup.

Quotas On Tablespaces

1	ALTER USER USER1 QUOTA 100M ON MYAPP_TS;

Users can be assigned quotas (limits) on on the space they may use for tablespaces.

Assign a tablespace to a tablespace group

1	ALTER TABLESPACE MYAPP_TS TABLESPACE GROUP MY_TS_Group1;

Renaming Tablespaces

1	ALTER TABLESPACE MYAPP_TS RENAME TO MY_NEW_TS;

For more Info:

CREATE TABLESPACE
CREATE TEMPORARY TABLESPACE
Managing Tablespaces
CREATE TABLESPACE
Oracle/PLSQL: CREATE TABLESPACE statement

If this post was good and helpful for you, Please give it Like.

.