I created a snapshot of a schema in a database and then used that to generate a sync script for that schema in another database. (It is not possible to compare those schemas directly.) But, after comparing the target with the snapshot as the source, some buggy SQL was produced:
DROP INDEX DC----_---_----MA.PK_---_ORGANIZTIONS;
ALTER TABLE DC----_---_----MA.A--_-----AD_SENT
MOVE LOB (P------_---T)
STORE AS BASICFILE (
TABLESPACE DC----_---_TBL);
ALTER TABLE DC----_---_----MA.A--_-----AD_SENT
MODIFY LOB (P------_---T)
(
);
The drop index statement is part of a rename constraint operation in which the actual rename SQL statement is far below in the script. (The word 'ORGANIZATIONS' is misspelled and being corrected.) The drop index fails, of course, because the constraint is still present and enabled. It is the very first SQL statement in the sync script.
Immediately after that and unrelated to it, there are two alter table statements as part of a move lob operation in which the second statement has an empty storage clause so that the whole second statement was generated in error.
Note: The two alter table statements for the LOB are for the same LOB on the same table.
Cheers,
Russ
Do any of you TOAD gurus have any idea about this?
Cheers,
Russ
Hi Russ,
Sorry it fell through the cracks.
So, steps to reproduce it are:
- start with a schema that has a table with a lob and a pk constraint
- make a snapshot file
- rename the pk constraint in the db (or another one just like it)
- compare the snapshot file to the DB
Yes sir. That looks about right.
Cheers,
Russ
The compare of the LOB must result in moving it to a different tablespace to generate the move lob code.
Ah, so new steps:
- start with a schema that has a table with a lob and a pk constraint
- make a snapshot file
- rename the pk constraint in the db (or another one just like it)
- move the lob to a different tablespace
- compare the snapshot file to the DB
And then when I compare...which is source and which is target? (edit: I guess can just try both)
Source is the snapshot with the PK constraint and the LOB. Target is the after changes state where PK constraint had been renamed and the LOB had been moved to a different tablespace.
1 Like
Hm, I must be doing something wrong. I didn't check to see that the index got renamed along with the constraint. Maybe that was it.
I got this script and it ran w/o error:
--------------------------------------------------------------------------
-- Run this script in BLOB_TEST@AZURE_19C_PLUG to make it look like c:\temp\BLOB_TEST.xml.
--
-- Please review the script before using it to make sure it won't cause any unacceptable data loss.
--
-- BLOB_TEST@AZURE_19C_PLUG schema extracted by user JDORLON
-- c:\temp\BLOB_TEST.xml schema extracted by user JDORLON
-- from snapshot file: c:\temp\BLOB_TEST.xml
-- file created: 10/6/2022 10:52:33 AM
ALTER TABLE BLOB_TEST.AA_CLOB_BLOB
MOVE LOB (BLOB_DATA)
STORE AS SECUREFILE (
TABLESPACE USERS
STORAGE (
INITIAL 104K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
));
ALTER INDEX BLOB_TEST.AA_CLOB_BLOB_PK REBUILD;
ALTER TABLE BLOB_TEST.AA_CLOB_BLOB
RENAME CONSTRAINT AA_CLOB_BLOB_NEW_NAME_PK
TO AA_CLOB_BLOB_PK;
I don't know why TOAD gave me such a different result. There must be some additional condition in my situation that I haven't identified or maybe some option I should or should not have specified in the process either when generating the snapshot or when comparing. I will take a closer look.
Cheers,
Russ
I was able to reproduce it by renaming the constraint and the index.
I still don't know why you got the 2nd alter table stmt with the empty storage clause, but I can probably fix that one w/o being able to reproduce it.
I'll log the index/constraint problem. Shouldn't be too hard to fix.
If it's not too late, please compare the LOGGING property of those lobs.
select owner, table_name, column_name, logging
from dba_lobs
where owner = ...
and table_name = ...
and column_name = ...
I think that's what was causing the lob problem. The difference caused Toad to start making the "alter table modify lob" stmt, but we didn't finish it because this statement is illegal:
ALTER TABLE BLOB_TEST.AA_CLOB_BLOB
modify LOB (CLOB_DATA)
(nologging);
Source and target have the same setting--both are 'logging'.
darn, I thought that was it. Actually, I'm certain a LOGGING diff on the lob could cause it.
Ah! I see what else could do it. Looks like if one of the tables is empty (or at least the lob segment is empty), that can lead to this problem also.
I'll have it fixed when betas start again.
Looking at all the properties of the LOB, I find three differences:
SEGMENT_NAME
INDEX_NAME
SEGMENT_CREATED
On source, SEGMENT_CREATED is 'NO'. On target, SEGMENT_CREATED is 'YES'.
I had 19 schemas to compare, and these two errors were from the start of the first sync script that I tried. Being in a hurry with this matter, I gave up on it after that to consider methods other than TOAD. But, I should probably go back and do them all just to see if there are any other issues.
The only problem is that these databases I need to compare are in separate restricted zones. There is no way to get TOAD to connect to both simultaneously.
I think it was due to the segment not being created.
Try it again once we start betas on Monday. Let me know if anything else pops up.
In Object Types to Compare, 'Synonyms Private, for schema's objects' and 'Synonyms Public, for schema's objects' are both subdued so that I cannot select them to compare. As a result, public synonyms are being flagged as differences, and the sync script creates them as public synonyms.
The intent of that being disabled is when you are comparing a pair of schemas in the same database.
In that case, if there are private synonyms for the schema's objects - the only thing we could compare is synonym owner, but since we are in the same database, there's not really a way to know what's right and wrong here.
Similarly with public synonyms for schema's objects.
I guess in your case, we are comparing a file to a live schema? I can't remember offhand what should happen in that scenario, but in any case...if the options are unchecked, they shouldn't be compared. So it does seem like there is some bug here. I'm done for the day but can look tomorrow.
