カウンタ
〜 ORACLEいろいろメモメモ 〜
メニュー
広告
広告
5.ちょっとしたSQL達!!でも忘れちゃう。。。
alter table TABLENAME add column COLUMN_NAME;
◇ 既存のカラム名の変更
alter table TABLENAME rename column OLDNAME to NEWNAME;
◇ 既存カラムの削除
alter table TABLENAME drop column TARGET_COLUMN_NAME;
◇ 既存のテーブル名の変更
alter table OLD_TABLENAME rename to NEW_TABLENAME;
□ カラムの長さ変更
alter table TABLENAME modify COLNAME VARCHAR2(30);
TOP
alter tablespace 表名
add datafile '/arc/space/WORK_XXXX_10.dbf' SIZE 2048M ,
'/arc/space/WORK_XXXX_11.dbf' SIZE 1024M
autoextend on を指定すると自動拡張となります。
TOP
一時表領域とはソート処理等で使われる表領域のことです。
9iではデフォルトの一時表領域として指定された表領域はそのままでは削除できないの
で、以下のようなやり方でどうでしょう。
/* 仮の一時表領域を作成 */
create temporary tablespace tempX tempfile '/oracle2/tempX.dbf' size 256M;
/* デフォルトの一時表領域を変更 */
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempX;
/* 肥大した一時表領域をdrop */
drop tablespace TEMP_XXXX;
この後、一時表領域で使用していたファイルをOSコマンドで削除
/* 一時表領域を再作成する(autoextend, mazsize は適宜)*/
create temporary tablespace TEMP_XXXX
tempfile '/data3/oracle/XXXX/space/TEMP_XXXX_1.dbf' size 900M
autoextend off
,'/data3/oracle/XXXX/space/TEMP_XXXX_2.dbf' size 900M
autoextend off
,'/data3/oracle/XXXX/space/TEMP_XXXX_3.dbf' size 900M
autoextend off
,'/oracle2/TEMP_XXXX_4.dbf' size 900M
autoextend on
next 32768K
,'/oracle2/TEMP_XXXX_5.dbf' size 900M autoextend on next 32768K
,'/usr/local/oracle/TEMP_XXXX_6.dbf' size 900M
autoextend off;
/* デフォルトの一時表領域を再度変更 */
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_XXXX;
/* 仮の一時表領域をdrop */
drop tablespace tempX;
TOP
select * from sys.dba_free_space;
もう少し解り易い出力にすると
select tablespace_name,file_id,
count(*) "Pieces",
max(blocks) "Max",
min(blocks) "Min",
avg(blocks) "Ave",
sum(blocks) "Total"
from dba_free_space
group by tablespace_name,file_id
order by tablespace_name,file_id;
です。
これにより出力された値が平均的になっているかどうかで判断してください
TOP
select max(decode(name,'sorts (disk)',value)) disk_sort,
max(decode(name,'sorts (memory)',value)) memory_sort,
to_char(max(decode(name,'sorts (disk)',value))*100/
max(decode(name,'sorts (memory)',value)),9990.99) dsort_ratio
from v$sysstat;
TOP
値が多いとログスイッチが頻繁に起きている
select * FROM v$sysstat where name='redo log space requests';
待機回数:0が望ましい
select * FROM v$sysstat where name='redo buffer allocation retries';
TOP
grant select,insert,update on 表名 to スキーマ
select,insert,updateは権限名です。
その他の権限の時はここを皆さんで読み替えてください
TOP
shutdown
abort 強制終了
immediate 接続ユーザがいなくなり次第終了
transactional トランザクションがなくなり次第終了
10gよりゴミ箱機能(フラッシュバック・ドロップ機能)が新規にできました。
これは、表をDROPしただけでは実際には表はリネームされただけでゴミ箱の中(削除前と同じ表領域)に残り続けるというものです。
これが邪魔な場合は、
PURGE RECYCLEBIN ;
を実行することによりそのスキーマのゴミ箱の中のテーブルが全て完全に消えます。
また、ほっておいても表領域が圧迫されと自動的に消えます。
PURGE TABLE 削除テーブル名
PURGE INDEX 削除索引名
PURGE TABLESPACE 削除表領域名
とすることにより、ピンポイントでゴミ箱から消す事も可能です。
下記のようにDROP TABLEでPURGE句を指定することにより
ゴミ箱を経由せず、今までどおりに表を削除することも可能です。
DROP 削除テーブル名 PURGE ;
また、以下のSQL文でリカバリできます。
FLASHBACK TABLE 削除前テーブル名 TO BEFORE DROP ;
□ 新機能各種(10g以降)
TOP
ここではSQL*Plusコマンドでの各種設定をご紹介いたします。
それぞれSQL*Plusが終了するまで有効です。
・setコマンドについて
このコマンドはSQL*Plus内における、環境設定を指定するものです。
コマンド一覧
コマンド
オプション
デフォルト値
説明
set linesize 文字数
数値
80
1行あたりの表示文字数を設定
set colsep 文字
文字
スペース
区切り文字の指定
set timi on
on/off
off
SQL実行時間の表示
set autot on
on/off
off
SQLの実行計画と統計の出力
set feedback 行数
set feed 行数
数値
6
指定行数以上だと何行取得されたか表示する
set feedback on
on/off
on
結果が何行取得されたか表示するか
set heading on
on/off
on
結果に列名を表示するか
set long バイト数
数値
80
LONG型のデータの最大表示バイト数
set pagesize 行数
数値
24
結果表示の1ページあたりの行数を指定
set pagesize 行数
数値
24
結果表示の1ページあたりの行数を指定
set pause off
on/off
off
結果が1画面で収まらない時に画面をスクロールさせるか
set termout on
on/off
on
結果を表示するかどうか
set echo on
on/off
on
スクリプトファイル内の実行コマンドを表示するか
set numwidth 文字数
数値
10
数値型の結果表示幅の設定
set time off
on/off
off
コマンドの前に現在時刻を表示するか
show コマンド名
コマンド名
コマンドの設定を確認する
(例)show linesize
・columnコマンドについて
このコマンドはSQL*Plus内における、列ごとの表示書式を指定するものです。
コマンド一覧
コマンド
オプション例
説明
column 列名 clear
表示書式の削除
column 列名 format オプション
列の表示書式を指定
a10
10バイトで表示
99999
5桁で表示
09999
4桁なら0で埋めて5桁に
$9999
頭に$をつける
L9999
各国の通貨記号の付与
column 列名 heading 文字列
表示する列名変更
column 列名 JUSTIFY オプション
left
表示列を左寄せ
center
表示列をセンタリング
right
表示列を右寄せ
column 列名 noprint
列を結果表示しない
column 列名 null 文字列
NULLの時文字列に表示を置き換える
column 列名 print
列を表示する
column 列名 truncated
表示幅より長い結果の場合残りの文字を切り捨てる
column 列名 wrapped
表示幅より長い結果の場合残りの文字を折り返す
column 列名
設定の確認
clear column
全ての設定削除
・ヘッダー・フッターについて
このコマンドはSQL*Plus内における、ヘッダーとフッターの表示書式を指定するものです。
コマンド一覧
コマンド
説明
ttitle 文字列
文字列をヘッダーの指定
ttitle off
ヘッダーの解除
btitle 文字列
文字列をフッターの指定
btitle off
フッターの解除
・編集バッファについて
このコマンドはSQL*Plus内でバッファを使いSQLを編集するものです。
コマンド一覧
コマンド
説明
ed
最後に実行したSQLをafiedt.bufとして編集
ed ファイル名
ファイルをOSのエディタで編集
append 文字列
a 文字列
編集バッファの現在の行に文字を追加
change 旧文字 新文字
c 旧文字 新文字
編集バッファの文字を置き換える
del 先頭行 最終行
先頭〜最終までの編集バッファを削除。
行を指定しないと現在の行が削除
run
編集バッファのSQLを実行
save ファイル名
バッファ内の文をファイルに出力
・ファイル操作について
このコマンドはSQL*Plus内でOS上のテキストファイルを使用するものです。
コマンド一覧
コマンド
説明
spool ファイル名
ファイルへ出力を保存開始
spool off
ファイルへの出力を終了
spool out
ファイルへの出力を終了し印刷
start ファイル名
@ファイル名
ファイル内のSQLを実行
get ファイル名
ファイル内を編集バッファに取り込む
TOP
運用していくにつれ、接続しているセッションの数を確認したり、接続中のセッションを
強制終了したりしなければならない事が多々あります。
そんなときは、
select username,sid,serial#,status from v$session;
で、「sid,serial#」を確認し、
alter system kill session 'sid,serial#';
で強制的に接続を終了させます。
TOP
「テーブルに条件に合うデータが有ったらUPDATEでなかったらINSERT」
という、ある種IF文的な使い方が出来るのがMERGE文です。
PL/SQLでLOOPするより高速で、また、1SQL文で処理するためアクセスパフォーマンスも
良好です。
merge into 表1 using 表2
on ( 表1.key = 表2.key )
when matched then update set 表1.col = '値'
when not matched then insert(表1.key,表1.col) values(210,'値')
です。説明としては、
1行目:「表1に対して表2を使ってマージします」
2行目:「表1のkeyの値と表2のkeyの値を比較します」
3行目:「表1のkeyと同じ値が表2のkeyあれば、それに対して記述したUPDATEを実行」
4行目:「表1のkeyと同じ値が表2のkeyなければ、記述したINSERTを実行」
となります。
(10g以降)
ちなみに、9iではUPDATE文・INSERT文が使えますが、
10gではそれに加え
「条件付(whereがある)のDELETE文」
が
「when matched then」
配下でかつ、
「UPDATE句がある」
場合のみで使えます
merge into 表1 using 表2
on ( 表1.key = 表2.key )
when matched then
update set 表1.col = '値'
delete where 表1.key = 291
上記のSQL文ですと「表1.key = 表2.key」に合致するデータは「表1.col = '値'」にUPDATEされ、
そのうち「表1.key = 291」のデータはDELETEされます。
□ 新機能各種(10g以降)
TOP