1

I wanted to examine a running Oracle instance on one of our servers, but nobody knows, if there's still any data of interest stored in the database.

Unfortunately our last Oracle admin has left our small company and so I try to figure out, if there's still any valuable data left.

But I can't really distinguish between system and user content, i.e. what is an example table/database and which tables where created by our ex employee?

I searched for log files but I wasn't really successful, so I thought about using a select about the last 100 (or more) updated that where made in the tables, so that I maybe get a clue what went on in the database.

It's running on Windows2000 and I have the system account.

Thanks for any hints.

  • 4
    Switch it off. Wait for a while. If no one complains, it's no longer valuable. – Michael Green Apr 21 '15 at 11:25
  • Oracle does not insert rows in any particular order, and rows are inserted based on the next free block in the table’s tablespace. – Md Haidar Ali Khan Apr 21 '15 at 11:33
  • The only way to find the last row is to have a date or timestamp field that is populated by sysdate at the time of insertion and select the max value of that column. – Md Haidar Ali Khan Apr 21 '15 at 11:35
4

You can identify most recently created users/schemas from dba_users view

select *
from dba_users
order by created desc;

When you know what schemas to look out for, you can list the most recently created tables with

select *
from dba_objects
where owner in ('SCHEMA1','SCHEMA2')
  and object_type = 'TABLE'
order by created desc;

For a single table, you can find a rough date when the data was last modified

select scn_to_timestamp(max(ora_rowscn)) 
from schema.table;

Or get a list of rows that were (potentially) modified last

select *
from schema.table
where ora_rowscn = (
  select max(ora_rowscn)
  from schema.table
);

Note that ora_rowscn is stored by default per database block, not per row. There is a limit how old scn values can be mapped back to timestamp.

You should also check the audit trail

select * 
from dba_audit_trail
order by timestamp desc;
| improve this answer | |
  • Great. Thank you very much. That helps! :-D – SYS49152 Apr 21 '15 at 12:23

Not the answer you're looking for? Browse other questions tagged or ask your own question.