Sunday 29 January 2012
Saturday 28 January 2012
Table Comment info user wise
Table Comment info user wise
DECLARE
l_unixdb_owner VARCHAR2(20) := UPPER('&1');
CURSOR get_tc IS
SELECT * FROM dba_tab_comments
WHERE comments IS NOT NULL
AND owner=l_unixdb_owner
AND table_name not like '%ANUJ%' -- omit Anuj Table tables
ORDER BY table_name;
CURSOR get_cc (p_owner dba_tables.owner%TYPE ,p_table_name dba_tables.table_name%TYPE) IS
SELECT dcc.comments
, dtc.column_name
, DECODE(dtc.nullable,'Y','(Optional)','N','(Mandatory)') nullable
FROM dba_col_comments dcc, dba_tab_columns dtc
WHERE dcc.owner = dtc.owner
AND dcc.table_name = dtc.table_name
AND dcc.column_name = dtc.column_name
AND dcc.owner=p_owner AND dcc.table_name= p_table_name
AND dcc.comments IS NOT NULL
ORDER BY dtc.column_name
;
BEGIN
FOR tab_rec IN get_tc LOOP
dbms_output.put_line('.');
dbms_output.put_line('------------------- Start Of '
||tab_rec.table_type
||' '
||tab_rec.table_name
|| ' ---------------------');
dbms_output.put_line('.');
dbms_output.put_line(tab_Rec.table_type||' Description');
dbms_output.put_line('-----------------');
dbms_output.put_line(tab_rec.comments);
FOR col_rec IN get_cc (tab_rec.owner, tab_rec.table_name) LOOP
IF get_cc%ROWCOUNT = 1 THEN
dbms_output.put_line('.');
dbms_output.put_line('Column Descriptions');
dbms_output.put_line('-------------------');
END IF;
dbms_output.put_line (col_rec.column_name
||' '||col_rec.nullable);
dbms_output.put_line (col_rec.comments);
END LOOP;
dbms_output.put_line('.');
dbms_output.put_line('------------------- End Of Object ---------------------'
);
END LOOP;
END;
Sunday 22 January 2012
Oracle function REGEXP_INSTR
1 2 3 4 5 6 7
REGEXP_INSTR(<source_string>, <pattern>[[, <start_position>][, <occurrence>][, <return_option>][, <match_parameter>][, <sub_expression>]])
5. return_option - The default value of the return_option is 0, which returns the starting position of the pattern.
A value of 1 returns the starting position of the next character following the match
SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4) "REGEXP_INSTR" FROM DUAL;
first character in the fourth subexpression, which is '78':
REGEXP_INSTR( '1234567890' , '(123)(4(56)(78))' , 1 , 1 , 0 , 'i' , 4 )
----------- ------------------ -- -- -- -- --
1 2 3 4 5 6 7
1. source_string is a character expression that serves as the search value.Pattern is the regular expression.
It is usually a text literal .It can contain up to 512 bytes.
2. Pattern is the regular expression. It is usually a text literal .It can contain up to 512 bytes.
For more about pattern:
3. position is a positive integer indicating the character of source_string where Oracle should begin the search. The default is 1,
meaning that Oracle begins the search at the first character of source_string.
4. occurrence is a positive integer indicating which occurrence of pattern in source_string Oracle should search for.
The default is 1, meaning that Oracle searches for the first occurrence of pattern.
5. return_option lets you specify what Oracle should return in relation to the occurrence:
If you specify 0, then Oracle returns the position of the first character of the occurrence. This is the default.
If you specify 1, then Oracle returns the position of the character following the occurrence.
6. match_parameter is a text literal that lets you change the default matching behavior of the function.
You can specify one or more of the following values for match_parameter:
i: to match case insensitively
c: to match case sensitively
n: to make the dot (.) match new lines as well
m: to make ^ and $ match beginning and end of a line in a multiline string
x: to ignore white spaces.
7. subexpr: REGEXP_SUBSTR functions include a new SUBEXPR parameter that limits the pattern match to a specific subexpression in the search pattern.
Anchoring Characters
^ Anchor the expression to the start of a line
$ Anchor the expression to the end of a line
[[:digit:]]{5} ---- any five digit
Posix Characters
Character Class Description
[:digit:] Only the digits 0 to 9
[:alnum:] Any alphanumeric character 0 to 9 OR A to Z or a to z.
[:alpha:] Any alpha character A to Z or a to z.
[:blank:] Space and TAB characters only.
[:xdigit:] Hexadecimal notation 0-9, A-F, a-f.
[:punct:] Punctuation symbols
------------------------------------------------------------
% . , " ' ? ! : # $ & ( ) * ; + - / = @ [ ] \ ^ _ { } | ~
------------------------------------------------------------
[:print:] Any printable character.
[:space:] Any whitespace characters (space, tab, NL, FF, VT, CR).
Many system abbreviate as \s.
[:graph:] Exclude whitespace (SPACE, TAB). Many system abbreviate as \W.
[:upper:] Any alpha character A to Z.
[:lower:] Any alpha character a to z.
[:cntrl:] Control Characters NL CR LF TAB VT FF NUL SOH STX
EXT EOT ENQ ACK SO SI DLE DC1 DC2 DC3 DC4 NAK SYN
ETB CAN EM SUB ESC IS1 IS2 IS3 IS4 DEL.
Quantifier Characters control the number of times a character
or string is found in a search.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Character Class Description
* Match 0 or more times
? Match 0 or 1 time
+ Match 1 or more times
{m} Match exactly m times
{m,} Match at least m times
{m, n} Match at least m times but no more than n times
\n Cause the previous expression to be repeated n times
Character Class Description
| Separates alternates, often used with grouping operator ()
( ) Groups subexpression into a unit for alternations, for quantifiers, or for backreferencing (see "Backreferences" section)
[char] Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes,
and the ^ and - metacharacters
More Special Characters:
\t tab (HT, TAB)
\n newline (LF, NL)
\r return (CR)
\f form feed (FF)
\a alarm (bell) (BEL)
\e escape (think troff) (ESC)
\033 octal char (think of a PDP-11)
\x1B hex char
\c[ control char
\l lowercase next char (think vi)
\u uppercase next char (think vi)
\L lowercase till \E (think vi)
\U uppercase till \E (think vi)
\E end case modification (think vi)
\Q quote (disable) pattern metacharacters till \E
Even More Special Characters:
\w Match a "word" character (alphanumeric plus "_")
\W Match a non-word character
\s Match a whitespace character
\S Match a non-whitespace character
\d Match a digit character
\D Match a non-digit character
\b Match a word boundary
\B Match a non-(word boundary)
\A Match only at beginning of string
\Z Match only at end of string, or before newline at the end
\z Match only at end of string
\G Match only where previous m//g left off (works only with /g)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
3 ---- refer to 3rd No above
--
SELECT REGEXP_COUNT('123123123123', '123', 3, 'i') COUNT FROM DUAL;
COUNT
----------
3
3 ---- refer to 3rd No above
--
SQL> SELECT REGEXP_COUNT('123123123123', '123', 2, 'i') COUNT FROM DUAL;
COUNT
----------
3
*
SQL> SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 2) "REGEXP_INSTR" FROM DUAL; --- * this will search pattern No 2 i.e. 4
REGEXP_INSTR
------------
4
**
SQL> SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 3) "REGEXP_INSTR" FROM DUAL; ---** this will search pattern No 3 i.e. 56
REGEXP_INSTR
------------
5
**
SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4) "REGEXP_INSTR" FROM DUAL; ---** this will search pattern No 3 i.e. 78
SQL> SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 4) "REGEXP_INSTR" FROM DUAL;
REGEXP_INSTR
------------
7
SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))', 1, 1, 0, 'i', 5) "REGEXP_INSTR" FROM DUAL; --- will search pattern No 5 in this '(123)(4(56)(78))' so no pattern in this case
REGEXP_INSTR
------------
0
it will search the o which is having 3 letter after o
SQL> SELECT REGEXP_INSTR('The slippery rabbit was pursued by a ravenous wolf', '[o][[:alpha:]]{3}', 1, 1, 0, 'i') RESULT FROM dual;
RESULT
----------
0
SQL> SELECT REGEXP_INSTR('The slippery rabbit was pursued by a ravenous wolf olf', '[o][[:alpha:]]{3}', 1, 1, 0, 'i') RESULT FROM dual;
RESULT
----------
0
SQL> SELECT REGEXP_INSTR('The slippery rabbit was pursued by a ravenous wolf olfw', '[o][[:alpha:]]{3}', 1, 1, 0, 'i') RESULT FROM dual;
RESULT
----------
52
search for blank sapce for 4th occurance
SELECT REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA','[ ]+', 1, 4) "REGEXP_INSTR" FROM DUAL;
REGEXP_INSTR
------------
28
find letter after 3rd occurance of |
select REGEXP_SUBSTR('One|Two|Three|Four|Five','[^|]+', 1, 3) from dual ;
REGEX
-----
Three
SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234','[[:digit:]]{5}$') AS REGEXP_INSTR FROM dual
REGEXP_INSTR
------------
45
Friday 20 January 2012
Oracle 11g Enabling ARCHIVELOG Mode
ARCHIVELOG Mode on
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence 5
Current log sequence 7
SQL> !mkdir /u01/app/oracle/Archice/vihaan
mkdir: cannot create directory `/u01/app/oracle/Archice/vihaan': No such file or directory
SQL> !mkdir -p /u01/app/oracle/Archice/vihaan
SQL> ALTER SYSTEM SET log_archive_dest ='/u01/app/oracle/Archice/vihaan' ;
System altered.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/Archice/vihaan
Oldest online log sequence 5
Current log sequence 7
SQL> ALTER SYSTEM SET log_archive_dest ='/u01/app/oracle/Archice/vihaan' scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 941600768 bytes
Fixed Size 1348860 bytes
Variable Size 608176900 bytes
Database Buffers 327155712 bytes
Redo Buffers 4919296 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open ;
Database altered.
SQL> alter system switch logfile ;
System altered.
SQL> /
System altered.
SQL> !ls -ltr /u01/app/oracle/Archice/vihaan
total 11284
-rw-r----- 1 oracle oinstall 1024 Jan 20 15:35 1_8_769768273.dbf
-rw-r----- 1 oracle oinstall 29083648 Jan 20 15:35 1_7_769768273.dbf
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/Archice/vihaan
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9
SQL> show parameter dest
log_archive_dest string /u01/app/oracle/Archice/vihaan
Oracle 11g REGEXP_COUNT
REGEXP_COUNT(<source_string>, <pattern>[[, <start_position>], [<match_parameter>]])
^
--------------------------------------------------------------------|
-- match parameter:
'c' = case sensitive
'i' = case insensitive search
'm' = treats the source string as multiple lines
'n' = allows the period (.) wild character to match newline
'x' = ignore whitespace characters
Total how many 123 in this string '123123123123' !!!!!!!! (in total string )
SQL> SELECT REGEXP_COUNT('123123123123', '123') COUNT FROM DUAL;
COUNT
----------
4
Total how many 123 in this string '123123123123' from 4th place
|<-----------------|
SELECT REGEXP_COUNT('123123123123', '123', 4) COUNT FROM DUAL;
^
|--- this is 4th place , from this place how many 123
COUNT
----------
3
Total how many 123 in this string '123123123123' from 5th place
|<---------------|
SQL> SELECT REGEXP_COUNT('123123123123', '123',5) COUNT FROM DUAL;
^
|--- this is 5th place , from this place how many 123
COUNT
----------
2
source string at the third character for case 'i' specifies case-insensitive matching.
SELECT REGEXP_COUNT('123123123123', '123', 3, 'i') COUNT FROM DUAL;
COUNT
----------
3
SELECT REGEXP_COUNT('123123123123123', '(12)3', 1, 'i') REGEXP_COUNT FROM DUAL; ---------'(12)3' ='123'
REGEXP_COUNT
------------
5
CREATE TABLE test (testcol VARCHAR2(50));
INSERT INTO test VALUES ('abcde');
INSERT INTO test VALUES ('12345');
INSERT INTO test VALUES ('1a4A5');
INSERT INTO test VALUES ('12a45');
INSERT INTO test VALUES ('12aBC');
INSERT INTO test VALUES ('12abc');
INSERT INTO test VALUES ('12ab5');
INSERT INTO test VALUES ('12aa5');
INSERT INTO test VALUES ('12AB5');
INSERT INTO test VALUES ('ABCDE');
INSERT INTO test VALUES ('123-5');
INSERT INTO test VALUES ('12.45');
INSERT INTO test VALUES ('1a4b5');
INSERT INTO test VALUES ('1 3 5');
INSERT INTO test VALUES ('1 45');
INSERT INTO test VALUES ('1 5');
INSERT INTO test VALUES ('a b c d');
INSERT INTO test VALUES ('a b c d e');
INSERT INTO test VALUES ('a e');
INSERT INTO test VALUES ('Steven');
INSERT INTO test VALUES ('Stephen');
INSERT INTO test VALUES ('111.222.3333');
INSERT INTO test VALUES ('222.333.4444');
INSERT INTO test VALUES ('333.444.5555');
INSERT INTO test VALUES ('abcdefabcdefabcxyz');
commit ;
set pagesize 100
select * from test ;
TESTCOL
--------------------------------------------------
abcde
12345
1a4A5
12a45
12aBC
12abc
12ab5
12aa5
12AB5
ABCDE
123-5
12.45
1a4b5
1 3 5
1 45
1 5
a b c d
a b c d e
a e
Steven
Stephen
111.222.3333
222.333.4444
333.444.5555
abcdefabcdefabcxyz
25 rows selected.
search for 2a start from 1 and i for ignore case
SELECT REGEXP_COUNT(testcol, '2a', 1, 'i') RESULT FROM test;
RESULT
----------
0
0
0
1
1
1
1
1
1
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
25 rows selected.
search for "e" start from 1 and i for ignore case
SQL> SELECT REGEXP_COUNT(testcol, 'e', 1, 'i') RESULT FROM test;
RESULT
----------
1
0
0
0
0
0
0
0
0
1
0
0
0
0
0
0
0
1
1
2
2
0
0
0
2
25 rows selected.
Thursday 19 January 2012
Oracle Database Corruption Issues (Doc ID 1088018.1)
Oracle Database Corruption Issues (Doc ID 1088018.1)
Oracle block corruptions check via DBV
DBV untility - to check oracle Database Corruption
refer this link for file name
http://anuj-singh.blogspot.com/2012/01/oracle-how-to-get-only-file-name-with.html
DB Verify
DB_VERIFY (dbv)
Oracle dbvverify Utility
col file_name format a20
select substr(name,instr(name,'/',-1)+1) file_name from v$datafile
FILE_NAME
--------------------
system01.dbf
sysaux01.dbf
users_compress.dbf
users01.dbf
example01.dbf
anujtest.dbf
tsapexf01.dbf
tsapexu01.dbf
test.dbf
rman.dbf
drop.dbf
anuj_perfstat.dbf
undotbR.dbf
oracle@apt-amd-02:~> cat dbv.sh
# !/bin/bash
export ORACLE_SID=orcl
export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=/opt/app/oracle/product/11.2
export PATH=${ORACLE_HOME}/bin:$PATH
sqlplus -s "/ as sysdba" <<EOF
set echo off feedback off verify off pages 0 termout off linesize 150
spool dbv-${ORACLE_SID}.sh
select 'dbv file=' || name || ' blocksize=' || block_size || ' feedback=' || round(blocks*.10,0) ||' logfile=/tmp/'||substr(name,instr(name,'/',-1)+1)||'.log'
from v\$datafile;
spool off
set feedback on verify on pages24 echo on termout on
EOF
this script will create
-rw-r--r-- 1 oracle oinstall 1963 2012-01-19 09:41 dbv-orcl.sh
sh dbv.sh
dbv file=/opt/app/oracle/oradata/orcl/system01.dbf blocksize=8192 feedback=11136 logfile=/tmp/system01.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/sysaux01.dbf blocksize=8192 feedback=10624 logfile=/tmp/sysaux01.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/users_compress.dbf blocksize=8192 feedback=640 logfile=/tmp/users_compress.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/users01.dbf blocksize=8192 feedback=8688 logfile=/tmp/users01.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/example01.dbf blocksize=8192 feedback=1280 logfile=/tmp/example01.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/anujtest.dbf blocksize=8192 feedback=128 logfile=/tmp/anujtest.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/tsapexf01.dbf blocksize=8192 feedback=640 logfile=/tmp/tsapexf01.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/tsapexu01.dbf blocksize=8192 feedback=1408 logfile=/tmp/tsapexu01.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/test.dbf blocksize=8192 feedback=256 logfile=/tmp/test.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/rman.dbf blocksize=8192 feedback=640 logfile=/tmp/rman.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/drop.dbf blocksize=8192 feedback=26 logfile=/tmp/drop.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/anuj_perfstat.dbf blocksize=8192 feedback=12800 logfile=/tmp/anuj_perfstat.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/undotbR.dbf blocksize=8192 feedback=6400 logfile=/tmp/undotbR.dbf.log
dbv help=y
DBVERIFY: Release 11.2.0.1.0 - Production on Thu Jan 19 09:48:16 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Keyword Description (Default)
----------------------------------------------------
FILE File to Verify (NONE)
START Start Block (First Block of File)
END End Block (Last Block of File)
BLOCKSIZE Logical Block Size (8192)
LOGFILE Output Log (NONE)
FEEDBACK Display Progress (0)
PARFILE Parameter File (NONE)
USERID Username/Password (NONE)
SEGMENT_ID Segment ID (tsn.relfile.block) (NONE)
HIGH_SCN Highest Block SCN To Verify (NONE)
(scn_wrap.scn_base OR scn)
refer this link for file name
http://anuj-singh.blogspot.com/2012/01/oracle-how-to-get-only-file-name-with.html
Oracle How to get only file name ( with out path )
Oracle file name
set linesize 200
col file_name format a50
col name format a70
col path for a50
select FILE_NAME,substr(FILE_NAME, 1, instr(FILE_NAME, '/',-1)) path , substr(FILE_NAME,instr(FILE_NAME,'/',-1)+1, instr(substr(FILE_NAME,instr(FILE_NAME,'/',-1)+1),'.')-1 ) file_name from dba_data_files
where TABLESPACE_NAME='USERS' ;
FILE_NAME PATH FILE_NAME
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
/u01/app/oradata/Orclsex/users01.dbf /u01/app/oradata/Orclsex/ users01
/u01/app/oradata/Orclsex/users02.dbf /u01/app/oradata/Orclsex/ users02
/u01/app/oradata/Orclsex/users03.dbf /u01/app/oradata/Orclsex/ users03
/u01/app/oradata/Orclsex/users04.dbf /u01/app/oradata/Orclsex/ users04
/u01/app/oradata/Orclsex/users05.dbf /u01/app/oradata/Orclsex/ users05
/u01/app/oradata/Orclsex/users06.dbf /u01/app/oradata/Orclsex/ users06
/u01/app/oradata/Orclsex/users07.dbf /u01/app/oradata/Orclsex/ users07
/u01/app/oradata/Orclsex/users08.dbf /u01/app/oradata/Orclsex/ users08
/u01/app/oradata/Orclsex/users09.dbf /u01/app/oradata/Orclsex/ users09
/u01/app/oradata/Orclsex/users10.dbf /u01/app/oradata/Orclsex/ users10
/u01/app/oradata/Orclsex/users11.dbf /u01/app/oradata/Orclsex/ users11
/u01/app/oradata/Orclsex/users12.dbf /u01/app/oradata/Orclsex/ users12
/u01/app/oradata/Orclsex/users13.dbf /u01/app/oradata/Orclsex/ users13
13 rows selected.
Oracle How to get only file name ( with out path ) Oracle file name
select substr(name,instr(name,'/',-1)+1) file_name from v$datafile; FILE_NAME -------------------- system01.dbf sysaux01.dbf undotbs01.dbf users01.dbf example01.dbf
set linesize 200 col file_name format a15 col name format a70 select name, substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) file_name from v$datafile;
NAME FILE_NAME ---------------------------------------------------------------------- --------------- /u01/app/oracle/oradata/vihaan/system01.dbf system01 /u01/app/oracle/oradata/vihaan/sysaux01.dbf sysaux01 /u01/app/oracle/oradata/vihaan/undotbs01.dbf undotbs01 /u01/app/oracle/oradata/vihaan/users01.dbf users01 /u01/app/oracle/oradata/vihaan/example01.dbf example01
set linesize 200 col file_name format a15 col name format a70 col path for a50 select name,substr(name, 1, instr(name, '/',-1)) path , substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) file_name from v$datafile; NAME PATH FILE_NAME ---------------------------------------------------------------------- -------------------------------------------------- --------------- /u01/app/oradata/Orclsex/system01.dbf /u01/app/oradata/Orclsex/ system01 /u01/app/oradata/Orclsex/sysaux01.dbf /u01/app/oradata/Orclsex/ sysaux01 /u01/app/oradata/Orclsex/undotbs01.dbf /u01/app/oradata/Orclsex/ undotbs01 /u01/app/oradata/Orclsex/users01.dbf /u01/app/oradata/Orclsex/ users01
set linesize 200
col file_name format a50
col name format a70
col path for a50
select FILE_NAME,substr(FILE_NAME, 1, instr(FILE_NAME, '/',-1)) path , substr(FILE_NAME,instr(FILE_NAME,'/',-1)+1, instr(substr(FILE_NAME,instr(FILE_NAME,'/',-1)+1),'.')-1 ) file_name from dba_data_files
where TABLESPACE_NAME='USERS' ;
FILE_NAME PATH FILE_NAME
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
/u01/app/oradata/Orclsex/users01.dbf /u01/app/oradata/Orclsex/ users01
/u01/app/oradata/Orclsex/users02.dbf /u01/app/oradata/Orclsex/ users02
/u01/app/oradata/Orclsex/users03.dbf /u01/app/oradata/Orclsex/ users03
/u01/app/oradata/Orclsex/users04.dbf /u01/app/oradata/Orclsex/ users04
/u01/app/oradata/Orclsex/users05.dbf /u01/app/oradata/Orclsex/ users05
/u01/app/oradata/Orclsex/users06.dbf /u01/app/oradata/Orclsex/ users06
/u01/app/oradata/Orclsex/users07.dbf /u01/app/oradata/Orclsex/ users07
/u01/app/oradata/Orclsex/users08.dbf /u01/app/oradata/Orclsex/ users08
/u01/app/oradata/Orclsex/users09.dbf /u01/app/oradata/Orclsex/ users09
/u01/app/oradata/Orclsex/users10.dbf /u01/app/oradata/Orclsex/ users10
/u01/app/oradata/Orclsex/users11.dbf /u01/app/oradata/Orclsex/ users11
/u01/app/oradata/Orclsex/users12.dbf /u01/app/oradata/Orclsex/ users12
/u01/app/oradata/Orclsex/users13.dbf /u01/app/oradata/Orclsex/ users13
13 rows selected.
====
SELECT 'set newname for datafile '
|| file#
|| ' to ''/dumps/UGARB/'
|| substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) ||'.dbf'
|| ''';'
FROM v$datafile;
Wednesday 18 January 2012
Monitor Oracle user memory wise
set linesize 300
col "User" format a10
col "Machine" format a10
col "Statement" format a60
col MODULE format a30
col sid_serial format a15
SELECT SUBSTR(USERNAME,1,10) "User",
v$session.SID||','||v$session.serial# sid_serial,
SUBSTR(machine,1,10) "Machine",
sharable_mem, persistent_mem,
runtime_mem, executions,
-- v$sql.module,
SUBSTR(v$sql.sql_text,1,60) "Statement"
FROM v$session, v$sql, v$open_cursor
WHERE v$open_cursor.saddr = v$session.saddr
AND v$open_cursor.address = v$sql.address
and rownum < 50
ORDER BY SUBSTR(USERNAME,1,10), SUBSTR(machine,1,10);
User SID_SERIAL Machine SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM EXECUTIONS Statement
---------- --------------- ---------- ------------ -------------- ----------- ---------- ------------------------------------------------------------
SYS 125,9 oel5u4 13870 3080 2424 261 select timestamp, flags from fixed_obj$ where obj#=:1
SYS 125,9 oel5u4 38694 12400 11380 1 SELECT SUBSTR(USERNAME,1,10) "User", v$session.SID||','||v$s
SYS 125,9 oel5u4 18053 5784 4232 310 select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
SYS 125,9 oel5u4 13912 11868 11168 1 select oper, nclsrs, clpcstr from appqossys.wlm_classifier_p
SYS 125,9 oel5u4 18053 5684 4132 4 select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
133,1 oel5u4 26242 6244 5480 57 select size_for_estimate, size_factor *
131,1 oel5u4 18034 4960 4276 4 insert into smon_scn_time (thread, time_mp, time_dp, scn, sc
133,1 oel5u4 13995 3276 2512 57 select PGA_TARGET_FOR_ESTIMATE s, PGA_TARGET_FACTOR
10,7 oel5u4 13938 6752 6032 3 select lobtsn, lobrdba, lobobjid from x$lobstat where lobs
16,8 oel5u4 18113 4764 3396 14 select count(*) from sys.job$ where next_date < :1 and (fiel
133,1 oel5u4 17966 8352 7820 5 delete /*+ CACHE_CB("ALERT_QT") */ from "SYS"."ALERT_QT" whe
15,1 oel5u4 22099 7744 7068 6 select o.obj#, t.flags from tab$ t, obj$ o, user$ u where u
133,1 oel5u4 18028 7752 6968 7 select time_mp, scn, num_mappings, tim_scn_map from smon_scn
133,1 oel5u4 13968 2576 1812 57 select shared_pool_size_for_estimate s, shared_pool
15,1 oel5u4 18053 5684 4132 4 select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
15,1 oel5u4 18053 5784 4232 310 select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
16,8 oel5u4 13901 3476 2692 8 select userid, name, subname, flags from sys.scheduler$_l
16,8 oel5u4 13901 3476 2692 1 select userid, name, subname, flags from sys.scheduler$_l
15,1 oel5u4 17991 13872 13340 5 update "SYS"."AQ$_ALERT_QT_H" set transaction_id = NULL wher
15,1 oel5u4 13891 4200 3224 606 select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$
7,1 oel5u4 22317 7352 5608 443 select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
133,1 oel5u4 18143 1072 484 1 BEGIN dbms_ha_alerts_prvt.clear_instance_resources( :dbdo
15,1 oel5u4 18095 6224 4952 1574 select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
7,1 oel5u4 18095 6224 4952 1574 select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
133,1 oel5u4 38685 19172 17236 5 select tab.rowid, tab.msgid, tab.corrid, tab.priority, tab.
15,1 oel5u4 34906 15312 12588 6 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
133,1 oel5u4 17976 4632 4060 0 select TIME_WAITED_MICRO from V$SYSTEM_EVENT where event =
15,1 oel5u4 13968 4500 3716 8 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
15,1 oel5u4 13968 4500 3716 318 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
133,1 oel5u4 9772 2868 2296 8 select max(FA#) from SYS_FBA_FA
16,8 oel5u4 22334 5284 3812 310 select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1
15,1 oel5u4 22154 11820 10588 314 select owner#,name,namespace,remoteowner,linkname,p_timestam
15,1 oel5u4 22154 11820 10588 13 select owner#,name,namespace,remoteowner,linkname,p_timestam
15,1 oel5u4 22154 11820 10588 1 select owner#,name,namespace,remoteowner,linkname,p_timestam
133,1 oel5u4 22587 6760 6040 7 select /*+ FIRST_ROWS(1) */ x.C1, x.C2, x.C3 from (select a
15,1 oel5u4 13914 4312 3400 653 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,
7,1 oel5u4 13914 4312 3400 653 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,
133,1 oel5u4 42824 27356 26316 5 insert into "SYS"."ALERT_QT" (q_name, msgid, corrid, priori
15,1 oel5u4 13887 3784 3000 4 select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ whe
15,1 oel5u4 13887 3796 3012 1347 select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ whe
133,1 oel5u4 14068 4920 3624 1 select CONNECTION_POOL_NAME, STATUS, MINSIZE, MAXSIZE,
15,1 oel5u4 43138 17848 14932 407 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
7,1 oel5u4 43138 17848 14932 407 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
140,5 oel5u4 18096 992 444 1 begin dbms_aqadm_sys.remove_all_nondurablesub(:1, :2);
133,1 oel5u4 18231 7580 6136 2 select subscriber_id, name, address, protocol, subscriber_ty
140,5 oel5u4 13916 2736 2164 2 select 1 from sys.aq$_subscriber_table where rownum < 2 and
16,8 oel5u4 44090 13900 13136 56 select next_run_date, obj#, run_job, sch_job from (select de
133,1 oel5u4 13978 2660 1896 57 select streams_pool_size_for_estimate s, streams_p
15,1 oel5u4 22317 7352 5608 443 select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
49 rows selected.
List of only hidden files in unix
unix hidden file
List only hidden files with ls command
ls -a mydir/ | grep '^\.'
ls -a | grep "^\."
List only hidden files with ls command
ls -a mydir/ | grep '^\.'
ls -a | grep "^\."
Tuesday 17 January 2012
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
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
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;
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
/
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
/
Monday 16 January 2012
Oracle Job logs
DBMS_SCHEDULER FAILS log
Oracle SCHEDULER log
SCHEDULER 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 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
,to_char(LOG_DATE,'dd-mm-yyyy hh:mi:ss')
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
,to_char(LOG_DATE,'dd-mm-yyyy hh:mi:ss')
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;
Oracle SCHEDULER log
SCHEDULER 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 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
,to_char(LOG_DATE,'dd-mm-yyyy hh:mi:ss')
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
,to_char(LOG_DATE,'dd-mm-yyyy hh:mi:ss')
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;
Generate DDL for scheduler jobs
Generate DDL for scheduler jobs
dbms_metadata job
set long 1000000000 pagesize 0 trimspool on
select replace(replace(replace(dbms_metadata.get_ddl ('PROCOBJ',JOB_NAME,owner),'(''"','('''||owner||'.'),'"'')',''')'),'"','')||'/'
from dba_scheduler_jobs
where JOB_TYPE is not null
and owner !='SYS';
or select from dba_objects
set long 1000000000 pagesize 0 trimspool on
select replace(replace(replace(dbms_metadata.get_ddl ('PROCOBJ',object_NAME,owner),'(''"','('''||owner||'.'),'"'')',''')'),'"','')||'/'
from dba_objects
where object_type='JOB'
and owner !='SYS';
or
expdp \"/ as sysdba\" dumpfile=scott_job.dmp SCHEMAS=SCOTT content=metadata_only include=JOB
****************************************************************************************************
Dbms_metadata.Get_ddl Can Not Capture Ddl For Scheduler Jobs [ID 567504.1] for sys owner ????????
not work for owner ='SYS'; no idea why !!!!!!!!!!!!!!!!!!!!
as per metalink <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
Currently the DBMS_METADATA does not support the new 'job' type created by DBMS_SCHEDULER.
********************************************************************************************************
Saturday 14 January 2012
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
in Datapump if you are getting this error
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/Oracle_Datapump/orcl.datapump..dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at 16:02:28
Then check folloing pl
select * from v$option where parameter like 'Transparent Data Encryption%';
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Transparent Data Encryption TRUE
encryption_password=xxx provide some password
expdp "'/ as sysdba'" directory=anuj_datapump SCHEMAS=scott EXCLUDE=STATISTICS parallel=4 encryption_password=vihaan
this time no error
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/Oracle_Datapump/orcl.datapump.14012012.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 18:34:30
Friday 13 January 2012
AWR Load Trending Report
AWR Load Trending Report
AWR Load report
set lines 130 pages 1000
col stat_name for a25
col BEGIN_INTERVAL_TIME for a25
col END_INTERVAL_TIME for a25
col redo_size for '999,999,990.99'
col sess_l_reads for '999,999,990.99'
col blk_change for '999,999,990.99'
col phy_reads for '999,999,990.99'
col phy_writes for '999,999,990.99'
col user_calls for '999,999,990.99'
col parse_count_tot for '999,999,990.99'
col parse_count_hard for '999,999,990.99'
col sort_disk for '999,999,990.99'
col logons for '999,999,990.99'
col execute_count for '999,999,990.99'
col trans for '999,999,990.99'
select
date_time,
sum(case WHEN stat_name='redo size' then round((e_val - b_val)/sec,2) else null end) redo_size,
sum(case WHEN stat_name='session logical reads' then round((e_val - b_val)/sec,2) else null end) sess_l_reads,
sum(case WHEN stat_name='db block changes' then round((e_val - b_val)/sec,2) else null end) blk_change,
sum(case WHEN stat_name='physical reads' then round((e_val - b_val)/sec,2) else null end) phy_reads,
sum(case WHEN stat_name='physical writes' then round((e_val - b_val)/sec,2) else null end) phy_writes,
sum(case WHEN stat_name='user calls' then round((e_val - b_val)/sec,2) else null end) user_calls,
--sum(case WHEN stat_name='parse count (total)' then round((e_val - b_val)/sec,2) else null end) parse_count_tot,
--sum(case WHEN stat_name='parse count (hard)' then round((e_val - b_val)/sec,2) else null end) parse_count_hard,
--sum(case WHEN stat_name='sorts (disk)' then round((e_val - b_val)/sec,2) else null end) sort_disk,
sum(case WHEN stat_name='logons cumulative' then round((e_val - b_val)/sec,2) else null end) logons,
sum(case WHEN stat_name='execute count' then round((e_val - b_val)/sec,2) else null end) execute_count,
round((sum(case WHEN stat_name='user commits' then (e_val - b_val)/sec else null end) +
sum(case WHEN stat_name='user rollbacks' then (e_val - b_val)/sec else null end)),2) trans
from
(
select
to_char(sn.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24_mi')|| to_char(sn.END_INTERVAL_TIME,'_hh24_mi') Date_Time,
b.stat_name stat_name,
e.value e_val,
b.value b_val,
(extract( day from (end_interval_time-begin_interval_time) )*24*60*60+
extract( hour from (end_interval_time-begin_interval_time) )*60*60+
extract( minute from (end_interval_time-begin_interval_time) )*60+
extract( second from (end_interval_time-begin_interval_time)) ) sec
FROM
dba_hist_sysstat b,
dba_hist_sysstat e,
dba_hist_snapshot sn
where
trunc(sn.begin_interval_time) ='&Date' and
b.snap_id(+) = e.snap_id-1
and e.snap_id = sn.snap_id
and b.dbid(+) = e.dbid
and e.dbid = (select dbid from v$database)
and sn.dbid = (select dbid from v$database)
and b.instance_number(+) = e.instance_number
and e.instance_number = (select instance_number from v$instance)
and sn.instance_number = (select instance_number from v$instance)
and b.instance_number(+) = e.instance_number
and b.stat_name = e.stat_name
and b.stat_name in (
'redo size',
'session logical reads',
'db block changes',
'physical reads',
'physical writes',
'user calls',
'parse count (total)',
'parse count (hard)',
'sorts (disk)',
'logons cumulative',
'execute count',
'transactions',
'user commits',
'user rollbacks'
)
)
group by date_time
Order by date_time
;
Enter value for date: 13-jan-12
old 32: trunc(sn.begin_interval_time) ='&Date' and
new 32: trunc(sn.begin_interval_time) ='13-jan-12' and
DATE_TIME REDO_SIZE SESS_L_READS BLK_CHANGE PHY_READS PHY_WRITES USER_CALLS LOGONS EXECUTE_COUNT TRANS
-------------------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- ------------- ----------
01/13/12_00_00_01_00 10226.85 120.02 68.66 2.69 1.55 .07 .02 4.44 .02
01/13/12_01_00_02_00 495.21 7.27 1.36 .1 .23 .07 .02 1.07 .01
01/13/12_02_00_03_00 480.02 5.44 1.2 .03 .22 .07 .02 .88 .01
01/13/12_03_00_04_00 417.28 5.71 1.02 .02 .21 .07 .02 .88 .01
01/13/12_04_00_05_00 413.48 5.25 .99 .01 .19 .07 .02 .86 .01
01/13/12_05_00_06_00 405.08 5.32 .9 0 .2 .07 .02 .78 .01
01/13/12_06_00_07_00 406.53 5.35 .91 0 .19 .07 .02 .8 .01
01/13/12_07_00_08_00 397.37 4.84 .89 0 .18 .07 .02 .79 .01
01/13/12_08_00_09_00 458.1 5.46 1.02 0 .2 .07 .02 .79 .01
01/13/12_09_00_10_00 384.42 4.72 .85 0 .19 .06 .02 .77 .01
01/13/12_10_00_11_00 400.58 6.78 .94 .08 .18 .07 .02 .87 .01
01/13/12_11_00_12_00 434.46 6.46 1.02 .19 .2 .06 .02 .8 .01
12 rows selected.
Monday 9 January 2012
Estimate the dumpfile size before taking Data pump export
If you want to estimate datapump file size then run following command
estimate datapump export size
datapump export file size
$ expdp "'/ as sysdba'" estimate_only=y full=Y
. estimated "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 0 KB
. estimated "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB
. estimated "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB
Total estimation using BLOCKS method: 257.5 MB
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 15:41:20
estimate datapump export size
datapump export file size
$ expdp "'/ as sysdba'" estimate_only=y full=Y
. estimated "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS" 0 KB
. estimated "SYSTEM"."REPCAT$_USER_PARM_VALUES" 0 KB
. estimated "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB
Total estimation using BLOCKS method: 257.5 MB
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at 15:41:20
Thursday 5 January 2012
Oracle Memory Allocation Per User / Process
-- Check Memory Allocation Per User/Process
Memory Allocation Per Process
Memory Allocation Per User
session wise memory
session memory
set linesize 150
SET PAGESIZE 9999
COLUMN sid_serial format a12 HEADING 'sid_serial#'
COLUMN sid FORMAT 999 HEADING 'SID'
COLUMN SERIAL# FORMAT 99999 HEADING 'SERIAL#'
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY left
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN session_program FORMAT a18 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a12 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA Memory'
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max'
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA Memory'
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX'
set linesize 150
SELECT
-- s.sid
s.sid||','||SERIAL# sid_serial
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,12) session_machine
, (select round(sum(ss.value/1024/1024)) from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session pga memory') session_pga_memory
, (select round(sum(ss.value/1024/1024)) from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session pga memory max') session_pga_memory_max
, (select round(sum(ss.value/1024/1024)) from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session uga memory') session_uga_memory
, (select round(sum(ss.value/1024/1024)) from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session uga memory max') session_uga_memory_max
FROM v$session s
ORDER BY session_pga_memory DESC
/
sid_serial# Oracle User O/S User Session Program Machine PGA Memory PGA Memory Max UGA Memory UGA Memory MAX
------------ ------------ --------- ------------------ ------------ -------------- -------------- -------------- --------------
21,2 oracle oracle@apt-amd-02 apt-amd-02 28 28 0 0
23,1 oracle oracle@apt-amd-02 apt-amd-02 28 28 0 0
19,5 oracle oracle@apt-amd-02 apt-amd-02 11 11 0 0
22,1 oracle oracle@apt-amd-02 apt-amd-02 11 11 0 0
11,1 oracle oracle@apt-amd-02 apt-amd-02 11 11 0 0
10,1 oracle oracle@apt-amd-02 apt-amd-02 7 32 0 0
15,1 oracle oracle@apt-amd-02 apt-amd-02 3 4 1 2
17,10 SYS oracle rman@apt-amd-02 (T apt-amd-02 2 2 0 1
13,1 oracle oracle@apt-amd-02 apt-amd-02 2 4 1 3
1,9 SYS oracle rman@apt-amd-02 (T apt-amd-02 2 24 1 3
56,622 SYS oracle sqlplus@apt-amd-02 apt-amd-02 2 2 0 0
62,47 SYS oracle sqlplus@apt-amd-02 apt-amd-02 2 14 1 6
32,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
34,3 oracle oracle@apt-amd-02 apt-amd-02 1 5 1 2
40,15 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
60,443 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
30,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 1 1
27,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
16,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
14,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 1
12,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
9,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
8,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
7,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
6,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
5,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
4,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
3,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
2,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
29 rows selected.
SET LINESIZE 300 PAGESIZE 9999
COLUMN sid FORMAT 99999 HEADING 'SID'
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN session_program FORMAT a35 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a8 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA Memory'
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max'
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA Memory'
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX'
SELECT /*+ PARALLEL(8) */
s.sid sid
, s.sql_id
, PREV_SQL_ID
,SQL_EXEC_ID
, lpad(s.username,20) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,20) session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session pga memory') session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session pga memory max') session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session uga memory') session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session uga memory max') session_uga_memory_max
FROM v$session s
ORDER BY session_pga_memory DESC
/
Oracle Date How to Add Day , Hour, Minute, Second to a Date Value
Oracle Date How to Add Day , Hour, Minute, Second to a Date Value
Oracle Date How to Add Day , Hour, Minute, Second to a Date Value in Oracle
Oracle add Days
Oracle add Hour
Oracle add Minute
Oracle add second
-- # Add a day
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') "Todays-date" , to_char(sysdate +1,'dd-mm-yyyy hh24:mi:ss') One_day from dual ;
Todays-date ONE_DAY
------------------- -------------------
05-01-2012 12:55:52 06-01-2012 12:55:52
-- # Add an hour
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') "Todays-date" , to_char(sysdate +1/24,'dd-mm-yyyy hh24:mi:ss') One_hour from dual ;
Todays-date ONE_HOUR
------------------- -------------------
05-01-2012 12:56:06 05-01-2012 13:56:06
-- # Add an Minute
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') "Todays-date" , to_char(sysdate +1/(24*60),'dd-mm-yyyy hh24:mi:ss') One_minute from dual ;
Todays-date ONE_MINUTE
------------------- -------------------
05-01-2012 12:56:18 05-01-2012 12:57:18
-- # Add Second
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') "Todays-date" , to_char(sysdate +1/(24*60*60),'dd-mm-yyyy hh24:mi:ss') One_second from dual ;
Todays-date ONE_SECOND
------------------- -------------------
05-01-2012 12:56:27 05-01-2012 12:56:28
if we want to insert into the table then
to_date(sysdate +1/(24*60*60),'dd-mm-yyyy hh24:mi:ss')
or
sysdate +1/(24*60*60)
or
SQL> select to_char(sysdate,'DD-MM-YYYY HH:MI:SS'), to_char(sysdate+interval '15' minute,'DD-MM-YYYY HH:MI:SS') from dual;
TO_CHAR(SYSDATE,'DD TO_CHAR(SYSDATE+INT
------------------- -------------------
05-01-2012 04:36:39 05-01-2012 04:51:39
Tuesday 3 January 2012
Oracle minact-scn master-status in trace file
minact-scn master-status
error message in Trace file " minact-scn master-status "
In On Oracle Version
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
In my case file location
/****us/oracle/Diag/diag/rdbms/aptdb/aptdb/trace
-rw-r-----. 1 oracle oinstall 125969 Jan 3 09:40 aptdb_mmon_2433.trc <<<<< trace file in
-bash-4.1$ tail -100 aptdb_mmon_2433.trc
*** 2012-01-03 09:30:01.182
minact-scn master-status: grec-scn:0x0000.0029e860 gmin-scn:0x0000.0029e860 gcalc-scn:0x0000.0029e860
minact-scn master-status: grec-scn:0x0000.0029e912 gmin-scn:0x0000.0029e912 gcalc-scn:0x0000.0029e912
*** 2012-01-03 09:40:07.680
minact-scn master-status: grec-scn:0x0000.0029e9b3 gmin-scn:0x0000.0029e9b3 gcalc-scn:0x0000.0029e9b3
*** 2012-01-03 09:45:08.186
minact-scn master-status: grec-scn:0x0000.0029ea76 gmin-scn:0x0000.0029ea76 gcalc-scn:0x0000.0029ea76
metalink note ID...
Minact-Scn Master-Status: Grec-Scn Messages In Trace File [ID 1361567.1]
Cause
Bug 11891463
The issue is similar with BUG 11891463 - MINACT-SCN MASTER-STATUS MESSAGES WRITTEN TO MMON TRACE FILE
solution ...
-----------
SQL> alter system set "_enable_minscn_cr"=false scope=spfile; ----- restart the database ..
System altered.
or
"_smu_debug_mode=134217728"
After setting above parameter the message will not be generated but This will disable min active SCN optimization feature.
"min active scn" introduced in 11g which is supposed to enhance delayed block cleanout operations.
Note : - Disabling min active scn optimization feature will not have any impact on database.
error message in Trace file " minact-scn master-status "
In On Oracle Version
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
In my case file location
/****us/oracle/Diag/diag/rdbms/aptdb/aptdb/trace
-rw-r-----. 1 oracle oinstall 125969 Jan 3 09:40 aptdb_mmon_2433.trc <<<<< trace file in
-bash-4.1$ tail -100 aptdb_mmon_2433.trc
*** 2012-01-03 09:30:01.182
minact-scn master-status: grec-scn:0x0000.0029e860 gmin-scn:0x0000.0029e860 gcalc-scn:0x0000.0029e860
minact-scn master-status: grec-scn:0x0000.0029e912 gmin-scn:0x0000.0029e912 gcalc-scn:0x0000.0029e912
*** 2012-01-03 09:40:07.680
minact-scn master-status: grec-scn:0x0000.0029e9b3 gmin-scn:0x0000.0029e9b3 gcalc-scn:0x0000.0029e9b3
*** 2012-01-03 09:45:08.186
minact-scn master-status: grec-scn:0x0000.0029ea76 gmin-scn:0x0000.0029ea76 gcalc-scn:0x0000.0029ea76
metalink note ID...
Minact-Scn Master-Status: Grec-Scn Messages In Trace File [ID 1361567.1]
Cause
Bug 11891463
The issue is similar with BUG 11891463 - MINACT-SCN MASTER-STATUS MESSAGES WRITTEN TO MMON TRACE FILE
solution ...
-----------
SQL> alter system set "_enable_minscn_cr"=false scope=spfile; ----- restart the database ..
System altered.
or
"_smu_debug_mode=134217728"
After setting above parameter the message will not be generated but This will disable min active SCN optimization feature.
"min active scn" introduced in 11g which is supposed to enhance delayed block cleanout operations.
Note : - Disabling min active scn optimization feature will not have any impact on database.
Subscribe to:
Posts (Atom)
