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
Subscribe to:
Posts (Atom)
