Categories: SQL

Oracle Milliseconds to Date

Nov 28, 2016 1 min.

Seconds to Date: SELECT to_timestamp('1970-01-01','yyyy-mm-dd') + numtodsinterval(<<seconds>>, 'SECOND') FROM dual; e.g. SELECT to_timestamp('1970-01-01','yyyy-mm-dd') + numtodsinterval(1480340561, 'SECOND') FROM dual; Milliseconds to Date: Just divide the millseconds my 1000 so it will become seconds and apply the same query as above. SELECT to_timestamp('1970-01-01','yyyy-mm-dd') + numtodsinterval(<<milliseconds>>/1000, 'SECOND') FROM dual; e.g. SELECT to_timestamp('1970-01-01','yyyy-mm-dd') + numtodsinterval(1480340490930/1000, 'SECOND') FROM dual;

Oracle: Statistics Update

Mar 30, 2016 1 min.

Get Last statistics Update SELECT table_name, last_analyzed, sample_size, num_rows sample_pct FROM dba_tables WHERE owner = 'your_schema_name_here' ORDER BY last_analyzed; Note: last_analyzed column shows the last analyzed date sample_size and sample_pct should be same (or difference should be very less) Statistics Update (for schema) execute DBMS_STATS.GATHER_SCHEMA_STATS( OWNNAME=> 'your_schema_name_here', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT=> 'FOR ALL INDEXED COLUMNS', DEGREE => 8, CASCADE=> TRUE ); Statistics Update (for table) EXEC DBMS_STATS.GATHER_TABLE_STATS( 'your_schema_name_here', 'your_table_name_here', estimate_percent=>'', method_opt => 'for all indexed columns size auto', DEGREE => 8, CASCADE => TRUE );

Oracle: Get list of connected users

Mar 30, 2016 1 min.

Following query returns the list of users who are currently connected to the Oracle database SELECT username, osuser, terminal, utl_inaddr.get_host_address(terminal) ip_address FROM system.v$session WHERE username is not null ORDER BY username, osuser;

Oracle Invalid Objects

Jan 20, 2016 1 min.

Get List of Invalid Objects Following query can be used to get the list of Invalid objects. SELECT object_type, object_name FROM user_objects WHERE STATUS != 'VALID' ORDER BY object_type; or SELECT owner, object_type, object_name FROM dba_objects WHERE STATUS != 'VALID' ORDER BY owner, object_type; Compile Invalid Objects Following SQL block can be used for compiling invalid objects DECLARE sql_statement varchar2(500); BEGIN dbms_output.put_line(chr(0)); dbms_output.put_line('Re-compilation of Invalid Objects'); dbms_output.put_line('---------------------------------'); dbms_output.put_line(chr(0)); FOR invalid in (SELECT object_type, object_name FROM user_objects o WHERE o.