Link to home
Create AccountLog in
Avatar of hussainkhan22
hussainkhan22Flag for United States of America

asked on


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.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

You are also missing a leading '/':


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

>>cannot be used together in a single statement!

You could be right.  Never tried it.
Avatar of hussainkhan22


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.
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,
Don't use RESIZE and MAXSIZE together in one statement!
MAXSIZE is meaningful only if AUTOEXTEND is ON.

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

Its autoextend on already
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!
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.
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.

The suggestion you accepted does NOT work.