Creating and Handling Tablespaces

Creating a new Tablespace in Database:

Create Tablespace with  below command, mention

• Tablespace name
• data file path & name
• Size- Initial,,Extendable and Maximum Size of data file.

Initially a data file needs to be created to create a tablespace.

CREATE TABLESPACE <Tablespace_Name> DATAFILE <Path with data file Name>
    SIZE <Initial Size> REUSE AUTOEXTEND ON NEXT <Extentable value> MAXSIZE <Maximum size for this datafile>;

Example:

CREATE TABLESPACE ARMS_IDX DATAFILE 'D:\APP\ORADATA\ORCL\ARMS_IDX.dbf' SIZE 600M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 700M;

After Creating a Tablespace, additional datafiles could be added to increase size of tablespace.

ALTER TABLESPACE ARMS_IDX add datafile 'D:\APP\ORADATA\ORCL\arms_IDX1.dbf'  SIZE 30G AUTOEXTEND ON;