Kill Locked Session

SELECT a.object_name, b.process, b.session_id
FROM all_objects a, v$locked_object b
WHERE a.object_id = b.object_id
SELECT SID, serial#
FROM v$session
WHERE SID = '[SESSION_ID]'
ALTER SYSTEM KILL SESSION '[SID],[SERIAL#]'

To Derive Module wise conc program count

SELECT fa.application_short_name,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method, COUNT (concurrent_program_id) COUNT
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
GROUP BY fa.application_short_name, fcpv.execution_method_code
ORDER BY 1

FNDLOAD(Ldt) Scripts

1) Concurrent Program --> afcpprog.lct
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct FNDLOAD_CONC_PRG_NAME.ldt PROGRAM APPLICATION_SHORT_NAME='XXXX' CONCURRENT_PROGRAM_NAME='CONC_PRG_NAME'
2) Value Sets --> afffload.lct
FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct VALUE_SET_XXXX.ldt VALUE_SET FLEX_VALUE_SET_NAME='XXXX'
3) Menus --> afsload.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt
4) Lookups --> aflvmlu.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME='XXXX' LOOKUP_TYPE='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt
5) Flexfield --> afffload.lct
Descriptive Flexfield
Download
FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX APPLICATION_SHORT_NAME='XXXX' DESCRIPTIVE_FLEXFIELD_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt
Key Flexfield
Download
FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX APPLICATION_SHORT_NAME='XXXX' DESCRIPTIVE_FLEXFIELD_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt
6) Profile Options --> afscprof.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME='XXXX' APPLICATION_SHORT_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt
7) Responsibility --> afscursp.lct
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt
8) Request Groups --> afcpreqg.lct
Download
FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME='XXXX' REQUEST_SET_NAME='XXXX'
Upload
FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt
9) Menus --> afsload.lct
Download
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME='XXXX'
Upload
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt
10) Forms Personalization --> affrmcus.lct
Download
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt FND_FORM_CUSTOM_RULES FUNCTION_NAME='XXXX'
Upload
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct file_name.ldt

Sample query to build calendar

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt+1,'iw') week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Thu",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fri",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sat"
FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y'))
GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt+1, 'iw' ))
ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week)

Derive business days

CREATE OR REPLACE FUNCTION F_BUSINESS_DAYS
(V_START_DATE IN DATE, V_END_DATE IN DATE)
RETURN NUMBER IS DAY_COUNT NUMBER := 0;
CURR_DATE DATE;
BEGIN -- loop through and update
CURR_DATE := V_START_DATE;
WHILE CURR_DATE <= V_END_DATE
LOOP
IF TO_CHAR(CURR_DATE,'DY') NOT IN ('SAT','SUN')
THEN DAY_COUNT := DAY_COUNT + 1;
END IF;
CURR_DATE := CURR_DATE + 1;
END LOOP;
RETURN DAY_COUNT;
END F_BUSINESS_DAYS;

Derive Business Days Count

SELECT count(business_date) business_day
FROM (SELECT TO_DATE (:TO_DATE, 'DD-MON-YYYY')
+ ROWNUM
- 1 business_date
FROM all_objects
WHERE ROWNUM <=
TO_DATE (:FROM_DATE, 'DD-MON-YYYY')
- TO_DATE (:TO_DATE, 'DD-MON-YYYY')
+ 1)
WHERE TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN'

Quickest way to generate the columns used in the ctl file

Select decode (column_id, 1, ' ', ' , ')
rpad (column_name, 33, ' ')
decode (data_type,
'VARCHAR2', 'CHAR NULLIF ('column_name'=BLANKS)',
'FLOAT', 'DECIMAL EXTERNAL NULLIF('column_name'=BLANKS)',
'NUMBER', decode (data_precision, 0,
'INTEGER EXTERNAL NULLIF ('column_name
'=BLANKS)', decode (data_scale, 0,
'INTEGER EXTERNAL NULLIF ('
column_name'=BLANKS)',
'DECIMAL EXTERNAL NULLIF ('
column_name'=BLANKS)')),
'DATE', 'DATE "mm/dd/yy" NULLIF ('
column_name'=BLANKS)', null)
from all_tab_columns
where table_name = upper ('$LOADTABLE')
and owner = upper ('$SCHEMA')
order by column_id;

fetch the values from a string separated by commas in Oracle

SELECT * FROM (
SELECT TRIM( SUBSTR ( txt
, INSTR (txt, ',', 1, level ) + 1
, INSTR (txt, ',', 1, level+1
)
- INSTR (txt, ',', 1, level) -1 ) )
AS Result
FROM ( SELECT ',':in_string',' AS txt
FROM dual )
CONNECT BY level <=
LENGTH(txt)-LENGTH(REPLACE(txt,',',''))-1
)
Ex:
:in_string := '1,q,a,z,2,w,s,x';
Result
--------------------------
1
q
a
z
2
w
s
x

Steps to upload GIF, XLS, PDF, JPG and DOC files into oracle database

Step 1
======
Create or replace directory my_files as '/usr/temp';
Create table demo(sno number, filedata blob);
Copy any file (jpg,gif,pdf,xls....) into /usr/temp
Execute the below code. Change the file name according to your requirement

Step 2
======
declare
l_blob blob;
l_bfile bfile;
begin
insert into demo values ( 3, empty_blob() )
returning filedata into l_blob;
l_bfile := bfilename( 'MY_FILES', 'water.jpg' );
dbms_lob.fileopen( l_bfile );
dbms_lob.loadfromfile( l_blob, l_bfile, dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );
commit;
end;
Step 3
======
Check the file length to ensure whether file loaded successfully or not
Select sno,dbms_lob.getlength(filedata) from demo
Reference: http://www.sql.ru/forum/actualthread.aspx?tid=202867