put .txt and of file

unix mv file and name will be xyz.txt .


cat mvfile.sh

for file in * ; do
mv "$file" "$file".txt
done



example
==========



-rw-r--r-- 1 root root 2411 2012-01-18 08:56 v$sql_plan_statistics~
-rw-r--r-- 1 root root 2699 2012-01-18 08:56 v$sql_plan_statistics
-rw-r--r-- 1 root root 8846 2012-01-18 08:56 vmstat~
-rw-r--r-- 1 root root 9677 2012-01-18 08:56 vmstat
-rw-r--r-- 1 root root 5496 2012-01-18 08:56 view~
-rw-r--r-- 1 root root 8102 2012-01-18 08:56 view
-rw-r--r-- 1 root root 75004 2012-01-18 08:56 version count~

apt-amd-02:/home/anujs/Aptus-text-file # ls -ltr *.sh
-rw-r--r-- 1 root root 48 2012-01-17 09:21 file.sh

apt-amd-02:/home/anujs/Aptus-text-file # sh file.sh



-rw-r--r-- 1 root root 2411 2012-01-18 08:56 v$sql_plan_statistics~.txt
-rw-r--r-- 1 root root 9677 2012-01-18 08:56 vmstat.txt
-rw-r--r-- 1 root root 8846 2012-01-18 08:56 vmstat~.txt
-rw-r--r-- 1 root root 8102 2012-01-18 08:56 view.txt
-rw-r--r-- 1 root root 5496 2012-01-18 08:56 view~.txt
-rw-r--r-- 1 root root 75004 2012-01-18 08:56 version count~.txt

Run shell script via dbms_scheduler

Run Shell script via Oracle Job



SQL> begin
dbms_scheduler.create_job
(
job_name => 'EXP_DATAPUMP_FULL_BACKUP',
job_type => 'EXECUTABLE',
job_action => '/usr/bin/ksh',
start_date =>sysdate+ 1/288 ,
number_of_arguments => 1,
repeat_interval => 'freq=daily; byhour=16; byminute=0; bysecond=0',
enabled => false,
comments => 'Oracle Export Data Pump Backup' );
end;
/

PL/SQL procedure successfully completed.


SQL> exec dbms_scheduler.set_job_argument_value(job_name=>'EXP_DATAPUMP_FULL_BACKUP',argument_position=>1,argument_value=>'/home/oracle/Oracle_Datapump/exp_pump.sh') ;

PL/SQL procedure successfully completed.


run this job manually

BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'EXP_DATAPUMP_FULL_BACKUP');
END;



SQL> select job_name, enabled, run_count from user_scheduler_jobs;

JOB_NAME ENABL RUN_COUNT
------------------------------ ----- ----------
BSLN_MAINTAIN_STATS_JOB TRUE 48
DRA_REEVALUATE_OPEN_FAILURES TRUE 281
EXP_DATAPUMP_FULL_BACKUP FALSE 0 <<<<<<<--------
FGR$AUTOPURGE_JOB FALSE 0
FILE_WATCHER FALSE 0
HM_CREATE_OFFLINE_DICTIONARY FALSE 0
ORA$AUTOTASK_CLEAN TRUE 296
PURGE_LOG TRUE 296
RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 294
SM$CLEAN_AUTO_SPLIT_MERGE TRUE 294
XMLDB_NFS_CLEANUP_JOB FALSE 0



BEGIN
DBMS_SCHEDULER.enable (NAME => 'EXP_DATAPUMP_FULL_BACKUP');
END;
/



to disable

BEGIN
DBMS_SCHEDULER.disable (NAME => 'EXP_DATAPUMP_FULL_BACKUP', FORCE=> TRUE);
END;
/



SQL> select job_name, enabled, run_count from user_scheduler_jobs;

JOB_NAME ENABL RUN_COUNT
------------------------------ ----- ----------
BSLN_MAINTAIN_STATS_JOB TRUE 48
DRA_REEVALUATE_OPEN_FAILURES TRUE 281
EXP_DATAPUMP_FULL_BACKUP TRUE 0 <<<<<<<<<<<<<-------------
FGR$AUTOPURGE_JOB FALSE 0
FILE_WATCHER FALSE 0
HM_CREATE_OFFLINE_DICTIONARY FALSE 0
ORA$AUTOTASK_CLEAN TRUE 296
PURGE_LOG TRUE 296
RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 294
SM$CLEAN_AUTO_SPLIT_MERGE TRUE 294
XMLDB_NFS_CLEANUP_JOB FALSE 0

11 rows selected.


TTITLE 'Results of Job Chain Testing (from DBA_SCHEDULER_JOB_LOG)'
COL log_id FORMAT 999999 HEADING 'Job|Log#'
COL job_name FORMAT A30 HEADING 'Job Name'
COL job_subname FORMAT A20 HEADING 'Step Name'
COL operation FORMAT A12 HEADING 'Operation'
COL status FORMAT A12 HEADING 'Step|Status'
COL condition FORMAT A40 HEADING 'Additional Information' WRAP
SELECT
log_id
,job_name
,job_subname
,operation
,status
,TO_CHAR(additional_info) condition
FROM dba_scheduler_job_log
WHERE 1=1
-- and owner = 'ANUJ'
AND log_date > (SYSDATE - 1/12)
ORDER BY log_id DESC;


COL log_id FORMAT 999999 HEADING 'Job|Log#'
COL job_name FORMAT A30 HEADING 'Job Name'
COL job_subname FORMAT A20 HEADING 'Step Name'
COL status FORMAT A12 HEADING 'Step Status'
COL error# FORMAT 9999999 HEADING 'Error|Code'
COL additional_info FORMAT A40 HEADING 'Additional Information' WRAP
SELECT
log_id
,job_name
,job_subname
,status
,error#
,additional_info
FROM dba_scheduler_job_run_details JRD
WHERE 1=1
-- JRD.owner = 'ANUJ'
AND log_date > (SYSDATE - 2)
and status!='SUCCEEDED'
ORDER BY log_id DESC;




COL log_id FORMAT 999999 HEADING 'Job|Log#'
COL job_name FORMAT A30 HEADING 'Job Name'
COL job_subname FORMAT A20 HEADING 'Step Name'
COL status FORMAT A12 HEADING 'Step Status'
COL error# FORMAT 9999999 HEADING 'Error|Code'
COL additional_info FORMAT A40 HEADING 'Additional Information' WRAP
SELECT
log_id
,job_name
,job_subname
,status
,error#
,additional_info
FROM dba_scheduler_job_run_details JRD
WHERE 1=1
-- JRD.owner = 'ANUJ'
AND log_date > (SYSDATE - 2)
and status!='SUCCEEDED'
ORDER BY log_id DESC;

===========================




cat /aptus/oracle/admin/Scripts/Exp_Datapump1.sh

# !/bin/bash
TODAY=`date`
ORACLE_SID=aptdb ; export ORACLE_SID
ORACLE_BASE=/aptus/oracle ; export ORACLE_BASE
ORACLE_HOME=/aptus/oracle/product/11.2.0/db_1 ; export ORACLE_HOME
PATH=${ORACLE_HOME}/bin:$PATH ; export PATH
EXPFILE=$ORACLE_SID.datapump.`date +%d%m%Y`.dmp; export EXPFILE
ERRFILE=$ORACLE_SID.datapump.`date +%d%m%Y`.err; export ERRFILE
LOGFILE=$ORACLE_SID.datapump.`date +%d%m%Y`.log ; export LOGFILE
SUCC=/aptus/db-backup/OracleExpBackup/SUCC.txt ; export SUCC

# echo $EXPFILE

find /aptus/db-backup/OracleExpBackup -name "*.err" -ctime +1 -exec rm {} \;
find /aptus/db-backup/OracleExpBackup -name "*.dmp*" -ctime +1 -exec rm {} \;
find /aptus/db-backup/OracleExpBackup -name "*.log*" -ctime +1 -exec rm {} \;
find /aptus/db-backup/OracleExpBackup -name "*.dmp*" -ctime +1 -exec rm {} \;
expdp "'/ as sysdba'" directory=aptusbackup DUMPFILE=$EXPFILE full=y logfile=$LOGFILE EXCLUDE=STATISTICS parallel=4
exit ;
==========




metalink ref


Window issues:
Note 742683.1 Scheduled Job Works Does Not Start In Window.

Purging Diagnostics
Note 443364.1 How To Purge DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_WINDOW_LOG
Note 749440.1 Dbms_scheduler.Purge Not Removing Entries from dba_scheduler_job_run_details

Schedule issues:
Note 312547.1 Advanced use of DBMS_JOB.SUBMIT �interval� Parameter
Note 428872.1 Scheduled Jobs Do Not Run After A Re-Start With Repeat_interval => Null.

Issues with Statistic Jobs:
Note 430636.1 GATHER_STATS_JOB Is Not Running
Note 377143.1 How to check what automatic statistics collection is scheduled on 10g
Note 803191.1 Checklist To Diagnose Issues Related To Automatic Run Of
Job GATHER_STATS_JOB

Issues with external Jobs:

http://forums.oracle.com/forums/thread.jspa?threadID=555102

NOTE:389685.1 Items to Check when Problems Running Executables through the Scheduler
NOTE:976049.1 DBMS_SCHEDULER fails with ORA-27369 and
STANDARD_ERROR= Launching external job failed: Invalid username or password
NOTE:1300215.1 ORA-27369: job of type EXECUTABLE failed with exit code: 274667

Please note that this article will guide through the correct analysis for Scheduler/Job Problems.

It is also practicable to go through a list of known issues / bugs which will be listed in
NOTE:1311355.1 Known Issues And Bugs With Using the Scheduler
This article is currently under edit and will be made publish in the near future


these file should be there



oracle@apt-amd-02:~/Oracle_Datapump> ls -ltr $ORACLE_HOME/rdbms/admin/externaljob.ora
-rw-r----- 1 root oinstall 1534 2005-12-21 12:30 /opt/app/oracle/product/11.2/rdbms/admin/externaljob.ora



SQL> !ls -ltr $ORACLE_HOME/bin/extjob
-rwsr-x--- 1 root oinstall 1232563 2011-01-10 15:16 /opt/app/oracle/product/11.2/bin/extjob

Oracle session info

e.sql


select ses.sid ||','||ses.serial#,sqa.SQL_TEXT SQL from
v$session ses, v$sqlarea sqa, v$process proc
where ses.paddr=proc.addr
and ses.sql_hash_value=sqa.hash_value
and proc.spid=&1;


-bash-3.00$ cat s.sql
set pagesize 32000
set linesize 200
column sid format a12
column username format a10
column terminal format a10
column sql_text format a40 wrap
Select ''''||s.sid||','||s.serial#||'''' SID
,s.status
,s.username
,s.terminal
,q.SQL_TEXT
,q.command_type
,s.sql_id
from
v$session s
,(select distinct
address
,sql_text
,command_type
from v$sql) q
where
q.address=s.sql_address
order by s.sid
/
prompt >>>>>>>>>>>>>>>>>>>>>>>>>>>-- run e.sql for explain plan alter system kill session IMMEDIATE;

Oracle SORT ACTIVITY

Oracle Sort Info


set linesize 150 pagesize 1400;

SELECT *
FROM (SELECT matching_criteria,
TO_CHAR(force_matching_signature) force_matching_signature,
sql_id,
child_number,
sql_text,
executions,
elapsed_time / 1000,
operation_type,
policy,
estimated_optimal_size,
last_memory_used,
last_execution,
active_time / 1000,
num_sort_operations,
tot_active_time / 1000,
tot_optimal_executions,
tot_onepass_executions,
tot_multipasses_executions,
all_tot_active_time / 1000,
max_tempseg_size,
parsing_schema_name
FROM (SELECT force_matching_signature,
sql_id,
child_number,
sql_text,
matching_criteria,
SUM(executions) OVER(PARTITION BY matching_criteria) executions,
SUM(elapsed_time) OVER(PARTITION BY matching_criteria) elapsed_time,
operation_type,
policy,
estimated_optimal_size,
last_memory_used,
last_execution,
active_time,
num_sort_operations,
SUM(tot_active_time) OVER(PARTITION BY matching_criteria) tot_active_time,
SUM(tot_optimal_executions) OVER(PARTITION BY matching_criteria) tot_optimal_executions,
SUM(tot_onepass_executions) OVER(PARTITION BY matching_criteria) tot_onepass_executions,
SUM(tot_multipasses_executions) OVER(PARTITION BY matching_criteria) tot_multipasses_executions,
MAX(max_tempseg_size) OVER(PARTITION BY matching_criteria) max_tempseg_size,
SUM(tot_active_time) OVER() all_tot_active_time,
ROW_NUMBER() OVER(PARTITION BY matching_criteria ORDER BY tot_multipasses_executions DESC, tot_onepass_executions DESC, last_memory_used DESC) rnum,
parsing_schema_name
FROM (SELECT s.sql_id,
s.child_number,
s.sql_text,
s.executions,
s.elapsed_time,
s.force_matching_signature,
CASE
WHEN s.force_matching_signature > 0 THEN
TO_CHAR(s.force_matching_signature)
ELSE
s.sql_id
END matching_criteria,
ROW_NUMBER() OVER(PARTITION BY s.sql_id, s.child_number ORDER BY sw.multipasses_executions DESC, sw.onepass_executions DESC, sw.last_memory_used DESC) rnum,
sw.operation_type,
sw.policy,
sw.estimated_optimal_size,
sw.last_memory_used,
sw.last_execution,
MAX(sw.max_tempseg_size) OVER(PARTITION BY s.sql_id, s.child_number) max_tempseg_size,
sw.active_time * sw.total_executions active_time,
SUM(sw.active_time * sw.total_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_active_time,
COUNT(*) OVER(PARTITION BY s.sql_id, s.child_number) num_sort_operations,
SUM(sw.optimal_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_optimal_executions,
SUM(sw.onepass_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_onepass_executions,
SUM(sw.multipasses_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_multipasses_executions,
NVL(u.username, s.parsing_schema_name) parsing_schema_name
FROM v$sql s, v$sql_workarea sw, all_users u
WHERE sw.sql_id = s.sql_id
AND sw.child_number = s.child_number
AND u.user_id(+) = s.parsing_user_id)
WHERE rnum = 1)
WHERE rnum = 1
ORDER BY tot_multipasses_executions DESC,
tot_onepass_executions DESC,
last_memory_used DESC)
WHERE ROWNUM <= 200
/

SELECT *
FROM (SELECT s.sid,
s.machine,
s.program,
s.module,
s.osuser,
NVL(DECODE(TYPE,
'BACKGROUND',
'SYS (' || b.ksbdpnam || ')',
s.username),
SUBSTR(p.program, INSTR(p.program, '('))) username,
NVL(SUM(CASE
WHEN sn.name = 'sorts (memory)' THEN
ss.VALUE
ELSE
0
END),
0) sorts_memory,
NVL(SUM(CASE
WHEN sn.name = 'sorts (disk)' THEN
ss.VALUE
ELSE
0
END),
0) sorts_disk,
NVL(SUM(CASE
WHEN sn.name = 'sorts (rows)' THEN
ss.VALUE
ELSE
0
END),
0) sorts_rows,
NVL(SUM(CASE
WHEN sn.name = 'physical reads direct temporary tablespace' THEN
ss.VALUE
ELSE
0
END),
0) reads_direct_temp,
NVL(SUM(CASE
WHEN sn.name = 'physical writes direct temporary tablespace' THEN
ss.VALUE
ELSE
0
END),
0) writes_direct_temp,
NVL(SUM(CASE
WHEN sn.name = 'workarea executions - optimal' THEN
ss.VALUE
ELSE
0
END),
0) workarea_exec_optimal,
NVL(SUM(CASE
WHEN sn.name = 'workarea executions - onepass' THEN
ss.VALUE
ELSE
0
END),
0) workarea_exec_onepass,
NVL(SUM(CASE
WHEN sn.name = 'workarea executions - multipass' THEN
ss.VALUE
ELSE
0
END),
0) workarea_exec_multipass
FROM v$session s,
v$sesstat ss,
v$statname sn,
v$process p,
x$ksbdp b
WHERE s.paddr = p.addr
AND b.inst_id(+) = USERENV('INSTANCE')
AND p.addr = b.ksbdppro(+)
AND s.TYPE = 'USER'
AND s.sid = ss.sid
AND ss.statistic# = sn.statistic#
AND sn.name IN ('sorts (memory)',
'sorts (disk)',
'sorts (rows)',
'physical reads direct temporary tablespace',
'physical writes direct temporary tablespace',
'workarea executions - optimal',
'workarea executions - onepass',
'workarea executions - multipass')
GROUP BY s.sid,
s.machine,
s.program,
s.module,
s.osuser,
NVL(DECODE(TYPE,
'BACKGROUND',
'SYS (' || b.ksbdpnam || ')',
s.username),
SUBSTR(p.program, INSTR(p.program, '(')))
ORDER BY workarea_exec_multipass DESC,
workarea_exec_onepass DESC,
reads_direct_temp + writes_direct_temp DESC,
sorts_rows DESC)
WHERE ROWNUM <= 200
/

SELECT rawtohex(workarea_address) workarea_address,
sql_id,
sql_text,
operation_type,
policy,
sid,
active_time,
work_area_size,
expected_size,
actual_mem_used,
max_mem_used,
number_passes,
tempseg_size,
tablespace,
complete_ratio,
elapsed,
time_remaining,
opname,
machine,
program,
module,
osuser,
username
FROM (SELECT swa.workarea_address,
swa.sql_id,
sa.sql_text,
swa.operation_type,
swa.policy,
swa.sid,
swa.active_time / 1000 active_time,
swa.work_area_size,
swa.expected_size,
swa.actual_mem_used,
swa.max_mem_used,
swa.number_passes,
swa.tempseg_size,
swa.tablespace,
(CASE
WHEN sl.totalwork <> 0 THEN
sl.sofar / sl.totalwork
ELSE
NULL
END) complete_ratio,
sl.elapsed_seconds * 1000 elapsed,
sl.time_remaining * 1000 time_remaining,
sl.opname,
s.machine,
s.program,
s.module,
s.osuser,
NVL(DECODE(TYPE,
'BACKGROUND',
'SYS (' || b.ksbdpnam || ')',
s.username),
SUBSTR(p.program, INSTR(p.program, '('))) username,
ROW_NUMBER() OVER(PARTITION BY swa.sql_id ORDER BY sl.last_update_time DESC) rnum
FROM v$sql_workarea_active swa,
v$sqlarea sa,
(SELECT * FROM v$session_longops WHERE sofar <> totalwork) sl,
v$session s,
v$process p,
x$ksbdp b
WHERE sl.sid(+) = swa.sid
AND sl.sql_id(+) = swa.sql_id
AND swa.sid <> USERENV('sid')
AND sa.sql_id = swa.sql_id
AND s.sid = swa.sid
AND s.paddr = p.addr
AND b.inst_id(+) = USERENV('INSTANCE')
AND p.addr = b.ksbdppro(+)
ORDER BY swa.number_passes DESC, swa.work_area_size DESC)
WHERE rnum = 1
/