More than 3 years have passed since last update.

Oracleで頑張りたいときに役立ちたいリンク集

Last updated at 2020-04-21Posted at 2019-09-03

データベースをエクスポートしてダンプファイルにする方法 - Qiita

文字列を〇〇したい

/* 結合(2つまで) */
CONCAT(str1, str2)
/* 結合(いっぱい) */
str1 || str2 || str3
/* 抜き出したい_'ponsuke'の4文字目から3文字抜く > 'suk'が抜き出せる */
SUBSTR('ponsuke', 4, 3)
/* 抜き出したい_'AB25'の数字抜く > '25'が抜き出せる */
REGEXP_SUBSTR('AB25','[0-9]+')

ソートしたい

SQLでExcelのセル位置みたいな値をソートする方法 - Qiita

削除したい

-- 文字列のの最後にある「_数字1-2文字」を削除する.
SELECT 対象カラム, REGEXP_REPLACE(対象カラム, '_[0-9]{1,2}$', '') AS REGEXP_REPLACE FROM テーブル;

対象カラム	REGEXP_REPLACE
PON-06-01-01_002_01	PON-06-01-01_002
PON-06-02-01_003_01	PON-06-02-01_003
HOGE_INFO	HOGE_INFO

正規表現パターン - オラクル・Oracleをマスターするための基本と仕組み

命名規則

バージョン確認

/* バージョンを確認したい */
select * from v$version;

LIKE句

/* 全半角を区別しないあいまい検索(lowerでもOK) */
select * from {テーブル名} where {対象のカラム} like upper('%{検索したいワード}%');

/* ワイルドカード的な文字を検索するときはESCAPEが必要 */
SELECT * FROM {テーブル名} WHERE 項目ID LIKE '%\_%' ESCAPE '\';

正規表現を使ってあいまい検索したい

select * from {テーブル名} where REGEXP_LIKE({カラム名}, '正規表現のパターン');

REGEXP_LIKE - オラクル・Oracle SQL 関数リファレンス

CREATE

CREATE TABLE {テーブル名} (
    {カラム名1}   {型} [{制約}],
    column_name VARCHAR2(256) NOT NULL,
    [CONSTRAINT {主キー名} PRIMARY KEY({主キーにするカラム名}),]
    [CONSTRAINT {外部キー名} FOREIGN KEY({外部キーにするカラム名}) REFERENCES {親テーブル名}({親テーブルのカラム名})]
)
;

テーブル定義を変更したい(ALTER)

-- 列を追加したい
alter table {テーブル名} add ({列名} {型});
-- NOT NULL制約の解除
alter table {テーブル名} modify ({カラム名} null);
-- 一意制約追加
alter table {テーブル名} add constraint {制約名} unique({列名,...});
-- 制約削除
ALTER TABLE {テーブル名} DROP CONSTRAINT {制約名};

テーブル定義の変更（列の追加、変更、削除） - オラクル・Oracle SQL 入門

INSERT

-- 複数データを一気にinsertしたい
INSERT ALL
INTO {テーブル名} ({カラム1}, {カラム2}, {カラム3}) VALUES ({値1}, {値2}, {値3})
INTO {テーブル名} ({カラム1}, {カラム2}, {カラム3}) VALUES ({値1}, {値2}, {値3})
SELECT * FROM DUAL;

Oracle で複数レコードを一気に INSERT する記法を毎回忘れる - Corredor

OracleではLimit句が使えないのでROWNUMを利用する

-- Limit句が使えたらやりたいイメージ
select * from {テーブル名} order by {ソートしたいカラム} desc limit {表示したい件数};
-- OracleにはLimit句がないからROWNUMを利用する
select * ROWNUM from (select * from {テーブル名} order by {ソートしたいカラム} desc) where ROWNUM <= {表示したい件数};

Limit句をOracle SQLで使う方法 | 株式会社アースリンク

○○の一覧が見たい

/* スキーマ */
select distinct * from all_users where username like '%見たいスキーマ名の一部%';
/* テーブル(自分のやつだけ：user_tables、DBにあるやつ：dba_tables) */
select * from all_tables;
/* ビュー(自分のやつだけ：user_views、DBにあるやつ：dba_views) */
select * from all_views;
/* カラム(自分のやつだけ：user_tab_columns) */
select * from all_tab_columns where table_name = {テーブル名};
/* 外部キー(自分のやつだけ：user_constraints) */
select * from all_constraints where table_name = {テーブル名};
/* テーブルとビューのコメント一覧を見たい */
select * from user_tab_comments order by table_type;
/* テーブルの件数一覧を見たい */
SELECT table_name, TO_NUMBER( EXTRACTVALUE( xmltype( dbms_xmlgen.getxml('SELECT COUNT(*) AS C FROM '||table_name) ), '/ROWSET/ROW/C' ) ) AS num FROM user_tables;
/* テーブルの主キーとかユニークキーとか見たい * CONSTRAINT_TYPE : C(表でのチェック制約) P(主キー) U(一意のキー) R(参照整合性) */
select * from user_constraints where table_name = {テーブル名};

表領域の一覧が見たい

select tablespace_name,status,contents from dba_tablespaces;

DBA_TABLESPACES

REDOログの一覧が見たい

select group#, status,type, member from v$logfile;

REDOログは、リカバリ操作にとって最も重要な構造です。これは、データベースに加えられたすべての変更を発生時に格納する、2つ以上の事前割当てファイルから構成されます。Oracle Databaseの各インスタンスには、インスタンスの障害時にデータベースを保護するためのREDOログが1つずつ対応付けられています。
REDOログの管理

V$LOGFILE - docs.oracle.com

シノニムの一覧が見たい

select * from all_synonyms;
/* 今使っているスキーマのシノニム一覧が見たい */
select * from all_synonyms where table_owner = {今のスキーマ名};

シーケンスを○○したい

順序(シーケンス)の概要
順序とは、複数のユーザーが一意の整数を生成するために使用できるデータベース・オブジェクトです。順序ジェネレータは順序番号を生成し、一意主キーの自動的な生成、および複数の行または表の間のキーの調整に使用できます。
順序の管理 - docs.oracle.com

/*
 * 一覧を見たい
 * all_sequences.last_number : 最後にディスクに書き込まれる順序番号。順序にキャッシュが使用される場合、ディスクに書き込まれる番号は、最後に順序キャッシュに入れられた番号。この番号は、前回に使用された順序番号より大きくなる。
 */
select * from all_sequences;
/* 今の番号を見たい */
select {シーケンス名}.currval from dual;
/* 番号を上げたい */
select {シーケンス名}.nextval from dual;
/* もう一度初めから始めたい */
alter sequence {シーケンス名} restart;
-- シーケンス削除
DROP SEQUENCE {シーケンス名};

日付に悩む

/*
 * 今日!今!now!
 * sysdateはDATE型で、systimestampはTIMESTAMP型
 */
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;
select to_char(systimestamp,'yyyy/mm/dd hh24:mi:ss.ff3') from dual;
/* 日付計算したい. */
select sysdate {+-演算子} {日数} from dual;

クライアントツール

SQL*Plus

# 接続
$ sqlplus ユーザ/パスワード@IPアドレス:ポート/SID

# 接続情報の確認
SQL> def
DEFINE _DATE           = "19-01-15" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "IPアドレス:ポート/SID" (CHAR)
DEFINE _USER           = "ユーザ名" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)
DEFINE _EDITOR         = "Notepad" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)
DEFINE _O_RELEASE      = "1202000100" (CHAR)

# 終了
SQL> exit

Oracle SQL Developer

MacにSQL Developerをインストールする方法 - Qiita
SQLDeveloperで文字がばけばけ - Log.log(work)
SQLワークシート上でスクロールしようとしたら、スクロールじゃなくて履歴見たいのがどんどん表示されていやだったときの対応方法
1. [ツール] > [プリファレンス] > [ショートカット・キー] > [ショートカット]列でソートして「Ctrl-下」となっているものを探す
2. [コマンド]列が「SQL履歴: 次と置換」になっている行を選択してで削除する
自動でCommitはされません - ソフトウェア開発者の日常
- F11でCommitされる
日付は時間まで表示させたい
- Oracle SQL Developerで時刻を表示する - Qiita
行番号を表示したい
- Oracle SQL Developerで行番号を表示する方法 - 小さい頃はエラ呼吸
実行計画を見る方法 - r3negis
- 実行計画表示 : SQLを選択 > F10

その他

困った

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up