読者です 読者をやめる 読者になる 読者になる

作業ノート

様々なまとめ、雑感など

Oracle

参照整合性制約で参照されるテーブルから、定義したテーブルと制約名を取得するSQL

以下はFOOテーブルのカラムを参照するテーブルと制約名を取得するSQL。 SELECT uc.table_name, uc.constraint_name FROM user_constraints uc JOIN user_constraints ruc ON ruc.owner = uc.r_owner AND ruc.constraint_name = uc.r_constraint_name WHERE u…

Oracleで特定テーブルの参照整合性制約を確認するSQL

今、Symfony2を使用した開発をしている。DBはOracle。 Symfony2のconsoleでORMの設定からSQLを作成することができる。利用しているDBの状況から最新にするために必要なSQLを作成できるので便利。しかし、その作成に時間がかなりかかるため、その調査をした。…

RHEL 7.1でOracle 12cのためのfirewalldの設定をする

仕事で、Oracle 12c Enterprise EditionをRHEL7.1にインストールした。 Oracleでは通常、Oracleのリスナーで1521、EM Expressで5500のポートを使用する。 RHEL6のときはiptablesの設定をしていたが、RHEL7からはfirewalldに変更されたので、その設定方法を調…

Oracleのシーケンスの取得値を所定値に変更する

OracleのDBで、シーケンス値を使用するカラムを持つテーブルのデータを移行するときに起きる問題。 Oracleでは、シーケンスとテーブルは別に扱う。このため、テーブルのデータを移行するときには あわせてシーケンスの値も変更しないと、値が重複してしまう…

Oracleユーザの有効期限を変更する

会社の開発環境でOracle 12cのDBを構築し、その確認時に作成したユーザに有効期限がついていた。 意識的にそのような設定をした憶えはなく、期限が過ぎて無効になると困るので、設定の確認と無制限にする対応を行った。以下はその手順。 1. sysdbaでログイン…

Oracleで「user」という名前のテーブルが作成できなかった

最近、既存システムを刷新することになり、その調査で知ったこと。 Oracleを使用することが決まっていて、プロジェクトの要件、使用するフレームワークなどを確認する過程で、試しにuserという名前のテーブルを作成しようとしたところ、 SQL> create table u…

OracleのSQLでUNIX timeを日付に変換する

UNIX timeをOracle DBに保存している古いシステムがある。 システム改修などで確認のためにSQLを使ってそれを持つデータを取得することがあるのだが、秒数ではわかりにくい。 そこで、SQLでUNIX timeを日付に変換する方法。 select to_date('1970-01-01','YY…

Oracle SQL Developerで日付表示の書式を変更する

Macで起動したときの変更方法。Windows版も同様だと思う。(環境がないので試してない) SQL Developerを起動する。 メニューから ツール > プリファレンス を選び、ダイアログを開く。 左の一覧から データベース > NLS を選ぶ。 右の一覧から日付書式(RR-MM-…

sqlplusでsqlファイルを読み込み、sqlを実行する

SQL> @/path/to/foo.sql SQL> @@bar.sql @が1つで、絶対パスで指定する。 @が2つで、相対パスで指定する。 参考 SQL*Plus上でSQLファイルを実行する | Kwappa研究開発室

oracleのアカウントロックを解除する

設定次第だけど、ログインを何度か失敗してしまうと、そのアカウントでのログインが、一定期間できなくなる。 そうなった場合、SYSユーザでログインし、該当するアカウントのロックを解除する。 $ sqlplus '/ as sysdba' SQL> alter user foo account unlock…

SchemaSpyでOracle DBのドキュメントを作成する

SchemaSpyは、データベースのスキーマ情報を解析して、htmlで出力するツール。javaで書かれている。 必要なのもの java5 以上 SchemaSpyのjarファイル(schemaSpy_5.0.0.jar) 解析したいデータベースのjdbcドライバー Graphviz OSはCentOS 5.7で、Oracle DBで…

oracleでトリガー名を確認する、内容を確認する、有効化と無効化

トリガー名を確認する SELECT trigger_name, status FROM user_triggers; 以下、fooという名前のトリガーがあったとして、 トリガーの内容を確認する SELECT line, text FROM user_source WHERE name = 'foo' ORDER BY name, line; lineは行数、textはその行…

sqlplusのプロンプトの変更

sqlplusで特に何も設定していないと、実行したときのプロンプトはこのようになる。 $ sqlplus foo/bar@hoge SQL> 複数の環境を扱っていると、どこに接続しているかわからなくなるので、プロンプトを変更した。 $ mkdir ~/.sqlplus $ vi ~/.sqlplus SET SQLPR…

sqlplusで特定のテーブルの制約を調べる。

user_constraintsテーブルに、テーブル名を指定して実行する。 select constraint_name, status from user_constraints where table_name = 'FOO'; これは、制約名と制約が有効か、無効かを検索するSQL文。 via 表・制約について

replace関数でタブ文字を削除する

SQL> select replace('a' || chr(9) || 'b' || chr(9) || 'c', chr(9)) as str from dual; STR --- abc SQL> via ORACLE/オラクルSQLリファレンス(REPLACE/TRANSLATE)

sqlplusでパッケージのfunctionを実行する

$ sqlplus scott/tiger SQL> variable ret varchar2(100); SQL> execute :ret := foo.bar(1,2,3); 返り値を受け取らずに実行するとエラーになる。 $ sqlplus scott/tiger SQL> execute foo.bar(1,2,3); PLS-00306: 'BAR'の呼出しで、引数の数または型が正し…

sqlplusを使ってpackageのソースを取得する

packageを更新するときに、バックアップ作業で必要になったので。 -- backup.sql spool foo_package.sql column TEXT FORMAT a4000 set linesize 4000 set pagesize 0 set head off set feed off select text from user_source where name = 'FOO' and type …

パッケージのソースをDBから取得する

PACKAGE select text from user_source where name = 'FOO' and type = 'PACKAGE' order by line; PAKCAGE BODY select text from user_source where name = 'FOO' and type = 'PACKAGE BODY' order by line; via ORACLE/オラクル・データディクショ…

データベースリンクの作成

リンク名: foo ユーザ名: bar パスワード; hoge TNSネーム: foobarhoge SQL> create database link foo connect to bar identified by hoge using 'foobarhoge'; 参考 Oracle・Tips集

データベースリンクの確認

$ sqlplus '/ as sysdba' SQL> column owner format a10 SQL> column host format a15 SQL> column username format a15 SQL> column db_link format a25 SQL> set line 400 SQL> SQL> select * from dba_db_links;

シーケンス一覧を取得する

$ sqlplus '/ as sysdba' SQL> set line 256 SQL> set numwidth 32 SQL> col SEQUENCE_OWNER format a14 SQL> col CYCLE_FLAG format a10 SQL> col ORDER_FLAG format a10 SQL> spool scott_sequence.txt SQL> select * from all_sequences where sequence_o…

マニュアル

Oracle 10gでDataGuardを構築したときに参考にしたマニュアル。 Oracle Database SQLリファレンス -- 目次 Oracle Databaseエラー・メッセージ10g リリース1(10.1) SQL*Plusユーザーズ・ガイドおよびリファレンス -- 目次 http://otndnld.oracle.co.jp/doc…