When you do a large repair chained rows operation on a very large database, say about over 3 million chained rows in one table.
What happens when you disconnect or loose connection suddenly in a VPN environment?
What will happen when repair is going on and a database connection disappears?
What are the options to recover in Toad, does it rollback?
Toad should behave the same way as Oracle sqlplus. There are options such as:
-Toad Options | Oracle | Transactions - Commit after every statement.
-Toad Options | Oracle | Transactions - Rollback when closing connections
Oracle does a rollback when a session is disconnected. There is no commit between when the rows are inserted into the temporary table and when they are inserted back into the main table. So this if the session becomes disconnected somewhere in the process, the unchaining part of it will completely commit or be completely rolled back. However, depending on where the disconnect happens, you may be left with disabled triggers or constraints on the main table.
If you have triggers or constraints on the table, this is NOT something that should be done on a table while others may be using it, because the triggers and constraints will be temporarily disabled.
A sql monitor output of what happens is below.
----------------------------------
Timestamp: 09:28:53.125
SELECT column_name
FROM SYS.dba_tab_columns
WHERE table_name = 'TABLES'
AND owner = 'JDORLON'
AND data_type IN ('LONG', 'LONG RAW')
----------------------------------
Timestamp: 09:28:53.312
SELECT cols.column_name AS NAME, nullable, data_type AS TYPE,
DECODE (data_type,
'NUMBER', data_precision + data_scale,
data_length
) LENGTH,
data_precision PRECISION, data_scale scale, data_length dlength,
' ' comments
FROM SYS.user_tab_columns cols
WHERE cols.table_name = :tabname
ORDER BY column_id
TABNAME = 'TABLES'
----------------------------------
Timestamp: 09:28:53.578
CREATE TABLE jdorlon.toad_tmp_398713950618402816
AS SELECT * FROM jdorlon.TABLES
WHERE ROWID IN (SELECT head_rowid FROM jdorlon.chained_rows
WHERE owner_name = 'JDORLON'
AND table_name = 'TABLES')
----------------------------------
Timestamp: 09:28:54.812
SELECT c2.table_name, c2.owner table_owner, c2.constraint_name
FROM SYS.dba_constraints c2, SYS.dba_constraints c1
WHERE c2.constraint_type = 'R'
AND c1.owner = c2.r_owner
AND c1.constraint_name = c2.r_constraint_name
AND c1.owner = :ownname
AND NOT (c1.owner = c2.owner AND c1.table_name = c2.table_name)
AND c1.table_name = :tabname
ownname = 'JDORLON'
tabname = 'TABLES'
----------------------------------
Timestamp: 09:28:55.046
ALTER TABLE jdorlon.TABLES DISABLE ALL TRIGGERS
----------------------------------
Timestamp: 09:28:55.187
DELETE FROM jdorlon.TABLES
WHERE ROWID IN (SELECT head_rowid
FROM jdorlon.chained_rows
WHERE owner_name = 'JDORLON' AND table_name = 'TABLES')
----------------------------------
Timestamp: 09:28:55.296
INSERT INTO jdorlon.TABLES
SELECT *
FROM jdorlon.toad_tmp_398713950618402816
----------------------------------
Timestamp: 09:28:56.687
DROP TABLE jdorlon.toad_tmp_398713950618402816
----------------------------------
Timestamp: 09:28:56.687
Successful commit.
----------------------------------
Timestamp: 09:28:56.718
ALTER TABLE jdorlon.TABLES ENABLE ALL TRIGGERS
----------------------------------
Timestamp: 09:28:56.734
DELETE FROM jdorlon.chained_rows
WHERE owner_name = :o AND table_name = :n
o = 'JDORLON'
n = 'TABLES'
----------------------------------
Timestamp: 09:28:56.750
Successful commit.
----------------------------------
Timestamp: 09:28:57.812
SELECT column_name col, con.constraint_name con
FROM all_constraints con, all_cons_columns col
WHERE con.owner = :owner
AND col.owner = :owner
AND con.table_name = :NAME
AND col.table_name = :NAME
AND con.constraint_type IN ('P', 'U')
AND con.constraint_name = col.constraint_name
ORDER BY con.constraint_name, col.POSITION
Name = 'CHAINED_ROWS'
Owner = 'JDORLON'
N:B These options help in certain situation. You can test this by testing a smaller sample in your environment and disconnect from your VPN to see if Toad
behaves correctly or similar to SQLPlus's behavior.
