How can we release the unused allocated blocks to the openvms file ?

[Chinraj Rajasekaran]

Hi,

we are running AlphaServer 4100 5/400 4MB running OpenVMS V7.1-2 clustering 2 alpha nodes with common storage.

Currently we have problems with one of the oracle database rollback segment DBS file allocated with large unused blocks.

***********

TS_ROLLBACK.DBS;1 204800/1024012

*******************

i already tried

*****************************
$ set file/trun TS_ROLLBACK.DBS

%SET-E-READERR, error reading DKC2:[ORACLE.DB_P73LAB]TS_ROLLBACK.DBS;1
-SYSTEM-W-ACCONFLICT, file access conflict
**********************************

we also tried with database complete shutdown but still i get the same error as above.

how can we release the unused allocated blocks?

regards
Raj

[Karl Rohwedder · Accepted solution]

If the file is not locked by another process the SET FILE/TRUNCATE should do it.
But I do not know why Oracle allocates such an amount of blocks, may be preformance reasons to avoid costly extension when needing the blocks.

regards Kalle

[Hein van den Heuvel]

Hello Chinraj,

Welcome to the HP OpenVMS ITRC forum

I would STRONGLY recommend against manipulating Oracle controlled files outside Oracle.

The EOF marker is a STRICTILY OPTIONAL piece of information. It may or might not be honored by Oracle.

Someone told Oracle it could use those blocks, so don't go taking them away behind its back. Your first task is to talk to the DBA to find out how much space it believed to be needed.

Now if it is indeed overallocated, then there is hope. Since this appears to be a ROLLBACK segment (Or a very silly/funny DBA), you can just tell Oracle to
- create a new rollback tablespace
- disable the rollback segments in the current tablespace
- DROP the whole oversized tablespace and delete the files.
(or change the INIT.ORA to select different automatic undo segments).

Met vriendelijk groetjes,
Hein.

[Ian Miller.]

SET FILE/TRUNC should work but appears something was accessing the file.

SHOW DEV/FILES DKC2 will tell you which process has the file open (will have to do this on each node in the cluster).

[Robert Gezelter]

Raj,

So long as the file is being accessed for write/append in some way, the EOF block is also not reliable information.

- Bob Gezelter, http://www.rlgs.com

[Peter Zeiszler]

On both systems run:
show dev/files DKC2:

This will show you the processes that have the file locked so you can not access it.
Rarely will you see someone install this in memory and then the PID will be 000000.

Since this looks like an Oracle database file I would suggest NOT making that change. The oracle table is normally preallocated to a specific size. Talk to your Orace Database Admin to get details on table size and requirements.

[Chinraj Rajasekaran]

Hi,

Thanks all of you for your replies.

today we had some problems with our oracle database and we were getting "ORA-1562 Unable to extend roll back segments Error"

So DBA tried to increase the size roll back segments size. But it dont help. finally we had shutdown and restart the database.

Then DBA reverted back the changes - set the old file size for roll back segments.

So now DBA can see the size is less now.

But the free space is not released yet.

I already tried after we shutdown the database one more time and i checked for

show dev dkc2 /file

but no process was using the file.

SO i think the only option left out is as HEIN said, we have to try to create new roll back segment file and delete the existing one.

regards
Raj

[Chinraj Rajasekaran]

set file /trun worked just after reboot before database was started. Thanks all of your for your help.