Happy New Year

Gwalior Fort, Madhya Pradesh

Darasuram Ayravatheswarar temple, Kumbhakonam Tamil Nadu

Ellora Maharashtra

Shilpa Shetty

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.

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

தமிழ் கவிதை: சொந்த மண்ணில்

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;

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

Shilpa Shetty

Koena Mitra

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.

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)

Lara Dutta

In the movie BLUE

Sheryln Chopra

Mugdha Godse

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

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"

How to clean your monitor

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;

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;
/

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;
/

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;
/

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;
/

Amruta Patki

Nisha Kothari

Gauhar Khan

New face

Udita Goswami

Shilpa & Shamita Shetty

Nisha Kothari

Indian Dress: Sarees