Oracle中如何从BasicFile迁移到SecureFile
2009-04-17 13:13:04 来源:WEB开发网1、数据字典视图
Oracle 11g也更新了多个关于SecureFile信息的数据字典视图,下面列出这些做了变动的视图。
清单4显示了多条SQL*Plus查询,它们使用了这些数据字典视图返回关于SecureFile元数据的信息,报告1显示了从这些查询返回的结果。
清单4 查询BasicFile和SecureFile LOB的元数据
SET PAGESIZE 1000
SET LINESIZE 140
-- 视图: DBA_SEGMENTS
--显示关于BasicFile 和SecureFile 段的元数据
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
-- 视图: DBA_LOBS
--显示关于BasicFile和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 loggingFORMAT A08 HEADING 'Logging'
COL cache FORMAT A10 HEADING 'Cacheing'
COL in_row FORMAT A07 HEADING 'Stored|In Row'
COL encryptFORMAT 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
--视图: DBA_PART_LOBS
--显示BasicFile和SecureFile LOB的默认值
TTITLE 'BasicFile and SecureFile Partitioned LOB Default Settings|(from DBA_PART_LOBS)'
COL table_name FORMAT A20 HEADING 'Table'
COL column_nameFORMAT 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_encryptFORMAT 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
--视图: DBA_LOB_PARTITIONS
--在独立的LOB级描述BasicFile和SecureFile设置
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 encryptFORMAT 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
报告1 从BasicFIle和SecureFile LOB元数据报告返回的结果
SET PAGESIZE 1000
SET LINESIZE 140
-- 视图: DBA_SEGMENTS
--显示关于BasicFile 和SecureFile 段的元数据
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
-- 视图: DBA_LOBS
--显示关于BasicFile和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 loggingFORMAT A08 HEADING 'Logging'
COL cache FORMAT A10 HEADING 'Cacheing'
COL in_row FORMAT A07 HEADING 'Stored|In Row'
COL encryptFORMAT 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
--视图: DBA_PART_LOBS
--显示BasicFile和SecureFile LOB的默认值
TTITLE 'BasicFile and SecureFile Partitioned LOB Default Settings|(from DBA_PART_LOBS)'
COL table_name FORMAT A20 HEADING 'Table'
COL column_nameFORMAT 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_encryptFORMAT 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
--视图: DBA_LOB_PARTITIONS
--在独立的LOB级描述BasicFile和SecureFile设置
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 encryptFORMAT 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
报告1 从BasicFIle和SecureFile LOB元数据报告返回的结果
LOB段信息
(来自DBA_SEGMENTS)
Segment Segment Partition
Segment Name Type SubType NameTablespace
------------------------- -------------------- -------------------- ------------ ------------
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
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和SecureFile LOB元数据
(来自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 NOYES NONE NONE NONE NO YES
SECURE_TICKETS SCRNIMG SYS_LOB0000072118C00006$$ BASICFILES NONE NOYES 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
BasicFile和SecureFile分区LOB默认设置
(来自DBA_PART_LOBS)
Table Column Cached Tablespace SecureFile Compressed DeDuplicated Encrypted
----- ------------ ------------ ------------ ------------ ------------ ------------ ------------
SECURE_TICKETS DOCUMENT NO BASICFILES NO NONENONENONE
SECURE_TICKETS SCRNIMG NO BASICFILES NO NONENONENONE
TICKETS DOCUMENT YES SECUREFILES YES NO NO NO
TICKETS SCRNIMG CACHEREADS SECUREFILES YES NO NO NO
BasicFile和SecureFile LOB分区
(来自DBA_LOB_PARTITIONS)
Stored in StoredDeDupli-
Table Column Partition Cacheing In Row Encrypted Compressed cated SecureFile
---- ------------ ------------ ---------- ---------- ---------- ---------- ---------- ----------
SECURE_TICKETS DOCUMENT STS_OTHER NOYES NONE NONE NONE NO
SECURE_TICKETS DOCUMENT STS_CLOSED NOYES NONE NONE NONE NO
SECURE_TICKETS DOCUMENT STS_OPEN NOYES NONE NONE NONE NO
SECURE_TICKETS DOCUMENT STS_PENDING NOYES NONE NONE NONE NO
SECURE_TICKETS SCRNIMG STS_OTHER NOYES NONE NONE NONE NO
SECURE_TICKETS SCRNIMG STS_CLOSED NOYES NONE NONE NONE NO
SECURE_TICKETS SCRNIMG STS_PENDING NOYES NONE NONE NONE NO
SECURE_TICKETS SCRNIMG STS_OPEN NOYES NONE NONE NONE NO
TICKETS DOCUMENT STS_OTHER YES NONOHIGH LOB YES
TICKETS DOCUMENT STS_PENDING YES NONONONOYES
TICKETS DOCUMENT STS_CLOSED YES NONOMEDIUM LOB YES
TICKETS DOCUMENT STS_OPEN YES NONONONOYES
TICKETS SCRNIMG STS_PENDING CACHEREADS NONOMEDIUM LOB YES
TICKETS SCRNIMG STS_OPEN CACHEREADS NONOMEDIUM NOYES
TICKETS SCRNIMG STS_CLOSED CACHEREADS NONOHIGH LOB YES
TICKETS SCRNIMG STS_OTHER CACHEREADS NONOHIGH LOB YES2、DBMS_SPACE
更多精彩
赞助商链接