DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: not import one huge table

  1. #1
    Join Date
    Jun 2000
    Posts
    315

    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!

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    imp help=y

    (TABLES=)

  3. #3
    Join Date
    Jan 2001
    Posts
    3,134
    You need to do a table level import and exclude that one huge table.
    I remember when this place was cool.

  4. #4
    Join Date
    Jun 2000
    Posts
    315
    The problem is that there are too many tables.

  5. #5
    Join Date
    Jan 2001
    Posts
    3,134
    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
    I remember when this place was cool.

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    The only way is to make a parfile and exclude this big table. That is what I usually do.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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".
    Last edited by jmodic; 06-22-2004 at 05:12 PM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    he is asking about import not export

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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...
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width