GOOGLE來的

--欄位長度不足

--CREATE SNAPSHOT

create snapshot RD_HUMAN_VACATION_LOG_SUM
REFRESH
START WITH TO_DATE('2009-05-27 09.09.36','yyyy-mm-dd hh24.mi.ss')
NEXT (sysdate+1)
AS SELECT a.* from HUMAN_VACATION_LOG_SUM a;

--手動更新SNAPSHOT
EXEC DBMS_SNAPSHOT.REFRESH('SNAPSHOT_NAME');

--更改snapshot的時間
alter snapshot W00_ACCT_EX_VIEW2
refresh
START WITH to_date(to_char(sysdate+1,'dd-mm-yyyy')|| ' 03:05:30','dd-mm-yyyy
hh24:mi:ss')
NEXT to_date(to_char(sysdate+1,'dd-mm-yyyy')|| ' 03:05:30','dd-mm-yyyy
hh24:mi:ss')

High-Performance Oracle JDBC Programming

http://www.oracle.com/technetwork/articles/vasiliev-oracle-jdbc-090470.html

Using pooling techniques such as connection pooling and statement pooling can significantly improve performance of database-intensive applications, because it enables the reuse of objects that would otherwise need to be created from scratch, at the expense of time and resources.

Reusing database connection objects representing physical database connections utilized by an application can result in significant performance gains, provided that the application interacts with the database intensively, frequently re-establishing connections with the same parameters. On the other hand, you won’t benefit from using a connection pool if your application connects to its underlying database only rarely. In practice, though, many database-intensive applications can benefit from utilizing a connection pool, provided that the pool’s settings, such as those that put a limit on the maximum and minimum number of connections allowed, are optimized for that particular application.

Like connection pooling, statement pooling is a technique for improving application performance. You can achieve additional performance gains by pooling statements that are executed multiple times in the course of a run of your program. It’s important to realize, though, that statement pooling is not a silver bullet for performance problems. If you cache every single statement without distinguishing how many times it is executed in your program, you are unlikely to achieve any performance improvement. In fact, caching the statements that are issued only once during program execution may actually degrade performance, due to the overhead associated with putting and then keeping such statements in the cache.

This article shows you how to take advantage of pooling connections and statements to improve performance of data-intensive Java DataBase Connectivity (JDBC) programs interacting with Oracle Database via the Oracle JDBC thin driver. In particular, it looks at the Oracle Universal Connection Pool (UCP) for JDBC, which provides a full-featured connection pool implementation for caching JDBC connections. Finally it discusses how you might benefit from statement pooling, utilizing features specific to Oracle’s JDBC drivers as well as the new JDBC 4.0 methods added to the Statement interface and available in Oracle JDBC drivers supporting Java Development Kit (JDK) 1.6 and later versions.

plan_table

http://www.adp-gmbh.ch/ora/exp_plan/plan_table.html



CREATE TABLE PLAN_TABLE (

  STATEMENT_ID                    VARCHAR2(30),

  TIMESTAMP                       DATE,

  REMARKS                         VARCHAR2(80),

  OPERATION                       VARCHAR2(30),

  OPTIONS                         VARCHAR2(30),

  OBJECT_NODE                     VARCHAR2(128),

  OBJECT_OWNER                    VARCHAR2(30),

  OBJECT_NAME                     VARCHAR2(30),

  OBJECT_INSTANCE                 NUMBER(38),

  OBJECT_TYPE                     VARCHAR2(30),

  OPTIMIZER                       VARCHAR2(255),

  SEARCH_COLUMNS                  NUMBER,

  ID                              NUMBER(38),

  PARENT_ID                       NUMBER(38),

  POSITION                        NUMBER(38),

  COST                            NUMBER(38),

  CARDINALITY                     NUMBER(38),

  BYTES                           NUMBER(38),

  OTHER_TAG                       VARCHAR2(255),

  PARTITION_START                 VARCHAR2(255),

  PARTITION_STOP                  VARCHAR2(255),

  PARTITION_ID                    NUMBER(38),

  OTHER                           LONG,

  DISTRIBUTION                    VARCHAR2(30)

);

好用喔!一定要用用看

begin
loop
delete from log where log.guardname is null and rownum <= 100000;
exit when sql%rowcount < 100000;
commit;
end loop;
commit;
end;

一次1萬比資料

How to perform long deletes
http://www.idevelopment.info/data/Oracle/DBA_tips/PL_SQL/PLSQL_3.shtml

oracle sqldeveloper 中顯示 dbms_output

SET SERVEROUTPUT ON

BEGIN

dbms_output.enable;

dbms_output.put_line('Column value too long');

DBMS_LOCK.SLEEP (10);

END;

DECLARE

my_exception EXCEPTION;

PRAGMA EXCEPTION_INIT (my_exception, -1401);

BEGIN

INSERT INTO TPORTF (values.....)

EXCEPTION

WHEN my_exception THEN dbms_output.put_line('Column value too long');

END;