<?xml version='1.0' encoding='UTF-8'?><?xml-stylesheet href="http://www.blogger.com/styles/atom.css" type="text/css"?><feed xmlns='http://www.w3.org/2005/Atom' xmlns:openSearch='http://a9.com/-/spec/opensearchrss/1.0/' xmlns:blogger='http://schemas.google.com/blogger/2008' xmlns:georss='http://www.georss.org/georss' xmlns:gd="http://schemas.google.com/g/2005" xmlns:thr='http://purl.org/syndication/thread/1.0'><id>tag:blogger.com,1999:blog-3979653527893526042</id><updated>2024-09-09T12:02:20.179+05:30</updated><category term="Implementing Row Level Security"/><category term="LISTAGG and PIVOT in ORACLE 11g"/><category term="Logon Failure Auditing in ORACLE"/><category term="Sessions Marked for Kill"/><title type='text'>My Experiments with ORACLE</title><subtitle type='html'></subtitle><link rel='http://schemas.google.com/g/2005#feed' type='application/atom+xml' href='http://j-oracle.blogspot.com/feeds/posts/default'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/3979653527893526042/posts/default?redirect=false'/><link rel='alternate' type='text/html' href='http://j-oracle.blogspot.com/'/><link rel='hub' href='http://pubsubhubbub.appspot.com/'/><author><name>Jeneesh</name><uri>http://www.blogger.com/profile/01069422196141471281</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><generator version='7.00' uri='http://www.blogger.com'>Blogger</generator><openSearch:totalResults>4</openSearch:totalResults><openSearch:startIndex>1</openSearch:startIndex><openSearch:itemsPerPage>25</openSearch:itemsPerPage><entry><id>tag:blogger.com,1999:blog-3979653527893526042.post-6389879492103402170</id><published>2012-01-31T20:02:00.025+05:30</published><updated>2012-04-28T14:07:38.790+05:30</updated><category scheme="http://www.blogger.com/atom/ns#" term="Implementing Row Level Security"/><title type='text'>Implementing Row Level Security (RLS)</title><content type='html'>&lt;div dir=&quot;ltr&quot; style=&quot;text-align: left;&quot; trbidi=&quot;on&quot;&gt;We have implemented Row Level Security using the &lt;a href=&quot;http://www.oracle.com/technetwork/database/security/index-088277.html&quot;&gt;Virtual Private database Policy (VPD)&lt;/a&gt;.What VPD will do is to add where conditions on user SQLs based on the policies defined.&lt;br /&gt;&lt;br /&gt;&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;For example, in the simplest manner, suppose you want to restrict the users to access the records created only by themselves in the table &quot;TEST_TAB&quot;. You can define and apply a policy &quot;POLICY1&quot; as &quot;CREATED_BY = USER&quot; for the table &quot;TEST_TAB&quot;.&lt;/div&gt;&lt;br /&gt;&lt;div align=&quot;justify&quot;&gt;The steps in implementing VPD is as follows:&lt;/div&gt;&lt;br /&gt;&lt;div align=&quot;justify&quot;&gt;1. Create a function which will return the where condition to be applied. The argumnets to this function should be schema and object name. &lt;/div&gt;&lt;span style=&quot;background-color: transparent;&quot;&gt;&lt;br /&gt;&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;background-color: transparent;&quot;&gt;function policy1 (p_schema varchar2,p_object varchar2) return varchar2&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;background-color: transparent;&quot;&gt;&lt;br /&gt;&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;background-color: transparent;&quot;&gt;2. Add the policy using &lt;/span&gt;&lt;a href=&quot;http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_rls.htm&quot; style=&quot;background-color: transparent;&quot;&gt;DBMS_RLS&lt;/a&gt;&lt;span style=&quot;background-color: transparent;&quot;&gt;.ADD_POLICY&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;background-color: transparent;&quot;&gt;Below is a simple example to add a policy on a table RETAILDM.TEST_RLS.&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;&lt;div align=&quot;justify&quot;&gt;First We will see the data in RETAILDM.TEST_RLS.It has two records created by RETAILDM and one record created by another user.&lt;br /&gt;&lt;pre&gt;SQL&gt;; show user&lt;br /&gt;USER is &quot;RETAILDM&quot;&lt;br /&gt;&lt;br /&gt;SQL&gt;; select *  &lt;br /&gt;2  from test_rls;&lt;br /&gt;C1                   CREATED_BY&lt;&lt;br /&gt;--------------------------------&lt;br /&gt;XXXXX                RETAILDM&lt;br /&gt;YYYYY                USER2&lt;br /&gt;ZZZZZ                RETAILDM&lt;/pre&gt;&lt;/div&gt;&lt;br /&gt;&lt;span style=&quot;background-color: transparent;&quot;&gt;&lt;br /&gt;Now, we have to define the Policy function to return the &quot;where&#39; condition.&lt;/span&gt;&lt;br /&gt;&lt;div align=&quot;justify&quot;&gt;&lt;pre&gt;SQL&gt;; create or replace function test_rls_fn(p_schema varchar2,p_object varchar2)  &lt;br /&gt;2          return varchar2 is  &lt;br /&gt;3      lc_where varchar2(500);  &lt;br /&gt;4  begin  &lt;br /&gt;5      lc_where := &#39; created_by = user&#39;;  &lt;br /&gt;6      return lc_where;&lt;br /&gt;7  end;&lt;br /&gt;8  /&lt;br /&gt;Function created.&lt;/pre&gt;&lt;/div&gt;&lt;span style=&quot;background-color: transparent;&quot;&gt;&lt;br /&gt;&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;background-color: transparent;&quot;&gt;&lt;br /&gt;next step is to add the policy using DBMS_RLS.ADD_POLICY. The arguments are&lt;/span&gt;&lt;br /&gt;&lt;div align=&quot;justify&quot;&gt;object schema, object name, policy name, policy function schema and policy function name.&lt;br /&gt;&lt;pre&gt;&lt;/pre&gt;&lt;pre&gt;SQL&gt;; exec dbms_rls.ADD_POLICY(&#39;RETAILDM&#39;,&#39;TEST_RLS&#39;,&#39;TEST_RLS_POLICY&#39;,&#39;RETAILDM&#39;,&#39;TEST_RLS_FN&#39;);&lt;br /&gt;PL/SQL procedure successfully completed.&lt;/pre&gt;&lt;/div&gt;&lt;br /&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;Now, if we select data from TEST_RLS, we will get only those records created by RETAILDM.&lt;br /&gt;&lt;pre&gt;&lt;/pre&gt;&lt;pre&gt;SQL&gt;; select *  &lt;br /&gt;2  from test_rls;&lt;br /&gt;C1                   CREATED_BY&lt;br /&gt;-------------------- ----------&lt;br /&gt;XXXXX                RETAILDM&lt;br /&gt;ZZZZZ                RETAILDM&lt;/pre&gt;&lt;/div&gt;&lt;br /&gt;&lt;span style=&quot;background-color: transparent;&quot;&gt;&lt;br /&gt;If you check the &lt;/span&gt;&lt;a href=&quot;http://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm&quot; style=&quot;background-color: transparent;&quot;&gt;explain plan&lt;/a&gt;&lt;span style=&quot;background-color: transparent;&quot;&gt;, the where condition applied can be spotted out.&lt;/span&gt;&lt;br /&gt;&lt;div align=&quot;justify&quot;&gt;&lt;pre&gt;SQL&gt;; explain plan for  &lt;br /&gt;2  select *  &lt;br /&gt;3  from test_rls;&lt;br /&gt;Explained&lt;br /&gt;&lt;br /&gt;SQL&gt;; select *&lt;br /&gt;2        from table(dbms_xplan.display);&lt;br /&gt;&lt;br /&gt;PLAN_TABLE_OUTPUT&lt;br /&gt;--------------------------------------------------------------------------------&lt;br /&gt;Plan hash value: 636636236&lt;br /&gt;&lt;br /&gt;------------------------------------------------------------------------------&lt;br /&gt;Id Operation Name Rows Bytes Cost (%CPU) Time&lt;br /&gt;------------------------------------------------------------------------------&lt;br /&gt;0 SELECT STATEMENT 2 38 3 (0) 00:00:01 *&lt;br /&gt;1 TABLE ACCESS FULL TEST_RLS 2 38 3 (0) 00:00:01&lt;br /&gt;------------------------------------------------------------------------------&lt;br /&gt;Predicate Information (identified by operation id):&lt;br /&gt;---------------------------------------------------&lt;br /&gt;&lt;br /&gt;PLAN_TABLE_OUTPUT&lt;br /&gt;--------------------------------------------------------------------------------&lt;br /&gt;&lt;br /&gt;1 - filter(&quot;CREATED_BY&quot;=USER@!)&lt;br /&gt;&lt;br /&gt;Note&lt;br /&gt;-----&lt;br /&gt;- dynamic sampling used for this statement (level=2)&lt;br /&gt;&lt;br /&gt;17 rows selected.&lt;br /&gt;&lt;/pre&gt;&lt;/div&gt;&lt;br /&gt;&lt;pre&gt;&lt;/pre&gt;&lt;div align=&quot;justify&quot;&gt;&lt;br /&gt;&lt;/div&gt;&lt;div align=&quot;justify&quot;&gt;&lt;span style=&quot;color: rgb(51, 51, 51); font-family: &#39;trebuchet ms&#39;, verdana, arial, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto; background-color: rgb(255, 255, 255); &quot;&gt;Tags: RLS ORACLE, Row level security ORACLE&lt;/span&gt;&lt;br style=&quot;color: rgb(51, 51, 51); font-family: &#39;trebuchet ms&#39;, verdana, arial, sans-serif; font-size: 13px; line-height: 18px; text-align: -webkit-auto; background-color: rgb(255, 255, 255); &quot;&gt;&lt;/div&gt;</content><link rel='replies' type='application/atom+xml' href='http://j-oracle.blogspot.com/feeds/6389879492103402170/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='http://j-oracle.blogspot.com/2012/01/implementing-row-level-security-rls.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/3979653527893526042/posts/default/6389879492103402170'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/3979653527893526042/posts/default/6389879492103402170'/><link rel='alternate' type='text/html' href='http://j-oracle.blogspot.com/2012/01/implementing-row-level-security-rls.html' title='Implementing Row Level Security (RLS)'/><author><name>Jeneesh</name><uri>http://www.blogger.com/profile/01069422196141471281</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3979653527893526042.post-6311823189319246080</id><published>2012-01-30T12:41:00.001+05:30</published><updated>2012-01-31T12:48:40.778+05:30</updated><category scheme="http://www.blogger.com/atom/ns#" term="Sessions Marked for Kill"/><title type='text'>Sessions Marked for Kill</title><content type='html'>Some times when we kill an ORACLE session with the “alter system kill” statement, it will return a message that the session is marked for kill. In most of the cases, it will be rolling back a huge transaction that you executed.&lt;br /&gt;&lt;br /&gt;As an example, if you killed a session which was supposed to update/delete 1Million rows on half the way , it will definitely take some time to roll the changes back. This can be verified by checking the &lt;a href=&quot;http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3114.htm&quot;&gt;v$transaction &lt;/a&gt;table.&lt;pre&gt;&lt;br /&gt;SQL&amp;gt; select count(*) cnt from test_tab1;&lt;br /&gt;     &lt;br /&gt;CNT&lt;br /&gt;----------  &lt;br /&gt;&lt;br /&gt;769538&lt;br /&gt;&lt;br /&gt;SQL&amp;gt; update test_tab1 set c1 = c1+1;&lt;/pre&gt;&lt;br /&gt;Huge update is going on now.In between, if you kill the session and check the &lt;a href=&quot;http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3114.htm&quot;&gt;v$transaction &lt;/a&gt;table you will see the change in the values of &quot;Number of undo blocks used&quot; and &quot;Number of undo records used&quot;&lt;pre&gt;&lt;br /&gt;SQL&amp;gt; alter system kill session &#39;49,27133&#39;;&lt;br /&gt;alter system kill session &#39;49,27133&#39;&lt;br /&gt;*&lt;br /&gt;ERROR at line 1:&lt;br /&gt;ORA-00031: session marked for kill&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;SQL&amp;gt; select status&lt;br /&gt;2  from v$session&lt;br /&gt;3  where sid = 49;&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;STATUS&lt;br /&gt;--------&lt;br /&gt;&lt;br /&gt;KILLED&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;SQL&amp;gt; select used_ublk,used_urec&lt;br /&gt;2  from v$transaction&lt;br /&gt;3  where ses_addr=(&lt;br /&gt;4                  select saddr&lt;br /&gt;5                  from v$session&lt;br /&gt;6                  where sid = 49&lt;br /&gt;7                  );&lt;br /&gt;&lt;br /&gt;&lt;br /&gt; USED_UBLK  USED_UREC&lt;br /&gt;---------- ----------  &lt;br /&gt;&lt;br /&gt; 24769     161040&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;SQL&amp;gt; /&lt;br /&gt; USED_UBLK  USED_UREC&lt;br /&gt;---------- ----------   &lt;br /&gt;&lt;br /&gt;24081     155939&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;SQL&amp;gt; /&lt;br /&gt; USED_UBLK  USED_UREC&lt;br /&gt;---------- ----------   &lt;br /&gt;&lt;br /&gt;22605     145321&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;SQL&amp;gt; /&lt;br /&gt; USED_UBLK  USED_UREC&lt;br /&gt;---------- ----------   &lt;br /&gt;&lt;br /&gt;10487      66038&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;SQL&amp;gt; /&lt;br /&gt; USED_UBLK  USED_UREC&lt;br /&gt;---------- ----------    &lt;br /&gt;&lt;br /&gt;6637      41384&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;SQL&amp;gt; /&lt;br /&gt;&lt;br /&gt;no rows selected&lt;/pre&gt;&lt;br /&gt;The rollback is over. We can check &lt;a href=&quot;http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3016.htm#sthref3552&quot;&gt;V$SESSION &lt;/a&gt;now.&lt;pre&gt;&lt;br /&gt;SQL&amp;gt; select status&lt;br /&gt;2  from v$session&lt;br /&gt;3  where sid = 49;&lt;br /&gt;&lt;br /&gt;no rows selected&lt;/pre&gt;</content><link rel='replies' type='application/atom+xml' href='http://j-oracle.blogspot.com/feeds/6311823189319246080/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='http://j-oracle.blogspot.com/2012/01/sessions-marked-for-kill.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/3979653527893526042/posts/default/6311823189319246080'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/3979653527893526042/posts/default/6311823189319246080'/><link rel='alternate' type='text/html' href='http://j-oracle.blogspot.com/2012/01/sessions-marked-for-kill.html' title='Sessions Marked for Kill'/><author><name>Jeneesh</name><uri>http://www.blogger.com/profile/01069422196141471281</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3979653527893526042.post-1240700657921603329</id><published>2012-01-29T19:24:00.000+05:30</published><updated>2012-01-30T11:11:38.530+05:30</updated><category scheme="http://www.blogger.com/atom/ns#" term="Logon Failure Auditing in ORACLE"/><title type='text'>Logon Failure Auditing in ORACLE</title><content type='html'>We just enabled the Logon Failure Auditing in ORACLE. The steps folowed are as follows.&lt;br /&gt;&lt;br /&gt;1. change the parammeter audit_trail=db in initSID.ora or ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;&lt;br /&gt;2.Restart the db.&lt;br /&gt;3.Connect as sysdba and run the below command.&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;&lt;blockquote&gt;audit create session whenever not successful;&lt;/blockquote&gt;&lt;br /&gt;4.Now failures will be logged in dba_audit_trail table.&lt;br /&gt;5.Details of audit options are available in dba_priv_audit_opts table.</content><link rel='replies' type='application/atom+xml' href='http://j-oracle.blogspot.com/feeds/1240700657921603329/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='http://j-oracle.blogspot.com/2012/01/logon-failure-auditing-in-oracle.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/3979653527893526042/posts/default/1240700657921603329'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/3979653527893526042/posts/default/1240700657921603329'/><link rel='alternate' type='text/html' href='http://j-oracle.blogspot.com/2012/01/logon-failure-auditing-in-oracle.html' title='Logon Failure Auditing in ORACLE'/><author><name>Jeneesh</name><uri>http://www.blogger.com/profile/01069422196141471281</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry><entry><id>tag:blogger.com,1999:blog-3979653527893526042.post-564622367432099243</id><published>2012-01-29T14:09:00.002+05:30</published><updated>2012-04-27T14:29:32.592+05:30</updated><category scheme="http://www.blogger.com/atom/ns#" term="LISTAGG and PIVOT in ORACLE 11g"/><title type='text'>LISTAGG and PIVOT in ORACLE 11g</title><content type='html'>&lt;div dir=&quot;ltr&quot; style=&quot;text-align: left;&quot; trbidi=&quot;on&quot;&gt;
&lt;span style=&quot;font-family: verdana;&quot;&gt;&lt;br /&gt;&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;br /&gt;
&lt;blockquote&gt;
&lt;span style=&quot;font-family: verdana;&quot;&gt;&lt;br /&gt;&lt;/span&gt;&lt;br /&gt;
&lt;span style=&quot;background-color: transparent; font-family: verdana;&quot;&gt;The two &lt;/span&gt;&lt;a href=&quot;http://docs.oracle.com/cd/E11882_01/server.112/e26088/wnsql.htm&quot; style=&quot;background-color: transparent; font-family: verdana;&quot;&gt;new features of ORACLE 11g &lt;/a&gt;&lt;span style=&quot;background-color: transparent; font-family: verdana;&quot;&gt;- The &lt;/span&gt;&lt;a href=&quot;http://docs.oracle.com/cd/E14072_01/server.112/e10592/functions087.htm&quot; style=&quot;background-color: transparent; font-family: verdana;&quot;&gt;LISTAGG&lt;/a&gt;&lt;span style=&quot;background-color: transparent; font-family: verdana;&quot;&gt; function and the &lt;/span&gt;&lt;a href=&quot;http://docs.oracle.com/cd/E14072_01/server.112/e10592/statements_10002.htm#sthref6746&quot; style=&quot;background-color: transparent; font-family: verdana;&quot;&gt;PIVOT SQL clause &lt;/a&gt;&lt;span style=&quot;background-color: transparent; font-family: verdana;&quot;&gt;- are very handy.&lt;/span&gt;&lt;br /&gt;
&lt;div align=&quot;justify&quot;&gt;
&lt;span style=&quot;font-family: verdana;&quot;&gt;&lt;span style=&quot;font-family: verdana;&quot;&gt;The benefit with the same is actually visible when you compare these features with the &quot;old&quot; technique to get the same output.&lt;br /&gt;We can see the differences with the sample EMP and DEPT &lt;/span&gt;&lt;span style=&quot;font-family: verdana;&quot;&gt;created and populated as below.&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;&lt;/span&gt;&lt;/span&gt;&lt;/div&gt;
&lt;span style=&quot;font-family: verdana;&quot;&gt;&lt;br /&gt;&lt;/span&gt;&lt;br /&gt;
&lt;div align=&quot;justify&quot;&gt;
&lt;span style=&quot;font-family: verdana;&quot;&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;drop table test_emp;&lt;br /&gt;drop table test_dept;&lt;/span&gt;&lt;br /&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;create table test_emp(emp_id number(5),emp_name varchar2(150), &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;dept_id number(4));&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;create table test_dept(dept_id number(4),dept_name varchar2(150));&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Insert into TEST_DEPT &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;(DEPT_ID, DEPT_NAME) &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Values (30, &#39;Sales&#39;);&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Insert&lt;br /&gt;into TEST_DEPT &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;(DEPT_ID, DEPT_NAME) &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Values (20, &#39;IT&#39;);&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Insert into TEST_DEPT &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;(DEPT_ID, DEPT_NAME) &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Values&lt;br /&gt;(10, &#39;HR&#39;);&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;COMMIT;&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Insert into TEST_EMP &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;(EMP_ID, EMP_NAME, DEPT_ID) &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Values (6, &#39;Sabu Samuel&#39;, 30);&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Insert into TEST_EMP &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;(EMP_ID, EMP_NAME, DEPT_ID) &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Values (6, &#39;Chinthaka G&#39;, 30);&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Insert into TEST_EMP &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;(EMP_ID, EMP_NAME, DEPT_ID) &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Values (5, &#39;Margarat K Jack&#39;, 20);&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Insert into TEST_EMP &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;(EMP_ID, EMP_NAME, DEPT_ID) &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Values (4, &#39;Kennet Lane&#39;, 20);&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Insert into TEST_EMP &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;(EMP_ID, EMP_NAME, DEPT_ID) &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Values (3, &#39;Rama Murthy&#39;, 10);&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Insert into TEST_EMP &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;(EMP_ID, EMP_NAME, DEPT_ID) &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Values (2, &#39;Wilson Dslva&#39;, 10);&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Insert into TEST_EMP &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;(EMP_ID, EMP_NAME, DEPT_ID) &lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;Values (1, &#39;Lawrence Clinch&#39;, 10);&lt;/span&gt;&lt;br /&gt;&lt;span style=&quot;font-family: verdana; font-size: 78%;&quot;&gt;COMMIT;&lt;/span&gt;&lt;br /&gt;Prior to 11g to get the Department name as a column and count as rows we need to use the SUM(DECODE()) technique.&lt;/span&gt;&lt;/div&gt;
&lt;pre&gt;&lt;span style=&quot;font-family: verdana;&quot;&gt;select sum(decode(dept_name,&#39;HR&#39;,1,0)) hr,
sum(decode(dept_name,&#39;IT&#39;,1,0)) it,
sum(decode(dept_name,&#39;Sales&#39;,1,0)) sales
from test_emp e,test_dept d
where e.DEPT_ID = d.DEPT_ID
group by dept_name; &lt;/span&gt;&lt;/pre&gt;
&lt;span style=&quot;font-family: verdana;&quot;&gt;&lt;br /&gt;&lt;/span&gt;&lt;br /&gt;
&lt;div align=&quot;justify&quot;&gt;
&lt;span style=&quot;font-family: verdana;&quot;&gt;To Get a list of employees across departments in a Pivoted manner we need to use SUM(DECODE()) technique along with &lt;a href=&quot;http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions183.htm&quot;&gt;SYS_CONNECT_BY_PATH &lt;/a&gt;prior to 11g.&lt;/span&gt;&lt;/div&gt;
&lt;pre&gt;&lt;span style=&quot;font-family: verdana;&quot;&gt;select max(decode(dept_name,&#39;HR&#39;,emp_list)) hr,
max(decode(dept_name,&#39;IT&#39;,emp_list)) it,
max(decode(dept_name,&#39;Sales&#39;,emp_list)) sales
from (
select dept_name,ltrim(max(sys_connect_by_path(emp_name,&#39;,&#39;)),&#39;,&#39;) emp_list
from(
select dept_name,emp_name,row_number() over(partition by dept_name order by emp_name) rn
from test_emp e,test_dept d
where e.DEPT_ID = d.DEPT_ID
)
start with rn = 1
connect by prior rn = rn - 1 and prior dept_name = dept_name
group by dept_name
);

HR                                       IT                        SALES
---------------------------------------- ------------------------- -------------------------
Lawrence Clinch,Rama Murthy,Wilson Dslva Kennet Lane,Margarat K Ja Chinthaka G,Sabu Samuel&lt;/span&gt;&lt;/pre&gt;
&lt;span style=&quot;font-family: verdana;&quot;&gt;&lt;br /&gt;&lt;/span&gt;&lt;br /&gt;
&lt;span style=&quot;background-color: transparent; font-family: verdana;&quot;&gt;In 11g the same thing can be achieved by a simple and fast query as shown below.&lt;/span&gt;&lt;br /&gt;
&lt;pre&gt;&lt;span style=&quot;font-family: verdana;&quot;&gt;select *
from(
select dept_name,listagg(emp_name) within group (order by emp_name) emp_list
from test_emp e,test_dept d
where e.DEPT_ID = d.DEPT_ID
group by dept_name
) pivot ( max(emp_list) for dept_name in (&#39;HR&#39;,&#39;IT&#39;,&#39;Sales&#39;) ); &lt;/span&gt;&lt;/pre&gt;
&lt;span style=&quot;font-family: verdana;&quot;&gt;&lt;br /&gt;&lt;/span&gt;&lt;br /&gt;
&lt;span style=&quot;font-family: verdana;&quot;&gt;Tags: LISTAGG ORACLE 11g , PIVOT ORACLE 11g&lt;br /&gt;&lt;br /&gt;&lt;/span&gt;&lt;/blockquote&gt;
&lt;/div&gt;</content><link rel='replies' type='application/atom+xml' href='http://j-oracle.blogspot.com/feeds/564622367432099243/comments/default' title='Post Comments'/><link rel='replies' type='text/html' href='http://j-oracle.blogspot.com/2012/01/listagg-and-pivot-in-oracle-11g.html#comment-form' title='0 Comments'/><link rel='edit' type='application/atom+xml' href='http://www.blogger.com/feeds/3979653527893526042/posts/default/564622367432099243'/><link rel='self' type='application/atom+xml' href='http://www.blogger.com/feeds/3979653527893526042/posts/default/564622367432099243'/><link rel='alternate' type='text/html' href='http://j-oracle.blogspot.com/2012/01/listagg-and-pivot-in-oracle-11g.html' title='LISTAGG and PIVOT in ORACLE 11g'/><author><name>Jeneesh</name><uri>http://www.blogger.com/profile/01069422196141471281</uri><email>noreply@blogger.com</email><gd:image rel='http://schemas.google.com/g/2005#thumbnail' width='16' height='16' src='https://img1.blogblog.com/img/b16-rounded.gif'/></author><thr:total>0</thr:total></entry></feed>