Oracle: Disable Archive log mode

Launch SQLPLUS and login as SYS

Determine if the database is setup in archive log mode

SQL> Select log_mode from v$database

SQL> archive log list;

Archive log list gives a little bit detailed information about your archive log setup. By default, when you enable archive log, all the archive log files are written into flash_recovery_area.Below command will show you the OS path

sql> show parameter recovery_file_dest

To Disable Archive log
SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog
SQL> alter database open
SQL> archive log list

Your database is now running in NO archive log mode

Oracle: Enable Archive log mode

Launch SQLPLUS and login as SYS

Determine if the database is setup in archive log mode

SQL> Select log_mode from v$database

SQL> archive log list;

Archive log list gives a little bit detailed information about your archive log setup. By default, when you enable archive log, all the archive log files are written into flash_recovery_area.Below command will show you the OS path

SQL> show parameter recovery_file_dest

To Enable Archive log
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog
SQL> alter database open
SQL> archive log list

Your database is now running in archive log mode

Oracle: Import 11g data dump into 10g

You can use Oracle data dump to export data dump from 11g server, and import into 10g using the data Pump parameter called Version. When you use Version, Datapump exports all objects that are compatible for your target version

So, If your database is 11g and you want to export 11g and import into 10g

from 11g db cmd promt> expdp Test_schema_name/passs directory=datapump schemas=Test_Schema_Name Version=10.2.0.4.0

Once the export is done, you do the regular import from 10g server.

Oracle: Convert XMLType to Varchar2

Use xmltype.getStringVal() or xmltype.getClobVal() based on the size.

declare
myxml_type xmltype;
mystr Varchar2(4000);
begin
mystr :=myxml_type.getStringVal();
end;
/

Explain Plan table in Toad

There are 2 options

1. Use PLAN_TABLE that comes with the schema
2. Use TOAD_PLAN_TABLE (Created by Toad)

Option 1

Query user table for PLAN_TABLE

Select * from user_tables where table_name ='PLAN_TABLE'

Set this table in Toad Menu

View->Toad Options-> Select Oracle

on the right had side, you will see options for Explain Plan

Option 2

You can create TOAD_PLAN_TABLE, by running TOADPREP.SQL (Found in older version of Toads). This is no longer available in Toad 10

ORA-01172:recovery of thread 1 stuck at block xxxx of file x

Lately, I've been plagued with all sorts of Oracle start up errors on my Vista box. I have Oracle 11gR2 installed, and it seems to crash everytime I force reboot my vista box

Yesterday I got,

ORA-01172:recovery of thread 1 stuck at block xxxx of file x

To solve

1) Open SqlPlus from Oracle Bin folder, login as sys/zzz as sysdba
2) Since the database is not open, you can only query on fixed tables/Views
3) Query on v$datafile for the file name in the error message

If the error is "ORA-01172:recovery of thread 1 stuck at block 1241 of file 3",

then Select name from v$datafile where file#=3

4) Recover datafile name_from_the_above_select
5) If file is recovered, then try
Alter database open

6) In my case, I got some weird ora-6000 [4194], So I did a shutdown and startup force, which resovled my issue

7) Shutdown immediate
8) Startup force

Ubuntu Wireless driver for Dell

After installing Ubuntu, go to additional drivers and install Broadcam STA Wireless driver.

This driver enabled my Wireless card ( Dell Wireless 1510), and works great.

Moved blog address

Please visit my new blog for updates

http://rmanni.blogspot.com

ORA-12528: TNS:Listener: All Appropriate instances are blocking new connections

• Go to the database server box
• Navigate to the bin folder and launch SQLPLUS (Usually C:\app\rmanni\product\11.1.0\db_1\BIN )

Luanch sqlplus and connect to the database as SYS without using @TNSAlias

ex: conn sys/sys as sysdba

Once you are in, do a

• Shutdown
• Startup

You are good to go.

Oracle 11g Installation: File not found

Oracle 11g Release 2 Installation files contains 2 zip files.

1. win32_11gR2_database_1of2.zip
2. win32_11gR2_database_2of2.zip

You need to unzip both the files into the same location. Unzipping the first one, will not unzip the second file.

Pretty lame, but you have to unzip both.

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor is the most common and most frustrating error in Oracle

What it means is, In tnsnams.ora, the service_name parameter is in correct or not available

TNS_Service =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = machine_name)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = Database_Name)
)
)

In my instance, I have Oracle hosted on a Windows box, and my OracleServiceDatabase_Name
was not started.

Once I started the Windows service , I was able to connect to the database

Start->Run->Services.msc

Locate the OracleServiceDatabase_Name, and right click and start the service.

CTRL ALT DEL on Remote Desktop

If you remote into desktop, and the desktop locks out, CTRL ALT DEL, will only bring your machine's screen.

To send CTRL ATL DEL for Remote pc, use


CTRL ALT END

CTRL ALT DEL on Remote Desktop

If you remote into desktop, and the desktop locks out, CTRL ALT DEL, will only bring your machine's screen.

To send CTRL ATL DEL for Remote pc, use


CTRL ALT END

Regular expression to split comma separated values

with test as
(select '5/10/2007,36352737,,"first_name, last_name Jr.",211001,7204,-75.00' str from dual)
Select regexp_substr(str,'".*"|[^,]+', 1, level)
from test
connect by regexp_substr(str,'".*"|[^,]+', 1, level) is not null

Oracle: Spool to Excel from SQLPlus

Spooling the output from SQLPlus to log file is often in unreadable format.

To Spool SQLPLus output to Excel (.xls) file, the trick is to turn On "MARKUP HTML"

SQL>set markup html on
SQL>set feedback off

SQL>spool c:\test.xls

SQL>select * from user_objects where rownum <= 20;
SQL>spool off

SQL>set markup html off

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

Vedas

Vedas are holy books of Hinduism, written in Sanskrit sometime between 1500 and 500BC

There are 4 Vedas (Sacred Texts)

1. Rig Veda : Knowledge of Praise. It contains Hymns(verses) praising gods
2. Sama Veda : Knowledge of Sacred Songs(chants). It contains Hymns recited as religious services
3. Yajur Veda : Knowledge of Rites(Sacrifices).Its divided into 2 parts, Black Yajurveda Taittiriy & White Yajurveda Vajasaneyi
4. Athara Veda :

More to come...

Vedas

Vedas are holy books of Hinduism, written in Sanskrit sometime between 1500 and 500BC

There are 4 Vedas (Sacred Texts)

1. Rig Veda : Knowledge of Praise. It contains Hymns(verses) praising gods
2. Sama Veda : Knowledge of Sacred Songs(chants). It contains Hymns recited as religious services
3. Yajur Veda : Knowledge of Rites(Sacrifices).Its divided into 2 parts, Black Yajurveda Taittiriy & White Yajurveda Vajasaneyi
4. Athara Veda :

More to come...

கடவுள் இல்லை என்பவனை நம்பலாம்....
கடவுள் இருக்கு என்பவனையும் நம்பலாம் ..
ஆனால் நான் தான் கடவுள் என்று சொல்பவனை என்றும் நம்பவே கூடாது

PLS-00306 WRONG number or types of arguments in call to XMLAGG

This a bug in Oracle. This error is thrown when you use ORDER BY clause with XMLAGG in PL/SQL.

Work Around: Remove the ORDER BY Clause

Funny part is, the SQL will work fine in SQLPLUS, but when you use in PL/SQL it will throws the above error.

Versions confirmed as being affected: 9.2.0.2 to 9.2.0.6

This bug is fixed from 9.2.0.7 onwards

Dell: AC Power Adapter Type Cannot Be Determined

Well, My New XPS 13 started to act strange, It wont charge when I plug in the power adapter and would display "

AC Power Adapter Type Cannot Be Determined"

After struggling for hours, found the issue.

If you look at the power cable that plugs into your laptop, you should see a small pin (male pin) inside. This pin was broke in my case, and hence my battery wont charge and Dell displays a strange message.

I bought a cheap replacement power cable from ebay ( $10-$15) and problem is gone.

Dell: AC Power Adapter Type Cannot Be Determined

Well, My New XPS 13 started to act strange, It wont charge when I plug in the power adapter and would display "

AC Power Adapter Type Cannot Be Determined"

After struggling for hours, found the issue.

If you look at the power cable that plugs into your laptop, you should see a small pin (male pin) inside. This pin was broke in my case, and hence my battery wont charge and Dell displays a strange message.

I bought a cheap replacement power cable from ebay ( $10-$15) and problem is gone.

ORA-38104: Columns referenced in the ON Clause cannot be updated

When you use Merge statement, you cannot update the columns reference in the ON Clause.

Not many know that, Merge supports "WHERE Clause".

Add a Where Clause to your Update statement under "When Matched"

Merge Into ...
Using ()
On() 
When Matched then
Update ...
Where ...
When Not Matched then
......

ORA-28056: Writing audit records to Windows Event Log failed

I started getting this error after installing Oracle llg database.

Here are couple of options to fix it.

Open Event Viewer: Click Start, click Control Panel,click Administrative Tools,
and then double-click Event Viewer.

1) Clear Application log. Right click on Application, Select clear all events

2) Or, Increase the Application log size. Right click on Application, Select properties, Increase Maximum log size.

3) Or, Change Max log size Over write options. Select "Overwrite events as needed"

ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_X'

Can't figure out why your SELECT statement is throwing 'OGC_X' error ?

1) Most probably, your SELECT has a table with an alias "x"
2) Your oracle is version is lower than 11g
3) You probably mistyped column name

Solution
Prior to Oracle 11g, Oracle by default comes with a synonym 'X' defined for "OGC_X".

1) In your SELECT, if you mistype the column name from the table aliased as "x", Oracle tries to use the Synonym object "OGC_X", and hence you get the error.

Following SQL works
select * from dual x where x.dummy = 'X'

Now change, x.dummy to x.fummy (wrong column name), you will the error

select * from dual x where x.fummy = 'X'

In summary, if your table alias matches any synonym in your database and you typed the column name of the aliased table wrong, you get the error "ORA-06553: PLS-306: wrong number or types of arguments in call to ..."

Ultra Edit option missing in right click Context Menu

If you don't see Ultra Edit in your context menu

Open UltraEdit, Click Menu Advanced->Configuration->File Associations -> Check Intergrate with Explorer as check box