Moving tables with LOBS
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Moving tables with LOBS

Hybrid View

  1. #1
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Anybody have any experiences moving tables with LOBS from one db to another ? Pre-import table creation and import with ignore=y seems do not work for me. I have tables with CLOBS to be moved.. (internal LOBS only)

    *COPY command doesn't work, SQL*Plus crashed for me when I tried.
    *CTAS doesn't work as there needs to dblink and LOBS are not supported over dblinks.

    Is there any easy of doing it ? other than creating the table with LOB on target database and inserting the data with SQL*PLus inserts.
    Reddy,Sam

  2. #2
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Sam,

    Have you tryed the dbms_lob.copy? I have not tryed but i think it will work over a db link.

    Cheers

    Angel

  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Originally posted by aarroyob
    Have you tryed the dbms_lob.copy?
    Nope! its not supported either in distributed environment ( I mean using dblink).
    Reddy,Sam

  4. #4
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    This is part of the note 119897.1 from metalink:

    "In short, you cannot select a lob column using remote dblink. You have to use
    DBMS_LOB package in a programming language like PL/SQL, JAVA to manipulate LOB
    data."

    But probably you have reason and the note is confussing

    Cheers

    Angel

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Originally posted by aarroyob

    But probably you have reason and the note is confussing
    TRUE ! Metalink Note#158924.1

    $oerr ora 22992
    22992, 00000, "cannot use LOB locators selected from remote tables"
    // *Cause: A remote LOB column cannot be referenced.
    // *Action: Remove references to LOBs in remote tables.
    Reddy,Sam

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