| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Exporting Tables
hi
i found one way to do this in metalink
How to Skip Tables when Exporting a User or an Entire Database
This solution only works with Oracle versions supporting FGAC. Oracle 8i and above support FGAC in the Enterprise Edition and the Personal Edition. The Standard Edition does not include the FGAC option, and it will not work.
Create user EXP_DB identified by EXP_DB; Grant DBA to EXP_DB;
Specify default tablespaces as required.
3. Configure Fine-Grained Access Control.
3.1 Connect as you new dba user EXP_DB.
3.2 Create a function which will return the predicate.
CREATE or REPLACE FUNCTION exclude_table (obj_schema VARCHAR2, obj_name VARCHAR2) RETURN VARCHAR2 IS d_predicate VARCHAR2(2000);
BEGIN
if sys_context ('USERENV', 'SESSION_USER') = 'EXP_DB' THEN
d_predicate := '1=2';
else
d_predicate := '';
end if;
RETURN d_predicate;
END exclude_table;
The following condition 1=2 is always false so rows will never be returned for EXP-DB. 3.2 Create a Policy for each table you want to skip.
execute dbms_rls.add_policy ('SCOTT','EMP','POL_EMP','EXP_DB','EXCLUDE_TABLE')
Where: 'SCOTT' --> Owner of the table you want to skip.
'EMP' --> Table you want to skip.
'POL_EMP' --> The name of your policy.
'EXP_DB' --> Owner of the predicate function.
'EXCLUDE_TABLE' --> Function which returns the predicate.
Now, when DBA_EXP will query EMP, no rows will be returned. You must create a policy for each table you want to skip.
4. Export your database or schema using the user EXP_DB.
You will get warnings like this for each table skipped: EXP-00079: Data in table "EMP" is protected. Conventional path may only be
exporting partial table.
Since the predicate 1=2 is always false, no rows will be exported.
5. Maintaining the list of tables.
5.1 Removing tables from the excluded list.
execute dbms_rls.drop_policy ('SCOTT','EMP','POL_EMP');
Where: 'SCOTT' --> Owner of the table you want to skip.
'EMP' --> Table you want to skip.
'POL_EMP' --> The name of your policy.
5.2 How to check which tables are excluded.
Use the views DBA/USER/ALL_POLICIES to see which tables are excluded.
--> select POLICY_NAME, OBJECT_NAME, FUNCTION
from DBA_POLICIES
where PF_OWNER='EXP_DB';
6. Example: Skip EMP and DEPT when exporting scott.
6.1 In SQL*Plus:
SQL> connect system/manager
Connected.
SQL> Create user EXP_DB identified by EXP_DB;
User created.
SQL> Grant DBA to EXP_DB;
Grant succeeded.
SQL> connect exp_db/exp_db
Connected.
SQL> CREATE or REPLACE FUNCTION exclude_table
2 (obj_schema VARCHAR2, obj_name VARCHAR2)
3 RETURN VARCHAR2 IS d_predicate VARCHAR2(2000);
4
5 BEGIN
6 if sys_context ('USERENV', 'SESSION_USER') = 'EXP_DB' THEN
7 d_predicate := '1=2';
8 else
9 d_predicate := '';
10 end if;
11 RETURN d_predicate;
12 END exclude_table;
13 /
Function created.
SQL> execute dbms_rls.add_policy ('SCOTT','DEPT','POL_DEPT','EXP_DB',
'EXCLUDE_TABLE') PL/SQL procedure successfully completed.
SQL> execute dbms_rls.add_policy ('SCOTT','EMP','POL_EMP','EXP_DB',
'EXCLUDE_TABLE') PL/SQL procedure successfully completed.
SQL> select POLICY_NAME, OBJECT_NAME, FUNCTION
from DBA_POLICIES
where PF_OWNER='EXP_DB';
POLICY_NAME OBJECT_NAME FUNCTION
----------------------- ----------------------- -----------------------
POL_DEPT DEPT EXCLUDE_TABLE
POL_EMP EMP EXCLUDE_TABLE
SQL> connect scott/tiger
Connected.
SQL> select count(*) from scott.emp;
COUNT(*)
14
SQL> select count.
(*) from scott.dept;
COUNT(*)
4
SQL> 6.2 Export
Export: Release 8.1.7.1.1 - Production on Wed Jul 25 10:32:30 2001
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Username: exp_db/exp_db
Connected to: Oracle8i Enterprise Edition Release 8.1.7.1.1 - Production
With the Partitioning option
JServer Release 8.1.7.1.1 - Production
Enter array fetch buffer size: 4096 >
Export file: EXPDAT.DMP >
(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U >
Export grants (yes/no): yes >
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
About to export specified users ...
User to be exported: (RETURN to quit) > scott
User to be exported: (RETURN to quit) >
. exporting pre-schema procedural objects and actions . exporting foreign function library names for user SCOTT . exporting object type definitions for user SCOTTAbout to export SCOTT's objects ...
. exporting database links . exporting sequence numbers . exporting cluster definitions . about to export SCOTT's tables via Conventional Path ... . . exporting table BONUS 0 rows exportedEXP-00079: Data in table "DEPT" is protected. Conventional path may only be exporting partial table.
. . exporting table DEPT 0 rows exported . . exporting table DUMMY 1 rows exportedEXP-00079: Data in table "EMP" is protected. Conventional path may only be exporting partial table.
. . exporting table EMP 0 rows exported . . exporting table HTTP_TEST 1 rows exported . . exporting table MASTER 3 rows exported . . exporting table OUTER_CTX 6 rows exported . . exporting table OUTER_OTHER 3 rows exported . . exporting table SALGRADE 5 rows exported . . exporting table TEST_TAB 2 rows exported . . exporting table TST 5 rows exported . . exporting table TST2 9 rows exported . exporting synonyms . exporting views . exporting stored procedures . exporting operators . exporting referential integrity constraints . exporting triggers . exporting indextypes . exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting snapshots . exporting snapshot logs . exporting job queues . exporting refresh groups and children . exporting dimensions
-- Message posted via http://www.oraclemonster.comReceived on Tue Dec 28 2004 - 23:43:05 CST
 |
 |