Oracle DBA FAQ - Managing Oracle Tablespaces and Data Files

Home >> FAQs/Tutorials >> Oracle DBA FAQ >> Index

By: FYIcenter.com

Part: 1 2 3 4

(Continued from previous part...)

How To Bring a Tablespace Online?

If you have brought a tablespace offline, now you want to make it available to users again, you can use the ALTER TABLESPACE ... ONLINE statement as shown in the following script:

SQL> connect HR/fyicenter

SQL> CREATE TABLESPACE my_space 
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> ALTER TABLESPACE my_space OFFLINE NORMAL;
Tablespace altered.

...

SQL> ALTER TABLESPACE my_space ONLINE;
Tablespace altered.

How To Add Another Datafile to a Tablespace?

If you created a tablespace with a data file a month ago, now 80% of the data file is used, you should add another data file to the tablespace. This can be done by using the ALTER TABLESPACE ... ADD DATAFILE statement. See the following sample script:

SQL> connect HR/fyicenter

SQL> CREATE TABLESPACE my_space 
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> ALTER TABLESPACE my_space 
  2  DATAFILE '/temp/my_space_2.dbf' SIZE 5M;
Tablespace altered.

SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES
  2  FROM DBA_DATA_FILES;
TABLESPACE_NAME FILE_NAME                             BYTES
--------------- --------------------------------- ---------
USERS           C:\ORACLEXE\ORADATA\XE\USERS.DBF  104857600
SYSAUX          C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 461373440
UNDO            C:\ORACLEXE\ORADATA\XE\UNDO.DBF    94371840
SYSTEM          C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 356515840
MY_SPACE        C:\TEMP\MY_SPACE.DBF               10485760
MY_SPACE        C:\TEMP\MY_SPACE_2.DBF              5242880

SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES 
  2  FROM USER_FREE_SPACE 
  3  WHERE TABLESPAE_NAME IN ('MY_SPACE');
TABLESPACE_NAME                   FILE_ID      BYTES
------------------------------ ---------- ----------
MY_SPACE                                6    5177344
MY_SPACE                                5   10354688

This script created one tablespace with two data files.

What Happens If You Lost a Data File?

After you shuting down an Oracle database, you accidently deleted a data file from the operating system. If you try to start the database again you will get error when Oracle tries to open the database after mounting the database. The following tutorial examples shows you what will happen if the data file c:\temp\my_space.dbf is deleted. Oracle can still start the database instance and mount the database. But it will fail on openning the database as shown below in a SQL*Plus session:

>sqlplus /nolog

SQL> connect SYSTEM/fyicenter AS SYSDBA

SQL> STARTUP
ORACLE instance started.

Total System Global Area  100663296 bytes
Fixed Size                  1285956 bytes
Variable Size              58720444 bytes
Database Buffers           37748736 bytes
Redo Buffers                2908160 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR
  trace file
ORA-01110: data file 5: 'C:\TEMP\MY_SPACE.DBF'

SQL> SHUTDOWN;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

How Remove Data Files befor opening a Database?

Let's say you have a corrupted data file or lost a data file. Oracle can mount the database. But it will not open the database. What you can do is to set the bad data file as offline befor opening the database. The tutorial exercise shows you how to set two data files offline and open the database without them:

>sqlplus /nolog

SQL> connect SYSTEM/fyicenter AS SYSDBA

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area  100663296 bytes
Fixed Size                  1285956 bytes
Variable Size              58720444 bytes
Database Buffers           37748736 bytes
Redo Buffers                2908160 bytes
Database mounted.

SQL> ALTER DATABASE DATAFILE '\temp\my_space.dbf'
  2  OFFLINE DROP;
Database altered.

SQL> ALTER DATABASE DATAFILE '\temp\my_space_2.dbf'
  2  OFFLINE DROP;
Database altered.

SQL> ALTER DATABASE OPEN;
Database altered.

SQL> col file_name format a36;
SQL> col tablespace_name format a16;
SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES
  2  FROM DBA_DATA_FILES;

TABLESPACE_NAME FILE_NAME                             BYTES
--------------- --------------------------------- ---------
USERS           C:\ORACLEXE\ORADATA\XE\USERS.DBF  104857600
SYSAUX          C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 503316480
UNDO            C:\ORACLEXE\ORADATA\XE\UNDO.DBF    94371840
SYSTEM          C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 367001600
MY_SPACE        C:\TEMP\MY_SPACE.DBF
MY_SPACE        C:\TEMP\MY_SPACE_2.DBF

At this point, if you don't care about the data in MY_SPACE, you can drop it now with the database opened.

Part: 1 2 3 4

Selected Developer Jobs:

More...