1、查看当前用户及所有用户
SQL> show user; #查看当前用户
USER is "YZY"
SQL> select username from all_users; #查看所有用户
USERNAME
------------------------------
SYS
SYSTEM
BSWAY
SQL> select username from dba_users;
USERNAME
------------------------------
KANGBO
YZYSZ
SYSMAN
DBSNMP
2、切换到另一个用户
SQL> conn yzysz/123456 # 用户名/密码
Connected.
SQL> show user;
USER is "YZYSZ"
3、查看当前用户的角色
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
YZY CONNECT NO YES NO
YZY DBA NO YES NO
SQL> select * from session_privs;
PRIVILEGE
----------------------------------------
ALTER SYSTEM
AUDIT SYSTEM
CREATE SESSION
................................ #省略
4、查看当前用户的系统权限和表级权限
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
--------------------------- ---------------------------------------- --------
YZY UNLIMITED TABLESPACE NO
SQL> select * from user_tab_privs;
5、查看当前用户的所有表
如果你没有在system表空间上建表,可以用
SQL> select * from tabs where tablespace_name!='SYSTEM'
SQL> select * from user_tables
就可以了,除非你把库建在系统表空间
SQL> select owner,table_name from all_tables where tablespace_name <>'SYSTEM'
前提是你没在system表空间中建表
SQL> select * from dba_tables where owner='YZYSZ' #YZYSZ必须大写
6、有一个表,假设是这样的
CREATE TABLE Test (
field1 number(7) primary key,
field2 varchar2(30)
);
假设field1上 有索引。
要删除表中所有field1重复的记录,可以用下面的SQL语句:
---------------------------------------------------------------
delete from Test a where a.rowid!=(select max(rowid) from Test b where a.field1=b.field1);