OracleのSQLコマンド

よく使うOracle8iのコマンドをメモ。Oracle10iでもほぼ同じ。

 

 

 

 


【SQL*Plus編】

 

■シェルからSQL/PLUSを使う

$ sqlplus /nolog

 

■ログインする

いくつかの方法がある。 

conn sys as sysdba
conn / as sysdba
conn sys/change_on_install
conn system/manager
conn scott/tiger
conn scott

データベース管理者ユーザー

セキュリティの理由からSYSユーザーとSYSTEMユーザーの初期パスワードは、早めに変更した方がよい。（passwordとコマンドを入力するだけで簡単に変更できる）

 

 


【Oracle管理編】

データベースを作成したら、表領域を確認して、ユーザーの作成を行う。

管理の作業は、管理者でログインして行う。

$ sqlplus /nolog
SQL> conn system/manager 　（Oracle 8i）
SQL> conn sys as sysdba　（Oracle 10g）

 

■表領域の確認

SQL> SELECT tablespace_name, status, contents FROM dba_tablespaces;

 

■表領域とデータ・ファイル情報の取得

SQL> SELECT file_name, tablespace_name FROM dba_data_files;

 

■ユーザーのデフォルト表領域

SQL> SELECT username, default_tablespace FROM dba_users;

 

■ユーザーの一覧

 SQL> SELECT username, default_tablespace FROM dba_users;

 

■ユーザー作成

CREATE USER suzuki     
  IDENTIFIED BY xxxxxx
 DEFAULT TABLESPACE users
 TEMPORARY TABLESPACE temp
 QUOTA unlimited ON users;

 

QUOTAを必ず付ける。QUOTA 15m ON usersなど。
後から変更もできる。（Oracle 8i） 

ALTER USER suzuki QUOTA unlimited on temp;
ALTER USER suzuki QUOTA unlimited on rbs;

 

■ユーザー作成後に権限の付与（ロールを与える。ざっくりと指定。）

GRANT connect, resource TO suzuki;

 これを付与しないと、せっかくユーザを作ってもログインさえできない。

 

■ユーザー作成後に権限の付与（システム権限を与える。細かく指定。）

アプリケーション開発者向けの場合、 

SQL> GRANT CREATE SESSION, CREATE TABLE, 
  2  CREATE SEQUENCE, CREATE VIEW, CREATE PROCEDURE
  3  TO suzuki;

 

■権限の確認

 自分が持っている権限を確認できる。 

SQL> SELECT * FROM session_privs;
PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
CREATE TABLE
CREATE ANY TABLE
CREATE CLUSTER
CREATE ANY INDEX
CREATE SYNONYM
CREATE ANY SYNONYM
CREATE VIEW
CREATE ANY VIEW
CREATE SEQUENCE
CREATE DATABASE LINK

 

 

■ユーザーの削除

DROP USER suzuki;
 ユーザーの所有するスキーマ・オブジェクトも一緒に削除
 DROP USER suzuki CASCADE;

 注意：接続しているユーザーがいたらダメ。

 

■誰が何のプロファイルを使用しているか

SELECT username, profile
 FROM dba_users;

 

■新規にテーブルを作成する 

CREATE TABLE emp
 (id      number,
 last_name  varchar2(100));

 

■既存のテーブルから新しいテーブルを作成する

部署の番号が20のものを拾って、新しいemp表を作る。

CREATE TABLE new_emp AS
SELECT * FROM emp
WHERE deptno = 20;

 

■テーブルを一覧する

SELECT * FROM tab;

よく使う。手クセになるコマンドのひとつ。

まず、これで手持ちのテーブルを一覧して、次に列の一覧でフィールド名を確認する。 

 

■列の一覧

DESC emp;

これも手クセになるはず。 

 

■列を追加する 

ALTER TABLE emp
 ADD (first_name varchar2(10));

列の一覧で確認し、足りないフィールドがあったらこれで足していく。 

 

■列を削除する 

ALTER TABLE emp
 DROP (first_name);

 

■列を変更する 

ALTER TABLE emp
 MODIFY (first_name varchar2(30));

 

■一意キーを作成する 

ALTER TABLE emp
 ADD CONSTRAINT id_pk PRIMARY KEY(id);

 

■順序（主キー値）の作成 

CREATE SEQUENCE emp_id_seq
 START WITH 1
 INCREMENT BY 1
 NOCACHE
 NOCYCLE;

 

■順序（主キー値）の確認

SELECT sequence_name, last_number
 FROM user_sequences;

 

 


【Oracle SQL編】


 ■日付で検索する

SELECT id, name FROM emp
WHERE birthday = TO_DATE( '1980/09/28' , 'YYYY/MM/DD' ) ; 

SELECT id, name FROM emp
WHERE TO_CHAR( birthday, 'YYYYMMDD' ) = '1980/09/28' ;

 

■期間で検索する

SELECT id, name FROM emp
WHERE birthday >= TO_DATE( '1980/09/28' , 'YYYY/MM/DD' ) 
AND birthday < TO_DATE( '1981/09/28' , 'YYYY/MM/DD' ) ;

 

 

■インサート文

INSERT INTO emp
(id, name)
VALUES
( 3, '高橋' );

 順序を使うとき、

INSERT INTO emp
(id, name)
VALUES
( emp_seq.nextval, '高橋' );

 

■データの修正

日付を一括変更。ついでにメモ欄を空にする。

UPDATE emp
SET 
modify_date = TO_DATE('2004/12/16', 'YYYY/MM/DD') , 
memo = NULL ;

 

■データの修正

'Sales' を 'SALE' に変更。 

SQL> UPDATE emp
  2  SET 
  3  dept = 'SALE'
  4  WHERE dept = 'Sales';

 

■文字列を数値に変換

'00123'という文字列は、数字の123に変換したい。

SQL> UPDATE emp
  2  SET
  3  num_no = to_number( str_no );

 

■数値を文字列に変換 

数字の123を文字列の'123'に変換したい。

SQL> UPDATE emp
  2  SET
  3  str_no = to_char( num_no );

 

■行を削除する

 SQL> DELETE FROM emp
  2  WHERE id = 300;

 

 

 

 


【Oracle エラー編】


 ■インサート文でエラー 

　　ORA-01552: SYSTEM表領域でない表領域:
 　　USERSにシステム・ロールバック・セグメントは使用できません。

原因として、ロールバックセグメントがオフラインになっている可能性がある。 

SELECT segment_name, status
 FROM dba_rollback_segs;
  SEGMENT_NAME                   STATUS
 ------------------------------ ----------------
 SYSTEM                         ONLINE
 R01                            OFFLINE
 R02                            OFFLINE
 R03                            OFFLINE
 R04                            OFFLINE

STATUSがOFFLINEになっていたので、
/u01/app/oracle/product/8.1.5/dbs/initXXX.oraを編集。
# rollback_segments = (r01, r02, r03, r04)のコメントを外して有効にする。
そして、dbshutでデータベース停止、dbstartでデータベース開始する。