ORA-1536 on 8.1.7.2 SE

[sambavan]

Database : 8.1.7.2 SE
OS : Solaris 8

Problem: ORA-1536 on create table

Description: I had created a user with the tablespaces where a quota limit of 100M assigned for him. And a role which has all the privileges under connect and resource except the unlimited tablespace system privilege. Now when I try to creat the table

  create table test (name VARCHAR2(10));

I'm getting the ORA-1536. When I get to assign an unlimited limit on the tablespace it all works, but I don't want to give the user the unlimited tablespace.

Any ideas or thoughts on why it happens?

Sam

[jmodic]

How much space have other segments of this user's allready occupied in that tablespace?

What is the default initial extent size for that tablespace?

[sambavan]

Originally posted by jmodic
How much space have other segments of this user's allready occupied in that tablespace?

None, this is a new user and no table/indices created yet.

Originally posted by jmodic

What is the default initial extent size for that tablespace?

Initial extent is 256M and Size 512M

-Sam

[marist89]

INITIAL = 256M and QUOTA =100M? Did I read that right?

[pravin]

Jeff - Really Good observation and good catch

[sambavan]

I'm missing something here Jurij.

The tablespace initial extent is of 256M but the user had been assigned a quota limit of 100M on that tablespace where

quota < initial extent of the tablespace.

So theoritically, doesn't the quota fit into the initial extent size of the tablespace. So why should this be an issue. Setting the quota to 256M seems to work.

Any pointers would greatly be appreciated

Sam

[marist89]

Originally posted by sambavan

So theoritically, doesn't the quota fit into the initial extent size of the tablespace.

You are asking to create a 256M extent (default from TS) but you only have 100M quota assigned.

[ocpwannabe]

u have a 5 gallin bucket full of water and u are trying to make it fit into a 1 gallin bucket. don't work that way!

[sambavan]

Oh my!! OH...h my!!!

My undestanding was in reverse!!! I was under the impression when you set the initial extent size of 256M and min extent 1 The tablespace gets created with 256M of physical space fixed out. Then when you ceate a user with a quota limit of 100M, which according to the docs "max space on the tablespace", it is suppose to work.

But now its time for me to hit the docs during the holidays to refresh things. It looks like its the reverse...

Still needs some reference material that would talk about this in detail.

Jeff and ocpwannabe, I do get your points but want to get more detail on the actual functionalities in detail, to nail it into my undestanding....

Sam