Re: Re: Reorganize Big Table with BLOB column

Hi Stefan,

Thanks,
Well, I think it is wrong to use alter table move at all. A monolythic operation that I can not control. That feels like doomed to fail.
Of course db write should not be involved. I think that is a conventional insert because of the BLOB column. I have however an idea to select from a pipeline table function that might improve things. Not sure, have to test if i get the chance. (No time so far.)

Regards

Lothar

----Ursprüngliche Nachricht----
Von : contact_at_soocs.de
Datum : 25/02/2021 - 08:29 (MN)
An : l.flatz_at_bluewin.ch, oracle-l_at_freelists.org Betreff : Re: Reorganize Big Table with BLOB column

Hello Lothar,
I might got it wrong but your main issue is that the "alter table move" fails because of getting stuck in "write complete" wait events, right?

If yes, then I would start digging into DBWR and what's the issue there, e.g. like issue with async I/O, I/O subsystem, etc.

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> l.flatz_at_bluewin.ch <l.flatz@bluewin.ch> hat am 24.02.2021 13:59 geschrieben:
>
>
> Hi,
>
> we have to move a completely fragmented table in an other Tablespace.
> The table contains About a billion rows (not partitioned ). It contains a BLOB column (securefile) storing a PDF.
> The big issue is the reconstruction of a text Index.
> Alter table move does fail in the sense that after a while it stucks with "write complete" waits.
> My collegue wants alter table move because she does not want to rebuild the text index. That is a Nightmare because of parsing .
>
> Questions:
> 1. I believe even alter table move will rebuild the index behind the Scenes anyway. True?
> 2. Will the LOB Locator Change if the LOb is relocted? (So that means does the lob locator have a physical charateristics as a rowid)
> 3. Is there a way to rebuild the text index but avoid reparsing?
> 4. what is the best practise to move such a table to a new tablespace? ( I have a Code solution that pulls a CLOB over a db link using a pipelined table function. I could Combine that with this idea : http://mareklall.blogspot.com/2015_06_01_archive.html)
>
> Reagards
> Lothar

--
http://www.freelists.org/webpage/oracle-l