Redo Operation 24 - DDL Operations

24.1 - DDL Operation

DDL statements are recorded in changes with operation code 24.1. For example:

REDO RECORD - Thread:1 RBA: 0x000060.0000001f.0098 LEN: 0x0194 VLD: 0x01
SCN: 0x0000.001e0c5f SUBSCN:  1 04/13/2013 00:09:57
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:24.1 ENC:0

Unfortunately this is the only information available in this dump. The dump does not report the DDL statement or any other information. However, this information IS stored in the redo log.

The example on this page based on the following DDL statement:

CREATE TABLE t200
(
  c1 NUMBER,
  c2 VARCHAR2(30),
  c3 DATE,
  c4 NUMBER
);

In Oracle 11.2, this DDL statement is represented in the redo log by a change of type 24.1 which has 25 elements (0 to 24). I have identified the contents of the majority of these changes.

For each element the following description includes a block dump. It should be noted that this research was performed on Linux which is little endian. In the block dumps, byte data is read from left to right; 16-bit and 32-bit numbers are read byte-wise right to left. Until the context is understood it is not possible to determine whether a field will consist of bytes or numbers. The reason I dump byte data left to right is that it is usually simpler to start decoding a block dump by starting with the ASCII values (strings). When the ASCII values have been eliminated it is easier to identify the remaining numeric values.

Element 0

This fixed length element contains 24 bytes

10270000 06001700 27050000 01000000 00000100 01000000
Bytes # Bytes Description Value
0-3 4 Purpose unknown - always 10270000 0x10270000
4-5 2 XID Undo Segment Number (USN) 0x0006
6-7 2 XID Slot Number 0x0017
8-11 4 XID Sequence Number 0x00000527
12-13 2 Command 0x0001
14-15 2 Unknown 0x0000
16-17 2 Unknown 0x0000
18-19 2 Unknown 0x0001
20-23 4 Unknown 0x00000001

The XID undo segment number, XID slot number and XID sequence number correspond to the XIDUSN, XIDSLOT and XIDSQN columns in V$TRANSACTION for the recursive transaction created for the DDL statement.

The command is the internal action number of the DDL command. An almost complete list of commands can be found in the AUDIT_ACTIONS table.

SQL> SELECT name FROM audit_actions WHERE action = 1;

NAME
----------------------------
CREATE TABLE

Element 1

This is a variable length element. It contains the login user name

55533031

In the above example the login user name is US01.

Element 2

This is a variable length element. It contains the current user name.

55533033

In the above example the current user name is US03.

The current user name can be set using

ALTER SESSION SET CURRENT_SCHEMA = US03;

Element 3

This fixed length element contains 12 bytes

54000000 392D0100 0100470A

Bytes # Bytes Description Value
0-3 4 Login user id 0x00000054
4-7 4 Object id 0x00012D39 (77113)
8-9 2 Unknown 0x0001
10-11 2 Unknown 0x0A47

The user id can be determined from DBA_USERS. For example:

SQL> SELECT user_id FROM dba_users WHERE username = 'US01';

   USER_ID
----------
        84

The object id can be determined from DBA_OBJECTS. For example:

SQL> SELECT object_id FROM dba_objects
  2  WHERE owner = 'US03'
  3  AND object_name = 'T200'
  4  AND object_type = 'TABLE';

 OBJECT_ID
----------
     77113

Testing (using TRUNCATE) confirms that this value is not the data object id.

Bytes 8-9 appear to be dependent on the command type. Examples observed so far include:

Bytes 8-9 Command
0001 CREATE TABLE
0002 ALTER TABLE
0001 DROP TABLE
FFFF CREATE INDEX
0000 DROP INDEX
FFFF CREATE VIEW
FFFF DROP VIEW
0000 CREATE TRIGGER
0000 ALTER TRIGGER
0000 DROP TRIGGER

Bytes 10-11 are always 0xA47 in examples observed so far.

Element 4

In this example this element is empty

Element 5

This fixed length element contains 2 bytes

0000

This element is the recursive depth. In this example the DDL statement is a top level call.

Consider the following:

DECLARE
  l_str VARCHAR2(100) := 'CREATE TABLE t201 (c1 NUMBER)';
BEGIN
  EXECUTE IMMEDIATE l_str;
END;
/

The recursive depth for the CREATE TABLE statement will be 1 as it is called from within a PL/SQL block.

The depth is also reported in level 10046 trace as the dep value. For example:

PARSING IN CURSOR #139724080042120 len=29 dep=1 uid=84 oct=1 lid=84 tim=1365819945533638 hv=1251380688 ad='919340f0' sqlid='b8rk1at59d3fh'
CREATE TABLE t201 (c1 NUMBER)
END OF STMT
...
PARSE #139724080042120:c=0,e=356,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,
tim=1365819945533637
EXEC #139724080042120:c=65990,e=72822,p=0,cr=87,cu=51,mis=0,r=0,dep=1,og=1,
plh=0,tim=1365819945606539
CLOSE #139724080042120:c=0,e=2,dep=1,type=0,tim=1365819945606644

In the above example the depth is 1 (dep=1)

Element 6

This fixed length element contains 2 bytes

0200

In this example the value of this element is 2 for a recursive depth of 0. If the recursive depth is increased to 1, then the value of this element will be 3.

Note that this may be an oversimplification. Further testing is required.

Element 7

This is a variable length element

43524541 54452054 41424C45 20743230 300A280A 20206331 204E554D 4245522C
0A202063 32205641 52434841 52322833 30292C0A 20206333 20444154 452C0A20
20633420 4E554D42 45520A29 00

This element consists of a null-terminated string containing the DDL command. In this example:

CREATE TABLE t200
(
  c1 NUMBER,
  c2 VARCHAR2(30),
  c3 DATE,
  c4 NUMBER
)

Element 8

This is a variable-length element

55533033

This element contains the owner (schema name) of the object that is being modified. In this example the schema name is US03.

Element 9

This is a variable-length element

54323030

This element contains the object name of the object that is being modified. In this example the object name is T200.

Element 10

This fixed length element contains 20 bytes

00000000 01000000 00000000 01000100 00000200

The contents of this element are not currently understood. They appear to be constant for a specific command type.

Bytes # Bytes Description Value
0-3 4 Unknown flags 0x00000000
4-7 4 Command Type 0x00000001
8-11 4 Unknown flags 0x00000000
12-13 2 Unknown 0x0001
14-15 2 Unknown 0x0001
16-17 2 Unknown 0x0000
18-19 2 Unknown 0x0002

Bytes 0-3 - usually 0x00000000 - can be 0x00000003 if the DDL statement is executed within a PL/SQL block.

Bytes 4-7 - The command is the internal action number of the DDL command. An almost complete list of commands can be found in the AUDIT_ACTIONS table:

SQL> SELECT name FROM audit_actions WHERE action = 1;

NAME
----------------------------
CREATE TABLE

Bytes 8-11 appear to be dependent on the command type. Examples observed so far include:

Bytes 8-11 Command
00000000 CREATE TABLE
00000000 ALTER TABLE
00000020 DROP TABLE
00000000 CREATE INDEX
00000000 DROP INDEX
00008000 CREATE VIEW
00008000 DROP VIEW
00008000 CREATE TRIGGER
00008000 ALTER TRIGGER
00008000 DROP TRIGGER

Bytes 12-15 appear to be dependent on the command type. Examples observed so far include:

Bytes 12-15 Command
00010001 CREATE TABLE
00010001 ALTER TABLE
00000001 DROP TABLE
00000001 CREATE INDEX
00000001 DROP INDEX
00000001 CREATE VIEW
00000000 DROP VIEW
00000001 CREATE TRIGGER
00000001 ALTER TRIGGER
00000001 DROP TRIGGER

Bytes 16-17 are also dependent on the command type. Examples observed so far:

Bytes 16-17 Command
0000 CREATE TABLE
0000 ALTER TABLE
0000 DROP TABLE
0000 CREATE INDEX
0000 DROP INDEX
FFFF CREATE VIEW
FFFF DROP VIEW
0000 CREATE TRIGGER
0000 ALTER TRIGGER
0000 DROP TRIGGER

Bytes 18-19 are also dependent on the command type. Examples observed so far:

Bytes 18-19 Command
0002 CREATE TABLE
0002 ALTER TABLE
0002 DROP TABLE
0001 CREATE INDEX
0001 DROP INDEX
B404 CREATE VIEW
B404 DROP VIEW
850C CREATE TRIGGER
850C ALTER TRIGGER
850C DROP TRIGGER

Element 11

This fixed length element contains 4 bytes

392D0100

0x00012d39 = 77113

The object id can be determined from DBA_OBJECTS. For example:

SQL> SELECT object_id FROM dba_objects
  2  WHERE owner = 'US03'
  3  AND object_name = 'T200'
  4  AND object_type = 'TABLE';

 OBJECT_ID
----------
     77113

Testing (using TRUNCATE) confirms that this value is not the data object id.

The element can be empty for some command types.

Element 12

This fixed length element contains 2 bytes

0100

The significance of this column is not known. So far the only observed value is 0x01 (1)

The element can be empty for some command types.

Element 13

This fixed length element contains 2 bytes

0000

The significance of this column is not known. So far the only observed value is 0x00 (0)

For synonyms this element is empty.

Element 14

This is a variable length element. In this example this element is empty

For some command types this element is the edition, for example ORA$BASE which is the default edition.

Bytes 16-17 Command
NULL CREATE TABLE
NULL ALTER TABLE
NULL DROP TABLE
NULL CREATE INDEX
NULL DROP INDEX
ORA$BASE CREATE VIEW
ORA$BASE CREATE SYNONYM

Element 15

This is a variable-length element

2E2C

This is the value for the NLS_NUMERIC_CHARACTERS parameter. In this example:

.,

Element 16

This is a variable-length element

44442D4D 4F4E2D52 52

This is the value for the NLS_DATE_FORMAT parameter. In this example:

DD-MON-RR

Element 17

This is a variable-length element
44442D4D 4F4E2D52 52204848 2E4D492E 53535846 4620414D

This is the value for the NLS_TIMESTAMP_FORMAT parameter. In this example:

DD-MON-RR HH.MI.SSXFF AM

Element 18

This is a variable-length element

48482E4D 492E5353 58464620 414D

This is the value for the NLS_TIME_FORMAT parameter. In this example:

HH.MI.SSXFF AM

Element 19

This is a variable-length element

48482E4D 492E5353 58464620 414D2054 5A52

This is the value for the NLS_TIME_TZ_FORMAT parameter. In this example:

HH.MI.SSXFF AM TZR

Element 20

This is a variable-length element

44442D4D 4F4E2D52 52204848 2E4D492E 53535846 4620414D 20545A52

This is the value for the NLS_TIMESTAMP_TZ_FORMAT parameter. In this example:

DD-MON-RR HH.MI.SSXFF AM TZR

Element 21

This is a variable-length element

454E474C 495348

This is the value for the NLS _DATE_LANGUAGE parameter. In this example:

ENGLISH

Element 22

This is a variable-length element

414D4552 4943414E

This is the value for the NLS_LANGUAGE parameter. In this example:

AMERICAN

Element 23

This is a variable-length element

47524547 4F524941 4E

This is the value for the NLS_CALENDAR parameter. In this example:

GREGORIAN

Element 24

In this example this element is empty