Monday, June 28, 2010

What is sytem privilege "export/import full database"

SYS@ODST> select * from dba_sys_privs where grantee='LIQY'; 

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
LIQY                           CREATE VIEW                              NO
LIQY                           CREATE TABLE                             NO
LIQY                           ALTER SESSION                            NO
LIQY                           CREATE SESSION                           NO

SYS@ODST> grant export full database to liqy;

Grant succeeded.


odsdev01:ODST:/software/oraods/temp> exp liqy/liqyliqy@ODST file=icc.dmp log=icc.log  tables=DBAM1.M1_icc_call

Export: Release 10.2.0.2.0 - Production on Fri Jun 25 10:45:50 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
EXP-00009: no privilege to export DBAM's table M_ICC_CALL
Export terminated successfully with warnings.
odsdev01:ODST:/software/oraods/temp> oerr exp 9
00009, 00000, "no privilege to export %s's table %s"
// *Cause:  An attempt was made to export another user's table. Only a
//          database administrator can export another user's tables.
// *Action: Ask your database administrator to do the export.


SYS@ODST> select * from dba_sys_privs where grantee='LIQY';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
LIQY                           CREATE VIEW                              NO
LIQY                           CREATE TABLE                             NO
LIQY                           ALTER SESSION                            NO
LIQY                           CREATE SESSION                           NO
LIQY                           EXPORT FULL DATABASE                     NO

SYS@ODST> select * from dba_role_privs where grantee='LIQY';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
LIQY                           T_ROLE                         NO  YES
LIQY                           EXP_FULL_DATABASE              NO  YES

odsdev01:ODST:/software/oraods/temp> exp liqy/liqyliqy@ODST file=icc.dmp log=icc.log  tables=DBAM1.M1_icc_call

Export: Release 10.2.0.2.0 - Production on Fri Jun 25 10:51:41 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
Current user changed to DBAM. . exporting table                    M_ICC_CALL



SYS@ODST> select * from dba_sys_privs where grantee='EXP_FULL_DATABASE';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
EXP_FULL_DATABASE              RESUMABLE                                NO
EXP_FULL_DATABASE              BACKUP ANY TABLE                         NO
EXP_FULL_DATABASE              EXECUTE ANY TYPE                         NO
EXP_FULL_DATABASE              SELECT ANY TABLE                         NO
EXP_FULL_DATABASE              READ ANY FILE GROUP                      NO
EXP_FULL_DATABASE              SELECT ANY SEQUENCE                      NO
EXP_FULL_DATABASE              EXECUTE ANY PROCEDURE                    NO
EXP_FULL_DATABASE              ADMINISTER RESOURCE MANAGER              NO

8 rows selected.

Finally, get the answer from metalink:

The system privileges EXPORT/IMPORT FULL DATABASE, introduced with 10gR1, are currently not used. These will be implemented in future releases with new functionality but in 10/11g these are not operational.

So confusing ...