oracle如何插入timestamp資料

SQL> select * from t_timestamp;
 
A
--------------------------------------------------------------------------------
14-11月-12 10.46.24.195000 上午
 
SQL> show parameter time
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
cursor_space_for_time                boolean     FALSE
ddl_lock_timeout                     integer     0
distributed_lock_timeout             integer     60
log_checkpoint_timeout               integer     1800
nls_time_format                      string     
nls_time_tz_format                   string     
nls_timestamp_format                 string     
nls_timestamp_tz_format              string     
parallel_min_time_threshold          string      AUTO
resumable_timeout                    integer     0
timed_os_statistics                  integer     0
timed_statistics                     boolean     TRUE
 
SQL> alter session set nls_time_format='yyyy-mm-dd hh24:mi:ss.ff';
 
alter session set nls_time_format='yyyy-mm-dd hh24:mi:ss.ff'
 
ORA-01821: 日期格式無法識別

SQL> conn as sysdba
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as SYS
 
SQL> show parameter nls
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_calendar                         string     
nls_comp                             string      BINARY
nls_currency                         string     
nls_date_format                      string     
nls_date_language                    string     
nls_dual_currency                    string     
nls_iso_currency                     string     
nls_language                         string      AMERICAN
nls_length_semantics                 string      BYTE
nls_nchar_conv_excp                  string      FALSE
nls_numeric_characters               string     
nls_sort                             string     
nls_territory                        string      AMERICA
nls_time_format                      string     
nls_time_tz_format                   string     
nls_timestamp_format                 string     
nls_timestamp_tz_format              string   

SQL> alter system set nls_date_format='yyyy-mm-dd' scope=spfile;
 
System altered

sql> shutdown immediate
sql>startup

SQL> show parameter nls
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_calendar                         string     
nls_comp                             string      BINARY
nls_currency                         string     
nls_date_format                      string      yyyy-mm-dd

SQL> conn
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott

SQL> alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff';
 
Session altered

SQL> select * from t_timestamp;
 
A
--------------------------------------------------------------------------------
2012-11-14 10:46:24.195000
 
SQL> insert into t_timestamp values('2012-12-01 09:21:13.1');
 
1 row inserted
 
 
 
 SQL> select * from t_timestamp;
 
A
--------------------------------------------------------------------------------
2012-11-14 10:46:24.195000
2012-12-01 09:21:13.100000
 
 
小結；1,nls_timestamp_format與nls_date_format關聯，必須先配置後者
        不然會報ORA-01821: 日期格式無法識別
      2,timestamp資料可以用to_timestamp自定義格式插入資料，道理
      　和to_date一樣