Some Simple Date Related Oracale SQL

1. Add Date
select (sysdate)+1 from dual;

2. Add Months
select add_months(sysdate, 2) from dual;

or Minus Months
select add_months(sysdate, -2) from dual;

3. Return Date Format
    a. select sysdate from dual -- return 10/19/2011 4:49:02 PM
    b. select to_char(sysdate, 'day') from dual -- return 'monday'
    c. select to_char(sysdate, 'DAY') from dual -- return 'MONDAY'
    d. select to_char(sysdate, 'dd-mm-yyyy') from dual -- return 29-10-2011
    e. select to_date(sysdate) from dual -- return 19-OCT-2011
    f. select to_char(sysdate, 'd') from dual -- return numeric day of week.
       -- For eg : Wednesday = 4
    g. select to_char(sysdate, 'dd') from dual -- return date only
    h. select to_char(sysdate, 'dd-mm') from dual -- return date and month.

Some Normal Oracle SQL Scripts

Delete column
alter table table_name drop column col_name1; -- drop one column
alter table table_name drop (col_name1, col_name2); -- drop many columns

Drop table
drop table table_name

Add column for
1.number
alter table table_name
add column_name number(10,4) NULL;

2.varchar2
alter table table_name
add column_name varchar2(10) NULL;



Edit Column name
alter table_name rename column column_name to new_colomn_name ;

sum() of column
SELECT SUM(column_name) FROM table_name

Update
UPDATE table SET column_name=value (, column_name2=value2 )WHERE xxx='yyy2';

Edit column size
ALTER TABLE table_name MODIFY (column_name varchar2(4000));

How to Create a View?

CREATE OR REPLACE VIEW AS view_name
SELECT * FROM ABC ;

select * from view_name;

Oracle String Tokenizer

select
get_token('foo,bar,baz',1), -- 'foo'
get_token('foo,bar,baz',3), -- 'baz'

get_token('a,,b',2), -- '' (null)
get_token('a,,b',3), -- 'b'
get_token('a|b|c',2,'|'), -- 'b'
get_token('a|b|c',4,'|') -- '' (null)
from
dual

＜转载＞Stuck Thread

A stuck thread means a thread is blocked and can't return to the thread pool smoothly in a given period of time. When an application thread is blocked unintentionally, it means it can't quickly complete its dispatch and be reused. In most of production situations, the root cause of these stuck threads is also the root cause of bad system performance because it interferes with regular task execution. [It's also a performance issue for producers and healthy consumers. < 1 ] (request frequency) < (healthy thread count for request execution/average measured request execution time per healthy thread.]

Blocking without specifying a network connect or read timeout is the most frequent reason we have seen. When we don't manually configure a timeout for each method call involving networking, it will have a potential blocking behavior by the underlying physical socket read/connect characteristic. While waiting infinitely for the response from the other side, the native OS networking layer probably throws an I/O exception. By default this behavior takes an unexpectedly long time (e.g., 240 seconds). Modern distributed systems need to factor in this situation (especially, Web Services invocations). Though we may set timeouts for well-known protocols via some system properties (e.g., sun.net.client.defaultConnectTimeout and sun.net.client.defaultReadTimeout), the newer version of JDK might provide a generic mechanism to explicitly configure each default timeout value for those whose methods call socket connect/read as a security policy file. For example, com.sun.jndi.ldap.read.timeout (http://java.sun.com/docs/books/tutorial/jndi/newstuff/readtimeout.html) wasn't available prior to JDK 6.0 for LDAP service provider read timeout. Otherwise, when the problematic code isn't under the control of end users, it usually needs to restart the application to temporarily reset the abnormal phenomenon propagated from the other side. In addition, we should take into account whether the service we called is idempotent while analyzing this kind of issue in the design phase because we don't know whether the service at the other end keeps executing when the thread has ended its invocation after a timeout (see Figure 4).

The unexpectedly long execution time of a SQL statement is a common condition that causes a stuck thread. In the thread dump we collected, we can see that the stuck thread was running a network socket read for a long time without changes and the thread's stack trace contains many JDBC driver classes. Under these conditions, we can also check the status of the database it connected with and set the query timeout for all application code using a JDBC statement setQueryTimeout method. (Most JDBC drivers support this feature but we'd have to read the JDBC driver's release note first.) According to the different nature of every SQL query, it would be better to segregate the programs that have a longer execution time in another thread pool and tune the database table with indices for faster access. We would also need to check whether the JDBC driver is certified with the connected database. A sub-issue is the accessed table locked by other processes so the threads for the JDBC query couldn't continue because of table locking.

Resource contention is an issue that's hard to find if we don't get the entire thread dump to analyze. Basically, it's an issue of producers and consumers. Any limited resources on the system (JDBC connections, socket connections, etc.) will impact this issue. The best thing to do is look at the thread dump, get the stuck thread name from the log, and find the bottleneck that's causing the stuck thread.

File descriptor leaking is an issue that causes this phenomenon (Note that a Unix socket implementation requires a file descriptor). So the JVM should have enough file descriptor numbers to host our applications. Generally, we can adjust the open file limit with the Unix shell 'ulimit' command for the current shell. And we can list the open files with the public domain 'lsof' tool. It's intensely interesting that many developers don't explicitly use the 'close()' method in the final block when an object inherently provides a 'close()' method and want JVM to release these unclosed objects when garbage is collected. We should keep firmly in mind that that act is bad without closing the system resource after use. A special case is when the socket connections in the application don't close properly while still being underdeployed and then the application begins to throw an IOException with a 'Too many open files' message after repeated application redeployment.

Quotes from :
http://java.sys-con.com/node/358060 Looking Inside Stuck Thread