GPS

Forgot SYS password in Oracle

If you forget or lost both SYS and SYSTEM password, you can reset them without logging in to the database.

Here is a cool utility to reset SYS and SYSTEM password, if you ever loose or forget the passwords for these account.

This is tested in Windows box only ( orapwd should work in Unix too)
---------------------------------------------------------------------

1) Go to services ( start -> Run->Services.msc)
2) Stop all Oracle services.
3) Find PWDxxxx.ora file. It is usually located in the Oracle\product\10.x\db_x\database. xxxx stands for your Oracle SID. For example, if you database is called ORCL, then the file will be PWDorcl.ora
4) Back up the existing file (just rename the file)
5) Generate new PWDxxxx.ora file using orapwd utility.
6) Go to command prompt, type
ORAPWD file=path_to_the_PWDxxx.ora_file password=zzzz
zzzz will the new password for both SYS and SYSTEM account
7) Now you will see a new PWDxxx.ora file generated.
8) Restart your Oracle service
9) You should be able to connect to the database with the new password.

ORA-04028: cannot generate diana for object

ORA-04028: cannot generate diana for object object_name

Goes away if you recreate the object in question

I got this error when compiling a package. The error said "Cannot generate Diana for a particular view".

There was nothing wrong with view, and select * from view_name ran fine with no errors.

I dropped and recreated the view, the Diana message went away.

Oracle: Regular expression to remove html tags

There are two ways to strip HTML tags from a string



1) Try to extract everything between end html tag(>)and next starting html tag (<)



2) First approach becomes complicated when you have nested tags. So the solution is to replace everything that is between (<) and (>)



SQL below replaces all the tags with space.


Select Regexp_Replace('<td>source string <b>bold<\b> with html tags<\td>', '<[^>]+>','') from dual

Oracle: How to replace non printable characters in a string

You can replace non printable characters in a string using regexp_replace

select regexp_replace('Test'||chr(10)||chr(13)||chr(1)||' String ', '([^[:print:]])',' ') from dual

If you want to retain the the new line characters like chr(10) and chr(13) then

Select regexp_replace('Test'||chr(10)||chr(13)||chr(1)||' String ','['||chr(1)||'-'||chr(9)||']|['||chr(11)||'-'||chr(12)||']|['||chr(14)||'-'||chr(31)||']',' ') from dual

Above sql is removing chr from 1-9, 11-12 and 14-31



Here is SQL to identify printable, non printable, control characters

select level,CHR(level),
decode(chr(level), regexp_substr(chr(level), '[[:graph:]]'), '*') is_graph, --printable character
decode(chr(level), regexp_substr(chr(level), '[[:blank:]]'), '*') is_blank,
decode(chr(level), regexp_substr(chr(level), '[[:alnum:]]'), '*') is_alnum, --Any alphanumeric character, [A-Za-z0-9]
decode(chr(level), regexp_substr(chr(level), '[[:alpha:]]'), '*') is_alpha, -- Any letter, [A-Za-z]
decode(chr(level), regexp_substr(chr(level), '[[:digit:]]'), '*') is_digit, -- Any digit, [0-9]
decode(chr(level), regexp_substr(chr(level), '[[:cntrl:]]'), '*') is_cntrl, --Any character not part of the character classes: [:upper:], [:lower:], [:alpha:], [:digit:], [:punct:], [:graph:], [:print:], [:xdigit:]
decode(chr(level), regexp_substr(chr(level), '[[:lower:]]'), '*') is_lower, -- Any lowercase letter, [a-z]
decode(chr(level), regexp_substr(chr(level), '[[:upper:]]'), '*') is_upper, --Any uppercase letter, [A-Z]
decode(chr(level), regexp_substr(chr(level), '[[:print:]]'), '*') is_print, --Any printable character
decode(chr(level), regexp_substr(chr(level), '[[:punct:]]'), '*') is_punct, --Any punctuation character: START ! ' # S % & ' ( ) * + , - . / : ; < = > ? @ [ / ] ^ _ { | } ~ END
decode(chr(level), regexp_substr(chr(level), '[[:space:]]'), '*') is_space, --A tab, new line, vertical tab, form feed, carriage return, or space
decode(chr(level), regexp_substr(chr(level), '[[:xdigit:]]'), '*') is_xdigit --Any hexadecimal digit, [0-9A-Fa-f]
from dual
where level between 0 and 255
connect by level <= 256