Monday, November 9, 2009

SQL to Concat Rows into a list.

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.

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

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;

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