2、DBMS_SPACE
这是另一个Oracle古老支持包，它的SPACE_USAGE存储过程提供关于BasicFile和SecureFile LOB的空间利用率，我在TRBTKT.PKG_SECUREFILES包中引入了两个存储过程：CALC_SPACE_BASICFILES和CALC_SPACE_SECUREFILES。但遗憾的是，这个功能只能在启用了自动段空间管理(ASSM)的表空间上使用，而且，它也不会考虑任何BasicFile LOB块的空间利用率，在清单5中，我对表TRBTKT.TICKETS和TRBTKT.SECURE_TICKETS分别调用了这些存储过程，并返回了相应的输出。
清单5 确定BasicFile和SecureFile LOB的空间利用率
SET SERVEROUTPUT ON
-- BasicFile存储利用率:
BEGIN
trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
,tabname => 'SECURE_TICKETS'
,colname => 'DOCUMENT'
,partname => 'STS_OPEN'
);
trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
,tabname => 'SECURE_TICKETS'
,colname => 'DOCUMENT'
,partname => 'STS_PENDING'
);
trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
,tabname => 'SECURE_TICKETS'
,colname => 'DOCUMENT'
,partname => 'STS_CLOSED'
);
trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
,tabname => 'SECURE_TICKETS'
,colname => 'DOCUMENT'
,partname => 'STS_OTHER'
);
trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
,tabname => 'SECURE_TICKETS'
,colname => 'SCRNIMG'
,partname => 'STS_OPEN'
);
trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
,tabname => 'SECURE_TICKETS'
,colname => 'SCRNIMG'
,partname => 'STS_PENDING'
);
trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
,tabname => 'SECURE_TICKETS'
,colname => 'SCRNIMG'
,partname => 'STS_CLOSED'
);
trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
,tabname => 'SECURE_TICKETS'
,colname => 'SCRNIMG'
,partname => 'STS_OTHER'
);
END;
/
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_OPEN
------------------------------------------------------------
Full Blocks: 123 KB: .96
Unformatted Blocks: 379 KB: 2.96
Total Blocks: 123 Total KB: .96
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_PENDING
------------------------------------------------------------
Full Blocks: 20 KB: .16
Unformatted Blocks: 482 KB: 3.77
Total Blocks: 20 Total KB: .16
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_CLOSED
------------------------------------------------------------
Full Blocks: 37 KB: .29
Unformatted Blocks: 465 KB: 3.63
Total Blocks: 37 Total KB: .29
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_OTHER
------------------------------------------------------------
Full Blocks: 0 KB: 0
Unformatted Blocks: 0 KB: 0
Total Blocks: 0 Total KB: 0
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG
Partition Name: STS_OPEN
------------------------------------------------------------
Full Blocks: 420 KB: 3.28
Unformatted Blocks: 82 KB: .64
Total Blocks: 420 Total KB: 3.28
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG
Partition Name: STS_PENDING
------------------------------------------------------------
Full Blocks: 66 KB: .52
Unformatted Blocks: 436 KB: 3.41
Total Blocks: 66 Total KB: .52
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG
Partition Name: STS_CLOSED
------------------------------------------------------------
Full Blocks: 144 KB: 1.13
Unformatted Blocks: 358 KB: 2.8
Total Blocks: 144 Total KB: 1.13
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG
Partition Name: STS_OTHER
------------------------------------------------------------
Full Blocks: 0 KB: 0
Unformatted Blocks: 0 KB: 0
Total Blocks: 0 Total KB: 0
============================================================
-- SecureFile存储利用率:
BEGIN
trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
,tabname => 'TICKETS'
,colname => 'DOCUMENT'
,partname => 'STS_OPEN'
);
trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
,tabname => 'TICKETS'
,colname => 'DOCUMENT'
,partname => 'STS_PENDING'
);
trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
,tabname => 'TICKETS'
,colname => 'DOCUMENT'
,partname => 'STS_CLOSED'
);
trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
,tabname => 'TICKETS'
,colname => 'DOCUMENT'
,partname => 'STS_OTHER'
);
trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
,tabname => 'TICKETS'
,colname => 'SCRNIMG'
,partname => 'STS_OPEN'
);
trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
,tabname => 'TICKETS'
,colname => 'SCRNIMG'
,partname => 'STS_PENDING'
);
trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
,tabname => 'TICKETS'
,colname => 'SCRNIMG'
,partname => 'STS_CLOSED'
);
trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
,tabname => 'TICKETS'
,colname => 'SCRNIMG'
,partname => 'STS_OTHER'
);
END;
/
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT
Partition Name: STS_OPEN
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192
Used Blocks: 124 KB: 992
Expired Blocks: 882 KB: 7056
Unexpired Blocks: 0 KB: 0
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT
Partition Name: STS_PENDING
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192
Used Blocks: 21 KB: 168
Expired Blocks: 985 KB: 7880
Unexpired Blocks: 0 KB: 0
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT
Partition Name: STS_CLOSED
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192
Used Blocks: 13 KB: 104
Expired Blocks: 993 KB: 7944
Unexpired Blocks: 0 KB: 0
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT
Partition Name: STS_OTHER
------------------------------------------------------------
Segment Blocks: 512 KB: 4096
Used Blocks: 501 KB: 4008
Expired Blocks: 0 KB: 0
Unexpired Blocks: 0 KB: 0
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG
Partition Name: STS_OPEN
------------------------------------------------------------
Segment Blocks: 2560 KB: 20480
Used Blocks: 405 KB: 3240
Expired Blocks: 2134 KB: 17072
Unexpired Blocks: 0 KB: 0
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG
Partition Name: STS_PENDING
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192
Used Blocks: 62 KB: 496
Expired Blocks: 944 KB: 7552
Unexpired Blocks: 0 KB: 0
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG
Partition Name: STS_CLOSED
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192
Used Blocks: 142 KB: 1136
Expired Blocks: 864 KB: 6912
Unexpired Blocks: 0 KB: 0
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG
Partition Name: STS_OTHER
------------------------------------------------------------
Segment Blocks: 512 KB: 4096
Used Blocks: 501 KB: 4008
Expired Blocks: 0 KB: 0
Unexpired Blocks: 0 KB: 0
============================================================
SET SERVEROUTPUT ON
-- BasicFile存储利用率:
BEGIN
trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
,tabname => 'SECURE_TICKETS'
,colname => 'DOCUMENT'
,partname => 'STS_OPEN'
);
trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
,tabname => 'SECURE_TICKETS'
,colname => 'DOCUMENT'
,partname => 'STS_PENDING'
);
trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
,tabname => 'SECURE_TICKETS'
,colname => 'DOCUMENT'
,partname => 'STS_CLOSED'
);
trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
,tabname => 'SECURE_TICKETS'
,colname => 'DOCUMENT'
,partname => 'STS_OTHER'
);
trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
,tabname => 'SECURE_TICKETS'
,colname => 'SCRNIMG'
,partname => 'STS_OPEN'
);
trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
,tabname => 'SECURE_TICKETS'
,colname => 'SCRNIMG'
,partname => 'STS_PENDING'
);
trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
,tabname => 'SECURE_TICKETS'
,colname => 'SCRNIMG'
,partname => 'STS_CLOSED'
);
trbtkt.pkg_securefiles.calc_space_basicfiles (
ownname => 'TRBTKT'
,tabname => 'SECURE_TICKETS'
,colname => 'SCRNIMG'
,partname => 'STS_OTHER'
);
END;
/
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_OPEN
------------------------------------------------------------
Full Blocks: 123 KB: .96
Unformatted Blocks: 379 KB: 2.96
Total Blocks: 123 Total KB: .96
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_PENDING
------------------------------------------------------------
Full Blocks: 20 KB: .16
Unformatted Blocks: 482 KB: 3.77
Total Blocks: 20 Total KB: .16
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_CLOSED
------------------------------------------------------------
Full Blocks: 37 KB: .29
Unformatted Blocks: 465 KB: 3.63
Total Blocks: 37 Total KB: .29
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.DOCUMENT
Partition Name: STS_OTHER
------------------------------------------------------------
Full Blocks: 0 KB: 0
Unformatted Blocks: 0 KB: 0
Total Blocks: 0 Total KB: 0
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG
Partition Name: STS_OPEN
------------------------------------------------------------
Full Blocks: 420 KB: 3.28
Unformatted Blocks: 82 KB: .64
Total Blocks: 420 Total KB: 3.28
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG
Partition Name: STS_PENDING
------------------------------------------------------------
Full Blocks: 66 KB: .52
Unformatted Blocks: 436 KB: 3.41
Total Blocks: 66 Total KB: .52
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG
Partition Name: STS_CLOSED
------------------------------------------------------------
Full Blocks: 144 KB: 1.13
Unformatted Blocks: 358 KB: 2.8
Total Blocks: 144 Total KB: 1.13
============================================================
============================================================
Space Usage for BasicFile LOB TRBTKT.SECURE_TICKETS.SCRNIMG
Partition Name: STS_OTHER
------------------------------------------------------------
Full Blocks: 0 KB: 0
Unformatted Blocks: 0 KB: 0
Total Blocks: 0 Total KB: 0
============================================================
-- SecureFile存储利用率:
BEGIN
trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
,tabname => 'TICKETS'
,colname => 'DOCUMENT'
,partname => 'STS_OPEN'
);
trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
,tabname => 'TICKETS'
,colname => 'DOCUMENT'
,partname => 'STS_PENDING'
);
trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
,tabname => 'TICKETS'
,colname => 'DOCUMENT'
,partname => 'STS_CLOSED'
);
trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
,tabname => 'TICKETS'
,colname => 'DOCUMENT'
,partname => 'STS_OTHER'
);
trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
,tabname => 'TICKETS'
,colname => 'SCRNIMG'
,partname => 'STS_OPEN'
);
trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
,tabname => 'TICKETS'
,colname => 'SCRNIMG'
,partname => 'STS_PENDING'
);
trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
,tabname => 'TICKETS'
,colname => 'SCRNIMG'
,partname => 'STS_CLOSED'
);
trbtkt.pkg_securefiles.calc_space_securefiles (
ownname => 'TRBTKT'
,tabname => 'TICKETS'
,colname => 'SCRNIMG'
,partname => 'STS_OTHER'
);
END;
/
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT
Partition Name: STS_OPEN
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192
Used Blocks: 124 KB: 992
Expired Blocks: 882 KB: 7056
Unexpired Blocks: 0 KB: 0
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT
Partition Name: STS_PENDING
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192
Used Blocks: 21 KB: 168
Expired Blocks: 985 KB: 7880
Unexpired Blocks: 0 KB: 0
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT
Partition Name: STS_CLOSED
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192
Used Blocks: 13 KB: 104
Expired Blocks: 993 KB: 7944
Unexpired Blocks: 0 KB: 0
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.DOCUMENT
Partition Name: STS_OTHER
------------------------------------------------------------
Segment Blocks: 512 KB: 4096
Used Blocks: 501 KB: 4008
Expired Blocks: 0 KB: 0
Unexpired Blocks: 0 KB: 0
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG
Partition Name: STS_OPEN
------------------------------------------------------------
Segment Blocks: 2560 KB: 20480
Used Blocks: 405 KB: 3240
Expired Blocks: 2134 KB: 17072
Unexpired Blocks: 0 KB: 0
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG
Partition Name: STS_PENDING
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192
Used Blocks: 62 KB: 496
Expired Blocks: 944 KB: 7552
Unexpired Blocks: 0 KB: 0
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG
Partition Name: STS_CLOSED
------------------------------------------------------------
Segment Blocks: 1024 KB: 8192
Used Blocks: 142 KB: 1136
Expired Blocks: 864 KB: 6912
Unexpired Blocks: 0 KB: 0
============================================================
============================================================
Space Usage for SecureFile LOB TRBTKT.TICKETS.SCRNIMG
Partition Name: STS_OTHER
------------------------------------------------------------
Segment Blocks: 512 KB: 4096
Used Blocks: 501 KB: 4008
Expired Blocks: 0 KB: 0
Unexpired Blocks: 0 KB: 0
============================================================
SET SERVEROUTPUT ON
