Re: Move lobs

From: Anne Fontaine <anne.fontaine_at_clinsight.fr>
Date: Tue, 7 Oct 2003 10:27:10 +0200
Message-ID: <bltsbn$bld$1@reader1.imaginet.fr>

Hello,

I knew when I wrote that news that 'Crossposting" was not the right thing to do and I should not have done it. I was simply waiting for an answer.

Actually, I know nothing about Oracle and, nevertheless, I have to move objects, lobs, to resize tablespaces.... It's a rather harsh training, isn't it?

My basic problem was to move the objects from a tablespace to another one and then to resize the first tablespace. I found the following method (with the help of a colleague of mine): - I moved all my objects (taking into consideration the fact that I only have tables with lobs and indexes):

          ALTER TABLE MY_TABLE MOVE TABLESPACE MY_TBLSPC
          ALTER INDEX MY_INDEX REBUILD TABLESPACE MY_TBLSPC
          ALTER TABLE MY_TABLE MOVE LOB(MY_LOB) STORE AS (TABLESPACE

MY_TBLSPC)
- I defragmented the MY_TBLSPC tablespace :

          ALTER TABLESPACE MY_TBLSPC COALESCE - I resized the MY_TBLSPC tablespace :

          ALTER DATABASE DATAFILE MY_FILE.DBF RESIZE 5M The problem of this solution is that some lobs are no longer readable. In the Oracle tables, I can read <Value Error> instead of <CLOB>.

I also tried another solution :
- I exported all my data.

          exp user_dba/user_pwd file=my_file.dmp owner=OWNER - I dropped my user (car tous les objets appartiennent au meme user) :

          drop user OWNER cascade
- I re-created my user in another tablespace :

          create user OWNER identified by PWD default tablespace MY_TBLSPC
          grant dba to OWNER
- I re-imported the data :
          imp user_dba/user_pwd file=my_file.dmp fromuser=OWNER touser=OWNER

The problem of this solution is that exporting seems to keep the "storages clauses" and therefore re-imports the data in the first tablespace. I tried to changed manually the .dmp file but, in that case, the import does not recognize the file anymore.

Here we are, I think the whole problem has been explained.

Does anyone have a solution now?
Do you know how to export data to the storages clauses?

Thanks for your help,

Anne.

"Daniel Morgan" <damorgan_at_x.washington.edu> a écrit dans le message de news:1065447939.483450_at_yasure...
> Anne Fontaine wrote:
>
> >Hello,
> >
> >When I move some lobs an error appears. In my table I have <Value Error>
> >instead of <CLOB> for several lobs.
> >
> >Does anyone know this problem ?
> >
> >Have you got a solution to give me ?
> >
> >Thanks in advance,
> >
> >Anne.
> >
> >
> Please don't crosspost to every usenet group that contains the word
> Oracle. You don't get
> more responses ... just angry ones for wasting people's time.
>
> In spite of posting the same thing three times, twice to groups where
> the topic is not relevant,
> you didn't include a single bit of useful information. For example ...
> how are you trying to move
> them? With what tool? With what code? From where? To where?
>
> Not off to a good start here I'm afraid. ;-)
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
Received on Tue Oct 07 2003 - 03:27:10 CDT