[adsense id=”0514458240″ width=”468″ height=”60″]
The following scenario occurs frequently, and while is should be monitored a quickly growing database with tablespaces in auto extend mode with no high limit, can cause the file system to fill up and thus the database can no longer function correctly.
If capacity is available on another disk we can issue the following set of commands:
The first step is to stop the Oracle server. The main reason for this is to then bring the database server up in mount state. Having the data server in mount state allows Oracle to read the control files which contains information about the setup of the database i.e. the location of the data files, tablespaces etc.
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
Now we restart the database in mount state – this is important as we will need to rename the data files after they have been moved to a new disk and this is only possible in mount state.
SQL> startup mount ORACLE instance started. Total System Global Area 134217728 bytes Fixed Size 1257596 bytes Variable Size 79695748 bytes Database Buffers 50331648 bytes Redo Buffers 2932736 bytes Database mounted.
Now that we have this in mount state, let’s take a look at the datafiles belonging to the database server:
SQL> select d.name, d.bytes/1024/1024 mbytes, t.name tablespace 2 from v$datafile d, v$tablespace t 3 where d.ts# = t.ts# 4 ; NAME MBYTES TABLESPACE -------------------------------------------------- ---------- ------------------------------ /usr/lib/oracle/xe/oradata/XE/system.dbf 350 SYSTEM /usr/lib/oracle/xe/oradata/XE/undo.dbf 210 UNDO /usr/lib/oracle/xe/oradata/XE/sysaux.dbf 340 SYSAUX /usr/lib/oracle/xe/oradata/XE/users.dbf 0 USERS
The second field defines the actual size of the data files in Mbytes. In this case here we are going to move the data file ‘/usr/lib/oracle/xe/oradata/XE/users.dbf’ to ‘/tmp/users.dbf’. This is to demonstrate the steps – we strongly recommend moving the data file to a properly mirrored disk instead of /tmp.
shell> mv /usr/lib/oracle/xe/oradata/XE/users.dbf /tmp
Now that we have freed up capacity we need to notify Oracle that the data file has moved.
SQL> ALTER DATABASE RENAME FILE '/usr/lib/oracle/xe/oradata/XE/users.dbf' TO '/tmp/users.dbf'; Database altered.
Now we can Open the database.
SQL> alter database open; Database altered.