|
Category :
Database
Resources -> RDBMS Server
DB Version
:
Oracle 8i /
Oracle 9i
OS Details :
Sun Solaris9
Sometimes,
we run in to the situation where after the tablespace and the
corresponding datafiles have been dropped from the oracle database,
space is not released back to the filesystem. This can potentially
happen often when you run network mounted disks. This article
discusses possible fixes for situations when the space is not released
back to the machine.
The case of space not being
released back to the unix filesystem usually happens when oracle
continues to have locks on the files and the OS thinks that those
files are still held open by oracle. Follow these steps when this
situation occurs:
(1) Change directory to the
directory where the files for the tablespace were removed:
cd /ora9idata/data
(2) Run this command to get the
OS process ids that are holding the files open:
lsof .
For a network mounted filesystem,
the output will be something like this (if you had tried removing the
datafiles earlier):
COMMAND PID
USER FD TYPE DEVICE
SIZE/OFF NODE
NAME
oracle 7028 ora9i
378r VREG 307,3 2098216960
14382072 ./.nfs123E
Note that the file names are
showing up as nfs123E since you have already tried removing the file
from the machine using the "rm filename" command.
(3) Make sure that the process
holding the file open is not a background process. This can be checked
by running this query :
select b.sid,b.serial#,(case
when background is null then 'NOT A BACKGROUND PROCESS' else
'BACKGROUND PROCESS' end) ProcessType from v$process a,v$session b
where spid=<Process id from running lsof command above> and a.addr =
b.paddr;
The value for ProcessType column
returned by the above query should be : "NOT A BACKGROUND
PROCESS".
(4) If the SQL statement from
step 3 above returns a row and is not a background process then you
can go ahead and kill the session using this command:
alter system kill session 'b.sid,b.serial#';
On the other hand, If the above
query returns no rows then chances are that it is longer a valid
process within oracle in which case you can kill the process id from
the machine. On unix, processes can be killed using the "kill"
command:
kill -9 <process id>
Completing the above steps should
release the space back to the filesystems.
The
"lsof" command, in addition to being used as described above has other
uses as well. Other uses for this command include:
(a) Can be used for managing
alert log files, listener logs and trace files. Use "lsof" to find out
if there are active oracle processes using the alert or listener log
or trace files and if there aren't any then you can go ahead and
compress the old files and create new empty ones for oracle to
continue logging in to.
(b) The utility can also be used
to find out if a particular port on the machine is already being used
by a process which can potentially prevent processes like oracle
listener from starting. Here's how you find the port details:
lsof -i TCP:<port number>
Note: The "lsof" is
a freeware utility for Sun Solaris that can be downloaded from this
location:
http://www.sunfreeware.com/programlistsparc7.html#lsof32
|
