SQL Query to Check Last Modified Time for an Oracle Object

SQL Query

select 	to_char(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') 
from 		dba_objects 
where 	owner='&user' 
and 		object_name='&Object_name';

Demo

SQL> 	select 	to_char(last_ddl_time,'DD-MON-YYYY HH24:MI:SS')
from 		dba_objects
where 	owner='&user'
and 		object_name='&Object_name'
SQL> /
Enter value for user: TEST
old   3: where 	owner='&user'
new   3: where 	owner='TEST'
Enter value for object_name: ROLE
old   4: and 		object_name='&Object_name'
new   4: and 		object_name='ROLE'

TO_CHAR(LAST_DDL_TIME,'DD-MON
-----------------------------
27-SEP-2019 07:27:21

advertisements

 

SELECT OBJECT_NAME, 
to_char(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') ,
OBJECT_TYPE FROM ALL_OBJECTS 
WHERE OWNER='TEST'
SQL> /

OBJECT_NAME		       TO_CHAR(LAST_DDL_TIME,'DD-MON OBJECT_TYPE
------------------------------ ----------------------------- -----------------------
EMPLOYEE		       27-SEP-2019 07:27:21	     TABLE
DEPARTMENT		       27-SEP-2019 07:27:21	     TABLE
SALARYGRADE		       27-SEP-2019 07:27:21	     TABLE
BONUS			       27-SEP-2019 07:27:21	     TABLE
PROJECT 		       27-SEP-2019 07:27:21	     TABLE
PROJECT_PARTICIPATION	27-SEP-2019 07:27:21	     TABLE
ROLE			       27-SEP-2019 07:27:21	     TABLE
EMP_PK			       27-SEP-2019 07:27:21	     INDEX
DEPT_PK 		       27-SEP-2019 07:27:21	     INDEX
SALGRADE_PK		       27-SEP-2019 07:27:21	     INDEX
BONUS_PK		       27-SEP-2019 07:27:21	     INDEX
PROJECT_PK		       27-SEP-2019 07:27:21	     INDEX
PARTICIPATION_PK	       27-SEP-2019 07:27:21	     INDEX
ROLE_PK 		       27-SEP-2019 07:27:21	     INDEX
FUN_TEST		       01-OCT-2019 08:31:41	     FUNCTION

15 rows selected.

TO_CHAR(LAST_DDL_TIME,'DD-MON
-----------------------------
27-SEP-2019 07:27:21