Oracle SQL Developer and JDeveloper : all-resources.dat files are getting too big

On a Windows 7 PC, these files were getting enormous, and causing problems with the size of my roaming profile. Metalink Note ID 1943500.1 gives a solution for SQL Developer, which also seems to work for JDeveloper.

The all-resources.dat file can be found in:

C:\Users\UserName\AppData\Roaming\SQL Developer\system\system_cache\var\cache

and

C:\Users\UserName\AppData\Roaming\JDeveloper\system\system_cache\var\cache

The fix is as follows…

Add the following text to the sqldeveloper.conf file (in the sqldeveloper\bin directory):

AddVMOption -Dorg.netbeans.core.update.all.resources=never

For JDeveloper, add the same line of text to the jdev.conf file (in the jdev\bin directory).

The all-resources.dat files can then be permanently deleted (Shift-Delete) to free up a considerable amount of roaming profile space.

Using SQL PIVOT

The SQL PIVOT command can be used to show the summarised amounts/values of an aggregated query (GROUP BY…) as columns, rather than grouped rows. Here’s an example, in HR, to show total amounts in specific pay elements, per employee/assignment (assignment_id):

SELECT assignment_id
,      NVL(gen,0)         gen
,      NVL(car,0)         car
,      NVL(study,0)       study
,      NVL(study_subs,0)  study_subs
,      NVL(acting,0)      acting
,      NVL(parking,0)     parking
,      NVL(retire,0)      retire
,      NVL(medical,0)     medical
FROM
(
SELECT ee.assignment_id
,      et.element_name
,      SUM(TO_NUMBER(ev.screen_entry_value)) val
FROM   pay_element_types_f_tl      et
,      pay_element_links_f         el
,      pay_element_entries_f       ee
,      pay_element_entry_values_f  ev
,      pay_input_values_f          iv
WHERE  et.element_type_id = el.element_type_id
AND    el.element_link_id = ee.element_link_id
AND    ee.element_entry_id = ev.element_entry_id
AND    ev.input_value_id = iv.input_value_id
AND    TRUNC(SYSDATE) BETWEEN el.effective_start_date AND el.effective_end_date
AND    TRUNC(SYSDATE) BETWEEN ee.effective_start_date AND ee.effective_end_date
AND    TRUNC(SYSDATE) BETWEEN ev.effective_start_date AND ev.effective_end_date
AND    TRUNC(SYSDATE) BETWEEN iv.effective_start_date AND iv.effective_end_date
AND    et.element_name IN ('Gen Allowance'
                          ,'Car Allow'
                          ,'Professional Study Allowance'
                          ,'Professional Study Subscription'
                          ,'Acting Up'
                          ,'Car Parking Space Paid'
                          ,'Retirement Allowance'
                          ,'Medical Insurance'
                          )
GROUP BY ee.assignment_id
,      et.element_name
)
PIVOT
(
SUM(val)
FOR element_name IN ('Gen Allowance'                   gen
                    ,'Car Allow'                       car
                    ,'Professional Study Allowance'    study
                    ,'Professional Study Subscription' study_subs
                    ,'Acting Up'                       acting
                    ,'Car Parking Space Paid'          parking
                    ,'Retirement Allowance'            retire
                    ,'Medical Insurance'               medical
                    )
)
ORDER BY 1,2
;