2017年12月 4日 (月)

”utl_file I/O” - この症状はあれの可能性が高いですね。

JPOUG Advent Calendar 2017の4日目のエントリーです。

さて、最近あまりお目にかかってなかったUTL_FILEパッケージで表データをcsvに書きだすネタにしました。

先日、UTL_FILEパッケージを利用した処理が想定以上に遅いという相談をうけました。
AWRレポートをみると、上位の待機イベントは、”utl_file I/O"。

!!!!おーーーこれは、珍しいというか、久々にみた病気w

UTL_FILEパッケージを利用したI/Oをグルグルしているとか、でかいファイル読み書きしているかの、どちらかですよねw
ということで、この症状の治療法を考えてみます。


<参考 - 環境>

MacBook:˜ system_profiler SPHardwareDataType | grep -E 'Model|Processor|Cores|Memory'
Model Name: MacBook
Model Identifier: MacBook9,1
Processor Name: Intel Core m5
Processor Speed: 1.2 GHz
Number of Processors: 1
Total Number of Cores: 2
Memory: 8 GB

ホストOS
MacBook:˜ discus$ sw_vers
ProductName: Mac OS X
ProductVersion: 10.13.1
BuildVersion: 17B1003

MacBook:˜ discus$ VBoxManage -version
5.1.30r118389


ゲストOSとOracle Database
orcl@SYS> !uname -a
Linux localhost.localdomain 4.1.12-94.3.6.el7uek.x86_64 #2 SMP Tue May 30 19:25:15 PDT 2017 x86_64 x86_64 x86_64 GNU/Linux

orcl@SYS> !cat /etc/oracle-release
Oracle Linux Server release 7.3

orcl@SYS>
orcl@SYS> select * from v$version;

BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0

UTL_FILEでファイル出力する際、ちょっとした手順の漏れが性能差として現れてしまうことがあります。
表の行長は数百バイト程度ですが、行数は数千万〜数億行なんていう状況だと、性能差が顕著に現れてしまうので注意が必要です。

以下の表、セグメントサイズは1GB程度ですが、行数は1千万行以上あります。
この表データをUTL_FILEパッケージを利用してcsvに出力してみます。

ORCL@SCOTT> select segment_name,sum(bytes)/1024/1024/1024 "GB" from user_segments group by segment_name;

SEGMENT_NAME GB
------------------------------ ----------
ABOUT_100BYTES_ROWS .9140625
PK_ABOUT_100BYTES_ROWS .171875


ORCL@SCOTT> select count(1) from about_100bytes_rows;

COUNT(1)
----------
10737420

表の1行は100bytes(改行コード含)です。

ORCL@SCOTT%gt; r
1 SELECT
2 LENGTH(
3 TO_CHAR(id,'FM000000000000000000000000000009')
4 ||','||FOO
5 ) row_length
6 FROM
7 about_100bytes_rows
8 WHERE
9* rownum <= 1

ROW_LENGTH
----------
99

次の2つのコードの赤太文字部分に着目してください。
その部分が異なるだけで処理時間に大きな差が出ます。

DECLARE
cDIR_NAME CONSTANT VARCHAR2(30) := 'FILES_DIR';
cFILE_NAME CONSTANT VARCHAR2(128) := 'no_writebuffering_'||TO_CHAR(systimestamp, 'rrmmddhh24miss.ff')||'.txt';
cBufferSize CONSTANT BINARY_INTEGER := 32767;
cOpenMode CONSTANT VARCHAR2(2) := 'w';
fileHandle UTL_FILE.FILE_TYPE;

cBulkReadLimit CONSTANT PLS_INTEGER := 324;
TYPE tBulkReadArray IS TABLE OF VARCHAR2(8192) INDEX BY BINARY_INTEGER;
bulkReadArray tBulkReadArray;
CURSOR cur_about100bytesRow IS
SELECT
TO_CHAR(id,'FM000000000000000000000000000009')
||','||FOO
AS csvrow
FROM
about_100bytes_rows
;
BEGIN
OPEN cur_about100bytesRow;
fileHandle := UTL_FILE.FOPEN(cDIR_NAME, cFILE_NAME, cOpenMode, cBufferSize);
LOOP
FETCH cur_about100bytesRow
BULK COLLECT INTO bulkReadArray
LIMIT cBulkReadLimit;

EXIT WHEN bulkReadArray.COUNT = 0;

FOR i IN bulkReadArray.FIRST..bulkReadArray.LAST LOOP
UTL_FILE.PUT_LINE(
file => fileHandle
, buffer => bulkReadArray(i)
, autoflush => true
);
END LOOP;
END LOOP;
UTL_FILE.FFLUSH(fileHandle);
UTL_FILE.FCLOSE(fileHandle);
CLOSE cur_about100bytesRow;
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(fileHandle) THEN
UTL_FILE.FCLOSE(fileHandle);
END IF;

IF cur_about100bytesRow%ISOPEN THEN
CLOSE cur_about100bytesRow;
END IF;
RAISE;
END;
/


PL/SQLプロシージャが正常に完了しました。

経過: 00:02:27.05


DECLARE
cDIR_NAME CONSTANT VARCHAR2(30) := 'FILES_DIR';
cFILE_NAME CONSTANT VARCHAR2(128) := 'writebuffering_'||TO_CHAR(systimestamp, 'rrmmddhh24miss.ff')||'.txt';
cBufferSize CONSTANT BINARY_INTEGER := 32767;
cOpenMode CONSTANT VARCHAR2(2) := 'w';
fileHandle UTL_FILE.FILE_TYPE;
buffer VARCHAR2(32767);

cBulkReadLimit CONSTANT PLS_INTEGER := 324;
TYPE tBulkReadArray IS TABLE OF VARCHAR2(8192) INDEX BY BINARY_INTEGER;
bulkReadArray tBulkReadArray;
CURSOR cur_about100bytesRow IS
SELECT
TO_CHAR(id,'FM000000000000000000000000000009')
||','||FOO
AS csvrow
FROM
about_100bytes_rows
;
BEGIN
OPEN cur_about100bytesRow;
fileHandle := UTL_FILE.FOPEN(cDIR_NAME, cFILE_NAME, cOpenMode, cBufferSize);
LOOP
FETCH cur_about100bytesRow
BULK COLLECT INTO bulkReadArray
LIMIT cBulkReadLimit;

EXIT WHEN bulkReadArray.COUNT = 0;

buffer := NULL;
FOR i IN bulkReadArray.FIRST..bulkReadArray.LAST LOOP
buffer := buffer || bulkReadArray(i) || UTL_TCP.CRLF;
END LOOP;
UTL_FILE.PUT(fileHandle, buffer);
UTL_FILE.FFLUSH(fileHandle);
END LOOP;
UTL_FILE.FFLUSH(fileHandle);
UTL_FILE.FCLOSE(fileHandle);
CLOSE cur_about100bytesRow;
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(fileHandle) THEN
UTL_FILE.FCLOSE(fileHandle);
END IF;

IF cur_about100bytesRow%ISOPEN THEN
CLOSE cur_about100bytesRow;
END IF;
RAISE;
END;
/


PL/SQLプロシージャが正常に完了しました。

経過: 00:00:43.96


何が違うかお分ですよね!
UTL_FILE.PUT_LINE/UTL_FILE.PUTが違う!!w
その部分は重要ではなく、100Bytes単位に書き込んでいるか、約32KB単位で書き込んでいるかが重要なんです。

UTL_FILEは最大32767バイトのバッファを利用できますが、前者はバッファを有効利用せず、100Bytes毎に書き出しています。後者は約32KB単位で書き出しています。

その差はAWRレポートからも見えてきます。


AWRレポート(一部抜粋)
UTL_FILEパッケージによるI/Oの待機は、”utl_file I/O”という待機イベントで現れます。

Avg Waitは短いですが、理由は100バイト単位の小さいなサイズの書き込みを繰り返しているわけなので、そんなとこでしょう。

Top 10 Foreground Events by Total Wait Time

Total Wait Avg % DB Wait
Event Waits Time (sec) Wait time Class
------------------------------ ----------- ---------- --------- ------ --------
DB CPU 147.4 99.3
utl_file I/O 32,212,266 49.5 1.54us 33.3 User I/O
direct path read 1,881 1.2 620.61us .8 User I/O
resmgr:cpu quantum 1 .1 85.85ms .1 Schedule
db file sequential read 17 0 .98ms .0 User I/O
PGA memory operation 90 0 35.13us .0 Other
latch: shared pool 1 0 1.05ms .0 Concurre
control file sequential read 80 0 12.83us .0 System I
Disk file operations I/O 6 0 73.67us .0 User I/O
SQL*Net message to client 2 0 9.00us .0 Network


一方、約32KB単位でバッファリングして書き出している場合、Waits回数が激減しています。
Avg Waitsは大きくなっていますが、書き出しサイズにほぼ比例しているので想定通りというところ。

Top 10 Foreground Events by Total Wait Time

Total Wait Avg % DB Wait
Event Waits Time (sec) Wait time Class
------------------------------ ----------- ---------- --------- ------ --------
DB CPU 45 98.5
utl_file I/O 66,288 27.4 413.54us 60.1 User I/O
direct path read 1,881 1 550.71us 2.3 User I/O
db file sequential read 88 0 443.38us .1 User I/O
direct path write 2 0 4.69ms .0 User I/O
control file sequential read 80 0 33.25us .0 System I
latch: shared pool 1 0 2.38ms .0 Concurre
Disk file operations I/O 4 0 323.50us .0 User I/O
PGA memory operation 79 0 15.73us .0 Other
SQL*Net message to client 4 0 20.25us .0 Network

utl_file.put/put_lineでcsvを出力しているdeviceのiostat(util%)
まだまだ余裕があるのでI/Oで詰まっているのではなく、UTL_FILE.PUT/PUT_LINEの使い方の影響が大きいということですね。
20171203_202342


最後に、
UTL_FILEパッケージの入出力時にはバッファの有効利用をお忘れなく。(つい忘れちゃうこともあるので、急いでるときとかw)
扱うデータが多い場合は得に。

そして、みなさま、
メリークリスマス(まだエントリーを書くかもしれませんがw)
#JPOUG

| | コメント (0) | トラックバック (0)

2017年11月 7日 (火)

DBMS_ADVISOR.CREATE_FILE()プロシージャは、Diagnostic/Tuning Pack不要らしいということの確認

Previously on Mac De Oracle



余談

DBMS_ADVISOR.CREATE_FILEとなっているがライセンス上、diagnostic/tuning packライセンスはなくてもアクセスしてできるように読み取れるのだけと、もしかすると、この影響で、DBMS_ADVISOR.CREATE_FILEの存在は知っていても使ってないのかあるのかな??

以下のマニュアルを読む限り、Diagnostic/Tuning Packでは、DBMS_ADVISORパッケージ全体ではなく、Diagnostic/Tuning Packに関連するパラメータを与える必要のある機能についての制限であることしか記載されていない。
つまり、DBMS_ADVISOR.CREATE_FILE()に関して言えば、Diagnostic/Tuning Pack特有の機能ではないから利用可能なはず。 ←間違ってたらツッコミ希望w

Oracle® Databaseライセンス情報 12cリリース1 (12.1) 2 オプションおよびパック
https://docs.oracle.com/cd/E49329_01/license.121/b71334/options.htm



なんてことを書いたところ、

control_management_pack_access=none

でも使えるとなら間違いなくないんじゃないの?
というコメントがあったので試してみました。 マニュアルの通りだと思われます。ということですね。FAQ!

orcl@SYS> show parameter control_management_pack_access

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string NONE
orcl@SYS>
orcl@SYS> l
1 DECLARE
2 reportClob CLOB := EMPTY_CLOB();
3 BEGIN
4 reportClob := 'test test test';
5 DBMS_ADVISOR.CREATE_FILE(
6 buffer => reportClob
7 ,location => 'REP_DIR'
8 ,filename => 'test.txt'
9 );
10* END;
orcl@SYS> /

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:00.23
orcl@SYS> !ls report
test.txt

orcl@SYS> !cat report/test.txt
test test test


DBMS_SQLTUNE.PACK_STGTAB_SQLSETって、例外投げんのかよw
SQL Tuning Setのキャプチャから退避までのスクリプト(やっつけ)
SQL Tuning Set (STS)のフィルタリング
DBMS_SQLTUNE.REPORT_ANALYSIS_TASK()ファンクションで生成されるCLOB型のレポートをファイルに保存する簡単な方法

| | コメント (0) | トラックバック (0)

2017年11月 3日 (金)

DBMS_SQLTUNE.REPORT_ANALYSIS_TASK()ファンクションで生成されるCLOB型のレポートをファイルに保存する簡単な方法

Previously on Mac De Oracle
STSとの格闘というかSPAとの格闘に疲れてきたところw ですが、
SQL Tuning Set (STS)のフィルタリング
などで、SQLSETを程よい大きさに分割するところまでたどり着きましたw

今回は、いくつかある細かい分析方法については、一旦、置いといて、
分析レポートを保存する簡単な方法についての備忘録

分析などにこの記事も参考になると思いますが、紹介している記事でも分析レポートをファイルに保存する方法はSQL*Plusのコマンドを駆使して行われています。
個人的には少々やぼったい方法かなと感じている方法なんですが、以前はこんなやりかたが多かったようにも思います。

var rep CLOB
...略...
EXEC :rep := DBMS_SQLSPA.REPORT_ANALYSIS_TASK('STSNAM','TEXT',...
spo hoge_report.txt
print :rep
spo off

SQL*PlusとPL/SQLが行ったり来たりするところや、ループ制御しにくいので、この方法は避けたい。。。。

かといって、DBMS_OUTPUT.PUT_LINEで行おうとするとset linesize等の制御が面倒くさいし
結局、SQL*Plusの機能にも依存してしまうので、いまひとつ。

PL/SQLだけで行う方法として、UTL_FILEパッケージを利用してファイル出力する方法もありますが、
UTL_FILEパッケージで提供されているAPIはlow levelなものばかりなので、急ぎの時にはめんどくさくて、やだw

もっと簡単なやつないの?????

あります!w

DBMS_ADVISOR.CREATE_FILE(buffer, location, filename)

なぜ、UTL_FILEパッケージに入れてくれないんだw と思いたくなる方もいるかと思いますが、 理由はよくわかりませんw
(UTL_FILEパッケージはlow levelなAPIだけだから入れたくないのかも、かも、かも。と思ってますが、DBMS_ADVISORパッケージに入れるとは。どういうつもりだ!w)

UTL_FILE.CREATE_FILE()ってあったほうが直感的に探しやすくないかw UTL_FILEみて無い!!
となると、他を探すこともあまりなくなって、結局、コード書いてる場面を多く見てる>< 


余談
DBMS_ADVISOR.CREATE_FILEとなっているがライセンス上、diagnostic/tuning packライセンスはなくてもアクセスしてできるように読み取れるのだけと、もしかすると、この影響で、DBMS_ADVISOR.CREATE_FILEの存在は知っていても使ってないのかあるのかな??

以下のマニュアルを読む限り、Diagnostic/Tuning Packでは、DBMS_ADVISORパッケージ全体ではなく、Diagnostic/Tuning Packに関連するパラメータを与える必要のある機能についての制限であることしか記載されていない。
つまり、DBMS_ADVISOR.CREATE_FILE()に関して言えば、Diagnostic/Tuning Pack特有の機能ではないから利用可能なはず。 ←間違ってたらツッコミ希望w

Oracle® Databaseライセンス情報 12cリリース1 (12.1) 2 オプションおよびパック
https://docs.oracle.com/cd/E49329_01/license.121/b71334/options.htm


とりあえず、SQLSETの分析レポートをDBMS_ADVISOR.CREATE_FILE()を利用して保存するやっつけスクリプトを作ってみました
これを元に育てていけるかなw


実行例)

注)STS20171102というSQLSETを事前に作成してあります。


orcl@SYS> select * from v$version;

BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0


orcl@SYS> select owner,name,statement_count,created from dba_sqlset order by name;

OWNER NAME STATEMENT_COUNT CREATED
---------- ------------------------------ --------------- ---------
SPAUSER STS20171102 1003 02-NOV-17


spa.sqlのパラメータは、SQLSETと分析レポートの出力ディレクトリオブジェクトとSQLを実行するスキーマ向けのDBリンクです。

スクリプトの概要
・初回の分析(SQLSETで元のSQLを分析)
・2回目の分析(新環境でSQLを実行して分析)
・初回と2回目の比較レポート作成/保存
・レポートを出力後、分析に利用したタスクを削除



orcl@SYS> @spa STS20171102 rep_dir link_4_target

task name:STS20171102 has been droped.
***END***

PL/SQL procedure successfully completed.

分析レポートは、ディレクトリオブジェクトに対応づけられたパスに保存されます

orcl@SYS> !ls -l report
-rw-r--r-- 1 oracle oinstall 13688 11月 3 02:44 DIFF_STS20171102171103024433.txt

orcl@SYS> !cat report/DIFF_STS20171102171103024433.txt

General Information
---------------------------------------------------------------------------------------------

Task Information: Workload Information:
--------------------------------------------- ---------------------------------------------
Task Name : STS20171102 SQL Tuning Set Name : STS20171102
Task Owner : SYS SQL Tuning Set Owner : SPAUSER
Description : Total SQL Statement Count : 1003

Execution Information:
---------------------------------------------------------------------------------------------
Execution Name : DIFF_STS20171102 Started : 11/03/2017 02:44:26
Execution Type : COMPARE PERFORMANCE Last Updated : 11/03/2017 02:44:29
Description : Global Time Limit : UNLIMITED
Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED
Status : COMPLETED Number of Errors : 0
Number of Unsupported SQL : 4

Analysis Information:
---------------------------------------------------------------------------------------------
Before Change Execution: After Change Execution:
--------------------------------------------- ---------------------------------------------
Execution Name : SOURCE_STS20171102 Execution Name : TARGET_STS20171102
Execution Type : CONVERT SQLSET Execution Type : TEST EXECUTE REMOTE
Scope : COMPREHENSIVE Database Link : LINK_4_TARGET
Status : COMPLETED Scope : COMPREHENSIVE
Started : 11/03/2017 02:44:04 Status : COMPLETED
Last Updated : 11/03/2017 02:44:04 Started : 11/03/2017 02:44:04
Global Time Limit : UNLIMITED Last Updated : 11/03/2017 02:44:18
Per-SQL Time Limit : UNUSED Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED
Number of Errors : 0

---------------------------------------------
Comparison Metric: ELAPSED_TIME
------------------
Workload Impact Threshold: 1%
--------------------------
SQL Impact Threshold: 1%
----------------------

Report Summary
---------------------------------------------------------------------------------------------

Projected Workload Change Impact:
-------------------------------------------
Overall Impact : 0%
Improvement Impact : 0%
Regression Impact : 0%

SQL Statement Count
-------------------------------------------
SQL Category SQL Count Plan Change Count
Overall 999 490
Unchanged 995 490
Unsupported 4 0

Top 100 SQL Sorted by Absolute Value of Change Impact on the Workload
---------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
| | | Impact on | Execution | Metric | Metric | Impact | Plan |
| object_id | sql_id | Workload | Frequency | Before | After | on SQL | Change |
-----------------------------------------------------------------------------------------
| 1477 | 7hys3h7ysgf9m | .1% | 1 | 14630 | 33 | 99.77% | n |
| 1811 | cw6vxf0kbz3v1 | .03% | 1 | 3602 | 24 | 99.33% | n |
| 1175 | 2s9mmb6g8kbqb | .01% | 1 | 1537 | 20 | 98.7% | n |

...中略...

| 1691 | az25yp5qunj77 | .01% | 1 | 882 | 20 | 97.73% | n |
| 1896 | faun6bjrjqr17 | .01% | 1 | 881 | 19 | 97.84% | n |
| 1956 | g7a2tmw1nrxbh | .01% | 1 | 881 | 19 | 97.84% | n |
-----------------------------------------------------------------------------------------
Note: time statistics are displayed in microseconds
---------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------

スクリプトは以下のとおり、無名PL/SQLブロックにしてあります。
レポートのタイプなどは固定にしてますが、それらも含めてパラメータにしてもいいかも。

ちなみに、このエントリの本題である、DBMS_ADVISOR.CREATE_FILE()は赤字部分です!

orcl@SYS> !cat spa.sql

set serveroutput on
DECLARE
report_clob CLOB;
cSQLSET_OWNER VARCHAR2(30) := 'SPAUSER';
cSQLSET_NAME VARCHAR2(20) := UPPER('&1');
cEXECUTION_NAME_PREFIX_SOURCE VARCHAR2(7) := 'SOURCE_';
cEXECUTION_NAME_PREFIX_TARGET VARCHAR2(7) := 'TARGET_';
cEXECUTION_NAME_PREFIX_DIFF VARCHAR2(5) := 'DIFF_';
cREPORT_TYPE CONSTANT VARCHAR2(4) := 'text';
cFILE_EXTENTION CONSTANT VARCHAR2(5) := '.txt';
cDIRECTORY_NAME CONSTANT VARCHAR2(30) := UPPER('&2');
cDB_LINK_4_PARSING_SCHEMA CONSTANT VARCHAR2(128) := UPPER('&3');
stmt_task VARCHAR2(64);

PROCEDURE drop_task (iTASK_NAME VARCHAR2)
IS
BEGIN
FOR task_rec IN (
SELECT
task_name
FROM
dba_advisor_tasks
WHERE
task_name = iTASK_NAME
AND advisor_name = 'SQL Performance Analyzer'
) LOOP
DBMS_OUTPUT.PUT_LINE(
'task name:'||task_rec.task_name||' has been droped.'
);
DBMS_SQLPA.DROP_ANALYSIS_TASK(
task_name => task_rec.task_name
);
END LOOP;
END drop_task;
BEGIN
--Create analysis task
stmt_task := DBMS_SQLPA.CREATE_ANALYSIS_TASK(
sqlset_name => cSQLSET_NAME
,basic_filter => 'parsing_schema_name in (''SCOTT'')'
,sqlset_owner => cSQLSET_OWNER
,task_name => cSQLSET_NAME
);

--SQL Analysis on Source DB
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => cSQLSET_NAME
,execution_type => 'CONVERT SQLSET'
,execution_name =>
cEXECUTION_NAME_PREFIX_SOURCE
|| cSQLSET_NAME
,execution_params =>
DBMS_ADVISOR.ARGLIST(
'sqlset_name', cSQLSET_NAME
,'sqlset_owner', cSQLSET_OWNER
)
);

--SQL Analysis on Target DB
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => cSQLSET_NAME
,execution_type => 'EXECUTE'
,execution_name =>
cEXECUTION_NAME_PREFIX_TARGET
|| cSQLSET_NAME
,execution_params =>
DBMS_ADVISOR.ARGLIST(
'DATABASE_LINK', cDB_LINK_4_PARSING_SCHEMA
,'EXECUTE_FULLDML', 'TRUE'
)
);

--Comparison Source and Target Analysys
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => cSQLSET_NAME
,execution_type => 'COMPARE'
,execution_name =>
cEXECUTION_NAME_PREFIX_DIFF
|| cSQLSET_NAME
,execution_params =>
DBMS_ADVISOR.ARGLIST(
'PLAN_LINES_COMPARISON','ALWAYS'
)
);

--Make comparison report
report_clob := DBMS_SQLPA.REPORT_ANALYSIS_TASK(
task_name => cSQLSET_NAME
,execution_name =>
cEXECUTION_NAME_PREFIX_DIFF
|| cSQLSET_NAME
,type => cREPORT_TYPE
,level => 'typical'
,section => 'summary'
);

--Save comparison report
DBMS_ADVISOR.CREATE_FILE(
buffer => report_clob
,location => cDIRECTORY_NAME
,filename =>
cEXECUTION_NAME_PREFIX_DIFF
||cSQLSET_NAME
||TO_CHAR(systimestamp, 'RRMMDDHH24MISS')
||cFILE_EXTENTION
);


--Drop Task
drop_task(cSQLSET_NAME);
DBMS_OUTPUT.PUT_LINE('***END***');
EXCEPTION
WHEN OTHERS THEN
drop_task(cSQLSET_NAME);
RAISE;
END;
/


DBMS_SQLTUNE.PACK_STGTAB_SQLSETって、例外投げんのかよw
SQL Tuning Setのキャプチャから退避までのスクリプト(やっつけ)
SQL Tuning Set (STS)のフィルタリング

| | コメント (0) | トラックバック (0)

2017年10月30日 (月)

SQL Tuning Set (STS)のフィルタリング

Previously on Mac De Oracle
前回は、やっつけすくりぷとを書いたところまででした。

ということで、今回のやっつけスクリプトの準備というかアイデア

SQLSET大量のSQL文キャプチャされていて、SQLSETにキャプチャされている大量のSQL文を一括でSPAでまわしたりすることが辛い場合があります。
そんなときはどうすればよいか。。なやみますよね。。。ほんと。。
SQL Performance AnalyzerによるSQLパフォーマンス比較実行例(API)

SQLSETから、より細かなSQLSETにほぼ当分に分割し、新たなSQLSETを作成したいような場合もあるかもしれません。
そんな時はどうするか?

手取りばやくやるなら、ORA_HASH()が便利ですよね。(SQLSETの全表走査はさけられないのですが、Exaならw)

たとえば、以下のSQLSETがあったとして、300件程度に均等に分割したいという場合。
ora_hash()のバケット数はだいたい12ぐらいでできそうですね。

orcl@SYS> select name,statement_count,created from dba_sqlset order by name;

NAME STATEMENT_COUNT CREATED
------------------------------ --------------- ---------
STS20171029 3819 29-OCT-17

orcl@SYS> select statement_count/300 from dba_sqlset where name='STS20171029';

STATEMENT_COUNT/300
-------------------
12.73

いい感じに分割できそうです。

orcl@SYS> r
1 select
2 ora_hash(sql_id,12) as hash#
3 ,count(sql_id)
4 from
5 table(dbms_sqltune.select_sqlset(
6 sqlset_name=>'STS20171029'
7 ,basic_filter=>null
8 ))
9 group by
10 ora_hash(sql_id,12)
11* order by 1

HASH# COUNT(SQL_ID)
---------- -------------
0 289
1 293
2 278
3 296
4 297
5 281
6 282
7 298
8 288
9 304
10 305
11 298
12 310

13 rows selected.


実際に利用するには、basic_filterでwhere句の条件文そのものを書いてあげます。サブクエリも書けますが、データ量が多い場合は性能面に注意しながらいろいろ試してみるといいと思います。
basic_filterでは、SQLSET_ROWオブジェクト・タイプの属性を利用したフィルタリングができるのですが、インジェクションですよね。構文を見ている限り;)
Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス 12c リリース1 (12.1) SQLSET_ROWオブジェクト・タイプ

orcl@SYS> set serveroutput on
orcl@SYS> r
1 begin
2 for i in 0..12 loop
3 for sqlset_rec in
4 (
5 select count(sql_id) as num_sql
6 from
7 table(dbms_sqltune.select_sqlset(
8 sqlset_name=>'STS20171029'
9 ,basic_filter=>'ora_hash(sql_id,12)='||i
10 ))
11 )
12 loop
13 dbms_output.put_line('hash#='||to_char(i,'fm99')||':'||sqlset_rec.num_sql);
14 end loop;
15 end loop;
16* end;
hash#=0:289
hash#=1:293
hash#=2:278
hash#=3:296
hash#=4:297
hash#=5:281
hash#=6:282
hash#=7:298
hash#=8:288
hash#=9:304
hash#=10:305
hash#=11:298
hash#=12:310

PL/SQL procedure successfully completed.

ハッシュ値10でsql_id数をカウント!
うまくできそうですよね。

orcl@SYS> r
1 select
2 count(sql_id)
3 from
4 table(dbms_sqltune.select_sqlset(
5 sqlset_name=>'STS20171029'
6 ,basic_filter=>'ora_hash(sql_id,12) = 10'
7* ))

COUNT(SQL_ID)
-------------
305

参考
Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス 12c リリース1 (12.1) DBMS_SQLTUNE
Oracle® Database SQL言語リファレンス 12cリリース1 (12.1) ORA_HASH


DBMS_SQLTUNE.PACK_STGTAB_SQLSETって、例外投げんのかよw
SQL Tuning Setのキャプチャから退避までのスクリプト(やっつけ)

| | コメント (0) | トラックバック (0)

2017年10月 7日 (土)

SQL Tuning Setのキャプチャから退避までのスクリプト(やっつけ)

Previously on Mac De Oracleは
DBMS_SQLTUNE.PACK_STGTAB_SQLSETって、例外投げんのかよwという備忘録でした、

今回は
以下のURLで紹介されているSTS (SQL Tuning Set)へSQLの性能統計や実行計画をキャプチャしちゃおう!
Oracle DatabaseのSTS(SQL Tuning Set) を活用して、SQLの性能統計や実行計画をキャプチャする。 / ora_gonsuke777

というSTS機能を利用した応用編w (という名のやっつけスクリプト) を書いたので、備忘録

STSでSQLの実行計画や性能統計をキャプチャするのはいいのですが、キャプチャするデータが多い場合、SYSAUX表領域を圧迫したり、拡張したりしてしまうことがあります。
本番環境で表領域サイズにドキドキする日々を送るのも嫌なので、一定期間STSヘキャプチャしたあとSTSを退避、削除したいよね。という方向の話が湧いてきたりしますw

で、書いたやっつけスクリプトが以下。(11g2と12cR1でテスト済み)
STSをキャプチャする時間、インターバル、そして、STSを退避するためのステージング表を作成するスキーマ名と、エクスポートに必要なディレクトリオブジェクト名を
パラメータに取ります。キャプチャする時間とインターバルは秒を指定します。エクスポートは、DBMS_DATAPUMPパッケージを利用しています。
今回利用したパッケージの詳細は以下を参照のこと:)

Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス 11g リリース2(11.2) B56262-06
- DBMS_SQLTUNEサブプログラムの要約
- 46 DBMS_DATAPUMP

なお、本スクリプト実施前に、DataPump Export向けディレクトリオブジェクトを、ステージング表を作成するユーザを作成しておく必要があります。
(後半の実行例は、STS_EXP_DIRディレクトリオブジェクト、STSUSRユーザを事前に作成)
sts_capture.sql

SET SERVEROUTPUT ON
/*
Arguments :
&1 - Schema name for sts staging table
&2 - Directory object name for Data Pump Export
&3 - Duration for SQLSET capturing (sec)
&4 - Sampling interval for SQLSET (sec)
*/
DECLARE
-- for STS capturing and packing
vStsName VARCHAR2(30) := 'STS' || TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS');
cTimeLimit CONSTANT POSITIVE := &3;
cInterval CONSTANT POSITIVE := &4;
cCaptureOpt CONSTANT VARCHAR2(20) := 'MERGE';
cStagingSchema CONSTANT VARCHAR2(30) := UPPER('&1');
v4Debug VARCHAR2(50);

-- for DataPump Export Job
cDirectory CONSTANT VARCHAR2(20) := UPPER('&2');
IsSkipExport BOOLEAN := false;
i NUMBER;
vDataPumpJobHandle NUMBER;
vProgress_ratio NUMBER;
vJobState VARCHAR2(30);
oLogEntry ku$_LogEntry;
oStatus ku$_Status;
BEGIN
DBMS_OUTPUT.ENABLE;

-- STS名にInstance#を付加
-- (RAC環境での複数ノードでの実行を考慮してインスタンス番号を付加)
v4Debug := 'Build STSNAME';
FOR instance_rec IN (SELECT TO_CHAR(instance_number) AS inum FROM v$instance) LOOP
vStsName := vStsName || instance_rec.inum;
END LOOP;

-- SQLSETの作成
v4Debug := 'CREATE_SQLSET';
DBMS_SQLTUNE.CREATE_SQLSET (
sqlset_name => vStsName
,sqlset_owner => NULL
);

-- カーソルキャッシュからSTSへ定期キャプチャ
v4Debug := 'CAPTURE_CURSOR_CACHE_SQLSET';
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET (
sqlset_name => vStsName
,time_limit => cTimeLimit
,repeat_interval => cInterval
,capture_option => cCaptureOpt
,capture_mode => DBMS_SQLTUNE.MODE_REPLACE_OLD_STATS
,basic_filter =>
'parsing_schema_name NOT IN (
''SYS'', ''SYSTEM'', ''APEX_050000'', ''APEX_040000'', ''SYSMAN''
)'
,sqlset_owner => NULL
);

-- STSエクスポート向けステージング表の作成
v4Debug := 'CREATE_STGTAB_SQLSET';
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (
table_name => vStsName
,schema_name => cStagingSchema
,db_version => NULL
);

-- STSをステージング表へパック
v4Debug := 'PACK_STGTAB_SQLSET';
BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET (
sqlset_name => vStsName
,sqlset_owner => NULL
,staging_table_name => vStsName
,staging_schema_owner => cStagingSchema
,db_version => NULL
);
EXCEPTION
WHEN OTHERS THEN
IF sqlcode = -15701 THEN
IsSkipExport := true;
DBMS_OUTPUT.PUT_LINE('*** Info - No data packed from SQLSET ***');
ELSE
RAISE;
END IF;
END;

IF IsSkipExport = false
THEN
-- ステージング表を表モードでエクスポート
-- エクスポートのモード等の設定
v4Debug := 'OPEN';
vDataPumpJobHandle
:= DBMS_DATAPUMP.OPEN (
operation => 'EXPORT'
,job_mode => 'TABLE'
,remote_link => NULL
,job_name => vStsName
,version => 'LATEST'
);

-- エスポートダンプファイルとログファイルの設定
v4Debug := 'ADD_FILE - dumpfile';
DBMS_DATAPUMP.ADD_FILE (
handle => vDataPumpJobHandle
,filename => vStsName || '.dmp'
,directory => cDirectory
,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
);

v4Debug := 'ADD_FILE - logfile';
DBMS_DATAPUMP.ADD_FILE (
handle => vDataPumpJobHandle
,filename => vStsName || '.log'
,directory => cDirectory
,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
);

-- メターデータフィルタの設定
-- エクスポート対象表のスキーマ
v4Debug := 'METADATA_FILTER - schema name';
DBMS_DATAPUMP.METADATA_FILTER (
handle => vDataPumpJobHandle
,name => 'SCHEMA_LIST'
,value => '''' || cStagingSchema || ''''
);

-- エクスポート対象表
v4Debug := 'METADATA_FILTER - table name';
DBMS_DATAPUMP.METADATA_FILTER (
handle => vDataPumpJobHandle
,name => 'NAME_LIST'
,value => '''' || vStsName || ''''
);

-- DataPump Exportジョブの実行
v4Debug := 'START_JOB';
DBMS_DATAPUMP.START_JOB (
handle => vDataPumpJobHandle
);

-- DataPump Exportジョブ状況監視と終了判定
-- ジョブ終了または停止されるまでループして待機
v4Debug := 'JOB_STATE';
vProgress_ratio := 0;
vJobState := 'UNDEFINED';
WHILE (vJobState != 'COMPLETED') AND (vJobState != 'STOPPED') LOOP
DBMS_DATAPUMP.GET_STATUS (
vDataPumpJobHandle
,DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR
+ DBMS_DATAPUMP.KU$_STATUS_JOB_STATUS
+ DBMS_DATAPUMP.KU$_STATUS_WIP
,-1
,vJobState
,oStatus
);

-- 処理中(Work-In-Progress : WIP)または、
-- エラーのいずれかのメッセージを受け取ったら表示
IF (BITAND(oStatus.mask, DBMS_DATAPUMP.KU$_STATUS_WIP) != 0)
THEN
oLogEntry := oStatus.wip;
ELSE
IF (BITAND(oStatus.mask, DBMS_DATAPUMP.KU$_STATUS_JOB_ERROR) != 0)
THEN
oLogEntry := oStatus.error;
ELSE
oLogEntry := NULL;
END IF;
END IF;
IF oLogEntry IS NOT NULL
THEN
i := oLogEntry.FIRST;
WHILE i IS NOT NULL LOOP
DBMS_OUTPUT.PUT_LINE(oLogEntry(i).LogText);
i := oLogEntry.NEXT(i);
END LOOP;
END IF;
END LOOP;

-- Data Pump Exportジョブ終了
DBMS_DATAPUMP.DETACH(vDataPumpJobHandle);
END IF;

-- ステージング表の削除
v4Debug := 'Drop staging table';
EXECUTE IMMEDIATE 'DROP TABLE ' || cStagingSchema || '.' || vStsName || ' PURGE';

-- SQLSETの削除
v4Debug := 'DROP_SQLSET';
DBMS_SQLTUNE.DROP_SQLSET (
sqlset_name => vStsName
);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm());
DBMS_OUTPUT.PUT_LINE(v4Debug);
RAISE;
END;
/
EXIT


STSが空ではない場合の実行例
キャプチャ処理は600秒実行しキャプチャ間隔は120秒、キャプチャ終了後、STSUSRスキーマにステージング表を作成、STSをステージング表へパック、ステージング表を指定したディレクトリオブジェクト以下にDataPump Exportしています。

[oracle@guppy ˜]$ sqlplus system@orcl @sts_capture stsusr sts_exp_dir 10*60 2*60

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 6 15:13:53 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

old 4: cTimeLimit CONSTANT POSITIVE := &3;
new 4: cTimeLimit CONSTANT POSITIVE := 10*60;
old 5: cInterval CONSTANT POSITIVE := &4;
new 5: cInterval CONSTANT POSITIVE := 2*60;
old 7: cStagingSchema CONSTANT VARCHAR2(30) := UPPER('&1');
new 7: cStagingSchema CONSTANT VARCHAR2(30) := UPPER('stsusr');
old 11: cDirectory CONSTANT VARCHAR2(20) := UPPER('&2');
new 11: cDirectory CONSTANT VARCHAR2(20) := UPPER('sts_exp_dir');
Starting "SYS"."STS201710061513571":
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.062 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "STSUSR"."STS201710061513571" 51.55 KB 1 rows
Master table "SYS"."STS201710061513571" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.STS201710061513571 is:
/home/oracle/exp/STS201710061513571.dmp
Job "SYS"."STS201710061513571" successfully completed at Fri Oct 6 15:26:50 2017 elapsed 0 00:00:47

PL/SQL procedure successfully completed.

STSが空の場合の例
ステージング表へのSTSのパックやDataPump Exportの実行をパイパス。STSが空であることを表示して終了します。
なお、以下の例ではキャプチャ時間と間隔は意図的に短くしてあります。

[oracle@guppy  ˜]$ sqlplus system@orcl @sts_capture stsusr sts_exp_dir 2*60 60

SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 6 16:04:55 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

old 4: cTimeLimit CONSTANT POSITIVE := &3;
new 4: cTimeLimit CONSTANT POSITIVE := 2*60;
old 5: cInterval CONSTANT POSITIVE := &4;
new 5: cInterval CONSTANT POSITIVE := 60;
old 7: cStagingSchema CONSTANT VARCHAR2(30) := UPPER('&1');
new 7: cStagingSchema CONSTANT VARCHAR2(30) := UPPER('stsusr');
old 11: cDirectory CONSTANT VARCHAR2(20) := UPPER('&2');
new 11: cDirectory CONSTANT VARCHAR2(20) := UPPER('sts_exp_dir');
*** Info - No data packed from SQLSET ***

PL/SQL procedure successfully completed.


このスクリプトをshellに組み込んでcronでも定期実行したりDBMS_SCHEDULERで定期実行するのも良いかな。
オレオレサンプルなので、より便利に改造して使ってね。v

| | コメント (0) | トラックバック (0)

2017年9月 1日 (金)

ORA-12034 #2

前回の通り、高速リフレッシュの間にdata pump export / importを完了させれば、ORA-12034なんて起きないはず。

VirtualBoxの環境は試験開始前のスナップショットで戻してあります
20170415_14044_2

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 28 20:57:49 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

orcl@MVIEW_SCHEMA1> @mview_info_c
Connected.

¥Session altered.

ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+5/1440 2017/08/28 21:00:54 3

MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE
------------------------------ ------ -------- -------- ------------------- -------------------
MV_MASTER DEMAND FAST FAST UNDEFINED VALID

JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/08/28 20:55:54 2017/08/28 21:00:54 sysdate+5/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');

前回は5分間隔だったので、余裕をもたせて30分にしてあります。
本番環境で5分間隔の高速リフレッシュを30分にしたり、止めたりってことはかなり敷居が高いとは思いますが、そのあたりは空気を読んで対応する必要があるかと。 :)

orcl12c@SYS> conn mview_schema1@orcl
Connected.
orcl@MVIEW_SCHEMA1> alter materialized view mv_master refresh next sysdate+30/1440;

Materialized view altered.

orcl@MVIEW_SCHEMA1> @mview_info_c
Connected.

Session altered.


ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+30/1440 2017/08/28 21:30:36 3

MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE
------------------------------ ------ -------- -------- ------------------- -------------------
MV_MASTER DEMAND FAST FAST UNDEFINED VALID

JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/08/28 20:55:54 2017/08/28 21:30:36 sysdate+30/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');

準備は整ったので、高速リフレッシュが終わったタイミングでexportからimportまでをやっつけちゃいます!
まずはエクスポート

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@orcl directory=workdir dumpfile=mview_schema1.dmp logfile=exp_mview_schema1.log schemas=mview_schema1
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
. . exported "MVIEW_SCHEMA1"."MV_MASTER" 5.5 KB 2 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/userhome/oracle/mview_schema1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Aug 28 21:05:00 2017 elapsed 0 00:00:56

[oracle@vbgeneric ˜]$ sqlplus master_schema@orcl

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 28 21:05:19 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

orcl@MASTER_SCHEMA> select count(*) from mlog$_master;

COUNT(*)
----------
0

orcl@MASTER_SCHEMA> conn mview_schema1@orcl
Connected.

orcl@MVIEW_SCHEMA1> @mview_info_c
Connected.

Session altered.


ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+30/1440 2017/08/28 21:30:36 3

MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE
------------------------------ ------ -------- -------- ------------------- -------------------
MV_MASTER DEMAND FAST FAST UNDEFINED VALID

JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/08/28 20:55:54 2017/08/28 21:30:36 sysdate+30/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');

次にインポートしてMviewを複製! 

orcl12c@SYS> conn system@orcl2
Connected.
orcl2@SYSTEM> create directory workdir as '/u01/userhome/oracle';

Directory created.

orcl2@SYSTEM> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@vbgeneric ˜]$ impdp system@orcl2 directory=workdir dumpfile=mview_schema1.dmp logfile=imp_mview_schema1.dmp

Import: Release 12.1.0.2.0 - Production on Mon Aug 28 21:07:13 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@orcl2 directory=workdir dumpfile=mview_schema1.dmp logfile=imp_mview_schema1.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MVIEW_SCHEMA1"."MV_MASTER" 5.5 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Mon Aug 28 21:07:40 2017 elapsed 0 00:00:23

複製完了!!!!
結果はいかに...

[oracle@vbgeneric ˜]$ sqlplus mview_schema1@orcl2

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 28 21:08:09 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

orcl2@MVIEW_SCHEMA1> @mview_info_c
Connected.

Session altered.

ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+30/1440 2017/08/28 22:00:46 5
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+30/1440 2017/08/28 22:00:46 3

MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE
------------------------------ ------ -------- -------- ------------------- -------------------
MV_MASTER DEMAND FAST FAST UNDEFINED VALID
MV_MASTER DEMAND FAST FAST UNDEFINED VALID

JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/08/28 21:30:46 2017/08/28 22:00:46 sysdate+30/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/08/28 21:30:46 2017/08/28 22:00:46 sysdate+30/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');

予想通り成功しました!

エクスポート〜インポートによるMVIEW複製までを高速リフレッシュの間に行うことが可能ならば例のエラーは回避できることが確認できました。
が、
小さなMViewならまだしも、巨大なMView、巨大なMViewが複数あるリフレッシュグループだったりすると、それはもう大変な作業になることは想像できます。
大人の事情でMViewの高速リフレッシュを止められない、とか高速リフレッシュ間隔が非常短い場合には無理しないで、ほかの手を考えた方が良いと思います。
ただし、他の手を進めるにも時間には余裕を持った方がいいですよね。なんでギリギリなんだろうねと、よく思います。
夏休みの最後の1週間で宿題全部やりきるみたいなのは嫌ですよねw
20170802_140801




Data Pumpも癖モノだよね〜w その1 - queryパラメーターの解析タイミング
Data Pumpも癖モノだよね〜w その2 - Materialized ViewをTableとして移行する
Data Pumpも癖モノだよね〜w その3 - dbms_job と dbms_scheduler との複雑な関係
Data Pumpも癖モノだよね〜w その4 - schemaモードでMviewを他のPDBへ複製
Data Pumpも癖モノだよね〜w その4と1/2 - schemaモードでMviewを他のPDBへ複製 (紛らわしいステータスw)
Data Pumpも癖モノだよね〜w その5 - schemaモードでMviewを他のPDBへ複製(オプジェクトパス de 絞り込み)
Data Pumpも癖モノだよね〜w その6 - schemaモードでMviewを他のPDBへ複製(オプジェクトパスが不足すると...)
ORA-12034

| | コメント (0) | トラックバック (0)

2017年8月31日 (木)

ORA-12034

一ヶ月の家庭内、ワンオペも無事終了したので、ブログも今年前半のペースで再開か!?w

ということで、
以前ちょっとだけ書いた高速リフレッシュを止めてないと完全リフレッシュが必要になってしまう。タイミングの問題にフォーカスしてみようと思います。

どのようなタイミングの問題かというと、

ORA-12034: materialized view log on "xxxxxx"."xxxxxxx" younger than last refresh

出会った方も意外と多かったりしてw 

前述のエラーは、materialized view logが絡んいるので、”高速リフレッシュ”時に発生するエラーです!
高速リフレッシュを行なっている環境でこの状態になってしまうと、”高速リフレッシュ”の再開には”完全リフレッシュ”が必須となってしまうところが怖いというか面倒くさいところ。
マスターサイトも含めて同期するサイズが小さければ完全リフレッシュも面倒なことにならない場合もありますが、数十GB以上の巨大なマテビューだったら、どうします???
マテリアライズドビューのリフレッシュ間隔が短いシステムだと、完全リフレッシュに要する時間が大問題になることも... (色々な状況が想定されていない構成だと、そうなりやすい.....なw

もう少し簡単に言うと、
materialized view logの伝播が必要なデータ有無に関係なく、リフレッシュ時刻を跨いてしまうかどうか! なんですよね、これ。

実際にどうなるか、materialized view logは空の状態で、Data Pumpも癖モノだよね〜w その2 - Materialized ViewをTableとして移行する
の環境を利用して再現させてみます。環境の詳細は、Data Pumpも癖モノだよね〜w その4 - schemaモードでMviewを他のPDBへ複製参照のこと。


VirtualBoxのスナップショットから基本レプリケーション環境構築済みの状態(以前いろいろ行った試験前の状態)に戻してあります。
20170415_14044

まず、高速リフレッシュの状態がどうなっているか確認してみると....5分間隔(INTERVAL列)で動作中であることがわかりますよね!(DBMS_JOBのINTERVALの指定方法っ非直感的わかりづらいので早くなくなってほしいw)
直近の高速リフレッシュが終わり、MLOGも0件。つまりマテリアライズドビューに反映する必要のあるデータは存在しない状態にしてあります。


orcl@MVIEW_SCHEMA1> select job,log_user,schema_user,last_date,next_date,interval,failures,what from user_jobs;

JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/08/28 09:00:44 2017/08/28 09:05:44 sysdate+5/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');

前述の状態(5分間隔の高速リフレッシュが動作中の状態)で、次の高速リフレッシュが実行されるまでの間にマテリアライズドビュー関連オブジェクトを含むschemaを丸ごとエクスポートします(他のデータベースへ複製するために)
エラーを再現するData Pump Export、Importと高速リフレッシュ間隔との間合いは下図の通り
20170802_140338

上図の通りの流れでData Pump ExportからImportまでを行い、ORA-12034の発生状況を確認

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@orcl directory=workdir dumpfile=mview_schema1.dmp logfile=exp_mview_schema1.log schemas=mview_schema1
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
. . exported "MVIEW_SCHEMA1"."MV_MASTER" 5.5 KB 2 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/userhome/oracle/mview_schema1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Aug 28 09:02:48 2017 elapsed 0 00:00:54


念のためマスターサイトでMLOGの件数を確認しておきます。
マテビューサイトと同期する必要のあるデータはないことが確認できます。

orcl@MASTER_SCHEMA> select count(*) from mlog$_master;

COUNT(*)
----------
0


という確認を行なっている間いに高速リフレッシュが実行されました! (LAST_DATE列、NEXT_DATE列、FAILURES列から正常にリフレッシュされたことを確認できます)

orcl@MVIEW_SCHEMA1> select job,log_user,schema_user,last_date,next_date,interval,failures,what from user_jobs;
JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/08/28 09:05:48 2017/08/28 09:10:48 sysdate+5/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');

まず、異なるPDBにディレクトリオブジェクトを作成して、schemaごとインポートします。
この時点で以前のエントリにもあるようにORCL:master_schema.masterをマスターサイトするmviewが、ORCL:mview_schema1.mview_masterとORCL2:mview_schema1.mview_masterという構成になります。


orcl2@SYSTEM> create directory workdir as '/u01/userhome/oracle';

Directory created.


Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@orcl2 directory=workdir dumpfile=mview_schema1.dmp logfile=imp_mview_schema1.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MVIEW_SCHEMA1"."MV_MASTER" 5.5 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Mon Aug 28 10:02:09 2017 elapsed 0 00:00:22


インポートが無事終わったので、高速リフレッシュの状態を確認!!

おおおおおおおお〜〜、インポートしたschemaに含まれるMVIEWは高速リフレッシュに失敗しています。
なんということでしょうw(狙い通りなので、わざとらしいですねw)

なお、LAST_REF列がCOMPLETEという完全リフレッシュを示すステータスになっていますが、これはで確認下通り、昔からこんな動作だった曖昧な記憶があるので仕様だと思われますが、
インポート時に完全リフレッシュしているわけでもないのに、完全リフレッシュ扱いとされている紛らわしい状態になります。(このステータスが影響している可能性もありそうな...)

orcl2@MVIEW_SCHEMA1> @mview_info_c
Connected.

Session altered.

ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+5/1440 2017/08/28 10:06:46 3
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+5/1440 2017/08/28 10:04:22 5


MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE
------------------------------ ------ -------- -------- ------------------- -------------------
MV_MASTER DEMAND FAST FAST UNDEFINED VALID
MV_MASTER DEMAND FAST COMPLETE UNDEFINED VALID


JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/08/28 10:01:46 2017/08/28 10:06:46 sysdate+5/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/08/28 10:04:22 sysdate+5/1440 1 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');


高速リフレッシュのが実行され2度目の失敗、failusers列が2になっています。いずれ高速リフレッシュジョブは自動的に停止されます。
(自動停止される前に問題に対処すればそのまま継続されます)

orcl2@MVIEW_SCHEMA1> @mview_info_c
Connected.

Session altered.

ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+5/1440 2017/08/28 10:08:26 5
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+5/1440 2017/08/28 10:06:46 3


MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE
------------------------------ ------ -------- -------- ------------------- -------------------
MV_MASTER DEMAND FAST FAST UNDEFINED VALID
MV_MASTER DEMAND FAST COMPLETE UNDEFINED VALID


JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/08/28 10:01:46 2017/08/28 10:06:46 sysdate+5/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/08/28 10:08:26 sysdate+5/1440 2 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');

では、原因を調査してみましょう!


P列(POSSIBLE列)の REFRESH_FAST行がNになっていて高速リフレッシュできない状態になっています。
コメントにはmv log is newer than last full refresh 記載されています。 このメッセージ冒頭で書いたメッセージと微妙に違うのですが、

orcl2@MVIEW_SCHEMA1> @?/rdbms/admin/utlxmv

Table created.

orcl2@MVIEW_SCHEMA1> exec dbms_mview.explain_mview('MV_MASTER','TEST01');

PL/SQL procedure successfully completed.

orcl2@MVIEW_SCHEMA1> SELECT mvname,capability_name,related_text,related_num,msgno,possible,msgtxt,seq FROM mv_capabilities_table;

MVNAME CAPABILITY_NAME RELATED_TEXT RELATED_NUM MSGNO P MSGTXT SEQ
------------------------------ ------------------------------ -------------------- ----------- ---------- - ------------------------------------------------------------------------------------------ ----------
MV_MASTER PCT N 1
MV_MASTER REFRESH_COMPLETE Y 1002
MV_MASTER REFRESH_FAST N 2003
MV_MASTER REWRITE N 3004
MV_MASTER PCT_TABLE MASTER 52 2068 N relation is not a partitioned table 4005
MV_MASTER REFRESH_FAST_AFTER_INSERT MASTER_SCHEMA.MASTER 2077 N mv log is newer than last full refresh 5006
MV_MASTER REFRESH_FAST_AFTER_ONETAB_DML 2146 N see the reason why REFRESH_FAST_AFTER_INSERT is disabled 6007
MV_MASTER REFRESH_FAST_AFTER_ANY_DML 2161 N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled 7008
MV_MASTER REFRESH_FAST_PCT 2197 N PCT FAST REFRESH is not possible if query contains a remote table 8009
MV_MASTER REWRITE_FULL_TEXT_MATCH MASTER 52 2099 N mv references a remote table or view in the FROM list 9010
MV_MASTER REWRITE_FULL_TEXT_MATCH 2159 N query rewrite is disabled on the materialized view 9011
MV_MASTER REWRITE_PARTIAL_TEXT_MATCH 2159 N query rewrite is disabled on the materialized view 10012
MV_MASTER REWRITE_GENERAL 2159 N query rewrite is disabled on the materialized view 11013
MV_MASTER REWRITE_PCT 2158 N general rewrite is not possible or PCT is not possible on any of the detail tables 12014
MV_MASTER PCT_TABLE_REWRITE MASTER 52 2068 N relation is not a partitioned table 13015

15 rows selected.


試しに手動で高速リフレッシュさせてみます。

orcl2@MVIEW_SCHEMA1> exec dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');
BEGIN dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"'); END;

*
ERROR at line 1:
ORA-12034: materialized view log on "MASTER_SCHEMA"."MASTER" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_IREFRESH", line 687
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1


冒頭で説明した高速リフレッシュ時のエラーが発生していることがわかります。
そして、.......
このエラーが発生してしまうと、完全リフレッシュで回復させるしかありません!!!!! 


参考:
新規マテリアライズド・ビュー・サイトでの高速リフレッシュ・エラー

"新規マテリアライズド・ビュー・サイトでマテリアライズド・ビュー作成中に、マスター表またはマスター・マテリアライズド・ビューのマテリアライズド・ビュー・ログがパージされる場合があります。これが発生すると、次のエラーが検出される場合があります。

ORA-12004 REFRESH FAST cannot be used for materialized view materialized_view_name
ORA-12034 materialized view log on materialized_view_name younger than last refresh"


回避策として使えそうな方法は....
高速リフレッシュを停止して行う方法もあります(止められないシステムもあるので大人の事情しだいでしょうけど)
デプロイメントテンプレートで対応できそうな要件か十分検討、検証した上でやる必要があると思います。(完全リフレッシュするマテビューが巨大すぎて完全リフレッシュだけで作業時間オーバーなんてこともありえますから)

マニュアルに記載されている回避方法ってのもあるけど、手順がめんどくさい>< な。 可能なら止めちゃった方が楽そうだが、大人の事情が絡んでるとそうもいかないだろうし、結局めんどくさいw
めんどくさいの嫌いなので試したこともないんだけど、時間があったら回避できるか試してみるか....な。

新規マテリアライズド・ビュー・サイトを追加するときの問題の回避


次回は、高速リフレッシュを跨がないように、したら回避できるよね。と言うお話へつづく
リフレッシュの間に終わらないとNGだけどw 、高速リフレッシュの間にexport/importが終わればこの問題は発生しない。。わけで。
次回のネタのイメージ(リフレッシュをまた跨らなければ例のエラーは発生しないという確認:)
20170802_140351




Data Pumpも癖モノだよね〜w その1 - queryパラメーターの解析タイミング

Data Pumpも癖モノだよね〜w その2 - Materialized ViewをTableとして移行する

Data Pumpも癖モノだよね〜w その3 - dbms_job と dbms_scheduler との複雑な関係

Data Pumpも癖モノだよね〜w その4 - schemaモードでMviewを他のPDBへ複製

Data Pumpも癖モノだよね〜w その4と1/2 - schemaモードでMviewを他のPDBへ複製 (紛らわしいステータスw)

Data Pumpも癖モノだよね〜w その5 - schemaモードでMviewを他のPDBへ複製(オプジェクトパス de 絞り込み)

Data Pumpも癖モノだよね〜w その6 - schemaモードでMviewを他のPDBへ複製(オプジェクトパスが不足すると...)

| | コメント (0) | トラックバック (0)

2017年7月29日 (土)

SQLとPL/SQLで「モルダー、あなた疲れてるのよ」を出力する方法

もうかれこれ1年以上経過しているので、何を今更という感じがしないでもないですがw

ここ2週間くらい、SQLチューニングじゃない方向で、非常に忙しかったこともあり

気分的に疲れてしまったので、気分転換のために作ってみました。

Ruby でモルダー、あなた疲れてるのよを出力する方法

Pythonでモルダー案件

Groovyで「モルダー、あなた疲れてるのよ」に対処する

と、どう見てもPL/SQLやSQL案件ではないネタですが、PL/SQL de Python Challenge精神(どんな精神じゃw)で無理やり案件化して見ました。

疲れてる時に疲れるネタやるの?、バカなの? 
とお感じの方もお多いかと存じますが、可能ならもっと変態的なネタに発展することを願いつつ、ご挨拶と代えさせていただきます

データベースエンジニアでも、「モルダー、あなた疲れてるよの」という文字列を出力させる無茶振り案件に突っ込まれるかもしれません。
そんなときの参考になれば幸いです。


スペースで区切ったワードをシャッフルし、「モルダー、あなた疲れてるのよ」が出力されたら終了です。


色々な「モルダー、あなた疲れてるのよ」がありますので、よろしくお願いします!

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
に接続されました。
orcl@SYSTEM> @mulder_you_tired

スカリーより
--------------------------------------------------------------------------------
1: 疲れてる、あなたのよモルダー
2: のよ、あなたモルダー疲れてる
3: モルダー、疲れてるあなたのよ
4: 疲れてる、あなたモルダーのよ
5: モルダー、あなたのよ疲れてる
6: のよ、あなたモルダー疲れてる
7: 疲れてる、モルダーあなたのよ
8: モルダー、のよ疲れてるあなた
9: のよ、疲れてるモルダーあなた
10: 疲れてる、のよモルダーあなた
11: モルダー、あなたのよ疲れてる
12: のよ、疲れてるあなたモルダー
13: モルダー、のよあなた疲れてる
14: 疲れてる、あなたのよモルダー
15: 疲れてる、あなたモルダーのよ
16: モルダー、あなたのよ疲れてる
17: のよ、あなたモルダー疲れてる
18: モルダー、あなた疲れてるのよ


コードは以下のとおり、WITH FUNCTIONで文字列を生成しCLOBで1行で返しています。
改行コード(UTL_TCP.CRLFを利用、UTL_TCPパッケージなんて使っている人は少ないと思いますがw)を付加し出力時に改行させ複数行に見せています。
WITH FUNCTIONをSELECT文のセレクトリスト部分で利用しています。(TABLE FunctionならFROM句で使えますが、TABLE Functionではないので)

09:54:34 orcl@SYSTEM> !cat mulder_you_tired.sql
set long 100000
set pagesize 10000
WITH function mulderYouAreTired (dummy char)
RETURN CLOB
IS
cMulderYouTired CONSTANT VARCHAR2(42) := 'モルダー、あなた疲れてるのよ';
vMessage VARCHAR2(42);
vMessageFromScully CLOB := EMPTY_CLOB();
line# BINARY_INTEGER := 1;
--
FUNCTION generateMessage
RETURN VARCHAR2
IS
TYPE word_arr IS TABLE OF VARCHAR2(12) INDEX BY BINARY_INTEGER;
cPunctuation CONSTANT CHAR(3) := '、';
vWords VARCHAR2(45) := 'モルダー あなた 疲れてる のよ';
vMessageFromScully VARCHAR2(42);
vMessage word_arr;
vWord VARCHAR2(13);
isSetWord BOOLEAN := FALSE;
idx BINARY_INTEGER := 1;
BEGIN
--
FOR i IN 1..REGEXP_COUNT(vWords, ' ') + 1 LOOP
vMessage(i) := NULL;
END LOOP;
--
WHILE INSTR(vWords, ' ') != 0 LOOP
vWord := SUBSTR(vWords, 1, INSTR(vWords, ' '));
vWords := REPLACE(vWords, vWord);
isSetWord := FALSE;
WHILE NOT isSetWord LOOP
idx := TRUNC(DBMS_RANDOM.VALUE(vMessage.FIRST, vMessage.LAST + 1));
IF vMessage(idx) IS NULL THEN
vMessage(idx) := TRIM(vWord);
isSetWord := TRUE;
END IF;
END LOOP;
END LOOP;
--
FOR j IN vMessage.FIRST..vMessage.LAST LOOP
IF vMessage(j) IS NULL THEN
vMessage(j) := vWords;
END IF;
END LOOP;
--
FOR x IN vMessage.FIRST..vMessage.LAST LOOP
IF x = 2 THEN
vMessageFromScully := vMessageFromScully || cPunctuation;
END IF;
vMessageFromScully := vMessageFromScully || vMessage(x);
END LOOP;
RETURN vMessageFromScully;
END generateMessage;
BEGIN
LOOP
vMessage := generateMessage();
vMessageFromScully :=
vMessageFromScully || TO_CHAR(line#)
|| ': ' || vMessage || UTL_TCP.CRLF;
line# := line# + 1;
EXIT WHEN vMessage = cMulderYouTired;
END LOOP;
RETURN vMessageFromScully;
END;
SELECT
mulderYouAreTired(null) AS "スカリーより"
FROM
dual;
/

| | コメント (0) | トラックバック (0)

2017年7月 1日 (土)

リソースマネージャ:MTA環境のインスタンスケージングが効いているかざっくり確認するスクリプト

元になるビューは1分間隔で更新されているようで秒単位の粒度では確認できないもののざっくりでもいいからリアルタムに確認したい場合には便利

むかーしやった検証はnon CDBかつ、秒単位で見たいという要望だったため他のビューから算出したこともあり、ここで利用しているv$rsrcmetricの類は使わなかったことを思い出した。
完全に忘れてた(@@)。
2年も触らなきゃ忘れるさ。人間だものw orz.

ということで、 Oracle Database 12c 12.1.0.2.0 向けのメモ

注)以下、CDB$ROOTに接続してSQL文を実行しています


利用環境は以下の通りのMTA

orcl12c@SYS> select * from v$version;

BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0


CPUは4 (VMですが)

orcl12c@SYS> show parameter cpu_count

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 4


ORCLとORCL2という2つのPDB対してCPUのUTILIZATION_LIMITでCPU利用率を制限しています。
12.2以降はPDBのインスタンスケージングもnon-CDB環境同様に、CPU_COUNT設定+リソースマネージャで制御できちゃうらしい。わかりやすくて良い!(時間があればいずれ)

orcl12c@SYS> select name from v$containers;

NAME
------------------------------
CDB$ROOT
PDB$SEED
ORCL
ORCL2


orcl12c@SYS> r
1 SELECT
2 vc.name
3 , vp.utilization_limit
4 FROM
5 v$rsrc_plan vp
6 INNER JOIN v$containers vc
7 ON vp.con_id=vc.con_id
8 ORDER BY
9* vc.con_id

NAME UTILIZATION_LIMIT
------------------------------ -----------------
CDB$ROOT
PDB$SEED
ORCL 25
ORCL2 50

それぞれのPDBでCPU数以上のCPUバウンドな処理を実行して負荷かけ中

resmgr:cpu quantumとう待機イベントはresource managerがCPUの利用率を制御していることを示す待機イベント!
リソース制御が効いていることを示してます。

orcl12c@SYS> select username,event from v$session where username is not null order by username;

USERNAME EVENT
-------------- ----------------------------------------------------------------
SYS Streams AQ: waiting for messages in the queue
SYS Streams AQ: waiting for messages in the queue
SYS SQL*Net message from client
SYS Streams AQ: waiting for messages in the queue
SYS Streams AQ: waiting for messages in the queue
SYS resmgr:cpu quantum
SYS SQL*Net message to client
SYS SQL*Net message from client
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum
TOTHER01 resmgr:cpu quantum


vmstatでは全体のCPU利用率は見えますが複数のPDBが想定通りケージングされているかは見えません。

$ vmstat -t 5 1000
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu----- -----timestamp-----
r b swpd free buff cache si so bi bo in cs us sy id wa st JST
12 0 0 1487628 55660 4727120 0 0 125 13 452 262 21 1 78 0 0 2017-06-29 23:48:16
11 0 0 1480868 55668 4727176 0 0 0 28 3817 1459 69 2 29 0 0 2017-06-29 23:48:21
11 0 0 1480900 55676 4727068 0 0 0 16 3982 1480 73 2 25 0 0 2017-06-29 23:48:26
13 0 0 1480948 55684 4727008 0 0 0 17 3722 1383 68 2 30 0 0 2017-06-29 23:48:31
1 0 0 1480956 55684 4726980 0 0 0 10 3765 1410 69 2 29 0 0 2017-06-29 23:48:36
3 0 0 1480956 55692 4726984 0 0 0 18 3886 1417 76 2 22 0 0 2017-06-29 23:48:41
10 0 0 1480336 55716 4727044 0 0 0 35 3830 1441 69 2 29 0 0 2017-06-29 23:48:46
0 0 0 1480336 55724 4727036 0 0 0 24 4033 1536 76 2 22 0 0 2017-06-29 23:48:51
14 0 0 1469888 55724 4727100 0 0 0 14 3722 1508 67 2 31 0 0 2017-06-29 23:48:56
11 0 0 1459988 55732 4727384 0 0 0 17 4049 1685 73 3 24 0 0 2017-06-29 23:49:01
13 0 0 1419652 55740 4728940 0 0 134 38 4299 1901 80 3 16 0 0 2017-06-29 23:49:06
10 0 0 1419676 55748 4728860 0 0 0 21 3861 1491 69 2 29 0 0 2017-06-29 23:49:11
10 0 0 1419768 55756 4729044 0 0 0 28 3914 1471 71 2 27 0 0 2017-06-29 23:49:16
12 0 0 1421380 55764 4729064 0 0 0 31 3697 1445 68 2 31 0 0 2017-06-29 23:49:21
11 0 0 1421412 55764 4729016 0 0 0 13 3735 1412 68 2 30 0 0 2017-06-29 23:49:26
10 0 0 1421544 55772 4728980 0 0 0 18 3986 1521 73 2 25 0 0 2017-06-29 23:49:31
11 0 0 1464824 55780 4728524 0 0 0 11 3702 1372 68 2 30 0 0 2017-06-29 23:49:36
10 0 0 1458468 55788 4728572 0 0 1 20 4030 1495 74 2 24 0 0 2017-06-29 23:49:41
10 0 0 1458624 55796 4728452 0 0 0 17 3836 1520 68 2 29 0 0 2017-06-29 23:49:46
0 0 0 1458880 55804 4728400 0 0 0 20 3908 1522 70 2 28 0 0 2017-06-29 23:49:51
0 0 0 1458864 55804 4728424 0 0 0 13 4039 1453 76 2 21 0 0 2017-06-29 23:49:56
0 0 0 1458864 55812 4728420 0 0 0 18 3870 1471 70 2 28 0 0 2017-06-29 23:50:01
1 0 0 1454680 55820 4728804 0 0 2 16 4541 2070 76 4 20 0 0 2017-06-29 23:50:06
1 0 0 1454728 55828 4728688 0 0 0 29 3829 1453 70 2 28 0 0 2017-06-29 23:50:11
9 0 0 1454048 55836 4728596 0 0 0 18 3751 1415 69 2 29 0 0 2017-06-29 23:50:16
10 0 0 1454056 55836 4728548 0 0 0 15 3867 1472 72 2 26 0 0 2017-06-29 23:50:21
11 0 0 1454088 55844 4728480 0 0 0 21 3704 1390 67 2 31 0 0 2017-06-29 23:50:26
10 0 0 1454252 55852 4728396 0 0 0 17 3774 1391 70 2 28 0 0 2017-06-29 23:50:31
10 0 0 1465692 55860 4728416 0 0 0 24 3914 1484 71 2 26 0 0 2017-06-29 23:50:36
10 0 0 1465468 55868 4728380 0 0 0 18 3794 1435 68 2 30 0 0 2017-06-29 23:50:41


最初に書いたように、1分間隔で少々粒度は粗めですがざっくり各PDBのCPU利用率を確認する場合には以下のようなスクリプトが便利!!
以下クエリをshellで定期的に実行するなり、随時実行するなりして確認すると便利、なお、gv$rsrcmgrmetricの代わりに、gv$rsrcmgrmetric_historyを
利用すれば過去1時間分だけですが、遡って確認することもできます。

orcl12c@SYS> !cat show_con_cpu.sql
SELECT
to_char(begin_time, 'RR/MM/DD HH24:MI:SS') AS time
, vc.con_id
, vc.name
, (SELECT value FROM v$osstat WHERE stat_name = 'NUM_CPUS') AS "CPUs"
, round((sum(cpu_consumed_time) / 1000) / (60 * (SELECT value FROM v$parameter WHERE name = 'cpu_count'))*100,2) AS "%cpu"
FROM
gv$rsrcmgrmetric gvr
INNER JOIN v$containers vc
ON
vc.con_id = gvr.con_id
GROUP BY
vc.con_id
,vc.name
,begin_time
ORDER BY
begin_time
,vc.con_id
/

各PDBが25%、50%で制限されていることがわかります!!

orcl12c@SYS> @show_con_cpu.sql

TIME CON_ID NAME CPUs %cpu
----------------- ---------- ------------------------------ ---------- ----------
17/06/29 23:48:30 1 CDB$ROOT 4 .32
17/06/29 23:48:30 2 PDB$SEED 4 0
17/06/29 23:48:30 3 ORCL 4 25.87
17/06/29 23:48:30 5 ORCL2 4 51.3

orcl12c@SYS> @show_con_cpu.sql

TIME CON_ID NAME CPUs %cpu
----------------- ---------- ------------------------------ ---------- ----------
17/06/29 23:49:31 1 CDB$ROOT 4 .03
17/06/29 23:49:31 2 PDB$SEED 4 0
17/06/29 23:49:31 3 ORCL 4 25.31
17/06/29 23:49:31 5 ORCL2 4 50.74


しかし、おとといの無茶振りというか、俺、何スレッドで動作すればいいの的な、パススルーな振りに、疲れて
こんなネタにしてみましたよ。と。w

| | コメント (0) | トラックバック (0)

2017年5月 8日 (月)

セマフォ?

Solaris11.3にOracle Database 12.1と12.2をなにげにインスコしてみたところ。。。

12.1.0.2はこんな感じ、processes=300なんで、Linuxでもよく見る感じでセマフォセット3 * セマフォ152(使われてないセマフォセットあり)

SQL> select * from v$version;

BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Solaris: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0

SQL>
SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 300
SQL>

root@angelfish:˜#  cat /etc/release
Oracle Solaris 11.3 X86
Copyright (c) 1983, 2015, Oracle and/or its affiliates. All rights reserved.
Assembled 06 October 2015

root@angelfish:˜# ipcs -a
IPC status from as of 2017年05月07日 (日) 17時58分27秒 JST
T ID KEY MODE OWNER GROUP CREATOR CGROUP CBYTES QNUM QBYTES LSPID LRPID STIME RTIME CTIME
Message Queues:
T ID KEY MODE OWNER GROUP CREATOR CGROUP NATTCH SEGSZ CPID LPID ATIME DTIME CTIME
Shared Memory:
m 28 0xc6002aac --rw-r----- oracle oinstall oracle oinstall 57 16384 1393 1671 17:57:45 17:58:02 17:47:36
m 27 0x0 --rw-r----- oracle oinstall oracle oinstall 57 14680064 1393 1671 17:57:45 17:58:02 17:47:36
m 26 0x0 --rw-r----- oracle oinstall oracle oinstall 57 2566914048 1393 1671 17:57:45 17:58:02 17:47:36
m 25 0x0 --rw-r----- oracle oinstall oracle oinstall 57 4194304 1393 1671 17:57:45 17:58:02 17:47:36
T ID KEY MODE OWNER GROUP CREATOR CGROUP NSEMS OTIME CTIME
Semaphores:
s 5 0xde1cdf8e --ra-r----- oracle oinstall oracle oinstall 152 17:47:39 17:47:36
s 4 0xde1cdf8d --ra-r----- oracle oinstall oracle oinstall 152 no-entry 17:47:36
s 3 0xde1cdf8c --ra-r----- oracle oinstall oracle oinstall 152 17:58:25 17:47:36
root@angelfish:˜#

こちらは、12.2.0.1なんですが、セマフォないんですよ! ????

Solarisつかうことも滅多にないんですが、気にはなりまする。なんだろう。なにかミスった? ちなみに12.2+Linuxだとしっかりセマフォが見えるんだけど。なにこれ。時間とれれば調べるか。。。も

SQL> select * from v$version;

BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Solaris: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0

SQL>
SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
asm_io_processes integer 20
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 4000
log_archive_max_processes integer 4
processes integer 300
SQL>

root@angelfish:˜# ipcs -a
IPC status from as of 2017年05月07日 (日) 18時15分51秒 JST
T ID KEY MODE OWNER GROUP CREATOR CGROUP CBYTES QNUM QBYTES LSPID LRPID STIME RTIME CTIME
Message Queues:
T ID KEY MODE OWNER GROUP CREATOR CGROUP NATTCH SEGSZ CPID LPID ATIME DTIME CTIME
Shared Memory:
m 8 0x60e9fb44 --rw------- oracle oinstall oracle oinstall 62 16384 1411 1463 18:15:01 18:15:15 18:11:48
m 7 0x0 --rw------- oracle oinstall oracle oinstall 62 8388608 1411 1463 18:15:01 18:15:15 18:11:48
m 6 0x0 --rw------- oracle oinstall oracle oinstall 62 2566914048 1411 1463 18:15:01 18:15:15 18:11:48
m 5 0x0 --rw------- oracle oinstall oracle oinstall 62 10485760 1411 1463 18:15:01 18:15:15 18:11:48
T ID KEY MODE OWNER GROUP CREATOR CGROUP NSEMS OTIME CTIME
Semaphores:
root@angelfish:˜#

追記
bash-4.1$ pkg list entire
NAME (PUBLISHER) VERSION IFO
entire 0.5.11-0.175.3.1.0.5.0 i--
bash-4.1$ uname -r
5.11
bash-4.1$ cat /etc/release
Oracle Solaris 11.3 X86
Copyright (c) 1983, 2015, Oracle and/or its affiliates. All rights reserved.
Assembled 06 October 2015
bash-4.1$
bash-4.1$
bash-4.1$ projects -l user.oracle
user.oracle
projid : 101
comment: ""
users : oracle
groups : (none)
attribs: process.max-sem-nsems=(privileged,256,deny)
project.max-sem-ids=(privileged,100,deny)
project.max-shm-ids=(privileged,100,deny)
project.max-shm-memory=(privileged,4294967296,deny)
bash-4.1$

| | コメント (0) | トラックバック (0)

2017年5月 2日 (火)

あまりにもネタになってないので、少しだけ sysresv のことを書いといた

共有メモリやセマフォセットの数などの確認は、ipcsでほぼ足りてるわけですが。(複数インスタンスが起動している場合を除く)
あまりにもネタになってないので、少しだけ sysresvのことを書いといた:)

[oracle@vbgeneric ˜]$ sysresv

IPC Resources for ORACLE_SID "orcl12c" :
Maximum shared memory segment size (shmmax): 4398046511104 bytes
Total system shared memory (shmall): 4398046511104 bytes
Total system shared memory count (shmmni): 4096
*********************** Dumping ipcs output ********************

------ Message Queues --------
key msqid owner perms used-bytes messages

------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 0 oracle 600 8802304 173
0x00000000 32769 oracle 600 822083584 87
0x00000000 65538 oracle 600 7974912 87
0x21485470 98307 oracle 600 16384 87
0x00000000 262148 oracle 600 524288 2 dest
0x00000000 294917 oracle 600 4194304 2 dest
0x00000000 393222 oracle 600 33554432 2 dest
0x00000000 491527 oracle 600 4194304 2 dest
0x00000000 524296 oracle 600 1048576 2 dest

------ Semaphore Arrays --------
key semid owner perms nsems
0x245b195c 163840 oracle 600 152
0x245b195d 196609 oracle 600 152
0x245b195e 229378 oracle 600 152

*********************** End of ipcs command dump **************


***************** Dumping Resource Limits(s/h) *****************
core file size 0 KB/UNLIMITED
data seg size UNLIMITED/UNLIMITED
scheduling priority 0 KB/0 KB
file size UNLIMITED/UNLIMITED
pending signals 30 KB/30 KB
max locked memory 128 GB/128 GB
max memory size UNLIMITED/UNLIMITED
open files 64 KB/64 KB
POSIX message queues 800 KB/800 KB
real-time priority 0 KB/0 KB
stack size 32 MB/32 MB
cpu time UNLIMITED/UNLIMITED
max user processes 16 KB/16 KB
virtual memory UNLIMITED/UNLIMITED
file locks UNLIMITED/UNLIMITED

***************** End of Resource Limits Dump ******************
Maximum map count configured per process: 65530
Total /dev/shm size: 4050014208 bytes, used: 98304 bytes
Shared Memory:
ID KEY
32769 0x00000000
65538 0x00000000
0 0x00000000
98307 0x21485470
Semaphores:
ID KEY
163840 0x245b195c
196609 0x245b195d
229378 0x245b195e
Oracle Instance alive for sid "orcl12c"
[oracle@vbgeneric ˜]$
[oracle@vbgeneric ˜]$  ipcs -sb

------ Semaphore Arrays --------
key semid owner perms nsems
0x245b195c 163840 oracle 600 152
0x245b195d 196609 oracle 600 152
0x245b195e 229378 oracle 600 152
[oracle@vbgeneric ˜]$  ipcs -st

------ Semaphore Operation/Change Times --------
semid owner last-op last-changed
163840 oracle Tue May 2 01:35:32 2017 Tue May 2 01:26:30 2017
196609 oracle Not set Tue May 2 01:26:30 2017
229378 oracle Tue May 2 01:26:35 2017 Tue May 2 01:26:30 2017

| | コメント (0) | トラックバック (0)

2017年4月30日 (日)

Data Pumpも癖モノだよね〜w その6 - schemaモードでMviewを他のPDBへ複製(オプジェクトパスが不足すると...)

Previously on Mac De Oracle

前回は、schemaモードのData PumpでMviewを他のPDBへ複製する際、必要最小限のオブジェクト絞っても可能か? という確認でした。

なんだ、簡単じゃんと、思ったあなた!!
そうでもないんですよ!

SCHEMA_EXPORT_OBJECTSビューより、
オブジェクトパスの親子関係から親をエクスポートすれば子も一緒だねぇ〜。と想像はできるわけですが、
兄弟(TABLEやDB_LINKやMATERIALIZED_VIEW)の場合はそう簡単ではなくて、そもそも依存関係を把握してないとうまく使いこなせないんですよ!

というのが今日のネタ

FAQ!


前回同様、基本レプリケーション環境が完成した状態からエクスポートしてみます。

その前にパラメータファイルに含めるincludeパラメータを確認しておきましょう。
複製に成功したオブジェクトパスは以下でした。
SCHEMA_EXPORT_OBJECTSビューより)

OBJECT_PATH                     COMMENTS
------------------------------- ------------------------------------------------------------
SCHEMA_EXPORT/DB_LINK Private database links in the selected schemas
SCHEMA_EXPORT/TABLE Tables in the selected schemas and their dependent objects
SCHEMA_EXPORT/MATERIALIZED_VIEW Materialized views
SCHEMA_EXPORT/JOB Jobs in the selected schemas
SCHEMA_EXPORT/REFRESH_GROUP Refresh groups in the selected schemas

上記の赤字のオブジェクト(DB_LINK/TABLE/MATERIALIZED_VIEW/JOB/REFRESH_GROUP)はSCHEMA_EXPORTを親とする兄弟という関係なのは見ての通り。

MVIEWをTABLEとして複製の回でお見せしたとおり、MVIEWであってもTABLEのパスでエクスポートすれば、それはMATERIALIZED_VIEWではなく、TABLEだけ(正確に言うと、TABLEとその子孫のオブジェクト)がエクスポートされていました。

つまり、兄弟の場合、親子の場合と異なり関連はあっても自動でエクスポートしてくれるわけではない。という癖が見えてきます。:)
はっ! そうだったのか! という方のために試してみます!


INCLUDE=TABLEとすると、Data Pumpも癖モノだよね〜w その2 - Materialized ViewをTableとして移行するに書いたようにMviewではなくTABLEとしてエクスポートされ、インポートしてもTABLEのままでリフレッユジョブもリフレッシュグループもありませんでした。

では、INCLUDE=MATERIALIZED_VIEWとした場合には、リフレッシュジョブもリフレッシュグループもエクスポートされるんじゃないか? (そうは、うまくはいかないんです!)

[oracle@vbgeneric ˜]$ cat exp_mviewonly.par 
schemas=mview_schema1
include=MATERIALIZED_VIEW:"IN ('MV_MASTER')"

では、その様子を再現w よーく見ててね。

えーーーーーーっ!
うまくいった前回との違いおわかりですよね。 Mviewに含まれるデータもエクスポートされていませんし、索引、制約、リフレッシュジョブや、リフレッシュグループもエクスポートされていません!!!
なんということでしょうw 

オブジェクトパス:MATERIALIZED_VIEWに紐づくと思っていたオブジェクトは自動ではされてエクスポートされません!!!w 

[Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@orcl directory=workdir dumpfile=exp_mviewonly.dmp logfile=exp_mviewonly.log parfile=exp_mviewonly.par
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
*****************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/userhome/oracle/exp_mviewonly.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Apr 25 23:40:55 2017 elapsed 0 00:00:15


この状態で、他のPDBにインポートとしてみます。ユーザ及び、権限とData Pump作業用ディレクリオブジェクトは事前に作成しておきます。

なんということでしょう! Mviewが作成できません!
別な意味で、これにハマっている方もいるようです。
Problems creating MVIEW DDL using Data Pump and the SQLFILE option / Simon DBA Advanced Oracle Tips, Tricks, and How-To's

[oracle@vbgeneric ˜]$ sqlplus sys/oracle@orcl2 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 25 23:52:49 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

orcl2@SYS> create directory workdir as '/u01/userhome/oracle';

Directory created.

orcl2@SYS> create user mview_schema1 identified by welcome1
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users;

User created.

orcl2@SYS> grant create session, create table, create database link, create materialized view to mview_schema1;

Grant succeeded.

orcl2@SYS> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@vbgeneric ˜]$
[oracle@vbgeneric ˜]$ impdp system/oracle@orcl2 directory=workdir dumpfile=exp_mviewonly.dmp logfile=imp_mviewonly.log

Import: Release 12.1.0.2.0 - Production on Tue Apr 25 23:53:58 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@orcl2 directory=workdir dumpfile=exp_mviewonly.dmp logfile=imp_mviewonly.log
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
ORA-39083: Object type MATERIALIZED_VIEW:"MVIEW_SCHEMA1"."MV_MASTER" failed to create with error:
ORA-06550: line 1, column 9:
PLS-00352: Unable to access another database 'TO_MASTER_SCHEMA'
ORA-06550: line 1, column 9:
PLS-00201: identifier 'SYS@TO_MASTER_SCHEMA' must be declared
ORA-06550: line 1, column 9:
PL/SQL: Statement ignored
Failing sql is:
CREATE MATERIALIZED VIEW "MVIEW_SCHEMA1"."MV_MASTER" ("ID", "FOO") USING ("MV_MASTER", (10, 'ORCL', 1, 0, 0, "MASTER_SCHEMA", "MASTER", '2017-04-25 23:37:06',
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Tue Apr 25 23:54:05 2017 elapsed 0 00:00:04

これ、どうやったら解決するんでしょう?
答えは、不足しているオプジェクトパスを指定する!。 なのですが、その前にいくつか確認。
(SCHEMA_EXPORT_OBJECTSビューのオブジェクトパスをよーく覗いて見るとよいと思います)

1つめの事実

include=TABLE だけでエクスポートするとMVIEWではなくTABLEとしてエクスポートされ、インポートしてもTABLEのまま。
だたし、CONSTARINTはincludeパラメータに含めなくても、TABLEに関連するCONSTRAINTとINDEXと統計はエクスポートされている。
かつ、データもエクスポートされる!!! (オブジェクトパスの親子関係を見ると TABLE_DATAは、TABLEの子、CONSTRAINT、INDEX、STATISTICSも子、実際の統計は孫)
DB_LINK、JOB、REFRESH_GROUPはエクスポートされない。(オブジェクトパスの階層を見るとそれらのオブジェクトはTABLEと同じ階層にある兄弟であることがわかります。)

2つめの事実

include=MATERIALIZED_VIEWだけでエクスポートするとMVIEWとしての定義はエクスポートされている。
オブジェクトパスを見ると、MATERIALIZED_VIEWには子孫は存在していません。親はSCHEMA_EXPORT。
なので、兄弟である、DB_LINK、TABLE、DB_LINK、JOB、REFRESH_GROUPエクスポートされない。かつ、どうやら、MATERIALIZED_VIEWに含まれるのはメタデータだけっぽい。(メタデータっぽいですが、それでだけMVIEWが作成できるわけでもない)

なかなかの癖モノですね。

なのであれば、
MATERIALIZED_VIEWのみエクスポートしてインポートした時に発生したMVIEW作成エラーを回避するオブジェクトパスは、DB_LINK, TABLE, MATERIALIZED_VIEWなんじゃないかなぁと思うわけです。はい。
ただし、完全ではありません。理由は、リフレッシュグループとリフレッシュジョブが含まれていなためです。前述のオブジェクトパスに加え、 JOB と REFRESH_GROUP を含めると、
前回のとおりMviewと関連オブジェクトのみエクスポート/インポートすることができるようになります。

では、DB_LINK, TABLE, MATERIALIZED_VIEWだけをエクスポートして、インポートしたら自動リフレッシュはできないけどMVIEWとして作成できるか確認してみます。

[oracle@vbgeneric ˜]$ cat exp_mviewonly.par 
schemas=mview_schema1
include=DB_LINK
include=TABLE:"IN ('MV_MASTER')"
include=MATERIALIZED_VIEW:"IN ('MV_MASTER')"

・・・中略・・・

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@orcl directory=workdir dumpfile=exp_mviewonly.dmp logfile=exp_mviewonly.log parfile=exp_mviewonly.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
. . exported "MVIEW_SCHEMA1"."MV_MASTER" 5.5 KB 2 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/userhome/oracle/exp_mviewonly.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Apr 26 00:20:46 2017 elapsed 0 00:00:34

うまくインポートできました。自動リフレッシュはできませんが。2行登録されているMVIEWが作成されました!!

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@orcl2 directory=workdir dumpfile=exp_mviewonly.dmp logfile=imp_mviewonly.log
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MVIEW_SCHEMA1"."MV_MASTER" 5.5 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Apr 26 00:22:50 2017 elapsed 0 00:00:04


以下のとおり、CON_ID=5 (PDB:ORCL2)にはTABLE/INDEX/DB_LINK/MATERIALIZED_VIEWは作成されましたが、リフレッシュグループもリフレッシュジョブもありません。

orcl2@MVIEW_SCHEMA1> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
------------------------------ -----------------------
MV_MASTER TABLE
SYS_C0014888 INDEX
MV_MASTER MATERIALIZED VIEW
TO_MASTER_SCHEMA DATABASE LINK

orcl2@MVIEW_SCHEMA1> select rowner,rname,refgroup,job,broken,interval,next_date from user_refresh;

no rows selected

orcl2@MVIEW_SCHEMA1> select job,log_user,schema_user,last_date,next_date,interval,failures,what from user_jobs;

no rows selected

ということで、おしまい。


Data Pumpも癖モノだよね〜w その1 - queryパラメーターの解析タイミング
Data Pumpも癖モノだよね〜w その2 - Materialized ViewをTableとして移行する
Data Pumpも癖モノだよね〜w その3 - dbms_job と dbms_scheduler との複雑な関係
Data Pumpも癖モノだよね〜w その4 - schemaモードでMviewを他のPDBへ複製
Data Pumpも癖モノだよね〜w その4と1/2 - schemaモードでMviewを他のPDBへ複製 (紛らわしいステータスw)
Data Pumpも癖モノだよね〜w その5 - schemaモードでMviewを他のPDBへ複製(オプジェクトパス de 絞り込み)

| | コメント (0) | トラックバック (0)

2017年4月25日 (火)

Data Pumpも癖モノだよね〜w その5 - schemaモードでMviewを他のPDBへ複製(オプジェクトパス de 絞り込み)

Previously on Mac De Oracle

前回はschemaモードのエクスポートで、schemaごとまるっと別PDBへインポートしてMviewサイトを複製してみました。意外と簡単なんですよね。Mviewにハマりさえしなければ。
今回は前回同様の構成で、schemaモードでMviewを他のPDBへ複製するのですが、スキーマごとまるっとではなく、エクスポートするオプジェクトパスを必要最小限に絞ってMviewと関連するオブジェクトのみ複製できることを確認しておきます。

前回と同じポンチ絵ですが、
今回は、基本レプリケーション環境は構築済みの状態からスタートします。(つまり、Data Pumpでエクスポートするところから始めます)
細かい操作を確認したい方は、前々回前回のログを参照ください。

20170415_14044


Mviewの複製に必要な最小限のオブジェクトだけData Pumpで複製するために必要なオブジェクトパスはどれなのか確認しておきます。
前々回のエントリでほぼ見えてますが実際にやってみないと”不安”なので (^^;;;。

data pumpのschemaモード向けSCHEMA_EXPORT_OBJECTSにはオブジェクトパスの定義でMviewに関連するオブジェクト全てが定義されていることは何度か書きました。
また、前々回のexpdp/impdpの実行ログでどのようなオブジェクトがエクスポートされ、インポートされていたかも赤字で示しておきました。

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@orcl2 directory=workdir dumpfile=mview_schema1.dmp logfile=imp_mview_schema1.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MVIEW_SCHEMA1"."MV_MASTER" 5.5 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 15 00:39:00 2017 elapsed 0 00:00:23


インポートされたオブジェクト、ジョブ、そして、リフレッシュグループの情報から、schemaモードでMVIEWの複製に必要な必要最小限のオブジェクトパスは以下と推測しました。
なお、CONSTRAINTはTABLEに紐づくので自動的にエクスポートされるのであえて選んでいません。
(SCHEMA_EXPORT_OBJECTSビューより)

OBJECT_PATH                     COMMENTS
------------------------------- ------------------------------------------------------------
SCHEMA_EXPORT/DB_LINK Private database links in the selected schemas
SCHEMA_EXPORT/TABLE Tables in the selected schemas and their dependent objects
SCHEMA_EXPORT/MATERIALIZED_VIEW Materialized views
SCHEMA_EXPORT/JOB Jobs in the selected schemas
SCHEMA_EXPORT/REFRESH_GROUP Refresh groups in the selected schemas

上記オブジェクトパスをincludeパラメータに指定し、schemaモードで必要最小限のオブジェクトのみエクスポート可能か確認します。これがうまくできれば、成功することはほぼ確実!
PDB:ORCLにて、Data Pumpのschemaモードで必要最小限のオブジェクトのみexportするため以下のパラメータファイルを利用。
対象Mviewと関連オブジェクト以外はエクスポートしないようにするため対象Mviewのみに限定するようにしてあります。なお、パラメータファイルを利用している理由は、コマンドラインだとなんだかんだと面倒くさいData Pumpの癖を回避するためですw

[oracle@vbgeneric ˜]$ cat exp_mviewonly.par 
schemas=mview_schema1
include=DB_LINK
include=TABLE:"IN ('MV_MASTER')"
include=MATERIALIZED_VIEW:"IN ('MV_MASTER')"
include=JOB
include=REFRESH_GROUP


エクスポート!
ログを見る限り、想定通りのオブジェクトがエクスポートされています。オブジェクトにはユーザや権限を含めていないので、インポートする前にユーザ作成と必要最小限の権限を付与する必要があります。ログを見る限り狙い通りになっている模様です。

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@orcl directory=workdir dumpfile=exp_mviewonly.dmp logfile=exp_mviewonly.log parfile=exp_mviewonly.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
. . exported "MVIEW_SCHEMA1"."MV_MASTER" 5.5 KB 2 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/userhome/oracle/exp_mviewonly.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Apr 15 23:56:33 2017 elapsed 0 00:00:35


では、複製の開始!

ユーザはロールや権限など含めてエクスポートしていないのでData Pump作業用ディレクトリオブジェクトの作成に加え、ユーザ作成、必要最小限の権限付与をインポート前に行っています。

PDB:ORCL2にて

[oracle@vbgeneric ˜]$ sqlplus sys/oracle@orcl2 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 15 23:57:17 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

PDB:ORCL2にて、Data Pump向けディレクトリオブジェクト作成


orcl2@SYS> create directory workdir as '/u01/userhome/oracle';

Directory created.


PDB:ORCL2にて、Mviewと関連オブジェクトをインポートするユーザを作成と必要最小限の権限付与
このユーザ名はコピー元と同一ユーザ名にしてあります。異なる場合にはremap_schemaパラメータが必要になり一手間増えるので同一ユーザ名で作成してあります。

orcl2@SYS> create user mview_schema1 identified by welcome1
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users;

User created.

orcl2@SYS> grant create session, create table, create database link, create materialized view to mview_schema1;

Grant succeeded.


PDB:ORCL2にて、インポート。エクスポート時に必要オブジェクトのみにしてあるので単純にインポートだけ!
想定しているオブジェクトがインポートされているように見えますね!!!!

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@orcl2 directory=workdir dumpfile=exp_mviewonly.dmp logfile=imp_mviewonly.log
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MVIEW_SCHEMA1"."MV_MASTER" 5.5 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 15 23:58:25 2017 elapsed 0 00:00:04


PDB:ORCL2のMview向けユーザにて、インポート後のオブジェクトを確認
必要なオブジェクトは正しくインポートされており、停止状態でエクスポートされたリフレッシュジョブも停止状態であることが確認できます。

できてた!
この時点で、マスターサイトに対して、ORCLとORCL2という2つのMViewサイトができあがり!

[oracle@vbgeneric ˜]$ sqlplus mview_schema1/welcome1@orcl2

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 15 23:58:46 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

orcl2@MVIEW_SCHEMA1> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
------------------------------ -----------------------
MV_MASTER MATERIALIZED VIEW
SYS_C0014888 INDEX
MV_MASTER TABLE
TO_MASTER_SCHEMA DATABASE LINK

orcl2@MVIEW_SCHEMA1> @mview_info

Session altered.

MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE
------------------------------ ------ -------- -------- ------------------- -------------------
MV_MASTER DEMAND FAST COMPLETE UNDEFINED VALID

JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 4000/01/01 00:00:00 sysdate+5/1440 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');

ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE
-------------------- -------------------- ---------- ---------- - -------------------- -------------------
MVIEW_SCHEMA1 MV_MASTER 61 81 Y sysdate+5/1440 4000/01/01 00:00:00


最後に、高速リフレッシュが停止中である2つのMviewの高速リフレッシュの再開を確認しておきます。
schemaモードの単純な複製でも問題はなかったのでここでもジョブを再開するだけで、高速リフレッシュが再開されるはず。。。です。


事前に作成したContaners句を利用したスクリプト(mview_info_c.sql(前回のエントリの最後にあります))で確認します。
2つあるMviewサイトの高速リフレッシュジョブは停止中であることが確認できます。

CDB$RORRTのSYSユーザにて
LAST_REF列がCOMPELETEになっていますが、前回のエントリのとおりの紛らわしいステータスに設定されているだけで実際には完全リフレッシュされていません。勘違いのないように!

orcl12c@SYS> @mview_info_c
Connected
Session altered.

ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1 MV_MASTER 61 81 Y sysdate+5/1440 4000/01/01 00:00:00 5
MVIEW_SCHEMA1 MV_MASTER 61 81 Y sysdate+5/1440 4000/01/01 00:00:00 3

MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE
------------------------------ ------ -------- -------- ------------------- -------------------
MV_MASTER DEMAND FAST COMPLETE UNDEFINED VALID
MV_MASTER DEMAND FAST FAST UNDEFINED VALID

JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 4000/01/01 00:00:00 sysdate+5/1440 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/04/15 23:53:39 4000/01/01 00:00:00 sysdate+5/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');

リフレッシュジョブを再開すれば高速リフレッシュで同期されるようになります!!

orcl12c@SYS> conn mview_schema1/welcome1@orcl
Connected.
orcl@MVIEW_SCHEMA1> exec dbms_job.broken(job=>81,broken=>false,next_date=>sysdate);

PL/SQL procedure successfully completed.

orcl@MVIEW_SCHEMA1> conn mview_schema1/welcome1@orcl2
Connected.
orcl2@MVIEW_SCHEMA1> exec dbms_job.broken(job=>81,broken=>false,next_date=>sysdate);

PL/SQL procedure successfully completed.

orcl2@MVIEW_SCHEMA1> @mview_info_c
Connected.
Session altered.

ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+5/1440 2017/04/16 00:08:24 3
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+5/1440 2017/04/16 00:07:19 5

MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE BUILD_MOD
------------------------------ ------ -------- -------- ------------------- ------------------- ---------
MV_MASTER DEMAND FAST FAST UNDEFINED VALID IMMEDIATE
MV_MASTER DEMAND FAST FAST UNDEFINED VALID IMMEDIATE

JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/04/16 00:02:19 2017/04/16 00:07:19 sysdate+5/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/04/16 00:03:24 2017/04/16 00:08:24 sysdate+5/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');


本文中で利用していたスクリプト: mview_info.sql

alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
col mview_name for a30
select mview_name,refresh_mode,refresh_method,last_refresh_type,after_fast_refresh,compile_state from user_mviews;

col log_user for a20
col schema_user for a20
col interval for a20
col what for a60
select job,log_user,schema_user,last_date,next_date,interval,failures,what from user_jobs;

col rowner for a20
col rname for a20
select rowner,rname,refgroup,job,broken,interval,next_date from user_refresh;


ということで、次回、癖モノシリーズ最終回?へ




Data Pumpも癖モノだよね〜w その1 - queryパラメーターの解析タイミング
Data Pumpも癖モノだよね〜w その2 - Materialized ViewをTableとして移行する
Data Pumpも癖モノだよね〜w その3 - dbms_job と dbms_scheduler との複雑な関係
Data Pumpも癖モノだよね〜w その4 - schemaモードでMviewを他のPDBへ複製
Data Pumpも癖モノだよね〜w その4と1/2 - schemaモードでMviewを他のPDBへ複製 (紛らわしいステータスw)

| | コメント (0) | トラックバック (0)

2017年4月23日 (日)

Data Pumpも癖モノだよね〜w その4と1/2 - schemaモードでMviewを他のPDBへ複製 (紛らわしいステータスw)

Previously on Mac De Oracle

前回は、Data PumpのschemaモードでschemaごとまるっとMviewサイトを複製してみたところまで、でした。

エントリには記載していなかったログを見ていて驚いた!ことを調査してみる回として”その4と1/2”にしてみましたw

ほんと、癖モノですよねーMviewも!w



なに驚いたのか、その内容から

Data Pumpを利用しMviewを複製する今回の目的の一つである、完全リフレッシュしないでMviewを複製するという目論見が外れていた!!!?

と思われるログが残っていました。

以下のログは、Data Pumpのインポートログとその後のMview関連ビューをリストしたものですが、赤字部分に注目

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@orcl2 directory=workdir dumpfile=mview_schema1.dmp logfile=imp_mview_schema1.dmp 
Processing object type SCHEMA_EXPORT/USER

・・・中略・・・

Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MVIEW_SCHEMA1"."MV_MASTER" 5.5 KB 2 rows

・・・中略・・・

Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sun Apr 23 20:30:19 2017 elapsed 0 00:00:28

インポート直後、2つのMviewの状態を確認したログですが、B列(DBA_REFRESH.BROKEN列)はどちらも”N"となっておりリフレッシュジョブは停止中です。問題は下のLAST_REF列(DBA_MVIEWS.LAST_REFRESH_TYPE列)がCOMPLETEとなっているというところです。
CON_ID=5は、PDB:ORCL2なのでインポートを行ったPDBを指します。DBA_MVIEWS.LAST_REFRESH_TYPE列は、「最新のリフレッシュに使用されるメソッド:COMPLETE- 最新のリフレッシュが完了した。」と説明されています。??
完全リフレッシュされちゃったの??? 

こうなったらData PumpでMviewが作成された時に完全リフレッシュされちゃうのか、されないのか確認するしかありませんねw。

ROWNER               RNAME                  REFGROUP        JOB B INTERVAL             NEXT_DATE               CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+5/1440 2017/04/23 20:38:31 5
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+5/1440 2017/04/23 20:43:30 3

MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE CON_ID
------------------------------ ------ -------- -------- ------------------- ------------------- ----------
MV_MASTER DEMAND FAST COMPLETE UNDEFINED VALID 5
MV_MASTER DEMAND FAST FAST UNDEFINED VALID 3

ということで、確認してみました。

最初は、前回と同じPDB:ORCLのスキーマ間で基本レプリケーション環境を作成し、リフレッシュジョブを停止するところまで(何をやっているかはDDLやSQL文を見ればわかると思うので略。わからないと言う方は前回の内容で確認ください。)

orcl12c@SYS> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCL READ WRITE NO
4 ORDS READ WRITE NO
5 ORCL2 READ WRITE NO

orcl@SYS> create directory workdir as '/u01/userhome/oracle';

Directory created.

orcl@SYS> create user master_schema identified by welcome1
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users;

User created.

orcl@SYS> grant create session, create table to master_schema;

Grant succeeded.

orcl@SYS> create user mview_schema1 identified by welcome1
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users;

User created.

orcl@SYS> grant create session, create table, create database link, create materialized view to mview_schema1;

Grant succeeded.

orcl@SYS> conn master_schema/welcome1@orcl
Connected.
orcl@MASTER_SCHEMA> create table master (
2 id number primary key
3 ,foo varchar2(100));

Table created.

orcl@MASTER_SCHEMA> insert into master values(1,'foo');

1 row created.

orcl@MASTER_SCHEMA> insert into master values(2,'bar');

1 row created.

orcl@MASTER_SCHEMA> commit;

Commit complete.

orcl@MASTER_SCHEMA> create materialized view log on master;

Materialized view log created.

orcl@MASTER_SCHEMA> conn mview_schema1/welcome1@orcl
Connected.
orcl@MVIEW_SCHEMA1> create database link to_master_schema
2 connect to master_schema identified by welcome1
3 using 'ORCL';

Database link created.

orcl@MVIEW_SCHEMA1> select count(*) from master@to_master_schema;

COUNT(*)
----------
2

orcl@MVIEW_SCHEMA1> create materialized view mv_master
2 refresh fast on demand
3 start with sysdate next sysdate+5/1440
4 as select * from master@to_master_schema;

Materialized view created.

orcl@MVIEW_SCHEMA1> select count(1) from mv_master;

COUNT(1)
----------
2

orcl@MVIEW_SCHEMA1> @mview_info_c
Connected.

Session altered.

ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+5/1440 2017/04/23 20:12:06 3

MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE
------------------------------ ------ -------- -------- ------------------- -------------------
MV_MASTER DEMAND FAST FAST UNDEFINED VALID

JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/04/23 20:07:06 2017/04/23 20:12:06 sysdate+5/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');

orcl12c@SYS> conn mview_schema1/welcome1@orcl
Connected.
orcl@MVIEW_SCHEMA1> exec dbms_job.broken(job=>81,broken=>true);

PL/SQL procedure successfully completed.

orcl@MVIEW_SCHEMA1> @mview_info_c

Session altered.

ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1 MV_MASTER 61 81 Y sysdate+5/1440 4000/01/01 00:00:00 3

MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE CON_ID
------------------------------ ------ -------- -------- ------------------- ------------------- ----------
MV_MASTER DEMAND FAST FAST UNDEFINED VALID 3

JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT    CON_ID
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------ ----------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/04/23 20:07:06 4000/01/01 00:00:00 sysdate+5/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"'); 3


ここまでは同じなのですが、Data PumpでMviewをインポートする際に完全リフレッシュしているのか?どうかを確認するため、リフレッシュジョブ停止後、マスター表を100行に増幅しておきます。
こうしておけば、Data PumpでMviewがインポートされる際、完全リフレッシュが行われていたとしたら、インポート後のMviewは100行になっているはずだし、
 逆に2行のままなら、LAST_REFRESH_TYPE=COMPLETEにはなっているものの実際には完全リフレッシュしなていない、という、なんともわかりづらい状況になるから気にすんな、ってことが見えてくるはず。

orcl12c@SYS> conn master_schema/welcome1@orcl
Connected.
orcl@MASTER_SCHEMA> begin for i in 3..100 loop insert into master values(i,'data#'||i); end loop; end;
2 /

PL/SQL procedure successfully completed.

orcl@MASTER_SCHEMA> commit;

Commit complete.

orcl@MASTER_SCHEMA> select count(1) from master;

COUNT(1)
----------
100

orcl@MASTER_SCHEMA> conn mview_schema1/welcome1@orcl
Connected.
orcl@MVIEW_SCHEMA1> select count(1) from mv_master;

COUNT(1)
----------
2

エクスポートは2行となっているので、Mviewから2行エクスポートされているのは間違いない!(マスター表は100行!)

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@orcl directory=workdir dumpfile=mview_schema1.dmp logfile=exp_mview_schema1.log schemas=mview_schema1
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
. . exported "MVIEW_SCHEMA1"."MV_MASTER" 5.5 KB 2 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/userhome/oracle/mview_schema1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Apr 23 20:28:25 2017 elapsed 0 00:00:54

PDB:ORCL2にて、Data Pump作業向けディレクトリオブジェクトを作成

[oracle@vbgeneric oracle]$ sqlplus sys/oracle@orcl2 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 23 20:29:07 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

orcl2@SYS> create directory workdir as '/u01/userhome/oracle';

Directory created.


インポートでも見ての通り、2行だけインポートされています!(ステータスが紛らわしい説が強くなってきました!!!)

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@orcl2 directory=workdir dumpfile=mview_schema1.dmp logfile=imp_mview_schema1.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MVIEW_SCHEMA1"."MV_MASTER" 5.5 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sun Apr 23 20:30:19 2017 elapsed 0 00:00:28


インポート後、どちらのリフレッシュジョブも停止中ですが、1箇所違いがみられます。
そう、インポートしたMviewのLAST_REF(DBA_MVIEWS.LAST_REFRESH_TYPE)がCOMPLETEになっています。前半に書いたとおりの状態が再現!!! なにっ!!

orcl12c@SYS> @mview_info_c
Connected.

Session altered.

ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1 MV_MASTER 61 81 Y sysdate+5/1440 4000/01/01 00:00:00 3
MVIEW_SCHEMA1 MV_MASTER 61 81 Y sysdate+5/1440 4000/01/01 00:00:00 5

MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE CON_ID
------------------------------ ------ -------- -------- ------------------- ------------------- ----------
MV_MASTER DEMAND FAST FAST UNDEFINED VALID 3
MV_MASTER DEMAND FAST COMPLETE UNDEFINED VALID 5

JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT CON_ID
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------ ----------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/04/23 20:07:06 4000/01/01 00:00:00 sysdate+5/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"'); 3
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 4000/01/01 00:00:00 sysdate+5/1440 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"'); 5

PDB_NAME CON_ID
------------------------------ ----------
PDB$SEED 2
ORCL 3
ORDS 4
ORCL2 5

DBA_MVIEWSビューのLAST_REFRESH_TYPEはCOMPLETE(完全リフレッシュされた)と設定されていますが、本当なのでしょうか? PDB:ORCL2のMV_MASTER表が本当に完全リフレッシュされていたとしたら100行になっているはずですが。。。。

PDB:ORCLのMViewの行数をカウント、2行です(リフレッシュジョブが停止しているので変化なし)

orcl12c@SYS> conn mview_schema1/welcome1@orcl
Connected.
orcl@MVIEW_SCHEMA1> show con_id

CON_ID
------------------------------
3

orcl@MVIEW_SCHEMA1> show con_name

CON_NAME
------------------------------
ORCL

orcl@MVIEW_SCHEMA1> select count(1) from mv_master;

COUNT(1)
----------
2


さて、問題のPDB:ORCL2。。。。2行のまま!!!!! 
ということは、Data PumpによるMviewのインポートでは完全リフレッシュは行われず、エクスポートされた行数がそのままインポートされている!! ステータスが紛らわしい!! だけというのが真相のようです!

orcl@MVIEW_SCHEMA1> conn mview_schema1/welcome1@orcl2
Connected.
orcl2@MVIEW_SCHEMA1> show con_id

CON_ID
------------------------------
5

orcl2@MVIEW_SCHEMA1> show con_name

CON_NAME
------------------------------
ORCL2

orcl2@MVIEW_SCHEMA1> select count(1) from mv_master;

COUNT(1)
----------
2


ジョブを再開すると。。。マスターサイトと同期され各MViewは100行なっています。めでたしめでたし。

@> conn mview_schema1/welcome1@orcl
Connected.
orcl@MVIEW_SCHEMA1> exec dbms_job.broken(job=>81,broken=>false,next_date=>sysdate);

PL/SQL procedure successfully completed.

orcl@MVIEW_SCHEMA1> conn mview_schema1/welcome1@orcl2
Connected.
orcl2@MVIEW_SCHEMA1> exec dbms_job.broken(job=>81,broken=>false,next_date=>sysdate);

PL/SQL procedure successfully completed.

orcl12c@SYS> @mview_info_c
Connected.

Session altered.

ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+5/1440 2017/04/23 20:44:05 5
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+5/1440 2017/04/23 20:43:30 3

MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE CON_ID
------------------------------ ------ -------- -------- ------------------- ------------------- ----------
MV_MASTER DEMAND FAST FAST UNDEFINED VALID 3
MV_MASTER DEMAND FAST FAST UNDEFINED VALID 5

JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT CON_ID
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------ ----------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/04/23 20:38:30 2017/04/23 20:43:30 sysdate+5/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"'); 3
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/04/23 20:39:05 2017/04/23 20:44:05 sysdate+5/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"'); 5

orcl12c@SYS> conn mview_schema1/welcome1@orcl
Connected.
orcl@MVIEW_SCHEMA1> select count(1) from mv_master;

COUNT(1)
----------
100

orcl@MVIEW_SCHEMA1> conn mview_schema1/welcome1@orcl2
Connected.
orcl2@MVIEW_SCHEMA1> select count(1) from mv_master;

COUNT(1)
----------
100

やれやれ、お騒がせすぎるw 

次回へつづく。


参考:ログ中で利用したSQLスクリプト(mview_info_c.sql)のソース

conn sys/oracle@orcl12c as sysdba

col rowner for a20
col rname for a20
col mview_name for a30
col log_user for a20
col schema_user for a20
col interval for a20
col what for a60
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
select rowner,rname,refgroup,job,broken,interval,next_date,con_id from containers(dba_refresh) where rowner='MVIEW_SCHEMA1';
select mview_name,refresh_mode,refresh_method,last_refresh_type,after_fast_refresh,compile_state from containers(dba_mviews) where mview_name='MV_MASTER';
select job,log_user,schema_user,last_date,next_date,interval,failures,what from containers(dba_jobs) where log_user='MVIEW_SCHEMA1';




Data Pumpも癖モノだよね〜w その1 - queryパラメーターの解析タイミング
Data Pumpも癖モノだよね〜w その2 - Materialized ViewをTableとして移行する
Data Pumpも癖モノだよね〜w その3 - dbms_job と dbms_scheduler との複雑な関係
Data Pumpも癖モノだよね〜w その4 - schemaモードでMviewを他のPDBへ複製

| | コメント (0) | トラックバック (0)

2017年4月22日 (土)

Data Pumpも癖モノだよね〜w その4 - schemaモードでMviewを他のPDBへ複製

一回、おやすみしましたが、Previously on Mac De Oracle

前々回は、Data Pump、dbms_jobとdbms_schedulerの複雑な関係を紐解いてみました。

今回は、箸休めとして、schemaモードより上位のモードならMviewはMviewのまま複製や移行することは簡単に行えるよね。という確認だけしておこうと思います。
シンプルだと思わせておいて、細けーことやろうとすると一癖ある、まさに癖モノw ということで :)


まずは、おさらいから

data pumpのschemaモード向けSCHEMA_EXPORT_OBJECTSにはオブジェクトパスの定義でMviewに関連するオブジェクト全てが定義されています。

OBJECT_PATH                     COMMENTS
------------------------------- ------------------------------------------------------------
SCHEMA_EXPORT/DB_LINK Private database links in the selected schemas
SCHEMA_EXPORT/TABLE Tables in the selected schemas and their dependent objects
SCHEMA_EXPORT/MATERIALIZED_VIEW Materialized views
SCHEMA_EXPORT/JOB Jobs in the selected schemas
SCHEMA_EXPORT/REFRESH_GROUP Refresh groups in the selected schemas

一方、tableモード向けTABLE_EXPORT_OBJECTSには、Mview、リフレッシュに必要なJOBやREFRESH GROUPなどのオブジェクトパスが定義されていません。
tableモードでMviewを複製しようとすること自体に無理があるのは明らかですね。MViewとしてエクスポートしたくてもオプジェクトパスが定義されていないのですから。
表を対象としているモードだからそれ以外のオブジェクトパスが定義されていないんだよね〜と、無理やり納得しています:)

orcl@SYSTEM> r
1 select * from TABLE_EXPORT_OBJECTS where
2 object_path like '%/JOB'
3 or object_path like '%/MATERIALIZED_VIEW'
4* or object_path like '%/REFRESH_GROUP'

no rows selected

おさらいはこれくらいにして、schemaモードエクスポートで以下の図に示したようなMviewの複製が行えるか確認しておきます。

ポンチ絵のとおりではあるのですが、簡単に説明すると、
PDB:ORCLの異なるschema間で高速リフレッシュ可能なMviewがあり、Data Pumpのschemaモードを利用して異なるPDBにMviewを複製するというシナリオです。
複製後はMviewサイトが2つになります。

20170415_14044


なぜ、異なるDB(PDB)にMVIEWを複製するシナリオにしたかって?
理由は、DBMS_JOBのジョブは、同一データベース(MTA構成であればPDB毎)でJOB番号により一意に管理されています。
DBMS_JOBのジョブを同一データベース内でexport/importした場合、ジョブが単純に複製される事になりJOB番号の一意制約エラーとなりimportに失敗します。
importできなければ再作成すれば問題ないわけですが、MTA環境なのでわざわざ同一PDB内に作成する必要はないわけです。(手数を減らせるならその方が楽ですから)

エクスポートする前にリフレッシュジョブを一時停止する理由は?
一時停止している理由は静止点を作りたいこともありますが、それをサボると、なかなか理解しにくいタイミングイシューと言われてる事象に遭遇しやすくなるんですよ。(感覚的に)
前述の状況になると高速リフレッシュを再開するには、一度、完全リフレッシュする必要があります。
ここで利用するMviewは2行しかないので完全リフレッシュは苦でもないですが、巨大サイズの表だったら完全リフレッシュはできることなら避けたいですよね。


PDB:ORCLの異なるschema間でmaster表→mv_master表で基本レプリケーション環境作成からData PumpでOmv_master表のオーナースキーマごとPDB:ORCL2へ複製までをつらつらと記録してあります。:)

MTA環境となっています。PDB:ORCL内で基本レプリケーション環境を作り、PDB:ORCL2にDBリンクやMVIEW等関連オブジェクトをData Pumpを利用して複製します。

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

orcl12c@SYS> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCL READ WRITE NO
4 ORDS READ WRITE NO
5 ORCL2 READ WRITE NO


PDB:ORCLデータベースにディレクトリオブジェクトを作成します。(Data Pump向け作業ディレクトリ)

orcl@SYS> create directory workdir as '/u01/userhome/oracle';

Directory created.

PDB:ORCLデータベースにマスターサイト向けユーザを作成し、必要最低限の権限を付与

orcl@SYS> create user master_schema identified by welcome1
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users;

User created.

orcl@SYS> grant create session, create table to master_schema;

Grant succeeded.

PDB:ORCL、作成したユーザに接続し、マスター表を作成

orcl@SYS> conn master_schema/welcome1@orcl
Connected.
orcl@MASTER_SCHEMA> create table master (
2 id number primary key
3 ,foo varchar2(100));

Table created.

orcl@MASTER_SCHEMA> insert into master values(1,'foo');

1 row created.

orcl@MASTER_SCHEMA> insert into master values(2,'bar');

1 row created.

orcl@MASTER_SCHEMA> commit;

Commit complete.


PDB:ORCL、高速リフレッシュに必要なMaterialized View LogをMaster表に作成します。

orcl@MASTER_SCHEMA> create materialized view log on master;

Materialized view log created.


同じくPDB:ORCLデータベースにMviewサイト向けユーザを作成し、必要最低限の権限を付与します。

orcl@SYS> create user mview_schema1 identified by welcome1
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users;

User created.

orcl@SYS> grant create session, create table, create database link, create materialized view to mview_schema1;

Grant succeeded.


PDB:ORCL、Mviewサイト向けに作成したユーザに接続し、マスターサイトへのデータベースリンクとMaterialized Viewを作成します。

orcl@SYS> conn mview_schema1/welcome1@orcl
Connected.
orcl@MVIEW_SCHEMA1> create database link to_master_schema
2 connect to master_schema identified by welcome1
3 using 'ORCL';

Database link created.

orcl@MVIEW_SCHEMA1> select count(*) from master@to_master_schema;

COUNT(*)
----------
2

orcl@MVIEW_SCHEMA1> create materialized view mv_master
2 refresh fast on demand
3 start with sysdate next sysdate+5/1440
4 as select * from master@to_master_schema;

Materialized view created.


PDB:ORCL、MViewサイトのオブジェクト確認からDBMS_JOBとリフレッシュグループの高速リフレッシュされているところまでを確認しています。

Mviewを作成すると、作成したMviewは、TABLEでもあり、MVIEWでもある。ということが確認できます。これ重要ですよ!
data pumpのschemaモード向けSCHEMA_EXPORT_OBJECTSビューにTABLEとMATERIALIZED_VIEWの2つのオブジェクトパスがあるにも関わらず、
tableモードのTABLE_EXPORT_OBJECTSビューにはTABLEオブジェクトパスは定義されているのにMATERIALIZED_VIEWオブジェクトパスされていないからMVIEWとしてはエクポートできないと言っていた理由なんですよ!!!!

orcl@MVIEW_SCHEMA1> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
------------------------------ -----------------------
MV_MASTER TABLE
SYS_C0014880 INDEX
MV_MASTER MATERIALIZED VIEW
TO_MASTER_SCHEMA DATABASE LINK

次はuser_jobsからリフレッシュジョブを確認しておきます。これもMviewをリフレッシュするのに必要なオブジェクトです。
data pumpのschemaモード向けSCHEMA_EXPORT_OBJECTSビューでJOBオブジェクトパスとして定義されています。これも重要なんです。
前回JOBオブジェクトパスに対応するオブジェクトは、DBMS_JOBのJOBだということを書きましたが、思い出していただけましたか? 

orcl@MVIEW_SCHEMA1> select job,log_user,schema_user,last_date,next_date,interval,failures,what from user_jobs;

JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/04/15 00:09:04 2017/04/15 00:14:04 sysdate+5/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');

最後にリフレッシュグループの確認、意外とこれの存在を忘れてしまうんですよ。私もそうでした。
単一MVIEWのリフレッシュでもリフレッシュグループが自動的に作成されてしまうことを...複数のMVIEWをリフレッシュする時だけに必要なのかと思ったら大間違いw
いつも、つい、忘れちゃいますw 影薄過ぎ! ですが、前述のWHAT列の内容を見れば、なるほど! となるはずです。
dbms_refresh.refreshプロシージャは、リフレッシュグループ名が引数!!

orcl@MVIEW_SCHEMA1> select rowner,rname,refgroup,job,broken,interval,next_date from user_refresh;

ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE
-------------------- -------------------- ---------- ---------- - -------------------- -------------------
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+5/1440 2017/04/15 00:29:29


PDB:ORCL、リフレッシュジョブを一時停止し、expdpのschemaモードでMviewサイト向けスキーマを丸ごとエクスポートします。

orcl@MVIEW_SCHEMA1> exec dbms_job.broken(job=>81,broken=>true);

PL/SQL procedure successfully completed.

orcl@MVIEW_SCHEMA1> select rowner,rname,refgroup,job,broken,interval,next_date from user_refresh;

ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE
-------------------- -------------------- ---------- ---------- - -------------------- -------------------
MVIEW_SCHEMA1 MV_MASTER 61 81 Y sysdate+5/1440 4000/01/01 00:00:00


Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@orcl directory=workdir dumpfile=mview_schema1.dmp logfile=exp_mview_schema1.log schemas=mview_schema1
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
. . exported "MVIEW_SCHEMA1"."MV_MASTER" 5.5 KB 2 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/userhome/oracle/mview_schema1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Apr 15 00:30:55 2017 elapsed 0 00:00:55


無事エクスポートできたようなので、次は、
PDB:ORCL2データベースで、ディレクトリオブジェクトを作成します。2つめのMviewサイトの準備です。

orcl2@SYSTEM> create directory workdir as '/u01/userhome/oracle';

Directory created.


PDB:ORCL2、schemaモードでインポート。(対象PDB:ORCL2には同一スキーマは存在しない状態で実施)

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@orcl2 directory=workdir dumpfile=mview_schema1.dmp logfile=imp_mview_schema1.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MVIEW_SCHEMA1"."MV_MASTER" 5.5 KB 2 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/JOB
Processing object type SCHEMA_EXPORT/REFRESH_GROUP
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sat Apr 15 00:39:00 2017 elapsed 0 00:00:23

と、すんなり終了w

PDB:ORCL2、インポートで複製されたMVIEW_SCHEMA1ユーザに接続しオブジェクトを確認
リフレッシュジョブは、エクスポート元と同一ジョブ番号でインポートされ、停止状態。想定通りで一安心:)

orcl2@MVIEW_SCHEMA1> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
------------------------------ -----------------------
MV_MASTER TABLE
SYS_C0014884 INDEX
MV_MASTER MATERIALIZED VIEW
TO_MASTER_SCHEMA DATABASE LINK

orcl2@MVIEW_SCHEMA1> select job,log_user,schema_user,last_date,next_date,interval,failures,what from user_jobs;

JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 4000/01/01 00:00:00 sysdate+5/1440 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');

orcl2@MVIEW_SCHEMA1> select rowner,rname,refgroup,job,broken,interval,next_date from user_refresh;

ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE
-------------------- -------------------- ---------- ---------- - -------------------- -------------------
MVIEW_SCHEMA1 MV_MASTER 61 81 Y sysdate+5/1440 4000/01/01 00:00:00


PDB:ORCLとORCL2、両MViewサイトのリフレッシュジョブを再開

orcl2@MVIEW_SCHEMA1> conn mview_schema1/welcome1@orcl2
Connected.
orcl2@MVIEW_SCHEMA1> exec dbms_job.broken(job=>81,broken=>false,next_date=>sysdate);

PL/SQL procedure successfully completed.

orcl2@MVIEW_SCHEMA1> conn mview_schema1/welcome1@orcl
Connected.
orcl@MVIEW_SCHEMA1> exec dbms_job.broken(job=>81,broken=>false,next_date=>sysdate);

PL/SQL procedure successfully completed.

最後に、containers句を使って全体を確認っと!

orcl2@MVIEW_SCHEMA1> conn sys/oracle@orcl12c as sysdba
Connected.
orcl12c@SYS> select rowner,rname,refgroup,job,broken,interval,next_date,con_id from containers(dba_refresh) where rowner='MVIEW_SCHEMA1';

ROWNER RNAME REFGROUP JOB B INTERVAL NEXT_DATE CON_ID
-------------------- -------------------- ---------- ---------- - -------------------- ------------------- ----------
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+5/1440 2017/04/15 22:25:47 5
MVIEW_SCHEMA1 MV_MASTER 61 81 N sysdate+5/1440 2017/04/15 22:26:52 3

orcl12c@SYS> select mview_name,refresh_mode,refresh_method,last_refresh_type,after_fast_refresh,compile_state from containers(dba_mviews) where mview_name='MV_MASTER';
MVIEW_NAME REFRES REFRESH_ LAST_REF AFTER_FAST_REFRESH COMPILE_STATE
------------------------------ ------ -------- -------- ------------------- -------------------
MV_MASTER DEMAND FAST FAST UNDEFINED VALID
MV_MASTER DEMAND FAST FAST UNDEFINED VALID

orcl12c@SYS> select job,log_user,schema_user,last_date,next_date,interval,failures,what from containers(dba_jobs) where log_user='MVIEW_SCHEMA1';
JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/04/15 22:20:47 2017/04/15 22:25:47 sysdate+5/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');
81 MVIEW_SCHEMA1 MVIEW_SCHEMA1 2017/04/15 22:21:52 2017/04/15 22:26:52 sysdate+5/1440 0 dbms_refresh.refresh('"MVIEW_SCHEMA1"."MV_MASTER"');

何事もなく複製できたところで、次回へ続く。

あ、その前に、環境を初期状態に戻さないと。。


Data Pumpも癖モノだよね〜w その1 - queryパラメーターの解析タイミング
Data Pumpも癖モノだよね〜w その2 - Materialized ViewをTableとして移行する
Data Pumpも癖モノだよね〜w その3 - dbms_job と dbms_scheduler との複雑な関係

| | コメント (0) | トラックバック (0)

2017年4月10日 (月)

Data Pumpも癖モノだよね〜w その3 - dbms_job と dbms_scheduler との複雑な関係

Previously on Mac De Oracle

前回は、Data PumpとMaterialized Viewという癖モノ二大巨頭を絡ませて見ました。

今回は、三つ巴?な感じでお送りしたいと思います。:)



以下は、Materialized ViewをリフレッシュするDBMS_JOBです。

今頃気づいたか! という感じですが、dbms_job で作成されるJOBは、ALL/DBA/USER_OBJECTSには含まれないのです!!

orcl@USERS> select job,log_user,schema_user,last_date,next_date,interval,failures,what from user_jobs;

JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
21 USERS USERS 2017/04/09 19:18:34 2017/04/09 19:19:34 sysdate+1/1440 0 dbms_refresh.refresh('"USERS"."MVIEW_MASTER"');

orcl@USERS> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
------------------------------ -----------------------
MVIEW_MASTER MATERIALIZED VIEW
SYS_C0014637 INDEX
MVIEW_MASTER TABLE
TOUSERM DATABASE LINK


ここで、注目!!
dbms_jobは、dba_objectsにはカウントされませんが、dbms_schedulerは、object_type=JOBとしてdba_objectsにカウントされるということ!!
以下にある、OBJECT_TYPE=JOBは、OBJECT_NAME=TESTという直前に作成されたdbms_schedulerのJOBだということ!!

ORCL@USERS> begin
dbms_scheduler.create_job (
job_name=>'test'
,job_type=>'PLSQL_BLOCK'
,job_action=>'BEGIN dbms_refresh.refresh(''USERS.MVIEW_MASTER''); END;'
,start_date=>systimestamp
,repeat_interval=>'FREQ=MONTHLY'
,end_date=>systimestamp + interval '1' year
,enabled=>true
,comments=>null
);
end;
/

PL/SQL procedure successfully completed.


なんとまぁ、面倒くさい、DBMS_JOBのJOBも、DBMS_SCHEDULERのJOBも、同じ初期化パラメータ(job_queue_processes)を利用するのにオブジェクト扱いされたりされなかったり、面倒くさい癖モノです。注意しましょうね!!

Oracle® Databaseリファレンス 12c リリース1 (12.1) 1.126 JOB_QUEUE_PROCESSES
https://docs.oracle.com/cd/E57425_01/121/REFRN/GUID-B8B68D16-00A3-43DD-BE39-01F877880955.htm

ORCL@USERS> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE
------------------------------ -----------------------
MVIEW_MASTER TABLE
SYS_C0014637 INDEX
MVIEW_MASTER MATERIALIZED VIEW
TEST JOB
TOUSERM DATABASE LINK

という準備運動が終わったところで、本題の Data Pumpでのお話です。
癖モノData Pumpと癖モノ感たっぷりの2種類のJOB、2時間ドラマの複雑でドロドロした関係が予想される展開になってきましたw

orcl@USERS> select job,log_user,schema_user,last_date,next_date,interval,failures,what from user_jobs;

JOB LOG_USER SCHEMA_USER LAST_DATE NEXT_DATE INTERVAL FAILURES WHAT
---------- -------------------- -------------------- ------------------- ------------------- -------------------- ---------- ------------------------------------------------------------
21 USERS USERS 2017/04/09 19:18:34 2017/04/09 19:19:34 sysdate+1/1440 0 dbms_refresh.refresh('"USERS"."MVIEW_MASTER"');

orcl@USERS> select job_name,job_action,repeat_interval,start_date from user_scheduler_jobs;

JOB_NAME JOB_ACTION REPEAT_INTERVAL START_DATE
---------- ------------------------------------------------------------------- -------------------- ----------------------------------------
TEST BEGIN dbms_refresh.refresh('USERS.MVIEW_MASTER'); END; FREQ=MONTHLY 09-APR-17 10.36.05.814317 PM +09:00


まず、Data Pumpで扱える"OBJECT”が定義されているSCHEMA_EXPORT_OBJECTSをみると以下の"OBJECT”定義が見つかります。
JOBというオブジェクトのコメントを読んでも、2種類あるJOBのどちらを指しているのか、はたまた、いずれか一つなのかさっぱりわかりません。

OBJECT_PATH                     COMMENTS
------------------------------- ------------------------------------------------------------
SCHEMA_EXPORT/DB_LINK Private database links in the selected schemas
SCHEMA_EXPORT/TABLE Tables in the selected schemas and their dependent objects
SCHEMA_EXPORT/MATERIALIZED_VIEW Materialized views
SCHEMA_EXPORT/JOB Jobs in the selected schemas
SCHEMA_EXPORT/REFRESH_GROUP Refresh groups in the selected schemas

これは試して、ガッテン!! するしかありません!!!

schemaモードでエクスポートします。このとき、includeパラメータでJOBだけをエクスポートするよう指定します!! 

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@orcl directory=homedir dumpfile=jobs.dmp logfile=jobsexp.log schemas=users include=job
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/JOB
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/jobs.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Apr 9 23:13:47 2017 elapsed 0 00:00:04

SQLFILEパラメータを指定して内容を確認してみます。(SQLFILEパラメータを指定したimpdpコマンドでは実際にインポートは行われません)

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01": system/********@orcl directory=homedir dumpfile=jobs.dmp logfile=jobsexp.log schemas=users sqlfile=jobsexp_ddl.txt
Processing object type SCHEMA_EXPORT/JOB
Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Sun Apr 9 23:20:44 2017 elapsed 0 00:00:03


おおおおお、これは!!! 

なんということでしょう!
Data Pumpでは、JOBオブジェクトは、DBMS_JOBのJOBオブジェクト(dba_objectsではオブジェクト扱いされていないのに!!)

だとすると、dba_objectsでは”OBJECT"として扱われている、DBMS_SCHEDULERのJOBはの扱いはいかに。。。

以下のとおり、DBMS_IJOBと内部的プロリージャに置き換わっていますが、パラメータを見れば一目瞭然、DBMS_JOBの定義しかありません。
つまり、Data PumpのJOBオブジェクトは、DBMS_JOBのJOBであることがわかりました!!

[oracle@catfish ˜]$ cat jobsexp_ddl.txt 

・・・中略・・・

-- new object type path: SCHEMA_EXPORT/JOB
BEGIN SYS.DBMS_IJOB.SUBMIT(
JOB=> 21,
LUSER=> 'USERS',
PUSER=> 'USERS',
CUSER=> 'USERS',
NEXT_DATE=> TO_DATE('2017-04-09 23:13:51', 'YYYY-MM-DD:HH24:MI:SS'),
INTERVAL=> 'sysdate+1/1440',
BROKEN=> FALSE,
WHAT=> 'dbms_refresh.refresh(''"USERS"."MVIEW_MASTER"'');',

・・・中略・・・

では、DBMS_SCHEDULERのJOBは。。。それは。。。PROCOBJというOBJECT_PATHに含まれているようで(ほぼ誰にも読み取れないw)。。。

MOSにもありそうだけど、この辺りをまとめてて疲れて、めんどくさい病の発作がw 
もっと深掘りする気力があったら探すかもw
Export/Import Scheduler Jobs


手取り早く、試して、ガッテン! includeパラメータで”PROCOBJ”を指定します。

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@orcl directory=homedir dumpfile=scheduler_jobs.dmp logfile=scheduler_jobsexp.log schemas=users include=procobj
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/scheduler_jobs.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Apr 9 23:49:20 2017 elapsed 0 00:00:04

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01": system/********@orcl directory=homedir dumpfile=scheduler_jobs.dmp logfile=jobsddldump.log schemas=users sqlfile=jobsexp2_ddl.txt
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at Sun Apr 9 23:50:05 2017 elapsed 0 00:00:02


確かに、PROCOBJにDBMS_SCHEDULERのJOBが含まれている!!

[oracle@catfish ˜]$ cat jobsexp2_ddl.txt

・・・中略・・・

BEGIN
dbms_scheduler.create_job('"TEST"',
job_type=>'PLSQL_BLOCK'
, job_action=>'BEGIN dbms_refresh.refresh(''USERS.MVIEW_MASTER''); END;'
, number_of_arguments=>0
, start_date=>TO_TIMESTAMP_TZ('09-APR-2017 10.36.05.814317000 PM +09:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english')
, repeat_interval=> 'FREQ=MONTHLY'
, end_date=>TO_TIMESTAMP_TZ('09-APR-2018 10.36.05.814344000 PM +09:00','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english')
, job_class=>'"DEFAULT_JOB_CLASS"'
, enabled=>FALSE
, auto_drop=>TRUE
,comments=>NULL
);
dbms_scheduler.enable('"TEST"');
COMMIT;
END;
/

・・・中略・・・

登場人物の関係が複雑すぎてよくわからなくなってきたので、まとめ。

DBMS_JOBのJOBは、DBA_OBJECTS上、オブジェクトとは扱われていない。
DBMS_SCHEDULEのJOBは、DBA_OBJECTS上、オブジェクトとして扱われている。
どちらのJOBも、初期化パラメータ、job_queue_processesにより制御されている。
Data PumpのSCHEMA_EXPORT_OBJECTSにて定義されているJOBオブジェクトは、DBMS_JOBのJOBのことである。
Data PumpのSCHEMA_EXPORT_OBJECTSにて定義されているPROCOBJオブジェクトが、DBMS_SCHEDULERのJOBのこと!?であるようだ。
(オブジェクト名からは想像できない可読性の悪さはなんとかしてくれ!)

DBMS_SCHEDULERへの移行が推奨されながらDBMS_JOBが未だに存在していることの弊害のようにも思えてきた。
このあたり、理解しやすいように改善してもらいたい癖モノの一つとしてリストに加えておこう。


満開の桜なのに残念な天気の日曜日終わってしまった!! ということで今日はここまで。




Data Pumpも癖モノだよね〜w その1 - queryパラメーターの解析タイミング
Data Pumpも癖モノだよね〜w その2 - Materialized ViewをTableとして移行する

| | コメント (0) | トラックバック (0)

2017年4月 9日 (日)

Data Pumpも癖モノだよね〜w その2 - Materialized ViewをTableとして移行する

Previously on Mac De Oracle

前回はMac De OracleはData Pumpのqueryパラメーターの解析タイミングについて調べたメモでした。

今回はタイトルのとおり、Materialized ViewをTableとして移行していしまおうというお題。
実は、Materialized Viewとして移行しようとして失敗ったのがきっかけで知ったんですけど、あまり書かれていないので仕様だとは思うんですが(そうなんですよね?w)
(癖モノData Pumpと癖モノMaterialized Viewを扱おうとする時点で、すんなり行くわけがない、ぐらいの覚悟はしておいたほうが無難でしょうけど)


ちなみに、今回の内容とは関係ないですが、ViewをTableとしてエクスポートする機能も12cR1から提供されていますね(使ったことはまだないですが)
VIEWS_AS_TABLES
https://docs.oracle.com/database/121/SUTIL/GUID-E4E45E81-5391-43BE-B27D-B763EF79A885.htm#SUTIL3904

Exporting views as tables Oracle Database 12C release 1 (12.1)
http://dbaora.com/exporting-views-as-tables-oracle-database-12c-release-1-12-1/



以下のようなMVIEWがあります。
エクスポート側ではMaterialize Viewとして定義され、自動的にリフレッシュされています。

orcl@USERS>  select object_name,object_type from user_objects where object_name='MVIEW_MASTER';

OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
MVIEW_MASTER MATERIALIZED VIEW
VIEW_MASTER TABLE

orcl@USERS> select job,log_user,last_date,next_date,interval,failures,broken,what from user_jobs;

JOB LOG_USER LAST_DATE NEXT_DATE INTERVAL FAILURES B WHAT
---------- ------------------------------ ------------------- ------------------- -------------------- ---------- - --------------------------------------------------
21 USERS 2017/04/09 14:16:53 2017/04/09 14:17:53 sysdate+1/1440 0 N dbms_refresh.refresh('"USERS"."MVIEW_MASTER"');

表モードでMviewからTableへの変換!
tablesパラメータでMviewを指定しているところがポイント! MVIEWでもありTABLEでもあるのでTABLEとしてエクスポートができます。

表モードで有効なオブジェクトをTABLE_EXPORT_OBJECTSで確認してみて気づいたのですが、TABLE_EXPORT_OBJECTSには MATERIALIZED_VIEW は存在しません。

orcl@SYSTEM^> r
1 select * from TABLE_EXPORT_OBJECTS where
2 object_path like '%/JOB'
3 or object_path like '%/MATERIALIZED_VIEW'
4* or object_path like '%/REFRESH_GROUP'

no rows selected


つまり、表モードではMaterialized ViewをMaterizlized Viewとしてエクスポート/インポートすることはできないということ!!(?)のようです。

[oracle@catfish ˜]$ expdp users/********@orcl directory=homedir dumpfile=mv2table.dmp logfile=mv2table.log tables=mview_master

Export: Release 12.1.0.2.0 - Production on Sun Apr 9 14:46:27 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "USERS"."SYS_EXPORT_TABLE_01": users/********@orcl directory=homedir dumpfile=mv2table.dmp logfile=mv2table.log tables=mview_master
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "USERS"."MVIEW_MASTER" 5.492 KB 2 rows
Master table "USERS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USERS.SYS_EXPORT_TABLE_01 is:
/home/oracle/mv2table.dmp
Job "USERS"."SYS_EXPORT_TABLE_01" successfully completed at Sun Apr 9 14:47:09 2017 elapsed 0 00:00:38

同一DB(PDB)の異なるschemaへ表としてインポート(異なるschemaへインポートするのでremap_schamaパラメータをお忘れなく)

[oracle@catfish ˜]$ impdp hr/********@orcl directory=homedir dumpfile=mv2table.dmp logfile=mv2table.log remap_schema=users:hr

Import: Release 12.1.0.2.0 - Production on Sun Apr 9 14:49:56 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "HR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_FULL_01": hr/********@orcl directory=homedir dumpfile=mv2table.dmp logfile=mv2table.log remap_schema=users:hr
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."MVIEW_MASTER" 5.492 KB 2 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "HR"."SYS_IMPORT_FULL_01" successfully completed at Sun Apr 9 14:50:19 2017 elapsed 0 00:00:22


表としてインポートされています!

orcl@HR> select object_name,object_type from user_objects where object_name='MVIEW_MASTER';

OBJECT_NAME OBJECT_TYPE
------------------------------ -----------------------
MVIEW_MASTER TABLE

orcl@HR> select job,log_user,last_date,next_date,interval,failures,broken,what from user_jobs;

no rows selected

orcl@HR> select count(1) from mview_master;

COUNT(1)
----------
2


さて、お次、
表モードとは異なり、schemaモードのオブジェクトを定義しているSCHEMA_EXPORT_OBJECTSには、Materialized ViewをMaterialized Viewとしてエクスポート/インポートするために必要な以下のオブジェクト定義が存在します。
ということは、schemaモードではMviewをMviewとして移行することも、MviewをTableとして移行することもできそうな設定になっているようです、

OBJECT_PATH                     COMMENTS
------------------------------- ------------------------------------------------------------
SCHEMA_EXPORT/DB_LINK Private database links in the selected schemas
SCHEMA_EXPORT/TABLE Tables in the selected schemas and their dependent objects
SCHEMA_EXPORT/MATERIALIZED_VIEW Materialized views
SCHEMA_EXPORT/JOB Jobs in the selected schemas
SCHEMA_EXPORT/REFRESH_GROUP Refresh groups in the selected schemas


ということで、schemaモードでも同様の変換が可能かためしてみます。
schemaモードでinclude=TABLEとして表関連オブジェクトのみエクスポート。
schemaモードを選択した場合は、materialized view、job、refresh groupもスコープに入ってしまうので、余分なオブジェクトや定義を取り込まないようにするのがポイント(でした)

[oracle@catfish ˜]$ expdp users/******** directory=homedir dumpfile=test.dmp logfile=testexp.log include=TABLE

Export: Release 12.1.0.2.0 - Production on Wed Apr 5 00:40:17 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "USERS"."SYS_EXPORT_SCHEMA_01": users/******** directory=homedir dumpfile=test.dmp logfile=testexp.log include=TABLE
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "USERS"."MVIEW_MASTER" 5.484 KB 1 rows
Master table "USERS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USERS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/test.dmp
Job "USERS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Apr 5 00:40:43 2017 elapsed 0 00:00:25

同一DB(PDB)の異なるschemaへ表としてインポート(異なるschemaへインポートするのでremap_schamaパラメータをお忘れなく)

[oracle@catfish ˜]$ impdp users2/******** directory=homedir dumpfile=test.dmp logfile=testexp.log include=TABLE remap_schema=users:users2

Import: Release 12.1.0.2.0 - Production on Wed Apr 5 00:42:18 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "USERS2"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "USERS2"."SYS_IMPORT_FULL_01": users2/******** directory=homedir dumpfile=test.dmp logfile=testexp.log include=TABLE remap_schema=users:users2
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "USERS2"."MVIEW_MASTER" 5.484 KB 1 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "USERS2"."SYS_IMPORT_FULL_01" successfully completed at Wed Apr 5 00:42:24 2017 elapsed 0 00:00:05


マスター表からDBリンク経由でCTASするという手もありますが、Materialized ViewではなくTableとして移行する場合、Data Pumpを利用する方法もあるなぁと。
そして、includeパラメータやエクスポートモードには注意しないと、いろいろハマりそうだなぁ、と思ったのでしたw


ということで、schemaモードでもMaterialized ViewをTableとして移行できるよ! めでたしめでたしw (失敗は気づきの母w)

[oracle@catfish ˜]$ sqlplus users2/********

SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 5 00:42:39 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Wed Apr 05 2017 00:42:18 +09:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

ORCL@USERS2> select object_name,object_type from user_objects where object_name='MVIEW_MASTER';

OBJECT_NAME OBJECT_TYPE
------------------------------ -----------------------
MVIEW_MASTER TABLE

ORCL@USERS2> select * from user_mviews;
no rows selected

ORCL@USERS2> select job,log_user,last_date,next_date,interval,failures,broken,what from user_jobs;

no rows selected

ORCL@USERS2> select * from mview_master;

ID DATA
---------- ----------
1 test1


今日はここまで。


Data Pumpも癖モノだよね〜w その1 - queryパラメーターの解析タイミング

| | コメント (0) | トラックバック (0)

2017年3月 3日 (金)

号外:遅まきながら、Oracle Database 12c R2インストールフェスタ参戦w

日本のサイトにはまだ見当たりませんが、本家のサイトからOracle Database 12c Release 2がダウンロード可能となったのをTanelのつぶやきで知ったのはいいが、仕事疲れで帰宅後、爆睡して出遅れ感満載で参戦中w

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

20170303_53712

ダウンロードしてるだけなんで、まだ、遊ぶ余裕はないかもしれないのですが:)

20170303_53944


| | コメント (0) | トラックバック (0)