Home » RDBMS Server » Performance Tuning » Assigning Quota on morethan one tablespace (Oracle Database, 10.2.0.5.0,Windows Server 2003)
Assigning Quota on morethan one tablespace [message #606653] Tue, 28 January 2014 00:38 Go to next message
Guna4324
Messages: 11
Registered: January 2014
Junior Member

Hi all,

Is it possible to allocate quota in morethan one tablespace for a single schema????

Thanks..
Re: Assigning Quota on morethan one tablespace [message #606654 is a reply to message #606653] Tue, 28 January 2014 00:51 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Documentation says, under CREATE USER statement:
Oracle

Use the QUOTA clause to specify the maximum amount of space the user can allocate in the tablespace.

A CREATE USER statement can have multiple QUOTA clauses for multiple tablespaces.
Re: Assigning Quota on morethan one tablespace [message #606655 is a reply to message #606654] Tue, 28 January 2014 01:01 Go to previous messageGo to next message
Guna4324
Messages: 11
Registered: January 2014
Junior Member
In this scenario, if i create one user,,

create user gudef identified by ***** default tablespace tbs_gudef;

grant connect,resource to gudef;

grant quota unlimited on tbs_xyz;

grant quota unlimited on tbs_rmz;

in this above case,if i create one table, where it will gets created.. kindly clarify???

thanks in advance...


Regards

Guna4324





Re: Assigning Quota on morethan one tablespace [message #606656 is a reply to message #606655] Tue, 28 January 2014 01:02 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Feel free to create such a user, let it create a table, and then check where (in which tablespace) it was created.
Re: Assigning Quota on morethan one tablespace [message #606661 is a reply to message #606655] Tue, 28 January 2014 02:41 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
The test scenario you describe will give you the answer to the question, but includes one misapprehension: when you grant RESOURCE there is an implicit grant of UNLIMITED TABLESPACE. So you are in fact giving your user the power to create segments of unlimited size in every tablespace. This behaviour is corrected only in release 12.x.
Previous Topic: Sudden decrease in performance of query
Next Topic: I/O Tuning
Goto Forum:
  


Current Time: Thu Mar 28 04:36:39 CDT 2024