方案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