• Welcome
• Projects
• Presentations
• Research

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

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

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 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 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 12-15 appear to be dependent on the command type. Examples observed so far include:

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

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

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.

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

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