Java Software Development

If a query exceeds the oracle.jdbc.ReadTimeout without receiving any data, an exception is thrown and the connection is terminated by the Oracle driver on the client. Unfortunately the session will still be queued on the database and continue to wait for locks, hold any current locks, and complete any DML/PL*SQL procedures that are pending on the server-side of the orphaned connection. If an application, on a another connection, due to ReadTimeout exception, retries DML/PL*SQL  which requires locks, those queries will queue behind the initial DML/PL*SQL. This will increase the workload exacerbating the situation. On applications with retries, this can be observed by querying the v$session table  or gv$session on RAC and noting new sessions started periodically based on the ReadTimeout interval. The sessions may often have the same SQL_ID and/or SQL_HASH_VALUE. If stmt.setQueryTimeout(Seconds) is issued and the statement exceeds the timeout, it will attempt to cancel the associated

If a session holds db locks and is abnormally terminated (no fin/ack), the locks will persist until the db session is closed, typically around 2 hours and 12 minutes with default network tcp_keepalive settings. Abnormally terminated does not include CTRL C or kill -9. The ojdbc6 driver apparently has a shutdown hook thread that closes the connections in graceful shutdowns. The OS apparently closes the connections when processes are killed. Abnormally terminated might include power failure, firewall failures, Out Of Memory, OS/kernel crash, network connection failure, JBoss or other server node failure, etc. This was easily reproduced by creating a  process that connected to the db and updated a record but did not commit. While the first process was waiting, a second process with a contending update was started which blocked on the first update. The first clients network cable was then disconnected. The second clients transaction waited for approximately 2 hours until the database O

  private static Log Log = LogFactory.getLog(LogMBean.class);   public void LogMBeans(String nameContains, String valueContains) throws IntrospectionException,       InstanceNotFoundException, ReflectionException   {     final List servers = new LinkedList ();     servers.add(ManagementFactory.getPlatformMBeanServer());     servers.addAll(MBeanServerFactory.findMBeanServer(null));     for (final MBeanServer server: servers)     {       Log.error("Server: " + server.getClass().getName());       final Set mbeans = new HashSet ();       mbeans.addAll(server.queryNames(null, null));       for (final ObjectName mbean: mbeans)       {         if (nameContains != null && mbean.toString().contains(nameContains) == false)         {           continue;         }         String mbeanName = mbean.toString();         if (valueContains == null || valueContains.isEmpty())         {           Log.error("mbean: " + mbeanName);         }      

service:jmx:rmi:///jndi/rmi:// : /jmxrmi Java based Nagios plugin for JMX http://exchange.nagios.org/directory/Plugins/Java-Applications-and-Servers/check_jmx/details java -jar check_jmx.jar       –U $ACTIVEMQ_JMX_URL       -O "org.apache.activemq:BrokerName= ,Type=Broker"        -A         java -jar check_jmx.jar    –U $ACTIVEMQ_JMX_URL    -O org.apache.activemq:BrokerName= ,Type=Queue,Destination= "     -A  

For rerunnable migration this was necessary to conditionally compile/execute plsql that had a static cursor that was referencing a table that might not exist. The static cursor was required for updating where current of. If the table didn't exist the static cursor definition wouldn't compile. DECLARE BEGIN  EXECUTE IMMEDIATE 'DECLARE     BEGIN       execute immediate ''select &7 from dual'';     END;'; END; /

DECLARE   CURSOR sqlCur IS SELECT id FROM (SELECT id, count(1) cnt FROM <someTable> GROUP BY id) WHERE cnt > 1;   recId <someTable>.id%TYPE;   newId <someTable>.id%TYPE;   fixSql VARCHAR2(500);   CURSOR dupeCur (recId <someTable>.id%TYPE) IS SELECT id FROM <someTable> WHERE id=recId FOR UPDATE of id; BEGIN     DBMS_OUTPUT.PUT_LINE('<someTable> checking and fixing dupe ids before creating PK');     --update 1 record from each id with a dupe     --repeat until none remaining     < >     LOOP       OPEN sqlCur;       FETCH sqlCur INTO recId;       EXIT dupe_detect WHEN sqlCur%NOTFOUND;         < >       LOOP         SELECT <someTable>_seq.nextval INTO newId FROM dual;         FOR rec IN dupeCur(recId) LOOP           --update only the first record and exit           UPDATE <someTable> SET id = newId WHERE CURRENT OF dupeCur;           EXIT;         END LOOP;         COMMIT;             FETC

The fastest way I've found over the years to generate and load data into Oracle, is to use sqlldr in direct mode reading from a linux pipe with all constraints disabled. Since  everything on linux is a file, any program can generate data and write it to the pipe/file. Disabling all constraints first dramatically increases the speed of loading. Indexes need to be rebuilt after direct loading since they are invalidated. Create a loader.ctl file which directs sqlldr how and where to load the data. The control file below instructs sqlldr to read the comma separated column data (CSV) from the /tmp/loadData.pipe file and write it to the table in direct mode which bypasses logging for speed. It should be noted that the data in the table is not recoverable should the database need to be restored unless you take a backup after the  load. Only the initial load is not logged. All future transaction against the data are logged and recoverable.  OPTIONS (DIRECT=TRUE)            UNRECOV

  public static void main(String[] args)   {     MemoryMXBean mbean = ManagementFactory.getMemoryMXBean();     System.out.println(mbean.getHeapMemoryUsage());     System.out.println("Avail " + (mbean.getHeapMemoryUsage().getMax() - mbean.getHeapMemoryUsage().getUsed()) / 1024 + "KB");          List mbeans = ManagementFactory.getMemoryPoolMXBeans();     for (MemoryPoolMXBean bean : mbeans)     {         String name = bean.getName();         MemoryType type = bean.getType();         MemoryUsage usage = bean.getUsage();         MemoryUsage peak = bean.getPeakUsage();         MemoryUsage coll = bean.getCollectionUsage();                  System.out.println(String.format("%s, type=%s, usage=%s, peak=%s, coll=%s", name, type, usage, peak, coll));     }   }

http://www.oracle.com/technetwork/database/enterprise-edition/memory.pdf 11.2 JDBC Developers Guide  HTML 11.2 JDBC Developers Guide - cache configuration and use  PDF The property can either be set as a System property via -D or as a connection property  on the getConnection method. Note that setting freeMemoryOnEnterImplicitCache does not  cause the parameter value buffers to be released, only the row data buffers. oracle.jdbc.freeMemoryOnEnterImplicitCache=true/false The default is Integer.MAX_VALUE. This is the  maximum size for a buffer which will be stored in the internal buffer cache. A single size is used  for both the char[] and byte[] buffers. The size is in chars for the char[] buffers and bytes for the  byte[] buffers. It is the maximum buffer size, not a predefined size. oracle.jdbc.maxCachedBufferSize= Integer.MAX_VALUE oracle.jdbc.useThreadLocalBufferCache=true/false With the 11.2 driver setting maxCachedBufferSize can  sometimes improve per

This Java code creates a multi-dimensional (2d) array and passes it to an Oracle PL/SQL Stored Procedure as varray or varrays which then iterates the multidimensional varray collections and inserts the data into a table. Oracle 11g R2 (11.2) Documentation package oracleMultiDimensionalArrays; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import oracle.sql.ARRAY; import oracle.sql.ArrayDescriptor; public class Test {   public static void send() throws SQLException   {     DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());     Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@dbIP:1521:SID", "troy_a", "troy_a");     ArrayDescriptor desc = ArrayDescriptor.createDescriptor("VARRAY2", conn);     int[][] elements = {{11, 12}, {21, 22}, {31, 32}};     ARRAY array2 = new ARRAY(desc, conn, elements);     CallableStatemen

How to execute anonymous PL/SQL from Java and return resultset Examples of PL/SQL While loop Loop labels Loop control bind variables in Dynamic execution set serveroutput on; declare   entityId data_table.entity_id%TYPE := 123;   delimiter char := '';   more char := 'T';   cnt NUMBER := 1;   lastTs data_table.timestamp%TYPE := get_ms_from_date(sysdate);   maxTs data_table.timestamp%TYPE;   timestampInClause VARCHAR2(100);   queryStr VARCHAR2(2000);   queryCnt NUMBER; begin   <<ts_loop>>   while more = 'T' and cnt <= 5   LOOP     select max(timestamp) into maxTs from data_table where entity_id = deviceId and timestamp < lastTs;     IF (maxTs IS NOT NULL) THEN       timestampInClause := timestampInClause || delimiter || maxTs;       lastTs := maxTs;       delimiter := ',';       cnt := cnt + 1;     ELSE       EXIT ts_loop;     END IF;   END LOOP ts_loop;   DBMS_OUTPUT.PUT_LINE('timestampInClause

Walk the last 'n' max(timestamp) records: AND id = 123 AND a.timestamp   >= (SELECT MAX(TIMESTAMP) FROM data_table WHERE id = 123 AND TIMESTAMP   < (SELECT MAX(TIMESTAMP) FROM data_table WHERE id = 123 AND TIMESTAMP   < (SELECT MAX(TIMESTAMP) FROM data_table WHERE id = 123 AND TIMESTAMP   < (SELECT MAX(TIMESTAMP) FROM data_table WHERE id = 123 AND TIMESTAMP   < (SELECT MAX(TIMESTAMP) FROM data_table WHERE id = 123))))) This method was chosen because the data must be ordered descending and using any particular time range may cover an enormous amount of high activity data or little. Always wanted the last 5 records and didn't want to incur high sort costs when lots of data was in the window. The index columns included (id, timestamp) This is a very fast query because the timestamp b-tree can be walked quickly.

To shrink/compact VirtualBox image: Was .vmdk and compact only works on .vdi. Clone the image and format to .vdi: First created another partition with enough space then cloned and formatted: /usr/lib/virtualbox/VBoxManage clonehd /media/winXpVM/tcollinsworth-lt/tcollinsworth-lt.vmdk /media/winXpVM2/tcollinsworth-lt/tcollinsworth-lt.vdi  --format VDI Added the disk to VM in VirtualBox Manager and removed the old one. Only change was to switch the disk to primary master. To convert .img to .vdi: VBoxManage convertdd  winXP.img winXP.vdi Started the OS and cleaned it up disk: downloads temp files trash restore points etc. Zeroed unused disk blocks with:  sdelete -z Shutdown OS Compact the .vdi /usr/lib/virtualbox/VBoxManage modifyhd /media/winXpVM2/tcollinsworth-lt/tcollinsworth-lt.vdi --compact

Why does count, min, max explain plan show a sort operation? SELECT count(1) FROM foo; SELECT min(x) FROM foo; Count/min/max/etc. will always show a sort operation in the explain plan because they are aggregate functions with an implicit group by even if they return only one row. They share the common code path even though it is effectively a no-op. Faster count avoiding full index/table scan with sampling: 1% sample SELECT COUNT(1) * 100 FROM  SAMPLE (1); Find ALL FKs to a table: SELECT owner,   constraint_name,   constraint_type,   table_name,   r_owner,   r_constraint_name FROM all_constraints WHERE constraint_type  ='R' AND r_constraint_name IN   (SELECT constraint_name   FROM all_constraints   WHERE constraint_type IN ('P','U')   AND table_name         ='DATA_AUDIT'   );

http://ubuntuforums.org/showthread.php?t=608212 http://www.x.org/releases/X11R7.6/doc/libXext/dpmslib.html

http://sfyang-en.blogspot.com/2008/02/rotate-cube-in-compiz-with-wmctrl.html Initiate rotate from command line, Exc exits rotation: dbus-send --type=method_call --dest=org.freedesktop.compiz /org/freedesktop/compiz/rotate/allscreens/initiate_button org.freedesktop.compiz.activate string:'root' int32:`xwininfo -root | grep id: | awk '{ print $4 }'`

http://stackoverflow.com/questions/2297347/splitting-a-string-at-every-n-th-character String[] tokens = s . split ( "(?<=\\G...)" ) The regex  (?<=\G...)  matches an empty string that has the  last match  ( \G ) followed by  three characters  ( ... )  before  it ( (?<= ) )

Flushing caches for testing: ALTER SYSTEM FLUSH BUFFER_CACHE; alter system flush shared_pool; Finding unusable, invalid, and disabled indexes: select owner, index_name from dba_indexes where status='UNUSABLE'; select index_name, status from dba_indexes where status = 'DISABLED'; select owner,table_name,index_name,last_analyzed,status from dba_indexes where status != 'VALID'; Rebuild index: alter index XXX.XXX rebuild;   BEGIN     FOR i IN       (SELECT index_name         FROM  dba_indexes          WHERE  status != 'VALID'                         AND  table_name=' ')     LOOP       EXECUTE immediate 'alter index ' || i.index_name || 'rebuild';     END LOOP;   END;   / Compute schema statistics: Auto is superior to estimate Use "cascade => TRUE" to also update all index stats on the table. EXEC DBMS_STATS.gather_table_stats(

Oracle 11g R2 supports automatic partition management. Creating Partitions White paper

$ sudo apt-get install lvm2 $ sudo modprobe dm-mod Scan your system for LVM volumes: $ sudo vgscan Activate the volume: $ sudo vgchange -ay vg_troylt Find the logical volume under LV column: $ sudo lvs Example: LG VG ... lv_root vg_troylt ... Create a mount point for that volume: $ sudo mkdir /mnt/rh_linux Mount it: $ sudo mount /dev/vg_troylt/lv_root /media/rh_linux -o ro,user Credit

When installing, put the GRUB1 bootload onto the same partition or it will replace the Ubuntu GRUB2 bootloader. When launching enter 'c' at grub menu to go into command line. Enter chainloader (hd0, and hit tab to list the partition options. Select the Red Hat partition # (label it for easy identification) and close the command with )+1 chainloader (hd0, msdos4)+1 boot The firewall is not open for port 1521. Make sure to set ORACLE_HOME and ORACLE_HOME_LISTENER in oracle .bash_profile Use dbstart and dbshut to start and shutdown the database Have to edit /etc/hosts to associate the host name and IP address, have to update it when DHCP changes. Have to edit tns listener config file to point to the correct hostname or IP or it won't start. Customizing Grub2 menu: Edit: /etc/grub.d/40_custom Add: menuentry "Redhat" { insmod ext2 set root=(hd0,3) linux /boot/vmlinuz-2.6.32-24-generic root=/dev/sda3 ro initrd /boot/initrd.img-2.6.32-24-generic } Update grub2 menu: sud

Disconnect sessions: select * from v$session; --single node select * from gv$session; --RAC, multi-node alter system kill session 'sid,serial#'; BEGIN FOR c IN ( SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program, s.machine, s.process, s.status, s.schemaname FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE s.type != 'BACKGROUND' and s.USERNAME='SOME USER NAME' ) LOOP EXECUTE IMMEDIATE 'alter system kill session ''' || c.sid || ',' || c.serial# || ''''; END LOOP; END;

http://blog.arkzoyd.com/2011/11/oracle-database-11g-and-ubuntu-1110.html

Java 6 JMX Code Example Add these properties to the target VM to specify the jmx port and allow remote connections so you can get to it from other systems without the need for authentication. JAVA_OPTS="$JAVA_OPTS -Dcom.sun.management.jmxremote.port=9999" JAVA_OPTS="$JAVA_OPTS -Dcom.sun.management.jmxremote.authenticate=false" JAVA_OPTS="$JAVA_OPTS -Dcom.sun.management.jmxremote.ssl=false" The example below demonstrates the following: Connection Listing all MBeans Listing all MBean attributes, types, and mutability Listing all MBean operations, parameters, and return types Getting an attributes current value Setting an attributes value Invoking operations import java.io.IOException; import java.util.Iterator; import java.util.Set; import javax.management.Attribute; import javax.management.AttributeNotFoundException; import javax.management.InstanceNotFoundException; import javax.management.IntrospectionException; import javax.management.InvalidAttributeVal