Oracle

Last DDL in Oracle

Many a times this question arises “HOW can I know when LAST DDL/DML was done??” So I thought to blog it.In this blog i will be writing only about last DDL.Last DML will be covered in next blog.

Firstly, let’s see what DDL and DML mean in oracle (as per oracle doc)

DDL statements: – These statements create, alter, maintain, and drop schema objects. DDL statements also include statements that permit a user to grant other users the privileges to access the database and specific objects within the database.

DML statements: – These statements manipulate data. For example, querying, inserting, updating, and deleting rows of a table are all DML operations. The most common SQL statement is the select statement, which retrieves data from the database. Locking a table or view and examining the execution plan of a SQL statement are also DML operations.

LAST DDL

16:31:46 [email protected]_27 >sho user

USER is “ANAND”

16:31:50 [email protected]_27 >

16:31:50 [email protected]_27 >

16:31:50 [email protected]_27 >select * from v$version;

BANNER

—————————————————————-

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Prod

PL/SQL Release 10.2.0.4.0 – Production

CORE  10.2.0.4.0        Production

TNS for 32-bit Windows: Version 10.2.0.4.0 – Production

NLSRTL Version 10.2.0.4.0 – Production

Elapsed: 00:00:00.00

16:32:08 [email protected]_27 >create table abc (id number,name varchar(10));

Table created.

Elapsed: 00:00:00.00

16:32:54 [email protected]_27 >col object_name for a10

16:34:07 [email protected]_27 >col object_type for a10

16:35:22 [email protected]_27 >col created for a20

16:35:[email protected]_27>select object_name,object_type,to_char(created,’DD-MM-YY HH24:MI:SS’)created,to_char(last_ddl_time,’DD-MM-YY HH24:MI:SS’)last_ddl from user_objects where object_name=’ABC’;

OBJECT_NAM OBJECT_TYPE  CREATED              LAST_DDL

———- ———- ——————– —————————————————————————

ABC        TABLE       19-09-09 16:41:26    19-09-09 16:41:26

Elapsed: 00:00:00.00

16:35:35 [email protected]_27 >

16:36:40 [email protected]_27 >

16:36:40 [email protected]_27 >REM LAST DDL TIME IS “19-09-09 16:41:26”

16:36:57 [email protected]_27 >

16:36:57 [email protected]_27 >REM NOW I ADD A COLUMN TO THE TABLE

16:37:09 [email protected]_27 >alter table abc add (DOB date);

Table altered.

Elapsed: 00:00:00.00

16:37:28 [email protected]_27 >

16:37:[email protected]_27>select object_name,object_type,to_char(created,’DD-MM-YY HH24:MI:SS’)created,to_char(last_ddl_time,’DD-MM-YY HH24:MI:SS’)last_ddl from user_objects where object_name=’ABC’;

OBJECT_NAM OBJECT_TYP CREATED              LAST_DDL

———- ———- ——————– —————————————————————————

ABC        TABLE      19-09-09 16:41:26    19-09-09 16:46:00

Elapsed: 00:00:00.00

16:37:39 [email protected]_27 >

16:37:40 [email protected]_27 >REM LAST DDL TIME NOW CHANGED TO “19-09-09 16:46:00”

16:37:54 [email protected]_27 >desc abc

Name              Null?    Type

———————————————————————————————————————————————

ID   NUMBER

NAME VARCHAR2(10)

DOB  DATE

16:38:02 [email protected]_27 >

16:40:44 [email protected]_27 >alter table abc drop column dob;

Table altered.

Elapsed: 00:00:00.00

16:40:49 [email protected]_27 >

16:40:[email protected]_27>select object_name,object_type,to_char(created,’DD-MM-YY HH24:MI:SS’)created,to_char(last_ddl_time,’DD-MM-YY HH24:MI:SS’)last_ddl from user_objects where object_name=’ABC’;

OBJECT_NAM OBJECT_TYP CREATED              LAST_DDL

———- ———- ——————– —————————————————————————

ABC        TABLE      19-09-09 16:41:26    19-09-09 16:49:21

Elapsed: 00:00:00.00

16:40:52 [email protected]_27 >

16:40:52 [email protected]_27 >

16:40:52 [email protected]_27 >REM LAST DDL TIME NOW CHANGED TO “19-09-09 16:49:21”

NOTE:- The server time and the my pc’s time is different.

3 thoughts on “Last DDL in Oracle

  1. Following Query to Solved Your Problem :
    select * from all_tab_modifications
    WHERE TABLE_OWNER = [user_name]
    AND TIMESTAMP > ’01-JUN-2012′;

    1. Hi,
      Thanks for visiting the site and hope it helped you.
      To know the list of columns and the date/timestamp you can enable ALTER table audit with audit_trail parameter set to DB, EXTENDED.Extended was introduced in 10g and will populate the SQL_BIND and SQL_TEXT columns.
      Eample:-
      SYS @ oracle >show parameter audit
      NAME TYPE VALUE
      ———————————— ———– ——————————
      audit_file_dest string D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORACLE\ADUMP
      audit_sys_operations boolean FALSE
      audit_trail string DB, EXTENDED
      ANAND @ oracle >create table test (a number);
      SYS @ oracle >audit alter on anand.test by access;
      ANAND @ oracle >alter table test add (b number);
      Table altered.
      ANAND @ oracle >alter table test add (c number);
      Table altered.
      Then you can query
      select USERNAME,TERMINAL,to_char(TIMESTAMP,’DD-MM-YY HH24:MI:SS’)ddl_time,ACTION,ACTION_NAME,SQL_TEXT from DBA_AUDIT_TRAIL;
      USERNAME TERMINAL DDL_TIME ACTION ACTION_NAME SQL_TEXT
      —————————— ———- —————– ———- —————————- ————————————————–
      ANAND ANAND 18-12-09 02:00:40 15 ALTER TABLE alter table test add (b number)
      ANAND ANAND 18-12-09 02:02:16 15 ALTER TABLE alter table test add (c number)
      NOTE:- You need to audit by access, so that if someone alters the same table from the same session you will have different entries (depending on the number of alters) in aud$ table.
      The second, option which is most commonly used is writing a ddl trigger on the table.For reference check the below link
      http://www.morganslibrary.org/reference/ddl_trigger.html
      Regards,
      Anand

Leave a Reply