Hiding things from datapump

Recently we had an organisational issue where we wanted to let some people export data out of anothers users schema, the only way to give this seemed to be to give EXP_FULL_DATABASE/DATAPUMP_EXP_FULL_DATABASE which seems excessive.

I wondered if i could give them that without giving the ability to extract any user they wanted.

I wondered how oracle is doing this internally as a number of schemas are excluded automatically - the internal ones basically, SYS, ORDSYS,CTXSYS.

Is this something hardcoded inside the executable or something 'configurable'?

After a bit of digging i found this:

So it would seem there is a table which contains a list of things to miss out - hmm can we make use of this? It also mentions the catmeta.sql script also (prvtmetd.sql does not seem to exist) - looking inside catmeta.sql we find this:

So it seems for certain objects types (don't know which) the performance of selecting from this table was too slow and they reverted to hardcoding the entries inside the view.

I didn't want to mess around with the views in particular so i thought i'd just try inserting some extra data into the table we found earlier on.

Looking at the table we see this - which is nice and self explanatory

SYS@>desc sys.ku_noexp_tab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ_TYPE                                           VARCHAR2(30)
 SCHEMA                                             VARCHAR2(30)
 NAME                                               VARCHAR2(30)

There are 213 rows in my table - including some for 'normal' schemas - (related to AQ internals it would seem - i didnt add them).

I want to try and exclude the schema aligne from being exported - so lets try and add that to the table based on what we see configured for ORDSYS


select * from sys.ku_noexp_tab where schema='ORDSYS' or name='ORDSYS'

USER                                                          ORDSYS
SCHEMA                                                        ORDSYS
ROLE_GRANT                                                    ORDSYS
DEFAULT_ROLE                                                  ORDSYS
SYSTEM_GRANT                                                  ORDSYS
OBJECT_GRANT                                                  ORDSYS
SYNONYM                        ORDSYS
TABLESPACE_QUOTA                                              ORDSYS

SO lets insert the same rows for 'ALIGNE'

insert into sys.ku_noexp_tab values ('USER',null,'ALIGNE');
insert into sys.ku_noexp_tab values ('SCHEMA',null,'ALIGNE');
insert into sys.ku_noexp_tab values ('ROLE_GRANT',null,'ALIGNE');
insert into sys.ku_noexp_tab values ('DEFAULT_ROLE',null,'ALIGNE');
insert into sys.ku_noexp_tab values ('SYSTEM_GRANT',null,'ALIGNE');
insert into sys.ku_noexp_tab values ('OBJECT_GRANT',null,'ALIGNE');
insert into sys.ku_noexp_tab values ('SYNONYM','ALIGNE',null);
insert into sys.ku_noexp_tab values ('TABLESPACE_QUOTA',null,'ALIGNE');


SYS@>commit;

Commit complete.







OK - that all went in OK - lets give it a try

[oracle@]::[~]# expdp / schemas=aligne

Export: Release 11.2.0.3.0 - Production on Mon Jan 13 19:47:54 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Starting "OPS$ORACLE"."SYS_EXPORT_SCHEMA_06":  /******** schemas=aligne
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB


ORA-39165: Schema ALIGNE was not found.
ORA-31655: no data or metadata objects selected for job
Job "OPS$ORACLE"."SYS_EXPORT_SCHEMA_06" completed with 2 error(s) at 19:48:35

So it seems to work - note the error is slightly different to the one you get if the schema genuinely doesnt exist - which is

[oracle@]::[~]# expdp / schemas=fhuisdhfuih

Export: Release 11.2.0.3.0 - Production on Mon Jan 13 19:48:51 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
ORA-39001: invalid argument value
ORA-39170: Schema expression 'FHUISDHFUIH' does not correspond to any schemas.

OK great - now were looking at it lets just try and hide a couple of tables only and leave everything else.

SYS@>delete from sys.ku_noexp_tab where schema='ALIGNE' or name='ALIGNE';

8 rows deleted.

SYS@>commit;

Commit complete.

SYS@>insert into sys.ku_noexp_tab values ('TABLE','ALIGNE','TPOW');

1 row created.

SYS@>insert into sys.ku_noexp_tab values ('TABLE','ALIGNE','TSHAPE');

1 row created.

SYS@>commit;

Commit complete.

so now TPOW and TSHAPE should be missing when we do an export.

 expdp / schemas=aligne reuse_dumpfiles=y

We let it run  (for some reason there is a massive pause at the end which i hope is nothing to do with what i changed).

Now looking at the logfile i can see that those two tables are absent - so it seems to work.

catmeta.sql must only be used for unusual object types i would guess?

Now this was all very interesting but please dont try this on a proper system, this will be unsupported and Oracle will get terribly upset. There may be cases where this is a legit thing to do but only under the guidance and support of oracle.