Oracle - Common Datafile Operations

Adding a table space

ALTER TABLESPACE users
ADD DATAFILE ‘/home/ssinghal/oradata/users01.dbf' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M

Adding a table space with minimal options

ALTER TABLESPACE name ADD DATAFILE ‘filespec’ SIZE 200M;


Altering a tablespace by adding a new datafile

ALTER TABLESPACE VPN2_LRG_TBLS
ADD DATAFILE '/home/ssinghal/oradata/users02.dbf' SIZE 100M
AUTOEXTEND ON
NEXT 1024K
MAXSIZE 250M


Resizing a data-file

alter database datafile '/home/ssinghal/oradata/users02.dbf' resize 500M


Dropping a datafile

alter database datafile 20 offline drop

Note : ID of datafile can be obtained from dba_data_file