这篇文章阐述了如何管理oracle ERP的interface表
这篇文章阐述了如何管理oracle ERP的interface表
这篇文章阐述了如何管理oracle ERP的interface表
http://blog.oraclecontractors.com/?p=212
There are a number of tables used by Oracle Applications that should have no rows in them when all is running well, and if any, only a few rows that are in error. Open Interface tables are an example of such tables; they are populated with data to be interfaced in to the system, and an interface specific concurrent program is then used to validate the data and import it in to the main system tables. Once the row has been imported it is removed from the interface table, leaving only invalid rows behind.
Assuming that the system is correctly configured and these interfaces are working correctly, the table is left empty.
Often, when these tables are accessed it is to process all the rows in the table, which involves a full table scan. However over time, depending on data volumes, these tables can have a large number of blocks allocated to them that no longer contain data.
A table segment is divided in to used blocks (i.e. blocks that at some time contained data) and free blocks (i.e. blocks that have never contained data) and the point of separation between the 2 groups is called the high water mark. Whenever Oracle performs a full table scan, it scans all the used blocks up to the high watermark.
I have found that when I am working with interface tables, they often seem to take a long time to return data; this is down to there being a huge number of used blocks that no longer contain data. Certainly if open interface tables are used during the go live for data migration, then the high watermark will be very high.
Examples of such tables are:
MTL_TRANSACTIONS_INTERFACE
OE_HEADERS_IFACE_ALL
RA_INTERFACE_LINES_ALL
SQL> set timing on
SQL> select count(*)
2 from MTL_TRANSACTIONS_INTERFACE
3 /
COUNT(*)
———
20
real: 2750
Nearly 3 seconds to count 20 rows!
As these tables are interface tables used b
相关文档:
1)导入oracle数据库文件.dmp时出现错误
imp PERSONNEL_MANAGE/MWQ@MIS file=C:\personnel_manage.dmp fromuser=PERSONNEL_MANAGE ignore=y commit=y grants=y;
IMP-00058: 遇到 ORACLE 错误 1017
ORA-01017: invalid username/password; logon denied用户名:
口令:
IMP-00058: 遇到 ORACLE 错误 1017
ORA-01017: in ......
一 数据库的事务处理
定义:事务是一组相关的数据改变的逻辑集合。在一个事务中的数据改变(DML)保持着一致的状态,数据的改变同时成功或者同时失败。
二 数据库的事务由下列语句组成
一组DML语句,修改的数据在他们中保持一致
一个 DDL (Data Define Language) 语句
一个 DCL (Data Control Language)语句
1、开 ......
oracle内置程序包
STANDARD和DBMS_STANDARD 定义和扩展PL/SQL语言环境
DBMS_ALERT 支持数据库事件的异步通知
DBMS_APPLICATION_INFO 允许为跟踪目的而注册应用程序
DBMS_AQ&DBMS_AQADM 管理oracle advanced queuing选件
DBMS_DEFER、DBMS_DEFER_SYS和DBMS_DEFER_QUERY 允许构建和管理延迟的远程过程调用
DBMS_DDL ......
一、语法
大致写法:select * from some_table [where 条件1] connect by [条件2] start with [条件3];
其中 connect by 与 start with 语句摆放的先后顺序不影响查询的结果,[where 条件1]可以不需要。
[where 条件1]、[条件2]、[条件3]各自作用的范围都不相同:
[where 条件1]是在根据“connect by [条件2] ......
pl/sql 表
在pl/sql块中临时使用、像数组一样的对象
包含一列和一个主键
不能对列和主键进行命名
列可以是任何标量数据类型
主键必须是binary_integer类型
大小没有限制
声明pl/sql表
定义表的类型
type 类型名 is table of 列类型|变量数据类型 ......