查看所有用户:
SQL>select * from dba_users;
SQL>select * from all_users;
SQL>select * from user_users;
创建新用户
create user 用户名 identified by 密码;
SQL> create user Tom identified by T123456; ##创建一个用户Lanny 密码是 T123456
SQL> grant create session to TOM
修改用户密码
SQL>alter user tom identified by tom123;
修改用户默认表空间
SQL> alter user tom default tablespace users;
修改用户临时表空间
SQL> alter user tom temporary tablespace temp_data;
强制用户修改口令
SQL> alter user tom password expire; #不修改口令则会提示密码过期
所有用户永不过期
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
SQL> alter profile default limit password_life_time unlimited;
将用户加锁、解锁
SQL> alter user tom account lock; // 加锁
SQL> alter user tom account unlock; // 解锁表示
删除用户
drop user 用户名 [CASCADE]; ## CASCADE删除带有数据的用户
查询用户会话信息
SQL> select username, sid, serial#, machine from v$session;
删除用户会话信息
SQL> alter system kill session 'sid, serial#';
查询用户SQL语句
SQL> select user_name, sql_text from v$open_cursor;
查看当前用户的缺省表空间
SQL> select username,default_tablespace from user_users;
查看用户或角色系统权限
SQL>select * from dba_sys_privs;
SQL>select * from user_sys_privs;
SQL>select * from all_sys_privs;
查看角色(登陆用户)所包含的权限
sql>select * from role_sys_privs;
查看用户对象权限
SQL>select * from dba_tab_privs;
SQL>select * from all_tab_privs;
SQL>select * from user_tab_privs;
查看哪些用户有sysdba或sysoper系统权限(查询时需要相应权限)
SQL>select * from V$PWFILE_USERS