考过10g ocp的朋友大概都看到过这样的问题，回收站中的对象所占空间是否算作free space？ 纸上得来终觉浅，我们实地考察一下:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
|
SQL>
set
long 99999999;
/*DBA_FREE_SPACE视图列出了数据库中所有表空间上空闲的区间,利用该视图我们可以计算表空间使用率等
注意该视图不会列出本地管理模式中offline的数据文件(或表空间)上的相关区间信息*/
SQL>
select
text
from
dba_views
where
view_name=
'DBA_FREE_SPACE'
;
TEXT
--------------------------------------------------------------------------------
select
ts.
name
, fi.file#, f.block#,
f.length * ts.blocksize, f.length, f.file#
from
sys.ts$ ts, sys.fet$ f, sys.file$ fi
where
ts.ts# = f.ts#
and
f.ts# = fi.ts#
and
f.file# = fi.relfile#
and
ts.bitmapped = 0
union
all
select
/*+ ordered use_nl(f) use_nl(fi) */
ts.
name
, fi.file#, f.ktfbfebno,
f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from
sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where
ts.ts# = f.ktfbfetsn
and
f.ktfbfetsn = fi.ts#
and
f.ktfbfefno = fi.relfile#
and
ts.bitmapped <> 0
and
ts.online$
in
(1,4)
and
ts.contents$ = 0
union
all
select
/*+ ordered use_nl(u) use_nl(fi) */
ts.
name
, fi.file#, u.ktfbuebno,
u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from
sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where
ts.ts# = rb.ts#
and
rb.ts# = fi.ts#
and
u.ktfbuefno = fi.relfile#
and
u.ktfbuesegtsn = rb.ts#
and
u.ktfbuesegfno = rb.file#
and
u.ktfbuesegbno = rb.block#
and
ts.bitmapped <> 0
and
ts.online$
in
(1,4)
and
ts.contents$ = 0
union
all
select
ts.
name
, fi.file#, u.block#,
u.length * ts.blocksize, u.length, u.file#
from
sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
where
ts.ts# = u.ts#
and
u.ts# = fi.ts#
and
u.segfile# = fi.relfile#
and
u.ts# = rb.ts#
and
u.segfile# = rb.file#
and
u.segblock# = rb.block#
and
ts.bitmapped = 0
/*可以看到后2个子查询链接中存在recyclebin$基表*/
SQL> show
user
;
User
is
"system"
SQL> purge recyclebin;
Done
SQL>
create
table
YOUYUS tablespace users
as
select
*
from
dba_objects;
Table
created
SQL>
select
sum
(bytes)
from
dba_free_space
where
tablespace_name=
'USERS'
;
SUM
(BYTES)
----------
851968
SQL>
drop
table
YOUYUS;
Table
dropped
SQL> col ORIGINAL_NAME
for
a10;
SQL> col ts_name
for
a10;
SQL>
select
original_name,operation,type,ts_name,
space
from
dba_recyclebin;
ORIGINAL_N OPERATION TYPE TS_NAME
SPACE
---------- --------- ------------------------- ---------- ----------
YOUYUS
DROP
TABLE
USERS 1152
/* 这里的
SPACE
单位是standard block
size
,1152 * 8k=9216k */
SQL>
select
sum
(bytes)
from
dba_free_space
where
tablespace_name=
'USERS'
;
SUM
(BYTES)
----------
10289152
/* 可以看到YOUYUS表被回收后，USERS表空间上的
FREE
EXTENT空间也随之增长了;10289152-851968=9216k 与YOUYUS表的大小吻合*/
col
name
for
a10;
/*通过以下查询可以发现数据库中本地管理模式表空间上已被回收对象可以被覆盖重用的区间信息*/
select
/*+ ordered use_nl(u) use_nl(fi) */
ts.
name
, fi.file#, u.ktfbuebno,
u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from
sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where
ts.ts# = rb.ts#
and
rb.ts# = fi.ts#
and
u.ktfbuefno = fi.relfile#
and
u.ktfbuesegtsn = rb.ts#
and
u.ktfbuesegfno = rb.file#
and
u.ktfbuesegbno = rb.block#
and
ts.bitmapped <> 0
and
ts.online$
in
(1,4)
and
ts.contents$ = 0;
NAME
FILE# KTFBUEBNO U.KTFBUEBLKS*TS.BLOCKSIZE KTFBUEBLKS KTFBUEFNO
---------- ---------- ---------- ------------------------- ---------- ----------
USERS 4 184 65536 8 4
USERS 4 192 65536 8 4
USERS 4 200 65536 8 4
USERS 4 208 65536 8 4
USERS 4 216 65536 8 4
USERS 4 224 65536 8 4
USERS 4 232 65536 8 4
So We can reuse segment
space
which resided
in
recyclebin!
That' great!
|
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277531
