Create table emp ( deptno int, ename varchar2(20));
SET DEFINE OFF;
Insert into EMP (DEPTNO, ENAME) Values (10, 'Mike');
Insert into EMP (DEPTNO, ENAME) Values (10, 'Nick');
Insert into EMP (DEPTNO, ENAME) Values (10, 'Carol');
Insert into EMP (DEPTNO, ENAME) Values (20, 'Jack');
Insert into EMP (DEPTNO, ENAME) Values (20, 'Joe');
Insert into EMP (DEPTNO, ENAME) Values (20, 'Janis');
Insert into EMP (DEPTNO, ENAME) Values (20, 'Chris');
Insert into EMP (DEPTNO, ENAME) Values (30, 'Adam');
COMMIT;
Select deptno,substr(max(sys_connect_by_path(ename,';')),2) ename_list
from (
Select deptno, ename, row_number() over(partition by deptno order by ename) r
from emp )
start with r = 1
connect by prior deptno = deptno
and prior r+1 = r
group by deptno
Though the sql looks complex, If you analyze the sql, there is
1) Row_number() which generates a running sequential number per deptno.
2) Connect by Deptno and rownumber
3) Get the max of the list using sys_connect_by_path and group by
4) Substr to remove the first delimiter.
Monday, November 9, 2009
Sunday, November 8, 2009
How to convert flac to Wav
Install Flac.exe
Open Command Prompt, Navigate to Flac.exe ( Usually c:\Program files\Flac)
flac -d "c:\Temp\Your_Song.flac"
Your_Song.wav will be generated in the same folder
Open Command Prompt, Navigate to Flac.exe ( Usually c:\Program files\Flac)
flac -d "c:\Temp\Your_Song.flac"
Your_Song.wav will be generated in the same folder
Saturday, November 7, 2009
Wednesday, November 4, 2009
How to move tablespace from one folder to another
This method only works for User created tablespaces. It does not require to shutdown your database.
If you need to move SYSTEM tablespaces, refer to link
In my example, I have 2 tablespaces named contm_data and contm_indx. They were created in Datapump folder by mistake. I had to move them one folder up.
Log in as SYS
Alter tablesapce contm_data offline;
Alter tablesapce contm_indx offline;
Manually copy/move the data files from old location ('E:\Oracle\OraData\PRODDB\DATAPUMP) to new location ('E:\Oracle\OraData\PRODDB)
alter tablespace contm_data rename datafile 'E:\Oracle\OraData\PRODDB\DATAPUMP\CONTM_DATA.DBF' to 'E:\Oracle\OraData\PRODDB\CONTM_DATA.DBF';
alter tablespace contm_indx rename datafile 'E:\Oracle\OraData\PRODDB\DATAPUMP\CONTM_INDX.DBF' to 'E:\Oracle\OraData\PRODDB\CONTM_INDX.DBF';
Alter tablesapce contm_data online;
Alter tablesapce contm_indx online;
If you need to move SYSTEM tablespaces, refer to link
In my example, I have 2 tablespaces named contm_data and contm_indx. They were created in Datapump folder by mistake. I had to move them one folder up.
Log in as SYS
Alter tablesapce contm_data offline;
Alter tablesapce contm_indx offline;
Manually copy/move the data files from old location ('E:\Oracle\OraData\PRODDB\DATAPUMP) to new location ('E:\Oracle\OraData\PRODDB)
alter tablespace contm_data rename datafile 'E:\Oracle\OraData\PRODDB\DATAPUMP\CONTM_DATA.DBF' to 'E:\Oracle\OraData\PRODDB\CONTM_DATA.DBF';
alter tablespace contm_indx rename datafile 'E:\Oracle\OraData\PRODDB\DATAPUMP\CONTM_INDX.DBF' to 'E:\Oracle\OraData\PRODDB\CONTM_INDX.DBF';
Alter tablesapce contm_data online;
Alter tablesapce contm_indx online;
Tuesday, November 3, 2009
Sharing TNSNames.Ora
If you have multiple oracle client installations (9i, 10g, 11i) on your box and don't want to configure tnsnames.ora for each oracle home, you can use tnsname sharing.
By sharing, you maintain 1 tnsnames.ora file and point all other tnsnames.ora (from all other home)
to the main file. Use ifile parameter to point to another location
example:
tnsnames.ora from 11i client will just have the following line. In this example it points to 10g client.
ifile=C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
By sharing, you maintain 1 tnsnames.ora file and point all other tnsnames.ora (from all other home)
to the main file. Use ifile parameter to point to another location
example:
tnsnames.ora from 11i client will just have the following line. In this example it points to 10g client.
ifile=C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
Thursday, October 29, 2009
Oracle: How to find patches installed on Oracle
Launch command prompt
1) Navigate to Oracle Home Directory
Your Command prompt should look like
c:\app\machine_name\product\11.x.x.\db_1> for 11g
c:\oracle\product\10.x.x\db_1> for 10g.
2) Now set Oracle_HOME to the Oracle homes path, In my case for 10g
c:\oracle\product\10.x.x\db_1> SET ORACLE_HOME= c:\oracle\product\10.x.x\db_1
3) Navigate to Opatch directory
c:\oracle\product\10.x.x\db_1> cd opatch
c:\oracle\product\10.x.x\db_1\opatch>
4) type opatch lsinventory
c:\oracle\product\10.x.x\db_1\opatch>opatch lsinventory
You will get a report on the patches installed.
1) Navigate to Oracle Home Directory
Your Command prompt should look like
c:\app\machine_name\product\11.x.x.\db_1> for 11g
c:\oracle\product\10.x.x\db_1> for 10g.
2) Now set Oracle_HOME to the Oracle homes path, In my case for 10g
c:\oracle\product\10.x.x\db_1> SET ORACLE_HOME= c:\oracle\product\10.x.x\db_1
3) Navigate to Opatch directory
c:\oracle\product\10.x.x\db_1> cd opatch
c:\oracle\product\10.x.x\db_1\opatch>
4) type opatch lsinventory
c:\oracle\product\10.x.x\db_1\opatch>opatch lsinventory
You will get a report on the patches installed.
Sunday, October 18, 2009
how to export all filenames in a folder to a text file
Well, I found a very simple solution to this.
Launch Windows command prompt, Navigate to the root folder ( say c:\temp)
C:\Temp> dir /s /b > filenames.txt
/s -> includes all subdirectories
/b -> will inlcude only file name ( no attributes, date created etc)
Launch Windows command prompt, Navigate to the root folder ( say c:\temp)
C:\Temp> dir /s /b > filenames.txt
/s -> includes all subdirectories
/b -> will inlcude only file name ( no attributes, date created etc)
Wednesday, October 14, 2009
Oracle: Sql to find table and column comments
Select t.table_name,tc.comments table_comments,c.column_name,data_type,cc.comments column_comments
from user_tables t, user_tab_columns c,user_tab_comments tc,user_col_comments cc
where t.table_name = c.table_name
and t.table_name = tc.table_name(+)
and c.table_name = cc.table_name (+)
and c.column_name = cc.column_name (+)
order by table_name,column_id
from user_tables t, user_tab_columns c,user_tab_comments tc,user_col_comments cc
where t.table_name = c.table_name
and t.table_name = tc.table_name(+)
and c.table_name = cc.table_name (+)
and c.column_name = cc.column_name (+)
order by table_name,column_id
Friday, October 9, 2009
enq: TM - contention
It took me a while to resolve this issue on my database. Every single resource on the Net pointed to "missing indexes on foreign key columns", but I found it was due to another session locking my database.
The following query gave me a list of blocking sessions. Once I killed those sessions,
"enq: TM - contention" went away.
Select v.sid,s.blocking_session, s.event, v.type,v.lmode,v.request,o.object_name, o.object_type
from v$lock v ,dba_objects o, v$session s
where v.id1 = o.object_id
and v.sid = s.sid
and owner ="Put_your_Owner_Name_Here"
The following query gave me a list of blocking sessions. Once I killed those sessions,
"enq: TM - contention" went away.
Select v.sid,s.blocking_session, s.event, v.type,v.lmode,v.request,o.object_name, o.object_type
from v$lock v ,dba_objects o, v$session s
where v.id1 = o.object_id
and v.sid = s.sid
and owner ="Put_your_Owner_Name_Here"
enq: TM - contention
It took me a while to resolve this issue on my database. Every single resource on the Net pointed to "missing indexes on foreign key columns", but I found it was due to another session locking my database.
The following query gave me a list of blocking sessions. Once I killed those sessions,
"enq: TM - contention" went away.
Select v.sid,s.blocking_session, s.event, v.type,v.lmode,v.request,o.object_name, o.object_type
from v$lock v ,dba_objects o, v$session s
where v.id1 = o.object_id
and v.sid = s.sid
and owner ="Put_your_Owner_Name_Here"
The following query gave me a list of blocking sessions. Once I killed those sessions,
"enq: TM - contention" went away.
Select v.sid,s.blocking_session, s.event, v.type,v.lmode,v.request,o.object_name, o.object_type
from v$lock v ,dba_objects o, v$session s
where v.id1 = o.object_id
and v.sid = s.sid
and owner ="Put_your_Owner_Name_Here"
Wednesday, October 7, 2009
Monday, October 5, 2009
ORA-39149: cannot link privileged user to non-privileged user
When you use Network_link parameter to import a database dump, you will get the error
"ORA-39149: cannot link privileged user to non-privileged user", if the EXP_FULL_DATABASE privilege is missing on the source schema.
Log in as SYS/System and grant the following privilege.
Grant EXP_FULL_DATABASE TO schema_owner;
"ORA-39149: cannot link privileged user to non-privileged user", if the EXP_FULL_DATABASE privilege is missing on the source schema.
Log in as SYS/System and grant the following privilege.
Grant EXP_FULL_DATABASE TO schema_owner;
Tuesday, September 29, 2009
Script to enable all Constraints
declare
Cursor cCons is select table_name,constraint_name
from user_constraints
where status = 'DISABLED';
--order by decode(constraint_type,'R','A',constraint_type);
type t is table of cCons%rowtype index by binary_integer;
all_constraints t;
nLimit int := 100;
begin
open cCons;
loop
Fetch cCons Bulk Collect into all_constraints limit nLimit;
for i in 1..all_constraints.count loop
begin
execute immediate 'Alter table '||all_constraints(i).table_name||' modify constraint '|| all_constraints(i).constraint_name ||' enable validate';
--Exception
-- WHEN Others then
-- null; -- to ignore globalmedi_intermedia system generated constriants
end;
end loop;
exit when all_constraints.count < nLimit;
end loop;
Close cCons;
end;
/
Cursor cCons is select table_name,constraint_name
from user_constraints
where status = 'DISABLED';
--order by decode(constraint_type,'R','A',constraint_type);
type t is table of cCons%rowtype index by binary_integer;
all_constraints t;
nLimit int := 100;
begin
open cCons;
loop
Fetch cCons Bulk Collect into all_constraints limit nLimit;
for i in 1..all_constraints.count loop
begin
execute immediate 'Alter table '||all_constraints(i).table_name||' modify constraint '|| all_constraints(i).constraint_name ||' enable validate';
--Exception
-- WHEN Others then
-- null; -- to ignore globalmedi_intermedia system generated constriants
end;
end loop;
exit when all_constraints.count < nLimit;
end loop;
Close cCons;
end;
/
Script to Enable All Triggers
Declare
Cursor cTriggers is Select table_name from user_tables;
type t is table of cTriggers%rowtype index by binary_integer;
all_triggers t;
nLimit int := 100;
begin
Open cTriggers;
loop
Fetch cTriggers Bulk Collect into all_triggers limit nLimit;
for i in 1..all_triggers.count loop
Execute Immediate ' Alter table '||all_triggers(i).table_name||' enable all triggers';
end loop;
Exit when all_triggers.count < nLimit;
end loop;
Close cTriggers;
end;
/
Cursor cTriggers is Select table_name from user_tables;
type t is table of cTriggers%rowtype index by binary_integer;
all_triggers t;
nLimit int := 100;
begin
Open cTriggers;
loop
Fetch cTriggers Bulk Collect into all_triggers limit nLimit;
for i in 1..all_triggers.count loop
Execute Immediate ' Alter table '||all_triggers(i).table_name||' enable all triggers';
end loop;
Exit when all_triggers.count < nLimit;
end loop;
Close cTriggers;
end;
/
Script to disable All Constraints
declare
Cursor cCons is select table_name,constraint_name
from user_constraints
where status = 'ENABLED'
order by decode(constraint_type,'R','A',constraint_type);
type t is table of cCons%rowtype index by binary_integer;
all_constraints t;
nLimit int := 100;
begin
open cCons;
loop
Fetch cCons Bulk Collect into all_constraints limit nLimit;
for i in 1..all_constraints.count loop
begin
execute immediate 'Alter table '||all_constraints(i).table_name||' disable constraint '|| all_constraints(i).constraint_name;
Exception
WHEN Others then
null; -- to ignore any system generated constriants for BLOB/CLOB columns
end;
end loop;
exit when all_constraints.count < nLimit;
end loop;
Close cCons;
end;
/
Cursor cCons is select table_name,constraint_name
from user_constraints
where status = 'ENABLED'
order by decode(constraint_type,'R','A',constraint_type);
type t is table of cCons%rowtype index by binary_integer;
all_constraints t;
nLimit int := 100;
begin
open cCons;
loop
Fetch cCons Bulk Collect into all_constraints limit nLimit;
for i in 1..all_constraints.count loop
begin
execute immediate 'Alter table '||all_constraints(i).table_name||' disable constraint '|| all_constraints(i).constraint_name;
Exception
WHEN Others then
null; -- to ignore any system generated constriants for BLOB/CLOB columns
end;
end loop;
exit when all_constraints.count < nLimit;
end loop;
Close cCons;
end;
/
Script to disable All Triggers
Declare
Cursor cTriggers is Select table_name from user_tables;
type t is table of cTriggers%rowtype index by binary_integer;
all_triggers t;
nLimit int := 100;
begin
Open cTriggers;
loop
Fetch cTriggers Bulk Collect into all_triggers limit nLimit;
for i in 1..all_triggers.count loop
Execute Immediate ' Alter table '||all_triggers(i).table_name||' disable all triggers';
end loop;
Exit when all_triggers.count < nLimit;
end loop;
Close cTriggers;
end;
/
Cursor cTriggers is Select table_name from user_tables;
type t is table of cTriggers%rowtype index by binary_integer;
all_triggers t;
nLimit int := 100;
begin
Open cTriggers;
loop
Fetch cTriggers Bulk Collect into all_triggers limit nLimit;
for i in 1..all_triggers.count loop
Execute Immediate ' Alter table '||all_triggers(i).table_name||' disable all triggers';
end loop;
Exit when all_triggers.count < nLimit;
end loop;
Close cTriggers;
end;
/
Tuesday, September 22, 2009
Sunday, September 20, 2009
Saturday, September 19, 2009
Windows: Task Manager won't open
You may have virus/Malware installed on your computer.
Click Start- > Run -> TaskMgr.exe
It it doesn't open
Start ->Run->Regedit
Navigate to
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\taskmgr.exe
Remove the value for the Field Debugger.
Click Start- > Run -> TaskMgr.exe
It it doesn't open
Start ->Run->Regedit
Navigate to
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\taskmgr.exe
Remove the value for the Field Debugger.
Wednesday, September 16, 2009
No Read Access to System Tables. Modify Access db before retrying
You may get this message, if you use Oracle SQL Developer to migrate Access data.
To fix this:
Open Access database, go to Tools->Security->User Groups & Permissions
Select each system tables (They all start with MSys), Check all the Check boxes under Permissions.
If the system tables are not visible, go to Tools-> Options -> View tab, and check System Tables
Save.
Now retry from SQL Developer.
To fix this:
Open Access database, go to Tools->Security->User Groups & Permissions
Select each system tables (They all start with MSys), Check all the Check boxes under Permissions.
If the system tables are not visible, go to Tools-> Options -> View tab, and check System Tables
Save.
Now retry from SQL Developer.
Tuesday, September 15, 2009
Oracle: How to convert CLOB to BLOB
FUNCTION clob_to_blob(p_clob IN CLOB) RETURN BLOB IS
v_blob BLOB;
v_offset NUMBER DEFAULT 1;
v_amount NUMBER DEFAULT 4096;
v_offsetwrite NUMBER DEFAULT 1;
v_amountwrite NUMBER;
v_buffer VARCHAR2(4096 CHAR);
BEGIN
dbms_lob.createtemporary(v_blob, TRUE);
Begin
LOOP
dbms_lob.READ(p_clob, v_amount, v_offset, v_buffer);
v_amountwrite := utl_raw.length(utl_raw.cast_to_raw(v_buffer));
dbms_lob.WRITE(v_blob, v_amountwrite, v_offsetwrite, utl_raw.cast_to_raw(v_buffer));
v_offsetwrite := v_offsetwrite + v_amountwrite;
v_offset := v_offset + v_amount;
v_amount := 4096;
END LOOP;
EXCEPTION
WHEN no_data_found THEN
NULL;
End;
RETURN v_blob;
END clob_to_blob;
v_blob BLOB;
v_offset NUMBER DEFAULT 1;
v_amount NUMBER DEFAULT 4096;
v_offsetwrite NUMBER DEFAULT 1;
v_amountwrite NUMBER;
v_buffer VARCHAR2(4096 CHAR);
BEGIN
dbms_lob.createtemporary(v_blob, TRUE);
Begin
LOOP
dbms_lob.READ(p_clob, v_amount, v_offset, v_buffer);
v_amountwrite := utl_raw.length(utl_raw.cast_to_raw(v_buffer));
dbms_lob.WRITE(v_blob, v_amountwrite, v_offsetwrite, utl_raw.cast_to_raw(v_buffer));
v_offsetwrite := v_offsetwrite + v_amountwrite;
v_offset := v_offset + v_amount;
v_amount := 4096;
END LOOP;
EXCEPTION
WHEN no_data_found THEN
NULL;
End;
RETURN v_blob;
END clob_to_blob;
Oracle: How to convert CLOB to BLOB
FUNCTION clob_to_blob(p_clob IN CLOB) RETURN BLOB IS
v_blob BLOB;
v_offset NUMBER DEFAULT 1;
v_amount NUMBER DEFAULT 4096;
v_offsetwrite NUMBER DEFAULT 1;
v_amountwrite NUMBER;
v_buffer VARCHAR2(4096 CHAR);
BEGIN
dbms_lob.createtemporary(v_blob, TRUE);
Begin
LOOP
dbms_lob.READ(p_clob, v_amount, v_offset, v_buffer);
v_amountwrite := utl_raw.length(utl_raw.cast_to_raw(v_buffer));
dbms_lob.WRITE(v_blob, v_amountwrite, v_offsetwrite, utl_raw.cast_to_raw(v_buffer));
v_offsetwrite := v_offsetwrite + v_amountwrite;
v_offset := v_offset + v_amount;
v_amount := 4096;
END LOOP;
EXCEPTION
WHEN no_data_found THEN
NULL;
End;
RETURN v_blob;
END clob_to_blob;
v_blob BLOB;
v_offset NUMBER DEFAULT 1;
v_amount NUMBER DEFAULT 4096;
v_offsetwrite NUMBER DEFAULT 1;
v_amountwrite NUMBER;
v_buffer VARCHAR2(4096 CHAR);
BEGIN
dbms_lob.createtemporary(v_blob, TRUE);
Begin
LOOP
dbms_lob.READ(p_clob, v_amount, v_offset, v_buffer);
v_amountwrite := utl_raw.length(utl_raw.cast_to_raw(v_buffer));
dbms_lob.WRITE(v_blob, v_amountwrite, v_offsetwrite, utl_raw.cast_to_raw(v_buffer));
v_offsetwrite := v_offsetwrite + v_amountwrite;
v_offset := v_offset + v_amount;
v_amount := 4096;
END LOOP;
EXCEPTION
WHEN no_data_found THEN
NULL;
End;
RETURN v_blob;
END clob_to_blob;
Oracle: How to convert BLOB to CLOB
FUNCTION blob2clob(p_blob IN BLOB) RETURN CLOB IS
v_clob CLOB;
v_amount NUMBER DEFAULT 2000;
v_offset NUMBER DEFAULT 1;
v_buffer VARCHAR2(32767);
v_length PLS_INTEGER := dbms_lob.getlength(p_blob);
BEGIN
dbms_lob.createtemporary(v_clob, TRUE);
dbms_lob.OPEN(v_clob, dbms_lob.lob_readwrite);
WHILE v_offset <= v_length LOOP
v_buffer := utl_raw.cast_to_varchar2(dbms_lob.substr(p_blob, v_amount, v_offset));
IF length(v_buffer) > 0 THEN
dbms_lob.writeappend(v_clob, length(v_buffer), v_buffer);
END IF;
v_offset := v_offset + v_amount;
EXIT WHEN v_offset > v_length;
END LOOP;
RETURN v_clob;
END blob2clob;
----------------------------------------------------------------
FUNCTION blob_to_clob (blob_in IN BLOB) RETURN CLOB
AS
v_clob CLOB;
v_varchar VARCHAR2(32767);
v_start PLS_INTEGER := 1;
v_buffer PLS_INTEGER := 32767;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
LOOP
v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));
DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
v_start := v_start + v_buffer;
END LOOP;
RETURN v_clob;
END blob_to_clob;
v_clob CLOB;
v_amount NUMBER DEFAULT 2000;
v_offset NUMBER DEFAULT 1;
v_buffer VARCHAR2(32767);
v_length PLS_INTEGER := dbms_lob.getlength(p_blob);
BEGIN
dbms_lob.createtemporary(v_clob, TRUE);
dbms_lob.OPEN(v_clob, dbms_lob.lob_readwrite);
WHILE v_offset <= v_length LOOP
v_buffer := utl_raw.cast_to_varchar2(dbms_lob.substr(p_blob, v_amount, v_offset));
IF length(v_buffer) > 0 THEN
dbms_lob.writeappend(v_clob, length(v_buffer), v_buffer);
END IF;
v_offset := v_offset + v_amount;
EXIT WHEN v_offset > v_length;
END LOOP;
RETURN v_clob;
END blob2clob;
----------------------------------------------------------------
FUNCTION blob_to_clob (blob_in IN BLOB) RETURN CLOB
AS
v_clob CLOB;
v_varchar VARCHAR2(32767);
v_start PLS_INTEGER := 1;
v_buffer PLS_INTEGER := 32767;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
LOOP
v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));
DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
v_start := v_start + v_buffer;
END LOOP;
RETURN v_clob;
END blob_to_clob;
Monday, August 24, 2009
ORA-00214: controlfile inconsistent
One of my database crashed with this error, and here is how I fixed it.
ORA-00214: controlfile inconsistent
Error I received
ORA-00214: controlfile 'C:\ORACLE\ORADATA\ORCLDB\CONTROL01.CTL' version 593
inconsistent with file 'C:\ORACLE\ORADATA\ORCLDB\CONTROL03.CTL' version 587
SQL> shutdown immediate;
SQL> create pfile from spfile;
SQL> shutdown immediate;
Open the pfile, locate "*.control_files", It will look similar to like below
*.control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL01.CTL','C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL02.CTL','C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL03.CTL'
Since one of the 3 control file is inconsistent, I removed the second and third and saved the file.
*.control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL01.CTL'
SQL> startup pfile=C:\oracle\ora92\database\INITorcldb.ORA
ORA-00214: controlfile 'C:\ORACLE\ORADATA\ORCLDB\CONTROL01.CTL' version 593
inconsistent with file 'C:\ORACLE\ORADATA\ORCLDB\CONTROL02.CTL' version 589
Now remove first and third, try with control file 2
*.control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL02.CTL'
SQL> startup pfile=C:\oracle\ora92\database\INITorcldb.ORA
Database mounted.
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\ORCLDB\REDO01.LOG'
SQL> shutdown immediate;
Now create spfile from modified pfile, so it is used by default.
SQL> create spfile from pfile='C:\oracle\ora92\database\INITorcldb.ORA';
SQL> startup mount
SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> alter database open
ERROR at line 1:
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\ORCLDB\REDO01.LOG'
SQL> recover database until cancel;
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\ORCLDB\SYSTEM01.DBF'
SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\ORCLDB\SYSTEM01.DBF'
SQL> shutdown immediate;
SQL> startup force
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
Specify the log file to use
SQL> Recover database using backup controlfile;
ORA-00279: change 3117356 generated at 04/08/2008 08:49:26 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC00053.001
ORA-00280: change 3117356 for thread 1 is in sequence #53
Specify log: {=suggested | filename | AUTO | CANCEL}
C:\oracle\oradata\ORCLDB\REDO01.LOG
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 370220868 bytes
Fixed Size 454468 bytes
Variable Size 343932928 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
ORA-00214: controlfile inconsistent
Error I received
ORA-00214: controlfile 'C:\ORACLE\ORADATA\ORCLDB\CONTROL01.CTL' version 593
inconsistent with file 'C:\ORACLE\ORADATA\ORCLDB\CONTROL03.CTL' version 587
SQL> shutdown immediate;
SQL> create pfile from spfile;
SQL> shutdown immediate;
Open the pfile, locate "*.control_files", It will look similar to like below
*.control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL01.CTL','C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL02.CTL','C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL03.CTL'
Since one of the 3 control file is inconsistent, I removed the second and third and saved the file.
*.control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL01.CTL'
SQL> startup pfile=C:\oracle\ora92\database\INITorcldb.ORA
ORA-00214: controlfile 'C:\ORACLE\ORADATA\ORCLDB\CONTROL01.CTL' version 593
inconsistent with file 'C:\ORACLE\ORADATA\ORCLDB\CONTROL02.CTL' version 589
Now remove first and third, try with control file 2
*.control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL02.CTL'
SQL> startup pfile=C:\oracle\ora92\database\INITorcldb.ORA
Database mounted.
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\ORCLDB\REDO01.LOG'
SQL> shutdown immediate;
Now create spfile from modified pfile, so it is used by default.
SQL> create spfile from pfile='C:\oracle\ora92\database\INITorcldb.ORA';
SQL> startup mount
SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> alter database open
ERROR at line 1:
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\ORCLDB\REDO01.LOG'
SQL> recover database until cancel;
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\ORCLDB\SYSTEM01.DBF'
SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\ORCLDB\SYSTEM01.DBF'
SQL> shutdown immediate;
SQL> startup force
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
Specify the log file to use
SQL> Recover database using backup controlfile;
ORA-00279: change 3117356 generated at 04/08/2008 08:49:26 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC00053.001
ORA-00280: change 3117356 for thread 1 is in sequence #53
Specify log: {
C:\oracle\oradata\ORCLDB\REDO01.LOG
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 370220868 bytes
Fixed Size 454468 bytes
Variable Size 343932928 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
ORA-00214: controlfile inconsistent
One of my database crashed with this error, and here is how I fixed it.
ORA-00214: controlfile inconsistent
Error I received
ORA-00214: controlfile 'C:\ORACLE\ORADATA\ORCLDB\CONTROL01.CTL' version 593
inconsistent with file 'C:\ORACLE\ORADATA\ORCLDB\CONTROL03.CTL' version 587
SQL> shutdown immediate;
SQL> create pfile from spfile;
SQL> shutdown immediate;
Open the pfile, locate "*.control_files", It will look similar to like below
*.control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL01.CTL','C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL02.CTL','C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL03.CTL'
Since one of the 3 control file is inconsistent, I removed the second and third and saved the file.
*.control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL01.CTL'
SQL> startup pfile=C:\oracle\ora92\database\INITorcldb.ORA
ORA-00214: controlfile 'C:\ORACLE\ORADATA\ORCLDB\CONTROL01.CTL' version 593
inconsistent with file 'C:\ORACLE\ORADATA\ORCLDB\CONTROL02.CTL' version 589
Now remove first and third, try with control file 2
*.control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL02.CTL'
SQL> startup pfile=C:\oracle\ora92\database\INITorcldb.ORA
Database mounted.
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\ORCLDB\REDO01.LOG'
SQL> shutdown immediate;
Now create spfile from modified pfile, so it is used by default.
SQL> create spfile from pfile='C:\oracle\ora92\database\INITorcldb.ORA';
SQL> startup mount
SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> alter database open
ERROR at line 1:
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\ORCLDB\REDO01.LOG'
SQL> recover database until cancel;
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\ORCLDB\SYSTEM01.DBF'
SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\ORCLDB\SYSTEM01.DBF'
SQL> shutdown immediate;
SQL> startup force
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
Specify the log file to use
SQL> Recover database using backup controlfile;
ORA-00279: change 3117356 generated at 04/08/2008 08:49:26 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC00053.001
ORA-00280: change 3117356 for thread 1 is in sequence #53
Specify log: {=suggested | filename | AUTO | CANCEL}
C:\oracle\oradata\ORCLDB\REDO01.LOG
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 370220868 bytes
Fixed Size 454468 bytes
Variable Size 343932928 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
ORA-00214: controlfile inconsistent
Error I received
ORA-00214: controlfile 'C:\ORACLE\ORADATA\ORCLDB\CONTROL01.CTL' version 593
inconsistent with file 'C:\ORACLE\ORADATA\ORCLDB\CONTROL03.CTL' version 587
SQL> shutdown immediate;
SQL> create pfile from spfile;
SQL> shutdown immediate;
Open the pfile, locate "*.control_files", It will look similar to like below
*.control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL01.CTL','C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL02.CTL','C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL03.CTL'
Since one of the 3 control file is inconsistent, I removed the second and third and saved the file.
*.control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL01.CTL'
SQL> startup pfile=C:\oracle\ora92\database\INITorcldb.ORA
ORA-00214: controlfile 'C:\ORACLE\ORADATA\ORCLDB\CONTROL01.CTL' version 593
inconsistent with file 'C:\ORACLE\ORADATA\ORCLDB\CONTROL02.CTL' version 589
Now remove first and third, try with control file 2
*.control_files='C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLDB\CONTROL02.CTL'
SQL> startup pfile=C:\oracle\ora92\database\INITorcldb.ORA
Database mounted.
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\ORCLDB\REDO01.LOG'
SQL> shutdown immediate;
Now create spfile from modified pfile, so it is used by default.
SQL> create spfile from pfile='C:\oracle\ora92\database\INITorcldb.ORA';
SQL> startup mount
SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> alter database open
ERROR at line 1:
ORA-00322: log 1 of thread 1 is not current copy
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\ORCLDB\REDO01.LOG'
SQL> recover database until cancel;
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\ORCLDB\SYSTEM01.DBF'
SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\ORCLDB\SYSTEM01.DBF'
SQL> shutdown immediate;
SQL> startup force
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
Specify the log file to use
SQL> Recover database using backup controlfile;
ORA-00279: change 3117356 generated at 04/08/2008 08:49:26 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC00053.001
ORA-00280: change 3117356 for thread 1 is in sequence #53
Specify log: {
C:\oracle\oradata\ORCLDB\REDO01.LOG
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 370220868 bytes
Fixed Size 454468 bytes
Variable Size 343932928 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
Monday, August 17, 2009
Blackberry: Battery too low for radio use
I am not sure what Blackberry engineers were thinking when they designed the phone.
It's really frustrating, and it is the most stupid thing to do. According to Blackberry, its a feature and not a design bug.
There are 2 options to force the battery on
1) You can dial 911 and disconnect the line, before the operator picks up. Though this works, I wont advice this, as you may offend 911 rules as this is clearly not an emergency
2) Install "ForceRadioOn" application. Its a freeware and available at http://www.blackberryapps.com/PlatformMain.asp?platform=5&sString=ForceRadioOn
or simply google for ForceRadioOn
After installing this application, just launch the application and click the button to turn the Radio On.
It's really frustrating, and it is the most stupid thing to do. According to Blackberry, its a feature and not a design bug.
There are 2 options to force the battery on
1) You can dial 911 and disconnect the line, before the operator picks up. Though this works, I wont advice this, as you may offend 911 rules as this is clearly not an emergency
2) Install "ForceRadioOn" application. Its a freeware and available at http://www.blackberryapps.com/PlatformMain.asp?platform=5&sString=ForceRadioOn
or simply google for ForceRadioOn
After installing this application, just launch the application and click the button to turn the Radio On.
Thursday, July 16, 2009
Oracle: How to kill data pump jobs
When you import or export using data pump impdp or expdp tools, the import/export is done by a job. You have an option to provide a job name using JOB_NAME parameter too
Following sql will give you the list of data pump jobs
select * from dba_datapump_jobs
If you want to kill your impdp or expdp
1) Make sure that your impdp/expdp command prompt window is active
2) Press Control-C , It will pause the job. Don't press another Control-C or close the command prompt. This will just close the window, but the job will still be running in the background
3) Type Kill_Job
ex: Import>kill_job
Are you sure you wish to stop this job (y/n): y
If by mistake, you closed the window and your import/export job is still running,
1) Get the name of the job using
select * from dba_datapump_jobs
2) Open a new command prompt window. If you want to kill your import job type
impdp username/password@database attach=name_of_the_job
3) Once you are attached to job, Type Kill_Job
ex: Import>kill_job
Are you sure you wish to stop this job (y/n): y
And your job is killed, it will no longer show in dba_datapump_jobs
Following sql will give you the list of data pump jobs
select * from dba_datapump_jobs
If you want to kill your impdp or expdp
1) Make sure that your impdp/expdp command prompt window is active
2) Press Control-C , It will pause the job. Don't press another Control-C or close the command prompt. This will just close the window, but the job will still be running in the background
3) Type Kill_Job
ex: Import>kill_job
Are you sure you wish to stop this job (y/n): y
If by mistake, you closed the window and your import/export job is still running,
1) Get the name of the job using
select * from dba_datapump_jobs
2) Open a new command prompt window. If you want to kill your import job type
impdp username/password@database attach=name_of_the_job
3) Once you are attached to job, Type Kill_Job
ex: Import>kill_job
Are you sure you wish to stop this job (y/n): y
And your job is killed, it will no longer show in dba_datapump_jobs
Wednesday, July 15, 2009
Force 64bit IIS to run in 32 bit mode
When you want to run 32 bit web applications on a 64bit Windows box, you need to enable IIS to run in 32 bit mode.
By default IIS is set to run in 64 bit.
To check if 32 bit support is enabled, go to command prompt on the IIS box and type the following command
cscript C:\Inetpub\AdminScripts\adsutil.vbs get W3SVC/AppPools/Enable32bitAppOnWin64
Change the path of adsutil.vbs according to your setup
The script will output either True (1) or FALSE(0)
To enable 32 bit support, go to command promtp again and run the following
cscript c:\Inetpub\AdminScripts\adsutil.vbs set W3SVC/AppPools/Enable32bitAppOnWin64
Now that you enabled 32 bit support, you will no longer see ASP.NET tab on your Web application, properties on Internet service manager
To fix that, jus run aspnet_regiis, from command prompt
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe /i
By default IIS is set to run in 64 bit.
To check if 32 bit support is enabled, go to command prompt on the IIS box and type the following command
cscript C:\Inetpub\AdminScripts\adsutil.vbs get W3SVC/AppPools/Enable32bitAppOnWin64
Change the path of adsutil.vbs according to your setup
The script will output either True (1) or FALSE(0)
To enable 32 bit support, go to command promtp again and run the following
cscript c:\Inetpub\AdminScripts\adsutil.vbs set W3SVC/AppPools/Enable32bitAppOnWin64
Now that you enabled 32 bit support, you will no longer see ASP.NET tab on your Web application, properties on Internet service manager
To fix that, jus run aspnet_regiis, from command prompt
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe /i
Thursday, June 18, 2009
Unsupported Oracle data type USERDEFINED encountered.
Make sure that you use Oracle.DataAccess.Client instead of System.Data.OracleClient.
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Client;
Oracle Host Credentials: Failed to verify credentials
To resolve this error, you need to set OS user name here
Control Panel-> Admin. Tools -> Local Security Policy -> Local Policies -> User Rights Assignment
Locate "Log on as batch job" Policy, right click and select properties
Add the OS User to "Local Security Setting" tab.
Control Panel-> Admin. Tools -> Local Security Policy -> Local Policies -> User Rights Assignment
Locate "Log on as batch job" Policy, right click and select properties
Add the OS User to "Local Security Setting" tab.
Thursday, May 28, 2009
Tuesday, May 19, 2009
Oracle: How to convert BLOB to CLOB
Thanks to Oracle forums.
CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB)
RETURN CLOB
AS
v_clob CLOB;
v_varchar VARCHAR2(32767);
v_start PLS_INTEGER := 1;
v_buffer PLS_INTEGER := 32767;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
LOOP
v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));
DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
v_start := v_start + v_buffer;
END LOOP;
RETURN v_clob;
END blob_to_clob;
/
Example: Select blob_to_clob(blob_column) from table_name;
CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB)
RETURN CLOB
AS
v_clob CLOB;
v_varchar VARCHAR2(32767);
v_start PLS_INTEGER := 1;
v_buffer PLS_INTEGER := 32767;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
LOOP
v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));
DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
v_start := v_start + v_buffer;
END LOOP;
RETURN v_clob;
END blob_to_clob;
/
Example: Select blob_to_clob(blob_column) from table_name;
Thursday, May 7, 2009
Oracle: SQL to find CPU used percentage for each session
Select username,v.sid||','||v.serial# sid_session,round((s.value/y.value)*100,2) cpu_used_percentage
from v$session v, v$sesstat s,v$sysstat y
where v.username is not null
and v.sid = s.sid
and s.statistic#=y.statistic#
and y. name = 'CPU used by this session'
Order by username,3 desc
from v$session v, v$sesstat s,v$sysstat y
where v.username is not null
and v.sid = s.sid
and s.statistic#=y.statistic#
and y. name = 'CPU used by this session'
Order by username,3 desc
EXP-00003: no storage definition found for segment(xx, xx)
This error may occur while exporting table with LOB columns or large tables expanding to multiple extents.
Resolution: Make sure that the version of your export client matches or greater than database version.
You will get this error when export client version is lower than database version.
Example: Exporting 9.2.08 database with 9.2.0.1 client.
Resolution: Make sure that the version of your export client matches or greater than database version.
You will get this error when export client version is lower than database version.
Example: Exporting 9.2.08 database with 9.2.0.1 client.
Monday, April 13, 2009
Friday, April 3, 2009
Thursday, March 12, 2009
Oracle: RecycleBin
From 10g, When you drop a table, Oracle renames the table and stores it in logical recycle bin. The table structure is preserved, meaning all the triggers and indexes on the table are preserved. Even though the table is logically dropped, it still exists in bin, there by occupying space in the tablespace
To view the contents of the recyclebin, go to sqlplusw, and type
sql> show recyclebin
If you dropped the table by mistake and want to revert it back, then issue the command
sql> Flashback table table_name to before drop;
If you want to permanently drop a table,bypassing recyclebin then
sql> drop table table_name purge;
To clear Recylcebin
sql> purge recyclebin
Scripts to manage recyclebin
To drop a table from recycle bin
purge table table_name
You can also purge tables at tablespace level
Purge tablespace tablespace_name
Purge for specific users in tablespace
Purge tablespace tablespace_name USER user_name
To drop all objects in recyclein
purge dba_recylebin
To view the contents of the recyclebin, go to sqlplusw, and type
sql> show recyclebin
If you dropped the table by mistake and want to revert it back, then issue the command
sql> Flashback table table_name to before drop;
If you want to permanently drop a table,bypassing recyclebin then
sql> drop table table_name purge;
To clear Recylcebin
sql> purge recyclebin
Scripts to manage recyclebin
To drop a table from recycle bin
purge table table_name
You can also purge tables at tablespace level
Purge tablespace tablespace_name
Purge for specific users in tablespace
Purge tablespace tablespace_name USER user_name
To drop all objects in recyclein
purge dba_recylebin
Oracle: How to kill Jobs
Sometimes Jobs run in background and they may not be visible in session viewer
Use below SQL to kill jobs
To get list of jobs running
Select sid,d.*
from dba_jobs_running r,dba_jobs d
where r.job = d.job
For SID from above query, Get Serial# from v$ v$session
select * from v$session
where sid in 138
ALTER SYSTEM KILL SESSION '138,19296';
Alter session kill session 'SID,SERIAL#'
Use below SQL to kill jobs
To get list of jobs running
Select sid,d.*
from dba_jobs_running r,dba_jobs d
where r.job = d.job
For SID from above query, Get Serial# from v$ v$session
select * from v$session
where sid in 138
ALTER SYSTEM KILL SESSION '138,19296';
Alter session kill session 'SID,SERIAL#'
Tuesday, March 10, 2009
Oracle: Renaming Tablespace
From 10g, it is possible to rename tablespace (except SYSTEM and SYSAUX).
Alter Tablespace old_name rename to new_name
Example:
Alter tablespace BB_Data rename to ZZ_Data
Renaming UNDO Tablespace
Select value from v$spparameter where name = 'undo_tablespace' --> UNDOTBS1
Alter Tablespace UNDOTBS1 Rename to UNDOTBS2
Select value from v$spparameter where name = 'undo_tablespace' --> UNDOTBS2
Alter Tablespace old_name rename to new_name
Example:
Alter tablespace BB_Data rename to ZZ_Data
Renaming UNDO Tablespace
Select value from v$spparameter where name = 'undo_tablespace' --> UNDOTBS1
Alter Tablespace UNDOTBS1 Rename to UNDOTBS2
Select value from v$spparameter where name = 'undo_tablespace' --> UNDOTBS2
Oracle: Renaming Tablespace
From 10g, it is possible to rename tablespace (except SYSTEM and SYSAUX).
Alter Tablespace old_name rename to new_name
Example:
Alter tablespace BB_Data rename to ZZ_Data
Renaming UNDO Tablespace
Select value from v$spparameter where name = 'undo_tablespace' --> UNDOTBS1
Alter Tablespace UNDOTBS1 Rename to UNDOTBS2
Select value from v$spparameter where name = 'undo_tablespace' --> UNDOTBS2
Alter Tablespace old_name rename to new_name
Example:
Alter tablespace BB_Data rename to ZZ_Data
Renaming UNDO Tablespace
Select value from v$spparameter where name = 'undo_tablespace' --> UNDOTBS1
Alter Tablespace UNDOTBS1 Rename to UNDOTBS2
Select value from v$spparameter where name = 'undo_tablespace' --> UNDOTBS2
Oracle:How to calculate Rollback duration
Here is a query to figure out how long it will take for a rollback to complete.
Select username,opname,start_time,last_update_time,totalwork,units,sofar,
elapsed_seconds,time_remaining,sql_Text
from v$session_longops l, v$sql s
where l.sql_id = s.sql_id(+)
and sid = sid_of_the_session_doing_rollback
Above query takes SID as input parameter.Replace "sid_of_the_session_doing_rollback" with the SID
For a Rollback Operation
Opname will say "Transaction Rollback"
Total Work: Amount of work.
Units: Total work is mesaured in terms of UNITS columns
SoFar: Work done so far.
SQL_TEXT: Sql that is being rolled back.
Since this sql queries LONGOPS View, you can use it to get an idea for how long any process would take.
Select username,opname,start_time,last_update_time,totalwork,units,sofar,
elapsed_seconds,time_remaining,sql_Text
from v$session_longops l, v$sql s
where l.sql_id = s.sql_id(+)
and sid = sid_of_the_session_doing_rollback
Above query takes SID as input parameter.Replace "sid_of_the_session_doing_rollback" with the SID
For a Rollback Operation
Opname will say "Transaction Rollback"
Total Work: Amount of work.
Units: Total work is mesaured in terms of UNITS columns
SoFar: Work done so far.
SQL_TEXT: Sql that is being rolled back.
Since this sql queries LONGOPS View, you can use it to get an idea for how long any process would take.
Oracle: Flashback versions Query
Oracle 10g has built in audit management to track changes to the table. Oracle tracks the values by using pseudo-columns versions_starttime,versions_endtime,
versions_xid ,versions_operation,versions_startscn and versions_endscn
versions_xid: Column identifies transaction that modified the table.You can query FLASHBACK_TRANSACTION_QUERY table for this value to get the history of the transaction and the corresponding UNDOSQL
Example
Create table testing (col1 varchar2(10), col2 int);
Insert into testing values ( 'A',10);
Insert into testing values ( 'B',20);
Commit;
Update testing set col2 = 202 where col1 = 'B';
Commit;
Delete from testing ;
Commit;
sELECT * from testing; -->0 Records
Now to get the history
Select testing.*,versions_starttime,versions_endtime,versions_operation,
versions_startscn,versions_endscn,versions_xid
from testing
versions between timestamp minvalue and maxvalue
To get details of the transaction and the undo sql fo the transaction,
Select * from
flashback_transaction_query
where xid ='050054001FAF0000'
IMPORTANT
This query pulls data from undo segments, so you cannot get a complete history of the transaction.As long as you have the data in UNDO_SEGMENTS, You can always query history of any tables.
versions_xid ,versions_operation,versions_startscn and versions_endscn
versions_xid: Column identifies transaction that modified the table.You can query FLASHBACK_TRANSACTION_QUERY table for this value to get the history of the transaction and the corresponding UNDOSQL
Example
Create table testing (col1 varchar2(10), col2 int);
Insert into testing values ( 'A',10);
Insert into testing values ( 'B',20);
Commit;
Update testing set col2 = 202 where col1 = 'B';
Commit;
Delete from testing ;
Commit;
sELECT * from testing; -->0 Records
Now to get the history
Select testing.*,versions_starttime,versions_endtime,versions_operation,
versions_startscn,versions_endscn,versions_xid
from testing
versions between timestamp minvalue and maxvalue
To get details of the transaction and the undo sql fo the transaction,
Select * from
flashback_transaction_query
where xid ='050054001FAF0000'
IMPORTANT
This query pulls data from undo segments, so you cannot get a complete history of the transaction.As long as you have the data in UNDO_SEGMENTS, You can always query history of any tables.
Tuesday, January 27, 2009
ORA-30926: unable to get a stable set of rows in the source tables
Quite often, you may see this warning message when you use MERGE statement.
If you get this, then your Merge statement is trying to update multiple rows in "WHEN MATCHED" Clause.
Multiple updates to the same row in destination table is NOT allowed.
Multiple insert is allowed though.
To know for sure, If you comment out your "WHEN MATCHED" section, you will no longer get this warning.
If you get this, then your Merge statement is trying to update multiple rows in "WHEN MATCHED" Clause.
Multiple updates to the same row in destination table is NOT allowed.
Multiple insert is allowed though.
To know for sure, If you comment out your "WHEN MATCHED" section, you will no longer get this warning.
Wednesday, January 14, 2009
Oracle: How to size UNDO Tablespace for Automatic Management
Here are some queries to figure out how to manage your UNDO Tablespace from 10g onwards. Oracle strongly recommends using UNDO_MANAGEMENT=AUTO.
If your UNDO tablespace is fixed size, some of the queries may help you. All of them are from Oracle Metalink.Replace UNDOTBS1 with name of your UNDO tablespace.
--UNDO Tablespace size
Select SUM(BYTES)/1024/1024 as "MB"
from dba_data_files
where tablespace_name = 'UNDOTBS1';
--Free space available
Select SUM(BYTES)/1024/1024 as "MB"
from dba_FREE_SPACE
where tablespace_name = 'UNDOTBS1';
--Active/Expired segments
SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024 as "MB", COUNT(*)
FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
--To calculate space required for your UNDO to grow
SELECT ((UR * (UPS * DBS)) + (DBS * 24))/1024/1024 AS "MB"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));
UR=UNDO_RETENTION (in seconds)
UPS=Number of UNDO data blocks generated per second
DBS=DB_BLOCK_SIZE (in bytes)
--SQL to find the active transaction that is consuming the UNDO
-- Replace UNDOTBS02 with your UNDO tablespace name
SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser, s.machine,
--s.osuser,s.terminal,s.module,s.schemaname,
s.state,s.program, r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 Undo_Size_KB,
t.status Transaction_Status,t.start_time,
t1.tablespace_name,sq.sql_text
FROM SYS.v_$rollname r, SYS.v_$session s, SYS.v_$transaction t, SYS.v_$parameter x, dba_rollback_segs t1, sys.v$sql sq
WHERE s.taddr = t.addr
and s.sql_id = sq.sql_id(+)
AND r.usn = t.xidusn(+)
AND x.NAME = 'db_block_size'
AND t1.segment_id = r.usn
AND t1.tablespace_name = 'UNDOTBS02'
If your UNDO tablespace is fixed size, some of the queries may help you. All of them are from Oracle Metalink.Replace UNDOTBS1 with name of your UNDO tablespace.
--UNDO Tablespace size
Select SUM(BYTES)/1024/1024 as "MB"
from dba_data_files
where tablespace_name = 'UNDOTBS1';
--Free space available
Select SUM(BYTES)/1024/1024 as "MB"
from dba_FREE_SPACE
where tablespace_name = 'UNDOTBS1';
--Active/Expired segments
SELECT DISTINCT STATUS, SUM(BYTES)/1024/1024 as "MB", COUNT(*)
FROM DBA_UNDO_EXTENTS GROUP BY STATUS;
--To calculate space required for your UNDO to grow
SELECT ((UR * (UPS * DBS)) + (DBS * 24))/1024/1024 AS "MB"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));
UR=UNDO_RETENTION (in seconds)
UPS=Number of UNDO data blocks generated per second
DBS=DB_BLOCK_SIZE (in bytes)
--SQL to find the active transaction that is consuming the UNDO
-- Replace UNDOTBS02 with your UNDO tablespace name
SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser, s.machine,
--s.osuser,s.terminal,s.module,s.schemaname,
s.state,s.program, r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 Undo_Size_KB,
t.status Transaction_Status,t.start_time,
t1.tablespace_name,sq.sql_text
FROM SYS.v_$rollname r, SYS.v_$session s, SYS.v_$transaction t, SYS.v_$parameter x, dba_rollback_segs t1, sys.v$sql sq
WHERE s.taddr = t.addr
and s.sql_id = sq.sql_id(+)
AND r.usn = t.xidusn(+)
AND x.NAME = 'db_block_size'
AND t1.segment_id = r.usn
AND t1.tablespace_name = 'UNDOTBS02'
Tuesday, January 6, 2009
Windows Explorer: Display security tab in Windows XP Professional
Click Start and launch Control Panel
Locate Folder Options
Click on the tab View, Scroll all the way down
Uncheck the option "Use simple file sharing (Recommended)"
Click OK. Now you should see Security tab on each folder.
Locate Folder Options
Click on the tab View, Scroll all the way down
Uncheck the option "Use simple file sharing (Recommended)"
Click OK. Now you should see Security tab on each folder.
Subscribe to:
Posts (Atom)