监视表空间、段和索引

监视表空间、段和索引

监视表空间

select tablespace_name,sum(bytes)

from dba_free_space group by tablespace_name;

TABLESPACE_NAME SUM(BYTES)

UNDOTBS1 8978432

SYSAUX 13893632

AAA 983040

R 2031616

USERS 89587712

DATA 51773440

WWW 20840448

SYSTEM 8716288

A 4546560

B 10354688

各表空间的剩余空间

第 31 页

col metrics_name format a30

col warning_value format a10

col critical_value format a10

select metrics_name,warning_operator,warning_value,critical_operator,critical_value,consecutive_occurrences

from dba_thresholds where metrics_name='Tablespace Space Usage';

METRICS_NAME WARNING_OPER WARNING_VA CRITICAL_OPE CRITICAL_V CONSECUTIVE_OCCURRENCES

Tablespace Space Usage GE 85 GE 97 1

这是一个表空间使用的阀值，当空间使用超过 85 时警告，超过 97 时达到危险，每次都产生报警（DBA_OUTSTANDING_ALERTS）。

create tablespace tbs datafile 'E:\OracleFiles\DatabaseFiles\WWW\TBS01.DBF' size 1M;

create table t (s char(2000)) tablespace tbs;

begin

for i in 1..600 loop

insert into t values('A');

end loop;

end;

/

col object_name format a20

col object_type format a10

col reason format a40

col host_nw_addr format a20

col time_suggested format a40

select object_name,object_type,reason,time_suggested,host_nw_addr

from dba_outstanding_alerts;

OBJECT_NAME OBJECT_TYP REASON TIME_SUGGESTED HOST_NW_ADDR

TBS TABLESPACE 表空间 [TBS] 已占用 [93 percent] 02-7 月 -08 02.56.19.703000 下午 +08:00 192.168.1.19

TBS TABLESPACE 表空间 [TBS] 只有 [0 megabytes] 空闲空间 02-7 月 -08 02.56.19.718000 下午 +08:00 192.168.1.19

出现警报，列出了对象名、对象类型、警报理由、最后一次修改时间、ip 地址等信息

当警报解除后，会把此报警记入 DBA_ALERT_HISTORY。

监视段

select segment_name,bytes from dba_segments where rownum <= 10 order by bytes desc;

SEGMENT_NAME BYTES

OBJ$ 5242880

SYN$ 983040

OBJAUTH$ 655360

CON$ 196608

UGROUP$ 65536

BOOTSTRAP$ 65536

FILE$ 65536

PROXY_ROLE_DATA$ 65536

UNDO$ 65536

PROXY_DATA$ 65536

获取空间占用最大的段

create table t as select * from dba_objects;

select bytes from dba_segments where segment_name='T';

delete from t where rownum <= 20000;

commit;

select bytes from dba_segments where segment_name='T';

SQL> select bytes from dba_segments where segment_name='T';

BYTES

6291456

SQL> select bytes from dba_segments where segment_name='T';

BYTES

6291456

exec show_space('T');

Unformatted Blocks ..................... 0

FS1 Blocks (0-25) ..................... 0

FS2 Blocks (25-50) ..................... 0

FS3 Blocks (50-75) ..................... 1

FS4 Blocks (75-100)..................... 266

Full Blocks ..................... 418

Total Blocks............................ 768

Total Bytes............................. 6,291,456

Total MBytes............................ 6

第 32 页

Unused Blocks........................... 63

Unused Bytes............................ 516,096

Last Used Ext FileId.................... 4

Last Used Ext BlockId................... 777

Last Used Block......................... 65

虽然删除了但空间还是占用着，可通过以下几个方法释放空间：

alter table t move;

move 过程中会创建一个原表差不多大小的表，所以须有足够空间，但会造成索引重建和影响在线应用

exec dbms_redefinition.can_redef_table(uname => 'TEST', tname => 'T');

测试表是否能重定义

create table t1 as select * from dba_objects where 1=0;

alter table t1 add constraint pk_t1 primary key(object_id);

exec dbms_redefinition.start_redef_table(uname => 'TEST', orig_table => 'T', int_table => 'T1');

exec dbms_redefinition.sync_interim_table(uname => 'TEST', orig_table => 'T', int_table => 'T1');

exec dbms_redefinition.finish_redef_table(uname => 'TEST', orig_table => 'T', int_table => 'T1');

有点像 move，但效果一般；速度也不快，对于影响在线运行好一点

alter table t shrink space;

操作正在收缩的表会产生额外的 REDO

drop table t purge;

监视索引

create table t as select * from dba_objects;

create index t_idx on t(object_id);

alter index t_idx monitoring usage;

select index_name,monitoring,used,start_monitoring,end_monitoring

from v$object_usage;

select count(object_id) from t;

INDEX_NAME MON USE START_MONITORING END_MONITORING

T_IDX YES NO 07/03/2008 12:41:28

INDEX_NAME MON USE START_MONITORING END_MONITORING

T_IDX YES YES 07/03/2008 12:41:28

监视索引的使用，当使用该索引后 USED 字段值由 NO 到 YES

alter index t_idx nomonitoring usage;

select index_name,monitoring,used,start_monitoring,end_monitoring from v$object_usage;

INDEX_NAME MON USE START_MONITORING END_MONITORING

T_IDX NO YES 07/03/2008 12:41:28 07/03/2008 12:49:43

停止监视索引

delete from t where rownum <= 20000;

commit;

analyze index t_idx validate structure;

select lf_rows_len,del_lf_rows_len from index_stats where name='T_IDX';

select tablespace_name,sum(bytes) from dba_free_space where tablespace_name='USERS' group by tablespace_name;

LF_ROWS_LEN DEL_LF_ROWS_LEN

787271 310119

列出了叶节点的总长度和所有已删除索引的长度

TABLESPACE_NAME SUM(BYTES)

USERS 75366400

表空间 USERS 的剩余空间

alter index t_idx shrink space;

analyze index t_idx validate structure;

select lf_rows_len,del_lf_rows_len from index_stats where name='T_IDX';

select tablespace_name,sum(bytes) from dba_free_space where tablespace_name='USERS' group by tablespace_name;

select 75759616 – 75366400 from dual;

LF_ROWS_LEN DEL_LF_ROWS_LEN

477152 0

第 33 页

TABLESPACE_NAME SUM(BYTES)

USERS 75759616

75759616-75366400

393216

空间得到了释放，当然也可用 REBUILD ONLINE 来重建索引

drop table t purge;

索引组织表

create table t (no number,

name varchar2(10),

content varchar2(1000),

constraint t_pk primary key (no))

organization index

including name

overflow tablespace www;

select table_name,tablespace_name,iot_name,iot_type from user_tables;

TABLE_NAME TABLESPACE_NAME IOT_NAME IOT_TYPE

SYS_IOT_OVER_52350 WWW T IOT_OVERFLOW

T IOT

可以看到对于溢出列，系统自建了表来存放。

select index_name,tablespace_name,index_type,table_name from user_indexes;

col segment_name format a30

select segment_name,tablespace_name,segment_type from user_segments;

INDEX_NAME TABLESPACE_NAME INDEX_TYPE TABLE_NAME

T_PK USERS IOT - TOP T

IOT 的索引类型

SEGMENT_NAME TABLESPACE_NAME SEGMENT_TYPE

T_PK USERS INDEX

SYS_IOT_OVER_52350 WWW TABLE

包含一个索引类型（包括主键和包含列）和一个溢出表

drop table test purge;