oracle 11g resize tablespace
create bigfile tablespace testbig datafile '/u01/app/oracle/oradata/ORADBT/testbig.dbf' size 1G autoextend on;
1) for bigfile tablespace we can use
sql- alter tablespace test resize 1100M; [note- 1)only integer value will be allowed
2)you cannot more than one datafile to bigfile tablespace]
but for smallfile tablespace we need to use
sql- alter database datafile '/u01/app/oracle/oradata/ORADBT/testbig.dbf' resize 1G;
(same command is used to reduce the size of datafile)
2) alter tablespace test add datafile '/u01/app/oracle/oradata/ORADBT/test3.dbf' size 1G autoextend on;
NOTE- There is no direct SQL statement to drop datafiles from a tablespace .
In that case we need to drop the tablespace after all data has been moved to a new tablespace.