能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嗎這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！