2019年2月12日 (火)

RDS Oracle 雑多なメモ#17/ FAQ

Oracle DB インスタンスの一般的な DBA タスクに記載されているAmazon RDS OracleとオンプレのOracleの操作方法の違は意外に多く、長年かけて体に染み付いていて、脊髄反応でタイプしてしまうとエラー、あ”〜っなんてこともしばしばw

仕方ないので、慣れるしかないわけですが、脊髄反応でオンプレのコマンドをタイプして、あ”〜っ! となったことのある個人的な Top5 を備忘録として書いておきます:)
脊髄反応でそんな権限ないよーというショックなエラーうけとる回数を少しでも減らせるようAmazon RDSパッケージのタイプ練習中の日々w (いずれ、うまく切り替えられるようになれるだろうと信じてw

Oracle DB インスタンスの一般的な DBA システムタスク
Oracle DB インスタンスの一般的な DBA データベースタスク
Oracle DB インスタンスの一般的な DBA ログタスク
Oracle DB インスタンスの一般的な DBA のその他のタスク

個人的に、つい、オンプレと同じ操作をして、エラーになってしまった Top5 w

1位. sysオブジェクトへ権限付与で grant文をタイプしてしまう。
2位. つい、alter system kill session をタイプしてしまう。
3位. オンラインログファイルを切り替えたり、追加、削除で、alter database add logfile..をタイプしたり、alter system switch logfileをタイプしてしまう。
4位. ディレクトリオブジェクトを作成しようとして、create directory...をタイプしてしまう。
5位. rmanの検証コマンドを使おうとして、生のrmanは使えなかった、と気づくw

私がつい、脊髄反応でオリジナルのコマンドをタイプして、エラー? なぬ? あ、RDSではAmazon RDS向けのパッケージ使うんだった!!と 気づく典型的な操作の数々(^^;;;;; 長年しみついた手癖で脊髄反応しちゃうのでどうしようもないのですw
みなさんはどのコマンドで、あ”! となることが多いのでしょうか?(おそらく Top.1は、私と同じ、grant関連ではないでしょうか?w 一番使う機会が多いですからね)

話は少々脱線しますが、上記操作を行うAmazon RDSパッケージでデータベースサイズが大きくなると処理時間を要するものもそこそこあります。
Amazon RDSパッケージで提供されていても内部ではOracleの対応する機能を実行しているわけで、処理時間を要するタイプの操作を行った場合、処理の進行状況を確認確認したくなることもあります。そんな時は、v$session_longops を参照するとよいのではないかと思います。全ての機能が詳細な情報をv$session_longopsに載せてくれるわけではないですが。。(オンプレのOracle Databaseを利用していたという方でも、v$session_longops を使ったことはないなんてことも少なくないような気がします)


RDS Oracleでも v$session_longops ビューは効果的に利用できる例として、datapumpやOracle DB インスタンスの一般的な DBA データベースタスクでも解説されているrmanの検証コマンドの実行時など、操作にそれなりの時間を要するタイプのものです。(datapumpについては、詳細なステータスを記録していないようなので、datapumpのlogを覗くほうが状況確認としては便利ではありますが、一応、datapumpもv$session_longopsには記録されます。この点はOracle由来なのでオンプレでも同じです。)

一例として、それなりに時間を要する処理の代表格、Amazon RDS プロシージャ rdsadmin.rdsadmin_rman_util.validate_database(Oracle DB インスタンスの一般的な DBA データベースタスク参照)を利用した関連ファイル検証の進行状況をv$session_longopsを利用してモニターリング:)

今回利用したRDS Oracleのデータファイルはぞれぞれ以下のようなサイズです。

SQL> r
1 select
2 tablespace_name
3 ,file_name
4 ,sum(bytes)/1024/1024 "MB"
5 from
6 dba_data_files
7 group by
8 tablespace_name
9 ,file_name
10 union all
11 select
12 tablespace_name
13 ,file_name
14 ,sum(bytes)/1024/1024 "MB"
15 from
16 dba_temp_files
17 group by
18 tablespace_name
19* ,file_name

TABLESPACE_NAME FILE_NAME MB
------------------------------ ------------------------------------------------------------ ----------
SYSAUX /rdsdbdata/db/ORCL_A/datafile/o1_mf_sysaux_fxpjf1nv_.dbf 498.9375
USERS /rdsdbdata/db/ORCL_A/datafile/o1_mf_users_fxpjf3d2_.dbf 100
UNDO_T1 /rdsdbdata/db/ORCL_A/datafile/o1_mf_undo_t1_fxpjf2lx_.dbf 290
RDSADMIN /rdsdbdata/db/ORCL_A/datafile/o1_mf_rdsadmin_fxpkkz9k_.dbf 7
SYSTEM /rdsdbdata/db/ORCL_A/datafile/o1_mf_system_fxpjdxws_.dbf 500
TEMP /rdsdbdata/db/ORCL_A/datafile/o1_mf_temp_fxpjf34b_.tmp 100
----------
sum 1495.9375


事前に作成しておいたスクリプトでAmazon RDS プロシージャ rdsadmin.rdsadmin_rman_util.validate_databaseの処理状況をモニタリングしています(スクリプトの例は後半参照のこと)

SQL> @show_validate_status

レコードが選択されませんでした。

SQL> /

NOW SID SERIAL# OPNAME CONTEXT SOFAR TOTALWORK % done
-------------------------------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ----------
19-02-11 09:42:18.217701 +00:00 665 51783 RMAN: full datafile backup 1 123322 178680 69.02

SQL> /

NOW SID SERIAL# OPNAME CONTEXT SOFAR TOTALWORK % done
-------------------------------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ----------
19-02-11 09:42:19.298741 +00:00 665 51783 RMAN: full datafile backup 1 139452 178680 78.05

SQL> /

NOW SID SERIAL# OPNAME CONTEXT SOFAR TOTALWORK % done
-------------------------------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ----------
19-02-11 09:42:20.367723 +00:00 665 51783 RMAN: full datafile backup 1 156028 178680 87.32

SQL> /

NOW SID SERIAL# OPNAME CONTEXT SOFAR TOTALWORK % done
-------------------------------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ----------
19-02-11 09:42:21.289310 +00:00 665 51783 RMAN: full datafile backup 1 170428 178680 95.38

SQL> /

レコードが選択されませんでした。

SQL>
SQL> !cat show_validate_status.sql
col "NOW" for a32
col opname for a30
SELECT
systimestamp AS "NOW"
,sid
,serial#
,opname
,context
,sofar
,totalwork
,round(sofar / totalwork * 100, 2) "% done"
FROM
v$session_longops
WHERE
opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND sofar != totalwork
AND totalwork != 0;


Previously on Mac De Oracle

RDS Oracle 雑多なメモ#1 / FAQ
RDS Oracle 雑多なメモ#2 / FAQ
RDS Oracle 雑多なメモ#3 / FAQ
RDS Oracle 雑多なメモ#4 / FAQ
RDS Oracle 雑多なメモ#5 / FAQ
RDS Oracle 雑多なメモ#6 / FAQ
RDS Oracle 雑多なメモ#7 / FAQ
RDS Oracle 雑多なメモ#8 / FAQ
RDS Oracle 雑多なメモ#9 / FAQ
RDS Oracle 雑多なメモ#10 / FAQ
RDS Oracle 雑多なメモ#11 / FAQ
RDS Oracle 雑多なメモ#12 / FAQ
RDS Oracle 雑多なメモ#13 / FAQ
RDS Oracle 雑多なメモ#14 - おまけ / FAQ
RDS Oracle 雑多なメモ#15 - おまけのおまけ / FAQ
RDS Oracle 雑多なメモ#16 - 再び:) / FAQ

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

2019年2月11日 (月)

\copy コマンド de CSVファイルからのロード

今回はOracleネタではなく、Aurora PostgreSQLネタです。(RDS PostgreSQLでも同じ

csvファイルをロードしてみます。

csvファイルは前回SQL*Plusで作成したファイルを使います:)

discus-mother:~ oracle$ echo $LANG
ja_JP.UTF-8
discus-mother:~ oracle$
discus-mother:~ oracle$ cat loaddata_test.csv
1,"テスト","note"
2,"平成","note"
3,"abcdbef","note"
4,"あ","note"
5,"A","note"
6,,"note"

つづいて、Aurora PostgreSQLへcsvデータをロードする準備。
事前に表を作成しておきました。

discus-mother:˜ oracle$ psql --host=xxxxxxxxxxxxxxxxxxxxx.rds.amazonaws.com --port=5432 --username=hoge --password --dbname=testdb

testdb=> select aurora_version();
aurora_version
----------------
2.1.0
(1 row)

testdb=> select version();
version
-----------------------------------------------------------------------------
PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit
(1 row)

testdb=> \l testdb
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
--------+-------+----------+---------+-------+-------------------
testdb | hoge | UTF8 | C | C |
(1 row)

testdb=>
testdb=> create schema hoge;
CREATE SCHEMA
testdb=> set search_path=hoge,public;
SET
testdb=>
testdb=> create table test (
testdb(> id numeric not null primary key
testdb(> ,data character varying(10)
testdb(> ,foo character varying(10) not null
testdb(> );
CREATE TABLE
testdb=> \d+ test
Table "hoge.test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+-----------------------+-----------+----------+--------------+-------------
id | numeric | not null | main | |
data | character varying(10) | | extended | |
foo | character varying(10) | not null | extended | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)

testdb=>

csvからのロードは \copy コマンドを利用しました:)

testdb=>  \copy test(id,data,foo) from 'loaddata_test.csv' with csv
COPY 6
testdb=> select * from test order by id;
id | data | foo
----+---------+------
1 | テスト | note
2 | 平成 | note
3 | abcdbef | note
4 | あ | note
5 | A | note
6 | | note
(6 rows)

testdb=>
testdb=> \pset null [null]
Null display is "[null]".
testdb=> select * from test order by id;
id | data | foo
----+---------+------
1 | テスト | note
2 | 平成 | note
3 | abcdbef | note
4 | あ | note
5 | A | note
6 | [null] | note
(6 rows)




目がしょぼしょぼするが、花粉症ではない(キッパリ

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

2019年1月 7日 (月)

PostgreSQL向け、俺よう便利メモ

testdb=> select version();
version
-----------------------------------------------------------------------------
PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit


以降のスクリプトの動作確認用の表定義

testdb=> \d+ hoge
Table "public.hoge"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------------------------+-----------+----------+--------------+-------------
id | numeric(10,0) | not null | main | |
status | numeric(2,0) | not null | main | |
note | character varying(100) | | extended | |
Indexes:
"hoge_pkey" PRIMARY KEY, btree (id)

PL/pgSQLでPL/SQLっぽい感じの無名ブロックを書いてみた。commit/rollbackは含めふことができないってところはちょいとハマるね。Oraclerには。(なれれば問題ないって感じはするが)

testdb=> \! cat ins10000000.sql
DO $$BEGIN
FOR i IN 1..10000000 LOOP
INSERT INTO hoge
(id, status, note)
VALUES(
i, 0, LPAD('hoge',100,'x')
);
END LOOP;
END$$;
testdb=> \i ins10000000.sql

まあ、いろいろ勝手が違うので、よく使いそうなSQL文も作り置きしておかないと。
以下のSQLは、Oracle Databaseだと表と索引のセグメントサイズの確認で dba_segmentsを問い合わせるのと同じイメージな。ビューは違うけど。

testdb=> \! cat show_segment_size.sql
SELECT
objectname
, TO_CHAR(pg_relation_size(objectname::regclass), '999,999,999,999') AS bytes
FROM
(
SELECT
tablename AS objectname
FROM
pg_tables
WHERE
schemaname = 'public'
UNION
SELECT
indexname AS objectname
FROM
pg_indexes
WHERE
schemaname = 'public'
) AS objects
ORDER BY
bytes DESC
;

testdb=> \i show_segment_size.sql 
objectname | bytes
------------+------------------
hoge | 1,412,415,488
hoge_pkey | 224,632,832
(2 rows)

Oracle Databaseとは異なるアーキテクチャを採用しているPostgreSQLの特徴がよく見える部分。全体の30%ぐらいの行を削除して、dead tupleを確認してみたところ。
Inside vacuum - 第一回PostgreSQLプレ勉強会

testdb=> \! cat show_dead_tup_ratio.sql
SELECT
relname
, n_live_tup
, n_dead_tup
, CASE
WHEN n_live_tup > 0
THEN ROUND(n_dead_tup * 100 / n_live_tup, 2)
ELSE NULL
END AS ratio
FROM
pg_stat_user_tables;

testdb=> delete from hoge where id between 1 and 3000000;
DELETE 3000000

testdb=> \i show_dead_tup_ratio.sql
relname | n_live_tup | n_dead_tup | ratio
---------+------------+------------+-------
hoge | 6973030 | 3020431 | 43.00
(1 row)

最後は、Lockの状態を確認するSQL文。この部分もOracle Databaseとは異なるとこも多いので、Lockがどうなってるか見えるようなSQL文は作り置きしておくと便利。
宣言的パーティションのロックもOracleのPartitionとは異なる部分も多いので事前に確認しておくと、あとから驚くようなことがなくて安心できる、かも。

testdb=> \! cat show_locked_objects.sql
SELECT
clock_timestamp()
, pc.relname
, pl.locktype
, pl.database
, pl.relation
, pl.page
, pl.tuple
, pl.virtualtransaction
, pl.pid
, pl.mode
, pl.granted
FROM
pg_locks pl
INNER JOIN pg_class pc
ON
pl.relation = pc.oid
WHERE
pc.relname !~ '^pg_'
AND pc.relname <> 'activelocks';

testdb=> BEGIN;
BEGIN
testdb=> SELECT * FROM hoge FOR UPDATE SKIP LOCKED;
id | status | note
----+--------+------------------------------------------------------------------------------------------------------
1 | 0 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxhogedkdkhoge
(1 row)


testdb=> \i show_locked_objects.sql
clock_timestamp | relname | locktype | database | relation | page | tuple | virtualtransaction | pid | mode | granted
-------------------------------+-----------+----------+----------+----------+------+-------+--------------------+-------+-----------------+---------
2019-01-07 07:45:33.742858+00 | hoge_pkey | relation | 16401 | 16405 | | | 8/11426 | 18445 | AccessShareLock | t
2019-01-07 07:45:33.742879+00 | hoge | relation | 16401 | 16402 | | | 8/11426 | 18445 | RowShareLock | t
(2 rows)

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

2018年11月 4日 (日)

RDS Oracle 雑多なメモ#16 - 再び:) / FAQ

再び忘れがちなので、備忘録。

RDS Oracleでマスターユーザー以外で、SQL*PLusの Auto trace そして、DBMS_XPLAN.DISPLAY や DBMS_XPLAN.DISPLAY_CURSOR を使おうとすると以下のようなエラーに遭遇! 
なにも準備してないと。(explain plan for文だけは準備していなくても可能なのでが)


TEST> set autot trace exp stat
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

とか

...略...
TEST> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION

なんてことに、

RDS Oracle、マスターユーザーでは可能なのですが、PLUSTRACEロールも作成されていない、かつ、 plustrce.sql がない.

AWSUSER> select role from dba_roles where role = 'PLUSTRACE';

no rows selected


ということで、いちいち調べるのも面倒なFAQとなっているので、備忘録として書いておきました。

マスターユーザー以外のユーザーに alter session システム権限を付与しておきます。
該当システム権限が付与されていないと、セッションレベルで statistics_level パラメータを変更できません。
このパラメータは、dbms_xplan.display_cursor でactual planを取得する際に必要になるのですが、最悪付与されていない場合には、SQL文に以下のヒントを追加することで代替可能ではありますが、いちいちSQL文に以下のヒントを追加しなければならないので面倒。
とはいえ、alter sessionは付与したくないということもなくはなく、そんな時は以下のヒントで頑張っください。

ex.

SELECT
/*+ gather_plan_statistics */
*
FROM
hoge
WHERE
id = 1;


次に必要なのは、 v$sessionなどを含むいくつかのパフォーマンスビューへのSELECTオブジェクト権限、グローバルな一時表として定義されているplan_tableへの全オブジェクト権限です。
管理面を考えてロールを作成し関連権限をロールに付与、作成したロールを対象ユーザーに付与するようにすると便利です。


alter sessionsシステム権限を含む最低限必要なオブジェクト権限とそれを付与するロール作成スクリプトの例は以下の通り。

foobar$ cat create_dev_role.sql

-- create developer role
create role dev_role;

-- for show parameter
grant alter session to dev_role;
-- for dbms_xplan.display_cursor and auto trace
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SESSION', 'DEV_ROLE', 'SELECT');

-- for dbms_xplan.display_cursor
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SQL_PLAN_STATISTICS_ALL', 'DEV_ROLE', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$PARAMETER', 'DEV_ROLE', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SQL', 'DEV_ROLE', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SQL_PLAN', 'DEV_ROLE', 'SELECT');

-- for auto trace
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$STATNAME', 'DEV_ROLE', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$MYSTAT', 'DEV_ROLE', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SESSTAT', 'DEV_ROLE', 'SELECT');

-- for auto trace (plan_table - temporary table)
exec rdsadmin.rdsadmin_util.grant_sys_object('PLAN_TABLE$', 'DEV_ROLE', 'ALL');

参考
Oracle DB インスタンスの一般的な DBA タスク


ということで、動作確認を兼ねたサンプルは以下のとおり。

まずは、RDS Oracleのマスターユーザーで.
マスターユーザー以外のユーザーの作成と権限とロールの付与(チューニングが必要とされる開発者等)を想定

foobar$ sqlplus awsuser/xxxxxxx@xxxx.xxxxxxxx.rds.amazonaws.com:1521/HOGE

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 2 22:05:14 2018

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

Last Successful login time: Fri Nov 02 2018 22:01:24 +09:00

...中略...

AWSUSER> l
1 create user test identified by xxxxxx
2 default tablespace users
3 temporary tablespace temp
4* quota unlimited on users
AWSUSER> /

User created.

AWSUSER> grant connect, resource to test;

Grant succeeded.

AWSUSER> @create_dev_role

Role created.

Grant succeeded.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

AWSUSER> grant dev_role to test;

Grant succeeded.

AWSUSER> exit


作成したユーザーで接続して、各方法で実行計画を取得できるか確認!
クエリーを実行するため適当な表を作成してデータを登録しておく。

foobar$ sqlplus test/xxxxxxx@xxxx.xxxxxxxx.rds.amazonaws.com:1521/HOGE

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 2 22:15:23 2018

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

...中略...
TEST>
TEST> l
1 create table hoge (
2 id number not null primary key
3 ,foobar varchar2(20)
4* ) nologging
TEST> /

Table created.

TEST>
TEST> l
1 begin
2 for i in 1..10000 loop
3 insert into
4 hoge (
5 id
6 ,foobar
7 )
8 values (
9 i
10 ,to_char(i)
11 );
12 end loop;
13 commit;
14* end;
TEST> /

PL/SQL procedure successfully completed.


SQL*Plusのauto traceが行えるか確認!

TEST> set autot trace exp stat
TEST> select * from hoge where id = 1;

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 2757398040

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HOGE | 1 | 25 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C005687 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
342 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

参考
文のトレースについて


explain plan for文は準備なしで問題ないのですが、念のための確認! DBMS_XPLAN.DISPLAYプロシージャを利用して実行計画を取得

TEST> 
TEST> explain plan for
2 select * from hoge where id = 1;

explained.

TEST> @show_explain

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2757398040

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HOGE | 1 | 25 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C005687 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=1)

14 rows selected.

Elapsed: 00:00:00.05

スクリプト例は以下の通り。$ORACLE_HOME/rdbms/admin/utlxpls.sql や、utlxplp.sqlが利用できれば楽なんでが、それら中身は、DBMS_XPLAN.DISPLAYなので大差ない内容。

$ cat show_explain.sql

set linesize 200
set long 100000
set longchunk 200
set tab off

SELECT
*
FROM
TABLE(
DBMS_XPLAN.DISPLAY(
format => 'ALL -PROJECTION -ALIAS'
)
)
;


Actual planをDBMS_XPLAN.DISPLAY_CURSORプロシージャで取得〜

TEST> 
TEST> @show_realplan

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL

Session altered.

*** SQL that you want to get an actual plan ***
1* select * from hoge where id = 1
***********************************************

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID 6f67zkz43kr76, child number 1
-------------------------------------
select * from hoge where id = 1

Plan hash value: 2757398040

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| HOGE | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX UNIQUE SCAN | SYS_C005687 | 1 | 1 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=1)

19 rows selected.

Elapsed: 00:00:00.11

Session altered.


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL

Actual planを取得するスクリプト例
SQL*Plusのコマンドを駆使してはいますが、ポイントはset statistics_level = allにすることと、DBMS_XPLAN.DISPLAY_CURSORをコールする際のformatパラメータです
set termout off/onでSQL文の結果を表示しないようにしています。この設定はSQLファイルからSQL文を実行した場合にだけ有効です。(ちょっとしたTips :)

$ cat show_realplan.sql

show parameter statistics_level
alter session set statistics_level = all;

set linesize 200
set long 100000
set longchunk 200
set tab off

PROMPT *** SQL that you want to get an actual plan ***

select * from hoge where id = 1
.
l

PROMPT ***********************************************

set termout off
r
set termout on


-- get the actual plan
set timi on
SELECT
*
FROM
TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
format => 'ALLSTATS LAST'
)
)
;
set timi off
alter session set statistics_level = typical;
show parameter statistics_level

参考
DBMS_XPLANサブプログラムの要約


Apple Store Ginzaで、息子が ”iPadでムービーを作ろう” に参加するので、その合間に、パタパタブログを書き、その足で英会話に向かう日曜日の午後w



Previously on Mac De Oracle

RDS Oracle 雑多なメモ#1 / FAQ
RDS Oracle 雑多なメモ#2 / FAQ
RDS Oracle 雑多なメモ#3 / FAQ
RDS Oracle 雑多なメモ#4 / FAQ
RDS Oracle 雑多なメモ#5 / FAQ
RDS Oracle 雑多なメモ#6 / FAQ
RDS Oracle 雑多なメモ#7 / FAQ
RDS Oracle 雑多なメモ#8 / FAQ
RDS Oracle 雑多なメモ#9 / FAQ
RDS Oracle 雑多なメモ#10 / FAQ
RDS Oracle 雑多なメモ#11 / FAQ
RDS Oracle 雑多なメモ#12 / FAQ
RDS Oracle 雑多なメモ#13 / FAQ
RDS Oracle 雑多なメモ#14 - おまけ / FAQ
RDS Oracle 雑多なメモ#15 - おまけのおまけ / FAQ

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

2018年9月28日 (金)

RDS Oracle 雑多なメモ#15 - おまけのおまけ / FAQ

やっぱり、改造してしまった。おまけのおまけ編w

前回単純にMD5を取得だけのスクリプトをやっつけで作ったので、それを少し改造して、締めくくり。?(たぶん。。)

前回作成したMD5取得スクリプトを元に、ファイルが同じかどうか比較するスクリプトに作り変えました :)

21:35:35 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
hoge.dmp.gz file 2603349 18-09-24
hoge.log.gz file 512 18-09-24
hoge.dmp.bak file 1083412480 18-09-24
hoge.dmp.gz.bak file 2603349 18-09-24
hoge.log.bak file 1202 18-09-24
hoge.log.gz.bak file 512 18-09-24
01/ directory 4096 18-09-24
hoge.dmp file 1083412480 18-09-24
hoge.log file 904 18-09-24

9行が選択されました。

ダンプファイル(一旦圧縮したファイルを解凍したファイル)とオリジナルのダンプファイルのコピーの比較

21:35:43 rdsora121@BILL> @diff_md5 test_dir hoge.dmp hoge.dmp.bak
Src : 1B84C9E09F13A4FDC6EF2F03137C0338
Dest: 1B84C9E09F13A4FDC6EF2F03137C0338
-- No difference found. --

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


ダンプファイル(オリジナルを圧縮したファイル)とオリジナルダンプファイルを圧縮したファイルのコピーの比較

21:36:28 rdsora121@BILL> @diff_md5 test_dir hoge.dmp.gz hoge.dmp.gz.bak
Src : D2CB26A7E75C9725F00C30A9280C1599
Dest: D2CB26A7E75C9725F00C30A9280C1599
-- No difference found. --

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

異なるファイルの比較、ログファイルのコピーと、圧縮したログファイルのコピー。

21:37:35 rdsora121@BILL> @diff_md5 test_dir hoge.log.bak hoge.log.gz.bak
Src : DCE17181C95EACE997F45A2537BC1DA8
Dest: 6398E040E157B4A0CEF50FCF45C76FF7
-- Difference found. --

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

圧縮したログファイルとそのコピーの比較。同じでなにより:)

21:38:15 rdsora121@BILL> @diff_md5 test_dir hoge.log.gz hoge.log.gz.bak
Src : 6398E040E157B4A0CEF50FCF45C76FF7
Dest: 6398E040E157B4A0CEF50FCF45C76FF7
-- No difference found. --

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


異なるファイルの比較、どちらもログファイルですが、内容がことなります。

21:38:51 rdsora121@BILL> @cat_file test_dir hoge.log

TEXT
------------------------------------------------------------------------------------------
マスター表"BILL"."SYS_IMPORT_TABLE_01"は正常にロード/アンロードされました
"BILL"."SYS_IMPORT_TABLE_01"を起動しています:
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
. . "SCOTT"."HOGE" 1.008 GB 270000行がインポートされました
オブジェクト型TABLE_EXPORT/TABLE/INDEX/INDEXの処理中です
オブジェクト型TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTの処理中です
オブジェクト型TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/MARKERの処理中です
ジョブ"BILL"."SYS_IMPORT_TABLE_01"が月 9月 24 04:05:17 2018 elapsed 0 00:00:52で正常に完了しました

11行が選択されました。

21:39:38 rdsora121@BILL> @cat_file test_dir hoge.log.bak

TEXT
------------------------------------------------------------------------------------------
"BILL"."EXPTABLE_HOGE"を起動しています:
BLOCKSメソッドを使用して見積り中です...
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
BLOCKSメソッドを使用した見積り合計: 2.125 GB
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/INDEX/INDEXの処理中です
オブジェクト型TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTの処理中です
オブジェクト型TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/MARKERの処理中です
. . "SCOTT"."HOGE" 1.008 GB 270000行がエクスポートされました
マスター表"BILL"."EXPTABLE_HOGE"は正常にロード/アンロードされました
******************************************************************************
BILL.EXPTABLE_HOGEに設定されたダンプ・ファイルは次のとおりです:
/rdsdbdata/userdirs/01/hoge.dmp
ジョブ"BILL"."EXPTABLE_HOGE"が日 9月 23 05:36:33 2018 elapsed 0 00:00:35で正常に完了しました

16行が選択されました。

21:39:48 rdsora121@BILL> @diff_md5 test_dir hoge.log hoge.log.bak
Src : 6DA2D7C6150A9C8ACD60D95D6A61C1B5
Dest: DCE17181C95EACE997F45A2537BC1DA8
-- Difference found. --

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

スクリプトは以下の通り。MD5の比較には、UTL_RAW.COMPARE関数を利用しています。

21:40:05 rdsora121@BILL> !cat diff_md5.sql
SET VERIFY OFF
SET SERVEROUTPUT ON FORMAT WRAPPED
DECLARE
vDirectoryName VARCHAR2(30);
vSrcFileName VARCHAR2(60);
vDestFileName VARCHAR2(60);
vSrcMd5 RAW(16);
vDestMd5 RAW(16);

PROCEDURE close_bfile
(
iBfile IN OUT NOCOPY BFILE
)
IS
BEGIN
IF DBMS_LOB.ISOPEN(iBfile) = 1 THEN
DBMS_LOB.CLOSE(iBfile);
END IF;
END close_bfile;

FUNCTION md5
(
iDirectoryName IN VARCHAR2
, iFileName IN VARCHAR2
) RETURN RAW
IS
vBfile BFILE;
vBlob BLOB;
vMd5 RAW(16);
BEGIN
vBFile := BFILENAME(UPPER(iDirectoryName), iFileName);
DBMS_LOB.OPEN(vBFile);

DBMS_LOB.CREATETEMPORARY(
lob_loc => vBlob
, cache => false
, dur => DBMS_LOB.SESSION
);

DBMS_LOB.LOADFROMFILE(
dest_lob => vBlob
, src_lob => vBFile
, amount => DBMS_LOB.GETLENGTH(vBFile)
, dest_offset => 1
, src_offset => 1
);

vMd5 := DBMS_CRYPTO.HASH(vBlob, DBMS_CRYPTO.HASH_MD5);

DBMS_LOB.FREETEMPORARY(vBlob);
close_bfile(vBFile);
RETURN vMd5;
EXCEPTION
WHEN OTHERS THEN
DBMS_LOB.FREETEMPORARY(vBlob);
close_bfile(vBFile);
RAISE;
END md5;

BEGIN
vDirectoryName := UPPER('&1');
vSrcFileName := '&2';
vDestFileName := '&3';

vSrcMd5 := md5(vDirectoryName, vSrcFileName);
vDestMd5 := md5(vDirectoryName, vDestFileName);

DBMS_OUTPUT.PUT_LINE('Src : '||vSrcMd5);
DBMS_OUTPUT.PUT_LINE('Dest: '||vDestMd5);

IF UTL_RAW.COMPARE(r1 => vSrcMd5, r2 => vDestMd5) = 0
THEN
DBMS_OUTPUT.PUT_LINE('-- No difference found. --');
ELSE
DBMS_OUTPUT.PUT_LINE('-- Difference found. --');
END IF;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM()||':'||SQLCODE());
RAISE;
END;
/

UNDEFINE 1
UNDEFINE 2
UNDEFINE 3
SET SERVEROUTPUT OFF
SET VERIFY ON


たぶん、これで終わりなはず。(なにか浮かばなければw)


Previously on Mac De Oracle

RDS Oracle 雑多なメモ#1 / FAQ
RDS Oracle 雑多なメモ#2 / FAQ
RDS Oracle 雑多なメモ#3 / FAQ
RDS Oracle 雑多なメモ#4 / FAQ
RDS Oracle 雑多なメモ#5 / FAQ
RDS Oracle 雑多なメモ#6 / FAQ
RDS Oracle 雑多なメモ#7 / FAQ
RDS Oracle 雑多なメモ#8 / FAQ
RDS Oracle 雑多なメモ#9 / FAQ
RDS Oracle 雑多なメモ#10 / FAQ
RDS Oracle 雑多なメモ#11 / FAQ
RDS Oracle 雑多なメモ#12 / FAQ
RDS Oracle 雑多なメモ#13 / FAQ
RDS Oracle 雑多なメモ#14 - おまけ / FAQ

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

2018年9月27日 (木)

RDS Oracle 雑多なメモ#14 - おまけ / FAQ

ということで、done ってしておきながら、おまけ(得意技w)です。

RDS Oracle限定というわけではなくなってきましたがw 勢いでさらに追加。


手作りのcpっぽいスクリプトなので、バグってないか少し不安w ということで、オリジナルファイルと同じなのか確認できるようにメッセージダイジェストを取得するスクリプトも作成しましたw

21:14:43 rdsora121@BILL> @list_dir

DIRECTORY_NAME DIRECTORY_PATH
---------------------------------------- ----------------------------------------------------------------------
ADUMP /rdsdbdata/log/audit
BDUMP /rdsdbdata/log/trace
DATA_PUMP_DIR /rdsdbdata/datapump
HOGE_DIR /rdsdbdata/userdirs/02
OPATCH_INST_DIR /rdsdbbin/oracle/OPatch
OPATCH_LOG_DIR /rdsdbbin/oracle/QOpatch
OPATCH_SCRIPT_DIR /rdsdbbin/oracle/QOpatch
TEST_DIR /rdsdbdata/userdirs/01

8行が選択されました。

21:14:48 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
hoge.dmp.gz file 2603349 18-09-24
hoge.log.gz file 512 18-09-24
hoge.dmp.bak file 1083412480 18-09-24
hoge.dmp.gz.bak file 2603349 18-09-24
hoge.log.bak file 1202 18-09-24
hoge.log.gz.bak file 512 18-09-24
01/ directory 4096 18-09-24
hoge.dmp file 1083412480 18-09-24
hoge.log file 904 18-09-24

9行が選択されました。

ダンプファイルを圧縮ファイル、ログファイル、ダンプファイルなどなど、MD5が取得できるようになりました。ファイルが壊れてないか確認しやすい:)

21:14:55 rdsora121@BILL> @check_md5 test_dir hoge.dmp.gz
TEST_DIR/hoge.dmp.gz MD5 : D2CB26A7E75C9725F00C30A9280C1599

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

21:15:32 rdsora121@BILL> @check_md5 test_dir hoge.dmp.gz.bak
TEST_DIR/hoge.dmp.gz.bak MD5 : D2CB26A7E75C9725F00C30A9280C1599

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

21:15:50 rdsora121@BILL> @check_md5 test_dir hoge.log
TEST_DIR/hoge.log MD5 : 6DA2D7C6150A9C8ACD60D95D6A61C1B5

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

21:16:26 rdsora121@BILL> @check_md5 test_dir hoge.dmp
TEST_DIR/hoge.dmp MD5 : 1B84C9E09F13A4FDC6EF2F03137C0338

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

21:18:02 rdsora121@BILL> @check_md5 test_dir hoge.dmp.bak
TEST_DIR/hoge.dmp.bak MD5 : 1B84C9E09F13A4FDC6EF2F03137C0338

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


ざっくり作ったスクリプトは以下の通り。BFILEからファイルを読み込み、一時BLOBとしてBFILEからロード、MD5取得というながれです。
(オンプレでもそのまま使える内容にはなっちゃいましたが、勢いで作ったので:)


21:18:50 rdsora121@BILL> !cat check_md5.sql
SET VERIFY OFF
SET SERVEROUTPUT ON FORMAT WRAPPED
DECLARE
vSrcfile BFILE;
vSrcBlob BLOB;
vSrcDirectoryName VARCHAR2(30);
vSrcFileName VARCHAR2(60);
vSrcFileSize PLS_INTEGER;
vSrcFileMd5 RAW(16);

PROCEDURE close_bfile
(
iBfile IN OUT NOCOPY BFILE
)
IS
BEGIN
IF DBMS_LOB.ISOPEN(iBfile) = 1 THEN
DBMS_LOB.CLOSE(iBfile);
END IF;
END close_bfile;

BEGIN
vSrcDirectoryName := UPPER('&1');
vSrcFileName := '&2';

vSrcFile
:= BFILENAME(UPPER(vSrcDirectoryName), vSrcFileName);

DBMS_LOB.OPEN(vSrcFile);
vSrcFileSize := DBMS_LOB.GETLENGTH(vSrcFile);


DBMS_LOB.CREATETEMPORARY (
lob_loc => vSrcBlob
, cache => false
, dur => DBMS_LOB.SESSION
);

DBMS_LOB.LOADFROMFILE (
dest_lob => vSrcBlob
, src_lob => vSrcFile
, amount => vSrcFileSize
, dest_offset => 1
, src_offset => 1
);

vSrcFileMd5 := DBMS_CRYPTO.HASH(vSrcBlob, DBMS_CRYPTO.HASH_MD5);

DBMS_LOB.FREETEMPORARY(vSrcBlob);
close_bfile(vSrcFile);

DBMS_OUTPUT.PUT_LINE(
vSrcDirectoryName
||'/'||vSrcFileName
||' MD5 : '||vSrcFileMd5
);
EXCEPTION
WHEN OTHERS THEN
DBMS_LOB.FREETEMPORARY(vSrcBlob);
close_bfile(vSrcFile);
RAISE;
END;
/

UNDEFINE 1
UNDEFINE 2
SET SERVEROUTPUT OFF
SET VERIFY ON

ということで、done... これを元にまたまにか作るかもしれない、作らないかもしれないw


Previously on Mac De Oracle

RDS Oracle 雑多なメモ#1 / FAQ
RDS Oracle 雑多なメモ#2 / FAQ
RDS Oracle 雑多なメモ#3 / FAQ
RDS Oracle 雑多なメモ#4 / FAQ
RDS Oracle 雑多なメモ#5 / FAQ
RDS Oracle 雑多なメモ#6 / FAQ
RDS Oracle 雑多なメモ#7 / FAQ
RDS Oracle 雑多なメモ#8 / FAQ
RDS Oracle 雑多なメモ#9 / FAQ
RDS Oracle 雑多なメモ#10 / FAQ
RDS Oracle 雑多なメモ#11 / FAQ
RDS Oracle 雑多なメモ#12 / FAQ
RDS Oracle 雑多なメモ#13 / FAQ

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

2018年9月26日 (水)

RDS Oracle 雑多なメモ#13 / FAQ

メモの続きです。
前回は、圧縮したテキストファイルとダンプファイル(バイナリファイル)を解凍するスクリプトを実行したところまででした。

やっとここまできたw

今回は、解凍したファイルが使えるかなど内容確認をしてみたいと思います。


まず、解凍したログファイル(テキストファイル)の内容を確認
問題なさそうですね。

12:35:01 rdsora121@BILL> @cat_file test_dir hoge.log

TEXT
----------------------------------------------------------------------------------------
"BILL"."EXPTABLE_HOGE"を起動しています:
BLOCKSメソッドを使用して見積り中です...
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
BLOCKSメソッドを使用した見積り合計: 2.125 GB
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/INDEX/INDEXの処理中です
オブジェクト型TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTの処理中です
オブジェクト型TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/MARKERの処理中です
. . "SCOTT"."HOGE" 1.008 GB 270000行がエクスポートされました
マスター表"BILL"."EXPTABLE_HOGE"は正常にロード/アンロードされました
******************************************************************************
BILL.EXPTABLE_HOGEに設定されたダンプ・ファイルは次のとおりです:
/rdsdbdata/userdirs/01/hoge.dmp
ジョブ"BILL"."EXPTABLE_HOGE"が日 9月 23 05:36:33 2018 elapsed 0 00:00:35で正常に完了しました

16行が選択されました。

解凍したダンプファイルをインポートして確認する前に、元のオブジェクトの確認後、削除しておきます。

12:37:17 rdsora121@BILL> set linesize 80
12:37:20 rdsora121@BILL> desc scott.hoge
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
FOO VARCHAR2(4000)

12:37:25 rdsora121@BILL> select count(1) from scott.hoge;

COUNT(1)
----------
270000

12:39:07 rdsora121@BILL> r
1 select
2 segment_name
3 ,sum(bytes)/1024/1024/1024 "GB"
4 from
5 dba_segments
6 where
7 owner='SCOTT'
8 and segment_name in ('HOGE','PK_HOGE')
9 group by
10* segment_name

SEGMENT_NAME GB
------------------------------ ----------
PK_HOGE .004882813
HOGE 2.125

12:39:17 rdsora121@BILL> set linesize 400
12:39:21 rdsora121@BILL>
12:40:02 rdsora121@BILL> drop table scott.hoge purge;

表が削除されました。

dbms_datapumpパッケージを利用したスクリプトを作成してインポート可能か確認 :)

13:03:40 rdsora121@BILL> @import_table test_dir hoge scott hoge
マスター表"BILL"."SYS_IMPORT_TABLE_01"は正常にロード/アンロードされました
"BILL"."SYS_IMPORT_TABLE_01"を起動しています:
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
. . "SCOTT"."HOGE" 1.008 GB 270000行がインポートされました
オブジェクト型TABLE_EXPORT/TABLE/INDEX/INDEXの処理中です
オブジェクト型TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTの処理中です
オブジェクト型TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/MARKERの処理中です
ジョブ"BILL"."SYS_IMPORT_TABLE_01"が月 9月 24 04:05:17 2018 elapsed 0 00:00:52で正常に完了しました

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

インポート成功!!! 表などの確認!

13:05:54 rdsora121@BILL> set linesize 80
13:06:00 rdsora121@BILL> desc scott.hoge
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
FOO VARCHAR2(4000)

13:06:04 rdsora121@BILL> select count(1) from scott.hoge;

COUNT(1)
----------
270000

13:07:27 rdsora121@BILL> r
1 select
2 segment_name
3 ,sum(bytes)/1024/1024/1024 "GB"
4 from
5 dba_segments
6 where
7 owner='SCOTT'
8 and segment_name in ('HOGE','PK_HOGE')
9 group by
10* segment_name

SEGMENT_NAME GB
------------------------------ ----------
PK_HOGE .004882813
HOGE 2.125


スクリプトは以下の通り。 表モードのインポートをハードコードしていますが、そのあたりパラメータ化すればそこそこ使いやすくなりますかね。。

13:08:03 rdsora121@BILL> !cat import_table.sql
SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
v4Debug VARCHAR2(50);
cDirectory CONSTANT VARCHAR2(30) := UPPER('&1');
cDumpFileName CONSTANT VARCHAR2(64) := '&2'||'.dmp';
cLogFileName CONSTANT VARCHAR2(64) := '&2'||'.log';
cSchemaName CONSTANT VARCHAR2(30) := UPPER('&3');
cTableName CONSTANT VARCHAR2(30) := UPPER('&4');
i NUMBER;
vDataPumpJobHandle NUMBER;
vProgress_ratio NUMBER;
vJobState VARCHAR2(30);
oLogEntry ku$_LogEntry;
oStatus ku$_Status;
BEGIN
DBMS_OUTPUT.ENABLE;

v4Debug := 'OPEN';
vDataPumpJobHandle
:= DBMS_DATAPUMP.OPEN (
operation => 'IMPORT'
,job_mode => 'TABLE'
,remote_link => NULL
);

v4Debug := 'ADD_FILE - dumpfile';
DBMS_DATAPUMP.ADD_FILE (
handle => vDataPumpJobHandle
,filename => cDumpFileName
,directory => cDirectory
,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
);

v4Debug := 'ADD_FILE - logfile';
DBMS_DATAPUMP.ADD_FILE (
handle => vDataPumpJobHandle
,filename => cLogFileName
,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 => '''' || cSchemaName || ''''
);

v4Debug := 'METADATA_FILTER - table name';
DBMS_DATAPUMP.METADATA_FILTER (
handle => vDataPumpJobHandle
,name => 'NAME_LIST'
,value => '''' || cTableName || ''''
);

v4Debug := 'START_JOB';
DBMS_DATAPUMP.START_JOB (
handle => vDataPumpJobHandle
);

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
);

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;

DBMS_DATAPUMP.DETACH(vDataPumpJobHandle);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm());
DBMS_OUTPUT.PUT_LINE(v4Debug);
RAISE;
END;
/

UNDEFINE 1
UNDEFINE 2
UNDEFINE 3
UNDEFINE 4
SET SERVEROUTPUT OFF
SET VERIFY ON


ということで、ひとまず、RDS Oracleの雑多なメモ done :)



Previously on Mac De Oracle

RDS Oracle 雑多なメモ#1 / FAQ
RDS Oracle 雑多なメモ#2 / FAQ
RDS Oracle 雑多なメモ#3 / FAQ
RDS Oracle 雑多なメモ#4 / FAQ
RDS Oracle 雑多なメモ#5 / FAQ
RDS Oracle 雑多なメモ#6 / FAQ
RDS Oracle 雑多なメモ#7 / FAQ
RDS Oracle 雑多なメモ#8 / FAQ
RDS Oracle 雑多なメモ#9 / FAQ
RDS Oracle 雑多なメモ#10 / FAQ
RDS Oracle 雑多なメモ#11 / FAQ
RDS Oracle 雑多なメモ#12 / FAQ

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

2018年9月25日 (火)

RDS Oracle 雑多なメモ#12 / FAQ

メモの続きです。

さて、前回、cpっぽいスクリプトの問題を改善したので今回は、圧縮したファイルを解凍するスクリプトを作ろうと思います。

前回の操作でファイルが増えて見づらくなったので、解凍に必要な最小限のファイルだけに整理します

12:20:19 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
hoge.log file 1202 18-09-23
hoge.dmp file 1083412480 18-09-23
hoge.dmp.gz file 2603349 18-09-24
hoge.log.gz file 512 18-09-24
hoge.dmp.bak file 1083412480 18-09-24
hoge.dmp.gz.bak file 2603349 18-09-24
hoge.log.bak file 1202 18-09-24
hoge.log.gz.bak file 512 18-09-24
01/ directory 4096 18-09-24

9行が選択されました。

12:20:25 rdsora121@BILL> @rm_file test_dir hoge.log
TEST_DIR/hoge.log removed.

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

12:21:49 rdsora121@BILL> @rm_file test_dir hoge.dmp
TEST_DIR/hoge.dmp removed.

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

12:21:57 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
hoge.dmp.gz file 2603349 18-09-24
hoge.log.gz file 512 18-09-24
hoge.dmp.bak file 1083412480 18-09-24
hoge.dmp.gz.bak file 2603349 18-09-24
hoge.log.bak file 1202 18-09-24
hoge.log.gz.bak file 512 18-09-24
01/ directory 4096 18-09-24

7行が選択されました。

では圧縮したテキストファイルとダンプファイル(バイナリファイル)を解凍してみます。

12:22:06 rdsora121@BILL> @gunzip_file test_dir hoge.log.gz
TEST_DIR/hoge.log.gz uncompressed to hoge.log (1202 bytes)

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

12:22:44 rdsora121@BILL> @gunzip_file test_dir hoge.dmp.gz
TEST_DIR/hoge.dmp.gz uncompressed to hoge.dmp (1083412480 bytes)

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

12:23:04 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
hoge.dmp.gz file 2603349 18-09-24
hoge.log.gz file 512 18-09-24
hoge.dmp.bak file 1083412480 18-09-24
hoge.dmp.gz.bak file 2603349 18-09-24
hoge.log.bak file 1202 18-09-24
hoge.log.gz.bak file 512 18-09-24
hoge.log file 1202 18-09-24
01/ directory 4096 18-09-24
hoge.dmp file 1083412480 18-09-24

9行が選択されました。


できた!!!


今回の利用した解凍スクリプトも元は12年前のエントリで利用したコードのほぼ再利用です。
Mac De Oracle (PL/SQL De UNCOMPRESS)

12:24:22 rdsora121@BILL> !cat gunzip_file.sql
SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
vSrcGzippedFile BFILE;
vUncompressedBlob BLOB;
vDirectoryName VARCHAR2(30);
vSrcGzippedFileName VARCHAR2(60);
vDestFileName VARCHAR2(60);
VUncompressedSize PLS_INTEGER;

PROCEDURE close_bfile
(
iBfile IN OUT NOCOPY BFILE
)
IS
BEGIN
IF DBMS_LOB.ISOPEN(iBfile) = 1 THEN
DBMS_LOB.CLOSE(iBfile);
END IF;
END close_bfile;

PROCEDURE write_blob_to_file
(
iDirectoryName IN VARCHAR2,
iFileName IN VARCHAR2,
iSrcBlob IN OUT NOCOPY BLOB
)
IS
vFile UTL_FILE.FILE_TYPE;
BEGIN
vFile := UTL_FILE.FOPEN(
UPPER(iDirectoryName),
iFileName,
'wb',
32767
);

DECLARE
cChunkSize CONSTANT PLS_INTEGER := 32767;

vBuffer RAW(32767);
vAmount PLS_INTEGER := cChunkSize;
vNumOfChunk PLS_INTEGER;
BEGIN
vNumOfChunk := CEIL(DBMS_LOB.GETLENGTH(iSrcBlob)/cChunkSize);
FOR chunk# IN 1..vNumOfChunk LOOP
DBMS_LOB.READ(
iSrcBlob,
vAmount,
(cChunkSize * (chunk# - 1)) + 1, /*offet*/
vBuffer
);
UTL_FILE.PUT_RAW(vFile, vBuffer, TRUE);
END LOOP;
END;

UTL_FILE.FCLOSE(vFile);
END write_blob_to_file;

BEGIN
vDirectoryName := UPPER('&1');
vSrcGzippedFileName := '&2';
vDestFileName := SUBSTR('&2', 1, INSTR('&2', '.gz') - 1);

vSrcGzippedFile
:= BFILENAME(UPPER(vDirectoryName), vSrcGzippedFileName);

DBMS_LOB.OPEN(vSrcGzippedFile);
vUncompressedBlob := UTL_COMPRESS.LZ_UNCOMPRESS(vSrcGzippedFile);
vUncompressedSize := DBMS_LOB.GETLENGTH(vUncompressedBlob);

write_blob_to_file(
UPPER(vDirectoryName),
vDestFileName,
vUncompressedBlob
);

DBMS_LOB.FREETEMPORARY(vUncompressedBlob);
close_bfile(vSrcGzippedFile);
DBMS_OUTPUT.PUT_LINE(
vDirectoryName||'/'
||vSrcGzippedFileName
||' uncompressed to '
||vDestFileName
||' ('||vUncompressedSize||' bytes)'
);
EXCEPTION
WHEN OTHERS THEN
DBMS_LOB.FREETEMPORARY(vUncompressedBlob);
close_bfile(vSrcGzippedFile);
RAISE;
END;
/

UNDEFINE 1
UNDEFINE 2
SET SERVEROUTPUT OFF
SET VERIFY ON

ということで、次回、解凍したテキストファイルの内容確認と、解凍したダンプファイルからインポートして内容確認へつづく :)



Previously on Mac De Oracle

RDS Oracle 雑多なメモ#1 / FAQ
RDS Oracle 雑多なメモ#2 / FAQ
RDS Oracle 雑多なメモ#3 / FAQ
RDS Oracle 雑多なメモ#4 / FAQ
RDS Oracle 雑多なメモ#5 / FAQ
RDS Oracle 雑多なメモ#6 / FAQ
RDS Oracle 雑多なメモ#7 / FAQ
RDS Oracle 雑多なメモ#8 / FAQ
RDS Oracle 雑多なメモ#9 / FAQ
RDS Oracle 雑多なメモ#10 / FAQ
RDS Oracle 雑多なメモ#11 / FAQ

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

2018年9月24日 (月)

RDS Oracle 雑多なメモ#11 / FAQ

メモの続きです。

はじめに、
ごねんなさい、ごねんなさい

RDS Oracle 雑多なメモ#6 / FAQ
で作成したUTL_FILE.FCOPYを利用したcpっぽいスクリプトはtextファイルにしか対応できません。
理由は、UTL_FILE.FCOPYプロシージャ自体がtextファイル向けでバイナリファイルに対応していないことが理由ではあるのですが、textファイルしかコピーできないのもなんなので、バイナリファイルにも対応したバージョンに変更したいと思います。

RDS Oracle 雑多なメモ#6 / FAQで作成したスクリプトは今回作成したスクリプトで置き換えていただければ、m(_ _)m


前々回作成したダンプファイル(バイナリファイル)やテキストファイルを使ってみようと思います。

utl_file.fcopyプロシージャでコピーできる例(テキストファイル)

10:48:50 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
hoge.dmp file 1083412480 18-09-23
hoge.log file 1202 18-09-23
hoge.dmp.gz file 2603349 18-09-24
hoge.log.gz file 512 18-09-24
01/ directory 4096 18-09-24

11:14:42 rdsora121@BILL> @cp_file test_dir hoge.log test_dir hoge.log.bak1

source file : test_dir/hoge.log
dest file : test_dir/hoge.log.bak1 copied.

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

11:15:10 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
hoge.dmp file 1083412480 18-09-23
hoge.log file 1202 18-09-23
hoge.dmp.gz file 2603349 18-09-24
hoge.log.gz file 512 18-09-24
01/ directory 4096 18-09-24
hoge.log.bak1 file 1202 18-09-24

6行が選択されました。

utl_file.fcopyプロシージャでコピーできない例(バイナリファイル)
エラーが発生し、コピー途中のゴミファイルが作成されていまいます。。。。これはこまる。。

11:15:10 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
hoge.dmp file 1083412480 18-09-23
hoge.log file 1202 18-09-23
hoge.dmp.gz file 2603349 18-09-24
hoge.log.gz file 512 18-09-24
01/ directory 4096 18-09-24
hoge.log.bak1 file 1202 18-09-24

11:16:51 rdsora121@BILL> @cp_file test_dir hoge.dmp test_dir hoge.dmp.bak1
-29284:ORA-29284: ファイル読取りエラーが発生しました。

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

11:17:20 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
hoge.dmp file 1083412480 18-09-23
hoge.log file 1202 18-09-23
hoge.dmp.gz file 2603349 18-09-24
hoge.log.gz file 512 18-09-24
hoge.log.bak1 file 1202 18-09-24
hoge.dmp.bak1 file 45093 18-09-24
01/ directory 4096 18-09-24

7行が選択されました。


RDS Oracle 雑多なメモ#6 / FAQで作成したcp_file.sqlのコードは以下の通り
これでバイナリーファイルもコピーできると嬉しいのにね〜。昔からできないのでしかたない。。

11:25:20 rdsora121@BILL> !cat cp_file.sql
set verify off
set serveroutput on format wrapped
BEGIN
UTL_FILE.FCOPY(
src_location => UPPER('&1')
,src_filename => '&2'
,dest_location => UPPER('&3')
,dest_filename => '&4'
);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('source file : &1/&2');
DBMS_OUTPUT.PUT_LINE('dest file : &3/&4 copied.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE()||':'||SQLERRM());
END;
/

undefine 1
undefine 2
undefine 3
undefine 4
set verify on
set serveroutput off


他の手はないかな??? あ、dbms_file_transfer.copy_fileプロシージャを使ったらどうか?

ただし、バイナリファイルをうまくコピーできるようになりましたが、このプロシージャにはいくつか制限があります。
Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス 12c リリース1 (12.1) DBMS_FILE_TRANSFER
コピーされるファイルのサイズは512バイトの倍数である必要があります。
コピーされるファイルのサイズは、2TB以下である必要があります。
コピーの進行状況を監視するには、V$SESSION_LONGOPS動的パフォーマンス・ビューを参照します。

コピーできない例
512バイトの倍数でないバイナリファイルをコピーしようとすると。。。

11:17:20 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
hoge.dmp file 1083412480 18-09-23
hoge.log file 1202 18-09-23
hoge.dmp.gz file 2603349 18-09-24
hoge.log.gz file 512 18-09-24
hoge.log.bak1 file 1202 18-09-24
hoge.dmp.bak1 file 45093 18-09-24
01/ directory 4096 18-09-24

7行が選択されました。

11:18:02 rdsora121@BILL> @cp_transferfile test_dir hoge.dmp.gz test_dir hoge.dmp.gz.bak1
-19505:ORA-19505: ファイル"/rdsdbdata/userdirs/01/hoge.dmp.gz"の識別に失敗しました。
ORA-27046: ファイル・サイズが論理ブロック・サイズの倍数ではありません。
Additional information: 1

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

うまくコピーできる例
512バイトの倍数サイズのバイナリファイルのコピー

11:23:12 rdsora121@BILL> @cp_transferfile test_dir hoge.dmp test_dir hoge.dmp.bak2

source file : test_dir/hoge.dmp
dest file : test_dir/hoge.dmp.bak2 copied.

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

11:24:01 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
hoge.dmp file 1083412480 18-09-23
hoge.log file 1202 18-09-23
hoge.dmp.gz file 2603349 18-09-24
hoge.log.gz file 512 18-09-24
hoge.log.bak1 file 1202 18-09-24
hoge.dmp.bak1 file 45093 18-09-24
01/ directory 4096 18-09-24
hoge.dmp.bak2 file 1083412480 18-09-24

8行が選択されました。

dbms_transfer_fileパッケージを利用したコードは以下の通り
これも制限が多くなければコードも短くてうれしいわけなんですが。。。しかたなし

11:24:10 rdsora121@BILL> !cat cp_transferfile.sql
set verify off
set serveroutput on format wrapped
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => UPPER('&1')
,source_file_name => '&2'
,destination_directory_object => UPPER('&3')
,destination_file_name => '&4'
);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('source file : &1/&2');
DBMS_OUTPUT.PUT_LINE('dest file : &3/&4 copied.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE()||':'||SQLERRM());
END;
/

undefine 1
undefine 2
undefine 3
undefine 4
set verify on
set serveroutput off


制限もあるし、使いやすいような使いにくいような...
いろいろ消化不良状態。。。なので

スクリプト長くなってめんどくさいけど、この部分楽はできない模様なので、手作り決定!

最終的に、既存プロシージャにないのなら、作っちゃえ。ということで、cp_file.sqlを全面作り変えw ました。

11:32:13 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
hoge.dmp file 1083412480 18-09-23
hoge.log file 1202 18-09-23
hoge.dmp.gz file 2603349 18-09-24
hoge.log.gz file 512 18-09-24
01/ directory 4096 18-09-24

11:32:21 rdsora121@BILL> @cp_file test_dir hoge.dmp test_dir hoge.dmp.bak
TEST_DIR/hoge.dmp (1083412480) to TEST_DIR/hoge.dmp.bak (1083412480)

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

11:33:07 rdsora121@BILL> @cp_file test_dir hoge.log test_dir hoge.log.bak
TEST_DIR/hoge.log (1202) to TEST_DIR/hoge.log.bak (1202)

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

11:33:26 rdsora121@BILL> @cp_file test_dir hoge.dmp.gz test_dir hoge.dmp.gz.bak
TEST_DIR/hoge.dmp.gz (2603349) to TEST_DIR/hoge.dmp.gz.bak (2603349)

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

11:33:42 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
hoge.log file 1202 18-09-23
hoge.dmp file 1083412480 18-09-23
hoge.dmp.gz file 2603349 18-09-24
hoge.log.gz file 512 18-09-24
hoge.dmp.bak file 1083412480 18-09-24
hoge.log.bak file 1202 18-09-24
01/ directory 4096 18-09-24
hoge.dmp.gz.bak file 2603349 18-09-24

8行が選択されました。

新、cp_file.sqlのスクリプトは以下の通り。基本的に、gzip_file.sql内部で利用していたバイナリファイルのハンドリング部分を抜き出した感じです。一部違うのは DBMS_LOB.CREATETEMPORARYで一時BLOBを読んでいるところ。BFILEからBLOBをロードして、出力先ファイルへ書き出しているかしょは圧縮で利用しているコードと同じです。最後に一時BLOBの解放をお忘れなく

11:34:21 rdsora121@BILL> !cat cp_file.sql
SET VERIFY OFF
SET SERVEROUTPUT ON FORMAT WRAPPED
DECLARE
vSrcfile BFILE;
vSrcBlob BLOB;
vSrcDirectoryName VARCHAR2(30);
vDestDirectoryName VARCHAR2(30);
vSrcFileName VARCHAR2(60);
vDestFileName VARCHAR2(60);
vSrcFileSize PLS_INTEGER;
vDestFileSize PLS_INTEGER;

PROCEDURE close_bfile
(
iBfile IN OUT NOCOPY BFILE
)
IS
BEGIN
IF DBMS_LOB.ISOPEN(iBfile) = 1 THEN
DBMS_LOB.CLOSE(iBfile);
END IF;
END close_bfile;

PROCEDURE write_blob_to_file
(
iDirectoryName IN VARCHAR2
, iFileName IN VARCHAR2
, iSrcBlob IN OUT NOCOPY BLOB
)
IS
vFile UTL_FILE.FILE_TYPE;
BEGIN
vFile := UTL_FILE.FOPEN(
UPPER(iDirectoryName)
, iFileName
, 'wb'
, 32767
);

DECLARE
cChunkSize CONSTANT PLS_INTEGER := 32767;

vBuffer RAW(32767);
vAmount PLS_INTEGER := cChunkSize;
vNumOfChunk PLS_INTEGER;
BEGIN
vNumOfChunk := CEIL(DBMS_LOB.GETLENGTH(iSrcBlob)/cChunkSize);
FOR chunk# IN 1..vNumOfChunk LOOP
DBMS_LOB.READ(
iSrcBlob
, vAmount
, (cChunkSize * (chunk# - 1)) + 1 /*offset*/
, vBuffer
);
UTL_FILE.PUT_RAW(vFile, vBuffer, TRUE);
END LOOP;
END;
UTL_FILE.FCLOSE(vFile);
END write_blob_to_file;

BEGIN
vSrcDirectoryName := UPPER('&1');
vSrcFileName := '&2';
vDestDirectoryName := UPPER('&3');
vDestFileName := '&4';

vSrcFile
:= BFILENAME(UPPER(vSrcDirectoryName), vSrcFileName);

DBMS_LOB.OPEN(vSrcFile);
vSrcFileSize := DBMS_LOB.GETLENGTH(vSrcFile);


DBMS_LOB.CREATETEMPORARY (
lob_loc => vSrcBlob
, cache => false
, dur => DBMS_LOB.SESSION
);

DBMS_LOB.LOADFROMFILE (
dest_lob => vSrcBlob
, src_lob => vSrcFile
, amount => vSrcFileSize
, dest_offset => 1
, src_offset => 1
);
vDestFileSize := DBMS_LOB.GETLENGTH(vSrcBlob);

write_blob_to_file(
UPPER(vDestDirectoryName)
, vDestFileName
, vSrcBlob
);

DBMS_LOB.FREETEMPORARY(vSrcBlob);
close_bfile(vSrcFile);

DBMS_OUTPUT.PUT_LINE(
vSrcDirectoryName||'/'||vSrcFileName||' ('||vDestFileSize||') to '
||vDestDirectoryName||'/'||vDestFileName||' ('||vSrcFileSize||')'
);
EXCEPTION
WHEN OTHERS THEN
DBMS_LOB.FREETEMPORARY(vSrcBlob);
close_bfile(vSrcFile);
RAISE;
END;
/

UNDEFINE 1
UNDEFINE 2
UNDEFINE 3
UNDEFINE 4
SET SERVEROUTPUT OFF
SET VERIFY ON

ということで、次回へづつく。:)


Previously on Mac De Oracle

RDS Oracle 雑多なメモ#1 / FAQ
RDS Oracle 雑多なメモ#2 / FAQ
RDS Oracle 雑多なメモ#3 / FAQ
RDS Oracle 雑多なメモ#4 / FAQ
RDS Oracle 雑多なメモ#5 / FAQ
RDS Oracle 雑多なメモ#6 / FAQ
RDS Oracle 雑多なメモ#7 / FAQ
RDS Oracle 雑多なメモ#8 / FAQ
RDS Oracle 雑多なメモ#9 / FAQ
RDS Oracle 雑多なメモ#10 / FAQ

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

RDS Oracle 雑多なメモ#10 / FAQ

メモの続きです。
前回は、dbms_datapumpパッケージを利用してエクスポートを行うスクリプトを作成しました。
今回は、作成したダンプファイル(バイナリファイルでサイズは1GB越え)をutl_compressパッケージを利用して圧縮するスクリプトを作成します。

このネタ実は、12年前の古いネタが元になっています。
Mac De Oracle (PL/SQL De COMPRESS)
Mac De Oracle (PL/SQL De UNCOMPRESS)

ということで、RDS Oracleのディレクトリオブジェクトにエクポートしたダンプファイル(バイナリファイル)圧縮してみましょう。。動くのか?。。。

ディレクトリを確認

10:48:42 rdsora121@BILL> @list_dir

DIRECTORY_NAME DIRECTORY_PATH
---------------------------------------- ----------------------------------------------------------------------
ADUMP /rdsdbdata/log/audit
BDUMP /rdsdbdata/log/trace
DATA_PUMP_DIR /rdsdbdata/datapump
HOGE_DIR /rdsdbdata/userdirs/02
OPATCH_INST_DIR /rdsdbbin/oracle/OPatch
OPATCH_LOG_DIR /rdsdbbin/oracle/QOpatch
OPATCH_SCRIPT_DIR /rdsdbbin/oracle/QOpatch
TEST_DIR /rdsdbdata/userdirs/01

8行が選択されました。

エクスポートしたダンプファイル(バイナリファイル)とログファイル(テキストファイル)が対象ディレクトリ以下にあることを確認

10:42:05 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
hoge.dmp file 1083412480 18-09-23
hoge.log file 1202 18-09-23
01/ directory 4096 18-09-24

まず、ダンプファイル(バイナリファイル)を圧縮します。ダンプファイルは圧縮効率がいいんですよね。なので転送する際には圧縮するのがオススメ
圧縮はうまくいった?ようですね。解凍スクリプト編で正常に解凍できるか確認する予定なのでしばしお待ちを。

10:46:00 rdsora121@BILL> @gzip_file test_dir hoge.dmp
TEST_DIR/hoge.dmp compressed to hoge.dmp.gz(99.76%)

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

圧縮したファイルは、.gzの拡張子をつけて出力するようにしています。

10:46:23 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
hoge.dmp file 1083412480 18-09-23
hoge.log file 1202 18-09-23
hoge.dmp.gz file 2603349 18-09-24
01/ directory 4096 18-09-24

次に、テキストファイルも圧縮!

10:46:32 rdsora121@BILL> @gzip_file test_dir hoge.log
TEST_DIR/hoge.log compressed to hoge.log.gz(57.4%)

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

10:46:51 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
hoge.dmp file 1083412480 18-09-23
hoge.log file 1202 18-09-23
hoge.dmp.gz file 2603349 18-09-24
hoge.log.gz file 512 18-09-24
001/ directory 4096 18-09-24

スクリプトは以下の通り、utl_compressパッケージを利用しています。バイナリファイルはBFILEから読み込み、圧縮、圧縮結果のバイナリは一時BLOBで返されるので、一時BLOBをバイナリファイルとして書き出しています。最後に一時BLOBを解放してあげてゴミ掃除という流れです。

10:46:58 rdsora121@BILL> !cat gzip_file.sql
SET VERIFY OFF
SET SERVEROUTPUT ON FORMAT WRAPPED
DECLARE
vSrcfile BFILE;
vCompressedBlob BLOB;
vDirectoryName VARCHAR2(30);
vSrcFileName VARCHAR2(60);
vDestFileName VARCHAR2(60);
vSrcFileSize PLS_INTEGER;
vDestFileSize PLS_INTEGER;

PROCEDURE close_bfile
(
iBfile IN OUT NOCOPY BFILE
)
IS
BEGIN
IF DBMS_LOB.ISOPEN(iBfile) = 1 THEN
DBMS_LOB.CLOSE(iBfile);
END IF;
END close_bfile;

PROCEDURE write_blob_to_file
(
iDirectoryName IN VARCHAR2,
iFileName IN VARCHAR2,
iSrcBlob IN OUT NOCOPY BLOB
)
IS
vFile UTL_FILE.FILE_TYPE;
BEGIN
vFile := UTL_FILE.FOPEN(
UPPER(iDirectoryName),
iFileName,
'wb',
32767
);

DECLARE
cChunkSize CONSTANT PLS_INTEGER := 32767;

vBuffer RAW(32767);
vAmount PLS_INTEGER := cChunkSize;
vNumOfChunk PLS_INTEGER;
BEGIN
vNumOfChunk := CEIL(DBMS_LOB.GETLENGTH(iSrcBlob)/cChunkSize);
FOR chunk# IN 1..vNumOfChunk LOOP
DBMS_LOB.READ(
iSrcBlob,
vAmount,
(cChunkSize * (chunk# - 1)) + 1, /*offset*/
vBuffer
);
UTL_FILE.PUT_RAW(vFile, vBuffer, TRUE);
END LOOP;
END;

UTL_FILE.FCLOSE(vFile);
END write_blob_to_file;
--
BEGIN
vDirectoryName := UPPER('&1');
vSrcFileName := '&2';
vDestFileName := '&2'||'.gz';

vSrcFile
:= BFILENAME(UPPER(vDirectoryName), vSrcFileName);

DBMS_LOB.OPEN(vSrcFile);
vSrcFileSize := DBMS_LOB.GETLENGTH(vSrcFile);
vCompressedBlob := UTL_COMPRESS.LZ_COMPRESS(vSrcFile);
vDestFileSize := DBMS_LOB.GETLENGTH(vCompressedBlob);

write_blob_to_file(
UPPER(vDirectoryName),
vDestFileName,
vCompressedBlob
);

DBMS_LOB.FREETEMPORARY(vCompressedBlob);
close_bfile(vSrcFile);

DBMS_OUTPUT.PUT_LINE(
vDirectoryName
||'/'||vSrcFileName||' compressed to '
||vDestFileName||'('
||ROUND((1 - (vDestFileSize / vSrcFileSize)) * 100, 2)||'%)'
);
XCEPTION
WHEN OTHERS THEN
DBMS_LOB.FREETEMPORARY(vCompressedBlob);
close_bfile(vSrcFile);
RAISE;
END;
/

UNDEFINE 1
UNDEFINE 2
SET SERVEROUTPUT OFF
SET VERIFY ON

できた〜

ということで、次回へづつく。:)



Previously on Mac De Oracle

RDS Oracle 雑多なメモ#1 / FAQ
RDS Oracle 雑多なメモ#2 / FAQ
RDS Oracle 雑多なメモ#3 / FAQ
RDS Oracle 雑多なメモ#4 / FAQ
RDS Oracle 雑多なメモ#5 / FAQ
RDS Oracle 雑多なメモ#6 / FAQ
RDS Oracle 雑多なメモ#7 / FAQ
RDS Oracle 雑多なメモ#8 / FAQ
RDS Oracle 雑多なメモ#9 / FAQ

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

RDS Oracle 雑多なメモ#9 / FAQ

メモの続きです。
ということで、 (どういうことだ〜っ。久々に一人、ツッコミ)

圧縮解凍ネタに進む前に、dbms_datapumpパッケージを使って、1GB越えのファイルを作っておこうかと。ここで作成したダンプファイルを圧縮解凍ネタにしようと思います。
事前にscottユーザーを作成して、hoge表を作成し、1GB程度のセグメントサイズになるようにデータを登録、その後、主キー索引を追加してあります。

14:29:48 rdsora121@BILL> desc scott.hoge
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
FOO VARCHAR2(4000)

14:31:24 rdsora121@BILL>select segment_name,sum(bytes)/1024/1024/1024 "GB" from dba_segments where owner='SCOTT' group by segment_name;

SEGMENT_NAME GB
------------------------------ ----------
PK_HOGE .004882813
HOGE 2.125

経過: 00:00:00.09
14:28:59 rdsora121@BILL> @list_dir

DIRECTORY_NAME DIRECTORY_PATH
---------------------------------------- ----------------------------------------------------------------------
ADUMP /rdsdbdata/log/audit
BDUMP /rdsdbdata/log/trace
DATA_PUMP_DIR /rdsdbdata/datapump
HOGE_DIR /rdsdbdata/userdirs/02
OPATCH_INST_DIR /rdsdbbin/oracle/OPatch
OPATCH_LOG_DIR /rdsdbbin/oracle/QOpatch
OPATCH_SCRIPT_DIR /rdsdbbin/oracle/QOpatch
TEST_DIR /rdsdbdata/userdirs/01

8行が選択されました。

経過: 00:00:00.03
14:29:03 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
01/ directory 4096 18-09-22

scottユーザーのhoge表を表モードでエクスポートしてダンプファイルをtest_dirへ出力させます!
以前やっつけで作ったのスクリプトの再利用なので、エクスポートモードなど他のパラメータも変更できるようにすれば、より良く改良できると思います。いまはやりませんがw
(以前の記事も参考にしてみてくさい。SQL Tuning Setのキャプチャから退避までのスクリプト(やっつけ))
出力されたダンプファイルは約1GBってところですね。

14:35:41 rdsora121@BILL> @export_table test_dir hoge scott hoge
"BILL"."EXPTABLE_HOGE"を起動しています:
BLOCKSメソッドを使用して見積り中です...
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
BLOCKSメソッドを使用した見積り合計: 2.125 GB
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/INDEX/INDEXの処理中です
オブジェクト型TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTの処理中です
オブジェクト型TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/MARKERの処理中です
. . "SCOTT"."HOGE" 1.008 GB 270000行がエクスポートされました
マスター表"BILL"."EXPTABLE_HOGE"は正常にロード/アンロードされました
******************************************************************************

BILL.EXPTABLE_HOGEに設定されたダンプ・ファイルは次のとおりです:
/rdsdbdata/userdirs/01/hoge.dmp
ジョブ"BILL"."EXPTABLE_HOGE"が日 9月 23 05:36:33 2018 elapsed 0 00:00:35で正常に完了しました

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

経過: 00:00:40.23
14:36:34 rdsora121@BILL>
14:37:04 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
01/ directory 4096 18-09-23
hoge.log file 1202 18-09-23
hoge.dmp file 1083412480 18-09-23

表モードエクスポートのDataPump Exportジョブを実行するスクリプトは以下のとおり。
(表モードに固定してある部分を変更してスキーマモードにしたり、複数の表のエクスポートに対応できるように改造してやれば、より便利なスクリプトにすることもできると思います。)

14:36:34 rdsora121@BILL> !cat export_table.sql
SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
v4Debug VARCHAR2(50);
cDirectory CONSTANT VARCHAR2(30) := UPPER('&1');
cDumpFileName CONSTANT VARCHAR2(64) := '&2'||'.dmp';
cLogFileName CONSTANT VARCHAR2(64) := '&2'||'.log';
cSchemaName CONSTANT VARCHAR2(30) := UPPER('&3');
cTableName CONSTANT VARCHAR2(30) := UPPER('&4');
i NUMBER;
vDataPumpJobHandle NUMBER;
vProgress_ratio NUMBER;
vJobState VARCHAR2(30);
oLogEntry ku$_LogEntry;
oStatus ku$_Status;
BEGIN
DBMS_OUTPUT.ENABLE;

v4Debug := 'OPEN';
vDataPumpJobHandle
:= DBMS_DATAPUMP.OPEN (
operation => 'EXPORT'
,job_mode => 'TABLE'
,remote_link => NULL
,job_name => 'EXPTABLE_'||cTableName
,version => 'LATEST'
);

v4Debug := 'ADD_FILE - dumpfile';
DBMS_DATAPUMP.ADD_FILE (
handle => vDataPumpJobHandle
,filename => cDumpFileName
,directory => cDirectory
,filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
);

v4Debug := 'ADD_FILE - logfile';
DBMS_DATAPUMP.ADD_FILE (
handle => vDataPumpJobHandle
,filename => cLogFileName
,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 => '''' || cSchemaName || ''''
);

v4Debug := 'METADATA_FILTER - table name';
DBMS_DATAPUMP.METADATA_FILTER (
handle => vDataPumpJobHandle
,name => 'NAME_LIST'
,value => '''' || cTableName || ''''
);

v4Debug := 'START_JOB';
DBMS_DATAPUMP.START_JOB (
handle => vDataPumpJobHandle
);

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
);

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;

DBMS_DATAPUMP.DETACH(vDataPumpJobHandle);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm());
DBMS_OUTPUT.PUT_LINE(v4Debug);
RAISE;
END;
/

UNDEFINE 1
UNDEFINE 2
UNDEFINE 3
UNDEFINE 4
SET SERVEROUTPUT OFF
SET VERIFY ON


ということで、次回へづつく。:)


Previously on Mac De Oracle

RDS Oracle 雑多なメモ#1 / FAQ
RDS Oracle 雑多なメモ#2 / FAQ
RDS Oracle 雑多なメモ#3 / FAQ
RDS Oracle 雑多なメモ#4 / FAQ
RDS Oracle 雑多なメモ#5 / FAQ
RDS Oracle 雑多なメモ#6 / FAQ
RDS Oracle 雑多なメモ#7 / FAQ
RDS Oracle 雑多なメモ#8 / FAQ

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

RDS Oracle 雑多なメモ#8 / FAQ

メモの続きです。
前回は、utl_fileパッケージでrmっぽいことを行うスクリプトを作成しました。
前回まででutl_fileパッケージを利用したディレクトリオブジェクト配下のファイル操作スクリリプトを作ることができました。

さて、今回はなにをしましょう?。。。。としばし考えて浮かんだのが、昔の圧縮解凍ネタとdbms_datapumpネタの組み合わせ。。。

再利用ネタ、その1は以下。
SQL Tuning Setのキャプチャから退避までのスクリプト(やっつけ
上記ネタの何を利用するかというと、DBMS_DATAPUMPパッケージ。このパッケージもディレクトリオブジェクトを利用するのでネタとしてはちょうどよさげ。

もう一つのネタはかなり古い(12年前?w)
Mac De Oracle (PL/SQL De COMPRESS)
Mac De Oracle (PL/SQL De UNCOMPRESS)
この辺りを使って、圧縮、解凍スクリプトも作っておいたら便利なんじゃないかとおもいます。

それから、kempe さんの記事も利用すればいろいろできそうですね。 プライベートサブネットのVPCエンドポイント経由でS3とやりとりすればパブリックネットワークは経由しなくて良さそうですし:)
RDSとS3でファイルのやり取りを行う

なお、11.2では、aclパラメータに.xml拡張子が必要ですが、12.1以降では必要ありません(いつ変わったw)が、12c以降では以下プロシージャは非推奨で下位互換向けにのこされています
11.2のマニュアルには aclパラメータには、.xml 拡張子が必要とは明記されていないのですが、意味不明なエラーが返されます。例のaclには.xml拡張子が付加されているのですが、パラメータの解説では一切触れられていないのでハマることがあるので注意しましょうね。

15:54:54 orcl@SYS> select version from v$instance;

VERSION
-----------------
11.2.0.4.0

15:54:00 orcl@SYS> !cat test.sql
begin
dbms_network_acl_admin.create_acl (
acl => 'test'
, description => 'acl create test'
, principal => 'SCOTT'
, is_grant => true
, privilege => 'connect'
);
end;
/

15:54:07 orcl@SYS> @test
begin
*
ERROR at line 1:
ORA-46059: Invalid ACL identifier specified
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 70
ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 218
ORA-06512: at line 2

15:54:19 orcl@SYS> !cat test.sql
begin
dbms_network_acl_admin.create_acl (
acl => 'test.xml'
, description => 'acl create test'
, principal => 'SCOTT'
, is_grant => true
, privilege => 'connect'
);
end;
/

15:54:25 orcl@SYS> @test

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
15:54:27 orcl@SYS> exec dbms_network_acl_admin.drop_acl('test.xml');

PL/SQL procedure successfully completed.

dbms_network_acl_adminについては11.2と12.1で考慮はいるかもしれません。(非推奨プロシージャはいずれ廃止されるでしょうから)

というあたりも考慮してネタを作るかどうか。。。
その前に、ファイル、圧縮解凍だけのネタを作っておくか。。

ということで、次回へづつく。:)



Previously on Mac De Oracle

RDS Oracle 雑多なメモ#1 / FAQ
RDS Oracle 雑多なメモ#2 / FAQ
RDS Oracle 雑多なメモ#3 / FAQ
RDS Oracle 雑多なメモ#4 / FAQ
RDS Oracle 雑多なメモ#5 / FAQ
RDS Oracle 雑多なメモ#6 / FAQ
RDS Oracle 雑多なメモ#7 / FAQ

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

2018年9月23日 (日)

RDS Oracle 雑多なメモ#7 / FAQ

メモの続きです。
前回は、utl_fileパッケージでcpっぽいことを行うスクリプトを作成しました。
今回は、ディレクトリオブジェクト以下のファイルにrmっぽい操作が行えるようなスクリプトを作ります :)

作成済みのtest_dirディレクトのファイルを再利用しています。

ディレクトリとファイルを指定してファイル削除!


17:11:54 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
top100_tables_v1.txt file 2277 18-09-19
top100_tables_v2.txt file 2277 18-09-21
01/ directory 4096 18-09-21


17:12:37 rdsora121@BILL> @rm_file test_dir top100_tables_v2.txt
TEST_DIR/top100_tables_v2.txt removed.

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

経過: 00:00:00.03

17:13:25 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
top100_tables_v1.txt file 2277 18-09-19
01/ directory 4096 18-09-22

スクリプトは以下の通り。UTL_FILE.FREMOVEを呼び出しているだけの単純なスクリプトにしてあります
第一回目にも書きましたが、スクリプトは、EC2など、SQL*Plusを起動しているクライアント側に作成します。

17:14:51 rdsora121@BILL> !cat rm_file.sql
set verify off
set serveroutput on
BEGIN
UTL_FILE.FREMOVE (
location => UPPER('&1')
, filename => '&2'
);
DBMS_OUTPUT.PUT_LINE(UPPER('&1')||'/&2 removed.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE()||':'||SQLERRM());
END;
/

set serveroutput off
undefine 1
undefine 2
set verify on


次回へ続く


Previously on Mac De Oracle

RDS Oracle 雑多なメモ#1 / FAQ
RDS Oracle 雑多なメモ#2 / FAQ
RDS Oracle 雑多なメモ#3 / FAQ
RDS Oracle 雑多なメモ#4 / FAQ
RDS Oracle 雑多なメモ#5 / FAQ
RDS Oracle 雑多なメモ#6 / FAQ




ところで、今年も秋葉原UDXで開催されたdb tech showcase 2018へ参加してきました。
今回、自分のセッションも司会もなしだったので、久々にゆっくりといろいろな方のセッションを聞くことがきたし、毎年このイベント合う多数のデータベースエンジニアとの交流もたのしくてたのしくてw それ以上表現のしようがないw
(ボキャ貧なのでごめんなさい、ごめんなさい)

そして、インサイトテクノロジーの社長を退かれた小幡さん、 ”おもしろった!” ありがとうございました。
おいしいみかんできたら、送ってね :)

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

2018年9月22日 (土)

RDS Oracle 雑多なメモ#6 / FAQ

メモの続きです。
前回は、utl_fileパッケージでmvっぽいことを行うスクリプトを作成しました。
今回は、ディレクトリオブジェクト以下のファイルにcpっぽい操作が行えるようなスクリプトを作ります :)



ただ、 utl_file.fcopyってテキストファイルしかコピーできないんだよね。という制限付きです。いまのところ。
(バイナリ版は手作りで作ればできたような、。。。昔のネタ引っ張り出すかw)

ということで、utl_file.fcopyもdbms_transfer_fileパッケージも使わずにLOB系操作でバイナリファイルとテキストファイルをコピーするよう、cp_file.sqlスクリプトを作り変えました。

RDS Oracle 雑多なメモ#11 / FAQも合わせて参照ください。m(_ _)m




作成済みのtest_dirとhoge_dirディレクトリとファイルを再利用しています。
ざっくりめで書いたので複数ファイルの一括コピーや引数の指定方法などなど、改善したいところはあるわけですがw 適宜対応ということで。

同一ディレクトリでファイルコピー

00:44:59 SQL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
top100_tables_v1.txt file 2277 18-09-19
01/ directory 4096 18-09-21

経過: 00:00:02.35
00:55:33 SQL> @cp_file test_dir top100_tables_v1.txt test_dir top100_tables_v2.txt

source file : test_dir/top100_tables_v1.txt
dest file : test_dir/top100_tables_v2.txt copied.

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

経過: 00:00:00.04
00:57:42 SQL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
top100_tables_v1.txt file 2277 18-09-19
top100_tables_v2.txt file 2277 18-09-21
01/ directory 4096 18-09-21

異なるディレクトリへ同一名でファイルコピー

01:01:55 SQL> @cp_file test_dir top100_tables_v2.txt hoge_dir top100_tables_v2.txt

source file : test_dir/top100_tables_v2.txt
dest file : hoge_dir/top100_tables_v2.txt copied.

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

経過: 00:00:00.05
01:05:13 SQL> @ls_dir hoge_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
top100_tables_v2.txt file 2277 18-09-21
02/ directory 4096 18-09-21

異なるディレクトリへ異なるファイル名でコピー

01:06:30 SQL> @cp_file test_dir top100_tables_v2.txt hoge_dir top100_tables_v3.txt

source file : test_dir/top100_tables_v2.txt
dest file : hoge_dir/top100_tables_v3.txt copied.

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

経過: 00:00:00.04
01:06:47 SQL> @ls_dir hoge_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
top100_tables_v2.txt file 2277 18-09-21
02/ directory 4096 18-09-21
top100_tables_v3.txt file 2277 18-09-21

スクリプトは以下の通り。


01:09:56 SQL> !cat cp_file.sql
set verify off
set serveroutput on format wrapped
BEGIN
UTL_FILE.FCOPY(
src_location => UPPER('&1')
,src_filename => '&2'
,dest_location => UPPER('&3')
,dest_filename => '&4'
);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('source file : &1/&2');
DBMS_OUTPUT.PUT_LINE('dest file : &3/&4 copied.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE()||':'||SQLERRM());
END;
/

undefine 1
undefine 2
undefine 3
undefine 4
set verify on
set serveroutput off


次回へ続く



Previously on Mac De Oracle

RDS Oracle 雑多なメモ#1 / FAQ
RDS Oracle 雑多なメモ#2 / FAQ
RDS Oracle 雑多なメモ#3 / FAQ
RDS Oracle 雑多なメモ#4 / FAQ
RDS Oracle 雑多なメモ#5 / FAQ

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

2018年9月21日 (金)

RDS Oracle 雑多なメモ#5 / FAQ

メモの続きです。
前回は、AWS提供のrdsadmin.rds_file_util.read_text_file関数でcatっぽいスクリプトを作っておきました。(改善したほうがよさげなところもありますがw)
今回は、ディレクトリオブジェクト以下のファイルにmvっぽい操作が行えるようなスクリプトを作ります。

これからの操作はファイルそのものに対しておこなうので、 わかりやすくする意味もあり新たにディレクトオブジェクトを作成して、ファイルを一作っておきます。

実験用ディレクトリオブジェクトの作成

00:49:07 rdsora121@BILL> @create_dir test_dir

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

経過: 00:00:02.04

DIRECTORY_NAME DIRECTORY_PATH
---------------------------------------- ----------------------------------------------------------------------
TEST_DIR /rdsdbdata/userdirs/01

経過: 00:00:00.03


作成したディレクトリオブジェクト以下に適当なファイル(今回はテキストファイル)を作成

01:05:30 rdsora121@BILL> @query2file.sql

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

経過: 00:00:00.12

ここでは、”utl_file I/O” - この症状はあれの可能性が高いですね。でも紹介したUTL_FILEパッケージでディレクトオブジェクト以下にファイルを出力するサンプルを再利用してファイルを書き出しました。

01:06:02 rdsora121@BILL> !cat query2file.sql
DECLARE
cDIR_NAME CONSTANT VARCHAR2(30) := 'TEST_DIR';
cFILE_NAME CONSTANT VARCHAR2(128) := 'top100_tablenames__'||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_top100_tablenames IS
SELECT
table_name
FROM
dba_tables
ORDER BY
table_name
FETCH FIRST 100 ROWS ONLY
;
BEGIN
OPEN cur_top100_tablenames;
fileHandle := UTL_FILE.FOPEN(cDIR_NAME, cFILE_NAME, cOpenMode, cBufferSize);
LOOP
FETCH cur_top100_tablenames
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_top100_tablenames;
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(fileHandle) THEN
UTL_FILE.FCLOSE(fileHandle);
END IF;

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

テキストファイルがディレクトリオブジェクト以下に作成されましった!

01:06:29 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
top100_tablenames__180919160551.113374000.txt file 2277 18-09-19
01/ directory 4096 18-09-19

念のため中身を確認

01:06:38 rdsora121@BILL> @cat_file test_dir top100_tablenames__180919160551.113374000.txt

TEXT
------------------------------------------------------------------------------------------
ACCESS$
ACLMV$
ACLMV$_REFLOG
ACLMVREFSTAT$


・・・中略・・・

AQ$_SUBSCRIBER_TABLE
AQ$_SYS$SERVICE_METRICS_TAB_G
AQ$_SYS$SERVICE_METRICS_TAB_H
AQ$_SYS$SERVICE_METRICS_TAB_I

100行が選択されました。

同一ディレクトリ内でファイル名を変更

01:29:47 rdsora121@BILL> @mv_file test_dir top100_tablenames__180919160551.113374000.txt test_dir top100_tables.txt

source file : test_dir/top100_tablenames__180919160551.113374000.txt
dest file : test_dir/top100_tables.txt moved.

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

長いファイル名称を短く変更!

01:29:57 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
top100_tables.txt file 2277 18-09-19
01/ directory 4096 18-09-19

ファイル名は同じままで、別ディレクトリへファイル移動

08:05:17 rdsora121@BILL> @create_dir hoge_dir

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

経過: 00:00:02.26

DIRECTORY_NAME DIRECTORY_PATH
---------------------------------------- ----------------------------------------------------------------------
HOGE_DIR /rdsdbdata/userdirs/02

経過: 00:00:00.04
08:05:29 rdsora121@BILL> @mv_file test_dir top100_tables.txt hoge_dir top100_tables.txt

source file : test_dir/top100_tables.txt
dest file : hoge_dir/top100_tables.txt moved.

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

経過: 00:00:00.04
08:06:27 rdsora121@BILL> @ls_dir hoge_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
top100_tables.txt file 2277 18-09-19
02/ directory 4096 18-09-19

経過: 00:00:02.25
08:06:39 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
01/ directory 4096 18-09-19

ファイル名を変更して、ディレクトリ移動

08:08:22 rdsora121@BILL> @mv_file hoge_dir top100_tables.txt test_dir top100_tables_v1.txt

source file : hoge_dir/top100_tables.txt
dest file : test_dir/top100_tables_v1.txt moved.

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

経過: 00:00:00.02
08:09:00 rdsora121@BILL> @ls_dir hoge_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
02/ directory 4096 18-09-19

経過: 00:00:02.05
08:09:11 rdsora121@BILL> @ls_dir test_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
top100_tables_v1.txt file 2277 18-09-19
01/ directory 4096 18-09-19

経過: 00:00:02.05


スクリプトは以下の通り

01:30:20 rdsora121@BILL> !cat mv_file.sql
set verify off
set serveroutput on format wrapped
BEGIN
UTL_FILE.FRENAME(
src_location => UPPER('&1')
,src_filename => '&2'
,dest_location => UPPER('&3')
,dest_filename => '&4'
,overwrite => false
);
DBMS_OUTPUT.PUT_LINE(' ');
DBMS_OUTPUT.PUT_LINE('source file : &1/&2');
DBMS_OUTPUT.PUT_LINE('dest file : &3/&4 moved.');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE()||':'||SQLERRM());
END;
/

undefine 1
undefine 2
undefine 3
undefine 4
set verify on
set serveroutput off

次回へ続く



Previously on Mac De Oracle

RDS Oracle 雑多なメモ#1 / FAQ
RDS Oracle 雑多なメモ#2 / FAQ
RDS Oracle 雑多なメモ#3 / FAQ
RDS Oracle 雑多なメモ#4 / FAQ

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

2018年9月20日 (木)

RDS Oracle 雑多なメモ#4 / FAQ

メモの続きです。
前回は、AWS提供のrdsadmin.rds_file_util.listdir関数を利用したlsっぽいファイルリストスクリプトを作成しました。
今回は、同じくAWS提供のrdsadmin.rds_file_util.read_text_file関数でcatっぽいスクリプトを。

ディレクト名を確認し。。

05:10:47 rdsora121@BILL> @list_dir

DIRECTORY_NAME DIRECTORY_PATH
---------------------------------------- ----------------------------------------------------------------------
ADUMP /rdsdbdata/log/audit
BDUMP /rdsdbdata/log/trace
DATA_PUMP_DIR /rdsdbdata/datapump
OPATCH_INST_DIR /rdsdbbin/oracle/OPatch
OPATCH_LOG_DIR /rdsdbbin/oracle/QOpatch
OPATCH_SCRIPT_DIR /rdsdbbin/oracle/QOpatch

6行が選択されました。

経過: 00:00:00.02

ファイル名を確認してから。。

05:10:50 rdsora121@BILL> @ls_dir bdump

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
ORCL_ora_12254.trc file 4393 18-09-15
ORCL_ora_12254.trm file 202 18-09-15

・・・中略・・・

ORCL_ora_12841.trm file 71 18-09-17
trace/ directory 12288 18-09-17
ORCL_ora_12841.trc file 990 18-09-17
alert_ORCL.log file 138516 18-09-17
ORCL_mmon_11216.trm file 118 18-09-17
ORCL_mmon_11216.trc file 1503 18-09-17

279行が選択されました。

経過: 00:00:02.28

alertログファイルを覗いて見ましょう。

05:10:57 rdsora121@BILL> @cat_file bdump alert_ORCL.log

TEXT
-----------------------------------------------------------------------------------------------
Sat Sep 15 19:03:04 2018
Starting ORACLE instance (normal) (OS id: 12254)
Sat Sep 15 19:03:04 2018

・・・中略・・・

pga_aggregate_limit is 4809 MB

3376行が選択されました。

経過: 00:00:03.09

スクリプトは以下のとおり。
ん〜。ざっくり作りすぎたかもw Top Nクエリもできるようにしたほうがいいかもねぇ〜と。思わなくもないけど、とりあえずこれで。

ちなみに、ディレクトリ名はUPPER()関数で強制的に大文字変換、ファイル名は入力されたファイル名をそのまま利用するようにしています。

05:12:04 rdsora121@BILL> !cat cat_file.sql
set verify off
set long 100000
set longchunk 100000
SELECT
text
FROM
TABLE (
rdsadmin.rds_file_util.read_text_file (
p_directory => UPPER('&1')
, p_filename => '&2'
)
)
;

undefine 1
undefine 2
set verify on

次回へ続く



Previously on Mac De Oracle

RDS Oracle 雑多なメモ#1 / FAQ
RDS Oracle 雑多なメモ#2 / FAQ
RDS Oracle 雑多なメモ#3 / FAQ

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

2018年9月19日 (水)

RDS Oracle 雑多なメモ#3 / FAQ

メモの続きです。
これまでに、ディレクトリ作成、削除、ディレクトリリストスクリプトを作成しました。

残る使用頻度の高そうなスクリプトは、ディレクトリオブジェクト以下にあるファイル操作スクリプト。
OSレイヤーに入れないRDS Oracleで、ディレクトリオブジェクト以下にあるファイルの操作に困るのは目に見えてるので。。。w

RDS Oracleでは、rdsadmin.rds_file_utilパッケージが提供されていますが、提供されている関数は以下の通り。
どう見ても全ての操作を行うに不十分なのですが、よーく思い出してみましょう。
ファイルの削除などの操作は、Oracle Databaseに昔からあるビルトインパッケージ、utl_fileパッケージを利用できちゃいそうですよね!!
ということで、これまた、いちいちタイプするのは面倒なので事前に作成しておいたほうが楽なはず。。。

13:40:16 rdsora121@BILL> desc rdsadmin.rds_file_util
FUNCTION LISTDIR RETURNS RDS_FILE_LIST_T
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
P_DIRECTORY VARCHAR2 IN
FUNCTION READ_TEXT_FILE RETURNS RDS_TEXT_LIST_T
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
P_DIRECTORY VARCHAR2 IN
P_FILENAME VARCHAR2 IN

まず、上記AWSから提供されているrdsadmin.rds_file_util.listdir関数を利用したスクリプトを作成しておきます。

13:53:44 rdsora121@BILL> @list_dir

DIRECTORY_NAME DIRECTORY_PATH
---------------------------------------- ----------------------------------------------------------------------
ADUMP /rdsdbdata/log/audit
BDUMP /rdsdbdata/log/trace
DATA_PUMP_DIR /rdsdbdata/datapump
OPATCH_INST_DIR /rdsdbbin/oracle/OPatch
OPATCH_LOG_DIR /rdsdbbin/oracle/QOpatch
OPATCH_SCRIPT_DIR /rdsdbbin/oracle/QOpatch

6行が選択されました。

経過: 00:00:00.03

ファイルの無い空のディレクトリは以下のようにリストされるか〜。
へぇ〜。

13:53:52 rdsora121@BILL> @ls_dir data_pump_dir

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
datapump/ directory 4096 18-09-17

経過: 00:00:02.25

ディレクトオブジェクトへの読み込み権限がないと以下のようなエラーが返ります。

13:54:07 rdsora121@BILL> @ls_dir opatch_log_dir
rdsadmin.rds_file_util.listdir(UPPER('OPATCH_LOG_DIR'))
*
行5でエラーが発生しました。:
ORA-20900: Directory OPATCH_LOG_DIR does not exist or no privileges.
ORA-06512: "SYS.RDS_SYS_UTIL", 行147
ORA-06512: "SYS.RDS_SYS_UTIL", 行161
ORA-06512: "RDSADMIN.RDS_FILE_UTIL", 行38


経過: 00:00:00.05


対象ディレクトリの読み取り権限が付与され、ディレクトリオブジェクト以下になんらかのファイルがあれば、以下のようなリリストが返ります。
こんな感じにリストされるのな。:)

13:54:42 rdsora121@BILL> @ls_dir adump

FILENAME TYPE FILESIZE MTIME
---------------------------------------------------------------------- ---------- ---------- --------
ORCL_ora_12254_20180915190307342858143795.aud file 805 18-09-15

...中略...

ORCL_ora_12687_20180917043000205002143795.aud file 1549 18-09-17
ORCL_ora_14013_20180917044500213934143795.aud file 1549 18-09-17
audit/ directory 16384 18-09-17

146行が選択されました。

経過: 00:00:02.17


スクリプトは以下のとおり。rdsadmin.rds_file_util.listdir関数は表関数なので以下のような使い方:)

13:55:17 rdsora121@BILL> !cat ls_dir.sql
set verify off
col filename for a70
SELECT
*
FROM
TABLE (
rdsadmin.rds_file_util.listdir(UPPER('&1'))
)
ORDER BY
mtime
;

undefine 1
set verify off


次回へ続く



Previously on Mac De Oracle

RDS Oracle 雑多なメモ#1 / FAQ
RDS Oracle 雑多なメモ#2 / FAQ

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

2018年9月18日 (火)

RDS Oracle 雑多なメモ#2 / FAQ

メモの続き。

前回、ディレクトリリストとディレクトリオブジェクト作成スクリプトを作ったので、作り忘れてたディレクトリオブジェクト削除スクリプトを。

実行例
前回作成しておいたlist_dir.sqlでディレクトリ名を確認しながら "TEST"ディレクトリを削除しています。

13:30:25 rdsora121@BILL> @list_dir

DIRECTORY_NAME DIRECTORY_PATH
---------------------------------------- ----------------------------------------------------------------------
ADUMP /rdsdbdata/log/audit
BDUMP /rdsdbdata/log/trace
DATA_PUMP_DIR /rdsdbdata/datapump
OPATCH_INST_DIR /rdsdbbin/oracle/OPatch
OPATCH_LOG_DIR /rdsdbbin/oracle/QOpatch
OPATCH_SCRIPT_DIR /rdsdbbin/oracle/QOpatch
TEST /rdsdbdata/userdirs/01

7行が選択されました。

経過: 00:00:00.02
13:30:29 rdsora121@BILL> @drop_dir test

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

経過: 00:00:00.02
13:30:34 rdsora121@BILL> @list_dir

DIRECTORY_NAME DIRECTORY_PATH
---------------------------------------- ----------------------------------------------------------------------
ADUMP /rdsdbdata/log/audit
BDUMP /rdsdbdata/log/trace
DATA_PUMP_DIR /rdsdbdata/datapump
OPATCH_INST_DIR /rdsdbbin/oracle/OPatch
OPATCH_LOG_DIR /rdsdbbin/oracle/QOpatch
OPATCH_SCRIPT_DIR /rdsdbbin/oracle/QOpatch

6行が選択されました。

スクリプトは以下の通り、rdsadmin.rdsadmin_util.drop_directoryを実行しているだけです:)

13:29:45 rdsora121@BILL> !cat drop_dir.sql
set verify off
exec rdsadmin.rdsadmin_util.drop_directory(p_directory_name => UPPER('&1'));

undefine 1
set verify on

次回へ続く



Previously on Mac De Oracle


RDS Oracle 雑多なメモ#1 / FAQ

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

2018年9月17日 (月)

RDS Oracle 雑多なメモ#1 / FAQ

さて、さて、ご無沙汰しておりましたw
メモも溜まってきたので、そろそろ RDS Oracle関連の雑多なメモを。。。

新たなスタートをきって1.5ヶ月目にしてRDS Oracleに触れなければいけない状況となり、オンプレのOracleの使い勝手とことなる部分にハマりつつなんとか乗り切ったのでときのメモです。
(みなさん通る道w)

ディレクトリオブジェクト周りはオンプレのOracleと勝手が違いOSレイヤーには入れないので、rdsadmin.rdsadmin_utilパッケージに慣れておく必要があり。
さらに、ファイル操作についても、rdsadmin.rds_file_utilパッケージで提供されている操作は一部なので、utl_fileパッケージを併用しないと削除、コピー、改名などもできません。
また、ダンプファイルの圧縮解答もOSレイヤーでは実行できないので、utl_compressパッケージを利用する必要もありそう。


Oracle DB インスタンスの一般的な DBA システムタスク
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.System.html

Oracle DB インスタンスの一般的な DBA ログタスク
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.Log.html

Oracle DB インスタンスの一般的な DBA データベース
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.Database.html

Oracle DB インスタンスの一般的な DBA のその他のタスク
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.Misc.html


ということで、いちいちexecute ほげほげってタイプするのもめんどくさいのでスクリプト化して、楽しましょう。という自分用メモシリーズです。
今後どれくらいRDS Oracleを利用する機会があるのかまったく予想できませんが、備えあれば。。。ということで ;)

まず、RDSADMIN.RDSADMIN_UTILパッケージをdescribeしてみた。
詳細は前述のマニュアルを見てもらうとして、頻繁に利用しそうなのは createdirectoryプロシージャ、drop_directoryプロシージャ、それに rds_versionファンクション
あたりかなぁ。個人的には。
(他のものも頻繁に使いそうなら、スクリプト化しておくと便利かだと思います。)

22:55:55 rdsora121@BILL> desc rdsadmin.rdsadmin_util
PROCEDURE ADD_LOGFILE
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
BYTES BINARY_INTEGER IN DEFAULT
PROCEDURE ADD_LOGFILE
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
P_SIZE VARCHAR2 IN
PROCEDURE ALTER_DB_TIME_ZONE
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
P_NEW_TZ VARCHAR2 IN
PROCEDURE ALTER_DEFAULT_EDITION
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
EDITION_NAME VARCHAR2 IN
PROCEDURE ALTER_DEFAULT_TABLESPACE
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
TABLESPACE_NAME VARCHAR2 IN
PROCEDURE ALTER_DEFAULT_TEMP_TABLESPACE
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
TABLESPACE_NAME VARCHAR2 IN
PROCEDURE ALTER_SUPPLEMENTAL_LOGGING
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
P_ACTION VARCHAR2 IN
P_TYPE VARCHAR2 IN DEFAULT
PROCEDURE CHECKPOINT
PROCEDURE CREATE_DIRECTORY
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
P_DIRECTORY_NAME VARCHAR2 IN
PROCEDURE DISABLE_DISTR_RECOVERY
PROCEDURE DISCONNECT
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL NUMBER IN
METHOD VARCHAR2 IN DEFAULT
PROCEDURE DROP_DIRECTORY
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
P_DIRECTORY_NAME VARCHAR2 IN
PROCEDURE DROP_LOGFILE
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
GRP BINARY_INTEGER IN
PROCEDURE ENABLE_DISTR_RECOVERY
PROCEDURE FLUSH_BUFFER_CACHE
PROCEDURE FLUSH_SHARED_POOL
PROCEDURE FORCE_LOGGING
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
P_ENABLE BOOLEAN IN DEFAULT
PROCEDURE GRANT_APEX_ADMIN_ROLE
PROCEDURE GRANT_SYS_OBJECT
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
P_OBJ_NAME VARCHAR2 IN
P_GRANTEE VARCHAR2 IN
P_PRIVILEGE VARCHAR2 IN DEFAULT
P_GRANT_OPTION BOOLEAN IN DEFAULT
PROCEDURE KILL
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL NUMBER IN
METHOD VARCHAR2 IN DEFAULT
FUNCTION RDS_VERSION RETURNS VARCHAR2
PROCEDURE RENAME_GLOBAL_NAME
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
P_NEW_GLOBAL_NAME VARCHAR2 IN
PROCEDURE RESTRICTED_SESSION
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
P_ENABLE BOOLEAN IN DEFAULT
PROCEDURE REVOKE_SYS_OBJECT
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
P_OBJ_NAME VARCHAR2 IN
P_REVOKEE VARCHAR2 IN
P_PRIVILEGE VARCHAR2 IN DEFAULT
PROCEDURE SET_CONFIGURATION
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
VALUE VARCHAR2 IN
PROCEDURE SHOW_CONFIGURATION
引数名 タイプ In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN DEFAULT
PROCEDURE SWITCH_LOGFILE

22:56:11 rdsora121@BILL>

ということで、とりあえず、DBのバージョンと、RDSのバージョンを一括取得するスクリプトを作った。
実行例

23:24:14 rdsora121@BILL> @show_version

VERSION
------------------------------
12.1.0.2.0


RDS_VERSION
------------------------------
1.2

スクリプトは以下の通り。
rdsadmin.rdsadmin_util.rds_versionファンクションを利用しています。
(ちなみに、スクリプトはクライアントとなるEC2などに作成することになります)

23:24:24 rdsora121@BILL> !cat show_version.sql
col version for a30
col rds_version for a30

SELECT
version
FROM
v$instance
;

SELECT
rdsadmin.rdsadmin_util.rds_version AS rds_version
FROM
dual
;


次は、ディレクトリ操作関連の便利スクリプト群の作成。
まずは、どんなディレクトリオブジェクトが存在するのかパスも含めて確認できると嬉しいですよね。RDS Oracleと言えど。

実行例

23:36:26 rdsora121@BILL> @list_dir

DIRECTORY_NAME DIRECTORY_PATH
---------------------------------------- ----------------------------------------------------------------------
ADUMP /rdsdbdata/log/audit
BDUMP /rdsdbdata/log/trace
DATA_PUMP_DIR /rdsdbdata/datapump
OPATCH_INST_DIR /rdsdbbin/oracle/OPatch
OPATCH_LOG_DIR /rdsdbbin/oracle/QOpatch
OPATCH_SCRIPT_DIR /rdsdbbin/oracle/QOpatch

6行が選択されました。

経過: 00:00:00.03

スクリプトは以下のとおり。
dba_directoriesを問い合わせているだけの簡単なお仕事 :)

23:36:31 rdsora121@BILL> !cat list_dir.sql
col directory_name for a40
col directory_path for a70
SELECT
directory_name
,directory_path
FROM
dba_directories
ORDER BY
directory_name
;


お次は、ディレクトリオブジェクトの作成スクリプト。

23:41:24 rdsora121@BILL> @create_dir test

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

経過: 00:00:02.12

DIRECTORY_NAME DIRECTORY_PATH
---------------------------------------- ----------------------------------------------------------------------
TEST /rdsdbdata/userdirs/01

経過: 00:00:00.06

スクリプトは以下のとおり。
rdsadmin.rdsadmin_util.create_directoryプロシージャを利用してRDS Oracleのディレクトリオブジェクトを作成後、dba_directoriesビューから作成したディレクトリオプジェクトを問い合わせています。

23:41:36 rdsora121@BILL> !cat create_dir.sql
set verify off
col directory_name for a40
col directory_path for a70

exec rdsadmin.rdsadmin_util.create_directory(p_directory_name => UPPER('&1'));

SELECT
directory_name
,directory_path
FROM
dba_directories
WHERE
directory_name = UPPER('&1')
ORDER BY
directory_name
;

undefine 1
set verify on


次回に続く

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