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