Use insert append hint for fast inserts. Almost as fast as bulk load. alter session enable parallel dml; alter index <INDEX> unusable; INSERT /*+ append parallel (<ALIAS1>) */ INTO <TABLE1> <ALIAS1> ( COL1, COL2 ) SELECT /*+ parallel(ALIAS2) */ COL1, COL2 …
–USE ANALYTIC FUNCTION TO DELETE ALL BUT ORIGINAL delete from <TABLE_NAME> where (key1, ins_timestamp) in (select key1, ins_timestamp from ( select key1, ins_timestamp, upd_timestamp , row_number() over (partition by key1 order by ins_timestamp) as rownumber from <TABLE_NAME> ) t1 where …
SELECT TRIM(REGEXP_REPLACE(<str>,’ +’,’ ‘)) AS compressed_str FROM <TABLE>
http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle –first gather stats for table… begin dbms_stats.gather_table_stats(‘MYSCHEMA’,’MYTABLE’); end; –or schema… begin dbms_stats.gather_table_stats(‘MYSCHEMA’); end; –get table size select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB, num_rows, avg_row_len, last_analyzed from all_tables where owner = ‘<WHOEVER>’ and table_name in (‘<WHATEVER>’) order by MB desc Also …
Issue: Trying to issue an EXECUTE IMMEDIATE command from within a PL/SQL procedure and geting: ORA-01031: insufficient privileges while issuing an EXECUTE IMMEDIATE. The same EXECUTE IMMEDIATE command works from within an anonymous PL/SQL block. The procedure and the anonymous block …
Click to access MV_Refresh_Parallel.pdf
http://log4plsql.sourceforge.net/docs/UserGuideDOC.html
Using DBMS_COMPARISON package: http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_comparison.htm#CHDHEFFJ
http://www.oracle.com/technetwork/indexes/documentation/index.html
http://www.orafaq.com/node/55 http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions004.htm