Oracle工作小結(20080901)

1.ORA-29275部分多字節字符 (開始--運行--regedit--HKEY_LOCAL_MACHINE--SOFTWARE--Oracle--KEY_OraClient10g_home1)    NSL_LANG  american_taiwan.zht16big5(備注:公司所用系統用繁體系統)

 

2. SELECT TRUNC(12.265,2) FROM DUAL;RETURN 12.26

   SELECT ROUND(12.265,2) FROM DUAL;RETURN 12.27

 

3.求今天與某一天相差天數

SELECT   FLOOR(SYSDATE - TO_DATE('20080401','YYYYMMDD')) FROM DUAL

4. ORA-01779: cannot modify a column which maps to a non key-preserved table

  解決方法: UPDATE

            ( SELECT /*+ BYPASS_UJVC */ A.STATUS STATUS1 ,B.STATUS STATUS2

              FROM TEST_0418 A,TEST_0418_1 B

              WHERE A.ID = B.ID

            )

            SET STATUS1=STATUS2

 

5.意外刪除一張表,解決方法:

FLASHBACK TABLE T TO BEFORE DROP

 

6.SELECT TO_DATE(SYSDATE,’YYYY-MM-DD’) FROM DUAL RETURN 0025/4/8,用RO_DATE則RETURN 2008-04-25

 

7.將所有表名改為大寫

SELECT ‘rename ‘||table_name||’to’upper(table_name)||’;’from user_tables;

加入spool c:/a.txt,spool off @c:/a.txt