Shulou(Shulou.com)06/01 Report--

SYS_IL0000072118C00005 $$INDEX PARTITION ASSM SYS_IL_P177 BASICFILES

SYS_IL0000072118C00006 $$INDEX PARTITION ASSM SYS_IL_P185 BASICFILES

SYS_IL0000072118C00006 $$INDEX PARTITION ASSM SYS_IL_P188 BASICFILES

SYS_IL0000072118C00006 $$INDEX PARTITION ASSM SYS_IL_P187 BASICFILES

SYS_IL0000072118C00006 $$INDEX PARTITION ASSM SYS_IL_P186 BASICFILES

SYS_IL0000072144C00005 $$INDEX PARTITION ASSM SYS_IL_P194 SECUREFILES

SYS_IL0000072144C00005 $$INDEX PARTITION ASSM SYS_IL_P193 SECUREFILES

SYS_IL0000072144C00005 $$INDEX PARTITION ASSM SYS_IL_P195 SECUREFILES

SYS_IL0000072144C00005 $$INDEX PARTITION ASSM SYS_IL_P196 SECUREFILES

SYS_IL0000072144C00006 $$INDEX PARTITION ASSM SYS_IL_P204 SECUREFILES

SYS_IL0000072144C00006 $$INDEX PARTITION ASSM SYS_IL_P203 SECUREFILES

SYS_IL0000072144C00006 $$INDEX PARTITION ASSM SYS_IL_P202 SECUREFILES

SYS_IL0000072144C00006 $$INDEX PARTITION ASSM SYS_IL_P201 SECUREFILES

SYS_LOB0000072118C00005 $$LOB PARTITION ASSM SYS_LOB_P173 BASICFILES

SYS_LOB0000072118C00005 $$LOB PARTITION ASSM SYS_LOB_P176 BASICFILES

SYS_LOB0000072118C00005 $$LOB PARTITION ASSM SYS_LOB_P175 BASICFILES

SYS_LOB0000072118C00005 $$LOB PARTITION ASSM SYS_LOB_P174 BASICFILES

SYS_LOB0000072118C00006 $$LOB PARTITION ASSM SYS_LOB_P184 BASICFILES

SYS_LOB0000072118C00006 $$LOB PARTITION ASSM SYS_LOB_P183 BASICFILES

SYS_LOB0000072118C00006 $$LOB PARTITION ASSM SYS_LOB_P181 BASICFILES

SYS_LOB0000072118C00006 $$LOB PARTITION ASSM SYS_LOB_P182 BASICFILES

SYS_LOB0000072144C00005 $$LOB PARTITION SECUREFILE SYS_LOB_P191 SECUREFILES

SYS_LOB0000072144C00005 $$LOB PARTITION SECUREFILE SYS_LOB_P192 SECUREFILES

SYS_LOB0000072144C00005 $$LOB PARTITION SECUREFILE SYS_LOB_P189 SECUREFILES

SYS_LOB0000072144C00005 $$LOB PARTITION SECUREFILE SYS_LOB_P190 SECUREFILES

SYS_LOB0000072144C00006 $$LOB PARTITION SECUREFILE SYS_LOB_P198 SECUREFILES

SYS_LOB0000072144C00006 $$LOB PARTITION SECUREFILE SYS_LOB_P199 SECUREFILES

SYS_LOB0000072144C00006 $$LOB PARTITION SECUREFILE SYS_LOB_P197 SECUREFILES

SYS_LOB0000072144C00006 $$LOB PARTITION SECUREFILE SYS_LOB_P200 SECUREFILES

TICKETS TABLE PARTITION ASSM STS_PENDING USERS

TICKETS TABLE PARTITION ASSM STS_OTHER USERS

TICKETS TABLE PARTITION ASSM STS_OPEN USERS

TICKETS TABLE PARTITION ASSM STS_CLOSED USERS

TICKETS_PK_IDX INDEX ASSM USERS

BasicFile and SecureFile LOB metadata

(from DBA_LOBS)

Stored Encryp- Compre- DeDupli- Secure Parti-

Table Column Segment Tablespace Logging Cacheing In Row tion ssion cation File? Tioned

- --

SECURE_TICKETS DOCUMENT SYS_LOB0000072118C00005 $$BASICFILES NONE NO YES NONE NONE NONE NO YES

SECURE_TICKETS SCRNIMG SYS_LOB0000072118C00006 $$BASICFILES NONE NO YES NONE NONE NONE NO YES

TICKETS DOCUMENT SYS_LOB0000072144C00005 $$SECUREFILES NONE YES NO NO NO NO YES YES

TICKETS SCRNIMG SYS_LOB0000072144C00006 $$SECUREFILES NONE CACHEREADS NO NO NO NO YES YES

Default settings for BasicFile and SecureFile partition LOB

(from DBA_PART_LOBS)

Table Column Cached Tablespace SecureFile Compressed DeDuplicated Encrypted

--

SECURE_TICKETS DOCUMENT NO BASICFILES NO NONE NONE NONE

SECURE_TICKETS SCRNIMG NO BASICFILES NO NONE NONE NONE

TICKETS DOCUMENT YES SECUREFILES YES NO NO NO

TICKETS SCRNIMG CACHEREADS SECUREFILES YES NO NO NO

BasicFile and SecureFile LOB Partition

(from DBA_LOB_PARTITIONS)

Stored in Stored DeDupli-

Table Column Partition Cacheing In Row Encrypted Compressed cated SecureFile

-

SECURE_TICKETS DOCUMENT STS_OTHER NO YES NONE NONE NONE NO

SECURE_TICKETS DOCUMENT STS_CLOSED NO YES NONE NONE NONE NO

SECURE_TICKETS DOCUMENT STS_OPEN NO YES NONE NONE NONE NO

SECURE_TICKETS DOCUMENT STS_PENDING NO YES NONE NONE NONE NO

SECURE_TICKETS SCRNIMG STS_OTHER NO YES NONE NONE NONE NO

SECURE_TICKETS SCRNIMG STS_CLOSED NO YES NONE NONE NONE NO

SECURE_TICKETS SCRNIMG STS_PENDING NO YES NONE NONE NONE NO

SECURE_TICKETS SCRNIMG STS_OPEN NO YES NONE NONE NONE NO

TICKETS DOCUMENT STS_OTHER YES NO NO HIGH LOB YES

TICKETS DOCUMENT STS_PENDING YES NO NO NO NO YES

TICKETS DOCUMENT STS_CLOSED YES NO NO MEDIUM LOB YES

TICKETS DOCUMENT STS_OPEN YES NO NO NO NO YES

TICKETS SCRNIMG STS_PENDING CACHEREADS NO NO MEDIUM LOB YES

TICKETS SCRNIMG STS_OPEN CACHEREADS NO NO MEDIUM NO YES

TICKETS SCRNIMG STS_CLOSED CACHEREADS NO NO HIGH LOB YES

TICKETS SCRNIMG STS_OTHER CACHEREADS NO NO HIGH LOB YES

2 、 DBMS_SPACE

This is another ancient Oracle support package whose SPACE_USAGE stored procedures provide space utilization for BasicFile and SecureFile LOB, and I introduced two stored procedures into the TRBTKT.PKG_SECUREFILES package: CALC_SPACE_BASICFILES and CALC_SPACE_SECUREFILES. Unfortunately, this feature can only be used on tablespaces with automatic segment space management (ASSM) enabled, and it doesn't take into account the space utilization of any BasicFile LOB blocks. In listing 5, I called these stored procedures on the tables TRBTKT.TICKETS and TRBTKT.SECURE_TICKETS, respectively, and returned the corresponding output.

Listing 5 determines the space utilization for BasicFile and SecureFile LOB

SET SERVEROUTPUT ON

-- BasicFile storage utilization:

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 storage utilization:

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

Modify the SecureFile property

When I create the TRBTKT.SECURE_TICKETS table, you may notice that I perform compression and repeated deletions in their corresponding partition for each LOB column, and importantly, I use the ALTER TABLE statement to fix it, and I can apply changes to SecureFile LOB on each LOB separate partition if necessary. The code in listing 6 shows four examples of modifying the SecureFile LOB properties of the TRBTKT.TICKETS table. Notice that the exchange has been performed with the code in listing 3 and the TRBTKT.SECURE_ tests table, and I have also created a report from DBA_PART_LOBS showing the status before and after modifying these LOB attributes, and the resulting output is shown in report 2.

Listing 6 manages SecureFile LOB properties

SQL > ALTER TABLE TRBTKT.TICKETS

SQL > MODIFY LOB (DOCUMENT) (NOCOMPRESS)

Table altered.

SQL > ALTER TABLE TRBTKT.TICKETS

SQL > MODIFY LOB (SCRNIMG) (KEEP_DUPLICATES)

Table altered.

SQL > ALTER TABLE TRBTKT.TICKETS

SQL > MODIFY PARTITION STS_PENDING LOB (SCRNIMG) (COMPRESS HIGH)

Table altered.

SQL > ALTER TABLE TRBTKT.TICKETS

SQL > MODIFY PARTITION STS_PENDING LOB (DOCUMENT) (DEDUPLICATE)

Table altered.

Report 2 results returned by query DBA_LOB_PARTITIONS after modifying a lot of SecureFile LOB

BasicFile and SecureFile LOB Partitions

(from DBA_LOB_PARTITIONS)

Stored in Stored DeDupli-

Table Column Partition Cacheing In Row Encrypted Compressed cated SecureFile

--

SECURE_TICKETS DOCUMENT STS_OTHER NO YES NONE NONE NONE NO

SECURE_TICKETS DOCUMENT STS_CLOSED NO YES NONE NONE NONE NO

SECURE_TICKETS DOCUMENT STS_OPEN NO YES NONE NONE NONE NO

SECURE_TICKETS DOCUMENT STS_PENDING NO YES NONE NONE NONE NO

SECURE_TICKETS SCRNIMG STS_OTHER NO YES NONE NONE NONE NO

SECURE_TICKETS SCRNIMG STS_CLOSED NO YES NONE NONE NONE NO

SECURE_TICKETS SCRNIMG STS_PENDING NO YES NONE NONE NONE NO

SECURE_TICKETS SCRNIMG STS_OPEN NO YES NONE NONE NONE NO

TICKETS DOCUMENT STS_OTHER YES NO NO NO LOB YES

TICKETS DOCUMENT STS_PENDING YES NO NO NO LOB YES

TICKETS DOCUMENT STS_CLOSED YES NO NO NO LOB YES

TICKETS DOCUMENT STS_OPEN YES NO NO NO NO YES

TICKETS SCRNIMG STS_PENDING CACHEREADS NO NO HIGH NO YES

TICKETS SCRNIMG STS_OPEN CACHEREADS NO NO MEDIUM NO YES

TICKETS SCRNIMG STS_CLOSED CACHEREADS NO NO HIGH NO YES

TICKETS SCRNIMG STS_OTHER CACHEREADS NO NO HIGH NO YES

Finally, keep in mind that any changes to the LOB property will only affect the newly created LOB or the newly modified LOB, for example, changing the compression method of the column TRBTKT.SECURE_TICKETS.SCRNIMG from COMPRESS to NOCOMPRESS does not affect existing LOB entries.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.