not import one huge table - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: not import one huge table

  1. #11
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Itīs on Metalink Notes using FGAC/Policies to skip tables when export...

  2. #12
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  3. #13
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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
    Last edited by tamilselvan; 06-23-2004 at 01:33 PM.

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

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