Selective Export

As a DBA you may have this type of requirement to refresh your test or dev/uat database with the production database but not need to take some big archive tables due to space constraint.

First general approach could be to create these table first and just start imp with ignore=y

But

This would be ok if you have 1 schema or few tables, but **think** about a scenario where 4 or 5 or more schemas are there and each schema is having 5 or 10 tables which are not required.

Then you need to create all schemas in target database giving up privileges/quotas and then create all these tables. in 9i we have dbms_metadata.get_ddl to get table structure, if you stuck with
old century release [8.1.*] then either you will be using first export for these tables and then imp with index file and then formatting that index file in order to run that or use DESC.

And If your tables are real ig then you would be spending more time to export [ for nothing ] which you would not be importing :) like in one env. we were having a small database of 60 GB but
these tables were using 25 GB space. So, again no point to take export of these tables and you need more space on OS level and need more resources of database also. And if using consistent=y
[obvious in real time ] then ORA-1555 [ Snapshot too Old ] is just waiting for to happen specially in 8i or in 9i pretty good chances for large running queries if your undo is properly set.

In my scenario I got few 20 tables in different schemas which were not required in DEV/UAT databases.

Note : in 10g we have this feature to exclude specific tables in data pump

I used "Fine Grained Access Control". Normally we use this to set up "Virtual Private Database" using DBMS_RLS package to create the policies on tables.

Virtual Private Database mean database would be PRIVATE for you/ or_each_session VIRTUALLY.

Like we would be setting up ** row low security **. Oracle will predict automatically a where clause with your each query. Like if you use "select * from emp" then as per our policy oracle can add a where clause " select * from emp where deptno= your.depnot ".

Mean you can not see other's data, you can not modify others data. You can see that data only for which you are permitted.

Here I am not using it's full functionality as our main concern is "Selective Export".


SQL> show user

USER is "OPS$ORA10"


SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

Here I am creating one function which will give us where clause which will be predicted
Dynamically to the queries.

SQL> create or replace function where_clause
2 ( p_1 varchar2,
3 p_2 varchar2
4 )
5 return varchar2
6 as
7 begin
8 return '1=2';
9 end;
10 /

Function created.

This function is returning '1=2'. we are passing 2 parameters which are required
for passing schema name and object name. these parameter would be passed automatically internally.

Here I am adding one policy using DBMS_RLS package.

This is self explantary like object_name,policy_name,policy_function and statement_types
like on which statements you want to add the where clause = '1-2'

Here we are attching this policy Only for SELECT statements on DEPT table.

SQL> begin
2 dbms_rls.add_policy
3 (
4 object_name=>'DEPT',
5 policy_name=>'policy_1',
6 POLICY_FUNCTION=>'where_clause',
7 statement_types=>'select'
8 );
9 end;
SQL> /

PL/SQL procedure successfully completed.

And if now I select from dept ....

SQL> select * from dept;

no rows selected

SQL> select count(*) from dept;

COUNT(*)
----------
0

It is not showing any result as our query is being executed
as "Select * from dept where 1=2"

But data is there you can see this ..

SQL> delete from dept; -- delete is working as we have this policy on for select

4 rows deleted.

SQL> roll

Rollback complete.


Because here we have coded this function for all the users/sessions.
but we want this behavior only for export sessions.

So, there are lot of ways to do it

o if you are using diff. user to take exp then put "if user=exp then 1=2 else null;'
o if you are using same user then check the program from v$session
and if like 'exp@%' then set this predict
o you can uses application context also and set it after connecting.

But here we would be using a diff. schema [EXP/EXP ] for taking backup, easy to go.

and I am modifying this function in order to attach where clause on for sessions
of EXP user.

SQL> Create or replace function where_clause
2 ( p_1 varchar2,
3 p_2 varchar2
4 )
5 return varchar2
6 as
7 v_client varchar2(30);
8 begin
9 if user = 'EXP' then -- checking if user EXP then 1=2 else return ''
10 return '1=2';
11 else
12 return '';
13 end if;
14 end;
15 /

Function created.

Now I am able to select table from ops$ORA10 user.

SQL> SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

But can not access this from exp/exp user as we have modified our code.
This policy would be working only for EXP users.

SQL> conn exp/exp

Connected.

SQL> select * from ops$ora10.dept;

no rows selected

SQL> delete from ops$ora10.dept; -- data is there

4 rows deleted.

SQL> roll

Rollback complete.


Now we can take exp of database from this user.

Here I am just taking exp of OPS$ORA10 user for testing purpose.

SQL> ho exp exp/exp file=f owner=ops\$ora10

Export: Release 10.2.0.1.0 - Production on Mon Mar 19 04:18:36 2007

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user OPS$ORA10
. exporting PUBLIC type synonyms
. exporting private type synonyms
....
. . exporting table DEMO_CUSTOMERS 7 rows exported
. . exporting table DEMO_IMAGES 11 rows exported
. . exporting table DEMO_ORDERS 10 rows exported
...
EXP-00079: Data in table "DEPT" is protected. <<<-----
Conventional path may only be exporting partial table.

. . exporting table DEPT --->>> 0 rows exported

. . exporting table EMP 14 rows exported
. exporting synonyms
. exporting views
. exporting post-schema procedural objects and actions
.....
. exporting statistics
Export terminated successfully with warnings.


Note : DEPT table got exported with 0 rows, And exp flash a error that table is protected [ with policy ] and may got partial exported [ as per our policy ]

So, We will create 1 function and need to attach policies to all tables which we do not want to export, we can add policies using a single loop.

We will end up with simple 1 dump file at database level, Only tables which are required, Big tables with structure only, -- no manually setup of schema/tables/quotas/privileges.

We can use this feature to export only partial data, like if you have tables which are not partitioned as per your requirement and you want only last 3 month's data.

I have seen this type of scenario where full database refresh with last 3 month data for big tables. if you are thinking to implement this by using "Query" clause then again you need to take multiple exports and again setting the same things schema/tables ...
as query only works for table level export only

But using this we will end up again 1 dump file which fulfill our all requirements.


Note : This feature will not work for direct=y and "/ as sysdba" user because direct=y -- we directly read from file and bypass the evaluation layer "/ as sysdba" --- I would simply say "This user is KING" no limitation works :)

I do not expect to use sys user to take exports.

Regards,
Jagjeet Singh