Solved

resize datafiles

Posted on 2000-04-14
10
4,334 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 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
 
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 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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 how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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…

896 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

16 Experts available now in Live!

Get 1:1 Help Now