Oracle DB SQL(更新中) - user000422/0 Wiki

未分別

-- インデックス 作成
CREATE INDEX sample_index -- インデックス名
ON sample_table( -- テーブル名
  user_id -- インデックス対象カラム
);

-- インデックス削除(インデックス名を指定)
DROP INDEX sample_index;

SELECT

-- CONCAT カラム連結
SELECT CONCAT(sample_id, sample_name) FROM sample_table; -- 基本型
SELECT * FROM ORDER BY CONCAT(sample_id, sample_name) ASC; -- ??

ROWNUM … 検索結果上限指定

SELECT * FROM sample_table WHERE ROWNUM <= 1

CREATE TABLE

CREATE TABLE sample_table(
  name varchar2(255)
)
ROW STORE COMPRESS BASIC; -- 表圧縮適用
-- テーブルをコピー
CREATE TABLE to_table AS SELECT * FROM from_table;

SYSTEM

-- EM Express ポート番号取得
SELECT dbms_xdb_config.gethttpsport FROM DUAL;

-- 表領域 情報取得
-- カラム「TABLESPACENAME」… 表領域名
-- カラム「SEGMENT_SPACE_MANAGEMENT」… セグメント管理方法
SELECT * FROM DBA_TABLESPACES;

-- 制御ファイルのフルパス取得
SELECT name FROM V$CONTROLFILE;

ALTER ※読み「おるたー」(Oracle公式セミナーより)

ALTER TABLE テーブル定義変更

-- 制約定義 CONSTRAINT:制約名
ALTER TABLE sample_table ADD CONSTRAINT sample_pk PRIMARY KEY(sample_id);

ALTER SYSTEM 初期化パラメータ変更

-- 基本型
ALTER SYSTEM SET db_cache_size = 150M;

-- SCOPE指定 BOTH 稼働中インスタンスとSPFILE両方に適用される
-- BOTHと省略は同じ扱い
ALTER SYSTEM SET db_cache_size = 150M SCOPE = BOTH;
ALTER SYSTEM SET db_cache_size = 150M;

FLASHBACK(フラッシュバック)

-- DROP TABLE を取り消す
FLASHBACK TABLE sample_table TO BEFORE DROP;

ユーザー

ユーザー作成

CREATE USER sample_user
  IDENTIFIED BY samplepass      -- パスワード
  DEFAULT TABLESPACE sample_tbs -- デフォルト表領域
  TEMPORARY TABLESPACE temp     -- デフォルト一時領域
;

アンロック

ALTER USER sample_user ACCOUNT UNLOCK;

ユーザーの削除 オブジェクトを所持しているユーザーを削除する場合は、CASCADEオプションが必要

DROP USER sample_user CASCADE;

表領域(TABLESPACE)

TABLESPACE 確認

SELECT * FROM DBA_TABLESPACES; -- 表領域の情報を確認(データディクショナリビュー)

SELECT * FROM DBA_FREE_SPACE; -- 表領域の空き領域を確認(データディクショナリビュー)

TABLESPACE 作成

CREATE TABLESPACE sample_tablespace
DATEFILE 'sample_path/sample_tablespace.dbf' -- データファイル
SIZE 100M -- 初期表示サイズ
AUTOEXTEND ON; -- 自動拡張

TABLESPACE 拡張

-- 既存のデータファイルを手動で拡張
ALTER DATABASE DATAFILE 'sample_path/sample_tablespace.dbf'
RESIZE 1G; -- 拡張サイズ

-- 既存のデータファイルに自動拡張設定
ALTER DATABASE DATAFILE 'sample_path/sample_tablespace.dbf'
AUTOEXTEND ON
NEXT 512k MAXSIZE 500M;
-- TABLESPACE 削除
ALTER TABLESPACE sample_tablespace OFLINE; -- 表領域がアクティブの場合、オフラインに
DROP TABLESPACE sample_tablespace INCLUDING CONTENTS;

PL/SQL(拡張SQL)

DECLARE … 宣言部 変数などを定義 BEGIN~END … 処理部

SELECT INTO … SELECT結果を変数に代入(主にMAXやCOUNTで使う) 結果が0件、複数件の場合はエラー

-- SELECT INTO 基本型
DECLARE
 c_userId sample_table.userId%TYPE; -- 変数宣言(SELECT結果を代入)
BEGIN
  SELECT userId
  INTO c_userId -- 
  FROM sample_table;
END

CASE … 分岐 WHEN … 条件式、THEN … 結果 単純CASE式では複雑な条件が指定できない IFより簡潔に記述できる ORDER BYに対しても使える

-- 単純CASE式 基本型
SELECT
  userId,
  CASE color -- 対象カラム
    WHEN 'red' THEN 'apple' -- 条件
    ELSE 'no fruits' -- 条件に一致しなかった場合
  END AS result -- 出力カラム(自身で定義)
FROM sample_table;

-- 検索CASE式 基本型
SELECT
  userId,
  CASE
    WHEN age < 19 THEN 'young' -- 条件
    ELSE 'old' -- 条件に一致しなかった場合
  END
FROM sample_table;

リスナー制御ユーティリティ(lsnrctl)

リスナー名がデフォルトの場合はリスナー名省略可

rem リスナーを起動
lsnrctlt start

rem リスナーを停止
lsnrctlt stop

その他

COALESCE … カラムnullの場合、第二引数の値を設定

-- 第一引数:カラム、第二引数:nullだった場合の値
SELECT COALESCS (userId, 1) FROM sampleTable;

並べ替えした検索結果の件数指定(ORDER BY したものを ROWNUM)

SELECT * 
FROM (
  SELECT * 
  FROM sample_table
  ORDER BY sample_column
)
WHERE ROWNUM <= 1