テーブルが何バイト使っているか調べる
DBA権限が必要。
SELECT * FROM ( SELECT owner, tablespace_name, segment_name, SUM (bytes) / (1024 * 1024 * 1024) GB FROM dba_segments GROUP BY owner, tablespace_name, segment_name ) ORDER BY GB DESC |
ログインユーザの持ち物に限るのであれば
SELECT * FROM ( SELECT tablespace_name, segment_name, SUM (bytes) / (1024 * 1024 * 1024) GB FROM user_segments GROUP BY tablespace_name, segment_name ) ORDER BY GB DESC |
表領域の残り空き容量を調べる
SELECT space .tablespace_name, TRUNC( space .bytes / (1024*1024*1024)*100)/100 "Capacity GB" , TRUNC(free_space.bytes / (1024*1024*1024)*10000)/10000 "Free GB" , TRUNC(free_space.bytes / space .bytes*100*10000)/10000 "Free Percentage" FROM ( SELECT tablespace_name, SUM (bytes) bytes FROM dba_free_space GROUP BY tablespace_name ) free_space LEFT JOIN ( SELECT tablespace_name, SUM (bytes) bytes FROM dba_data_files GROUP BY tablespace_name ) space ON free_space.tablespace_name = space .tablespace_name ORDER BY space .tablespace_name |
スキーマが何バイト使っているか調べる
select * from ( SELECT owner, SUM (bytes)/ (1024*1024*1024) "GB" FROM dba_segments GROUP BY owner ) order by "GB" desc |
スキーマが表領域単位で何バイト使っているか調べる
select * from ( SELECT owner, tablespace_name, SUM (bytes)/ (1024*1024*1024) "GB" FROM dba_segments GROUP BY owner, tablespace_name ) order by "GB" desc |
SYSAUX表領域があふれる
こんな感じのアラートログがよく出る。
ORA- 1688 : unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_2116900225_21393 by 128 in tablespace SYSAUX ORA- 1688 : unable to extend table SYS.WRH$_ACTIVE_SMMON Flush encountered SYSAUX out of space error( 1688 ). MMON (emergency) purge of WR snapshots ( 21385 ) and older ORA- 1688 : unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_2116900225_21393 by 128 in tablespace SYSAUX ORA- 1688 : unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_2116900225_21393 by 128 in tablespace SYSAUX |
これ、ちょっと調べたら http://oraworklog.wordpress.com/2010/07/24/handling-smoptstat-component-growth-in-sysaux-tablespace/ こういう話があって、Oracleのバグ?パッチも出ているらしいが…… 本番は大丈夫?
外部キーというかFKを全て無効化する
BEGIN FOR c IN (SELECT c.owner, c.table_name, c.constraint_name FROM user_constraints c, user_tables t WHERE c.table_name = t.table_name AND c.status = 'ENABLED' AND c.constraint_type = 'R' ORDER BY c.constraint_type DESC) LOOP dbms_utility.exec_ddl_statement( 'alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' || c.constraint_name); END LOOP; END; / |
よく読まれている記事
この記事を読んだ人は次の記事も読んでいます:
[`google_buzz` not found]
[`yahoo` not found]
[`livedoor` not found]
[`friendfeed` not found]
[`grow` not found]
[`evernote` not found]
関連