Oracle: monitoring long tasks

SELECT start_time, username, target, sofar blocks_read, totalwork total_blocks, round(time_remaining/60) minutes FROM v$session_longops WHERE sofar <> totalwork;

Oracle: remove duplicate records

Tom said: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15258974323143

Git tips from the trenches

https://ochronus.com/git-tips-from-the-trenches/#null

PL/SQL: how to deal with nullable variables in query

where decode(column, variable, 0) is not null

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Find the blocking session port, and then kill the process listening on that port.

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT 
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, 
V$PROCESS P, V$SQL SQ 
WHERE L.OBJECT_ID = O.OBJECT_ID 
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR 
AND S.SQL_ADDRESS = SQ.ADDRESS;

[root@dwh1 ~]# netstat -ap | grep 22735
tcp        1      0 dwh1.prod.mbqt:ncube-lm     dwh1.prod.mbqt:22735        CLOSE_WAIT  19050/oracledwhmbqt 

[root@dwh1 ~]# kill -9 19050

VirtualBox: force guest IP address

> sudo vi /etc/sysconfig/network-scripts/ifcfg-eth0

DEVICE=eth0
HWADDR=08:00:27:B5:56:05
NM_CONTROLLED=no
ONBOOT=yes
BOOTPROTO=static
#IPADDR=192.168.2.8
IPADDR=192.168.1.101
NETMASK=255.255.255.0
TYPE=Ethernet
DNS1=8.8.8.8
DNS2=8.8.4.4

Then restart network

> sudo service network restart

Bulk Processing with BULK COLLECT and FORALL

Oracle magazine article by Steven Feuerstein

/usr/bin/perl^M: bad interpreter

One perlish way to fix this:

>./fm_test.pl
-bash: ./fm_test.pl: /usr/bin/perl^M: bad interpreter: No such file or directory

is:

> perl -pi -e 'tr[\r][]d' ./fm_test.pl

Another way is to use dos2unix

Compute files size

find . -name "*" -ls | awk '{total += $7} END {print total}'

Vim backspace issue

If backspace leaves ^? in vim, this is most probably because of "stty erase ^H" being present either in your .bashrc or .bash_profile.
Remove it and Vim will be happy with backspace.

Oracle: ORA-14402: updating partition key column would cause a partition change

This can be circumvented with:

ALTER TABLE my_table ENABLE ROW MOVEMENT;

CyanogenMod 10.2 on HTC Explorer

• How To Root HTC Explorer Pico and Install CWM Recovery
• CyanogenMod 10.2 Weekly Builds
• Install Jelly Bean 4.3 Based CyanogenMod 10.2 Nightly Build ROM on HTC Explorer/Pico
• How To Increase Your HTC Pico's Internal Memory Using Link2SD

Perl: sort hash values by keys

my @sorted_values = @hash{sort {$a <=> $b} keys %hash};

Oracle: aggregate data from a number of rows into a single row

Base Data:

    DEPTNO ENAME
---------- ----------
        20 SMITH
        30 ALLEN
        30 WARD
        20 JONES
        30 MARTIN
        30 BLAKE
        10 CLARK
        20 SCOTT
        10 KING
        30 TURNER
        20 ADAMS
        30 JAMES
        20 FORD
        10 MILLER

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

jQuery: Have UI datetimepicker 'Now' button use UTC time?

This solution requires an update of the source code:
http://stackoverflow.com/a/17833188/732797

Windows 8: Could'nt upgrade to Windows 8.1

If ever you meet this stupid error in the process of upgrading to Windows 8.1:

"Could'nt update to Windows 8.1.
Sorry, we couldn't complete the update to Windows 8.1
We've restored your previous version of Windows to this PC.
ERROR CODE: 0x80070002 - 0x20009."

This is most likely because the HDD on which Windows has been installed in not the one you booted on.
In this case, change the options in the BIOS in order to boot on the Windows HDD, upgrade, then restore as before.

Oracle: IN vs. EXISTS

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:442029737684

PL SQL: Convert array into SQL table

Use:

SELECT COLUMN_VALUE FROM TABLE(my_array)

Beware to use only SQL array types or you will get the following error a run time (at least with Oracle 11.2.0.1):

ORA-21700: object does not exist or is marked for delete

E.g.

CREATE OR REPLACE TYPE MBQT_DB.ID_ARRAY_TYPE IS TABLE OF VARCHAR2(20);
/

GRANT EXECUTE ON MBQT_DB.ID_ARRAY_TYPE TO PUBLIC;

PL SQL: open ref cursor from explicit cursor

PROCEDURE my_proc(x_ref_cursor OUT sys_refcursor) IS

    TYPE my_array_type IS TABLE of MY_TABLE%ROWTYPE;
    my_array my_array_type;
BEGIN
    OPEN x_ref_cursor FOR SELECT * FROM MY_TABLE;

    FETCH x_ref_cursor BULK COLLECT INTO my_array;

    -- Do something with my_array...
    FORALL i IN 1 .. my_array.COUNT
        INSERT INTO MY_TABLE2 (
            col1,
            col2,
            col3)
        VALUES (
            my_array(i).col1,
            my_array(i).col2,
            my_array(i).col3
        );
END;

Oracle: record vs object

Record version
In package:

TYPE PARAMETER_REC_TYPE IS RECORD (
    PARAMETER_ID CRM_DB.HT_CO_ADDON_PARAMETER.PARAMETER_ID%TYPE,
    PARAMETER_VALUE CRM_DB.HT_CO_ADDON_PARAMETER.PARAMETER_VALUE%TYPE
);
TYPE PARAMETER_ARRAY_TYPE IS TABLE OF PARAMETER_REC_TYPE;

In package body:

set serveroutput on;
declare
    my_param_array PROV_DB.PROV_API.PARAMETER_ARRAY_TYPE;
    my_param_record PROV_DB.PROV_API.PARAMETER_REC_TYPE;
    g_sysdate date := sysdate;
    CURSOR my_rz_addon_cursor(p_co_id CRM_DB.DT_CO.CO_ID%TYPE) is
        SELECT  'RZ_ID[]' PARAMETER_ID,
                HT_CO_ADDON_PARAMETER.PARAMETER_VALUE
        FROM    CRM_DB.HT_CO_ADDON,
                CRM_DB.HT_CO_ADDON_PARAMETER,
                CRM_DB.HT_CO_ADDON_STATUS
        WHERE   HT_CO_ADDON.CO_ID = p_co_id
            AND HT_CO_ADDON.ADDON_ID LIKE 'ROAMING_ZONE_%'
            AND HT_CO_ADDON.FROM_DATE <= g_sysdate AND HT_CO_ADDON.TO_DATE > g_sysdate
            AND HT_CO_ADDON_PARAMETER.CO_ADDON_ID = HT_CO_ADDON.CO_ADDON_ID
            AND HT_CO_ADDON_PARAMETER.FROM_DATE <= g_sysdate AND HT_CO_ADDON_PARAMETER.TO_DATE > g_sysdate
            AND HT_CO_ADDON_STATUS.CO_ADDON_ID = HT_CO_ADDON.CO_ADDON_ID
            AND HT_CO_ADDON_STATUS.STATUS_ID = CRM_DB.CO_API.STATUS_ADDON_ACTIVE
            AND HT_CO_ADDON_STATUS.FROM_DATE <= g_sysdate AND HT_CO_ADDON_STATUS.TO_DATE > g_sysdate;
begin
    OPEN my_rz_addon_cursor('000000000941');
    FETCH my_rz_addon_cursor BULK COLLECT INTO my_param_array;
    CLOSE my_rz_addon_cursor;

    my_param_record.parameter_id := 'TOTO';
    my_param_record.parameter_value := 'coin coin';
    my_param_array.extend;
    my_param_array(my_param_array.count) := my_param_record;

    my_param_record.parameter_id := 'pim';
    my_param_record.parameter_value := '1 2 3';
    my_param_array.extend;
    my_param_array(my_param_array.count) := my_param_record;

    for i in 1..my_param_array.count loop
        dbms_output.put_line(my_param_array(i).parameter_id||' = '||my_param_array(i).parameter_value);
    end loop;
end;

Object version
As PROV_DB user:

create TYPE PARAMETER_OBJ_TYPE AS object (
    PARAMETER_ID varchar2(20),
    PARAMETER_VALUE varchar2(256)
);
/
create TYPE PARAMETER_OBJ_ARRAY_TYPE IS TABLE OF PARAMETER_OBJ_TYPE;
/;

GRANT EXECUTE ON PARAMETER_OBJ_TYPE TO public;
GRANT EXECUTE ON PARAMETER_OBJ_ARRAY_TYPE TO public;

In package body:

set serveroutput on;
declare
    my_param_array PROV_DB.PARAMETER_OBJ_ARRAY_TYPE;
    g_sysdate date := sysdate;
    CURSOR my_rz_addon_cursor(p_co_id CRM_DB.DT_CO.CO_ID%TYPE) is
        SELECT  PROV_DB.PARAMETER_OBJ_TYPE('RZ_ID[]', HT_CO_ADDON_PARAMETER.PARAMETER_VALUE)
        FROM    CRM_DB.HT_CO_ADDON,
                CRM_DB.HT_CO_ADDON_PARAMETER,
                CRM_DB.HT_CO_ADDON_STATUS
        WHERE   HT_CO_ADDON.CO_ID = p_co_id
            AND HT_CO_ADDON.ADDON_ID LIKE 'ROAMING_ZONE_%'
            AND HT_CO_ADDON.FROM_DATE <= g_sysdate AND HT_CO_ADDON.TO_DATE > g_sysdate
            AND HT_CO_ADDON_PARAMETER.CO_ADDON_ID = HT_CO_ADDON.CO_ADDON_ID
            AND HT_CO_ADDON_PARAMETER.FROM_DATE <= g_sysdate AND HT_CO_ADDON_PARAMETER.TO_DATE > g_sysdate
            AND HT_CO_ADDON_STATUS.CO_ADDON_ID = HT_CO_ADDON.CO_ADDON_ID
            AND HT_CO_ADDON_STATUS.STATUS_ID = CRM_DB.CO_API.STATUS_ADDON_ACTIVE
            AND HT_CO_ADDON_STATUS.FROM_DATE <= g_sysdate AND HT_CO_ADDON_STATUS.TO_DATE > g_sysdate;
begin
    OPEN my_rz_addon_cursor('000000000941');
    FETCH my_rz_addon_cursor BULK COLLECT INTO my_param_array;
    CLOSE my_rz_addon_cursor;

    my_param_array.extend;
    my_param_array(my_param_array.count) := PROV_DB.PARAMETER_OBJ_TYPE('TOTO', 'coin coin');
    my_param_array.extend;
    my_param_array(my_param_array.count) := PROV_DB.PARAMETER_OBJ_TYPE('pim', '1 2 3');

    for i in 1..my_param_array.count loop
        dbms_output.put_line(my_param_array(i).parameter_id||' = '||my_param_array(i).parameter_value);
    end loop;
end;