alter database datafile file_number in 8i does not work

We’ve got a mixture of 9i, 8i and 7.3 databases, but most of them are 9i

I’ve recently got in the habit using the file_id rather than the full filename when I do a resize. This is fairly handy in that avoids either a lot of typing or using the mouse to cut and paste. And it makes me feel smug and clever….

So you can do:

alter database datafile 1 resize 500M;

Instead of:

alter database datafile ‘H:ORACLEORADATADB1SYSTEM01.DBF’ resize 500M;

What I’d either hadn’t realized or forgotten was that the option to use file_id rather than filename is a 9i feature.

So what happens when you try to use it in 8i?

Not much:

SQL> @sfiles
Enter value for tablespace: SYSTEM
SYSTEM 1 H:ORACLEORADATADB1SYSTEM01.DBF 500

alter database datafile 1 resize 600M;

Database altered.

@sfiles
Enter value for tablespace: SYSTEM
SYSTEM 1 H:ORACLEORADATADB1SYSTEM01.DBF 500

I’d resized the same file a couple of mornings running before I realised nothing was happening. Comparing the 9i doc with the 8i doc it’s obvious that it’s a 9i feature.

It’s a bit of a shame it silently fails – I’m sure I’ve seen this before with features that haven’t been built yet – but this is more a problem with the wetware than anything else. Again.

Advertisements