Oracle Data File Management

1. Manually change the data file size

SQL> conn/as sysdba

SQL> Createtablespace exampletb Datafile 'e: \ examp01.dbf' size 10 M;

SQL> alter database datafile 'e: \ examp01.dbf' resize 20 m;

SQL> alter database datafile 'e: \ examp01.dbf' resize 9 m;

2. Add data files to the tablespace

SQL> alter tablespace exampletb add datafile 'e: \ examp02.dbf' size 10 M;

3. delete data files from tablespace

SQL> alter tablespace exampletb drop datafile 'e: \ examp02.dbf ';

4. Set the tablespace to read-only and read-write modes.

SQL> CREATETABLE scott. student (id NUMBER (5), name VARCHAR2 (10) TABLESPACE exampletb;

SQL> insertinto scott. student (id, name) values (1, 'Lucy ');

SQL> alter tablespace exampletb read only;

SQL> insertinto scott. student (id, name) values (2, 'lily ');

SQL> select * from scott. student;

SQL> alter tablespace exampletb read write;

SQL> insertinto scott. student (id, name) values (2, 'lily ');

5. Set the tablespace to online or offline.

SQL> alter tablespace exampletb offline;

SQL> select * from scott. student;

SQL> alter tablespace exampletb online;

SQL> select * from scott. student;

6. query table space and data files

SQL> select * from v $ tablespace;

SQL> select * from dba_tablespaces;

SQL> select * from v $ datafile;

SQL> select * from dba_data_files;