Re: COPY table with LOBs

Great! That appears to do the trick.

There is one oddity, though, at least to me. The table's owner is now the user from whom the table was copied. I did this:

1. User 'dev' granted select priveleges on 'table' to user 'test';
2. User test did the CTAS;

The table was created and appears to have all the LOBs, however, a query of:

    SELECT table_name, owner from all_all_tables by user 'test' shows that the table is owned by user 'dev.'

That seems like it'll be a problem. Why didn't the table take on the ownership of the user that created it?

Dave
FYI: I'm using Oracle 8.1.7 and am relatively new to Oracle. (if you couldn't tell!)

"Brian Peasland" <dba_at_remove_spam.peasland.com> wrote in message news:409FB81F.666942F9_at_remove_spam.peasland.com...
> Dave Bender wrote:
> >
> > How can I easily copy a table and its data from one user schema to
another
> > when the table contains one or more LOB columms?
> >
> > I've tried the SQL*Plus "COPY" command and this works fine for all the
other
> > tables we need to copy. But if the table contains a LOB, I get this
error:
> >
> > CPY0012: Object datatypes cannot be copied
> >
> > I've searched high and low for anything about the specific error
(CPY0012),
> > but haven't had much luck. However, the error message combined with the
> > table description seems to point to the LOBs as the culprit. If that's
the
> > problem, how do I move the LOBs (ideally without having to write a
program
> > to do it)? We'd like this to happen on the Oracle server and not have
to
> > round-trip the LOB-ular data back and forth, since it's rather large.
> >
> > Thanks in advance.
> >
> > Dave
>
> Have you tried the CTAS method (CREATE TABLE AS SELECT)?
>
> Do something similar to the following:
>
> CREATE TABLE new_user.new_table
> AS SELECT * FROM old_user.old_table;
>
> It should copy LOB columns as well.
>
> HTH,
> Brian
>
>
> --
> ===================================================================
>
> Brian Peasland
> dba_at_remove_spam.peasland.com
>
> Remove the "remove_spam." from the email address to email me.
>
>
> "I can give it to you cheap, quick, and good. Now pick two out of
> the three"
Received on Mon May 10 2004 - 14:24:32 CDT