EBS 清理附件表空间apps_ts_media表空间内附件fnd_lobs
来源:互联网 发布:爱优软件 编辑:程序博客网 时间:2024/06/07 02:53
参考文档:
FND_LOBS table's FILE_DATA LOB column usually gets the data uploaded only once, but is read multiple times. Hence it is not necessary to keep older versions of LOB data. It is recommended that this value be changed to "0".
By default PCTVERSION is set to 10%. So, most of the instances usually have it set to 10%, it must be set to 0% explicitly. The value can be changed any time in a running system.
Use the following query to find out currently set value for PCTVERSION:
select PCTVERSION from dba_lobs where TABLE_NAME = 'FND_LOBS' and COLUMN_NAME='FILE_DATA';
PCTVERSION
----------
10
PCTVERSION can be changed using the following SQL (it can be run anytime in a running system):
ALTER TABLE FND_LOBS MODIFY LOB (FILE_DATA) ( PCTVERSION 0 );
Please be aware :
The solution shows, how to avoid the increase of the table in future - it does not shrink the current tablespace !
The only supported way to shrink the tablespace is to export and re-import the whole Database.
oracle创建了两个objects TEST这个是我们很熟悉的表了,PCT_LOB就是LOB了
创建了三个segment,一个是表,一个是LOBSEGMENT,另外一个是LOBINDEX
这个看上去好像莫名其妙的LOBINDEX就是主要为我们的LOB read consistency服务的
注意我们在创建表的时候指明了disable storage in row,所以lob存储将采取out-line的方式存储到LOBSEGMENT中。
如果是enable storage in row的话,那么长度小于3960bytes的采用in-line的方式存储,那么这时undo, redo的产生和普通的数据一样。
修改数据的时候old version存储在回滚段中,这样query可以利用undo信息重构block生成其所需的前镜像。
当采用out-line的方式存储的时候,这时读一致性可以说完全是由LOBINDEX+LOGSEGMENT来保证的。
LOBINDEX类似于B-tree的结构,存储各个LOB entry的LOB ID,LOB ID指向LOBSEGMENT中的实际存储区域。
如果要delete一条数据,删除的操作就是更新一下LOBINDEX, 并不会去将LOBSEGMENT中的内容写入回滚段中,这时候有少量的undo信息产生,但是是因为修改LOGINDEX产生的。
如果是update LOB,并不是去update原来的LOB entry,而是插入一条新的LOB entry,并且对LOB自身不产生undo信息,原来旧的数据仍然存放于LOBSEGMENT中(LOBSEGMENT中会存储相关的SCN信息)
这样query需要读取old version的数据就不是从回滚段中读取old value来重构,而是从LOBSEGMENT中读取原先的LOB entry。
如果delete,update操作很多,oracle会不会一直保存这些old version的数据呢?答案是不会。
这时就靠我们上面在创建LOB时的参数PCTVERSION来控制了。PCTVERSION=10的含义就是在HWM下留有10%的空间用于存放Old version的数据.
如果存放old version的空间多于PCTVERSION,那么就可以被重用。这时如果有query需要重构旧的数据,就会产生ORA-01555错误。
想要避免01555的话一个就是尽量缩短query的时间,另外就是增大PCTVERSION,当然这会消耗更多的空间存放旧数据。
那么最后一个问题是如果辨别ORA-01555是不是发生在LOB上的
一般来说,普通的01555错误会指明发生01555的rollback segment,而LOB的则没有,而是伴随着ORA-22924出现
xfan-tiger1$> oerr ora 22924
22924, 00000, “snapshot too old”
// *Cause: The version of the LOB value needed for the consistent read was
// already overwritten by another writer.
// *Action: Use a larger version pool.
xfan-tiger1$> oerr ora 01555
01555, 00000, “snapshot too old: rollback segment number %s with name \”%s\” too small”
// *Cause: rollback records needed by a reader for consistent read are
// overwritten by other writers
// *Action: If in Automatic Undo Management mode, increase undo_retention
// setting. Otherwise, use larger rollback segments
参考:http://www.dbafan.com/blog/?p=11
参考网页:
http://note.youdao.com/share/?id ... fff9f582d&type=note
There is only one standard method to purge the table, that is concurrent program "Purge Obsolete Generic File Manager Data".
This program will not purge FND_HELP and other records without EXPIRATION_DATE. It will purge the data with program_name=export and the data with an expiration date.
You can use following SQL statement to query the data which has no expiration date
select program_name,count(*)
from FND_LOBS
where expiration_date is NULL
group by program_name;
An example output would look like:
PROGRAM_NAME COUNT(*)
-------------------------------- ----------
FND_HELP 46439
export 9
FNDATTCH 6
FND_HELP is the data of iHelp and should not be deleted otherwise you will not use the help function.
export is the data generated by "export" from form, this kind of data can be purged freely.
FNDATTCH is attachment.
There are also other kinds of "program_name" from the result, even "null" program_name, these files are from different modules and products, you may need to check with end users if they can be purged.
Please keep in mind, the FND_LOBS table is used by different Oracle Applications features (such as Export, Attachments, FND Help, etc) and different application product modules utilize FND_LOBS to store a variety of data, both temporary data and active on-going business data. There is no any program or utility to mass purge business records because we don't know which file is useful and which is not.
In summary, to purge the data of FND_LOBS, you need to set the data to have an EXPIRATION_DATE and then run the concurrent program. We can only suggest to keep "FND_HELP" untouched and for other files, you need to check with application users.
If you are not sure what the file is, you can follow Note 1457782.1 to download the file and then check with end-users.
Other methods to delete/rebuild the table are not supported.
Best Regards
Vincent
Oracle Support
Thanks a lot for your explaination and I got to know it.
I have checked that all the attachments that uploaded by users have its colum in FND_LOBS with the value of NULL for both EXPIRATION_DATE and UPLOAD_DATE.
so to purge these part of attachments(confirmed these attachments to be useless in the test environmnet), what I do is like following:
1. Set a real date before for the colum of EXPIRATION_DATE for all that part of attachments( EXPIRATION_DATE is null and UPLOAD_DATE is null).
2. run the request "Purge Obsolete Generic File Manager Data" to purge the data .
3. I guess the size of SYS_LOB0000048967C00004$$ would be reduced after that.
is that all right?
thanks and regards
Step 1 and 2 are correct method to purge the table FND_LOBS.
Regarding the question 3, the size of the segment does not change.
You need to perform additional steps like re-allocate to reduce the size of a segment.
You can refer to Note 303709.1 for details. If you need more assistance regarding the sizing issue, please open a new SR with product "Oracle Applications Technology Stack"-->"Database".
Best Regards
Vincent
Oracle Support
Assign the "Purge Obsolete Generic File Manager Data" Concurrent Program to the Request Group "System Administrator Reports". 1. Access Oracle Applications as the Sysadmin user. 2. Select the System Administrator responsibility. 3. Navigate to the following menus: Security / Responsibility / Request 4. Place the cursor in the "Group" field of the "Request Groups" form.5. Select "View" from the menu. Select "Find" from the menu. Select "System Administrator Reports".6. Place the cursor in the "Type" column. Use "File" from the menu to create a "New" record. 7. Set the "Type" field to "Program".8. Select "Purge Obsolete Generic File Manager Data" for the "Name" field.9. Select "File" from the menu. Select "Save and Proceed" from the menu.
Program Parameters:
- Expired: Enter "Y" if you want to purge expired data only. Enter "N" if you want the purge to include all data. The default is "Y."
- Program Name:Enter the program name(s) to process. Leave blank to process all programs.
- Program Tag:Enter the program tag(s) to process. Leave blank to process all program tags. Tags are identifiers used by the Generic File Manager for categorization purposes.
- EBS 清理附件表空间apps_ts_media表空间内附件fnd_lobs
- EBS 清理附件表空间apps_ts_media表空间内附件fnd_lobs
- 清理表空间
- ORACLE EBS 表空间控制
- ORACLE EBS 增加表空间
- Confluence 空间附件(Attachments )宏
- EBS附件功能
- Oracle EBS附件下载功能
- EAS BOS附件表
- 附件
- 附件
- 附件
- 清理UNDOTBS表空间,获取20G空间
- 清理临时表空间满的方法
- oracle数据库如何清理临时表空间
- oracle临时表空间的清理
- 00009.Oracle清理临时表空间
- oracle清理数据库的临时表空间
- linux kill -3
- sql 语句开启、关闭标识列
- 山寨智能机发展
- Drupal 7中 page.tpl.php 的可用变量
- IMPDP :Schema XXX was not found
- EBS 清理附件表空间apps_ts_media表空间内附件fnd_lobs
- linux设备模型之platform总线(转)
- android library projects cannot be launched
- must implement the inherited abstract method 问题解决
- 软件开发人员需要的不仅是技术,也不是文档,也不是管理,而是……
- 数字图像处理中的形态学
- Flash制作简单塔防游戏(一)
- Hash函数
- c/c++ 算法之快速排序法 冒泡排序法,选择排序法,插入排序法