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: @OracleSK
l.flatz@xxxxxxxxxx <l.flatz@xxxxxxxxxx> 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
