Oracle useful stuff II

============================================
Index
============================================
Copy large amount amount of data from TABLE_A to TABLE_B
Select from CLOB

===================================================
Copy large amount amount of data from TABLE_A to TABLE_B
===================================================
For example:

CREATE TABLE MY_TABLE ...

ALTER TABLE MY_TABLE NOLOGGING;

INSERT /*+ APPEND */ INTO MY_TABLE SELECT * FROM OTHER_TABLE;

COMMIT;

ALTER TABLE MY_TABLE LOGGING;

BEGIN  
  DBMS_STATS.gather_table_stats('MY_USER', 'MY_TABLE', method_opt => 'FOR ALL COLUMNS SIZE 254');

END;

NOLOGGING => No archiving is taking place.
/*+ APPEND */ => Data is written via direct-path into db blocks.


Table Mode    Insert Mode     ArchiveLog mode      Result
-----------   -------------   ------------------   --------------
LOGGING       APPEND          ARCHIVE LOG          redo generated
NOLOGGING     APPEND          ARCHIVE LOG          no redo
LOGGING       no append       ARCHIVE LOG          redo generated
NOLOGGING     no append       ARCHIVE LOG          redo generated
LOGGING       APPEND          NOARCHIVE LOG mode   no redo
NOLOGGING     APPEND          NOARCHIVE LOG mode   no redo
LOGGING       no append       NOARCHIVE LOG mode   redo generated
NOLOGGING     no append       NOARCHIVE LOG mode   redo generated


Logging versus Nologging Reference 



Direct-Path Insert
The APPEND hint tells the optimizer to perform a direct-path insert, which improves the performance of INSERT .. SELECT for a number of reasons:

- Data is appended to the end of the table.
- Data is written directly to the data files, without using the buffer cache.
- Referential integrity constraints are not considered.
- No trigger processing is performed.

============================================
Select text from CLOB
============================================
SELECT TO_CHAR(clob_column) FROM ...

SELECT TO_CHAR(DBMS_LOB.substr(HIST_SQLTEXT.sql_text,4000,1)) as sql_text
FROM...

Where 4000 is length and 1 is offset.

============================================
Handle ORA-00054
============================================

When issued a DDL command, such as ALTER TABLE SHRINK, an error ORA-00054 might be thrown:
ORA-00054 resource busy and acquire with NOWAIT specified

ALTER TABLE MY_TABLE ENABLE ROW MOVEMENT;
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

The solution, new from Oracle 11g is to increase the DDL_LOCK_TIMEOUT parameter.
It tels Oracle that the DDL would wait for the object to become available.

SQL> ALTER SESSION SET DDL_LOCK_TIMEOUT = 600;
Session altered.
SQL> ALTER TABLE MY_TABLE ENABLE ROW MOVEMENT;

Table altered.

============================================
Update many columns at once

============================================
UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...

[WHERE conditions];

Half Automated procedure for Migration from Oracle EE to Oracle SE

Here are the steps to convert from Oracle Enterprise Edition to Oracle Standard Edition.

Step 0 - Take an export, using expdp, from Enterprise Edition Schema.


Step 1 - Before import: disable constraints, disable triggers, drop sequences.

@before_import.sql

before_import.sql contents

SET FEEDBACK OFF
SET HEADING OFF
SET TERMOUT OFF 
SET LINESIZE 400
SET PAGESIZE 0
spool disable_triggers.sql
select 'alter trigger '|| trigger_name || ' disable  '  || ';' from user_triggers;
spool off
spool disable_constraints.sql
select 'alter table '|| table_name || ' disable constraint ' || constraint_name || ';' from user_constraints where CONSTRAINT_TYPE='R';
spool off
spool drop_sequences.sql
select 'drop sequence '||sequence_name ||';' from user_sequences;
spool off

@disable_triggers.sql
@disable_constraints.sql
@drop_sequences.sql
EXIT;


Step 2 - Import data and Sequences.

impdp user/pass@orainst parfile=param.prm

param.prm contents

directory=IG_EXP_DIR 
dumpfile=my_file.dmp
logfile=my_file.log
table_exists_action=truncate 
content=data_only
EXCLUDE=TABLE:"IN ('TABLE_A', 'TABLE_A')"

impdp user/pass@igt directory=MY_EXP_DIR dumpfile=my_file.dmp logfile=my_seq.log include=sequence

Step 3 - After import: enable constraints, enable triggers, compile objects.
@after_import.sql

after_import.sql contents

SET FEEDBACK OFF
SET HEADING OFF
SET TERMOUT OFF 
SET LINESIZE 400
SET PAGESIZE 0
spool enable_triggers.sql
select 'alter trigger '|| trigger_name || ' enable  '  || ';' from user_triggers;
spool off
spool enable_constraints.sql
select 'alter table '|| table_name || ' enable constraint ' || constraint_name || ';' from user_constraints where CONSTRAINT_TYPE='R';
spool off

@enable_triggers.sql
@enable_constraints.sql


SET FEEDBACK OFF
SET HEADING OFF
SET TERMOUT OFF 
SET LINESIZE 400
SET PAGESIZE 0

spool compile_objects.sql
SELECT object_type, 'ALTER '||
       DECODE(object_type,'PACKAGE BODY','PACKAGE',object_type)||
       ' ' ||owner||'.'||object_name||' COMPILE '||
       decode(object_type,'PACKAGE BODY','BODY','')||';' "ALTER ... COMPILE"
FROM DBA_OBJECTS
WHERE status='INVALID'
  AND object_type NOT IN('JAVA CLASS','JAVA SOURCE')
  AND (object_type != 'SYNONYM' AND OWNER!='PUBLIC')
  AND owner <> 'SYS'

UNION ALL

SELECT object_type, 'ALTER '||object_type||' "'||owner||'.'||object_name||'" COMPILE; '
FROM  DBA_OBJECTS 
WHERE status='INVALID' 
  AND object_type in('JAVA CLASS','JAVA SOURCE')

UNION ALL

SELECT object_type, 'ALTER ' || OWNER || ' ' || OBJECT_TYPE ||' '||OBJECT_NAME || ' COMPILE;'
FROM DBA_OBJECTS
WHERE STATUS='INVALID'
  AND OBJECT_TYPE='SYNONYM'
  AND owner = 'PUBLIC'
ORDER BY 1,2;

spool off

@compile_objects.sql
EXIT;


Step 4 - Remove temporary files.

rm_temp_files.sh

rm_temp_files content

#!/bin/bash
rm disable_triggers.sql
rm disable_constraints.sql
rm drop_sequences.sql

rm enable_triggers.sql

rm enable_constraints.sql

rm compile_objects.sql

Code Example.Multi Delete.

General
PL/SQL block to DELETE, UPDATE, INSERT many rows to a Table in a Loop.

===================================

DELETE in Loop 

===================================

Save below PL/SQL block into a file named multi_delete.sql.

DECLARE
  v_effected_rows NUMBER;
  v_delete_rows NUMBER;
  v_row_counter NUMBER;
  v_sql_str VARCHAR2(1000);

BEGIN
  v_delete_rows :=10001;
  v_effected_rows := 1; 
  v_row_counter := 0; 

  v_sql_str := 'DELETE FROM MY_TABLE WHERE ROWNUM < '|| v_delete_rows ||' AND my_column_value = '||'''kuku''';
  DBMS_OUTPUT.put_line(v_sql_str); 
  WHILE v_effected_rows > 0 LOOP
   
    EXECUTE IMMEDIATE v_sql_Str;
    v_effected_rows := SQL%rowcount;
    v_row_counter := v_row_counter + v_effected_rows;
    commit;
  END LOOP;   
  commit;
  DBMS_OUTPUT.put_line('Number of Deleted rows: '||v_row_counter); 
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('Error in PL/SQL Block.'|| SUBSTR(SQLERRM, 1, 1000)); 
END ;
/

@multi_delete.sql

The problem with with DBMS_OUTPUT, is that the flash takes place only when PL/SQL block completes.
To log the ongoing updates, need to perform writes to log table.

===================================

DELETE in Loop with with logging to a log table

===================================
Save below PL/SQL block into a file, multi_delete.sql

multi_delete.sql

DECLARE
  v_date VARCHAR2(8);
  v_effected_rows NUMBER;
  v_delete_rows NUMBER;
  v_row_counter NUMBER;
  v_table_name VARCHAR2(30);
  v_sql_str VARCHAR2(1000);
  v_module_name VARCHAR2(30);
  v_msg_text VARCHAR2(1000);

BEGIN
  v_table_name :='MY_TABLE';
  v_date := '20140101';
  v_delete_rows :=1001;
  v_effected_rows := 1; 
  v_row_counter := 0;
  v_module_name := 'MULTI DELETE';

  v_sql_str := 'DELETE FROM '||v_table_name||' WHERE ROWNUM < '|| v_delete_rows ||' AND last_analyzed < TO_DATE('||v_date||','||'''YYYYMMDD'''||')';
  v_msg_text  := 'MANUAL_DELETE FROM '||v_table_name|| '. Running SQL: '||v_sql_str;
  --INSERT INTO MY_LOG (PROCEDURE_NAME, DATA, UPDATE_DATE) 
  --VALUES(v_module_name,v_msg_text, SYSDATE);
  writeTrace(v_module_name,v_msg_text);

  WHILE v_effected_rows > 0 LOOP
    EXECUTE IMMEDIATE v_sql_str;
    v_effected_rows := SQL%ROWCOUNT;
    v_row_counter := v_row_counter + v_effected_rows;
    v_msg_text := v_row_counter||' rows deleted from table '||v_table_name;
    --INSERT INTO MY_LOG (PROCEDURE_NAME ,DATA, UPDATE_DATE) 
    --VALUES(v_module_name, v_msg_text, SYSDATE);
    writeTrace(v_module_name,v_msg_text);

    commit;
  END LOOP; 
  
  v_msg_text := 'MANUAL_DELETE FROM '||v_table_name||' Finished Successfully';
  --INSERT INTO MY_LOG (PROCEDURE_NAME ,DATA, UPDATE_DATE) 
  --VALUES(v_module_name, v_msg_text, SYSDATE);
  writeTrace(v_module_name,v_msg_text);
  commit;

EXCEPTION
  WHEN OTHERS THEN
    v_msg_text := 'Error in procedure '||v_module_name||'. Error Details: '|| SUBSTR(SQLERRM, 1, 900); 
    writeTrace(v_module_name,v_msg_text);
    DBMS_OUTPUT.put_line(v_msg_text ); 
END ;
/


To run the SQL:

@multi_delete.sql

===================================

Update, and if no data found do INSERT

===================================

Example of doing UPDATE, and if no data found then doing INSERT.

DECLARE    
  v_customerId    CUSTOMER_GMT_OFFSET.customer_id%TYPE;
  v_gmtOffset     CUSTOMER_GMT_OFFSET.gmt_offset%TYPE;

BEGIN
  -- update record in the table
  UPDATE MY_TABLE
     SET column_A = 'A'
  WHERE column_B = 'B';
    
    -- In case no data found
    IF (SQL%ROWCOUNT = 0) THEN
      INSERT INTO MY_TABLE (column_A, column_b)
             VALUES ('A', 'B');
    END IF;
    writeTrace('updateOrInsert', 'MY_TABLE was updated');
    
  EXCEPTION
    WHEN OTHERS THEN    
      writeTrace('updateOrInsert',
                     'Error updating MY_TABLE '||' ; ERROR: '||SQLERRM);
  END;


writeTrace() performs logging to a log table.

==========================================
Multi Delete using BULK COLLECT and FORALL
==========================================
1. MULTI_DELETE Procedure
-------------------------------------------------------------
-- Input Parameters:
-- param1: How many days back to purge
-- param2: How many rows to commit
-------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY ADMIN_UTIL_PKG IS

  PROCEDURE MULTI_DELETE(pDaysToKeep IN NUMBER, pRowsToCommit IN NUMBER) IS
  -------------------------------------------------------------
  -- Propose : Procedure to delete the old record from TABLE MY_TALE.
  -- Input Parameters:
  -- param1: How many days back to purge
  -- param2: the size of a bulk to delete, default is 10,000
  -------------------------------------------------------------
    v_date_to_check        DATE := SYSDATE - pDaysToKeep;
    v_counter              NUMBER(12):=0;
    v_str                  VARCHAR2(100):='';
    v_bulk                 NUMBER(10):=0;
    v_module_name          VARCHAR2(30) := 'MULTI_DELETE';
    v_msg_str              VARCHAR2(1000);

    CURSOR purge_population is
    SELECT customer_id FROM CUSTOMER
    WHERE (effective_date <= v_date_to_check);

    TYPE aat_key1 IS TABLE OF CUSTOMER.customer_id%TYPE INDEX BY PLS_INTEGER;

    aat_key1s aat_key1;

    BEGIN
       v_msg_str := 'Procedure Started at: '||TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS');
       UTIL_PKG.write_log(v_module_name,v_msg_str);
       v_msg_str := 'Parameters: days_to_keep: '||pDaysToKeep||' rows_to_commit: '||pRowsToCommit;       
       UTIL_PKG.write_log(v_module_name,v_msg_str);
       
       v_str:='DELETE from CUSTOMER where customer_id=:1';

       OPEN purge_population;
       LOOP
       BEGIN
        FETCH purge_population BULK COLLECT INTO aat_key1s limit pRowsToCommit;

        FORALL i in 1..aat_key1s.count SAVE EXCEPTIONS

          EXECUTE IMMEDIATE v_str USING aat_key1s(i);
          v_bulk:=v_bulk+1;
          v_counter := v_counter + aat_key1s.count;

          COMMIT;
          UTIL_PKG.write_log(v_module_name,'current bulk: '||to_char(v_bulk));
          EXCEPTION
               WHEN others THEN
                  COMMIT;
                  UTIL_PKG.write_log(v_module_name,'# of bulks: '||to_char(v_bulk));
                  UTIL_PKG.write_log(v_module_name,'# of rows deleted: ' || TO_CHAR(v_counter));
                  UTIL_PKG.write_log(v_module_name,'Procedure Failed with Error: '||SQLCODE||' '||SQLERRM);
                  RAISE_APPLICATION_ERROR(-20000,v_module_name||' '||SQLCODE||' '||SQLERRM);
         END;
         EXIT WHEN purge_population%NOTFOUND;
      END LOOP;
      CLOSE purge_population;
      COMMIT;
      UTIL_PKG.write_log(v_module_name,'# of bulks: '||to_char(v_bulk));
      UTIL_PKG.write_log(v_module_name,'# of rows deleted: ' || TO_CHAR(v_counter));
      UTIL_PKG.write_log(v_module_name,'Procedure Ended: ' || TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));

    EXCEPTION
      WHEN others then
         RAISE_APPLICATION_ERROR(-20000,'SGA_W_PSMS_SUBSCRIBER ' || SQLCODE || ' ' || SQLERRM);
    END SGA_SUBSCRIBER_PURGE;

END ADMIN_UTIL_PKG;

2. MULTI_DELETE Procedure job

DECLARE

   v_job_number NUMBER(10);

BEGIN

 DBMS_JOB.SUBMIT (JOB => v_job_number, 

                  WHAT => 'ADMIN_UTIL_PKG.multi_delete(365,10000);', 

                  NEXT_DATE => TRUNC(SYSDATE + 1)+4/24, 

                  INTERVAL => 'TRUNC(SYSDATE + 1)+4/24'

 );

 COMMIT;

END;

/

3. MULTI_DELETE Utility write_log procedure.
PROCEDURE WRITE_LOG(p_procedure_name IN VARCHAR2,
                p_msg            IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO TRACE_LOG(PROCEDURE_NAME, DATA, TS_LAST_MODIFIED)
  VALUES(p_procedure_name, p_msg, SYSDATE);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END WRITE_LOG;

4. Log table definition

CREATE TABLE TRACE_LOG

(

  procedure_name   VARCHAR2(100) NOT NULL,

  data             VARCHAR2(4000) NOT NULL,

   ts_last_modified DATE NOT NULL

)

TABLESPACE MY_TBS

  (

    initial 1M

    next 1M

    minextents 1

    maxextents unlimited

  );

Multi Delete with Bulk Collect and FORALL

MULTI_DELETE_WITH_BULK PROCEDURE IS

  v_last_day_to_run DATE;

  TYPE DATE_S IS TABLE OF MY_TABLE.start_date%TYPE;

  date_to_delete DATE_S;

  CURSOR to_delete IS

  SELECT trunc(start_date)

    FROM MY_TABLE

   WHERE TRUNV(start_date) < v_last_day_to_run;

BEGIN

  DATE v_last_day_to_run := TRUNC(sysdate);

  OPEN to_delete;

  LOOP

    BEGIN

      FETCH to_delete BULK COLLECT INTO date_to_delete LIMIT 50000;

      FORALL i IN 1 .. date_to_delete.COUNT

      DELETE FROM MY_TABLE MY_TABLE

       WHERE TRUNC(MY_TABLE.start_date) = date_to_delete(i);

      COMMIT;

      EXIT WHEN to_delete%NOTFOUND;

    END;

  END LOOP;

  COMMIT;

  CLOSE to_delete;

===================================

Anonymous block, Delete in Loop 

===================================

--==============================

--Pre execution steps:

--==============================

CREATE TABLE TEMP_SUBS_TO_DELETE AS SELECT current_vlr, imsi, msisdn, is_subscriber FROM SGA_W_PSMS_SUBSCRIBER WHERE 1=2;

INSERT  /*+ APPEND */  INTO TEMP_SUBS_TO_DELETE (current_vlr, imsi, msisdn,is_subscriber)

SELECT current_vlr, imsi, msisdn, 0 FROM SGA_W_PSMS_SUBSCRIBER WHERE current_vlr LIKE '1%';

commit;

--==============================
-- actual delete

--==============================

DECLARE

  v_rows_handled NUMBER;

  v_error_msg        VARCHAR2(1000);

  v_error_code       NUMBER;  

BEGIN

  v_rows_handled := 1;

  WHILE v_rows_handled > 0 LOOP

    UPDATE TEMP_SUBS_TO_DELETE SET is_subscriber = 1 WHERE is_subscriber = 0 AND ROWNUM < 10001;

    commit;

    DELETE FROM SGA_W_PSMS_SUBSCRIBER_TEMP WHERE SGA_W_PSMS_SUBSCRIBER_TEMP.msisdn IN (SELECT DISTINCT msisdn FROM TEMP_SUBS_TO_DELETE WHERE is_subscriber = 1);

    commit;

    UPDATE TEMP_SUBS_TO_DELETE SET is_subscriber = 2 WHERE is_subscriber=1;

    v_rows_handled := SQL%ROWCOUNT;

  commit;

  

  INSERT INTO SGA_W_LOG(procedure_name, data, ts_last_modified)

  VALUES ('MANUAL_DELETE_FROM_SUBS', 'Rows Deleted: '||TO_CHAR(v_rows_handled),SYSDATE);

  commit;

  END LOOP;

EXCEPTION

  WHEN OTHERS THEN

    v_error_code := SQLCODE;

     v_error_msg := SUBSTR(SQLERRM,1,1000);

    INSERT INTO SGA_W_LOG(procedure_name, data, ts_last_modified)

    VALUES ('MANUAL_DELETE_FROM_SUBS', 'Unexpected Error: '||v_error_msg, SYSDATE);

    commit;  

END;  

/

--==============================

--Post execution steps:

DROP TABLE TEMP_SUBS_TO_DELETE;

--==============================