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

- [データベースをエクスポートしてダンプファイルにする方法 - Qiita](https://qiita.com/ponsuke0531/items/b0fabe4628c7573b4d6f)

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

- [正規表現がわからないから見たいリンク集 - Qiita](https://qiita.com/ponsuke0531/items/64f6a180a4b699336096)
- [REGEXP_SUBSTR - オラクル・Oracle SQL 関数リファレンス](https://www.shift-the-oracle.com/sql/functions/regexp_substr.html)

## ソートしたい
- [SQLでExcelのセル位置みたいな値をソートする方法 - Qiita](https://qiita.com/ponsuke0531/items/3c2808163efe01c8f731)


## 削除したい

```sql
-- 文字列のの最後にある「_数字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をマスターするための基本と仕組み](https://www.shift-the-oracle.com/sql/regular-expression-metacharacter.html)

# 命名規則
- [田舎暮らしプログラマの技術メモ [ORACLE] テーブル名の規則](https://htech.blog.fc2.com/blog-entry-49.html)
- [Oracle入門 第６章補足](http://rina.jpn.ph/~rance/om/oracle/hosoku06.html)
- [データベース・オブジェクト名および修飾子](https://docs.oracle.com/cd/E57425_01/121/SQLRF/sql_elements008.htm)

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

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

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

- [ＯＲＡＣＬＥ／オラクルＳＱＬリファレンス（LIKE）](http://oracle.se-free.com/dml/01_like.html)
- [全角・半角、大文字・小文字を区別しない検索 - オラクル・Oracleをマスターするための基本と仕組み](https://www.shift-the-oracle.com/sql/national-language-compare.html)

## 正規表現を使ってあいまい検索したい
```sql
select * from {テーブル名} where REGEXP_LIKE({カラム名}, '正規表現のパターン');
```

- [REGEXP_LIKE - オラクル・Oracle SQL 関数リファレンス](https://www.shift-the-oracle.com/sql/functions/regexp_like.html)

# CREATE
```sql
CREATE TABLE {テーブル名} (
    {カラム名1}   {型} [{制約}],
    column_name VARCHAR2(256) NOT NULL,
    [CONSTRAINT {主キー名} PRIMARY KEY({主キーにするカラム名}),]
    [CONSTRAINT {外部キー名} FOREIGN KEY({外部キーにするカラム名}) REFERENCES {親テーブル名}({親テーブルのカラム名})]
)
;
```
# テーブル定義を変更したい(ALTER)
```sql
-- 列を追加したい
alter table {テーブル名} add ({列名} {型});
-- NOT NULL制約の解除
alter table {テーブル名} modify ({カラム名} null);
-- 一意制約追加
alter table {テーブル名} add constraint {制約名} unique({列名,...});
-- 制約削除
ALTER TABLE {テーブル名} DROP CONSTRAINT {制約名};
```

- [テーブル定義の変更（列の追加、変更、削除） - オラクル・Oracle SQL 入門](https://www.shift-the-oracle.com/sql/alter-table-column.html)

# INSERT
```sql
-- 複数データを一気に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](http://neos21.hatenablog.com/entry/2016/06/09/000000)

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

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

- [Limit句をOracle SQLで使う方法 | 株式会社アースリンク](https://www.earthlink.co.jp/engineerblog/intra-mart-engineerblog/2562/)

# ○○の一覧が見たい

```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 = {テーブル名};
```

- [tnsnames.ora - 解決!ORACLE!](http://www.noguopin.com/oracle/index.php?tnsnames.ora)
- [テーブルの一覧を取得する（USER_TABLES / ALL_TABLES） - Oracle - Project Group](https://www.projectgroup.info/tips/Oracle/SQL/SQL000019.html)
- [Oracleでデータの件数の一覧を表示するSQL - 思考停止するな](http://shimonss.hatenablog.com/entry/2017/09/04/225143)
- [制約の確認方法 | Oracle オラクルエンジニア通信](https://blogs.oracle.com/oracle4engineer/oracle-database-v24)


```sql:表領域の一覧が見たい
select tablespace_name,status,contents from dba_tablespaces;
```

- [DBA_TABLESPACES](https://docs.oracle.com/cd/E16338_01/server.112/b56311/statviews_5060.htm)

```sql:REDOログの一覧が見たい
select group#, status,type, member from v$logfile;
```

> REDOログは、リカバリ操作にとって最も重要な構造です。これは、データベースに加えられたすべての変更を発生時に格納する、2つ以上の事前割当てファイルから構成されます。Oracle Databaseの各インスタンスには、インスタンスの障害時にデータベースを保護するためのREDOログが1つずつ対応付けられています。
> [REDOログの管理](http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19224-02/onlineredo.htm)

- [V$LOGFILE - docs.oracle.com](https://docs.oracle.com/cd/E16338_01/server.112/b56311/dynviews_2033.htm)

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

- [Oracleでシノニムを確認する  |  Oracle初心者でもスッキリわかる](https://sql-oracle.com/?p=211)
- [ALL_SYNONYMS - docs.oracle.com](https://docs.oracle.com/cd/E16338_01/server.112/b56311/statviews_2100.htm)

# シーケンスを○○したい
> **順序(シーケンス)の概要**
> 順序とは、複数のユーザーが一意の整数を生成するために使用できるデータベース・オブジェクトです。順序ジェネレータは順序番号を生成し、一意主キーの自動的な生成、および複数の行または表の間のキーの調整に使用できます。
> [順序の管理 - docs.oracle.com](https://docs.oracle.com/cd/E96517_01/admin/managing-views-sequences-and-synonyms.html#GUID-50B77DCA-C44E-488C-B281-7478A01E842E)

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

- [シーケンスの一覧を取得する - Oracle - Project Group](https://www.projectgroup.info/tips/Oracle/SQL/SQL000031.html)
- [ALL_SEQUENCES](https://docs.oracle.com/cd/E57425_01/121/REFRN/GUID-5867804F-1339-4CB6-9A56-6B6DCECB61BB.htm)
- [Sequencesの変更 - docs.oracle.com](https://docs.oracle.com/cd/E96517_01/admin/managing-views-sequences-and-synonyms.html#GUID-5ABC0615-BE0C-48CE-BD18-C86AAF7DF953)

# 日付に悩む
```sql
/*
 * 今日!今!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;
```
- [日付を加算・減算する  |  Oracle初心者でもスッキリわかる](https://sql-oracle.com/?p=741)
- [SYSDATE/SYSTIMESTAMP | 忘れっぽいエンジニアのオラクルＳＱＬリファレンス](http://oracle.se-free.com/dml/07_sysdate.html)
- [whereで日付型のカラムと比較をする - Qiita](https://qiita.com/ponsuke0531/items/57bc035f129922b1b0de)

# クライアントツール
## SQL*Plus
```bash
# 接続
$ 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
```

- [インストールしてみた。 - ponsuke_tarou’s blog](http://ponsuke-tarou.hatenablog.com/entry/2018/09/05/010413?_ga=2.133291342.1577066598.1536108398-46550728.1535950425)
- [sqlplusに接続する２つの方法の違い - Qiita](https://qiita.com/ABCompany1/items/57d667319b1096749aef)
- [SQL*Plusメモ(Hishidama's sqlplus for Oracle9i Memo)](http://www.ne.jp/asahi/hishidama/home/tech/oracle/sqlplus.html)
- [日本語が文字化けするときの対処法](https://blog.mukairiku.net/2014/04/windows%E3%81%AEsqlplus%E3%81%A7%E6%97%A5%E6%9C%AC%E8%AA%9E%E3%81%8C%E6%96%87%E5%AD%97%E5%8C%96%E3%81%91%E3%81%99%E3%82%8B%E3%81%A8%E3%81%8D%E3%81%AE%E5%AF%BE%E5%87%A6%E6%B3%95.html)
- [@と@@の違い](https://oracle.programmer-reference.com/sqlplus-atmark/)
- [SQL*Plusで全体の表示幅とカラムの表示幅を変更する方法 - Qiita](https://qiita.com/ponsuke0531/items/7195cad874a4b4991008)
- [SQL*Plusのページの行数を設定する方法 - Qiita](https://qiita.com/ponsuke0531/items/5edb4b061fe08d3c259f)


## Oracle SQL Developer
- [MacにSQL Developerをインストールする方法 - Qiita](https://qiita.com/ponsuke0531/items/6b094ea102fa78336130)
- [SQLDeveloperで文字がばけばけ - Log.log(work)](http://shinomiya.hatenablog.com/entry/2014/07/22/121311)
- SQLワークシート上でスクロールしようとしたら、スクロールじゃなくて履歴見たいのがどんどん表示されていやだったときの対応方法
    1. [ツール] > [プリファレンス] > [ショートカット・キー] > [ショートカット]列でソートして「Ctrl-下」となっているものを探す
    2. [コマンド]列が「SQL履歴: 次と置換」になっている行を選択して![image.png](https://qiita-image-store.s3.amazonaws.com/0/159761/c4145ef0-d6a5-04d2-65dd-0c99070e9544.png)で削除する
- [自動でCommitはされません - ソフトウェア開発者の日常](https://ajya.hatenablog.jp/entry/2015/12/24/140000)
    - F11でCommitされる
- 日付は時間まで表示させたい
    - [Oracle SQL Developerで時刻を表示する - Qiita](https://qiita.com/akatsuki174/items/cb60d742df049e33485a)
    - ![image.png](https://qiita-image-store.s3.amazonaws.com/0/159761/7b107090-456c-a4a1-86e9-8f0fa1117852.png)
- 行番号を表示したい
    - ![image.png](https://qiita-image-store.s3.amazonaws.com/0/159761/5f7164bd-9d96-a7bc-f96b-fcae370caf54.png)
    - [Oracle SQL Developerで行番号を表示する方法 - 小さい頃はエラ呼吸](http://replication.hatenablog.com/entry/2014/07/07/011058)
- [実行計画を見る方法 - r3negis](http://blogs.cosol.jp/r3negis/2015/12/sqldeveloper-explain-plan-compare/)
    - 実行計画表示 : `SQLを選択 > F10`

# その他
- [DBと仲良くしたいときに役立つリンク集 - Qiita](https://qiita.com/ponsuke0531/items/f34d60e514dde47e81df#%E3%82%AF%E3%83%A9%E3%82%A4%E3%82%A2%E3%83%B3%E3%83%88%E3%83%84%E3%83%BC%E3%83%AB)
- [SIDとは何か : eraxメモ](http://erax.blog.fc2.com/blog-entry-33.html)
- [Oracle SIDを確認・設定する方法  |  Oracle初心者でもスッキリわかる](https://sql-oracle.com/?p=1706)
- [OracleのSQLをPostgeSQLのSQLにしてみる時に役立てたいリンク集 - Qiita](https://qiita.com/ponsuke0531/items/7da0e916023ffcdc7a64)

# 困った
- [ORA-00904: : 無効な識別子です。 - Qiita](https://qiita.com/ponsuke0531/items/912eeaf516ec9897b4e0)
- [ORA-00988: パスワードが指定されていないか、または無効です。- Qiita](https://qiita.com/ponsuke0531/items/97689b3908c918d3ff63)
- [ORA-01045: user username lacks CREATE SESSION privilege - Qiita](https://qiita.com/ponsuke0531/items/998dacd2bfbd4238eae0)
- [ORA-01918: user ユーザ does not exist - Qiita](https://qiita.com/ponsuke0531/items/331576993149e0181758)
- [ORA-12514: TNS:listener does not currently know of service requested in connect - Qiita](https://qiita.com/ponsuke0531/items/3f970a6e84f222727d42)
- [ORA-28001: the password has expired - Qiita](https://qiita.com/ponsuke0531/items/535ba018fed951d921c8)
- `SP2-0310: ファイル"LOGIN.SQL"をオープンできません。`
    - [ファイル名に相対パス（./ でも）を含むと @ コマンドと同じファイル探索方法となりSP2-0310: ファイル"xxxx"をオープンできません。のエラーになる。（マニュアルに詳細説明がないので憶測）](https://www.shift-the-oracle.com/sqlplus/command/start.html)
- [テーブル名は1から30バイトの長さで指定する必要があります。](http://azuma006.hatenablog.com/entry/2016/04/09/233608)
- [Oracle ODBC Driver with same name already exists. - Qiita](https://qiita.com/ponsuke0531/items/4f05ef36157067e60075)
- [sqlplus: command not found - Qiita](https://qiita.com/ponsuke0531/items/0facefd73aa5da0b8024)
- [IMP-00038: Could not convert to environment character set's handle - Qiita](https://qiita.com/ponsuke0531/items/aabb66dee21e2eb8a61b)