Script:查找表或索引增长的历史信息

有同学在Oracle ALL STARS群中提问 如何通过AWR来查找一段时间内,数据库段对象(堆表、索引)等的空间增长信息。

 

在Oracle 10g开始awr自动负载仓库引入了dba_hist_seg_stat视图,该视图记录了快照时间内segment-level段级的历史统计信息:DBA_HIST_SEG_STAT displays historical information about segment-level statistics. This view captures the top segments based on a set of criteria and captures information from V$SEGSTAT. The total value is the value of the statistics since instance startup. The delta value is the value of the statistics from the BEGIN_INTERVAL_TIME to the END_INTERVAL_TIME in the DBA_HIST_SNAPSHOT view.

 

我们可以通过以下SQL脚本来列出相关段对象在 快照时间内的使用空间的历史变化信息:

 

 

column owner format a16
column object_name format a36
column start_day format a11
column block_increase format 9999999999

select   obj.owner, obj.object_name,
         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD') start_day,
         sum(a.db_block_changes_delta) block_increase
from     dba_hist_seg_stat a,
         dba_hist_snapshot sn,
         dba_objects obj
where    sn.snap_id = a.snap_id
and      obj.object_id = a.obj#
and      obj.owner not in ('SYS','SYSTEM')
and      end_interval_time between to_timestamp('01-JAN-2000','DD-MON-RRRR')
         and to_timestamp('02-FEB-2013','DD-MON-RRRR')
group by obj.owner, obj.object_name,
         to_char(sn.BEGIN_INTERVAL_TIME,'RRRR-MON-DD')
order by obj.owner, obj.object_name
/

 

 

使用示例:

 

Goldengate一个令人郁闷的小细节

晚上有兴趣测试了下Goldengate的initial load功能,通过initial load+change sync可以很容易做到数据迁移data migration;不过发现一个令人很郁闷的细节,就是replicat的params中定义map参数时TARGET到前面一个逗号间要留一个空格space:

如果map参数中target和前面的逗号间没有空格会出现invaild option for map错误:

GGSCI (rh2.oracle.com) 32> view params init1
extract init1
userid ggate,password ggate
RMTHOST rh3.oracle.com,MGRPORT 7809
RMTTASK replicat,GROUP  init3
table sh.products;

GGSCI (rh3.oracle.com) 40> view params init3

replicat init3
userid ggate,password ggate
ASSUMETARGETDEFS
map sh.products,target sh.products;

GGSCI (rh2.oracle.com) 33> start extract init1

Sending START request to MANAGER ...
EXTRACT INIT1 starting

GGSCI (rh3.oracle.com) 41> view report init3
..............
MAP resolved (entry SH.PRODUCTS):
  map SH.PRODUCTS, sh.products;

Source Context :
  SourceModule            : [er.main]
  SourceID                : [/home/ecloud/workspace/Build_FBO_OpenSys_r11.1.1.0.0_078_[34093]/perforce/src/app/er/rep.c]
  SourceFunction          : [get_map_entry]
  SourceLine              : [8573]
  ThreadBacktrace         : [11] elements
                          : [./replicat(CMessageContext::AddThreadContext()+0x26) [0x5d9516]]
                          : [./replicat(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x7b2) [0x5cffb2]]
                          : [./replicat(_MSG_ERR_STARTUP_PARAMERROR_INVALID_OPTION_VALUE
(CSourceContext*, char const*, char const*, CMessageFactory::MessageDisposition)
+0x9b) [0x59086b]]
                          : [./replicat(get_map_entry(char*, int, __wc*, int)+0x284d) [0x4ee66d]]
                          : [./replicat [0x5357d5]]
                          : [./replicat(WILDCARD_check_table(char const*, char const*, int, 
unsigned int*, int, unsigned int, DBString<777>*, int)+0x15e) [0x536fce]]
                          : [./replicat(REP_find_source_file_wc(char const*, unsigned int, DBString<777>*, int)+0x64f) [0x8a1caf]]
                          : [./replicat [0x8a9b04]]
                          : [./replicat(main+0x227a) [0x4f50da]]
                          : [/lib64/libc.so.6(__libc_start_main+0xf4) [0x340d01d994]]
                          : [./replicat(__gxx_personality_v0+0x1e2) [0x4d86ba]]

2010-12-01 08:58:30  ERROR   OGG-00212  Invalid option for MAP: sh.products.

/*我们来加上空格*/

GGSCI (rh3.oracle.com) 43> view params init3
replicat init3
userid ggate,password ggate
ASSUMETARGETDEFS
map sh.products, target sh.products;

GGSCI (rh3.oracle.com) 43> view report init3
MAP resolved (entry SH.PRODUCTS):
  map SH.PRODUCTS, target sh.products;

2010-12-01 09:00:30  WARNING OGG-00869  No unique key is defined for table PRODUCTS. 
All viable columns will be used to represent the key, but may not guarantee uniqueness.  
KEYCOLS may be used to define the key.
Using following columns in default map by name:
  PROD_ID, PROD_NAME, PROD_DESC, PROD_SUBCATEGORY, PROD_SUBCATEGORY_ID, 
  PROD_SUBCATEGORY_DESC, PROD_CATEGORY, PROD_CATEGORY_ID, 
  PROD_CATEGORY_DESC, PROD_WEIGHT_CLASS, PROD_UNIT_OF_MEASURE, 
  PROD_PACK_SIZE, SUPPLIER_ID, PROD_STATUS, PROD_LIST_PRICE, 
  PROD_MIN_PRICE, PROD_TOTAL, PROD_TOTAL_ID, PROD_SRC_ID, 
  PROD_EFF_FROM, PROD_EFF_TO, PROD_VALID

/* 加上空格后初始化成功*/

沪ICP备14014813号

沪公网安备 31010802001379号