Useful shortcuts for vi editor

Sunday 26 October 2014

How to define a tablespace in Windows?

Please execute the commands as "sys";

To define the tablespace:
SQL> CREATE TABLESPACE ALPER_DATA 
DATAFILE 'C:\tablespaces\data_01.dbf' SIZE 128M

To alter the tablespace:
SQL> ALTER TABLESPACE ALPER_DATA ADD DATAFILE 'C:\tablespaces\data_new_02.dbf' size 128m;

To drop the datafile from the tablespace:
SQL> ALTER TABLESPACE ALPER_DATA DROP DATAFILE 'C:\tablespaces\data_new_02.dbf';

To drop the tablespace:
SQL> DROP TABLESPACE ALPER_DATA 
INCLUDING CONTENTS AND DATAFILES;

To check tablespace's database files:
SQL> SELECT NAME, FILE#, STATUS FROM V$DATAFILE;

Used Oracle Version and OS: Oracle XE and Win7