管理用户（二）--修改及删除用户

 

1、修改用户

（1）修改口令

09:57:30 SQL> alter user tiany identified by tianya;

User altered.

（2）修改表空间配额

表空间配额用于限制用户对象在表空间上可占用的最大空间。

09:59:43 SQL> alter user tiany quota 5m on users;

User altered

（3）锁定用户账户

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）解锁账户

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）设置用户默认角色

10:06:36 SQL> alter user tiany default role r1;

2、删除用户

注意：会话中的用户不能被删除。

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;——会话中的用户不能被删除

drop user rose

*

ERROR at line 1:

ORA-01940: cannot drop a user that is currently connected

——强制关闭用户会话

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; ——将用户所有的对象都一起删除

User dropped.