SQL Developer Web で Autonomous Database にローカルファイル(xlsx)をロードする

この記事は「Oracle Cloud Infrastructure Advent Calendar 2019 - Adventar」の12月1日の記事として書かれています。
記事の内容は執筆時(2019/12/01)のものであり、現在とは異なる可能性がありますので適宜最新状況をご確認ください。

Autonomous Database へデータをロードする方法はいくつかありますが、一番手っ取り早そうな SQL Developer Web によるロードをご紹介します。
SQL Developer Web は、SQL Developer をご利用されてる方は、SQL Developer のブラウザ版だと思って頂ければ、理解が早いです。
ブラウザ環境さえあれば、SQLの実行が可能になり、Autonomous Database では、プロビジョニング直後から利用可能です。
もちろん Always Free で作成した Autonomous Database でも利用可能です。

前置き

本記事では、Autonomous Database 自体の作成やSQL Developer Web の接続方法は含みません。
SQL Developer Web で Autonomous Database 上の既存表へエクセルファイル(xlsx)をロードするために、必要なファイルと表の準備から紹介しています。

まずはマニュアル

読まなくても直感で使えますが、念のため本記事で紹介する機能のマニュアルを置いておきます。

マニュアルは、ADW/ATP で分かれてますが、"Load Data from Local Files with SQL Developer Web" という章でどちらも内容は同じです。

Using Oracle Autonomous Data Warehouse
docs.oracle.com

Using Oracle Autonomous Transaction Processing
docs.oracle.com

ロードするデータの準備

今回は、日本オラクルの事業所一覧を元に、データを作成しました。
www.oracle.com

下記のタブ区切り文字列をコピー、Excelに貼りつけて保存してください。
私は、日本オラクル事業所一覧.xlsxという名前で保存しました。

事業所名	郵便番号	所在地	電話番号
本 社	107-0061	東京都港区北青山2-5-8 オラクル青山センター	03-6834-6666
北海道オフィス	060-0001	札幌市中央区北一条西4-1-2 武田りそなビル 6F	011-252-6500
東北オフィス	980-0014	宮城県仙台市青葉区本町1-1-1 大樹生命仙台本町ビル 18F	022-212-3303
東海オフィス	453-6111	愛知県名古屋市中村区平池町4-60-12 グローバルゲート名古屋 11F	052-551-7511
北陸オフィス	920-0901	石川県金沢市彦三町1-2-1 アソルティ金沢彦三 1F リージャス金沢駅東センター
関西オフィス	530-0003	大阪府大阪市北区堂島2-4-27 新藤田ビル 9F	06-6442-6777
中国・四国オフィス	730-0051	広島市中区大手町2-8-1 大手町スクエア 8F	082-249-3371
九州オフィス	810-0001	福岡県福岡市中央区天神1-12-7 福岡ダイヤモンドビル 8F	092-736-0150
沖縄オフィス	900-0033	沖縄県那覇市久米2-3-15 COI那覇ビル 5F リージャス COI那覇ビルセンター	098-916-8353
赤坂オフィス	107-0051	東京都港区元赤坂1-3-13 赤坂センタービルディング	03-6834-6666
豊田オフィス	471-0833	愛知県豊田市山之手4-46 三井住友海上豊田ビル5F	0565-74-1600
トレーニングキャンパス大阪	530-0003	大阪府大阪市北区堂島2-4-27 新藤田ビル	06-6442-6172
トレーニングキャンパス赤坂	107-0051	東京都港区元赤坂1-3-13 赤坂センタービルディング12F	0120-155-092

北陸オフィスには、オフィス用の電話番号はないのですね。
例では、日本オラクル事業所一覧.xlsxという名前で保存しました。

f:id:tzm1001:20191201022059p:plain
日本オラクル事業所一覧.xlsx

ロード対象の表を準備

今回は初めてのデータ投入なので、OCJ_Officeという表を作成します。

CREATE TABLE OCJ_Office(
    OfficeName varchar2(60),
    ZipCode varchar2(8),
    Address varchar2(120),
    OfficeTEL varchar2(12)
);

f:id:tzm1001:20191201034239p:plain

SQL Developer であれば、新規表もロード時に作成できますが、SQL Developer Web ではロード先の表を事前に作成しておく必要があります。

SQL Developer Web でローカルファイル(xlsx)をロード

準備したデータと表を使って SQL Developer Web で Autonomous Database にロードしていきます。
表を決めて、ファイルの選んで、表とファイルの列を結びつけておしまいです。

  • ロードしたい表を右クリックしてデータをアップロードを選択

f:id:tzm1001:20191201034242p:plain

  • インポートウィザードがブラウザ内に開く

f:id:tzm1001:20191201034245p:plain

  • ロードしたいデータが入ったファイルを指定するとプレビュー画面へ

f:id:tzm1001:20191201034212p:plain

  • ⚙を押すとスキップする行など選択も可能

f:id:tzm1001:20191201034215p:plain

  • 表のどの列にファイルのどの列を対応させるか選択

f:id:tzm1001:20191201034218p:plain

  • 1つ1つやるのは、面倒なので、⚙を押して、位置を選択

f:id:tzm1001:20191201034222p:plain

f:id:tzm1001:20191201034225p:plain

  • 確認画面で良ければ、終了を教えてジョブ実行

f:id:tzm1001:20191201034228p:plain

  • この画面は実行されたことがわかるだけなので、OK押しましょう。

f:id:tzm1001:20191201034232p:plain

  • フッターに完了した旨通知されます。

f:id:tzm1001:20191201035705p:plain

  • SELECT するとデータが入っているのが確認できます。日本語もできてますね。
SELECT * FROM OCJ_Office;

f:id:tzm1001:20191201034235p:plain


まとめ

今回は、XLSLを使いましたが、その他にも CSV、XLS、TSV、TXTが利用できます。
非常に簡単なので軽量なワークシートをとりあえず放り込むには、この方法も選択肢になるのかなと思います。
Autonomous Database は、周辺機能も含めて日々進化しているので、SQL Developer 同様に、ロード時に新規に表も作ってくれるエンハンスに期待したいですね。

今日(今年?)は、以上です。

ADB Schema Advisor の利用

この記事は「Oracle Cloud その2 Advent Calendar 2018 - Adventar」の12月8日の記事として書かれています。
記事の内容は執筆時(2018/12/08)のものであり、現在とは異なる可能性がありますので適宜最新状況をご確認ください。


自律型データベース・クラウドサービスの1つである Autonomous Data Warehouse Cloud Service では、特定のデータベースオブジェクトの作成が制限されています。

Autonomous Data Warehouse for Experienced Oracle Database Users

経験豊富なOracle DatabaseユーザーのためのAutonomous Data Warehouse*1

制限対象のオブジェクトが入っているかどうかは、現行データベースで DBA_OBJECTS の OBJECT_TYPE 列などを見ていけば確認できます。

ただ、これらの作業は少し煩雑であるため、調査用に Schema Advisor*2 というパッケージを用意しています。
今回は、このパッケージの使い方を紹介したいと思います。
なお、パッケージの入手には、My Oracle Supportへのログインが必要になります。

Autonomous Data Warehouse Schema Advisor って何?

Autonomous Data Warehouse にオブジェクトを移行する際に、制限事項に抵触しているオブジェクトの有無を調べるパッケージです。
スキーマを指定することで、対象スキーマのオブジェクトが、移行可能、不可なオブジェクトの件数やその理由を出力してくれるツールです。

パッケージのダウンロード

まずは、パッケージ(adw_advisor.plb)を入手し、調査対象環境で実行できるところに配置しておきます。
NOTE:2462677.1 から Oracle Autonomous Database Schema Advisor のパッケージをダウンロードしてください*3

Oracle Autonomous Data Warehouse Schema Advisor (Doc ID 2462677.1)
support.oracle.com

$ ls -l adw_advisor.plb 
-rw-rw-r-- 1 oracle oracle 5962 Sep 28 08:10 adw_advisor.plb

最新版は9月下旬にリリースされたもので、6k弱と非常にコンパクトです。

ユーザー作成と権限付与

必要に応じて、パッケージをインストールするユーザーと必要な権限を付与していきます。
同じ権限を保有しているものがあれば、既存ユーザーを利用することもできます。
今回は、12.1.0.2 の pdb 環境(testpdb1)で、新規に ADW_ADVISOR というユーザーを作成し、調査に必要な権限を付与していきます。

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 8 10:29:32 2018

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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics, Real Application Testing and Unified Auditing options

SQL> alter session set container = testpdb1;

Session altered.

SQL> CREATE USER ADW_ADVISOR IDENTIFIED by ADW_ADVISOR;

User created.

SQL> GRANT CREATE SESSION, CREATE PROCEDURE TO ADW_ADVISOR;

Grant succeeded.

SQL> GRANT SELECT ON DBA_USERS TO ADW_ADVISOR;

Grant succeeded.

SQL> GRANT SELECT ON DBA_OBJECTS TO ADW_ADVISOR;

Grant succeeded.

SQL> GRANT SELECT ON DBA_TABLES TO ADW_ADVISOR;

Grant succeeded.

SQL> GRANT SELECT ON DBA_INDEXES TO ADW_ADVISOR;

Grant succeeded.

SQL> GRANT SELECT ON DBA_NESTED_TABLES TO ADW_ADVISOR;

Grant succeeded.

SQL> GRANT SELECT ON DBA_MVIEW_LOGS TO ADW_ADVISOR;

Grant succeeded.

SQL> GRANT SELECT ON DBA_TYPES TO ADW_ADVISOR;

Grant succeeded.

SQL> GRANT SELECT ON DBA_CONSTRAINTS TO ADW_ADVISOR;

Grant succeeded.

SQL> GRANT SELECT ON DBA_TAB_COLS TO ADW_ADVISOR;

Grant succeeded.

SQL> GRANT SELECT ON DBA_EXTERNAL_TABLES TO ADW_ADVISOR;

Grant succeeded.

-- 12.1 以降の場合にはゾーンマップも。
SQL> GRANT SELECT ON DBA_ZONEMAPS TO ADW_ADVISOR;

Grant succeeded.
パッケージのインストール

次に、調査用のスキーマにパッケージをインストールします。
インストールと言っても、ダウンロードしていた adw_advisor.plb を実行するだけです。

$ sqlplus ADW_ADVISOR/ADW_ADVISOR@testpdb1

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 8 10:51:51 2018

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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics, Real Application Testing and Unified Auditing options

SQL> @adw_advisor.plb

Package created.


Package body created.

-- 一応エラーチェック
SQL> show errors;
No errors.
SQL> desc adw_advisor
PROCEDURE REPORT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 I_SCHEMA                       VARCHAR2                IN

準備はこれで完了です。

実行と結果確認

実行は、調査対象のスキーマをadw_advisor.report('<スキーマ名>') に指定するだけです。

-- SH スキーマを調べた結果
SQL> set serveroutput on
SQL> set linesize 95 -- default の 80 だと少しレイアウトが崩れるのでおまじないで 95 以上に。
SQL> exec adw_advisor.report('SH');
==========================================================================================
== SCHEMA MIGRATION REPORT (SH)
==========================================================================================
------------------------------------------------------------------------------------------
-- MIGRATION SUMMARY
------------------------------------------------------------------------------------------
Object Type                Object Count          ADW Object Count      Objects Not Migrated
-------------------------  --------------------  --------------------  --------------------
INDEX PARTITION            196                   196                   0
TABLE PARTITION            56                    56                    0
DIMENSION                  5                     5                     0
MATERIALIZED VIEW          2                     2                     0
VIEW                       1                     1                     0
TABLE                      17                    17                    0
INDEX                      26                    26                    0
CONSTRAINT                 146                   146                   0
------------------------------------------------------------------------------------------
-- OBJECTS NOT MIGRATED
------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
-- OBJECTS MIGRATED WITH CHANGES
------------------------------------------------------------------------------------------
Index Organized Tables (Total=1)
--------------------------------
Note: IOT tables are not supported in ADW but will migrate as regular tables when transformed
by Data Pump using 'dwcs_cvt_iots:y'.
DR$SUP_TEXT_IDX$K                     DR$SUP_TEXT_IDX$N
External Tables (Total=1)
-------------------------
Note: External tables will migrate as regular tables. Please re-architect them to use a cloud
storage such as Oracle Object Store or Amazon S3 instead of a regular file system.
SALES_TRANSACTIONS_EXT

PL/SQL procedure successfully completed.

MIGRATION SUMMARY に対象スキーマのオブジェクト数と移行可否オブジェクトの数が出力されます。
サポートしていないものの対応策があるものについては、OBJECTS MIGRATED WITH CHANGES の項目内で簡単なガイドをしてくれます。

サポートしてないオブジェクトがある場合

Database Link(TEST_LINK) や LONG型のテーブル(LONG_TABLE)を作成したスキーマでテストした例

SQL> exec adw_advisor.report('ASATEST');
==========================================================================================
== SCHEMA MIGRATION REPORT (ASATEST)
==========================================================================================
------------------------------------------------------------------------------------------
-- MIGRATION SUMMARY
------------------------------------------------------------------------------------------
Object Type                Object Count          ADW Object Count      Objects Not Migrated
-------------------------  --------------------  --------------------  --------------------
DATABASE LINK              1                     0                     1
TABLE                      1                     0                     1
------------------------------------------------------------------------------------------
-- OBJECTS NOT MIGRATED
------------------------------------------------------------------------------------------
Tables with LONG/LONG RAW Columns (Total=1)
-------------------------------------------
Note: LONG/LONG RAW columns are not supported in ADW. Consider changing the column type to LOB 
as it's designed to handle large data.
LONG_TABLE
Database Links Not Migrated (Total=1)
-------------------------------------
Note: Database Links are currently not allowed in ADW. Consider alternatives to integrating
data from other Oracle Databases.
TEST_LINK
------------------------------------------------------------------------------------------
-- OBJECTS MIGRATED WITH CHANGES
------------------------------------------------------------------------------------------
<NONE>

PL/SQL procedure successfully completed.

対象となるオブジェクトと理由がOBJECTS NOT MIGRATED 項目に出力されます。
データベース統合等、別の方法でDatabase Link を削除できないか、LONG型データの内容の精査し、変更可能有無などを判断してください。

お掃除

使い終わったら消しておきましょう。

-- 管理者ユーザーにて
SQL> drop user adw_advisor cascade;

User dropped.

-- or --

-- パッケージをインストールしたユーザーにて
SQL> DROP PACKAGE ADW_ADVISOR;

Package dropped.
まとめ

今回は、Autonomous Data Warehouse Cloud Service を利用する前の事前チェックスクリプトを紹介しました。
サービス提供当初は、索引やパーティション、マテリアライズドビューについても制限事項に含まれておりましたので、チェックが今よりもだいぶ煩雑でした。
現状はだいぶ規制が緩まったので、チェック項目は少なくなっていますが、自家製スクリプトの抜け漏れリスクや作成・修正コストを考えるとサポートから提供されているツールを利用するのが、賢い選択ではないかと思います。

今日は、以上です。

追加更新<2020-01-10>

記載当時は、Autonomous Data Warehouse(ADW)だけでしたが、Autonomous Transaction Processing(ATP)にも対応しています。
そのため、NOTEやパッケージ名も Autonomous Database(ADB)に変わっています。この記事もタイトルだけ ADW から ADB に変更しました。
当時サポートしてなかった Database Link にも対応しています。
10.2以降で利用できるのでぜひご利用ください。

*1:日本語訳は更新にラグがありますので、英語版も一緒にご確認ください

*2:公式が Advisor なので、Adviser ではなく Advisor としています

*3:My Oracle Support へのログインが必要です