能select的表就可以exp吗

这篇文章主要讲解了“能select的表就可以exp吗”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“能select的表就可以exp吗”吧！

1.建立导出测试用户，并授予相关权限

[oracle@GXDB1] $sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Fri May 3 15:11:19 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected.

SQL> create user test_exp identified by a;

User created.

SQL> grant connect to test_exp;

Grant succeeded.

SQL> grant select on dep_study.bas_region to test_exp;

Grant succeeded.

2.导出dep_study.bas_region表

[oracle@GXDB1] $exp test_exp/a@gxtest file=a.dmp tables=dep_study.bas_region

Export: Release 10.2.0.4.0 - Production on Fri May 3 15:26:15 2013

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and UTF8 NCHAR character set

About to export specified tables via Conventional Path ...
EXP-00009: no privilege to export DEP_STUDY's table BAS_REGION
Export terminated successfully with warnings.
[oracle@GXDB1] $

3.给test_exp授予exp_full_database权限
SQL> grant exp_full_database to test_exp;

Grant succeeded.

4.成功导出表

[oracle@GXDB1] $exp test_exp/a@gxtest file=a.dmp tables=dep_study.bas_region

Export: Release 10.2.0.4.0 - Production on Fri May 3 15:29:20 2013

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and UTF8 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to DEP_STUDY
. . exporting table                     BAS_REGION         17 rows exported
Export terminated successfully without warnings.

--ok 成功导出

5.收回select权限，并导出表

SQL>  revoke select on dep_study.bas_region from test_exp;

Revoke succeeded.

[oracle@GXDB1] $exp test_exp/a@gxtest file=a.dmp tables=dep_study.bas_region

Export: Release 10.2.0.4.0 - Production on Fri May 3 15:32:15 2013

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and UTF8 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to DEP_STUDY
. . exporting table                     BAS_REGION
EXP-00056: ORACLE error 1031 encountered
ORA-01031: insufficient privileges
Export terminated successfully with warnings.

结论：

    exp其他用户的表，用户需要拥有select表和exp_full_database(角色)两个权限。

感谢各位的阅读，以上就是“能select的表就可以exp吗”的内容了，经过本文的学习后，相信大家对能select的表就可以exp吗这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！