OracleのTips

Oracle関係のメモ。

再起SQLでデータ複製

-- hogeテーブルのid = 1を1000件まで複製するSQL
insert into hoge (
select * from (
  with hoge_cal (id, name) as (
    select
      id, name
    from
      hoge
    where
      id = 1 -- 複製元を指定
    union all
    select
      id + 1
      , name || id + 1
    from
      hoge_cal
    where
      id <= 1000 -- 1000件まで
  )
  select
    id, name
  from
    hoge_cal
  where
    id <>  1 -- 複製元は除く
));

explain plan

# sqlplus内で以下を実行する
-- まずplan tableを作成する
@/apps/oracle/product/10.2.0/db_1/rdbms/admin/utlxplan.sql

-- explain planを取得する
set linesize 300
truncate table plan_table;
explain plan for select aaa,bbb from ccc where ddd = 'eee';
select operation || ' options= ' || options || ' object= '  || object_name || ' position= ' || position || ' cost= ' || cost from plan_table;

-- SQL実行時間計測
set timing on
select * from hoge;
Elapsed: 00:00:01.23

kill session

# sqlplus内で以下を実行する

-- sidとserial#を取得
select sid, serial#, username, program, status from v$session where username = 'hoge';
ID       SERIAL#  USERNAME  STATUS
1256  45167      hoge              XXXX

alter system kill session '1256,45167' immediate;

テーブル別の容量確認

select
  at.table_name
  , trunc(us.bytes/1024/1024,0) tablesize_m
  from
    all_tables 
    join user_segments us on us.segment_name = at.table_name
  where
    owner = 'hoge'
  order by
    tablesize_m desc

既存のテーブル属性を継承したまま、同一表領域上に新しいセグメントを確保

• すべてのデータを再配置して現在使用中のセグメントは解放する
• ただこれをやるとINDEXが壊れる模様。index再生成が必要

  alter table hoge move [ online ]

インデックスを再生成

alter index index_name rebuild

権限周り

-- オブジェクト権限付与
grant select, update, delete, insert, 
on hoge_schema.fuga_table
to sample_user;

-- オブジェクト権限確認
select *
from
  dba_tab_privs
where
  table_name = 'HOGE'
  and owner = 'FUGA'

sqlplus

-- 折返し表示を見やすくする
set linesize 1000
set pagesize1000