Categories: Oracle

Oracle Milliseconds to Date

Nov 28, 2016 1 min.

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

Oracle: Statistics Update

Mar 30, 2016 1 min.

Get Last statistics Update SELECTtable_name,last_analyzed,sample_size,num_rowssample_pctFROMdba_tablesWHEREowner='your_schema_name_here'ORDERBYlast_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) executeDBMS_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) EXECDBMS_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 Invalid Objects

Jan 20, 2016 1 min.

Get List of Invalid Objects Following query can be used to get the list of Invalid objects. SELECTobject_type,object_nameFROMuser_objectsWHERESTATUS!='VALID'ORDERBYobject_type;or SELECTowner,object_type,object_nameFROMdba_objectsWHERESTATUS!='VALID'ORDERBYowner,object_type;Compile Invalid Objects Following SQL block can be used for compiling invalid objects DECLAREsql_statementvarchar2(500);BEGINdbms_output.put_line(chr(0));dbms_output.put_line('Re-compilation of Invalid Objects');dbms_output.put_line('---------------------------------');dbms_output.put_line(chr(0));FORinvalidin(SELECTobject_type,object_nameFROMuser_objectsoWHEREo.status='INVALID'ANDo.object_typeIN('PACKAGE','PACKAGE BODY','TRIGGER','VIEW','MATERIALIZED VIEW','PROCEDURE')ORDERBYo.object_type)LOOPIFinvalid.object_type='PACKAGE BODY'THENsql_statement:='alter package '||invalid.object_name||' compile body';ELSEsql_statement:='alter '||invalid.object_type||' '||invalid.object_name||' compile';ENDIF;BEGINEXECUTEIMMEDIATEsql_statement;dbms_output.put_line(rpad(initcap(invalid.object_type)||' '||invalid.object_name,32)||' : compiled');EXCEPTIONWHENOTHERSTHENdbms_output.put_line(sqlerrm);END;ENDLOOP;END;/or SETheadingoff;SETfeedbackoff;SETechooff;SETlines999;SPOOLrun_invalid.sqlSELECT'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;'FROMdba_objectsWHERESTATUS='INVALID'ANDobject_typeIN('PACKAGE','FUNCTION','PROCEDURE');SPOOLoff;SETheadingon;SETfeedbackon;SETechoon;@run_invalid.sqlor EXECUTL_RECOMP.recomp_serial('schema name');