How to Prevent EXP-79 or EXP-80 Warnings (Data In Table Is Protected) During Export (Doc ID 277606.1)

APPLIES TO:

Enterprise Manager for Oracle Database - Version 8.1.7.4 and later
Oracle Database - Enterprise Edition - Version 8.1.7.0 to 11.2.0.4 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 21-Oct-2013***

GOAL

The information in this article applies to:

Enterprise Manager for RDBMS - Version: 2.1 to 11g
Oracle Server - Enterprise Edition - Version: 8.1.7.0 and higher

Errors:

• EXP-79
• EXP-80

When exporting data from an Oracle8i, Oracle9i or Oracle10g database you see the following warning in the export log file (note that the table name is probably different):

About to export specified tables via Conventional Path ...
EXP-00079: Data in table "TEST_TAB" is protected. Conventional path may only be exporting partial table.
. . exporting table TEST_TAB 1 rows exported
Export terminated successfully with warnings.

- OR -

About to export specified tables via Direct Path ...
EXP-00080: Data in table "TEST_TAB" is protected. Using conventional mode.
EXP-00079: Data in table "TEST_TAB" is protected. Conventional path may only be exporting partial table.
. . exporting table TEST_TAB 1 rows exported
Export terminated successfully with warnings.

This document describes:

• How to ensure that all table data has been exported
• How to prevent these warnings during export.

SOLUTION

The warning is given because the table has a so called fine-grained access control policy (FGA) enabled. If fine-grained access control is enabled on a SELECT statement, then the Export may not export the entire table, because fine-grained access may rewrite the query.

Example (very simplified):

% sqlplus /nolog

SQL> CONNECT system/manager
SQL> GRANT execute_catalog_role, create procedure TO scott;

SQL> CONNECT scott/tiger
SQL> CREATE TABLE test_tab (first NUMBER);
SQL> INSERT INTO test_tab VALUES (1);
SQL> INSERT INTO test_tab VALUES (2);
SQL> COMMIT;
SQL> SELECT COUNT(*) FROM test_tab;

COUNT(*)
----------
            2

SQL> CREATE OR REPLACE FUNCTION test_func (schema_in VARCHAR2,
object_in VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN 'first = 1';
END;
/
SQL> EXECUTE DBMS_RLS.ADD_POLICY ('scott', 'test_tab', 'test_privs', -
'scott', 'test_func');

A select from table TEST_TAB will shows only one row:

SQL> SELECT COUNT(*) FROM test_tab;

COUNT(*)
----------
             1

Similar, a conventional path export (DIRECT=N or parameter DIRECT has not been specified), will export one row only. In addition, the following warning is given during export:

EXP-00079: Data in table "TEST_TAB" is protected. Conventional path may
only be exporting partial table.

How to ensure that all table data has been exported?

Only the user SYS (all versions) or any user who has the EXEMPT ACCESS POLICY applied (Oracle9i and higher), can select all rows. To verify that the table has an enabled FGA Control policy:

SQL> CONNECT system/manager
SQL> SELECT enable,sel,object_owner||'.'||object_name "OBJECT_OWNER.NAME"
FROM dba_policies WHERE object_name='TEST_TAB';

ENA SEL OBJECT_OWNER.NAME
--- --- -----------------------
YES YES SCOTT.TEST_TAB

To verify that all table data has been exported, the following options are available.

1. Connect AS SYSDBA and check the number of rows in the table.

SQL> CONNECT / AS SYSDBA
SQL> SELECT COUNT(*) FROM scott.test_tab;

COUNT(*)
----------
             2

Compare this number with the number of exported rows in the export logfile. If different, not all rows have been exported due to the enabled FGA control policy. See below how to resolve this.

Or in Oracle9i and above:

2. Connect as a user who has the EXEMPT ACCESS POLICY privilege and check the number of rows in the table.

SQL> CONNECT system/manager
SQL> GRANT exempt access policy TO system;
SQL> SELECT COUNT(*) FROM scott.test_tab;

COUNT(*)
----------
             2

Compare this number with the number of exported rows in the export logfile. If different, not all rows have been exported due to the enabled FGA control policy. See below how to resolve this.


How to prevent the EXP-79 and EXP-80 warnings during an export?

1. Run a DIRECT path export (Oracle8i and above), while connected as a user who has the EXP_FULL_DATABASE role. Example:

SQL> SELECT grantee FROM dba_role_privs WHERE granted_role IN
(SELECT grantee FROM dba_role_privs
WHERE granted_role='EXP_FULL_DATABASE');

GRANTEE
----------------
SYS
SYSTEM
...

exp system/manager FILE=exp_tab.dmp LOG=exp_tab.log TABLES=scott.test_tab DIRECT=y

About to export specified tables via Direct Path ...
Current user changed to SCOTT
. . exporting table TEST_TAB 2 rows exported
Export terminated successfully without warnings.

Note:
Rows in tables that contain objects and LOBs will be exported using conventional path method, even if direct path was specified. If this table has fine-grained access control policy (FGA) enabled, the export may still error with:

EXP-00008: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-28112: failed to execute policy function

In those cases, run the export while connected as a user who has the EXEMPT ACCESS POLICY enabled (only available in Oracle9i and above). See solution (3) mentioned below.

For details, see also:
"Parameter DIRECT: Conventional Path Export Versus Direct Path Export"

- OR -

2. Run the export while connected AS SYSDBA (Oracle8i and above).

Example:

exp '/ as sysdba' FILE=exp_tab.dmp LOG=exp_tab.log TABLES=scott.test_tab

About to export specified tables via Conventional Path ...
Current user changed to SCOTT
. . exporting table TEST_TAB 2 rows exported
Export terminated successfully without warnings.

For details, see:
"How to Connect AS SYSDBA when Using Export or Import"

- OR -
in Oracle9i and above:

3. Run the export while connected as a user who has the EXEMPT ACCESS POLICY (only available in Oracle9i and above).

Example:

SQL> CONNECT system/manager
SQL> GRANT exempt access policy TO scott;

exp scott/tiger FILE=exp_tab.dmp LOG=exp_tab.log TABLES=test_tab

About to export specified tables via Conventional Path ...
EXP-00079: Data in table "TEST_TAB" is protected. Conventional path may only
be exporting partial table.
. . exporting table TEST_TAB 2 rows exported
Export terminated successfully with warnings.

Note that although all rows have been exported, the EXP-79 warning is still given. This is caused by:
"Exempt Access Policy Privilege Not Properly Recognized By The Export Utility".

In this case, you can ignore the warning.

Remarks

Note that this document does not apply to the Oracle10g Export DataPump utility. An export with the new Oracle10g Export DataPump utility will not give any warning message.

For details, see:
"NO EXP-79 WARNING IF EXP WITH EXPORT DATAPUMP AND TABLE HAS FGA POLICY APPLIED"

References

"Exempt Access Policy Privilege Not Properly Recognized By The Export Utility"


"Parameter DIRECT: Conventional Path Export Versus Direct Path Export"
"All About Security: User, Privilege, Role, SYSDBA, O/S Authentication, Audit, Encryption, OLS"
"How to Connect AS SYSDBA when Using Export or Import"