WEB开发网
开发学院数据库Oracle Oracle中如何从BasicFile迁移到SecureFile 阅读

Oracle中如何从BasicFile迁移到SecureFile

 2009-04-17 13:13:04 来源:WEB开发网   
核心提示: 1、数据字典视图Oracle 11g也更新了多个关于SecureFile信息的数据字典视图,下面列出这些做了变动的视图,Oracle中如何从BasicFile迁移到SecureFile(5),清单4显示了多条SQL*Plus查询,它们使用了这些数据字典视图返回关于SecureFile元数据

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 
 

上一页  1 2 3 4 5 6 7  下一页

Tags:Oracle 如何 BasicFile

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接