查询用户所有的表

相关视图

user_tables：当前用户创建的表

all_tables：当前用户拥有权限的表

dba_tables：数据库所有的表，拥有DBA权限或者SYS用户授权才能访问

示例

1，使用sys用户连接数据库，创建两个用户，分别授予DBA和普通权限

[oracle@rac1 ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 8 14:00:10 2016


Copyright (c) 1982, 2009, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


SQL> create user admin_user identified by oracle;


User created.


SQL> create user test_user identified by oracle;


User created.


SQL> grant dba to admin_user;


Grant succeeded.


SQL> grant resource,connect to test_user;


Grant succeeded.

2，两个用户分别创建测试表

SQL> conn admin_user/oracle
Connected.
SQL> create table admin_czrk(xm varchar2(20),sfzh varchar2(18));

Table created.

SQL> create table admin_xyr(xm varchar2(20),sfzh varchar2(18));

Table created.

SQL> conn test_user/oracle
Connected.
SQL> create table test_czrk(xm varchar2(20),sfzh varchar2(18));

Table created.

SQL> create table test_xyr(xm varchar2(20),sfzh varchar2(18));

Table created.

3，在两个用户下分别查询user_tables，all_tables，dba_tables

SQL> conn admin_user/oracle
Connected.
SQL> select table_name from user_tables; 


TABLE_NAME
------------------------------
ADMIN_CZRK
ADMIN_XYR


SQL> select count(*) from all_tables;


  COUNT(*)
----------
      2733


SQL> select count(*) from dba_tables;


  COUNT(*)
----------
      2733

admin_user拥有dba角色，对dba_tables拥有查询权限，并且对数据库所有用户的表都拥有查询权限

SQL> conn test_user/oracle
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
TEST_CZRK
TEST_XYR

SQL> select count(*) from all_tables;

  COUNT(*)
----------
       101

SQL> select count(*) from dba_tables;
select count(*) from dba_tables
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

test_user是普通用户角色，无法查询dba_tables，只对数据库部分表拥有查询权限

其他

user_tab_privs：用户表授权信息

查看表授权情况

SQL> conn admin_user/oracle
Connected.
SQL> grant select on admin_czrk to test_user;

Grant succeeded.

SQL> select * from user_tab_privs;

GRANTEE    OWNER      TABLE_NAME GRANTOR    PRIVILEGE  GRA HIE
---------- ---------- ---------- ---------- ---------- --- ---
TEST_USER  ADMIN_USER ADMIN_CZRK ADMIN_USER SELECT     NO  NO

查看用户所有的其他对象，比如视图，存储过程，函数，索引等跟上面类似

比如视图：user_views，all_views，dba_views

索引：user_indexes，all_indexes，dba_indexes

用户对象汇总信息在对象表里面，比如user_objects，all_objects，dba_objects

数据字典前缀

user前缀：当前用户级别

all前缀：当前用户和其他用户级别

dba前缀：数据库级别