Oracle User Creation and Management

1) As a competent DBA, before creating a new user to check the following items:

* Determine the object must be stored in the user table space;
* Determine the quota for each table space;
* Give the user the default table space and temporary table space;
* Create a user;
* The user system privileges and roles required to give users;

2) The following example creates a user:

    create user infotech

--to create a user name for the infotech;
    identified by infotech

-- the user's password is infotech;
    default tablespace APEX_SPACE

-- the user's default table space is APEX_SPACE;
    temporary tablespace temp

--the user temporary table space is pioneer_temp;
    quota 30m on APEX_SPACE

--the user to use up to 30M in APEX_SPACE space;
    quota 30m on users

--the user in the users table space to use up to 30M of space;
    password expire

--the first time the user logs in, password invalid, the system prompted to enter a new password;
    account unlock

--User is not locked (the default is not locked)
    profile default;

3) is used to view and confirm the user quota table space:
Command as follows:

select username, tablespace_name,
bytes/1024/1024 MB,max_bytes/1024/1024
from dba_ts_quotas 
where username='INFOTECH'; 

4) database schema model (schema) is an order of the name of a collection of objects such as tables, views, and serial number; When a user is created, a corresponding pattern is created.

Model includes the elements:
* Table
* Views
* Index
* Constraints
* Sequences
* Synonyms
* Trigger
* Stored procedures, functions, and packages
* User-defined data types and so on.

5) How to change the user quota on tablespace

For example, if you want the INFOTECH user is no longer in the users tablespace new space can be as follows:

ALTER USER INFOTECH ALTER USER INFOTECH
QUOTA 0 ON USERS; QUOTA 0 ON USERS;
-- Before the object is created in the USERS can continue to use, but no longer have the disk in the above space.

6) Changing the default table space command

alter user INFOTECH defatult tablesapce system; 

7) Delete User

drop user INFOTECH cascade; 

Note: If the user mode, including the object, to use the cascade model clause to remove all the objects. Also cannot delete the currently connected users.
When a user is deleted, all the objects the user has gone, not easy to delete all users, or to make a backup before.