You can create an user named oracle with password bangladesh simply by,
SQL> CREATE USER ORACLE IDENTIFIED BY BANGLADESH;
User created.
You can create user with assigning more option like,
SQL> CREATE USER INDIA IDENTIFIED BY USA
DEFAULT TABLESPACE TEST_DF_DELETE
TEMPORARY TABLESPACE TEMP_T
QUOTA 200K ON TEST_DF_DELETE
PROFILE DEFAULT
PASSWORD EXPIRE
ACCOUNT UNLOCK;
User created.
Here IDENTIFIED BY indicates the password of the user.
DEFAULT TABLESPACE indicates where his created objects will be stored if he don't specify tablespace clause explicitly while creating objects.
TEMPORARY TABLESPACE indicates where his temporary data will be stored.
QUOTA indicates how much space he is allowed to use on the tablespace.
PROFILE indicates which profile he is assigned.
PASSWORD EXPIRE makes the user’s password expire when they first log in. When you use this option, you will give the user an initial password, but after the user logs in the user will have to change his password.
ACCOUNT UNLOCK makes he can log on to database. When the user’s account is locked, no one can use it until it is unlocked.
To know about user's quota on the tablespace issue,
SQL> SELECT tablespace_name,username,bytes,max_bytes from dba_ts_quotas ;
TABLESPACE_NAME USERNAME BYTES MAX_BYTES
------------------------------ -------------------- ---------- ----------
SYSAUX DMSYS 262144 209715200
SYSAUX OLAPSYS 16318464 -1
SYSAUX SYSMAN 56623104 -1
TEST_DF_DELETE INDIA 0 204800
-1 means no limit.
SQL> ALTER USER ORACLE QUOTA UNLIMITED ON USER_TBS;
User altered.
SQL> SELECT tablespace_name,username,bytes,max_bytes from dba_ts_quotas ;
TABLESPACE_NAME USERNAME BYTES MAX_BYTES
------------------------------ -------------------- ---------- ----------
SYSAUX DMSYS 262144 209715200
SYSAUX OLAPSYS 16318464 -1
SYSAUX SYSMAN 56623104 -1
TEST_DF_DELETE INDIA 0 204800
USER_TBS ORACLE 0 -1
In order to know users common information issue following command.
SQL> SET LINESIZE 200
SQL> COL USERNAME FORMAT A7
SQL> COL TEMPORARY_TABLESPACE FORMAT A10
SQL> COL DEFAULT_TABLESPACE FORMAT A10
SQL> COL ACCOUNT_STATUS FORMAT A10
SQL> COL PROFILE FORMAT A10
SQL> SELECT USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE,ACCOUNT_STATUS FROM DBA_USERS WHERE username in ('ORACLE','INDIA');
USERNAM DEFAULT_TA TEMPORARY_ PROFILE ACCOUNT_ST
------- ---------- ---------- ---------- ----------
ORACLE USER_TBS TEMP2 DEFAULT OPEN
INDIA TEST_DF_DE TEMP_T DEFAULT EXPIRED
LETE
To be able a user log on to database he must be granted CREATE SESSION privilege.
SQL> GRANT CREATE SESSION TO INDIA;
Grant succeeded.
SQL> CONN INDIA/USA
ERROR:
ORA-28001: the password has expired
Changing password for INDIA
New password:
Retype new password:
Password changed
Connected.
Since while creating user I added option "password expire" so it invoked me to give a new password.
No comments:
Post a Comment