ALTER DATABASE DATAFILE...ORA-03297...HELP!

[John Dunn]

Greetings:

I'm hoping someone can help me or point me in the right direction..

Here's what I'm trying to do:

I'm trying to resize a datafile in my RBS (Rollback) Tablespace from
400M to 150M. When I issue the command

ALTER DATABASE DATAFILE 'f:\mydatafile.dbf' RESIZE 150M

I get the "ORA-03297: File contains 4100 blocks of data beyond requested
resize value" message. I figure that this means that there are still
active transactions in the rollback file. The question is, how do I
commit these transactions? I really don't care about any data that might
be in the file. I've tried shutting down the database and bringing it
back online again but this doesn't help. I got myself into this fix by
setting autoextend ON...

How can I resize the datafile? Or how do I drop it and re-create it?
Thanks for any pointers and/or references to the documentation. I've
looked but it's rather muddy.

My environment: Workgroup Server 7.3.2 on NT 3.51.

[Hassan Abba]

Try:

ALTER DATABASE DATAFILE 'D....' OFFLINE;

This can only be done if the media recovery is enabled.

Then try to resize it.

Regards

Hassan Abba
Database Developer/Administrator
Soft Options UK Ltd

John Dunn wrote:

> Greetings:
>
> I'm hoping someone can help me or point me in the right direction..
>
> Here's what I'm trying to do:
>
> I'm trying to resize a datafile in my RBS (Rollback) Tablespace from
> 400M to 150M. When I issue the command
>
> ALTER DATABASE DATAFILE 'f:\mydatafile.dbf' RESIZE 150M
>
> I get the "ORA-03297: File contains 4100 blocks of data beyond requested

> resize value" message. How can I resize the datafile? Or how do I drop it

[Halina Monka]

You will not be able to offline tablespace which has active rollback
segment. And ALL of your user rollback segs are in one tablespace,
right.
First create rollback segment in another tablespace, make sure that your
session uses this rollback then bring the tablespace offline.
In this case I would drop the rollback tablespace and create it again
with new storage parameters.
Halina

[Norbert Ferstl]

John Dunn wrote:
>
> Greetings:
>
> I'm hoping someone can help me or point me in the right direction..
>
> Here's what I'm trying to do:
>
> I'm trying to resize a datafile in my RBS (Rollback) Tablespace from
> 400M to 150M. When I issue the command
>
> ALTER DATABASE DATAFILE 'f:\mydatafile.dbf' RESIZE 150M
>
> I get the "ORA-03297: File contains 4100 blocks of data beyond requested

> resize value" message. I figure that this means that there are still
> active transactions in the rollback file. The question is, how do I
> commit these transactions? I really don't care about any data that might
> be in the file. I've tried shutting down the database and bringing it
> back online again but this doesn't help. I got myself into this fix by
> setting autoextend ON...
>

> How can I resize the datafile? Or how do I drop it and re-create it?
> Thanks for any pointers and/or references to the documentation. I've
> looked but it's rather muddy.
>
> My environment: Workgroup Server 7.3.2 on NT 3.51.

Drop your rollbacksegments, create new ones and then resize datafile

Hope it helps Norbert

--
=====================================
Norbert Ferstl
DI Forsthuber GmbH
Kohlbauernstr. 17
A-2630 Ternitz, AUSTRIA
Tel: +43 2630 382500
Fax: +43 2630 3825014
<fer...@netway.at>
=====================================

[Vsevolod Afanasjev]

Hi, John,
The message "File contains ... blocks beyond requested resize
value" means at the moment this command is issued,
the sum of sizes of all rollback segments exceeds the
requested resize value. This can happen even in database
which does not have any active transactions: simply because
RBS have some size. Yes, if you specified OPTIMAL during
rollback segment creation, Oracle will try to resize them automatically.
However, the default is no resize and even after resize
the rollback segment has some nonzero size (at least two extents).
So, what you need to do:
1. Check the STORAGE for your rollback segments, i.e. INITIAL, NEXT,
MINEXTENTS, OPTIMAL.

2. Resize them manually.

3. Take unnecessary rollback segments offline (by ALTER ROLLBACK
SEGMENT segment_name OFFLINE).

4. Drop the segments taken offline at step 3.

5. Check the sum of sizes of the remaining rbs by quering
DBA_SEGMENTS

6. Resize the datafile.

Seva

P.S. E-mail me if you have any questions.