Aim:
To determine export start time which is generally not published in export log file.
Situation:
Below is sample of export log.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to APPLSYS
. . exporting table FND_USER 5641 rows exported
Export terminated successfully without warnings.
You can find export finish time by
[oracle@myhost tmp]$ ls -l 1.log
-rw-r--r-- 1 oracle dba 547 Apr 4 16:39 1.log
But how to find old (might be 2 months old) export start time which you require now and don't remember at all.
Solution:
Oracle 10g has a nice feature called Active Session History which stores all the data regarding your old sessions.
By the logfile time stamp you atleast know which day export started and you can guess the start date. In this example my export ended at 4 Apr 2011. Out of which I can determine start date should be 4 only (if export is bigger, it might be 3 Apr)
Use your anticiated date below in the query
select sid,sample_time,session_type,event,program,action,module from V$ACTIVE_SESSION_HISTORY where EXTRACT(day FROM sample_time)=&dt and module like '%exp%';
Enter only day(number) when asked, in this case only enter 4 (for our example).
It will list all the sessions which ran export. See the example xls attached.
** Only thing make sure is all SIDs are same since active session history is watching session at a period of time called sample_time so you will find multiple rows in output.
But sort by sample_time and pick the oldest time which is your export start time :)
To determine export start time which is generally not published in export log file.
Situation:
Below is sample of export log.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
Current user changed to APPLSYS
. . exporting table FND_USER 5641 rows exported
Export terminated successfully without warnings.
You can find export finish time by
[oracle@myhost tmp]$ ls -l 1.log
-rw-r--r-- 1 oracle dba 547 Apr 4 16:39 1.log
But how to find old (might be 2 months old) export start time which you require now and don't remember at all.
Solution:
Oracle 10g has a nice feature called Active Session History which stores all the data regarding your old sessions.
By the logfile time stamp you atleast know which day export started and you can guess the start date. In this example my export ended at 4 Apr 2011. Out of which I can determine start date should be 4 only (if export is bigger, it might be 3 Apr)
Use your anticiated date below in the query
select sid,sample_time,session_type,event,program,action,module from V$ACTIVE_SESSION_HISTORY where EXTRACT(day FROM sample_time)=&dt and module like '%exp%';
Enter only day(number) when asked, in this case only enter 4 (for our example).
It will list all the sessions which ran export. See the example xls attached.
** Only thing make sure is all SIDs are same since active session history is watching session at a period of time called sample_time so you will find multiple rows in output.
But sort by sample_time and pick the oldest time which is your export start time :)
No comments:
Post a Comment