这篇文章阐述了如何管理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
相关文档：
需要大量oracle测试数据时，可以使用以下方法。
DECLARE
i INT;
BEGIN
i := 0;
WHILE(i < 100000)
LOOP
i := i + 1;
INSERT INTO TEST_TABLE(ID, XM) VALUES(i, '姓名' || i);
END LOOP;
COMMIT;
END; ......
--查出所有被锁住的表
select b.owner TABLEOWNER, b.object_name TABLENAME, c.OSUSER LOCKBY, c.USERNAME LOGINID, c.sid SID, c.SERIAL# SERIAL
from v$locked_object a,dba_objects b, v$session c
where b.object_id = a.object_id AND a.SESSION_ID =c.sid;
--通过SID, SERIAL解锁
--alter system kill sessio ......
启动各个模式的过程：
1.nomount ----读参数文件---扩内存/启进程（主要是重建控制文件）
2.mount ------读参数文件---找控制文件---开控制文件---找数据文件/日志文件位置与名称---联系实例与数据库
（主要是恢复数据库）
3.open--------打开数据文件---打开日志文件 ......
成员函数和过程
构造函数方法
-oracle的每个对象均有一个内置的构造函数方法
-用于创建对象的一个实例
-无需显式地定义
-与对象类型同名
定义对象类型
create or replace
type persontype as object
(
id int,
name varchar(20),
member function ......
