FINDING DEADLOCKS INFORMATION

col username form A15
col sid form 9990
col type form A4
col lmode form 990
col request form 990
col id1 form 9999990
col id2 form 9999990
break on id1 skip 1 dup
spool check_lock.lis
SELECT sn.username, m.sid, m.type, DECODE(m.lmode, 0, 'None' , 1, 'Null' , 2, 'Row Share' , 3, 'Row Excl.' , 4, 'Share' , 5, 'S/Row Excl.' , 6, 'Exclusive' , lmode, ltrim(to_char(lmode,'990'))) lmode, DECODE(m.request, 0, 'None' , 1, 'Null' , 2, 'Row Share' , 3, 'Row Excl.' , 4, 'Share' , 5, 'S/Row Excl.' , 6, 'Exclusive' , request, ltrim(to_char(request,'990'))) request, m.id1,m.id2FROM v$session sn, V$lock mWHERE (sn.sid = m.sid AND m.request != 0) OR (sn.sid = m.sid AND m.request = 0 AND lmode != 4 AND (id1, id2 ) IN (SELECT s.id1, s.id2 FROM v$lock s WHERE request != 0 AND s.id1 = m.id1 AND s.id2 = m.id2 ) )ORDER BY id1,id2, m.request;
spool off
clear breaks

WRITING TO A TEXT FILE FROM PL/SQL

create or replace procedure Cursos_Loading(p_nothing IN integer) is
v_filehandle utl_file.file_type;
begin v_filehandle := utl_file.fopen('jagat', 'ABC.TXT', 'w', 4000);
for r in 1..10 loop
utl_file.put_line(v_filehandle, 'LINE # : 'r);
end loop;
utl_file.fclose(v_filehandle);
end Cursos_Loading;
/
EXEC Cursos_Loading(10);

Dynamic Script for Kill Inactive Sessions

select 'ALTER SYSTEM KILL SESSION','''' SID ',' SERial# ''''' ;' from V$SESSION where USERNAME NOT IN ('SYS','SYSTEM')and TRUNC(LOGON_TIME)<='&DT' AND STATUS='INACTIVE';
----FOR LOCKING SESSIONS
select 'ALTER SYSTEM KILL SESSION','''' SID ',' SERial# ''''' ;'from V$SESSION where sid in(select session_id from DBA_DDL_LOCKS where owner not in ('SYS','SYSTEM'))
/

Generate SQL*Loader Control File for Any Table

set verify off
set heading off
set embedded on
set recsep off
set pages 500
set lines 80
set feedback off
define &&1 = ''
spool c:\your_path_name\&&2..ctl
select'load datainfile ''' 'C:\your_path_name\' upper('&2') '.CSV''' 'into table ' upper('&1') '.' upper('&2') 'replacefields terminated by '',''' 'trailing nullcols'from all_tables where owner = upper('devcpos') and table_name = upper('itemmaster')
/
select decode(a.column_id, '1', '(') a.column_name (case when a.column_id < max(b.column_id) then ',' else ')' END)from all_tab_columns a, all_tab_columns bwhere a.owner = upper('&1') and a.table_name = upper('&2') andb.owner = upper('&1') and b.table_name = upper('&2')group by a.column_name, a.column_idorder by a.column_id
/
spool off
set verify on
set heading on
set embedded off
set recsep wrapped
set pages 64
set lines 80
set feedback 6
undefine 1
undefine 2

EXPDP Invoking from PL/SQL

One can invoke datapump from PL/SQL -- this might be handy for scheduling a daily or weekly export with DBMS_SCHEDULER.
DECLARE hand NUMBER;
BEGIN hand := Dbms_DataPump.Open(operation => 'EXPORT',
job_mode => 'FULL', job_name 'FULLEXPJOB',
version => 'COMPATIBLE');
Dbms_DataPump.Add_File(handle => hand, filename => 'expdp_plsql.log', directory => 'JAGAT', filetype => 3);
Dbms_DataPump.Add_File(handle => hand, filename => 'expdp_plsql.dmp', directory => 'DMPDIR', filetype => 1);
END;
/

EXPDP/IMPDP

CREATE DIRECTORY DIR AS '/opt/oracle';
GRANT read, write ON DIRECTORY dmpdir TO scott;
C:\EXPDP DEVCPOS/DEVCPOS DIRECTORY=DIR DUMPFILE=DEVCPOS.DMP
C:\IMPDP SYSTEM/SYSTEM DIRECTORY=DIR DUMPFILE=DEVCPOS.DMP
C:\IMPDP QACPOS/QACPOS DIRECTORY=DIR
C:\expdp scott/tiger schemas=SCOTT directory=SCOTT_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
C:\IMPDP TEST/TEST@ORCL DIRECTORY=TSTDIR DUMPFILE=BACKUPSCOTT.DMP SCHEMAS=SCOTT REMAP_SCHEMA=SCOTT:TEST

oradim

1: oradim -new -sid PMHIST -startmode auto -PFILE D:\oracle\product\10.2.0\admin\PMHIST\pfile\initPMHIST.ora
2: oradim -new -sid PMOBILE -startmode auto -PFILE D:\oracle\product\10.2.0\admin\PMOBILE\pfile\initPMOBILE.ora
3: oradim -new -sid JAGAT -intpwd password -startmode a -pfile C:\oraclexe\app\oracle\admin\Jagat\pfile\initJAGATemp.ora