Archive

Archive for January, 2013

Exclude table/tables during export.

Let’s assume you’re using EXP, not EXPDP to backup your tables (I still have some Oracle 9i databases). You have schema with about 1000 tables, and the list can change. There is one table that is huge, and you don’t need to export this or one of this table is corrupted, DBMS_REPAIR doesn’t work and you want the full export without this one table. This is what you can do:

1. Assume that user you use to export is SYSTEM (of course you can use other user name).

2. Create function (SYS AS SYSDBA) that returns condition that is false when user is SYSTEM:

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') = 'SYSTEM' THEN
d_predicate := '1=2';
else
d_predicate := '';
end if;
RETURN d_predicate;
END exclude_table;

So, if user is SYSTEM, condition 1=2 will be added to select statement doing export, as a result no rows will be exported.

3. Add policy for each table you want to exclude:

execute dbms_rls.add_policy ('EXAMPLE_SCHEMA','EXAMPLE_TABLE','EXCLUDE_FROM_EXPORT','SYSTEM','EXCLUDE_TABLE');

4. Now when you run export, table EXAMPLE_SCHEMA.EXAMPLE_TABLE will be skipped, like this:

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

One important thing to remember – you cannot use option DIRECT in export.
During import the empty table EXAMPLE_TABLE will be created with all related objects (indexes, constraints, triggers, etc.).