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

Oracle 11g's new SecureFile storage features extend the flexibility and capacity of large objects (LOB). This article examines how to effectively migrate from BasicFile to SecureFile storage, how to measure the efficiency of SecureFile vs BasicFile LOB, and how to use different compression and deduplication options for SecureFile LOB.

I created a new table TRBTKT.SECURE_TICKETS redefined the structure of the original table TRBTKT.TICKETS, mapped the original BasicFile LOB to the corresponding SecureFile LOB, and then executed an INSERT INTO. The SELECT FROM DML statement transfers the contents of the original table to the new table.

In a relatively simple application environment, this loading method is sufficient, but LOB is widely used in data warehouses and business intelligence applications, especially for storing XML documents containing complex business transactions and medical image data captured by hospital information systems.

For illustration purposes, I recreated the two tables using the code in listing 1, partitioned them with list partitions, and reloaded the TRBTKT.TICKETS table with larger data in listing 2.

Listing 1 uses list partitions to recreate these two tables to simulate a data warehouse environment

Recreate the table TRBTKT.TICKETS, this time including a STATUS column

CREATE TABLE trbtkt.tickets (

Tkt_id NUMBER

Description VARCHAR2 (30)

, submit_dtm TIMESTAMP

, status VARCHAR2 (8)

, document BLOB

, scrnimg BLOB

)

LOB (document)

STORE AS BASICFILE (TABLESPACE basicfiles)

, LOB (scrnimg)

STORE AS BASICFILE (TABLESPACE basicfiles)

PARTITION BY LIST (status) (

PARTITION sts_open

VALUES ('OPEN')

, PARTITION sts_pending

VALUES ('PENDING')

, PARTITION sts_closed

VALUES ('CLOSED')

, PARTITION sts_other

VALUES (DEFAULT)

)

-- comment

COMMENT ON TABLE trbtkt.tickets

IS 'Contains Trouble Ticket transaction data'

COMMENT ON COLUMN trbtkt.tickets.tkt_id

IS 'Unique identifier for a Trouble Ticket'

COMMENT ON COLUMN trbtkt.tickets.description

IS 'Trouble Ticket Description'

COMMENT ON COLUMN trbtkt.tickets.submit_dtm

IS 'Trouble Ticket Submission Time Stamp'

COMMENT ON COLUMN trbtkt.tickets.status

IS 'Trouble Ticket Status'

COMMENT ON COLUMN trbtkt.tickets.document

IS 'Large Object (LOB) that contains a Microsoft Word document that provides detailed information about a Trouble Ticket'

COMMENT ON COLUMN trbtkt.tickets.scrnimg

IS 'Large Object (LOB) that contains a screen print or other p_w_picpath that helps a problem solver identify how to act on a Trouble Ticket'

-- create indexes and constraints

CREATE UNIQUE INDEX trbtkt.tickets_pk_idx

ON trbtkt.tickets (tkt_id)

TABLESPACE users

ALTER TABLE trbtkt.tickets

ADD CONSTRAINT tickets_pk

PRIMARY KEY (tkt_id)

-

Create an improved version of the table TRBTKT.SECURE_TICKETS, partition the SecureFile LOB storage using partitions, and set the appropriate retention period based on the value of STATUS

DROP TABLE trbtkt.secure_tickets PURGE

CREATE TABLE trbtkt.secure_tickets (

Tkt_id NUMBER

Description VARCHAR2 (30)

, submit_dtm TIMESTAMP

, status VARCHAR2 (8)

, document BLOB

, scrnimg BLOB

)

LOB (document)

STORE AS SECUREFILE (

TABLESPACE securefiles

DISABLE STORAGE IN ROW

CACHE

)

, LOB (scrnimg)

STORE AS SECUREFILE (

TABLESPACE securefiles

DISABLE STORAGE IN ROW

CACHE READS

)

PARTITION BY LIST (status) (

PARTITION sts_open

VALUES ('OPEN')

LOB (document) STORE AS SECUREFILE (KEEP_DUPLICATES NOCOMPRESS)

, LOB (scrnimg) STORE AS SECUREFILE (KEEP_DUPLICATES COMPRESS)

, PARTITION sts_pending

VALUES ('PENDING')

LOB (document) STORE AS SECUREFILE (KEEP_DUPLICATES NOCOMPRESS)

, LOB (scrnimg) STORE AS SECUREFILE (DEDUPLICATE COMPRESS)

, PARTITION sts_closed

VALUES ('CLOSED')

LOB (document) STORE AS SECUREFILE (DEDUPLICATE COMPRESS)

, LOB (scrnimg) STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH)

, PARTITION sts_other

VALUES (DEFAULT)

LOB (document) STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH)

, LOB (scrnimg) STORE AS SECUREFILE (DEDUPLICATE COMPRESS HIGH)

)

-- comment

COMMENT ON TABLE trbtkt.secure_tickets

IS 'Contains Trouble Ticket transaction data'

COMMENT ON COLUMN trbtkt.secure_tickets.tkt_id

IS 'Unique identifier for a Trouble Ticket'

COMMENT ON COLUMN trbtkt.secure_tickets.description

IS 'Trouble Ticket Description'

COMMENT ON COLUMN trbtkt.secure_tickets.submit_dtm

IS 'Trouble Ticket Submission Time Stamp'

COMMENT ON COLUMN trbtkt.secure_tickets.status

IS 'Trouble Ticket Status'

COMMENT ON COLUMN trbtkt.secure_tickets.document

IS 'Large Object (LOB) that contains a Microsoft Word document that provides detailed information about a Trouble Ticket'

COMMENT ON COLUMN trbtkt.secure_tickets.scrnimg

IS 'Large Object (LOB) that contains a screen print or other p_w_picpath that helps a problem solver identify how to act on a Trouble Ticket'

Listing 2 reloads the table TRBTKT.TICKETS with additional data

SET SERVEROUTPUT ON

TRUNCATE TABLE trbtkt.tickets

BEGIN

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 101

, description = > 'Trouble Ticket 101'

, submit_dts = > '2008-12-31 23V 45purl 00'

, status = > 'OPEN'

, docFileName = > 'New_101.doc'

, imgFileName = > 'DBRIssues.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 102

, description = > 'Trouble Ticket 102'

, submit_dts = > '2009-01-04 00 purl 0000'

, status = > 'OPEN'

, docFileName = > 'New_102.doc'

, imgFileName = > 'Unresolved.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 103

, description = > 'Trouble Ticket 103'

, submit_dts = > '2009-01-02 00 purl 0000'

, status = > 'OPEN'

, docFileName = > 'New_103.doc'

, imgFileName = > 'Unresolved.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 104

, description = > 'Trouble Ticket 104'

, submit_dts = > '2009-01-14 1215 30 purl 00'

, status = > 'OPEN'

, docFileName = > 'New_104.doc'

, imgFileName = > 'DBRIssues.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 105

, description = > 'Trouble Ticket 105'

, submit_dts = > '2009-01-09 00 purl 0000'

, status = > 'OPEN'

, docFileName = > 'New_105.doc'

, imgFileName = > 'Unresolved.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 106

, description = > 'Trouble Ticket 106'

, submit_dts = > '2009-01-11 00 purse 00'

, status = > 'OPEN'

, docFileName = > 'New_106.doc'

, imgFileName = > 'Unresolved.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 107

, description = > 'Trouble Ticket 107'

, submit_dts = > '2009-01-1600 purl 0000'

, status = > 'OPEN'

, docFileName = > 'New_107.doc'

, imgFileName = > 'DBRIssues.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 108

, description = > 'Trouble Ticket 108'

, submit_dts = > '2009-01-12 00 purse 00'

, status = > 'OPEN'

, docFileName = > 'New_108.doc'

, imgFileName = > 'Unresolved.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 109

, description = > 'Trouble Ticket 109'

, submit_dts = > '2009-01-02 00 purl 0000'

, status = > 'OPEN'

, docFileName = > 'New_109.doc'

, imgFileName = > 'Unresolved.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 110

, description = > 'Trouble Ticket 110'

, submit_dts = > '2009-01-14 12 purl 45purl 00'

, status = > 'OPEN'

, docFileName = > 'New_110.doc'

, imgFileName = > 'DBRIssues.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 201

, description = > 'Trouble Ticket 201'

, submit_dts = > '2008-12-31 23V 45purl 00'

, status = > 'PENDING'

, docFileName = > 'New_101.doc'

, imgFileName = > 'DBRIssues.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 202

, description = > 'Trouble Ticket 202'

, submit_dts = > '2009-01-04 00 purl 0000'

, status = > 'OPEN'

, docFileName = > 'New_102.doc'

, imgFileName = > 'Unresolved.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 203

, description = > 'Trouble Ticket 203'

, submit_dts = > '2009-01-02 00 purl 0000'

, status = > 'CLOSED'

, docFileName = > 'New_103.doc'

, imgFileName = > 'Unresolved.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 204

, description = > 'Trouble Ticket 204'

, submit_dts = > '2009-01-14 1215 30 purl 00'

, status = > 'OPEN'

, docFileName = > 'New_104.doc'

, imgFileName = > 'DBRIssues.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 205

, description = > 'Trouble Ticket 205'

, submit_dts = > '2009-01-09 00 purl 0000'

, status = > 'OPEN'

, docFileName = > 'New_105.doc'

, imgFileName = > 'Unresolved.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 206

, description = > 'Trouble Ticket 206'

, submit_dts = > '2009-01-11 00 purse 00'

, status = > 'PENDING'

, docFileName = > 'New_106.doc'

, imgFileName = > 'Unresolved.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 207

, description = > 'Trouble Ticket 207'

, submit_dts = > '2009-01-1600 purl 0000'

, status = > 'OPEN'

, docFileName = > 'New_107.doc'

, imgFileName = > 'DBRIssues.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 208

, description = > 'Trouble Ticket 208'

, submit_dts = > '2009-01-12 00 purse 00'

, status = > 'OPEN'

, docFileName = > 'New_108.doc'

, imgFileName = > 'Unresolved.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 209

, description = > 'Trouble Ticket 209'

, submit_dts = > '2009-01-02 00 purl 0000'

, status = > 'PENDING'

, docFileName = > 'New_109.doc'

, imgFileName = > 'Unresolved.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 210

, description = > 'Trouble Ticket 210'

, submit_dts = > '2009-01-14 12 purl 45purl 00'

, status = > 'OPEN'

, docFileName = > 'New_110.doc'

, imgFileName = > 'DBRIssues.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 301

, description = > 'Trouble Ticket 301'

, submit_dts = > '2008-12-31 23V 45purl 00'

, status = > 'CLOSED'

, docFileName = > 'New_101.doc'

, imgFileName = > 'DBRIssues.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 302

, description = > 'Trouble Ticket 302'

, submit_dts = > '2009-01-04 00 purl 0000'

, status = > 'OPEN'

, docFileName = > 'New_102.doc'

, imgFileName = > 'Unresolved.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 303

, description = > 'Trouble Ticket 303'

, submit_dts = > '2009-01-02 00 purl 0000'

, status = > 'OPEN'

, docFileName = > 'New_103.doc'

, imgFileName = > 'Unresolved.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 304

, description = > 'Trouble Ticket 304'

, submit_dts = > '2009-01-14 1215 30 purl 00'

, status = > 'CLOSED'

, docFileName = > 'New_104.doc'

, imgFileName = > 'DBRIssues.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 305

, description = > 'Trouble Ticket 305'

, submit_dts = > '2009-01-09 00 purl 0000'

, status = > 'PENDING'

, docFileName = > 'New_105.doc'

, imgFileName = > 'Unresolved.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 306

, description = > 'Trouble Ticket 306'

, submit_dts = > '2009-01-11 00 purse 00'

, status = > 'CLOSED'

, docFileName = > 'New_106.doc'

, imgFileName = > 'Unresolved.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 307

, description = > 'Trouble Ticket 307'

, submit_dts = > '2009-01-1600 purl 0000'

, status = > 'OPEN'

, docFileName = > 'New_107.doc'

, imgFileName = > 'DBRIssues.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 308

, description = > 'Trouble Ticket 308'

, submit_dts = > '2009-01-12 00 purse 00'

, status = > 'OPEN'

, docFileName = > 'New_108.doc'

, imgFileName = > 'Unresolved.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 309

, description = > 'Trouble Ticket 309'

, submit_dts = > '2009-01-02 00 purl 0000'

, status = > 'CLOSED'

, docFileName = > 'New_109.doc'

, imgFileName = > 'Unresolved.jpg'

);

Trbtkt.pkg_securefiles.AddTroubleTicket (

Tkt_id = > 310

, description = > 'Trouble Ticket 310'

, submit_dts = > '2009-01-14 12 purl 45purl 00'

, status = > 'CLOSED'

, docFileName = > 'New_110.doc'

, imgFileName = > 'DBRIssues.jpg'

);

COMMIT

END

/

-- collect optimizer statistics

BEGIN

DBMS_STATS.GATHER_SCHEMA_STATS (ownname = > 'TRBTKT', CASCADE = > TRUE)

END

/

Effectively migrate from BasicFile to SecureFile

Now that these tables are partitioned for more efficient use in a data warehouse environment, I have considered how to quickly and efficiently convert LOB from BasicFile format to the corresponding SecureFile format. Fortunately, Oracle 11g provides two methods: partition swapping and online redefinition.

1. Partition switching

Partition swap loading (Partition Exchange Load, that is, PDL), sometimes called partition rotation, involves creating a non-partitioned table whose format and content exactly match the desired partition of the target partition table. Once the original table is created, you can start swapping the target partition of the partitioned table. This method does provide many benefits. In other words, the local index of the original table is maintained during the exchange. It can be executed in parallel, and this method can be repeated multiple times to populate each partition. Its main disadvantage is that the partition that receives the data must be offline during the exchange.

2. Online redefinition

Oracle recommends using the DBMS_REDEFINITION package to perform online redefinitions of the source and target tables because it achieves the goal of using the same partition swapping method. But it does not need to take the target offline during the redefinition process. Online redefinition can be performed at the table level or at the partition level, or it can be performed in parallel for multiple partitions at the same time. I like the feature of online redefinition is that after the successful execution of this process, the source and target objects will actually transform the definition in the database data directory, which means that any PL/SQL objects that reference the new object name do not need to be modified. Listing 3 shows how to use the TRBTKT.TICKETS table as the source object and the TRBTKT.SECURE_TICKETS table as the target object for online redefinition. The final consideration for the conversion from BasicFile LOB to SecureFile LOB is that once the conversion is complete, LOB can no longer be degraded back to BasicFile LOB.

Manage SecureFile metadata

These new SecureFile features undoubtedly extend the capabilities of LOB, and for DBA, you need to pay attention to several additional attributes added to traditional BasicFile LOB. Fortunately, Oracle 11g provides multiple ways to track BasicFile and SecureFile LOB metadata.

1. Data dictionary view

Oracle 11g has also updated several data dictionary views on SecureFile information, which are listed below.

Listing 4 shows several SQL*Plus queries that use these data dictionary views to return information about SecureFile metadata, and report 1 shows the results returned from these queries.

Listing 4 queries metadata for BasicFile and SecureFile LOB

SET PAGESIZE 1000

SET LINESIZE 140

-- View: DBA_SEGMENTS

-- display metadata about BasicFile and SecureFile segments

TTITLE 'LOB Segment Information | (from DBA_SEGMENTS)'

COL segment_name FORMAT A30 HEADING 'Segment Name'

COL segment_type FORMAT A20 HEADING 'Segment | Type'

COL segment_subtype FORMAT A20 HEADING 'Segment | SubType'

COL partition_name FORMAT A12 HEADING 'Partition | Name'

COL tablespace_name FORMAT A12 HEADING 'Tablespace'

SELECT

Segment_name

, segment_type

, segment_subtype

, partition_name

, tablespace_name

FROM dba_segments

WHERE owner = 'TRBTKT'

ORDER BY segment_name

TTITLE OFF

-- View: DBA_LOBS

-- display metadata about BasicFile and SecureFile LOB

TTITLE 'BasicFile and SecureFile LOBs Metadata | (from DBA_LOBS)'

COL table_name FORMAT A14 HEADING 'Table'

COL segment_name FORMAT A26 HEADING 'Segment'

COL column_name FORMAT A10 HEADING 'Column'

COL tablespace_name FORMAT A12 HEADING 'Tablespace'

COL logging FORMAT A08 HEADING 'Logging'

COL cache FORMAT A10 HEADING 'Cacheing'

COL in_row FORMAT A07 HEADING 'Stored | In Row'

COL encrypt FORMAT A07 HEADING 'Encryp- | tion'

COL compression FORMAT A07 HEADING 'Compre- | ssion'

COL deduplication FORMAT A08 HEADING 'DeDupli- | cation'

COL securefile FORMAT A07 HEADING 'Secure | File?'

COL partitioned FORMAT A07 HEADING 'Parti- | tioned'

SELECT

Table_name

, column_name

, segment_name

, tablespace_name

, logging

, cache

, in_row

, encrypt

, compression

, deduplication

, securefile

, partitioned

FROM dba_lobs

WHERE owner = 'TRBTKT'

ORDER BY table_name, column_name

TTITLE OFF

-- View: DBA_PART_LOBS

-displays the default values for BasicFile and SecureFile LOB

TTITLE 'BasicFile and SecureFile Partitioned LOB Default Settings | (from DBA_PART_LOBS)'

COL table_name FORMAT A20 HEADING 'Table'

COL column_name FORMAT A12 HEADING 'Column'

COL def_cache FORMAT A12 HEADING 'Cached'

COL def_tablespace_name FORMAT A12 HEADING 'Tablespace'

COL def_securefile FORMAT A12 HEADING 'SecureFile'

COL def_encrypt FORMAT A12 HEADING 'Encrypted'

COL def_compress FORMAT A12 HEADING 'Compressed'

COL def_deduplicate FORMAT A12 HEADING 'DeDuplicated'

SELECT

Table_name

, column_name

, def_cache

, def_tablespace_name

, def_securefile

, def_compress

, def_deduplicate

, def_encrypt

FROM dba_part_lobs

WHERE table_owner = 'TRBTKT'

ORDER BY table_name, column_name

TTITLE OFF

-- View: DBA_LOB_PARTITIONS

-- describe BasicFile and SecureFile settings at a separate LOB level

TTITLE 'BasicFile and SecureFile LOB Partitions | (from DBA_LOB_PARTITIONS)'

COL table_name FORMAT A16 HEADING 'Table'

COL column_name FORMAT A12 HEADING 'Column'

COL partition_name FORMAT A12 HEADING 'Stored in | Partition'

COL cache FORMAT A10 HEADING 'Cacheing'

COL in_row FORMAT A10 HEADING 'Stored | In Row'

COL encrypt FORMAT A10 HEADING 'Encrypted'

COL compression FORMAT A10 HEADING 'Compressed'

COL deduplication FORMAT A10 HEADING 'DeDupli- | cated'

COL securefile FORMAT A10 HEADING 'SecureFile?'

SELECT

Table_name

, column_name

, partition_name

, cache

, in_row

, encrypt

, compression

, deduplication

, securefile

FROM dba_lob_partitions

WHERE table_owner = 'TRBTKT'

ORDER BY table_name, column_name

TTITLE OFF

Report 1 results returned from BasicFIle and SecureFile LOB metadata reports

SET PAGESIZE 1000

SET LINESIZE 140

-- View: DBA_SEGMENTS

-- display metadata about BasicFile and SecureFile segments

TTITLE 'LOB Segment Information | (from DBA_SEGMENTS)'

COL segment_name FORMAT A30 HEADING 'Segment Name'

COL segment_type FORMAT A20 HEADING 'Segment | Type'

COL segment_subtype FORMAT A20 HEADING 'Segment | SubType'

COL partition_name FORMAT A12 HEADING 'Partition | Name'

COL tablespace_name FORMAT A12 HEADING 'Tablespace'

SELECT

Segment_name

, segment_type

, segment_subtype

, partition_name

, tablespace_name

FROM dba_segments

WHERE owner = 'TRBTKT'

ORDER BY segment_name

TTITLE OFF

-- View: DBA_LOBS

-- display metadata about BasicFile and SecureFile LOB

TTITLE 'BasicFile and SecureFile LOBs Metadata | (from DBA_LOBS)'

COL table_name FORMAT A14 HEADING 'Table'

COL segment_name FORMAT A26 HEADING 'Segment'

COL column_name FORMAT A10 HEADING 'Column'

COL tablespace_name FORMAT A12 HEADING 'Tablespace'

COL logging FORMAT A08 HEADING 'Logging'

COL cache FORMAT A10 HEADING 'Cacheing'

COL in_row FORMAT A07 HEADING 'Stored | In Row'

COL encrypt FORMAT A07 HEADING 'Encryp- | tion'

COL compression FORMAT A07 HEADING 'Compre- | ssion'

COL deduplication FORMAT A08 HEADING 'DeDupli- | cation'

COL securefile FORMAT A07 HEADING 'Secure | File?'

COL partitioned FORMAT A07 HEADING 'Parti- | tioned'

SELECT

Table_name

, column_name

, segment_name

, tablespace_name

, logging

, cache

, in_row

, encrypt

, compression

, deduplication

, securefile

, partitioned

FROM dba_lobs

WHERE owner = 'TRBTKT'

ORDER BY table_name, column_name

TTITLE OFF

-- View: DBA_PART_LOBS

-displays the default values for BasicFile and SecureFile LOB

TTITLE 'BasicFile and SecureFile Partitioned LOB Default Settings | (from DBA_PART_LOBS)'

COL table_name FORMAT A20 HEADING 'Table'

COL column_name FORMAT A12 HEADING 'Column'

COL def_cache FORMAT A12 HEADING 'Cached'

COL def_tablespace_name FORMAT A12 HEADING 'Tablespace'

COL def_securefile FORMAT A12 HEADING 'SecureFile'

COL def_encrypt FORMAT A12 HEADING 'Encrypted'

COL def_compress FORMAT A12 HEADING 'Compressed'

COL def_deduplicate FORMAT A12 HEADING 'DeDuplicated'

SELECT

Table_name

, column_name

, def_cache

, def_tablespace_name

, def_securefile

, def_compress

, def_deduplicate

, def_encrypt

FROM dba_part_lobs

WHERE table_owner = 'TRBTKT'

ORDER BY table_name, column_name

TTITLE OFF

-- View: DBA_LOB_PARTITIONS

-- describe BasicFile and SecureFile settings at a separate LOB level

TTITLE 'BasicFile and SecureFile LOB Partitions | (from DBA_LOB_PARTITIONS)'

COL table_name FORMAT A16 HEADING 'Table'

COL column_name FORMAT A12 HEADING 'Column'

COL partition_name FORMAT A12 HEADING 'Stored in | Partition'

COL cache FORMAT A10 HEADING 'Cacheing'

COL in_row FORMAT A10 HEADING 'Stored | In Row'

COL encrypt FORMAT A10 HEADING 'Encrypted'

COL compression FORMAT A10 HEADING 'Compressed'

COL deduplication FORMAT A10 HEADING 'DeDupli- | cated'

COL securefile FORMAT A10 HEADING 'SecureFile?'

SELECT

Table_name

, column_name

, partition_name

, cache

, in_row

, encrypt

, compression

, deduplication

, securefile

FROM dba_lob_partitions

WHERE table_owner = 'TRBTKT'

ORDER BY table_name, column_name

TTITLE OFF

Report 1 results returned from BasicFIle and SecureFile LOB metadata reports

LOB segment information

(from DBA_SEGMENTS)

Segment Segment Partition

Segment Name Type SubType Name Tablespace

--

SECURE_TICKETS TABLE PARTITION ASSM STS_PENDING USERS

SECURE_TICKETS TABLE PARTITION ASSM STS_CLOSED USERS

SECURE_TICKETS TABLE PARTITION ASSM STS_OPEN USERS

SECURE_TICKETS TABLE PARTITION ASSM STS_OTHER USERS

SYS_IL0000072118C00005 $$INDEX PARTITION ASSM SYS_IL_P180 BASICFILES

SYS_IL0000072118C00005 $$INDEX PARTITION ASSM SYS_IL_P179 BASICFILES

SYS_IL0000072118C00005 $$INDEX PARTITION ASSM SYS_IL_P178 BASICFILES

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.