resize datafiles

I'm currently running 7.3.4 of Oracle.
I'm trying to resize a datafile using the command "alter database datafile '/prodDB2/data1_04.dbf' resize 500M".

Every time I issue the command I get:
TQ_PROD> alter database datafile '/prodDB2/data1_04.dbf' resize 500M;
alter database datafile '/prodDB2/data1_04.dbf' resize 500M
ERROR at line 1:
ORA-01114: IO error writing block to file 11 (block # 1)
ORA-01110: data file 11: '/prodDB2/data1_04.dbf'
ORA-07376: sfwfb: write error, unable to write database block.
IBM AIX RISC System/6000 Error: 5: I/O error
Additional information: 1

Here is the strange thing about, I can shrink it to what every size, butI can't increase it.

Can anyone help?

Thanks in advance.
Who is Participating?
iqraConnect With a Mentor Commented:
dear R. Solomon
you r trying wrong command to extend datafile size. Actually 'Resize' is used to shrink the size of data file.
You can extend your data file using following command:

DATAFILE 'filename'

cap words are keywords

If you have OEM (Oracle Enterprise Manager) option then you can do it via Storage Manager of OEM.
Here r the steps:

.. Run Storage Manager
.. Expand the Tablespace node
.. Select the datafile whose size u want to increse
.. In the autoextend page of the property sheet, turn off the Enable Auto Extend button
.. Click Apply

Have a trouble free day
Mark GeerlingsDatabase AdministratorCommented:
Dumb question: Are you sure there is additional free space on that disk device?
rsolomonAuthor Commented:
Yes. Also, I can add a datafile of 500M,
but I couldn't resize a datafile to 500M.
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Sounds like the priveleges are screwed up.
When you install, you create a user and a dba group.
Check to see that they are still intact.
Then make sure that the oracle/dba group has the proper previleges
to allocate a dataset.
Actually, a quicker test would be to create a new tablespace with a small data file in that directory/file system.
If that works, Drop the test tablespace and try to create on with a 500M datafile.  
Doesn't AIX/UNIX have a quota space concept?  Maybe you are beyond your quota... I mean oracle user is beyond his/her quota...

Good luck...
Are you using RAW device or file system ?
1..  Try to run structure check on all the objects in the tablespace that the datafile belongs to:


run the above on all the tables in the tablespace.

2..  Also, run dbverify on the datafile. You will need to shut the database down before you run this on the datafile, Or atleast take the tablespace offline.

I know this problem. As soon as I saw your question, it occurred that the error messages looked very familiar.

Indeed it has something to do with AIX 'quota'. You should take a look at the ulimit of the user you started Oracle with, or to be more precise: the users that ownes the processes.

Now I don't know the exact ins and outs about it, just that you should take a look at your ulimit. But there are some AIX gurus where I work, so I could ask them to explain if you like.

Marcel-Jan Krijgsman
Origin Nederland
OK, I showed your error to an AIX guru here. The first thing he uttered was 'ulimit' :-) Here is the technical story:

In AIX writing to files is done by asynchronous I/O processes. These processes are kernel processes (kproc).

To see the status or owner of these processes, issue ps -fk.

The owner of these processes can be root, but it can happen root isn't the owner, but another user. That user is not able to write files larger than the ulimit and this ulimit in your case might be 512Mb. Another possibility is that your root user has a ulimit of 512 Mb.

After you change the ulimit of root, you'll have to reboot.

I hope this helps.

Marcel-Jan Krijgsman
Origin Nederland
Mark GeerlingsDatabase AdministratorCommented:
I disagree with igra.  "Resize" can be used with "alter database datafile" to make the file either larger or smaller.

Setting "autoextend" on just allows the datafile to grow as needed, when objects in it grow and there is not enough freespace left in the datafile.  It does not immediately set the size of the datafile to a particular value.
rsolomonAuthor Commented:
I only can shrink it. (Example, If I shrink it to 5M, I can't resize it to 10M or anything else.) I have another database where I can shrink and increase it using the resize command.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.