Pivot Table

We have a table as below:

SQL> select * from tablea;

ID1 ID2 ID3
----- ----- -----
1 B MAR
1 A JAN
1 A FEB
1 B JAN
1 B JAN
1 B FEB
2 A JAN
2 A JAN
2 A FEB
2 B FEB
2 B FEB
2 B JAN

12 rows selected


Now, we want to for each ID1 and ID2, how many ID3 are 'JAN' & how many ID3 are 'FEB' and how many other than JAN or FEB, in a single row as below:

ID1| ID2| JAN| FEB|OTHER
---| ---| ---| ---|-----
1 | A | 1 | 1 | 0
1 | B | 2 | 1 | 1
2 | A | 2 | 1 | 0
2 | B | 1 | 2 | 0


We can easily do this using decode:

select id1,
id2,
count(decode(id3, 'JAN', 'JAN', null)) JAN,
count(decode(id3, 'FEB', 'FEB', null)) FEB,
count(decode(id3, 'JAN',null,'FEB', null,'OTHER')) OTHER
from tablea
group by id1, id2;

We can easily also do this using case:

select id1,
id2,
count((case when id3='JAN' then 'JAN' else null end)) JAN,
count((case when id3='FEB' then 'FEB' else null end)) FEB,
count((case when id3 not in ('JAN','FEB') then 'OTHER' else null end)) OTHER
from tablea
group by id1, id2;

ORA-14450: attempt to access a transactional temp table already in use

While DDL(alter table ....) on a Global Temporary Table I encountered "ORA-14450".
Then I searched in V$LOCKED_OBJECTS but didn't find anything there(If I am not wrong,V$LOCKED_OBJECTS contains only DML related locks ). The I fired below query to find the session locking my TEMP Table:

SELECT s.INST_ID,
o.object_name,
s.sid,
s.STATUS,
s.serial#,
s.username,
s.osuser,
s.machine,
'alter system kill session ''' || to_char(s.sid) || ',' ||
to_char(s.serial#) || ''';' ks
FROM dba_objects o, gv$lock a, gv$session s
WHERE o.object_name = ''
AND o.owner = ''
AND a.id1 = o.object_id
AND a.type = 'TO'
AND a.sid = s.sid;

For the description of type column we can user below query:

select type,name,description from v$lock_type where type='TO';

Thanks to http://www.oracleoverflow.com/questions/266/alter-temporary-table-throws-ora-14450

Foreign Key Constraints Referencing to a Table

We had a table to purge with primary key.
Problem is we need to backup purged data which includes data from all the child tables referencing with foreign keys to that table.
Now I had to find all the child tables and their foreign key columns. I used below query to resolve this problem:

select a.owner,
a.constraint_name,
a.constraint_type,
a.table_name,
b.column_name,
a.r_owner,
a.r_constraint_name
from dba_constraints a, dba_cons_columns b
where a.owner = 'MYUSER'
and a.owner = b.owner
and a.constraint_name = b.constraint_name
and a.r_constraint_name in
(select constraint_name
from dba_constraints
where table_name = 'MYTABLE'
and owner = 'MYUSER');


http://bytes.com/topic/oracle/answers/644008-query-find-primary-foreign-keys
http://www.databasejournal.com/features/oracle/article.php/3665591/Finding-Foreign-Key-Constraints-in-Oracle.htm