ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:mi:ss';
ALTER SYSTEM KILL SESSION 'sid,serial#';
ALTER SYSTEM KILL SESSION 'sid,serial#@inst_id';

SELECT *
FROM
(SELECT b.poc_cde, a.*, row_number() over(PARTITION BY b.poc_cde ORDER BY b.poc_cde, a.batch_trans_dte) r FROM pbr_prcsng_org_batch_run_sched a, pop_prcsng_org_pay_period b WHERE a.pop_ident = b.pop_ident AND a.batch_trans_stat_cde = 'S')
WHERE r = 1

select level lev from dual connect by level 0 THEN pipe ROW(SUBSTR(l_list, 1, l_idx -1)); l_list := SUBSTR(l_list, l_idx + LENGTH(p_del)); ELSE pipe ROW(l_list); EXIT;
END IF;
END LOOP;
RETURN;
END split;
/
TABLESPACE SIZE/DATA FILE ERRORS :

ALTER TABLESPACE my_ts ADD DATAFILE 'D:\app\Administrator\oradata\vw\my_ts2.dbf' SIZE 10M AUTOEXTEND ON NEXT 200K MAXSIZE 200M;

+++++ LOCKED OBJECTS

-- view all currently locked objects: SELECT username U_NAME, owner OBJ_OWNER, object_name, object_type, s.osuser,
DECODE(l.block,
0, 'Not Blocking', 1, 'Blocking', 2, 'Global') STATUS, DECODE(v.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share', 5, 'S/Row-X (SSX)', 6, 'Exclusive', TO_CHAR(lmode) ) MODE_HELD
FROM gv$locked_object v, dba_objects d, gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
ORDER BY username, session_id;

-- list current locks SELECT session_id,lock_type, mode_held, mode_requested, blocking_others, lock_id1
FROM dba_lock l
WHERE lock_type
NOT IN ('Media Recovery', 'Redo Thread');

-- list objects that have been
-- locked for 60 seconds or more: SELECT SUBSTR(TO_CHAR(w.session_id),1,5) WSID, p1.spid WPID,
SUBSTR(s1.username,1,12) "WAITING User",
SUBSTR(s1.osuser,1,8) "OS User",
SUBSTR(s1.program,1,20) "WAITING Program", s1.client_info "WAITING Client",
SUBSTR(TO_CHAR(h.session_id),1,5) HSID, p2.spid HPID,
SUBSTR(s2.username,1,12) "HOLDING User",
SUBSTR(s2.osuser,1,8) "OS User",
SUBSTR(s2.program,1,20) "HOLDING Program", s2.client_info "HOLDING Client",
o.object_name "HOLDING Object"
FROM gv$process p1, gv$process p2, gv$session s1, gv$session s2, dba_locks w, dba_locks h, dba_objects o
WHERE w.last_convert > 60
AND h.mode_held != 'None'
AND h.mode_held != 'Null'
AND w.mode_requested != 'None'
AND s1.row_wait_obj# = o.object_id
AND w.lock_type(+) = h.lock_type
AND w.lock_id1(+) = h.lock_id1
AND w.lock_id2 (+) = h.lock_id2
AND w.session_id = s1.sid (+)
AND h.session_id = s2.sid (+)
AND s1.paddr = p1.addr (+)
AND s2.paddr = p2.addr (+)
ORDER BY w.last_convert DESC;

-- alternate example: SELECT s.username, s.sid, s.serial#, s.osuser, k.ctime, o.object_name object, k.kaddr, DECODE(l.locked_mode, 1, 'No Lock', 2, 'Row Share', 3, 'Row Exclusive', 4, 'Shared Table', 5, 'Shared Row Exclusive', 6, 'Exclusive') locked_mode, DECODE(k.TYPE, 'BL','Buffer Cache Management (PCM lock)', 'CF','Controlfile Transaction', 'CI','Cross Instance Call', 'CU','Bind Enqueue', 'DF','Data File', 'DL','Direct Loader', 'DM','Database Mount', 'DR','Distributed Recovery', 'DX','Distributed Transaction', 'FS','File Set', 'IN','Instance Number', 'IR','Instance Recovery', 'IS','Instance State', 'IV','Library Cache Invalidation', 'JQ','Job Queue', 'KK','Redo Log Kick', 'LA','Library Cache Lock', 'LB','Library Cache Lock', 'LC','Library Cache Lock', 'LD','Library Cache Lock', 'LE','Library Cache Lock', 'LF','Library Cache Lock', 'LG','Library Cache Lock', 'LH','Library Cache Lock', 'LI','Library Cache Lock', 'LJ','Library Cache Lock', 'LK','Library Cache Lock', 'LL','Library Cache Lock', 'LM','Library Cache Lock', 'LN','Library Cache Lock', 'LO','Library Cache Lock', 'LP','Library Cache Lock', 'MM','Mount Definition', 'MR','Media Recovery', 'NA','Library Cache Pin', 'NB','Library Cache Pin', 'NC','Library Cache Pin', 'ND','Library Cache Pin', 'NE','Library Cache Pin', 'NF','Library Cache Pin', 'NG','Library Cache Pin', 'NH','Library Cache Pin', 'NI','Library Cache Pin', 'NJ','Library Cache Pin', 'NK','Library Cache Pin', 'NL','Library Cache Pin', 'NM','Library Cache Pin', 'NN','Library Cache Pin', 'NO','Library Cache Pin', 'NP','Library Cache Pin', 'NQ','Library Cache Pin', 'NR','Library Cache Pin', 'NS','Library Cache Pin', 'NT','Library Cache Pin', 'NU','Library Cache Pin', 'NV','Library Cache Pin', 'NW','Library Cache Pin', 'NX','Library Cache Pin', 'NY','Library Cache Pin', 'NZ','Library Cache Pin', 'PF','Password File', 'PI','Parallel Slaves', 'PR','Process Startup', 'PS','Parallel Slave Synchronization', 'QA','Row Cache Lock', 'QB','Row Cache Lock', 'QC','Row Cache Lock', 'QD','Row Cache Lock', 'QE','Row Cache Lock', 'QF','Row Cache Lock', 'QG','Row Cache Lock', 'QH','Row Cache Lock', 'QI','Row Cache Lock', 'QJ','Row Cache Lock', 'QK','Row Cache Lock', 'QL','Row Cache Lock', 'QM','Row Cache Lock', 'QN','Row Cache Lock', 'QO','Row Cache Lock', 'QP','Row Cache Lock', 'QQ','Row Cache Lock', 'QR','Row Cache Lock', 'QS','Row Cache Lock', 'QT','Row Cache Lock', 'QU','Row Cache Lock', 'QV','Row Cache Lock', 'QW','Row Cache Lock', 'QX','Row Cache Lock', 'QY','Row Cache Lock', 'QZ','Row Cache Lock', 'RT','Redo Thread', 'SC','System Commit number', 'SM','SMON synchronization', 'SN','Sequence Number', 'SQ','Sequence Enqueue', 'SR','Synchronous Replication', 'SS','Sort Segment', 'ST','Space Management Transaction', 'SV','Sequence Number Value', 'TA','Transaction Recovery', 'TM','DML Enqueue', 'TS','Table Space (or Temporary Segment)', 'TT','Temporary Table', 'TX','Transaction', 'UL','User-defined Locks', 'UN','User Name', 'US','Undo segment Serialization', 'WL','Writing redo Log', 'XA','Instance Attribute Lock', 'XI','Instance Registration Lock') TYPE
FROM gv$session s, sys.gv$lock c, sys.gv$locked_object l, dba_objects o, sys.gv$lock k, gv$lock v
WHERE o.object_id = l.object_id
AND l.session_id = s.sid
AND k.sid = s.sid
AND s.saddr = c.saddr
AND k.kaddr = c.kaddr
AND k.kaddr = v.kaddr
AND v.saddr = s.saddr
AND k.lmode = l.locked_mode
AND k.lmode = c.lmode
AND k.request = c.request
ORDER BY object;

*****************

11G TO 10G (XE)

1. expdp ETAG_CHTBW/etag@vw FULL=N VERSION=10.2 DIRECTORY=data_pump_dir DUMPFILE=ETAG_CHTBW.dmp LOGFILE=ETAG_CHTBW.log

dump generated in : D:\app\Administrator\admin\vw\dpdump

in 10g (XE for eg) put the dump generated above in this location (where 10g is installed) before running the below command:

c:\oraclexe\app\oracle\admin\XE\dpdump

1.1 GRANT read, write on directory data_pump_dir to ETAG_CHTBW

2. impdp etag_chtbw/etag_chtbw FULL=Y DIRECTORY=data_pump_dir DUMPFILE=ETAG_CHTBW.DMP LOGFILE=ETAG_CHTBW.DMP_imp.log

*****

TABLESPACE (MINEXTENTS) ERROR RESOLUTION (IN CASE U GET IT):

ALTER TABLESPACE ETAG_DAT01 ADD DATAFILE 'C:\oraclexe\oradata\XE\MY_TS2.DBF' SIZE 90M AUTOEXTEND ON NEXT 200K MAXSIZE 200M;

****************************

REF CURSOR

****************************

Passing REF CURSOR as parameters to sub-programs

In the previous section, we already started working with sub-programs (or sub-routines). In this section, I shall extend the same with the concept of "parameters" (or arguments). Every sub-program (or sub-routine) can accept values passed to it in the form of "parameters" (or arguments). Every parameter is very similar to a variable, but gets declared as part of a sub-program.

Let us consider the following program:

declare type r_cursor is REF CURSOR; c_emp r_cursor; type rec_emp is record ( name varchar2(20), sal number(6) ); procedure PrintEmployeeDetails(p_emp r_cursor) is er rec_emp; begin loop fetch p_emp into er; exit when p_emp%notfound; dbms_output.put_line(er.name || ' - ' || er.sal); end loop; end; begin for i in (select deptno,dname from dept) loop open c_emp for select ename,sal from emp where deptno = i.deptno; dbms_output.put_line(i.dname); dbms_output.put_line('--------------'); PrintEmployeeDetails(c_emp); close c_emp; end loop; end; From the above program, you can observe the following declaration:

procedure PrintEmployeeDetails(p_emp r_cursor) is

*****************************
CSV to normal;

DECLARE sourceStr VARCHAR2(100) := '3,34,55,6'; workStr sourceStr%TYPE; singleVal sourceStr%TYPE; charPos BINARY_INTEGER; sep VARCHAR2(1) := ',';
BEGIN
workStr := sourceStr; WHILE (INSTR(workStr,sep) > 0 ) LOOP charPos := INSTR(workStr,sep); singleVal := SUBSTR(workStr,0,charPos - 1); workStr := SUBSTR(workStr,charPos + 1); DBMS_OUTPUT.PUT_LINE('Val: '||singleVal); END LOOP; --Last value, or THE value if there is only one. singleVal := workStr; DBMS_OUTPUT.PUT_LINE('Val: '||singleVal);
END LOOP;