We had an ODI job that was dropping and creating 296 tables (and indexes) every two minutes. The drops were not issued with a “purge” directive appended, so we created, dropped, and pushed to the recycle bin almost 8 million…
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…
Pushing a file to Oracle database to write to database filesystem
import java.sql.*; import java.io.*; public class sendFile { public static void main (String args[]) throws Exception { File file = new File(“a.txt”); FileInputStream fis = new FileInputStream(file); byte[] data = new byte[(int)file.length()]; fis.read(data); fis.close(); String s = new String(data, “UTF-8”);…
Broken out into two routes… Consume web service and enqueuer in ActiveMQ Dequeue from ActiveMQ and persist in PO object in Oracle database <route id=”get-po”> <from uri=”timer://simpleTimer?period=30s”/> <to uri=”http://cmhlcarchapp01:8080/tradestone/send_po.jsp“/> <to uri=”jms:queue:testMQ” /> </route> <route id=”insert-db”> <from…
Oracle global temporary table and connection pool
Our question was what happens to the rows in a global temporary table when populated from a session obtained from a connection pool. Are they still there for the next application user to borrow the same session from the pool?…
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…
Command line script to print referential integrity constraints
Nothing fancy today, just a quickie as indicated in the subject line of the post… set serverout on format wrapped declare l_level number := 0; –get only tables with no parent tables cursor main is select distinct table_name,constraint_name from user_constraints…
Calculating waits for a session for a given time window
We used this to quickly determine where a specific session was spending its time. This simply creates associative arrays for events, the time waited, and the total number of waits for that event, all at a specific point in time.…
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…