Manage Tablespace in Oracle

Dropping Tablespaces

DROP TABLESPACE userdata INCLUDING CONTENTS AND DATAFILES;

Enabling Automatic Extension

ALTER DATABASE DATAFILE '/u01/oradata/userdata02.dbf' SIZE 200M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

RESIZING DATAFILES:

ALTER DATABASE DATAFILE '/oradata/imstest/users01.dbf' RESIZE 50M;

Dropping a datafile:

ALTER DATABASE DATAFILE '/orasoft/10.2.0/dbs/undo102.dbf' OFFLINE DROP;

Adding Data Files to a Tablespace

ALTER TABLESPACE users ADD DATAFILE '/oradata/imstest/users02.dbf'
SIZE 200M;