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”
Following Query to Solved Your Problem :
select * from all_tab_modifications
WHERE TABLE_OWNER = [user_name]
AND TIMESTAMP > ’01-JUN-2012′;
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