CODEWITHSUNDEEP
oracle-databaseoracle12c
SaP
SaP

Reputation: 71

How to grant quota on a temporary tablespace to a user

I'm trying to create a user in Oracle 12c with a default temporary tablespace and with a quota for that tablespace.

My statement is:

CREATE USER test_user2
IDENTIFIED BY "pass1234"
DEFAULT TABLESPACE TEMP_WORK_TS
QUOTA 100M ON TEMP_WORK_TS
QUOTA 100M ON TEMP_TEST_01
TEMPORARY TABLESPACE TEMP_TEST_01
PROFILE DEFAULT_PROFILE
ACCOUNT UNLOCK;

But I get error:

ORA-30041: Cannot grant quota on the tablespace

The Oracle documentation shows a quota being assigned for a user's temporary tablespace.

Why does my statement doing the same thing get that error?

Upvotes: 1

Views: 13582

Answers (1)

Alex Poole
Alex Poole

Reputation: 191245

That looks like a documentation bug. That example goes back at least to Oracle 9i documentation, so it may predate true temporary tablespaces.

Also from the 12c documentation:

You can assign each user a tablespace quota for any tablespace (except a temporary tablespace).

And the text for ORA-30041 is:

ORA-30041: Cannot grant quota on the tablespace
Cause: User tried to grant quota on an undo or temporary tablespace
Action: Check the tablespace name and reissue the command

It's said that since 10g, but the 9i version only referred to undo tablespaces. The behaviour seems to have changed, and the example just has hasn't been updated to reflect that.

So you can't grant that temporary tablespace quota. Just omit it from the create user statement. A quota only really makes sense for permanent extents anyway, and you don't know how much temporary space the optimiser might need to allocate for queries the user might run.

Upvotes: 4

Related Questions