1- Create a new Tablespace:

CREATE TABLESPACE TEST DATAFILE 'D:\TEST\TEST١.DBF' SIZE 100M NOLOGGING;

Since we did not specify the type of Tablespace management, the origin is Locally Managed Tablespace.

CREATE TABLESPACE TEST DATAFILE 'D:\TEST\TEST.DBF' SIZE ٠١M EXTENT MANAGEMENT LOCAL UNIFORM 200M;

2- Increase the size of the tablespace:

The first method is to add a new Data File to the same Tablespace.

ALTER TABLESPACE TEST ADD DATAFILE ‘D:\TEST\TEST.DBF’ SIZE 500M; 

The second method is to increase the size of the existing data file.

ALTER DATABASE DATAFILE 'D:\TEST\TEST.DBF' RESIZE 200M; 

3- Delete the Tablespace:

The first method is to delete only the Tablespace without the Data Files.

DROP TABLESPACE TEST; 

This way the Data File remains on the operating system and can be used when creating a new Tablespace.

CREATE TABLESPACE OBAY DATAFILE 'D:\TEST\TEST.DBF' REUSE; 

The second way to delete the Tablespace and the Data Files.

DROP TABLESPACE OBAY INCLUDING CONTENTS AND DATAFILES; 

4- Convert the Tablespace to Read Only mode:

ALTER TABLESPACE TEST READ ONLY; 

Try to put the System Tablespace in Read Only mode, you will see an error message ORA-01643,

5- Convert the Tablespace to Read Write mode:

ALTER TABLESPACE TEST READ WRITE; 

6- Convert the Tablespace to OFFLINE mode:

ALTER TABLESPACE TEST OFFLINE; 

7- Convert the Tablespace to ONLINE mode:

ALTER TABLESPACE TEST ONLINE; 

8- Rename the Tablespace:

ALTER TABLESPACE TEST RENAM TO EXAM; 

9- Create Temporary Tablespace:

CREATE TEMPORARY TABLESPACE TEMPEXAM TEMPFILE 'D:\TEST\TEMPEXAM.DBF' SIZE 100M; 

So that this tablespace is the default temporary tablespace in the database.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPEXAM; 

You can check the Default Temporary Tablespace.

SELECT PROPERTY_NAME,PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

10- Create Temporary Tablespace Group:

It is a new feature in Oracle 10g release that is used when the resource used for temporary storage is low.

RCREATE TEMPORARY TABLESPACE TEMPG١ TEMPFILE 'D:\TEST\TEMPG١.DBF' SIZE 50M TABLESPACE GROUP GROUB1; 

Add an existing Tablespace to the Group.

ALTER TABLESPACE TEMPEXAM TABLESPACE GROUP GROUP1; 

You can also create another Tablespace for the same Group.

11- Create a Big Tablespace:

CREATE BIGFILE TABLESPACE BIG١ DATAFILE ‘D:\TEST\BIG١.BDF’ SIZE 60M;

To inquire about DATAFILES & TABLESPACES:

DBA_TABLESPACES

USER_TABLESPACES

DBA_TABLESPACE_GROUPS

V$TABLESPACE

DBA_DATA_FILES

DBA_TEMP_FILES