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ユーザー:管理情報が含まれる表やビューを所有している。

セキュリティの理由から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でデータベース開始する。