ORACLE中查询指定用户拥有的所有表

方案1：

根据指定用户名获得对应用户所拥有权限的表

SQL> select table_name, owner from all_tables where owner = 'SYS' and rownum < 6;

TABLE_NAME        OWNER
------------------------------ ------------------------------
AUDIT_ACTIONS   SYS
AW$AWCREATE    SYS
AW$AWCREATE10G SYS
AW$AWMD           SYS
AW$AWREPORT    SYS

方案2：

通过tab视图获得当前登录用户所有表和视图，通过tabletype过滤获得所有表

 

SQL> select * from tab where tabtype = 'TABLE';
 
TNAME                   TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                      TABLE 
EMP                        TABLE 
BONUS                   TABLE 
SALGRADE             TABLE 

 

 tab，dual概念：
Tab is a view, as seen:

create or replace view sys.tab as
select o.name,
decode(o.type#, 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM'), t.tab#
from sys.tab$ t, sys.obj$ o
where o.owner# = userenv('SCHEMAID')
and o.type# >=2
and o.type# <=5
and o.linkname is null
and o.obj# = t.obj# ( )

Dual is a table with a unique column.
This column is a dummy varchar, that returns what you type.
That´s why if you type select * from dual, the result will be 'X'.

 

方案3:

根据user_tables表获得当前用户拥有所有表

SQL> select table_name from user_tables;
 
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE

方案4:

根据sys表空间下all_object表获得指定用户指定类型对象（表）

SQL> select object_name from sys.all_objects where owner = 'SCOTT' and OBJECT_TYPE = 'TABLE';
 
OBJECT_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE