Hello Tom,
i tried to figure out which information is logged (and how it is logged) in the redolog files, if i create an index.
I have created a little test table with one field (number) and round about 128 datasets. After that i created a non unqiue index on the table.
Then i run the logminer on the corresponding redolog file and check the logged statements.
It just looks like this:
=======================================
SQL> select SCN, OPERATION, SQL_REDO, SQL_UNDO from V$LOGMNR_CONTENTS;
605670 DDL create index i_test on test(a);
605672 INSERT insert into "SYS"."ICOL$"("BO#","OBJ#","COL#","POS#","SEGCOL#","SEGCOLLENGTH","OFFSET","INTCOL#","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('47762','47764','1','1','0','0','0','1','0','0',NULL,NULL,NULL,NULL); delete from "SYS"."ICOL$" where "BO#" = '47762' and "OBJ#" = '47764' and "COL#" = '1' and "POS#" = '1' and "SEGCOL#" = '0' and "SEGCOLLENGTH" = '0' and "OFFSET" = '0' and "INTCOL#" = '1' and "SPARE1" = '0' and "SPARE2" = '0' and "SPARE3" IS NULL and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAACAABAAAMp0AAB';
605672 INSERT insert into "SYS"."IND$"("BO#","OBJ#","DATAOBJ#","TS#","FILE#","BLOCK#","INDMETHOD#","COLS","PCTFREE$","INITRANS","MAXTRANS","PCTTHRES$","TYPE#","FLAGS","PROPERTY","BLEVEL","LEAFCNT","DISTKEY","LBLKKEY","DBLKKEY","CLUFAC","ANALYZETIME","SAMPLESIZE","ROWCNT","INTCOLS","DEGREE","INSTANCES","TRUNCCNT","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('47762','47764','47764','4','4','19','0','1','10','2','255',NULL,'1','2','0','0','1','1','1','1','1',TO_DATE('20.08.08 11:28:13', 'DD.MM.RR HH24:MI:SS'),'128','128','1',NULL,NULL,NULL,'1',NULL,NULL,NULL,NULL,TO_DATE('20.08.08 11:28:13', 'DD.MM.RR HH24:MI:SS')); delete from "SYS"."IND$" where "BO#" = '47762' and "OBJ#" = '47764' and "DATAOBJ#" = '47764' and "TS#" = '4' and "FILE#" = '4' and "BLOCK#" = '19' and "INDMETHOD#" = '0' and "COLS" = '1' and "PCTFREE$" = '10' and "INITRANS" = '2' and "MAXTRANS" = '255' and "PCTTHRES$" IS NULL and "TYPE#" = '1' and "FLAGS" = '2' and "PROPERTY" = '0' and "BLEVEL" = '0' and "LEAFCNT" = '1' and "DISTKEY" = '1' and "LBLKKEY" = '1' and "DBLKKEY" = '1' and "CLUFAC" = '1' and "ANALYZETIME" = TO_DATE('20.08.08 11:28:13', 'DD.MM.RR HH24:MI:SS') and "SAMPLESIZE" = '128' and "ROWCNT" = '128' and "INTCOLS" = '1' and "DEGREE" IS NULL and "INSTANCES" IS NULL and "TRUNCCNT" IS NULL and "SPARE1" = '1' and "SPARE2" IS NULL and "SPARE3" IS NULL and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" = TO_DATE('20.08.08 11:28:13', 'DD.MM.RR HH24:MI:SS') and ROWID = 'AAAAACAABAAAMp0AAA';
605672 UPDATE update "SYS"."SEG$" set "TYPE#" = '6', "BLOCKS" = '8', "EXTENTS" = '1', "INIEXTS" = '8', "MINEXTS" = '1', "MAXEXTS" = '2147483645', "EXTSIZE" = '128', "EXTPCT" = '0', "USER#" = '48', "LISTS" = '0', "GROUPS" = '0', "CACHEHINT" = '0', "SCANHINT" = '0', "HWMINCR" = '47764', "SPARE1" = '131329' where "TS#" = '4' and "FILE#" = '4' and "BLOCK#" = '19' and "TYPE#" = '3' and "BLOCKS" = '8' and "EXTENTS" = '1' and "INIEXTS" = '8' and "MINEXTS" = '1' and "MAXEXTS" = '2147483645' and "EXTSIZE" = '128' and "EXTPCT" = '0' and "USER#" = '48' and "LISTS" = '0' and "GROUPS" = '0' and "BITMAPRANGES" = '0' and "CACHEHINT" = '0' and "SCANHINT" = '0' and "HWMINCR" = '47764' and "SPARE1" = '131457' and "SPARE2" IS NULL and ROWID = 'AAAAAIAABAAAM51AAD'; update "SYS"."SEG$" set "TYPE#" = '3', "BLOCKS" = '8', "EXTENTS" = '1', "INIEXTS" = '8', "MINEXTS" = '1', "MAXEXTS" = '2147483645', "EXTSIZE" = '128', "EXTPCT" = '0', "USER#" = '48', "LISTS" = '0', "GROUPS" = '0', "CACHEHINT" = '0', "SCANHINT" = '0', "HWMINCR" = '47764', "SPARE1" = '131457' where "TS#" = '4' and "FILE#" = '4' and "BLOCK#" = '19' and "TYPE#" = '6' and "BLOCKS" = '8' and "EXTENTS" = '1' and "INIEXTS" = '8' and "MINEXTS" = '1' and "MAXEXTS" = '2147483645' and "EXTSIZE" = '128' and "EXTPCT" = '0' and "USER#" = '48' and "LISTS" = '0' and "GROUPS" = '0' and "BITMAPRANGES" = '0' and "CACHEHINT" = '0' and "SCANHINT" = '0' and "HWMINCR" = '47764' and "SPARE1" = '131329' and "SPARE2" IS NULL and ROWID = 'AAAAAIAABAAAM51AAD';
605673 UPDATE update "SYS"."OBJ$" set "OBJ#" = '47762', "DATAOBJ#" = '47762', "TYPE#" = '2', "CTIME" = TO_DATE('20.08.08 11:23:11', 'DD.MM.RR HH24:MI:SS'), "MTIME" = TO_DATE('20.08.08 11:28:13', 'DD.MM.RR HH24:MI:SS'), "STIME" = TO_DATE('20.08.08 11:23:11', 'DD.MM.RR HH24:MI:SS'), "STATUS" = '1', "FLAGS" = '0', "OID$" = NULL, "SPARE1" = '6', "SPARE2" = '1' where "OBJ#" = '47762' and "DATAOBJ#" = '47762' and "OWNER#" = '48' and "NAME" = 'TEST' and "NAMESPACE" = '1' and "SUBNAME" IS NULL and "TYPE#" = '2' and "CTIME" = TO_DATE('20.08.08 11:23:11', 'DD.MM.RR HH24:MI:SS') and "MTIME" = TO_DATE('20.08.08 11:28:08', 'DD.MM.RR HH24:MI:SS') and "STIME" = TO_DATE('20.08.08 11:23:11', 'DD.MM.RR HH24:MI:SS') and "STATUS" = '1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0' and "OID$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3" IS NULL and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAASAABAAAMM8AAG'; update "SYS"."OBJ$" set "OBJ#" = '47762', "DATAOBJ#" = '47762', "TYPE#" = '2', "CTIME" = TO_DATE('20.08.08 11:23:11', 'DD.MM.RR HH24:MI:SS'), "MTIME" = TO_DATE('20.08.08 11:28:08', 'DD.MM.RR HH24:MI:SS'), "STIME" = TO_DATE('20.08.08 11:23:11', 'DD.MM.RR HH24:MI:SS'), "STATUS" = '1', "FLAGS" = '0', "OID$" = NULL, "SPARE1" = '6', "SPARE2" = '1' where "OBJ#" = '47762' and "DATAOBJ#" = '47762' and "OWNER#" = '48' and "NAME" = 'TEST' and "NAMESPACE" = '1' and "SUBNAME" IS NULL and "TYPE#" = '2' and "CTIME" = TO_DATE('20.08.08 11:23:11', 'DD.MM.RR HH24:MI:SS') and "MTIME" = TO_DATE('20.08.08 11:28:13', 'DD.MM.RR HH24:MI:SS') and "STIME" = TO_DATE('20.08.08 11:23:11', 'DD.MM.RR HH24:MI:SS') and "STATUS" = '1' and "REMOTEOWNER" IS NULL and "LINKNAME" IS NULL and "FLAGS" = '0' and "OID$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and "SPARE3" IS NULL and "SPARE4" IS NULL and "SPARE5" IS NULL and "SPARE6" IS NULL and ROWID = 'AAAAASAABAAAMM8AAG';
605674 COMMIT commit;
=======================================
With the logminer i can just see the DDIC DML statements, which are performed by an CREATE index, but i can't see how the index creation itself is logged.
If i specify the NOLOGGING option - there is generated less redolog information.
So my question is:
How does oracle log the index block creations/changes/modifications while an index creation?
Are the complete index blocks logged (like a block image) while an index creation?
Thanks and Regards
Stefan
.. i tried to figure out which information is logged (and how it is logged) in the redolog files, if i create an index. ...
the change vectors needed to take an empty block (they would all be new in a create index) and make it look like the block should after the create index are logged.
there is nothing "human readable" about it - it is not rows in a table, there are no updates, inserts, deletes - just redo vectors (changed bytes).
for an index create - it is basically just a copy of the index itself into the redo - think of it that way.
... If i specify the NOLOGGING option - there is generated less redolog information. ...
right - and you have to backup the affected datafiles right away (you cannot do media recovery on those blocks anymore!)