Solved

ora-00933

Posted on 2011-02-11
14
882 Views
Last Modified: 2012-08-13
Hey When I am trying to execute
SQL> alter database datafile 'appl/oracle/abc/test01.dbf' resize 6001M maxsize 6051M;

Getting the below error:
Ora-00933: Sql command not properly ended.

I really appreciate your help. Thanks in advance.
0
Comment
Question by:hussainkhan22
  • 6
  • 4
  • 3
14 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
try:
 alter database datafile 'appl/oracle/abc/test01.dbf' resize 6001M autoextend on maxsize 6051M;

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
You are also missing a leading '/':

'/appl/oracle/abc/test01.dbf'
0
 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
Hi,

afaik RESIZE and MAXSIZE cannot be used together in a single statement!

wmp
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>cannot be used together in a single statement!

You could be right.  Never tried it.
0
 

Author Comment

by:hussainkhan22
Comment Utility
It is "/appl" only I made a mistake in typing. So what do you guys suggest me to do to resolve this error. I really appreciate your help.
0
 

Author Comment

by:hussainkhan22
Comment Utility
I have one question can maxsize be greater than the resize filesize. As above customer asking to resize tablespace to 6001M and specifying maxsize as 6051M,
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
Don't use RESIZE and MAXSIZE together in one statement!
0
 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
MAXSIZE is meaningful only if AUTOEXTEND is ON.

Why would one try to resize a datafile which has AUTOEXTEND ON?

0
 

Author Comment

by:hussainkhan22
Comment Utility
Its autoextend on already
0
 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
So do you want to desrease the size? That's the only use of RESIZE for an AUTOEXTEND datafile which could make some sense, IMHO.

And, actually, if both modifications have to be performed, use two distinct statements!
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
It also doesn't seem to make sense to resize basically, a 6 Gig datafile and only let it autoextend another 50 Meg.

I would hope the NEXT size on a 6 Gig datafile would be more than 50M.  I would probably have it autoextend 1 Gig at a time since that is a pretty expensive operation.

I also confirmed that woolmilkporc said:  It does need to be two commands.
0
 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
I just tested it here:

If RESIZE is being used nothing else can be specified in ALTER DATABASE DATAFILE ...

Also, MAXSIZE cannot be specified without AUTOEXTEND ON preceeding it.

0
 
LVL 68

Expert Comment

by:woolmilkporc
Comment Utility
The suggestion you accepted does NOT work.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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.

772 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

11 Experts available now in Live!

Get 1:1 Help Now