在轉資料的時候發生exp匯出資料時，不會導出空表。
使用exp/imp轉資料時，
從別的地方轉回來發現程式不能RUN，找了好久才知道原因。
Oracle 11g中，空的表格在exp指令中，為了節省空間並不會匯出表格。
為了解決這個問題，要將table的屬性改掉，才能順利匯出
從別的網站上找到的方法：
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;
以上的語句會匯出更改table屬性的語法，
執行那些語法就能夠順利將table匯出了！
另外，為了一勞永逸解決這些問題，記得更改系統參數。
alter system set deferred_segment_creation=false scope=both;
更改之後，新創立的table使用exp指令也可以輸出了。
參考網頁：
http://wanwentao.blog.51cto.com/2406488/545154
http://bloodsucker.pixnet.net/blog/post/40267539-oracle-11g-r2-%E9%82%A3-exp-imp-%E9%99%B7%E9%98%B1%E5%95%8F%E9%A1%8C
2014年12月18日 星期四
2014年10月31日 星期五
PIVOT and UNPIVOT
來源：http://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php
工作上用到，現在忙，有空再回來翻譯。
工作上用到，現在忙，有空再回來翻譯。
PIVOT and UNPIVOT Operators in Oracle Database 11g Release 1
This article shows how to use the new
PIVOT and UNPIVOT operators in 11g, as well as giving a pre-11g solution to the same problems.PIVOT
The
PIVOT operator takes data in separate rows, aggregates it and converts it into columns. To see thePIVOT operator in action we need to create a test table.CREATE TABLE pivot_test ( id NUMBER, customer_id NUMBER, product_code VARCHAR2(5), quantity NUMBER ); INSERT INTO pivot_test VALUES (1, 1, 'A', 10); INSERT INTO pivot_test VALUES (2, 1, 'B', 20); INSERT INTO pivot_test VALUES (3, 1, 'C', 30); INSERT INTO pivot_test VALUES (4, 2, 'A', 40); INSERT INTO pivot_test VALUES (5, 2, 'C', 50); INSERT INTO pivot_test VALUES (6, 3, 'A', 60); INSERT INTO pivot_test VALUES (7, 3, 'B', 70); INSERT INTO pivot_test VALUES (8, 3, 'C', 80); INSERT INTO pivot_test VALUES (9, 3, 'D', 90); INSERT INTO pivot_test VALUES (10, 4, 'A', 100); COMMIT;
So our test data starts off looking like this.
SELECT * FROM pivot_test;
ID CUSTOMER_ID PRODU QUANTITY
---------- ----------- ----- ----------
1 1 A 10
2 1 B 20
3 1 C 30
4 2 A 40
5 2 C 50
6 3 A 60
7 3 B 70
8 3 C 80
9 3 D 90
10 4 A 100
10 rows selected.
SQL>
In its basic form the
PIVOT operator is quite limited. We are forced to list the required values to PIVOT using the IN clause.
SELECT *
FROM (SELECT product_code, quantity
FROM pivot_test)
PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b, 'C' AS c));
A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
-------------- -------------- --------------
210 90 160
1 row selected.
SQL>
If we want to break it down by customer, we simply include the
CUSTOMER_ID column in the initial select list.
SELECT *
FROM (SELECT customer_id, product_code, quantity
FROM pivot_test)
PIVOT (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b, 'C' AS c))
ORDER BY customer_id;
CUSTOMER_ID A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
----------- -------------- -------------- --------------
1 10 20 30
2 40 50
3 60 70 80
4 100
4 rows selected.
SQL>
Prior to 11g we could accomplish a similar result using the
DECODE function combined with aggregate functions.
SELECT SUM(DECODE(product_code, 'A', quantity, 0)) AS a_sum_quantity,
SUM(DECODE(product_code, 'B', quantity, 0)) AS b_sum_quantity,
SUM(DECODE(product_code, 'C', quantity, 0)) AS c_sum_quantity
FROM pivot_test
ORDER BY customer_id;
A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
-------------- -------------- --------------
210 90 160
1 row selected.
SQL>
SELECT customer_id,
SUM(DECODE(product_code, 'A', quantity, 0)) AS a_sum_quantity,
SUM(DECODE(product_code, 'B', quantity, 0)) AS b_sum_quantity,
SUM(DECODE(product_code, 'C', quantity, 0)) AS c_sum_quantity
FROM pivot_test
GROUP BY customer_id
ORDER BY customer_id;
CUSTOMER_ID A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
----------- -------------- -------------- --------------
1 10 20 30
2 40 0 50
3 60 70 80
4 100 0 0
4 rows selected.
SQL>
Adding the
XML keyword to the PIVOT operator allows us to convert the generated pivot results to XML format. It also makes the PIVOT a little more flexible, allowing us to replace the hard coded IN clause with a subquery, or the ANY wildcard.
SET LONG 10000
SELECT *
FROM (SELECT product_code, quantity
FROM pivot_test)
PIVOT XML (SUM(quantity) AS sum_quantity FOR (product_code) IN (SELECT DISTINCT product_code
FROM pivot_test
WHERE id < 10));
product_code_XML
----------------------------------------------------------------------------------------------------
<PivotSet><item><column name = "PRODUCT_CODE">A</column><column name = "SUM_QUANTITY">210</column></
item><item><column name = "PRODUCT_CODE">B</column><column name = "SUM_QUANTITY">90</column></item><
item><column name = "PRODUCT_CODE">C</column><column name = "SUM_QUANTITY">160</column></item><item>
<column name = "PRODUCT_CODE">D</column><column name = "SUM_QUANTITY">90</column></item></PivotSet>
1 row selected.
SQL>
SELECT *
FROM (SELECT product_code, quantity
FROM pivot_test)
PIVOT XML (SUM(quantity) AS sum_quantity FOR (product_code) IN (ANY));
product_code_XML
----------------------------------------------------------------------------------------------------
<PivotSet><item><column name = "PRODUCT_CODE">A</column><column name = "SUM_QUANTITY">210</column></
item><item><column name = "PRODUCT_CODE">B</column><column name = "SUM_QUANTITY">90</column></item><
item><column name = "PRODUCT_CODE">C</column><column name = "SUM_QUANTITY">160</column></item><item>
<column name = "PRODUCT_CODE">D</column><column name = "SUM_QUANTITY">90</column></item></PivotSet>
1 row selected.
SQL>
Once again, the results can be broken down by customer, with each customers XML presented as a separate row.
SET LONG 10000
SELECT *
FROM (SELECT customer_id, product_code, quantity
FROM pivot_test)
PIVOT XML (SUM(quantity) AS sum_quantity FOR (product_code) IN (SELECT DISTINCT product_code
FROM pivot_test));
CUSTOMER_ID
-----------
PRODUCT_CODE_XML
----------------------------------------------------------------------------------------------------
1
<PivotSet><item><column name = "PRODUCT_CODE">A</column><column name = "SUM_QUANTITY">10</column></i
tem><item><column name = "PRODUCT_CODE">B</column><column name = "SUM_QUANTITY">20</column></item><i
tem><column name = "PRODUCT_CODE">C</column><column name = "SUM_QUANTITY">30</column></item><item><c
olumn name = "PRODUCT_CODE">D</column><column name = "SUM_QUANTITY"></column></item></PivotSet>
2
<PivotSet><item><column name = "PRODUCT_CODE">A</column><column name = "SUM_QUANTITY">40</column></i
tem><item><column name = "PRODUCT_CODE">B</column><column name = "SUM_QUANTITY"></column></item><ite
CUSTOMER_ID
-----------
PRODUCT_CODE_XML
----------------------------------------------------------------------------------------------------
m><column name = "PRODUCT_CODE">C</column><column name = "SUM_QUANTITY">50</column></item><item><col
umn name = "PRODUCT_CODE">D</column><column name = "SUM_QUANTITY"></column></item></PivotSet>
3
<PivotSet><item><column name = "PRODUCT_CODE">A</column><column name = "SUM_QUANTITY">60</column></i
tem><item><column name = "PRODUCT_CODE">B</column><column name = "SUM_QUANTITY">70</column></item><i
tem><column name = "PRODUCT_CODE">C</column><column name = "SUM_QUANTITY">80</column></item><item><c
olumn name = "PRODUCT_CODE">D</column><column name = "SUM_QUANTITY">90</column></item></PivotSet>
CUSTOMER_ID
-----------
PRODUCT_CODE_XML
----------------------------------------------------------------------------------------------------
4
<PivotSet><item><column name = "PRODUCT_CODE">A</column><column name = "SUM_QUANTITY">100</column></
item><item><column name = "PRODUCT_CODE">B</column><column name = "SUM_QUANTITY"></column></item><it
em><column name = "PRODUCT_CODE">C</column><column name = "SUM_QUANTITY"></column></item><item><colu
mn name = "PRODUCT_CODE">D</column><column name = "SUM_QUANTITY"></column></item></PivotSet>
4 rows selected.
SQL>
UNPIVOT
The
UNPIVOT operator converts column-based data into separate rows. To see the UNPIVOT operator in action we need to create a test table.CREATE TABLE unpivot_test ( id NUMBER, customer_id NUMBER, product_code_a NUMBER, product_code_b NUMBER, product_code_c NUMBER, product_code_d NUMBER ); INSERT INTO unpivot_test VALUES (1, 101, 10, 20, 30, NULL); INSERT INTO unpivot_test VALUES (2, 102, 40, NULL, 50, NULL); INSERT INTO unpivot_test VALUES (3, 103, 60, 70, 80, 90); INSERT INTO unpivot_test VALUES (4, 104, 100, NULL, NULL, NULL); COMMIT;
So our test data starts off looking like this.
SELECT * FROM unpivot_test;
ID CUSTOMER_ID PRODUCT_CODE_A PRODUCT_CODE_B PRODUCT_CODE_C PRODUCT_CODE_D
---------- ----------- -------------- -------------- -------------- --------------
1 101 10 20 30
2 102 40 50
3 103 60 70 80 90
4 104 100
4 rows selected.
SQL>
The
UNPIVOT operator converts this column-based data into individual rows.
SELECT *
FROM unpivot_test
UNPIVOT (quantity FOR product_code IN (product_code_a AS 'A', product_code_b AS 'B', product_code_c AS 'C', product_code_d AS 'D'));
ID CUSTOMER_ID P QUANTITY
---------- ----------- - ----------
1 101 A 10
1 101 B 20
1 101 C 30
2 102 A 40
2 102 C 50
3 103 A 60
3 103 B 70
3 103 C 80
3 103 D 90
4 104 A 100
10 rows selected.
SQL>
There are several things to note about the query:
- The required column names, in this case
QUANTITYandPRODUCT_CODE, are define in theUNPIVOTclause. These can be set to any name not currently in the driving table. - The columns to be unpivoted must be named in the
INclause. - The
PRODUCT_CODEvalue will match the column name it is derived from, unless you alias it to another value. - By default the
EXCLUDE NULLSclause is used. To override the default behaviour use theINCLUDE NULLSclause.
The following query shows the inclusion of the
INCLUDE NULLS clause.
SELECT *
FROM unpivot_test
UNPIVOT INCLUDE NULLS (quantity FOR product_code IN (product_code_a AS 'A', product_code_b AS 'B', product_code_c AS 'C', product_code_d AS 'D'));
ID CUSTOMER_ID P QUANTITY
---------- ----------- - ----------
1 101 A 10
1 101 B 20
1 101 C 30
1 101 D
2 102 A 40
2 102 B
2 102 C 50
2 102 D
3 103 A 60
3 103 B 70
3 103 C 80
ID CUSTOMER_ID P QUANTITY
---------- ----------- - ----------
3 103 D 90
4 104 A 100
4 104 B
4 104 C
4 104 D
16 rows selected.
SQL>
Prior to 11g, we can get the same result using the DECODE function and a pivot table with the correct number of rows. In the following example we use the CONNECT BY clause in a query from dual to generate the correct number of rows for the unpivot operation.
SELECT id,
customer_id,
DECODE(unpivot_row, 1, 'A',
2, 'B',
3, 'C',
4, 'D',
'N/A') AS product_code,
DECODE(unpivot_row, 1, product_code_a,
2, product_code_b,
3, product_code_c,
4, product_code_d,
'N/A') AS quantity
FROM unpivot_test,
(SELECT level AS unpivot_row FROM dual CONNECT BY level <= 4)
ORDER BY 1,2,3;
ID CUSTOMER_ID PRO QUANTITY
---------- ----------- --- ----------
1 101 A 10
1 101 B 20
1 101 C 30
1 101 D
2 102 A 40
2 102 B
2 102 C 50
2 102 D
3 103 A 60
3 103 B 70
3 103 C 80
ID CUSTOMER_ID PRO QUANTITY
---------- ----------- --- ----------
3 103 D 90
4 104 A 100
4 104 B
4 104 C
4 104 D
16 rows selected.
SQL>
2014年10月30日 星期四
[SYBASE]BackupServer不正常關閉，無法開啟backupserver
Sybase在進行backup的時候，
NFS硬碟網路斷線，導致Sybase卡住無法使用。
於是我先進入isql將backup server關閉，讓使用者可以正常使用，
>shutdown SYB_BACKUP
使用者可以正常使用了，但是接下來卻發生問題：
backup server沒有正常關閉。
導致backup server變成一種開不起來也關不掉的情形。
此時使用sybase使用者，進入Linux的Sybase環境，
root> su - sybase
sybase> showserver
檢查backupserver是否關閉，若是還沒關閉就直接關起來。
以這次的例子，backupserver沒有出現在showserver中，表示已關閉。
於是檢查是否有backup程式還在跑
sybase> su - root
root> ps -ef | grep back
檢查後發現有很多.lo的程式還在跑，
那些是backup用的檔案，把她們關閉。
root> kill -9 [pid]
接著就可以正常開啟backupserver了！
NFS硬碟網路斷線，導致Sybase卡住無法使用。
於是我先進入isql將backup server關閉，讓使用者可以正常使用，
>shutdown SYB_BACKUP
使用者可以正常使用了，但是接下來卻發生問題：
backup server沒有正常關閉。
導致backup server變成一種開不起來也關不掉的情形。
此時使用sybase使用者，進入Linux的Sybase環境，
root> su - sybase
sybase> showserver
檢查backupserver是否關閉，若是還沒關閉就直接關起來。
以這次的例子，backupserver沒有出現在showserver中，表示已關閉。
於是檢查是否有backup程式還在跑
sybase> su - root
root> ps -ef | grep back
檢查後發現有很多.lo的程式還在跑，
那些是backup用的檔案，把她們關閉。
root> kill -9 [pid]
接著就可以正常開啟backupserver了！
2014年9月11日 星期四
[轉貼文章]SQL優化過程中常見Oracle HINT的用法
來源：http://oracled2k.pixnet.net/blog/post/21722059-sql%E5%84%AA%E5%8C%96%E9%81%8E%E7%A8%8B%E4%B8%AD%E5%B8%B8%E8%A6%8Boracle-hint%E7%9A%84%E7%94%A8%E6%B3%95-
在SQL語句優化過程中，我們經常會用到hint,現總結一下在SQL優化過程中常見Oracle HINT的用法：
1. /*+ALL_ROWS*/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳吞吐量,使資源消耗最小化.
例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
2. /*+FIRST_ROWS*/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳響應時間,使資源消耗最小化.
例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
3. /*+CHOOSE*/
表明如果數據字典中有訪問表的統計信息,將基於開銷的優化方法,並獲得最佳的吞吐量;
表明如果數據字典中沒有訪問表的統計信息,將基於規則開銷的優化方法;
例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
4. /*+RULE*/
表明對語句塊選擇基於規則的優化方法.
例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
5. /*+FULL(TABLE)*/
表明對錶選擇全局掃描的方法.
例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';
6. /*+ROWID(TABLE)*/
提示明確表明對指定表根據ROWID進行訪問.
例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
AND EMP_NO='SCOTT';
7. /*+CLUSTER(TABLE)*/
提示明確表明對指定表選擇簇掃描的訪問方法,它只對簇對像有效.
例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
8. /*+INDEX(TABLE INDEX_NAME)*/
表明對錶選擇索引的掃描方法.
例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';
9. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明對錶選擇索引升序的掃描方法.
例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';
10. /*+INDEX_COMBINE*/
為指定表選擇位圖訪問路經,如果INDEX_COMBINE中沒有提供作為參數的索引,將選擇出位圖索引的布爾組合方式.
例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
WHERE SAL<5000000 emp_no="SCOTT" sex="M" dpt_no="V.DPT_NO">V.AVG_SAL;
20. /*+NO_MERGE(TABLE)*/
對於有可合併的視圖不再合併.
例如:
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;
21. /*+ORDERED*/
根據表出現在FROM中的順序,ORDERED使ORACLE依此順序對其連接.
例如:
SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;
22. /*+USE_NL(TABLE)*/
將指定表與嵌套的連接的行源進行連接,並把指定表作為內部表.
例如:
SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
23. /*+USE_MERGE(TABLE)*/
將指定的表與其他行源通過合併排序連接方式連接起來.
例如:
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
24. /*+USE_HASH(TABLE)*/
將指定的表與其他行源通過哈希連接方式連接起來.
例如:
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
25. /*+DRIVING_SITE(TABLE)*/
強制與ORACLE所選擇的位置不同的表進行查詢執行.
例如:
SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;
26. /*+LEADING(TABLE)*/
將指定的表作為連接次序中的首表.
27. /*+CACHE(TABLE)*/
當進行全表掃描時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
28. /*+NOCACHE(TABLE)*/
當進行全表掃描時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
29. /*+APPEND*/
直接插入到表的最後,可以提高速度.
insert /*+append*/ into test1 select * from test4 ;
30. /*+NOAPPEND*/
通過在插入語句生存期內停止並行模式來啟動常規插入.
insert /*+noappend*/ into test1 select * from test4 ;
在SQL語句優化過程中，我們經常會用到hint,現總結一下在SQL優化過程中常見Oracle HINT的用法：
1. /*+ALL_ROWS*/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳吞吐量,使資源消耗最小化.
例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
2. /*+FIRST_ROWS*/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳響應時間,使資源消耗最小化.
例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
3. /*+CHOOSE*/
表明如果數據字典中有訪問表的統計信息,將基於開銷的優化方法,並獲得最佳的吞吐量;
表明如果數據字典中沒有訪問表的統計信息,將基於規則開銷的優化方法;
例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
4. /*+RULE*/
表明對語句塊選擇基於規則的優化方法.
例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
5. /*+FULL(TABLE)*/
表明對錶選擇全局掃描的方法.
例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';
6. /*+ROWID(TABLE)*/
提示明確表明對指定表根據ROWID進行訪問.
例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
AND EMP_NO='SCOTT';
7. /*+CLUSTER(TABLE)*/
提示明確表明對指定表選擇簇掃描的訪問方法,它只對簇對像有效.
例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
8. /*+INDEX(TABLE INDEX_NAME)*/
表明對錶選擇索引的掃描方法.
例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';
9. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明對錶選擇索引升序的掃描方法.
例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';
10. /*+INDEX_COMBINE*/
為指定表選擇位圖訪問路經,如果INDEX_COMBINE中沒有提供作為參數的索引,將選擇出位圖索引的布爾組合方式.
例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
WHERE SAL<5000000 emp_no="SCOTT" sex="M" dpt_no="V.DPT_NO">V.AVG_SAL;
20. /*+NO_MERGE(TABLE)*/
對於有可合併的視圖不再合併.
例如:
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;
21. /*+ORDERED*/
根據表出現在FROM中的順序,ORDERED使ORACLE依此順序對其連接.
例如:
SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;
22. /*+USE_NL(TABLE)*/
將指定表與嵌套的連接的行源進行連接,並把指定表作為內部表.
例如:
SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
23. /*+USE_MERGE(TABLE)*/
將指定的表與其他行源通過合併排序連接方式連接起來.
例如:
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
24. /*+USE_HASH(TABLE)*/
將指定的表與其他行源通過哈希連接方式連接起來.
例如:
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
25. /*+DRIVING_SITE(TABLE)*/
強制與ORACLE所選擇的位置不同的表進行查詢執行.
例如:
SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;
26. /*+LEADING(TABLE)*/
將指定的表作為連接次序中的首表.
27. /*+CACHE(TABLE)*/
當進行全表掃描時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
28. /*+NOCACHE(TABLE)*/
當進行全表掃描時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
29. /*+APPEND*/
直接插入到表的最後,可以提高速度.
insert /*+append*/ into test1 select * from test4 ;
30. /*+NOAPPEND*/
通過在插入語句生存期內停止並行模式來啟動常規插入.
insert /*+noappend*/ into test1 select * from test4 ;
2014年8月25日 星期一
Find Invalid,Unusable Indexes.Rebuild Index. 找出失敗、無法使用的索引
此篇為翻譯文章
資料來源如下：
Check the Status of Indexes:
確認索引的狀態
SELECT status,count(*) from USER_INDEXES GROUP BY STATUS;
SELECT status,count(*) from ALL_INDEXES GROUP BY STATUS;
SELECT status,count(*) from DBA_INDEXES GROUP BY STATUS;
Solution
1. Drop the specified index and/or recreate the index
2. Rebuild the specified index
3. Rebuild the unusable index partition
解決方案
1.刪除特定索引，然後重建它
2.重建特定索引
3.重建不可使用的索引區塊
Rebuilding UNUSABLE indexes online, querying to USER_INDEXES,ALL_INDEXES,DBA_INDEXES view.
創造出重建語法
Sql> SELECT count(*),status FROM all_indexes GROUP BY status;
Sql> select 'alter index '||owner||'.'||index_name||' rebuild online ;' from dba_indexes
where status = 'UNUSABLE' ;
where status = 'UNUSABLE' ;
Output :
輸出如下：
'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDONLINE;'
--------------------------------------------------------------------------------
alter index APPLSYS.FND_COLUMNS_U4 rebuild online ;
alter index APPLSYS.FND_COLUMNS_U2 rebuild online ;
alter index APPLSYS.WF_PROCESS_ACTIVITIES_U2 rebuild online ;
alter index APPLSYS.WF_PROCESS_ACTIVITIES_PK rebuild online ;
alter index APPLSYS.WF_PROCESS_ACTIVITIES_N2 rebuild online ;
alter index APPLSYS.WF_PROCESS_ACTIVITIES_N1 rebuild online ;
alter index APPLSYS.FND_COLUMNS_U3 rebuild online ;
alter index APPLSYS.FND_COLUMNS_U1 rebuild online ;
2014年5月18日 星期日
EBS系統更新PATCH時發生錯誤解決方法
EBS系統更新PATCH時，跑了異常的久，於是我決定先停掉在重開看看狀況，
結果就關閉後重新進行adpatch就遇到以下問題：
The worker should not have status 'Running' or 'Restarted' at this point.
這個問題要進入adctrl來將目前的worker更改狀態。
這次手邊沒有記錄資料，大略說明一下。
oramgr>adctrl
進去後會問你一堆問題，全部回答完後會進入選單：
AD Controller Menu
---------------------------------------------------
1. Show worker status
2. Tell worker to restart a failed job
3. Tell worker to quit
4. Tell manager that a worker failed its job
5. Tell manager that a worker acknowledges quit
6. Restart a worker on the current machine
7. Exit
Enter your choice [1] :
首先輸入1，確認worker status。
確認若有狀態是RUNNING，
則輸入4，將worker status都改回Failed。
最後輸入2，
將worker的狀態設為Restart。
如此就可以在回到adpatch繼續將PATCH完成了。
adadmin關閉Maintenance Mode
[crp40mgr@ebstest 17023223_ZHT]$ adadmin
Copyright (c) 2002 Oracle Corporation
Redwood Shores, California, USA
Oracle Applications AD Administration
Version 12.0.0
NOTE: You may not use this utility for custom development
unless you have written permission from Oracle Corporation.
Your default directory is '/u01/crp40/apps/apps_st/appl'.
Is this the correct APPL_TOP [Yes] ?
AD Administration records your AD Administration session in a text file
you specify. Enter your AD Administration log file name or press [Return]
to accept the default file name shown in brackets.
Filename [adadmin.log] :
************* Start of AD Administration session *************
AD Administration version: 12.0.0
AD Administration started at: Wed May 14 2014 15:08:31
APPL_TOP is set to /u01/crp40/apps/apps_st/appl
You can be notified by email if a failure occurs.
Do you wish to activate this feature [No] ?
Please enter the batchsize [1000] :
Please enter the name of the Oracle Applications System that this
APPL_TOP belongs to.
The Applications System name must be unique across all Oracle
Applications Systems at your site, must be from 1 to 30 characters
long, may only contain alphanumeric and underscore characters,
and must start with a letter.
Sample Applications System names are: "prod", "test", "demo" and
"Development_2".
Applications System Name [crp40] : crp40 *
NOTE: If you do not currently have certain types of files installed
in this APPL_TOP, you may not be able to perform certain tasks.
Example 1: If you don't have files used for installing or upgrading
the database installed in this area, you cannot install or upgrade
the database from this APPL_TOP.
Example 2: If you don't have forms files installed in this area, you cannot
generate them or run them from this APPL_TOP.
Example 3: If you don't have concurrent program files installed in this area,
you cannot relink concurrent programs or generate reports from this APPL_TOP.
Do you currently have files used for installing or upgrading the database
installed in this APPL_TOP [YES] ? YES *
Do you currently have Java and HTML files for HTML-based functionality
installed in this APPL_TOP [YES] ? YES *
Do you currently have Oracle Applications forms files installed
in this APPL_TOP [YES] ? YES *
Do you currently have concurrent program files installed
in this APPL_TOP [YES] ? YES *
Please enter the name Oracle Applications will use to identify this APPL_TOP.
The APPL_TOP name you select must be unique within an Oracle Applications
System, must be from 1 to 30 characters long, may only contain
alphanumeric and underscore characters, and must start with a letter.
Sample APPL_TOP Names are: "prod_all", "demo3_forms2", and "forms1".
APPL_TOP Name [ebstest] : ebstest *
You are about to use or modify Oracle Applications product tables
in your ORACLE database 'crp40'
using ORACLE executables in '/u01/crp40/apps/tech_st/10.1.2'.
Is this the correct database [Yes] ?
AD Administration needs the password for your 'SYSTEM' ORACLE schema
in order to determine your installation configuration.
Enter the password for your 'SYSTEM' ORACLE schema:
The ORACLE username specified below for Application Object Library
uniquely identifies your existing product group: APPLSYS
Enter the ORACLE password of Application Object Library [APPS] :
AD Administration is verifying your username/password.
The status of various features in this run of AD Administration is:
<-Feature version in->
Feature Active? APPLTOP Data model Flags
------------------------------ ------- -------- ----------- -----------
CHECKFILE Yes 1 1 Y N N Y N Y
PREREQ Yes 6 6 Y N N Y N Y
CONCURRENT_SESSIONS No 2 2 Y Y N Y Y N
PATCH_TIMING Yes 2 2 Y N N Y N Y
PATCH_HIST_IN_DB Yes 6 6 Y N N Y N Y
SCHEMA_SWAP Yes 1 1 Y N N Y Y Y
JAVA_WORKER Yes 1 1 Y N N Y N Y
CODELEVEL Yes 1 1 Y N N Y N Y
Identifier for the current session is 39835
Reading product information from file...
Reading language and territory information from file...
Reading language information from applUS.txt ...
AD Administration warning:
Product Data File
/u01/crp40/apps/apps_st/appl/admin/zfaprod.txt
does not exist for product "zfa".
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
AD Administration warning:
Product Data File
/u01/crp40/apps/apps_st/appl/admin/zsaprod.txt
does not exist for product "zsa".
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
AD Administration warning:
Product Data File
/u01/crp40/apps/apps_st/appl/admin/jtsprod.txt
does not exist for product "jts".
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
Reading database to see what industry is currently installed.
Reading FND_LANGUAGES to see what is currently installed.
Currently, the following languages are installed:
Code Language Status
---- --------------------------------------- ---------
US American English Base
ZHT Traditional Chinese Install
Reading language information from applZHT.txt ...
Your base language will be AMERICAN.
Your other languages to install are: TRADITIONAL CHINESE
Setting up module information.
Reading database for information about the modules.
Saving module information.
Reading database for information about the products.
Reading database for information about how products depend on each other.
Reading topfile.txt ...
Saving product information.
AD code level : [B.3]
AD Administration Main Menu
--------------------------------------------------
1. Generate Applications Files menu
2. Maintain Applications Files menu
3. Compile/Reload Applications Database Entities menu
4. Maintain Applications Database Entities menu
5. Change Maintenance Mode
6. Exit AD Administration
Enter your choice [6] : 5
Change Maintenance Mode
----------------------------------------
Maintenance Mode is currently: [Enabled].
Maintenance mode should normally be enabled when patching
Oracle Applications and disabled when users are logged on
to the system. See the Oracle Applications Maintenance
Utilities manual for more information about maintenance mode.
Please select an option:
1. Enable Maintenance Mode
2. Disable Maintenance Mode
3. Return to Main Menu
Enter your choice [3] : 2
sqlplus -s &un_apps/***** @/u01/crp40/apps/apps_st/appl/ad/12.0.0/patch/115/sql/adsetmmd.sql DISABLE
Successfully disabled Maintenance Mode.
Review the messages above, then press [Return] to continue.
Backing up restart files, if any......Done.
Change Maintenance Mode
----------------------------------------
Maintenance Mode is currently: [Disabled].
Maintenance mode should normally be enabled when patching
Oracle Applications and disabled when users are logged on
to the system. See the Oracle Applications Maintenance
Utilities manual for more information about maintenance mode.
Please select an option:
1. Enable Maintenance Mode
2. Disable Maintenance Mode
3. Return to Main Menu
Enter your choice [3] : 3
AD Administration Main Menu
--------------------------------------------------
1. Generate Applications Files menu
2. Maintain Applications Files menu
3. Compile/Reload Applications Database Entities menu
4. Maintain Applications Database Entities menu
5. Change Maintenance Mode
6. Exit AD Administration
Enter your choice [6] : 6
Backing up restart files, if any......Done.
There is no timing information available for the current session.
AD Administration is complete.
Errors and warnings are listed in the log file
/u01/crp40/apps/apps_st/appl/admin/crp40/log/adadmin.log
and in other log files in the same directory.
[crp40mgr@ebstest 17023223_ZHT]$
Copyright (c) 2002 Oracle Corporation
Redwood Shores, California, USA
Oracle Applications AD Administration
Version 12.0.0
NOTE: You may not use this utility for custom development
unless you have written permission from Oracle Corporation.
Your default directory is '/u01/crp40/apps/apps_st/appl'.
Is this the correct APPL_TOP [Yes] ?
AD Administration records your AD Administration session in a text file
you specify. Enter your AD Administration log file name or press [Return]
to accept the default file name shown in brackets.
Filename [adadmin.log] :
************* Start of AD Administration session *************
AD Administration version: 12.0.0
AD Administration started at: Wed May 14 2014 15:08:31
APPL_TOP is set to /u01/crp40/apps/apps_st/appl
You can be notified by email if a failure occurs.
Do you wish to activate this feature [No] ?
Please enter the batchsize [1000] :
Please enter the name of the Oracle Applications System that this
APPL_TOP belongs to.
The Applications System name must be unique across all Oracle
Applications Systems at your site, must be from 1 to 30 characters
long, may only contain alphanumeric and underscore characters,
and must start with a letter.
Sample Applications System names are: "prod", "test", "demo" and
"Development_2".
Applications System Name [crp40] : crp40 *
NOTE: If you do not currently have certain types of files installed
in this APPL_TOP, you may not be able to perform certain tasks.
Example 1: If you don't have files used for installing or upgrading
the database installed in this area, you cannot install or upgrade
the database from this APPL_TOP.
Example 2: If you don't have forms files installed in this area, you cannot
generate them or run them from this APPL_TOP.
Example 3: If you don't have concurrent program files installed in this area,
you cannot relink concurrent programs or generate reports from this APPL_TOP.
Do you currently have files used for installing or upgrading the database
installed in this APPL_TOP [YES] ? YES *
Do you currently have Java and HTML files for HTML-based functionality
installed in this APPL_TOP [YES] ? YES *
Do you currently have Oracle Applications forms files installed
in this APPL_TOP [YES] ? YES *
Do you currently have concurrent program files installed
in this APPL_TOP [YES] ? YES *
Please enter the name Oracle Applications will use to identify this APPL_TOP.
The APPL_TOP name you select must be unique within an Oracle Applications
System, must be from 1 to 30 characters long, may only contain
alphanumeric and underscore characters, and must start with a letter.
Sample APPL_TOP Names are: "prod_all", "demo3_forms2", and "forms1".
APPL_TOP Name [ebstest] : ebstest *
You are about to use or modify Oracle Applications product tables
in your ORACLE database 'crp40'
using ORACLE executables in '/u01/crp40/apps/tech_st/10.1.2'.
Is this the correct database [Yes] ?
AD Administration needs the password for your 'SYSTEM' ORACLE schema
in order to determine your installation configuration.
Enter the password for your 'SYSTEM' ORACLE schema:
The ORACLE username specified below for Application Object Library
uniquely identifies your existing product group: APPLSYS
Enter the ORACLE password of Application Object Library [APPS] :
AD Administration is verifying your username/password.
The status of various features in this run of AD Administration is:
<-Feature version in->
Feature Active? APPLTOP Data model Flags
------------------------------ ------- -------- ----------- -----------
CHECKFILE Yes 1 1 Y N N Y N Y
PREREQ Yes 6 6 Y N N Y N Y
CONCURRENT_SESSIONS No 2 2 Y Y N Y Y N
PATCH_TIMING Yes 2 2 Y N N Y N Y
PATCH_HIST_IN_DB Yes 6 6 Y N N Y N Y
SCHEMA_SWAP Yes 1 1 Y N N Y Y Y
JAVA_WORKER Yes 1 1 Y N N Y N Y
CODELEVEL Yes 1 1 Y N N Y N Y
Identifier for the current session is 39835
Reading product information from file...
Reading language and territory information from file...
Reading language information from applUS.txt ...
AD Administration warning:
Product Data File
/u01/crp40/apps/apps_st/appl/admin/zfaprod.txt
does not exist for product "zfa".
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
AD Administration warning:
Product Data File
/u01/crp40/apps/apps_st/appl/admin/zsaprod.txt
does not exist for product "zsa".
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
AD Administration warning:
Product Data File
/u01/crp40/apps/apps_st/appl/admin/jtsprod.txt
does not exist for product "jts".
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
Reading database to see what industry is currently installed.
Reading FND_LANGUAGES to see what is currently installed.
Currently, the following languages are installed:
Code Language Status
---- --------------------------------------- ---------
US American English Base
ZHT Traditional Chinese Install
Reading language information from applZHT.txt ...
Your base language will be AMERICAN.
Your other languages to install are: TRADITIONAL CHINESE
Setting up module information.
Reading database for information about the modules.
Saving module information.
Reading database for information about the products.
Reading database for information about how products depend on each other.
Reading topfile.txt ...
Saving product information.
AD code level : [B.3]
AD Administration Main Menu
--------------------------------------------------
1. Generate Applications Files menu
2. Maintain Applications Files menu
3. Compile/Reload Applications Database Entities menu
4. Maintain Applications Database Entities menu
5. Change Maintenance Mode
6. Exit AD Administration
Enter your choice [6] : 5
Change Maintenance Mode
----------------------------------------
Maintenance Mode is currently: [Enabled].
Maintenance mode should normally be enabled when patching
Oracle Applications and disabled when users are logged on
to the system. See the Oracle Applications Maintenance
Utilities manual for more information about maintenance mode.
Please select an option:
1. Enable Maintenance Mode
2. Disable Maintenance Mode
3. Return to Main Menu
Enter your choice [3] : 2
sqlplus -s &un_apps/***** @/u01/crp40/apps/apps_st/appl/ad/12.0.0/patch/115/sql/adsetmmd.sql DISABLE
Successfully disabled Maintenance Mode.
Review the messages above, then press [Return] to continue.
Backing up restart files, if any......Done.
Change Maintenance Mode
----------------------------------------
Maintenance Mode is currently: [Disabled].
Maintenance mode should normally be enabled when patching
Oracle Applications and disabled when users are logged on
to the system. See the Oracle Applications Maintenance
Utilities manual for more information about maintenance mode.
Please select an option:
1. Enable Maintenance Mode
2. Disable Maintenance Mode
3. Return to Main Menu
Enter your choice [3] : 3
AD Administration Main Menu
--------------------------------------------------
1. Generate Applications Files menu
2. Maintain Applications Files menu
3. Compile/Reload Applications Database Entities menu
4. Maintain Applications Database Entities menu
5. Change Maintenance Mode
6. Exit AD Administration
Enter your choice [6] : 6
Backing up restart files, if any......Done.
There is no timing information available for the current session.
AD Administration is complete.
Errors and warnings are listed in the log file
/u01/crp40/apps/apps_st/appl/admin/crp40/log/adadmin.log
and in other log files in the same directory.
[crp40mgr@ebstest 17023223_ZHT]$
adpatch更新patch
[crp40mgr@ebstest 17023223_ZHT]$ adpatch
Copyright (c) 2002 Oracle Corporation
Redwood Shores, California, USA
Oracle Applications AutoPatch
Version 12.0.0
NOTE: You may not use this utility for custom development
unless you have written permission from Oracle Corporation.
Attention: AutoPatch no longer checks for unapplied pre-requisite patches.
You must use OAM Patch Wizard for this feature. Alternatively, you can
review the README for pre-requisite information.
Your default directory is '/u01/crp40/apps/apps_st/appl'.
Is this the correct APPL_TOP [Yes] ?
AutoPatch records your AutoPatch session in a text file
you specify. Enter your AutoPatch log file name or press [Return]
to accept the default file name shown in brackets.
Filename [adpatch.log] : patchTest.log
You can be notified by email if a failure occurs.
Do you wish to activate this feature [No] ?
Please enter the batchsize [1000] :
Please enter the name of the Oracle Applications System that this
APPL_TOP belongs to.
The Applications System name must be unique across all Oracle
Applications Systems at your site, must be from 1 to 30 characters
long, may only contain alphanumeric and underscore characters,
and must start with a letter.
Sample Applications System names are: "prod", "test", "demo" and
"Development_2".
Applications System Name [crp40] : crp40 *
NOTE: If you do not currently have certain types of files installed
in this APPL_TOP, you may not be able to perform certain tasks.
Example 1: If you don't have files used for installing or upgrading
the database installed in this area, you cannot install or upgrade
the database from this APPL_TOP.
Example 2: If you don't have forms files installed in this area, you cannot
generate them or run them from this APPL_TOP.
Example 3: If you don't have concurrent program files installed in this area,
you cannot relink concurrent programs or generate reports from this APPL_TOP.
Do you currently have files used for installing or upgrading the database
installed in this APPL_TOP [YES] ? YES *
Do you currently have Java and HTML files for HTML-based functionality
installed in this APPL_TOP [YES] ? YES *
Do you currently have Oracle Applications forms files installed
in this APPL_TOP [YES] ? YES *
Do you currently have concurrent program files installed
in this APPL_TOP [YES] ? YES *
Please enter the name Oracle Applications will use to identify this APPL_TOP.
The APPL_TOP name you select must be unique within an Oracle Applications
System, must be from 1 to 30 characters long, may only contain
alphanumeric and underscore characters, and must start with a letter.
Sample APPL_TOP Names are: "prod_all", "demo3_forms2", and "forms1".
APPL_TOP Name [ebstest] : ebstest *
You are about to apply a patch to the installation of Oracle Applications
in your ORACLE database 'crp40'
using ORACLE executables in '/u01/crp40/apps/tech_st/10.1.2'.
Is this the correct database [Yes] ?
AutoPatch needs the password for your 'SYSTEM' ORACLE schema
in order to determine your installation configuration.
Enter the password for your 'SYSTEM' ORACLE schema:
The ORACLE username specified below for Application Object Library
uniquely identifies your existing product group: APPLSYS
Enter the ORACLE password of Application Object Library [APPS] :
AutoPatch is verifying your username/password.
The status of various features in this run of AutoPatch is:
<-Feature version in->
Feature Active? APPLTOP Data model Flags
------------------------------ ------- -------- ----------- -----------
CHECKFILE Yes 1 1 Y N N Y N Y
PREREQ Yes 6 6 Y N N Y N Y
CONCURRENT_SESSIONS No 2 2 Y Y N Y Y N
PATCH_TIMING Yes 2 2 Y N N Y N Y
PATCH_HIST_IN_DB Yes 6 6 Y N N Y N Y
SCHEMA_SWAP Yes 1 1 Y N N Y Y Y
JAVA_WORKER Yes 1 1 Y N N Y N Y
CODELEVEL Yes 1 1 Y N N Y N Y
Identifier for the current session is 39834
Reading product information from file...
Reading language and territory information from file...
Reading language information from applUS.txt ...
AutoPatch warning:
Product Data File
/u01/crp40/apps/apps_st/appl/admin/zfaprod.txt
does not exist for product "zfa".
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
AutoPatch warning:
Product Data File
/u01/crp40/apps/apps_st/appl/admin/zsaprod.txt
does not exist for product "zsa".
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
AutoPatch warning:
Product Data File
/u01/crp40/apps/apps_st/appl/admin/jtsprod.txt
does not exist for product "jts".
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
Reading database to see what industry is currently installed.
Reading FND_LANGUAGES to see what is currently installed.
Currently, the following languages are installed:
Code Language Status
---- --------------------------------------- ---------
US American English Base
ZHT Traditional Chinese Install
Reading language information from applZHT.txt ...
Your base language will be AMERICAN.
Your other languages to install are: TRADITIONAL CHINESE
Setting up module information.
Reading database for information about the modules.
Saving module information.
Reading database for information about the products.
Reading database for information about how products depend on each other.
Reading topfile.txt ...
Saving product information.
AD code level : [B.3]
Trying to obtain a lock...
Attempting to instantiate the current-view snapshot...
Was already instantiated. So no instantiation done this time.
**************** S T A R T O F U P L O A D ****************
Start date: Wed May 14 2014 15:05:43
0 "left over" javaupdates.txt files uploaded to DB: Wed May 14 2014 15:05:43
0 patches uploaded from the ADPSV format patch history files: Wed May 14 2014 15:05:43
Uploading information about files copied during the previous runs ...
0 "left over" filescopied_<session_id>.txt files uploaded to DB: Wed May 14 2014 15:05:43
****************** E N D O F U P L O A D ******************
End date: Wed May 14 2014 15:05:43
Enter the directory where your Oracle Applications patch has been unloaded
The default directory is [/u01/crp40/patches/17023223_ZHT] :
Please enter the name of your AutoPatch driver file : u17023223.drv
Copyright (c) 2002 Oracle Corporation
Redwood Shores, California, USA
Oracle Applications AutoPatch
Version 12.0.0
NOTE: You may not use this utility for custom development
unless you have written permission from Oracle Corporation.
Attention: AutoPatch no longer checks for unapplied pre-requisite patches.
You must use OAM Patch Wizard for this feature. Alternatively, you can
review the README for pre-requisite information.
Your default directory is '/u01/crp40/apps/apps_st/appl'.
Is this the correct APPL_TOP [Yes] ?
AutoPatch records your AutoPatch session in a text file
you specify. Enter your AutoPatch log file name or press [Return]
to accept the default file name shown in brackets.
Filename [adpatch.log] : patchTest.log
You can be notified by email if a failure occurs.
Do you wish to activate this feature [No] ?
Please enter the batchsize [1000] :
Please enter the name of the Oracle Applications System that this
APPL_TOP belongs to.
The Applications System name must be unique across all Oracle
Applications Systems at your site, must be from 1 to 30 characters
long, may only contain alphanumeric and underscore characters,
and must start with a letter.
Sample Applications System names are: "prod", "test", "demo" and
"Development_2".
Applications System Name [crp40] : crp40 *
NOTE: If you do not currently have certain types of files installed
in this APPL_TOP, you may not be able to perform certain tasks.
Example 1: If you don't have files used for installing or upgrading
the database installed in this area, you cannot install or upgrade
the database from this APPL_TOP.
Example 2: If you don't have forms files installed in this area, you cannot
generate them or run them from this APPL_TOP.
Example 3: If you don't have concurrent program files installed in this area,
you cannot relink concurrent programs or generate reports from this APPL_TOP.
Do you currently have files used for installing or upgrading the database
installed in this APPL_TOP [YES] ? YES *
Do you currently have Java and HTML files for HTML-based functionality
installed in this APPL_TOP [YES] ? YES *
Do you currently have Oracle Applications forms files installed
in this APPL_TOP [YES] ? YES *
Do you currently have concurrent program files installed
in this APPL_TOP [YES] ? YES *
Please enter the name Oracle Applications will use to identify this APPL_TOP.
The APPL_TOP name you select must be unique within an Oracle Applications
System, must be from 1 to 30 characters long, may only contain
alphanumeric and underscore characters, and must start with a letter.
Sample APPL_TOP Names are: "prod_all", "demo3_forms2", and "forms1".
APPL_TOP Name [ebstest] : ebstest *
You are about to apply a patch to the installation of Oracle Applications
in your ORACLE database 'crp40'
using ORACLE executables in '/u01/crp40/apps/tech_st/10.1.2'.
Is this the correct database [Yes] ?
AutoPatch needs the password for your 'SYSTEM' ORACLE schema
in order to determine your installation configuration.
Enter the password for your 'SYSTEM' ORACLE schema:
The ORACLE username specified below for Application Object Library
uniquely identifies your existing product group: APPLSYS
Enter the ORACLE password of Application Object Library [APPS] :
AutoPatch is verifying your username/password.
The status of various features in this run of AutoPatch is:
<-Feature version in->
Feature Active? APPLTOP Data model Flags
------------------------------ ------- -------- ----------- -----------
CHECKFILE Yes 1 1 Y N N Y N Y
PREREQ Yes 6 6 Y N N Y N Y
CONCURRENT_SESSIONS No 2 2 Y Y N Y Y N
PATCH_TIMING Yes 2 2 Y N N Y N Y
PATCH_HIST_IN_DB Yes 6 6 Y N N Y N Y
SCHEMA_SWAP Yes 1 1 Y N N Y Y Y
JAVA_WORKER Yes 1 1 Y N N Y N Y
CODELEVEL Yes 1 1 Y N N Y N Y
Identifier for the current session is 39834
Reading product information from file...
Reading language and territory information from file...
Reading language information from applUS.txt ...
AutoPatch warning:
Product Data File
/u01/crp40/apps/apps_st/appl/admin/zfaprod.txt
does not exist for product "zfa".
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
AutoPatch warning:
Product Data File
/u01/crp40/apps/apps_st/appl/admin/zsaprod.txt
does not exist for product "zsa".
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
AutoPatch warning:
Product Data File
/u01/crp40/apps/apps_st/appl/admin/jtsprod.txt
does not exist for product "jts".
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
Reading database to see what industry is currently installed.
Reading FND_LANGUAGES to see what is currently installed.
Currently, the following languages are installed:
Code Language Status
---- --------------------------------------- ---------
US American English Base
ZHT Traditional Chinese Install
Reading language information from applZHT.txt ...
Your base language will be AMERICAN.
Your other languages to install are: TRADITIONAL CHINESE
Setting up module information.
Reading database for information about the modules.
Saving module information.
Reading database for information about the products.
Reading database for information about how products depend on each other.
Reading topfile.txt ...
Saving product information.
AD code level : [B.3]
Trying to obtain a lock...
Attempting to instantiate the current-view snapshot...
Was already instantiated. So no instantiation done this time.
**************** S T A R T O F U P L O A D ****************
Start date: Wed May 14 2014 15:05:43
0 "left over" javaupdates.txt files uploaded to DB: Wed May 14 2014 15:05:43
0 patches uploaded from the ADPSV format patch history files: Wed May 14 2014 15:05:43
Uploading information about files copied during the previous runs ...
0 "left over" filescopied_<session_id>.txt files uploaded to DB: Wed May 14 2014 15:05:43
****************** E N D O F U P L O A D ******************
End date: Wed May 14 2014 15:05:43
Enter the directory where your Oracle Applications patch has been unloaded
The default directory is [/u01/crp40/patches/17023223_ZHT] :
Please enter the name of your AutoPatch driver file : u17023223.drv
adadmin開啟Maintenance Mode
[crp40mgr@ebstest patches]$ adadmin
Copyright (c) 2002 Oracle Corporation
Redwood Shores, California, USA
Oracle Applications AD Administration
Version 12.0.0
NOTE: You may not use this utility for custom development
unless you have written permission from Oracle Corporation.
Your default directory is '/u01/crp40/apps/apps_st/appl'.
Is this the correct APPL_TOP [Yes] ?
AD Administration records your AD Administration session in a text file
you specify. Enter your AD Administration log file name or press [Return]
to accept the default file name shown in brackets.
Filename [adadmin.log] :
************* Start of AD Administration session *************
AD Administration version: 12.0.0
AD Administration started at: Wed May 14 2014 14:21:42
APPL_TOP is set to /u01/crp40/apps/apps_st/appl
You can be notified by email if a failure occurs.
Do you wish to activate this feature [No] ?
Please enter the batchsize [1000] :
Please enter the name of the Oracle Applications System that this
APPL_TOP belongs to.
The Applications System name must be unique across all Oracle
Applications Systems at your site, must be from 1 to 30 characters
long, may only contain alphanumeric and underscore characters,
and must start with a letter.
Sample Applications System names are: "prod", "test", "demo" and
"Development_2".
Applications System Name [crp40] : crp40 *
NOTE: If you do not currently have certain types of files installed
in this APPL_TOP, you may not be able to perform certain tasks.
Example 1: If you don't have files used for installing or upgrading
the database installed in this area, you cannot install or upgrade
the database from this APPL_TOP.
Example 2: If you don't have forms files installed in this area, you cannot
generate them or run them from this APPL_TOP.
Example 3: If you don't have concurrent program files installed in this area,
you cannot relink concurrent programs or generate reports from this APPL_TOP.
Do you currently have files used for installing or upgrading the database
installed in this APPL_TOP [YES] ? YES *
Do you currently have Java and HTML files for HTML-based functionality
installed in this APPL_TOP [YES] ? YES *
Do you currently have Oracle Applications forms files installed
in this APPL_TOP [YES] ? YES *
Do you currently have concurrent program files installed
in this APPL_TOP [YES] ? YES *
Please enter the name Oracle Applications will use to identify this APPL_TOP.
The APPL_TOP name you select must be unique within an Oracle Applications
System, must be from 1 to 30 characters long, may only contain
alphanumeric and underscore characters, and must start with a letter.
Sample APPL_TOP Names are: "prod_all", "demo3_forms2", and "forms1".
APPL_TOP Name [ebstest] : ebstest *
You are about to use or modify Oracle Applications product tables
in your ORACLE database 'crp40'
using ORACLE executables in '/u01/crp40/apps/tech_st/10.1.2'.
Is this the correct database [Yes] ?
AD Administration needs the password for your 'SYSTEM' ORACLE schema
in order to determine your installation configuration.
Enter the password for your 'SYSTEM' ORACLE schema:
The ORACLE username specified below for Application Object Library
uniquely identifies your existing product group: APPLSYS
Enter the ORACLE password of Application Object Library [APPS] :
AD Administration is verifying your username/password.
The status of various features in this run of AD Administration is:
<-Feature version in->
Feature Active? APPLTOP Data model Flags
------------------------------ ------- -------- ----------- -----------
CHECKFILE Yes 1 1 Y N N Y N Y
PREREQ Yes 6 6 Y N N Y N Y
CONCURRENT_SESSIONS No 2 2 Y Y N Y Y N
PATCH_TIMING Yes 2 2 Y N N Y N Y
PATCH_HIST_IN_DB Yes 6 6 Y N N Y N Y
SCHEMA_SWAP Yes 1 1 Y N N Y Y Y
JAVA_WORKER Yes 1 1 Y N N Y N Y
CODELEVEL Yes 1 1 Y N N Y N Y
Identifier for the current session is 39831
Reading product information from file...
Reading language and territory information from file...
Reading language information from applUS.txt ...
AD Administration warning:
Product Data File
/u01/crp40/apps/apps_st/appl/admin/zfaprod.txt
does not exist for product "zfa".
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
AD Administration warning:
Product Data File
/u01/crp40/apps/apps_st/appl/admin/zsaprod.txt
does not exist for product "zsa".
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
AD Administration warning:
Product Data File
/u01/crp40/apps/apps_st/appl/admin/jtsprod.txt
does not exist for product "jts".
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
Reading database to see what industry is currently installed.
Reading FND_LANGUAGES to see what is currently installed.
Currently, the following languages are installed:
Code Language Status
---- --------------------------------------- ---------
US American English Base
ZHT Traditional Chinese Install
Reading language information from applZHT.txt ...
Your base language will be AMERICAN.
Your other languages to install are: TRADITIONAL CHINESE
Setting up module information.
Reading database for information about the modules.
Saving module information.
Reading database for information about the products.
Reading database for information about how products depend on each other.
Reading topfile.txt ...
Saving product information.
AD code level : [B.3]
AD Administration Main Menu
--------------------------------------------------
1. Generate Applications Files menu
2. Maintain Applications Files menu
3. Compile/Reload Applications Database Entities menu
4. Maintain Applications Database Entities menu
5. Change Maintenance Mode
6. Exit AD Administration
Enter your choice [6] : 5
Change Maintenance Mode
----------------------------------------
Maintenance Mode is currently: [Disabled].
Maintenance mode should normally be enabled when patching
Oracle Applications and disabled when users are logged on
to the system. See the Oracle Applications Maintenance
Utilities manual for more information about maintenance mode.
Please select an option:
1. Enable Maintenance Mode
2. Disable Maintenance Mode
3. Return to Main Menu
Enter your choice [3] : 1
sqlplus -s &un_apps/***** @/u01/crp40/apps/apps_st/appl/ad/12.0.0/patch/115/sql/adsetmmd.sql ENABLE
Successfully enabled Maintenance Mode.
Review the messages above, then press [Return] to continue.
Backing up restart files, if any......Done.
Change Maintenance Mode
----------------------------------------
Maintenance Mode is currently: [Enabled].
Maintenance mode should normally be enabled when patching
Oracle Applications and disabled when users are logged on
to the system. See the Oracle Applications Maintenance
Utilities manual for more information about maintenance mode.
Please select an option:
1. Enable Maintenance Mode
2. Disable Maintenance Mode
3. Return to Main Menu
Enter your choice [3] :
AD Administration Main Menu
--------------------------------------------------
1. Generate Applications Files menu
2. Maintain Applications Files menu
3. Compile/Reload Applications Database Entities menu
4. Maintain Applications Database Entities menu
5. Change Maintenance Mode
6. Exit AD Administration
Enter your choice [6] :
Backing up restart files, if any......Done.
There is no timing information available for the current session.
AD Administration is complete.
Errors and warnings are listed in the log file
/u01/crp40/apps/apps_st/appl/admin/crp40/log/adadmin.log
and in other log files in the same directory.
[crp40mgr@ebstest patches]$
Copyright (c) 2002 Oracle Corporation
Redwood Shores, California, USA
Oracle Applications AD Administration
Version 12.0.0
NOTE: You may not use this utility for custom development
unless you have written permission from Oracle Corporation.
Your default directory is '/u01/crp40/apps/apps_st/appl'.
Is this the correct APPL_TOP [Yes] ?
AD Administration records your AD Administration session in a text file
you specify. Enter your AD Administration log file name or press [Return]
to accept the default file name shown in brackets.
Filename [adadmin.log] :
************* Start of AD Administration session *************
AD Administration version: 12.0.0
AD Administration started at: Wed May 14 2014 14:21:42
APPL_TOP is set to /u01/crp40/apps/apps_st/appl
You can be notified by email if a failure occurs.
Do you wish to activate this feature [No] ?
Please enter the batchsize [1000] :
Please enter the name of the Oracle Applications System that this
APPL_TOP belongs to.
The Applications System name must be unique across all Oracle
Applications Systems at your site, must be from 1 to 30 characters
long, may only contain alphanumeric and underscore characters,
and must start with a letter.
Sample Applications System names are: "prod", "test", "demo" and
"Development_2".
Applications System Name [crp40] : crp40 *
NOTE: If you do not currently have certain types of files installed
in this APPL_TOP, you may not be able to perform certain tasks.
Example 1: If you don't have files used for installing or upgrading
the database installed in this area, you cannot install or upgrade
the database from this APPL_TOP.
Example 2: If you don't have forms files installed in this area, you cannot
generate them or run them from this APPL_TOP.
Example 3: If you don't have concurrent program files installed in this area,
you cannot relink concurrent programs or generate reports from this APPL_TOP.
Do you currently have files used for installing or upgrading the database
installed in this APPL_TOP [YES] ? YES *
Do you currently have Java and HTML files for HTML-based functionality
installed in this APPL_TOP [YES] ? YES *
Do you currently have Oracle Applications forms files installed
in this APPL_TOP [YES] ? YES *
Do you currently have concurrent program files installed
in this APPL_TOP [YES] ? YES *
Please enter the name Oracle Applications will use to identify this APPL_TOP.
The APPL_TOP name you select must be unique within an Oracle Applications
System, must be from 1 to 30 characters long, may only contain
alphanumeric and underscore characters, and must start with a letter.
Sample APPL_TOP Names are: "prod_all", "demo3_forms2", and "forms1".
APPL_TOP Name [ebstest] : ebstest *
You are about to use or modify Oracle Applications product tables
in your ORACLE database 'crp40'
using ORACLE executables in '/u01/crp40/apps/tech_st/10.1.2'.
Is this the correct database [Yes] ?
AD Administration needs the password for your 'SYSTEM' ORACLE schema
in order to determine your installation configuration.
Enter the password for your 'SYSTEM' ORACLE schema:
The ORACLE username specified below for Application Object Library
uniquely identifies your existing product group: APPLSYS
Enter the ORACLE password of Application Object Library [APPS] :
AD Administration is verifying your username/password.
The status of various features in this run of AD Administration is:
<-Feature version in->
Feature Active? APPLTOP Data model Flags
------------------------------ ------- -------- ----------- -----------
CHECKFILE Yes 1 1 Y N N Y N Y
PREREQ Yes 6 6 Y N N Y N Y
CONCURRENT_SESSIONS No 2 2 Y Y N Y Y N
PATCH_TIMING Yes 2 2 Y N N Y N Y
PATCH_HIST_IN_DB Yes 6 6 Y N N Y N Y
SCHEMA_SWAP Yes 1 1 Y N N Y Y Y
JAVA_WORKER Yes 1 1 Y N N Y N Y
CODELEVEL Yes 1 1 Y N N Y N Y
Identifier for the current session is 39831
Reading product information from file...
Reading language and territory information from file...
Reading language information from applUS.txt ...
AD Administration warning:
Product Data File
/u01/crp40/apps/apps_st/appl/admin/zfaprod.txt
does not exist for product "zfa".
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
AD Administration warning:
Product Data File
/u01/crp40/apps/apps_st/appl/admin/zsaprod.txt
does not exist for product "zsa".
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
AD Administration warning:
Product Data File
/u01/crp40/apps/apps_st/appl/admin/jtsprod.txt
does not exist for product "jts".
This product is registered in the database but the
above file does not exist in APPL_TOP. The product
will be ignored without error.
Reading database to see what industry is currently installed.
Reading FND_LANGUAGES to see what is currently installed.
Currently, the following languages are installed:
Code Language Status
---- --------------------------------------- ---------
US American English Base
ZHT Traditional Chinese Install
Reading language information from applZHT.txt ...
Your base language will be AMERICAN.
Your other languages to install are: TRADITIONAL CHINESE
Setting up module information.
Reading database for information about the modules.
Saving module information.
Reading database for information about the products.
Reading database for information about how products depend on each other.
Reading topfile.txt ...
Saving product information.
AD code level : [B.3]
AD Administration Main Menu
--------------------------------------------------
1. Generate Applications Files menu
2. Maintain Applications Files menu
3. Compile/Reload Applications Database Entities menu
4. Maintain Applications Database Entities menu
5. Change Maintenance Mode
6. Exit AD Administration
Enter your choice [6] : 5
Change Maintenance Mode
----------------------------------------
Maintenance Mode is currently: [Disabled].
Maintenance mode should normally be enabled when patching
Oracle Applications and disabled when users are logged on
to the system. See the Oracle Applications Maintenance
Utilities manual for more information about maintenance mode.
Please select an option:
1. Enable Maintenance Mode
2. Disable Maintenance Mode
3. Return to Main Menu
Enter your choice [3] : 1
sqlplus -s &un_apps/***** @/u01/crp40/apps/apps_st/appl/ad/12.0.0/patch/115/sql/adsetmmd.sql ENABLE
Successfully enabled Maintenance Mode.
Review the messages above, then press [Return] to continue.
Backing up restart files, if any......Done.
Change Maintenance Mode
----------------------------------------
Maintenance Mode is currently: [Enabled].
Maintenance mode should normally be enabled when patching
Oracle Applications and disabled when users are logged on
to the system. See the Oracle Applications Maintenance
Utilities manual for more information about maintenance mode.
Please select an option:
1. Enable Maintenance Mode
2. Disable Maintenance Mode
3. Return to Main Menu
Enter your choice [3] :
AD Administration Main Menu
--------------------------------------------------
1. Generate Applications Files menu
2. Maintain Applications Files menu
3. Compile/Reload Applications Database Entities menu
4. Maintain Applications Database Entities menu
5. Change Maintenance Mode
6. Exit AD Administration
Enter your choice [6] :
Backing up restart files, if any......Done.
There is no timing information available for the current session.
AD Administration is complete.
Errors and warnings are listed in the log file
/u01/crp40/apps/apps_st/appl/admin/crp40/log/adadmin.log
and in other log files in the same directory.
[crp40mgr@ebstest patches]$
訂閱：
文章 (Atom)