oracle dbms_space,使用dbms_space来检测位于高水位线之下的空间

柔情只为你懂 2023-01-18 15:21 141阅读 0赞

使用dbms\_space来检测位于高水位线之下的空间

set serverout on;

declare

l\_fs1\_bytes

number;

l\_fs2\_bytes

number;

l\_fs3\_bytes

number;

l\_fs4\_bytes

number;

l\_fs1\_blocks

number;

l\_fs2\_blocks

number;

l\_fs3\_blocks

number;

l\_fs4\_blocks

number;

l\_full\_bytes

number;

l\_full\_blocks

number;

l\_unformatted\_bytes

number;

l\_unformatted\_blocks

number;

begin

SYS.dbms\_space.space\_usage(

SEGMENT\_OWNER

'users',

SEGMENT\_NAME

'EMP',

SEGMENT\_TYPE

'TABLE',

fs1\_bytes

l\_fs1\_bytes,

fs1\_blocks

l\_fs1\_blocks,

fs2\_bytes

l\_fs2\_bytes,

fs2\_blocks

l\_fs2\_blocks,

fs3\_bytes

l\_fs3\_bytes,

fs3\_blocks

l\_fs3\_blocks,

fs4\_bytes

l\_fs4\_bytes,

fs4\_blocks

l\_fs4\_blocks,

full\_bytes

l\_full\_bytes,

full\_blocks

l\_full\_blocks,

unformatted\_blocks =>

l\_unformatted\_blocks,

unformatted\_bytes  =>

l\_unformatted\_bytes

);

dbms\_output.put\_line(' FS1 Blocks = '||l\_fs1\_blocks||'

and Bytes =

'||l\_fs1\_bytes);

dbms\_output.put\_line(' FS2 Blocks = '||l\_fs2\_blocks||' and Bytes =

'||l\_fs2\_bytes);

dbms\_output.put\_line(' FS3 Blocks = '||l\_fs3\_blocks||' and Bytes =

'||l\_fs3\_bytes);

dbms\_output.put\_line(' FS4 Blocks = '||l\_fs4\_blocks||' and Bytes =

'||l\_fs4\_bytes);

dbms\_output.put\_line(' Full Blocks = '||l\_full\_blocks||' and Bytes

'||l\_full\_bytes);

dbms\_output.put\_line(' Unformatted Blocks =

'||l\_unformatted\_blocks||' and Bytes

='||l\_unformatted\_bytes);

end;

追踪检测到的位于高水位线下的空间

SQL>set autotrace trace

statistics;

运行一个进行全表扫描的查询

SQL> select \* from

emp;

已选择 39

行。

统计信息

\----------------------------------------------------------

1  recursive

calls

0  db block

gets

9  consistent

gets

0  physical

reads

116  redo

size

1720  bytes

sent via SQL\*Net to

client

574  bytes

received via SQL\*Net from

client

4  SQL\*Net roundtrips to/from

client

0  sorts

(memory)

0  sorts

(disk)

39  rows

processed

将处理的数据行数目与从内存中读取的数据块数目相比较

SQL> delete from EMP

;

SQL> select \* from

emp;

已选择 39

行。

统计信息

\----------------------------------------------------------

0  recursive

calls

0  db block

gets

46  consistent

gets

0  physical

reads

116  redo

size

1720  bytes sent via SQL\*Net to

client

574  bytes received via SQL\*Net from

client

4  SQL\*Net roundtrips to/from

client

0  sorts

(memory)

0  sorts

(disk)

39  rows

processed

SQL>

ROLLBACK    已选择

39 行。

统计信息

\----------------------------------------------------------

0  recursive

calls

0  db block

gets

6  consistent

gets

0  physical

reads

0  redo

size

1720  bytes sent via SQL\*Net to

client

574  bytes received via SQL\*Net from

client

4  SQL\*Net roundtrips to/from

client

0  sorts

(memory)

0  sorts

(disk)

39  rows

processed

SQL> delete from EMP

;

SQL>

commit;

SQL> select \* from

emp;

未选定行

统计信息

\----------------------------------------------------------

0  recursive

calls

0  db block

gets

3  consistent

gets

0  physical

reads

0  redo

size

485  bytes sent via SQL\*Net to

client

541  bytes received via SQL\*Net from

client

1  SQL\*Net roundtrips to/from

client

0  sorts

(memory)

0  sorts

(disk)

0  rows

processed

检测表中未使用的空间

delete from EMP ;

COMMIT;

select COUNT(1) from EMP;

COUNT(1)

\------

\---------------

select COUNT(1) from user\_extents where segment\_name =

'EMP'

COUNT(1)

\------

select BLOCKS from USER\_SEGMENTS where segment\_name =

'EMP' BLOCKS

\------

640

\-----

截断表truncate table  EMP

select BLOCKS from USER\_SEGMENTS where segment\_name =

'EMP';   BLOCKS

\------

select COUNT(1) from user\_extents where segment\_name =

'EMP'

COUNT(1)

\------

压缩数据以进行直接路径加载

1.基本压缩

a.使用row store compress子句在创建、修改或移动表时启用压缩

b.通过直接路径方法加载数据 创建启用基本压缩并使用直接路径加载数据的表： create

table regs\_dss compress as select

reg\_id,reg\_name from

regs;

\--查看表是否启用压缩

select

table\_name,compression,compress\_for from

user\_tables where

table\_name='REGS\_DSS';

修改基础压缩特性

ALTER TABLE EMP COMPRESS;

启用压缩特性，不影响表中现有的数据，而仅仅影响后面直接路径加载的数据

ALTER TABLE EMP MOVE COMPRESS;

禁用压缩特性，不影响表中现有的数据，而仅仅影响后面直接路径加载的数据

ALTER TABLE EMP NOCOMPRESS;

禁用压缩特性，对已有数据解压缩

ALTER TABLE EMP MOVE NOCOMPRESS;

oracle dbms_space,使用dbms_space来检测位于高水位线之下的空间

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 oracle dbms_space,使用dbms_space来检测位于高水位线之下的空间

相关 oracle查询表空间脚本,Oracle表空间数据文件高水位查询脚本

相关 ORACLE 11g 生产中高水位线(HWM)处理

相关 Oracle 高水位线查询（show_space)

相关 oracle 高水位线详解

相关 Oracle表碎片查询以及整理（高水位线）

相关高水位线和全表扫描

相关高水位线和全表扫描

相关 Oracle高水位线(HWM)及性能优化

相关一、oracle 高水位线详解