Suggestion: Investigate using FGAC.
I just spent part of my day working on a solution to a very similar problem.
I wanted to do a user level export but restrict the records from one table
based on the value of a timestamp field. I did not want to have multiple
exports. I originally considered a combination of tablespace and table
level exports, but it made the nightly import process rather difficult to
perform the way I would like.
The solution I settled on was to use Oracle's fine-grained access control
(FGAC). This feature has been available since 8i. I found FGAC a bit
difficult to get my head around, but I had no problems after I worked
through the example. I got the idea from Metalink DOC_ID: 162914.1 which
explains how to use FGAC to skip a table during an export. I also referred
to the Application Developer's Guide documentation.
The effect of the FAGC I implemented is restrict which records the EXPORT
user can see in a table. This effects only the EXPORT user. All other
users see all of the records.
RECORD COUNT ISSUED RUN AS ME (I SEE ALL RECORDS)
SQL> select count(*) from bigjet.message;
COUNT(*)
----------
342559
Export run as user EXPORT (HE SEES FILTERED RECORDS)
exp export/freewilly@WHALEJET file=test.dmp tables=bigjet.message
About to export specified tables via Conventional Path ...
Current user changed to BIGJET
EXP-00079: Data in table "MESSAGE" is protected. Conventional path may only
be exporting partial table.
. . exporting table MESSAGE 52445 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
The basic steps I followed are listed below.
/****************************************
CREATE Function to return predicate
used to filter records for the EXPORT user
****************************************/
CREATE OR REPLACE FUNCTION export.export_message(obj_schema varchar2,
obj_name varchar2)
RETURN VARCHAR2 IS d_predicate VARCHAR2(2000);
BEGIN
IF sys_context('USERENV','SESSION_USER')='EXPORT' THEN
d_predicate := 'SYS_LAST_CHANGED_TS > TRUNC(SYSDATE-2) ';
ELSE
d_predicate := '';
END IF;
RETURN d_predicate;
END export_message;
/
/****************************************
CREATE FGAC Policy Group
****************************************/
EXECUTE DBMS_RLS.CREATE_POLICY_GROUP('BIGJET','MESSAGE','MESSAGE_GROUP');
/****************************************
CREATE FGAC Policy in the Policy Group
****************************************/
EXECUTE
DBMS_RLS.ADD_GROUPED_POLICY('BIGJET','MESSAGE','MESSAGE_GROUP','EXPORT_POL',
'EXPORT','EXPORT_MESSAGE');
/****************************************
ENABLE FGAC Policy
****************************************/
EXECUTE DBMS_RLS.ENABLE_GROUPED_POLICY
('BIGJET','MESSAGE','MESSAGE_GROUP','EXPORT_POL',TRUE);
/****************************************
DISABLE FGAC Policy
****************************************/
EXECUTE DBMS_RLS.ENABLE_GROUPED_POLICY
('BIGJET','MESSAGE','MESSAGE_GROUP','EXPORT_POL',FALSE);
> -----Original Message-----
> Is there a
> way to skip
> certain tables during an import?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephen Lee
INET: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ed Bittel
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
