Table space drop not seeing new size

[wayne_104]

Hi all

The dba has dropped his table spaces with delete files. The files are no longer there and when i do a du i see the smaller size but bdf is showing the old size.

I am on a rx 6600 with enterprise eva.
hp ux 11.31 september 2010 relese

any suggestions

[Shibin_2]

What was the table space size? How much you expecting ? Old size means your free space or the size of file system ?

[wayne_104]

Will get the size. Yes old size is the size before dropping the table space.

It seems to have come right now there seems to be a delay. not sure why.

[wayne_104]

home/oracle bdf
/dev/vg03/lvol1 835584000 820548225 14096160 98% /oradata1
/home/oracle bdf
/dev/vg03/lvol1 835584000 718836265 109451123 87% /oradata1

I dropped datafile around 8:30 and only saw new space round 9;30
F
rom oracle I ran the following cmd:

drop tablespace users including contents and datafiles;

[klb]

I've seen similar behaviour with Oracle/Unix.

This is a good reason to do your UNIX system management outside of Oracle. Oracle likes to think they can do stuff better than the other vendors ( ASM, EXADATA, others ), but they are often wrong. If you've bought into ASM and that product actually uses files on a filesystem, you'll have to ask Oracle whats up. If this is not ASM, remove you own files.

Or, after you do the drop tablespace ..., wait just a few minutes, then go to the command line on UNIX and issue:

sync;sync;sync;sync

... and see if that does anything for you.

BTW, if Oracle removes but does not unlink a datafile, DU will NOT see it and therefore not report it, however bdf still knows the space is not available as free yet.

If you want to experiment with this in a test database, do the drop tablespace, then after it completes, do a shutdown abort on your Oracle Instance. Chances are, this will remove the delay!!!

-klb

[Shibin_2]

Agree with klb. I think, it might not be unlinked properly.

[Volker Borowski]

Hi,
What may help is forcing a checkpoint or a logswitch in oracle, because this forces the oracle processes to initiate their pending writes (yes, you may think of this as a "sync" inside oracle :-)

Here is an old thread about this topic.
http://h30499.www3.hp.com/t5/System-Administration/Unnamed-inode-when-droping-tablespace/m-p/3746935#M257063

Best regards
Volker

[klb]

Agree there.

alter system checkpoint;

would be less destructive than:

shutdown abort;

...and would actually do the same thing in this context.

-klb

[Volker Borowski]

Actually it would do a whole lot less in other context compared to a "shutdown abort" :-)
Volker