not import one huge table

I am doing a user mode import. There is a huge table I don't need. How could I not import this table but every other table?

Thanks!

imp help=y

(TABLES=)

You need to do a table level import and exclude that one huge table.

The problem is that there are too many tables.

Use SQL* to spool a comma delimited file. Or just import the monster and drop it. I'm not sure that you have any other solutions.

MH

The only way is to make a parfile and exclude this big table. That is what I usually do.

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.

Code:

---

CREATE OR REPLACE FUNCTION scott.exclude_from_export_RLS
    (p_schema IN VARCHAR2,
    p_object IN VARCHAR2)
RETURN VARCHAR2
AS
  v_predicate VARCHAR2(1000);
BEGIN
  IF user = 'EXP_USER' THEN v_predicate := '1=2';
  ELSE v_predicate := '';
  END IF;
  RETURN v_predicate;
END;
/

---

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".

Code:

---

BEGIN
  DBMS_RLS.ADD_POLICY
    (object_schema => 'SCOTT',
    object_name  => 'GARBAGE_TABLE',
    policy_name  => 'EXCLUDE_FROM_EXPORT_POLICY',
    function_schema => 'SCOTT',
    policy_function => 'EXCLUDE_FROM_EXPORT_RLS'
    );
END;
/

---

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".

he is asking about import not export

Ah, what-a-mistaka-to-meka! Sorry.

In that case, how about simply having that huge table precreated in the target schema and using IGNORE=N while importing?

Quote:

---

Originally posted by jmodic
Ah, what-a-mistaka-to-meka! Sorry.

In that case, how about simply having that huge table precreated in the target schema and using IGNORE=N while importing?

---

But your export solution is very ellegant. Never tried that...

It´s on Metalink Notes using FGAC/Policies to skip tables when export...

I have not tried this. But I think it may work.

create a small tablespace, test.
Put the garbage table into test tablespace.
Change the tablespace into read only.

Start import with ignore=Y. You may get an error, but ignore them. Check all other tables rows are imported.

Tamil

Yes It worked. I just move the table T2 to read only tablespace. And the import worked fine.

UAT:SID=SBLUAT1=>imp / file=user1.dmp formuser=user1 touser=user1 rows=y ignore=Y
LRM-00101: unknown parameter name 'formuser'

IMP-00022: failed to process parameters, type 'IMP HELP=Y' for help
IMP-00000: Import terminated unsuccessfully

[ /opt/sblppr2/stage/benchmark ]
UAT:SID=SBLUAT1=>imp / file=user1.dmp fromuser=user1 touser=user1 rows=y ignore=Y

Import: Release 9.2.0.4.0 - Production on Wed Jun 23 11:05:08 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing USER1's objects into USER1
. . importing table "T1" 6344 rows imported
. . importing table "T2"
IMP-00058: ORACLE error 372 encountered
ORA-00372: file 8 cannot be modified at this time
ORA-01110: data file 8: '/opt/sblppr2/ora01/SBLUAT1/sbluat1_medium_data02.dbf'
. . importing table "T3" 6346 rows imported
Import terminated successfully with warnings.

2nd method:
create a dummy table with the same columns as in garbage table.
Add suitable check constraint so that insert will fail.
For example, on a number column, you can add check rn = -99999.
Now you create a view (name should match garbage table).
Start your import.
This method also worked fine. I would opt for import through view, if the table is small. It is simple. But performance wise, the first method works faster than 2nd method.


Tamil

Quote:

---

Originally posted by tamilselvan
2nd method:
create a dummy table with the same columns as in garbage table.
Add suitable check constraint so that insert will fail.
For example, on a number column, you can add check rn = -99999.
Now you create a view (name should match garbage table).

---

Isn't there too much unnecessary steps in there? Why bothering creating a dummy table with the same columns as in garbage table + create some check constraints + create a view? When you can simply create a dummy table with the exact name as the original GARBAGE_TABLE have and just make sure that this table has different columns as the original one (it should differ either in number of columns or in column names or in column datatypes)? In fact, the dummiest table with a single column should be just fine. Import will fail for this existing table but will succeed for all other tables.

Also don't see much point in moving the GARBAGE_TABLE to the read only tablespace when the mentioned method is much much simplier.