Dev Stack Source

Answer : UPDATE: What Worked... So after reading the link from @Raj and reading @ora-600's answer I tried to validate the database with the RMAN command backup check logical validate database; . While this worked fine, it was also clear that it was not looking at everything that the ANALYZE INDEX command would. After trying many different variations, I finally discovered that this command would work: ANALYZE TABLE .. VALIDATE STRUCTURE CASCADE offline; Yep, just switching to OFFLINE appears to fix it. And that eventually led me to this bug# 5752105 on this page: http://www.eygle.com/case/10204_buglist.htm. I am not in a position to prove it right now (cannot apply any patches for the time being), but I strongly suspect that this is what I was running into. So while the question is not fully answered, I am going to mark @ora-600's very helpful answer as correct so that he can collect Paul White's very generous bounty. I think the article Raj quoted (https:...

Answer : This question is pretty much the inverse of Convert Unixtime to Datetime SQL (Oracle) As Justin Cave says: There are no built-in functions. But it's relatively easy to write one. Since a Unix timestamp is the number of seconds since January 1, 1970 As subtracting one date from another date results in the number of days between them you can do something like: create or replace function date_to_unix_ts( PDate in date ) return number is l_unix_ts number; begin l_unix_ts := ( PDate - date '1970-01-01' ) * 60 * 60 * 24; return l_unix_ts; end; As its in seconds since 1970 the number of fractional seconds is immaterial. You can still call it with a timestamp data-type though... SQL> select date_to_unix_ts(systimestamp) from dual; DATE_TO_UNIX_TS(SYSTIMESTAMP) ----------------------------- 1345801660 In response to your comment, I'm sorry but I don't see that behaviour: SQL> with the_dates as ( 2 select to_date('...

Answer : The IF NOT EXISTS syntax available in SQL Server, is not available in Oracle. In general, Oracle scripts simply execute the CREATE statement, and if the object already exist, you'll get an error indicating that, which you can ignore. This is what all the standard Oracle deployment scripts do. However, if you really want to check for existence, and only execute if object doesn't exist, thereby avoiding the error, you can code a PL/SQL block. Write a SQL that checks for user existence, and if it doesn't exist, use EXECUTE IMMEDIATE to do CREATE USER from the PL/SQL block. An example of such a PL/SQL block might be: declare userexist integer; begin select count(*) into userexist from dba_users where username='SMITH'; if (userexist = 0) then execute immediate 'create user smith identified by smith'; end if; end; / You need to write a pl/sql block. See an example here You can check if the user exists in the all_users table using som...

Answer : When you subtract two variables of type TIMESTAMP , you get an INTERVAL DAY TO SECOND which includes a number of milliseconds and/or microseconds depending on the platform. If the database is running on Windows, systimestamp will generally have milliseconds. If the database is running on Unix, systimestamp will generally have microseconds. 1 select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' ) 2* from dual SQL> / SYSTIMESTAMP-TO_TIMESTAMP('2012-07-23','YYYY-MM-DD') --------------------------------------------------------------------------- +000000000 14:51:04.339000000 You can use the EXTRACT function to extract the individual elements of an INTERVAL DAY TO SECOND SQL> ed Wrote file afiedt.buf 1 select extract( day from diff ) days, 2 extract( hour from diff ) hours, 3 extract( minute from diff ) minutes, 4 extract( second from diff ) seconds 5 from (select systimes...