Create Tablespace in ORACLE 10g

Create Tablespace in ORACLE 10g

Permanent tablespace

Adding Tablespace in database:

CREATE SMALLFILE TABLESPACE BU_SYSTEM_TBS DATAFILE '$DATAFILE_LOCATION/BU_SYSTEM_TBS' SIZE 100M AUTOEXTEND ON NEXT 1000M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Adding Datafile in a Tablespace:

Use ALTER TABLESPACE to add datafile in tablespace:

ALTER TABLESPACE BU_HIS_LOG_TBS ADD DATAFILE '$DATAFILE_LOCATION/BU_HISTLOG_TBS_01.dbf' SIZE 1000M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED;

Modify Datafile:

You can modify datafile using ALTER DATABASE command:

ALTER DATABASE DATAFILE ’$DATAFILE_LOCATION/data01.dbf’ AUTOEXTEND ON NEXT 30M MAXSIZE 1200M;

which means datafile data02.dbf can automatically grow upto 1200 MB size in blocks of 30 MB each time as required.

Renaming Tablespaces:

This is a feature that is available with Oracle 10g and can be useful in transportable tablespace scenarios.

alter tablespace ts_user rename to ts_user_01;

ALTER TABLESPACE BU_IMAGE TO BU_PICTURE;

----------------------------------------------------------------------------------------------------------