Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4376
  • Last Modified:

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.
0
rsolomon
Asked:
rsolomon
  • 2
  • 2
  • 2
  • +4
1 Solution
 
Mark GeerlingsDatabase AdministratorCommented:
Dumb question: Are you sure there is additional free space on that disk device?
0
 
rsolomonAuthor Commented:
Yes. Also, I can add a datafile of 500M,
but I couldn't resize a datafile to 500M.
0
 
banickiCommented:
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...
Terry
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

ANALYZE TABLE owner.table_name VALIDATE STRUCTURE CASCADE;

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.
0
 
mjkrijgsmanCommented:
rsolomon,

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
0
 
mjkrijgsmanCommented:
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
0
 
iqraCommented:
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:

ALTER DATABASE [database]
DATAFILE 'filename'
AUTOEXTEND


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
0
 
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.
0
 
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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now