PeopleSoft Questions
From TekiWiki
This whole wiki has now moved to http://www.springboardsolutions.com/wiki
Go to this page PeopleSoft Questions
| Line 1: | Line 1: | ||
| - | Please add to this page any PeopleSoft Questions or queries you may have... | + | 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? | Q: Ran a SQL script from app des build, insert failed, how can I stop this happening? | ||
| + | |||
Reply: | 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: | ||
| + | <pre> | ||
| + | select original_name, object_name, droptime | ||
| + | from recyclebin | ||
| + | where | ||
| + | original_name = 'PS_NAMES' | ||
| + | order by drop time desc | ||
| + | </pre> | ||
| + | |||
| + | 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: | ||
| + | <pre> | ||
| + | flashback table tst to before drop | ||
| + | </pre> | ||
| + | |||
| + | Now we can look at prevention, rather than cure - see this article: | ||
| + | |||
| + | http://springboardsolutions.com/errors_in_sql_plus.htm | ||
| + | |||
================================================================================================= | ================================================================================================= | ||
| - | Q: | + | Q: How do I find out when a database was refreshed? |
| + | |||
Reply: | Reply: | ||
| + | |||
| + | In Oracle, you determine when the database was created with: | ||
| + | <pre> | ||
| + | select name,created from v$database; | ||
| + | </pre> | ||
| + | |||
| + | Not a perfect method, but the last day the database was Production will be the last day there was a large amount of activity: | ||
| + | <pre> | ||
| + | select trunc(audit_stamp),count(*) from psaudit group by trunc(audit_stamp) order by trunc(audit_stamp); | ||
| + | </pre> | ||
| + | |||
================================================================================================= | ================================================================================================= | ||
