Wednesday, May 01, 2013

How to: when did the given database schema,last refresh using impdp or import or SQL Loader?

 

Option 1:

If schema refresh is carried out by dropping schema then Created column in DBA_USERS will give the answer.

Select username,created from dba_users where username='USR40';

USERNAME                       CREATED
------------------------------          --------------------
USR40                          28-APR-2013 17:28:16

Option 2:

If schema refresh is carried out by dropping the tables, then we can check the created and last_ddl_time in DBA_OBJECTS

SELECT OWNER,OBJECT_NAME,CREATED,LAST_DDL_TIME FROM DBA_OBJECTS WHERE OWNER='USR40'

OWNER                          OBJECT_NAME                              CREATED              LAST_DDL_TIME
------------------------------ ---------------------------------------- -                       ------------------- --------------------
USR40                          XPK_ASE_USR                               28-APR-2013 18:42:09 28-APR-2013 18:42:09
USR40                          XPK_ASE_USR_ACC                       28-APR-2013 18:42:09 28-APR-2013 18:42:09
USR40                          XPK_ASE_USR_ACC_K                   28-APR-2013 18:42:09 28-APR-2013 18:42:10

 

No comments: