ORACLE下大表相关测试

时间：2010-07-28 来源：fyuansheng

环境说明: 物理机: 2C+4G+1TB PC机(非SERVER) VMWARE: 2C+2G +100G 大表: "EI_SRCDATA_HIST"."OF_05_CL" 25.20 GB 178690871 rows

OWNER	SEGMENT_NAME	SEGMENT_TYPE	TABLESPACE_NAME	BYTES/1024/1024	BLOCKS	EXTENTS
EI_SRCDATA_HIST	IDX_OF_05_CL	INDEX	TBS_EI_SRCDATA_HIST_IDX	3257.25	416928	418
EI_SRCDATA_HIST	OF_05_CL	TABLE	TBS_EI_SRCDATA_HIST	29760	3809280	649

测试内容:

1\ 更改一字段char(6)--->char(12) 耗时多久

注意以下几点:

1) 更改中会占用大量的undo tablespace;留意undo 表空间是否够用

2) 更改中会产生大量的archivelog;留意归档目录是否有空间归档.

2\ expdp 导出此表要多久,

1.78亿条记录; 25.2GB 导出耗时 6分25秒 (注此操作非在测试机上进行，在AIX上导出的)

Export: Release 10.2.0.3.0 - 64bit Production on Wednesday, 28 July, 2010 10:10:18

Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=f_test dumpfile=EI_SRCDATA_HIST.OF_05_CL.dmp logfile=EI_SRCDATA_HIST.OF_05_CL.log tables=EI_SRCDATA_HIST.OF_05_CL
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 28.82 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "EI_SRCDATA_HIST"."OF_05_CL" 25.20 GB 178690871 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/VOL3/EI_SRCDATA_HIST.OF_05_CL.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 10:16:43