ORACLE関係

Oracle SQL Plus コマンド

SQL Plusを使用してDBを操作するには、Oracle Net8 assistant(プログラム名)で適切な設定をする必要があります。 (Oracle 8i 8.1.7.0.1)

Oracle Net8 assistantでの設定手順
サービス名　｜　編集　｜　作成....

起動

sqlplus

SQL Plusの終了

SQL> exit

外部エディタを使用してSQLファイルの作成

SQL> ed FileName
通常の保存先：c:\orant\bin\FileName.sql

ファイルに保存したSQL文を実行

SQL> @FileName

作成した表の確認

SQL> describe TableName

作成した表を削除

SQL> drop table TableName

入力したSQL文をファイルに保存

SQL> save FileName

実行結果（log）をファイルに保存開始/終了

SQL> spool FileName
SQL> spool off

直前に入力/実行したSQL文を実行

SQL> /

現在ログイン中のユーザーの表示

SQL> show user

読み込まれているSQL文の表示

SQL> list

接続/切断

SQL> connect UserName/Password@InstanceName
SQL> disconnect

一行表示/消去

SQL> l LineNumber
SQL> del LineNumber

文字列の置き換え

SQL> c/wrong_word/right_word

編集行の移動

行番号を入力しリターン

Server Manager　の操作

起動/終了

SVRMGR30
EXIT

データベースの起動/停止

SVRMGR> connect internal/oracle
SVRMGR> startup
SVRMGR> shutdown

LSNRCTL の操作

起動/終了

LSNRCTL80
EXIT

状況/起動/停止

LSNRCTL> status
LSNRCTL> start
LSNRCTL> stop

SQL文

データディクショナリの表示

select * from dict
where table_name like 'USER%';	（そのユーザに関する情報）
where table_name like 'ALL%';	（そのユーザがアクセス可能なすべての情報）
where table_name like 'DBA%';	（データベース全体に関する情報）

ユーザの作成/変更/削除/確認

create user UserName
identified by Password
default tablespace AREA1
temporary tablespace TEMP
quota unlimited on AREA1
quota 2M on AREA2;

alter user UserName
identified by Password1
default tablespace AREA2
temporary tablespace TEMP2
quota unlimited on AREA2;

drop user UserName cascade;

select username,default_tablespace,temporary_tablespace from user_users;
select * from user_ts_quotas;
select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users;

システム権限の付与/取り消し/確認

grant create session,create user
to UserName;

revoke create user
from UserName;

select * from session_privs;

オブジェクト権限の付与/取り消し/確認

grant select,alter,delete,index,insert,references,update,execute
on ObjectName
to UserName;
all, grant update(name,id)...

revoke select
on ObjectName
from UserName;

select * from user_tab_privs_made;
select * from user_tab_privs_recd;

ロールの付与/取り消し/確認

grant connect,resource,dba to UserName;

revoke dba from UserName;

select * from session_roles;

表の作成

create table TableName
( a	number(4) constraint a_pk
	primary key
, b	varchar2(30)
, c	number(7.2) constraint c_check
	check( c >= 0 and c <200 )
, d	number(3) constraint d_fk
	references TabeleName(ID)
, e	number(3) constraint E_NN not null
, f	number(3) constraint F_U unique
, g	number(4) default 230
);

create table TableName
( a	number(4)
, b	varchar2(30)
, c	number(7.2) 
, d	number(3)
, e	number(3) constraint E_NN not null
, f	number(3)
, g	number(4) default 230
, constraint a_pk primary key (a)
, constraint c_check check( c >= 0 and c <200 )
, constraint d_fk (d) references TabeleName(ID)
, constraint F_U unique(f)
);

制約の確認

select owner,constraint_name,constraint_type,table_name,
search_condition,r_constraint_name,status
from user_constraints;

select owner,constraint_name,table_name,column_name
from user_cons_columns;

select data_default
from user_tab_columns;

表の削除

drop table TableName cascade constraints;

truncate table TableName;

delete from TableName;

表名の変更

rename TableName to TableName2;

列の追加/変更

alter table TableName
add ( ii number(3)
constraint ii_nn not null
constraint ii_u unique
);

alter table TableName
modify (name constraint name_nn not null);

制約の追加/削除

alter table TableName add
constraint NUM_check
check( NUM >0 );

alter table TableName drop constraint NUM_check;

既存の表から新しい表を作成

create table TableName as
select a,b from TableName2
where c=10;

create table CopyTable as
select * from OriginalTable
where 1=2;

ビューの作成/削除/確認

create or replace view ViewName as
select a,b from TableName
where c=10;

drop view ViewName;

set long 800
set arraysize 1
select view_name,text from user_views;

索引の作成/削除/確認

create index IndexName
on TableName(row1,row2)
tablespace TableSpaceName;

drop index IndexName;

select index_name,table_name,uniqueness from user_indexes;
select index_name,table_name,column_name from user_ind_columns;

シノニムの作成/削除/確認

create public synonym SynonymName
for Skima.Object;

drop public synonym SynonymName;

select * from user_synonyms;	(private)
select * from all_synonyms;	(public)

順序の作成/削除/キャッシュの変更

create sequence SequenceName
start with 5
increment by 2
nomaxvalue
nominvalue
nocycle
nocache;

drop sequence SequenceName;

alter sequence SequenceName cache 3;

検索/追加/更新/削除

select distinct a, b from TableName order by a desc(asc)
select sum(SAL) from emp        min,max,avg,
select ename,job from emp where job like '%A%'
select ename from emp where sal in (150,200,300)
select ename from emp where mgr in not null

insert into TableName (Col1,Col2) values (12,'aa');
insert into emp2
        select * from emp
        where job='manager';
insert into emp2(empno,ename,job)
        select empno,ename,job from emp;
insert into emp
	(empno,ename)
	values(41,null)

update TableName set Col1=15, Col2='bbb' where Col0=8;

delete from TableName where Col0=8;

管理用SQL文

データベースファイルの情報を見る

select * from sys.dba_data_files ; 

表領域情報の確認

select * from sys.dba_tablespaces ; 
select tablespace_name, file_id,
count(*) "PIECES",
max(blocks) "MAXMUM",
min(blocks) "MINIMUN",
avr(blocks) "AVERAGE",
sum(blocks) "TOTAL"
from sys.dba_free_space
group by tablespace_name, file_id ;

ユーザオブジェクトのリスト

select * from user_objects ; 

現在どのユーザがどのマシンでアクセスしているか確認

select username, program, terminal, osuser, status
from v$session where user# != 0 ; 

REDOログの状態を確認

select * from v$log ; 

REDOログファイルの確認

select * from v$logfile ; 

REDOログファイルの削除/作成

alter database drop logfile group 1 ;
alter database add logfile group 1 '/ora/data/log1.log' size 50K ; 

パフォーマンスチューニング

ライブラリキャッシュのチェック

select sum(pins),sum(reloads),
sum(reloads)/(sum(pins)+sum(reloads))* 100
from v$librarycache ;

ディクショナリキャッシュのチェック（5〜10%を越えないように）

select sum(getmisses)/(sum(gets)+sum(getmisses))*100
from v$rowcache;

ソート領域のチューニング（この値が高いとメモリの使用効率が悪い。initxxx.ora の SORTB_AREA_SIZE を調整）

select v2.value/(v1.value+v2.value)*100
from v$sysstat v1, v$sysstat v2
where v1.statistic# = 161
and   v2.statistic# = 162;

インデックスの拡張回数の上限に達した時の対処

select count(*) from DWC207T;
select count(*),sum(bytes) from user_extents
where segment_name='PK_DWC207T';
alter table DWC208T drop primary key;
alter table DWC208T add constraint PK_DWC208T
prinmary key ( P_N,REV_NO,COOC,TRN_DATE)
using index pctfree 5 tablespace JEIS_INDEX
storage ( initial 40M next 1M );

リンク

オラクルＤＢ虎の穴

チューニング等の情報あり。

Insight Technology

メールマガジンがあります。オラクル関連製品を開発しています。