Truncate and data_object_id explained

Shows a way to find if table was truncated, using object_id, data_object_id one can tell possibly if the table was truncated if the object_id, data_object_id is being monitored.

SQL> create table x ( x number );

Table created.

-- shows object_id, data_object_id is the same when table is initially created
SQL> select object_id, data_object_id, last_ddl_time, created from user_objects where object_name = 'X';

OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIME CREATED
———- ————– ——————– ——————–
82672 82672 08-APR-2011 19:54:51 08-APR-2011 19:54:51

-- truncating table
SQL> truncate table x;

Table truncated.

-- table was truncated but as it has 0 rows the object_id, data_object_id and last_ddl_time did not change
SQL> select object_id, data_object_id, last_ddl_time, created from user_objects where object_name = 'X';

OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIME CREATED
———- ————– ——————– ——————–
82672 82672 08-APR-2011 19:54:51 08-APR-2011 19:54:51

-- insert a row in the table
SQL> insert into x values ( 1 );

1 row created.

SQL> commit;

Commit complete.

SQL> select count(1) from x;

COUNT(1)
———-
1

-- Show object id and data_object_id
SQL> select object_id, data_object_id, last_ddl_time, created from user_objects where object_name = 'X';

OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIME CREATED
———- ————– ——————– ——————–
82672 82672 08-APR-2011 19:54:51 08-APR-2011 19:54:51

-- Truncate table as this point the data_object_id and last_ddl_time will change on the table
SQL> truncate table x;

Table truncated.

-- Shows data_object_id has changed as before it was 82672 and after truncate it changed
SQL> select object_id, data_object_id, last_ddl_time, created from user_objects where object_name = 'X';

OBJECT_ID DATA_OBJECT_ID LAST_DDL_TIME CREATED
———- ————– ——————– ——————–
82672 82673 08-APR-2011 19:57:48 08-APR-2011 19:54:51

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s