Oracle SQL Plus コマンド - lookworld @ 簡単メモ～

SQL Plusを使用してDBを操作するには、Oracle Net8 assistant(プログラム名)で適切な設定をする必要があります。 (Oracle 8i 
8.1.7.0.1)
Oracle Net8 assistantでの設定手順
サービス名　｜　編集　｜　作成.... 
起動 
sqlplusSQL 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 );