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.
    Blogger Comment
    Facebook Comment

0 comments:

Post a Comment