Category: Oracle

Peoplesoft – Printing Balance sheet tree

with mytree as (select distinct tnode.tree_name, tnode.parent_node_num, tnode.tree_node_num, tnode.tree_node, tlvl.tree_level, glacct.account, glacct.account_type, tnode.parent_node_name, glacct.descr from sysadm.pstreenode tnode, sysadm.pstreeleaf tleaf, sysadm.ps_gl_account_tbl glacct, sysadm.pstreelevel tlvl where tnode.setid=tleaf.setid and tnode.tree_name=tleaf.tree_name and tnode.effdt=tleaf.effdt and tleaf.tree_node_num between tnode.tree_node_num and tnode.tree_node_num_end and glacct.account between tleaf.range_from and…

Finding client side port in Oracle

select machine||':'||port from gv\$active_session_history where machine like '%app01%' and inst_id = 1" | sort -u ———————————————————— MACHINE||':'||PORT             hostname.domain:34218 MACHINE||':'||PORT             hostname.domain:34252 MACHINE||':'||PORT             hostname.domain:34312 MACHINE||':'||PORT             hostname.domain:34313 MACHINE||':'||PORT             hostname.domain:34314 MACHINE||':'||PORT             hostname.domain:34317 MACHINE||':'||PORT             hostname.domain:34319 MACHINE||':'||PORT             hostname.domain:34326 MACHINE||':'||PORT             hostname.domain:34328 MACHINE||':'||PORT             hostname.domain:34331 MACHINE||':'||PORT             hostname.domain:34332 MACHINE||':'||PORT             hostname.domain:34334…

Oracle materialized view and query rewrite

Just a simple example to show what it takes to get this to work… SQL> set lines 1000 trims on pages 100 SQL> explain plan for select count(*) from member; Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT ——————————————————————————– Plan hash…

Rolling your own Grid Control with AQ

DBControl can do the same thing, but if you have more than one database on a server, this is where something like this will come in handy. PLSQL setup, you should only have to change the SHOWARD part… begin dbms_aqadm.grant_queue_privilege…