Altering Users

A user, once created, can subsequently be altered. Any of the default settings provided by the CREATE USER command can be altered with the ALTER USER command.

It will often become necessary to modify a user's tablespace quota when tables owned by that user have seen unanticipated growth, when an application is enhanced and therefore requires additional tables or indexes that weren't taken into account originally, or when objects are reorganized and placed into different tablespaces.

The following command alters the awells user, removes all quota on userdata tablespace, gives unlimited space on awellsdata tablespace, and sets the default tablespace and the user's temporary tablespace:

 ALTER USER awells QUOTA 0 ON userdata QUOTA UNLIMITED ON awellsdata TEMPORARY TABLESPACE new_temp DEFAULT TABLESPACE new_default; 

When a schema owner's quota is set back to 0, the objects owned by that user still exist in the given tablespace, but no new space can be allocated to the objects. If the object's size changes due to deleting data or truncating tables, again no new space can be allocated. After the currently allocated extents have been filled, no new inserts can be put into those tables and/or indexes.

And finally, you can not only create and alter users but also, when it becomes necessary, you can drop the user.