If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Oh, there are other solution. And quite elegant ones, too. For example, using RLS (row level security).

Suppose you want to export all the tables from SCOTT's schema, there are thousands of tables there. But there is one huge GARBAGE_TABLE with billions of rows that you don't want to export. You can afford to export this table's definition, but you certanly don't want to export its data.

All you have to do is to make sure that the user you are doing export with can't see that table's rows, even if they are there. RLS is just as god's gift here.

For start we'll create a function that creates a predicate for that taable based on who's accessing it.

Next we attach the special RLS policy to the table SCOTT.GARBAGE_TABLE and use the above function to implement that policy. This policy says: "For every user that has garnts to select from table SCOTT.GARBAGE_TABLE, return them all the rows that they are interested in. But if that user is EXP_USER then return him no rows, behave like the table is totaly empty".

Now we are all set up. Everybody that has the right permisions can see each and every row from that table, except for user EXP_USER, to whom the table will appear as empty. No matter which tool he uses, wether it is SQL*Plus or TOAD or EXP or whatever - he'll see the table, but he will not be able to see any data from it. So you just go ahead and export SCOTT's schema (or entire database) with that user EXP_USER, the result for GARBAGE_TABLE will simply be:

"exporting table GARBAGE_TABLE ..... 0 rows exported".

Last edited by jmodic; 06-22-2004 at 05:12 PM.

Jurij ModicASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?