Solved

resize datafiles

Posted on 2000-04-14
10
4,356 Views
Last Modified: 2008-03-17
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
Comment
Question by:rsolomon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 2717738
Dumb question: Are you sure there is additional free space on that disk device?
0
 

Author Comment

by:rsolomon
ID: 2717758
Yes. Also, I can add a datafile of 500M,
but I couldn't resize a datafile to 500M.
0
 
LVL 2

Expert Comment

by:banicki
ID: 2717898
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 5

Expert Comment

by:sbenyo
ID: 2718119
Are you using RAW device or file system ?
0
 
LVL 6

Expert Comment

by:mshaikh
ID: 2718439
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
 
LVL 1

Expert Comment

by:mjkrijgsman
ID: 2724150
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
 
LVL 1

Expert Comment

by:mjkrijgsman
ID: 2725463
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
 

Accepted Solution

by:
iqra earned 100 total points
ID: 2736459
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 2743950
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
 

Author Comment

by:rsolomon
ID: 2743985
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question