2
表空间碎片查询语句.txt
免费下载
Script to Detect Tablespace Fragmentation (Doc ID 1020182.6)
set pagesize 60
set newpage 0
set echo off
ttitle center 'Contiguous Extents Report' skip 3
break on "TABLESPACE NAME" skip page duplicate
spool contig_free_space.lis
rem
column "CONTIGUOUS BYTES" format 999,999,999
column "COUNT" format 999
column "TOTAL BYTES" format 999,999,999
column "TODAY" noprint new_value new_today format a1
rem
with a
as
(
SELECT tablespace_name,file_id,block_id,bytes,blocks,LEVEL,CONNECT_BY_ROOT
block_id as root,
count(*) over(partition by tablespace_name, file_id, CONNECT_BY_ROOT block_id)
as EXTENTS,
sum(bytes) over(partition by tablespace_name, file_id, CONNECT_BY_ROOT block_id)
as CONTIGUOUS_BYTES
FROM dba_free_space
CONNECT BY PRIOR (block_id+blocks) = block_id
and PRIOR tablespace_name = tablespace_name
and PRIOR file_id = file_id
)
select TABLESPACE_NAME "TABLESPACE NAME",
CONTIGUOUS_BYTES "CONTIGUOUS BYTES"
from a
where (tablespace_name, file_id, block_id) in
(select tablespace_name, file_id, block_id from
a
group by tablespace_name, file_id, block_id
having count(*)=1
)
order by 1,2 desc;
with a
as
(
SELECT tablespace_name,file_id,block_id,bytes,blocks,LEVEL,CONNECT_BY_ROOT
block_id as root,
count(*) over(partition by tablespace_name, file_id, CONNECT_BY_ROOT block_id)
as EXTENTS,
sum(bytes) over(partition by tablespace_name, file_id, CONNECT_BY_ROOT block_id)
as CONTIGUOUS_BYTES
FROM dba_free_space
CONNECT BY PRIOR (block_id+blocks) = block_id
and PRIOR tablespace_name = tablespace_name
and PRIOR file_id = file_id
)
select tablespace_name,count(*) "# OF EXTENTS",sum(CONTIGUOUS_BYTES) "TOTAL
BYTES"
from a
where (tablespace_name, file_id, block_id) in
(select tablespace_name, file_id, block_id from
a
group by tablespace_name, file_id, block_id
having count(*)=1
)
group by tablespace_name;
-- Create new tablespace & new table.
DROP tablespace tbs_test including contents and datafiles;
CREATE TABLESPACE tbs_test DATAFILE SIZE 1M AUTOEXTEND on EXTENT MANAGEMENT
LOCAL AUTOALLOCATE;
CREATE TABLE TEST_1
( FIELD1 NUMBER(20,0) NOT NULL ENABLE,
FIELD2 NUMBER(20,0) NOT NULL ENABLE,
FIELD3 NUMBER(20,0) NOT NULL ENABLE,
FIELD4 NUMBER(20,8) NOT NULL ENABLE,
FIELD5 CHAR(1),
FIELD6 TIMESTAMP (6) DEFAULT SYSTIMESTAMP)
TABLESPACE tbs_test;
-- Insert data to table TEST_1.
declare
pkv number:=8888888888;
stmt varchar2(4000);
begin
for i in 1..10000000 loop
pkv:=pkv+i;
insert into test_1(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) values
(pkv,88888888888,88888888888,888888888888,'W');
end loop;
end;
/
-- Check tablespace fragment situation.
col tablespace format a15 justify c trunc heading 'Tablespace'
col file_id format 990 justify c heading 'File'
col block_id format 9,999,990 justify c heading 'Block Id'
col blocks format 999,990 justify c heading 'Size'
col segment format a38 justify c trunc heading 'Segment'
break -
on tablespace skip page
select
tablespace_name tablespace,
file_id,
block_id,
blocks,
owner||'.'||segment_name segment
from
dba_extents
where tablespace_name='TBS_TEST'
union
select
tablespace_name tablespace,
file_id,
block_id,
blocks,
'<free>'
of 17
免费下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论