Solved

resize datafiles

Posted on 2000-04-14
10
4,331 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
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 34

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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now