Find out when the last insert in a table occured

Unfortunately there isn't a timestamp column which records when the insert statement occured

- basically you need a timestamp column such as lastedateupdate from the above example to get the last insert record in a table. you need to create a column for such and then use either a trigger or set default SYSDATE value for that column.

- even by using ORA_ROWSCN, there is a possibility that you might not getting a confirmed max row. from what i understand, its  depending on how do you specify the maintenance of ORA_ROWSCN during the table creation, at the row level or defaulted at block level (no row dependency). if not specified, it is possible that there is duplicate ORA_ROWSCN for a block of record. i would suggest to verify its uniqueness first.

if you don't have some sort of auditting built in, you can't do what you are trying.

the ora_rowscn  is only an approximation, as the SCN is associated with a transaction, not a specific statement.

also, the data isn't around forever so scn_to_timestamp will fail with "ORA-08181: specified number is not a valid system change number" if old enough

The function ORA_ROWSCN is for oracle 10 not 9i. I
My db is Oracle 9 as stated in the question

another "limited duration" option is flashback queries which are supported in 9i

but the best way will be to write some auditting

Is this a one time deal to see if a table is actually being used or something you need moving forward?

If a one time deal and if you are in archive log mode you might see if log miner will help.

It's a one time deal

Is this to see if a table is actually used?

If so, check the dba_dependicies view first.

It's risky but:
If there aren't any and you are really interested, just rename it and see who complains or what breaks.