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 anand@test_27 >sho user

USER is “ANAND”

16:31:50 anand@test_27 >

16:31:50 anand@test_27 >

16:31:50 anand@test_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 anand@test_27 >create table abc (id number,name varchar(10));

Table created.

Elapsed: 00:00:00.00

16:32:54 anand@test_27 >col object_name for a10

16:34:07 anand@test_27 >col object_type for a10

16:35:22 anand@test_27 >col created for a20

16:35:34anand@test_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 anand@test_27 >

16:36:40 anand@test_27 >

16:36:40 anand@test_27 >REM LAST DDL TIME IS “19-09-09 16:41:26″

16:36:57 anand@test_27 >

16:36:57 anand@test_27 >REM NOW I ADD A COLUMN TO THE TABLE

16:37:09 anand@test_27 >alter table abc add (DOB date);

Table altered.

Elapsed: 00:00:00.00

16:37:28 anand@test_27 >

16:37:28anand@test_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 anand@test_27 >

16:37:40 anand@test_27 >REM LAST DDL TIME NOW CHANGED TO “19-09-09 16:46:00″

16:37:54 anand@test_27 >desc abc

Name              Null?    Type

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

ID   NUMBER

NAME VARCHAR2(10)

DOB  DATE

16:38:02 anand@test_27 >

16:40:44 anand@test_27 >alter table abc drop column dob;

Table altered.

Elapsed: 00:00:00.00

16:40:49 anand@test_27 >

16:40:49anand@test_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 anand@test_27 >

16:40:52 anand@test_27 >

16:40:52 anand@test_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.

About these ads
    • PRASHANT
    • December 16th, 2009

    CAN I KNOW THE LIST OF COLUMNS WHICH HAVE BEEN ADDED LATER IN THE TABLE ALONG WITH ADDITION DATE

    ?

    • 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

    • Chandresh Mesvaniya
    • June 18th, 2012

    Following Query to Solved Your Problem :

    select * from all_tab_modifications
    WHERE TABLE_OWNER = [user_name]
    AND TIMESTAMP > ’01-JUN-2012′;

  1. No trackbacks yet.

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

Follow

Get every new post delivered to your Inbox.

Join 463 other followers

%d bloggers like this: