Unable to extend by 7 in tablespace #3

[markrem]

Crystal reports runs fine if we connect to the Oracle 8i database as user SYSTEM. If we change the connection to any other user (such as TEST) we get the subject error. Tablespace #3 is TEMP, and it has 10 GB free space, so I don't think that's the problem. Users SYSTEM and TEST both have default tablespace TOOLS and temporary tablespace TEMP.

The only differences between these are:
1. SYSTEM has the DBA role, but TEST has CONNECT role plus SELECT on all data tables.
2. SYSTEM has unlimited tablespace quota on TOOLS, but TEST has 0 quota.

Other thing to consider, TEMP is a locally managed tablespace with a single 200 MB datafile set to autoextend unlimited on a disk with 10 GB free space. There is currently 100 MB free space in the TEMP tablespace.

-Mark

 

[Naith]

This is an Oracle rather than a Crystal error.

Basically, the query your report is attempting to run is attempting to extend segments beyond the 100MB limit that you have left.

You will have to perform an alter tablespace on TOOLS to either resize the TOOLS space allocation, or add a new datafile to the tablespace.

I have made the assumption that you're getting this error for in the TOOLS tablespace (you haven't pasted the full error, as the offending tablespace should be revealed in the message), as you do not suggest that either user has different rights to the TEMP tablespace. However, I would suggest that you check your group rights as well as your user rights when looking for differences, as I imagine both your SYSTEM and TEST user would belong in completely seperate groups.

Naith

 

[Naith]

I just noticed that you said Tablespace #3 is your TEMP tablespace. Each user must be inheriting different rights for this tablespace from their groups or at user level. However, adding a datafile or resizing the tablespace will still stop you encountering the error.

Naith

 

[markrem]

I'll keep looking into the Oracle end of this. I've been troubleshooting it from an Oracle perspective and can't find anything wrong. Posted here to see if any Crystal users have seen this before.

All non-Crystal users of my system can perform large scale queries using the same user account that Crystal is using. Therefore, it appears crystal is doing something funny in the background. I.e., does Crystal preface its queries with session altering commands or embed optimizer hints?

No users need to be assigned space quotas on TEMP. all sort segments are owned by SYS regardless of the user issuing the query.

-Mark
Oracle9i Certified DBA

 

[Naith]

Mark,

This is absolutely, definitely not being caused by Crystal. Unless Crystal is using a datasource which includes optimiser hints, Crystal will in no way attempt to alter the session or optimise at runtime.

What I would suggest is going into Crystal's Database/Show SQL Query, copying this into the Oracle client, and running it as the Crystal user. You should get exactly the same error you get when you run the rpt file itself.

Naith
OCP 9i