1、安裝logminer，執行兩個PL/SQL程式（9i已建立，所以我沒有執行）
$ORACLE_HOME/rdbms/admin/dbmslm.sql #建立DBMS_LOGMNR的package，用來分析日誌文件。
$ORACLE_HOME/rdbms/admin/dbmslmd.sql #建立DBMS_LOGMNR_D的package，用來建立字典文件。
2、找出用於LogMiner分析的資料存放的位置，以參數utl_file_dir的值為主，
目前分類：Oracle (119)
- Apr 30 Wed 2014 18:03
Oracle利用logminer分析archivelog、redolog
- Apr 01 Tue 2014 15:24
Oracle SQL Loader 使用範例
SQL Loader可以快速將大量的資料匯入Oracle的Table中。
例如有一個檔案test01.csv的資料要上傳到tmp_a的Table上。
- Mar 27 Thu 2014 16:06
執行Gather Schema Statistics錯誤：ORA-20005: object statistics are locked
新的Oracle 11g資料是由datapump移轉過來的，在執行Gather Schema Statistics時發生錯誤：
ORA-20005: object statistics are locked (stattype = ALL)
解決方案：
- Mar 25 Tue 2014 16:42
Oracle 11g Table名稱大小寫的影響
因為Oracle DB從9i升到11g，所以廠商幫我們做table statistics時，出現了錯誤，
錯誤的原因是找到三個表格，但是這三個表格在DB是存在的，原來是名稱大小寫的原故。
在建立表格的預設上都會變成大寫的名稱，但是可能有其他的工具或在表格名稱的前後加上雙引號，
例如：create table "test01"…，因為表格名稱帶有雙引號，所以DB會建立小寫的表格名稱。
- Mar 12 Wed 2014 10:12
Oracle DB HWM（high water mark）對查詢的效能影響
HWM高水位是指一個segment（Table/Index）中已使用與未曾使用的Block分界線，
也就是說在HWM之下的Block是已使用（包含曾使用），在HWM之上的Block是未曾使用。
當不斷地insert資料時，HWM也會跟著向上移動，但是Delete資料，HWM卻不會往下移動，
就像飲料一樣，在裝杯時水量不斷增加，但是開始喝飲料時，水量就下降，
但是最高的水痕依然留在杯上，沒有降低。
- Feb 10 Mon 2014 14:49
刪除Statspack歷史資料
一開始我是用delete stats$snapshot來刪除snap_id之前的資料，
delete stats$snapshot會把其他相關的Statspack Tables的資料也一併清除，
完成後卻發現Tablespace的使用空間並未減少，倒是舊snap_id資料已經刪除了。
網路上有人提到幾種方式來解決這個問題：
- Jan 28 Tue 2014 09:11
LTRIM、RTRIM除掉指定字串的函數
LTRIM，去掉左邊指定的字串，預設為空白值
select ltrim ('aaabbbccc','aaa') from dual;
結果：bbbccc
select ltrim ( ' aaabbbccc') from dual;
結果：aaabbbccc
- Jan 08 Wed 2014 13:38
查Table或PROCEDURE被誰Lock住
v$access：displays information about locks that are currently imposed on library cache objects
v$access.type有：CURSOR、FUNCTION、JAVA CLASS、LIBRARY、NON-EXISTENT、PACKAGE、
PROCEDURE、SEQUENCE、SYNONYM、TABLE、TRIGGER、TYPE、VIEW
- Dec 30 Mon 2013 09:25
利用TOAD找出Lock的Session
- Dec 27 Fri 2013 16:43
Oracle 9iR2可以利用flashback query來查詢undo的資料
Oracle 9iR2可以利用flashback query來查詢undo的資料，
只要是Undo的資料尚未被覆寫，就有機會把資料還原。
先將測試tmp_ccc內的資料清除
SQL> select count(1) from tmp01;
COUNT(1)
----------
10
SQL> delete tmp01 ;
10 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(1) from tmp01;
COUNT(1)
----------
0
1、利用SCN查詢
找出現在的SCN
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM dual;
GET_SYSTEM_CHANGE_NUMBER
----------------------------
450302
查詢tmp01在SCN 450301時的資料
SELECT count(1) FROM tmp01 AS OF SCN 450301;
COUNT(1)
----------
10
2、利用時間查詢
找30分鐘前的資料
select count(1) from tmp_ccc AS OF TIMESTAMP TO_TIMESTAMP(sysdate-30/1440);
COUNT(1)
----------
10
也可以直接指定時間，先設定時間格式
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
查現在的時間，看看時間格式
SQL> SELECT LOCALTIMESTAMP FROM dual;
LOCALTIMESTAMP
---------------------------------------------------------------------------
27-DEC-2013 15:59:19
SQL> SELECT count(1) FROM tmp01 AS OF TIMESTAMP TO_TIMESTAMP('27-DEC-2013 15:29:00');
COUNT(1)
----------
10
只要是Undo的資料尚未被覆寫，就有機會把資料還原。
先將測試tmp_ccc內的資料清除
SQL> select count(1) from tmp01;
COUNT(1)
----------
10
SQL> delete tmp01 ;
10 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(1) from tmp01;
COUNT(1)
----------
0
1、利用SCN查詢
找出現在的SCN
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM dual;
GET_SYSTEM_CHANGE_NUMBER
----------------------------
450302
查詢tmp01在SCN 450301時的資料
SELECT count(1) FROM tmp01 AS OF SCN 450301;
COUNT(1)
----------
10
2、利用時間查詢
找30分鐘前的資料
select count(1) from tmp_ccc AS OF TIMESTAMP TO_TIMESTAMP(sysdate-30/1440);
COUNT(1)
----------
10
也可以直接指定時間，先設定時間格式
SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
查現在的時間，看看時間格式
SQL> SELECT LOCALTIMESTAMP FROM dual;
LOCALTIMESTAMP
---------------------------------------------------------------------------
27-DEC-2013 15:59:19
SQL> SELECT count(1) FROM tmp01 AS OF TIMESTAMP TO_TIMESTAMP('27-DEC-2013 15:29:00');
COUNT(1)
----------
10
- Dec 19 Thu 2013 15:49
自訂V$SESSION Action及Module欄位
V$SESSION有兩個欄位Action及Module可以用DBMS_APPLICATION_INFO來自訂，
例如：帳號、程式名稱，這樣就可以知道Session是由誰及執行那一支程式了。
Syntax
DBMS_APPLICATION_INFO.SET_MODULE (
- Nov 15 Fri 2013 12:03
用DBMS_METADATA.GET_DDL取得表格的DDL
雖然我都是用Toad、SQL Developer等工具來取得DDL，但是可能偶而會遇到沒有工具的時候。
所以找了一下不用工具而取得DDL的方法。
語法如下，會傳回Clob型態的值
DBMS_METADATA.GET_DDL (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;
範例如下：
單一Table的DDL
set heading off;
set echo off;
Set pages 999;
set long 90000;
spool c:\ddl.txt
select dbms_metadata.get_ddl('TABLE','TMP_CCC','TEST_USER') from dual;
spool off;
取出當前使用者的整個Schmea的Table DDL
set pagesize 0
set long 90000
set feedback off
set echo off
spool c:\schema.sql
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;
spool off;
取出所有的Tablespace的DDL
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;
所以找了一下不用工具而取得DDL的方法。
語法如下，會傳回Clob型態的值
DBMS_METADATA.GET_DDL (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;
範例如下：
單一Table的DDL
set heading off;
set echo off;
Set pages 999;
set long 90000;
spool c:\ddl.txt
select dbms_metadata.get_ddl('TABLE','TMP_CCC','TEST_USER') from dual;
spool off;
取出當前使用者的整個Schmea的Table DDL
set pagesize 0
set long 90000
set feedback off
set echo off
spool c:\schema.sql
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
FROM USER_TABLES u;
spool off;
取出所有的Tablespace的DDL
SELECT DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)
FROM DBA_TABLESPACES TS;
- Nov 14 Thu 2013 16:18
自動備份alert.log及刪除dump file的scripts
因為DB alert.log資料太多，以致閱讀及查詢上較為不便，顧問也建議如此做法，
所以設計了一個Script，可以設定排程來定時備份alert.log，且清空alert.log的內容，
這樣每次查詢時就不會有那麼多的資料。順便也將14天前DB Dump出來Trace file也一併清除。
系統是HP-UX，程式如下：
- Nov 12 Tue 2013 16:21
dba_jobs_running查詢速度很慢
網路找的答案是Oracle 9i的Bug，可以用hint來試著解決：
select /*+ rule */ * from dba_jobs_running;
<Bug:2624130>
- Nov 12 Tue 2013 16:11
刪除一個執行很久的JOB
先以下列的SQL找出正在running的job，並確認要刪除的job及sid。
select /*+ rule */ * from dba_job_runnings;
假設找到的 job=1640，sid=32
查job的內容
select * from dba_jobs where job=1640;
select /*+ rule */ * from dba_job_runnings;
假設找到的 job=1640，sid=32
查job的內容
select * from dba_jobs where job=1640;
- Nov 11 Mon 2013 08:45
Temporary Tablespace的實際用量與OEM顯示不同
在Oracle Enterprise Manager中顯示Temporary Tablespace已經滿了，
的確在前一段日子，因為一支客製SQL的緣故而導致Temporary使用率100%，
ERP的作業也就受到了影響，alter log也記錄了ora的錯誤。
但是當時強制將SQL停掉後，ERP也就恢復了正常，但是100%卻再也沒有下降過。
之前的作法先加大Temporary的空間，然後在星期日停機的時候重建Temporary，
- Oct 24 Thu 2013 10:36
查Temporary Tablespace實際使用量
--查Session在Temporary的實際使用量
select vs.sid,vs.serial#,vp.spid,vs.module,vs.action,vtu.username,sum(vtu.blocks)*8/1024
from V$TEMPSEG_USAGE vtu,v$session vs,v$process vp
where vtu.session_num=vs.serial#
and vtu.session_addr=vs.saddr
- Oct 09 Wed 2013 09:15
OEM中Temporary使用率與實際使用率不同
資料庫版本：Oracle 9.2.0.7 64bit
作業系統：HP-UX 11i v1（11.11）
下圖是Oracle Enterprise Manager的Temporary Tablespace的狀況，使用率一直都是100%，
但是顧問說這是這個一個Bug，並非正確的數據。
- Sep 10 Tue 2013 13:12
同類型的資料做分類群組排序時，可用函數dense_rank()、row_number()、rank()
有時候我們想要將同類型的資料做分類群組排序時，可用函數dense_rank()、row_number()、rank()
例如要抓取每個班級數學成績前10名的學生 ，student ：學生資料 ；score_info ：數學成績資料
- Aug 02 Fri 2013 17:22
ORA-04031: "unable to allocate n bytes of shared memory ("shared pool" …
今天AP人員告訴我在執行一段程式時發生了ORA-04031的錯誤：
ORA-04031: "unable to allocate 6488 bytes of shared memory ("shared pool" …
為了讓程式可以正常執行，只好更改shared pool的大小。
但是直接加大shared pool會產生下列的錯誤
