alter tablespace

• CREATE DATABASE

• CREATE TABLESPACE ... DATAFILE

• ALTER TABLESPACE ... ADD DATAFILE

################################

ALTER DATABASE DATAFILE filespec [autoextend_clause]

autoextend_clause:== [ AUTOEXTEND { OFF|ON[NEXT integer[K|M]]

[MAXSIZE UNLIMITED | integer[K|M]]

#################################

CREATE TABLESPACE userdata

DATAFILE '/u01/oradata/userdata01.dbf'

SIZE 500M EXTENT MANAGEMENT DICTIONARY

DEFAULT STORAGE

(initial 1M NEXT 1M PCTINCREASE 0);

******************************************

ALTER TABLESPACE undotbs

ADD DATAFILE '/u01/oradata/undotbs2.dbf'

SIZE 30M

AUTOEXTEND ON;

******************************************

CREATE TABLESPACE userdata02

 DATAFILE '/u01/oradata/userdata02.dbf' SIZE 5M

 AUTOEXTEND ON NEXT 2M MAXSIZE 200M;

 By specifying AUTOEXTEND after tablespace creation

*******************************

SQL> ALTER DATABASE

2 DATAFILE '/u01/oradata/userdata02.dbf'

3 AUTOEXTEND ON NEXT 2M;

- Manually

******************************

SQL> ALTER DATABASE

2 DATAFILE '/u01/oradata/userdata02.dbf' RESIZE 5M;

*************************************

 CREATE TEMPORARY TABLESPACE "xxxxxx" TEMPFILE

 'xxxxxx.dbf' SIZE 838860800 REUSE

 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576