Oracle Admin User (ii) Oracle Modify and delete user

1, modify the user

(1) Modify password

09:57:30 sql> alter user tiany identified by Tianya;

User altered.

(2) Modify table space quotas

Table space quotas are used to limit the maximum space that a user object can occupy on a table space.

09:59:43 sql> Alter user tiany quota 5m on users;

User Altered

(3) Lock user account

10:00:59 sql> alter user Tiany account lock;

User altered.

10:01:01 sql> Select Username,account_status from Dba_users

10:01:35 2 where username= ' Tiany ';

USERNAME Account_status

------------------------------ --------------------------------

Tiany LOCKED

10:02:19 sql> CONN Tiany/tianya

ERROR:

Ora-28000:the account is locked

Warning:you are no longer connected to ORACLE.

(4) Unlock account

10:03:27 sql> alter user tiany account unlock;

User altered.

10:03:29 sql> Select Username,account_status from Dba_users

10:03:34 2 where username= ' Tiany ';

USERNAME Account_status

------------------------------ --------------------------------

Tiany OPEN

(5) Set user Default role

10:06:36 sql> ALTER user tiany default role r1;

2, delete the user

Note: Users in a session cannot be deleted.

Sql> Select username,sid,serial# from V$session

2 where username is not null;

USERNAME SID serial#

---------- ---------- ----------

SYS 31 84

ROSE 39 31

sql> Drop user rose;--users in a session cannot be deleted

Drop User Rose

*

ERROR at line 1:

Ora-01940:cannot drop a user is currently connected

--Force Close user session

Sql> alter system kill session ' 39,31 ';--' sid,serial# '

System altered.

Sql> select * from EMP1;

SELECT * FROM EMP1

*

ERROR at line 1:

Ora-00028:your session has been killed

sql> drop user rose cascade; --Delete all users ' objects together

User dropped.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/