PeopleSoft Questions

Please add to this page any PeopleSoft Questions or queries you may have...

=====================================================================================

Q: Ran a SQL script from app des build, insert failed, how can I stop this happening?

Reply:

I suspect your issue here was that the table insert failed, but the script went on to drop the previous table anyway, so you ended up with a new empty table and the prior table was dropped. If you are running Oracle 10g or better, the table may exist in the database "recycle bin". Say for instance PS_NAMES is the affected table, we can find out if the dropped table is in the recycle bin:

select original_name, object_name, droptime
from recyclebin 
where
original_name = 'PS_NAMES'
order by drop time desc

The object name from this query is the recycle bin name for the image of the dropped table (normally begins with "BIN$"). It can be selected as though it was a normal table so the rows can be checked. Once assured the table is there and is correct, it can be restored using:

flashback table tst to before drop

Now we can look at prevention, rather than cure - see this article:

http://springboardsolutions.com/errors_in_sql_plus.htm

=====================================================================================

Q: How do I find out when a database was refreshed?

Reply:

In Oracle, you determine when the database was created with:

select name,created from v$database;

Not a perfect method, but the last day the database was Production will be the last day there was a large amount of activity:

select trunc(audit_stamp),count(*) from psaudit group by trunc(audit_stamp) order by trunc(audit_stamp);