Oct 10, 2012 Re: Alter User Tablespace andrewmy Oct 10, 2012 6: 27 AM ( in response to scottjhn ) (1)& (2): A user can create objects in any tablespace he has quotas on, not just the default tablespace.Managing Users and Resources. You can set a user's default tablespace during user creation, and change it later with the ALTER USER statement. Changing the user's default tablespace affects only objects created after the setting is changed. To permit a user to use an unlimited amount of any tablespace in the database, grant the user the alter user unlimited tablespace oracle
UNLIMITED TABLESPACE. Use an unlimited amount of any tablespace. This privilege overrides any specific quotas assigned. If you revoke this privilege from a user, the user's schema objects remain but further tablespace allocation is denied unless authorized by specific tablespace quotas.
Alter user in Oracle SQL; Alter user quota. alter user quota unlimited on; alter user quota 1M on; Sets the quota for a user on a tablespace. Alter user identified by NEWPASSWORD. Sets a new password for a user. Alternatively, the SQLPlus command password can be used. 2 user ALTER USER command and sets the QUOTA to zero. this will revoke TESTER user access to that tablespace. the existing TESTER user's objects in the revoked tablespace still remain, but the objects cannot be allocated any new space. if you need the objects, you will need to remove those object into the new tablespace: alter user unlimited tablespace oracle You plan to revoke UNLIMITED TABLESPACE from this user and instead grant him UNLIMITED quota on USERS tablespace, so you plan to grant required quota first and then revoke UNLIMITED TABLESPACE as follows: SQL alter user KAMRAN quota unlimited on USERS; User altered.
As I said, user i owns the table, therefore user i needs to be granted a quota on the USERS tablespace. i IS still a user that exists in the database. In Oracle SchemaUser (at least until 12c came along). alter user unlimited tablespace oracle Dec 14, 2001 grant quota unlimited on tablespacename to username; or what is the syntax that I can use to grant unlimited quota to specific tablespaces to specifc user? Reply With Quote Restriction on Default Tablespaces You cannot specify a locally managed temporary tablespace, including an undo tablespace, or a dictionarymanaged temporary tablespace, as a user's default tablespace. TEMPORARY TABLESPACE Clause. Use this clause to assign or reassign a tablespace or tablespace group for the user's temporary segments. ALTER USER QUOTA 100M ON GRANT UNLIMITED TABLESPACE TO And to also make sure the user has been granted Connect, Resources roles incase the user was not given Create table privileges. alter user john quota unlimited on users; When you creates the User and Grants him the Connect option by default the User gets allocated with the Unlimited Tablespace only you need to do is eigther Revoke that Option. or as bobanjayan said u just alter the user and add the Quota to that user.Rating: 4.97 / Views: 684