渋谷生活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でデータベース開始する。
- 100825 reads