tag:blogger.com,1999:blog-24686153.post1620274938708758163..comments2024-03-21T05:37:05.904-07:00Comments on Anton Nielsen: AVOID the VOIDAnton Nielsenhttp://www.blogger.com/profile/01507583135719899159noreply@blogger.comBlogger3125tag:blogger.com,1999:blog-24686153.post-11781039113284082432015-09-25T20:56:44.881-07:002015-09-25T20:56:44.881-07:00good as always. Thank you Anton.
AZgood as always. Thank you Anton.<br />AZAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-24686153.post-51342558353080777912015-07-09T23:55:52.186-07:002015-07-09T23:55:52.186-07:00The real problem seems to be that the simple UK do...The real problem seems to be that the simple UK does not match the business requirement (no active emplyoee can have a duplicate SSN).<br /><br />A function based UK would solve the problem (not syntax checked):<br /><br />alter table "EMPLOYEE_NO_DELETE" add constraint "EMPLOYEE_ND_SSN_V_UK" unique (case when VOID = "Y" then null else "SSN" end)<br />/<br />Sven Wellerhttp://svenweller.wordpress.comnoreply@blogger.comtag:blogger.com,1999:blog-24686153.post-52699216050180374512015-04-10T05:45:55.507-07:002015-04-10T05:45:55.507-07:00Flashback Data Archive is the REAL answer here. It...Flashback Data Archive is the REAL answer here. It's as trivial as:<br /><br />Create a tablespace to hold our flashback (you are using ASM and Oracle Managed Files, right?):<br /><br />CREATE TABLESPACE FDA_100_YEAR SIZE 2G AUTOEXTEND ON NEXT 10M; <br /><br />Create a flashback data archive. Note that you can have multiple, this one is being set as the default one.:<br /><br />CREATE FLASHBACK ARCHIVE DEFAULT FLASHBACK_10_YEARS RETENTION 100 YEAR;<br /><br />Grant flashback archive to ANTON:<br /><br />GRANT FLASHBACK ARCHIVE ON FLASHBACK_100_YEARS TO ANTON;<br /><br />Alter the table to use flashback archive. Note that you can assign a specific archive, here, we'll just use the default.:<br /><br />ALTER TABLE ANTON.EMPLOYEE FLASHBACK ARCHIVE;<br /><br />Now you're done. Just use an AS OF | clause or VERSIONS BETWEEN clause in your queries and you've got every change that ever happened, effectively providing a 'No Delete' (at least for 100 years in the above case) table without ANY work needed on your part other than the above.Rich Soulehttps://www.blogger.com/profile/12724306541978087456noreply@blogger.com