ORA-03297 when trying to shrink size of datafile

[slicendice]

Hi
I have a test database set up and have performed an import of some existing data. Due to a slight oversight(errmm...on my part!) the imported data went into the system tablespace instead of the intended tablespace. So I ended up with a 2GB SYSTEM tablespace (it had originally been about 500M)
I've managed to move all the necessary tables from SYSTEM to the correct tablespace now, but then when I try and shrink the SYSTEM tablespace back to 500M again using:

alter database datafile 'C:\oracle\oradata\db1\system01.dbf' resize 500M

I get the error:

ORA-03297: file contains used data beyond requested RESIZE value

I'm guessing this is because there is a used block right at the end of the file, or at least somewhere beyond the 500M mark....does that sound feasible? If so, is there any way of overcoming this so I can shrink the datafile back to it's original size?

Thanks very much

 

[Turkbear]

Hi,
What method did you use to move the tables?

 

[SantaMufasa]

Slice,

As I recall, the "alter database datafile...resize..." command affects only those blocks that are above the file's high-water mark (HWM). Specifically, if you have used the block for data, then Oracle cannot resize that block. Since you have used 2GB of space in your SYSTEM tablespace files, then you are unable to resize them downward.

Sorry,

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:38 (27Sep04) UTC (aka "GMT" and "Zulu"), 13:38 (27Sep04) Mountain Time)

 

[slicendice]

Thanks Mufasa
I had a nasty feeling that this might be the case! Ah well...it's only a test database so it's not the end of the world, just a bit of a nuisance. Plus, it will teach me to be more careful about importing in the future! ;-)

Thanks again

 

[SantaMufasa]

Slice,

If it's just a "test" database, then you may wish to pursue this to its resolution. To shrink the file:

1) Export all schemas (except SYS and SYSTEM).
2) Drop your old database (erasing all old datafiles).
2) Create a new database.
3) Import the dump files from step 1.

This will defrag all tables and tablespaces and leave the SYSTEM tablespace datafiles at their reduced size. It will recover 2GB of space for your use by some other application. This is a good exercise to confirm your skills if this is just practise anyway.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:23 (27Sep04) UTC (aka "GMT" and "Zulu"), 14:23 (27Sep04) Mountain Time)

 

[slicendice]

Sounds like a good plan...I'll give it a try

Thanks for your help