其实, 在ERP 领域, 最主要就是3种角色: Technical,Techno-Functional,Functional ,下面这个图对3个角色的职业路线做了一些简单的阐述. 这些角色无所谓好与不好, 只是每个人的职业倾向不一样而已.
|
|
Technical
|
Techno-Functional
|
Functional
|
|
教育背景
|
工学,理学,管理学
|
工学,理学,管理学
|
MBA,财务,管理
|
|
职责
|
开发
架构
DBA
研究员
|
技术
技术分析
商务分析
协调员
|
操作
|
|
任务
|
研发
|
产品支持
协调沟通
|
实现
培训
|
这是个很有意思的问题, 就是 Oracle 在什么样的情况下会自己做commit, rollback 动作.
结论是:
1, 如果是sqlplus, exit 则commit, 异常退出则 rollback.
2, 如果是Oracle ERP 的 request , 则commit (未测试过如果error呢,是否会rollback, 回头测试一把).
3,如果是dbms_job 则会commit. 如下引用一个 dbms_job 的例子:
- Create the test table.
SQL> create table babette ( thedate date, timesupdate number);
SQL> insert into babette values ( sysdate, 0, null, null);
SQL> commit;
SQL> select to_char(thedate, 'YYYY/MM/DD HH24:MI:SS'), timesupdate from babette;
TO_CHAR(THEDATE,'YY TIMESUPDATE
------------------- -----------
2007/02/26 14:50:21 0
- Create the procedure.
SQL> create or replace procedure update_babette is
2 begin
3 update babette set thedate = sysdate, timesupdate = timesupdate + 1;
4 end;
5 /
- Create the Scheduler job.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'BABETTE_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'avail.update_babette',
start_date => sysdate,
repeat_interval => 'FREQ=MINUTELY',
enabled => TRUE);
END;
/
- Confirm that the job is running.
select log_id, to_char(log_date,'YYYY/MM/DD HH24:MI') log_date,
owner, job_name, status,
to_char(req_start_date, 'YYYY/MM/DD HH24:MI') start_date,
to_char(actual_start_date,'YYYY/MM/DD HH24:MI') actual_start_dt
from DBA_SCHEDULER_JOB_RUN_DETAILS
where job_name = 'BABETTE_JOB' and actual_start_date > sysdate - 5
order by owner, actual_start_dt;
LOG_ID LOG_DATE OWNER JOB_NAME STATUS ACTUAL_START_DT
432292 2007/02/26 15:10 AVAIL BABETTE_JOB SUCCEEDED 2007/02/26 15:10
432294 2007/02/26 15:11 AVAIL BABETTE_JOB SUCCEEDED 2007/02/26 15:11
- Check if the table updates are kept and visible to other sessions.
SQL> select to_char(thedate, 'YYYY/MM/DD HH24:MI:SS'), timesupdate from babette;
TO_CHAR(THEDATE,'YY TIMESUPDATE
------------------- -----------
2007/02/26 15:10:23 3
有如下的几篇文章值得参考:
http://www.pythian.com/blogs/398/dbms_scheduler-and-implicit-commits
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/c926a81c85f67806?hl=en
Thursday, September 20th, 2007 by Sunil Chawla
Sub Ledger Accounting uptake
Oracle Fixed Assets is now fully integrated with Sub Ledger Architecture (SLA). Users can create/modify seeded Account Derivation Definitions (ADD) as per the business requirements. For backward compatibility, Account generator is supported for existing books. Users can use new SLA reporting and online account inquiry.
No need to use Create Journal Entries to post journals after SLA Uptake. After SLA setup, new Process is executed as:
- Perform Transactions in Oracle Assets
- Run the Create Accounting Program and it will process accounting.
- Inquire and drilldown from SLA Pages
Mass Additions enhancement for conversions
New attributes are added to ‘fa_mass_additions’ interface table such as:
- Asset life
- Depreciation method
- Prorate convention
- Bonus rule Ceiling name
- Depreciation limit etc.
Web ADI is also enhanced to accomodate the new attributes.
Automatic Preparation of Mass Additions
New features allow users to automatically prepare mass additions attributes like Expense Account, Category, Location etc.
User can either use Default Rule or can use custom API calls as per the bussiness requirements. It does not require manual intervention during Mass addition Process and public API will avoid customization.
Three new quickcodes are added for the same and one can be used as required. Qucikcodes are Default, custom, Custom Energy. New Concurrent Program Name is Prepare Mass Additions.
Reporting using XML Publisher
Several transaction reports are enhanced to support XML Publisher. New seeded templates can be modified as per the requirement. XML Reporting is now available for the following reports:
- Asset Transfers Report
- Transaction History Report
- Asset Reclassification Report
- Mass Additions Create Report
- Cost Adjustment Report
- Cost Summary Report
- CIP Summary Report
- Reserve Summary
- Journal Entry Reserve Ledger
- Asset Additions Report
- CIP Capitalization Report
- Mass Additions Posting
- Asset Retirements
Asset Level Depreciation Rollback
No more need to rollback depreciation for the whole book when just transaction on one or few assets is required. Depreciation is rolled back automatically by the system when any transaction is performed on an asset if the following conditions are met:
- Depreciation has been processed in that period and
- The period is not closed.
- The asset(s) for which depreciation was rolled back is automatically picked up during the next depreciation run or at the time that the depreciation period is finally closed.
Functionality enhancement related to the Energy Industry
Now ‘Units of production’ method for group asset is supported. One more depreciation method ‘Energy Straight line ‘ for energy industry is added. Depreciation calculation formula for the same is Net Book Value / Remaining Life.
今天在asktom里头看到这样一篇文章, 是关于Oracle到底是会select 哪个 temportary tablespace进行工作的?
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:930074500346678128
此用户有9个temp tablespace, 其中 5 个16G, 4 个3G. User 在做一个很大的动作的时候,经常抛出:
ERROR at line 1:
ORA-01652: unable to extend temp segment by 512 in tablespace TEMP_1
Tom的解答是:
Oracle 并没有公布任何文档来讲述select 是哪个 tablespace, 是自由的. 每个tablespace在oracle看来是平等的.
同时,这个用户还问了这样子一个问题:
如下的2个语句有什么区别?
create table a as select * from all_objects;
AND
create table a as select * from all_objects where 1=2;
insert into a select * from all_objects;
create table as select 将不会产生undo文件. 如果此table 是nologging的, 甚至redo 也不产生.
insert 动作恰好相反.
在沟通 AP , PA, 跟 FA 的时候, Mass Additions Create Process 是个很重要的request.
这里主要罗列一下 11 i 版本 跟 12 R版本在这块上面的区别:
1, 12R 插入的表是 F一个a global temporary table FA_MASS_ADDITIONS_GT , 11i下插入的是 FA_MASS_ADDITIONS
2, 11R 调用 FA_MASSADD_CREATE_PKG (famadcb.pls) 进行插入. 11i调用 FA_MASSADD_PKG (FAMAPTS.pls) 进行插入动作. 其实 ADI 应该也是调用这个口来插入.
3, FA: Include Nonrecoverable Tax in Mass Addition 这个profile 在 12R 下没了,取而代之的是 Non-Recoverable Taxes 模块.
4, asset book 和 asset category 是可以在invoice的line里头定义,并且传入FA.
相关的更多信息可以查看这个Oralce的文档.
12r_mass-additions-create
SUM (v.ytd) KEEP (DENSE_RANK LAST ORDER BY v.period_name) OVER (PARTITION BY v.concatenated_segments)
解释： 取出 按照 v.concatenated_segments 分区，按照 v.period_name 的最后一个。并且sum 一下。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
SELECT v.concatenated_segments, v.period_name, v.begin_balance, v.dr, v.cr
,v.ytd
,RANK () OVER (PARTITION BY v.concatenated_segments ORDER BY v.period_name)
MONTH
FROM cux_gl_summary_balances_v v
WHERE v.template_name = ‘D.T.三级科目汇总.T.T.T.T.T’
AND v.period_name BETWEEN ‘2007-01′ AND ‘2007-03′
AND v.concatenated_segments IN
(’060100.T.66012101.T.T.T.T.T’, ‘070100.T.66012606.T.T.T.T.T’)
AND v.currency_code = ‘CNY’
| CONCATENATED_SEGMENTS |
PERIOD_NAME |
BEGIN_BALANCE |
DR |
CR |
YTD |
MONTH |
| 060100.T.66012101.T.T.T.T.T |
2007-01 |
0 |
5594.48 |
0 |
5594.48 |
1 |
| 060100.T.66012101.T.T.T.T.T |
2007-02 |
5594.48 |
3549.69 |
0 |
9144.17 |
2 |
| 060100.T.66012101.T.T.T.T.T |
2007-03 |
9144.17 |
8602.4 |
0 |
17746.57 |
3 |
| 070100.T.66012606.T.T.T.T.T |
2007-02 |
0 |
26920 |
0 |
26920 |
1 |
| 070100.T.66012606.T.T.T.T.T |
2007-03 |
26920 |
0 |
0 |
26920 |
2 |
现在的要求是，比如针对 060100.T.66012101.T.T.T.T.T， 的期初，我们取 2007－01 的。
对于 YTD ，我们取 2007－03 的， 其他的DR，CR 是进行SUM，下面是实现的方法。
SELECT o.concatenated_segments, o.begin_balance, SUM (o.dr)
,SUM (o.cr), o.ytd
FROM (SELECT v.concatenated_segments, v.period_name, v.dr
,v.cr
,SUM (v.begin_balance)KEEP (DENSE_RANK FIRST ORDER BY v.period_name) OVER (PARTITION BY v.concatenated_segments)
begin_balance
,SUM (v.ytd) KEEP (DENSE_RANK LAST ORDER BY v.period_name) OVER (PARTITION BY v.concatenated_segments)
ytd
FROM cux_gl_summary_balances_v v
WHERE v.template_name = ‘D.T.三级科目汇总.T.T.T.T.T’
AND v.period_name BETWEEN ‘2007-01′ AND ‘2007-03′
AND v.concatenated_segments IN
(’060100.T.66012101.T.T.T.T.T’
,’070100.T.66012606.T.T.T.T.T’
)
AND v.currency_code = ‘CNY’) o
GROUP BY o.concatenated_segments, o.begin_balance, o.ytd
实现的结果：
| CONCATENATED_SEGMENTS |
BEGIN_BALANCE |
SUM(O.DR) |
SUM(O.CR) |
YTD |
| 060100.T.66012101.T.T.T.T.T |
0 |
17746.57 |
0 |
17746.57 |
| 070100.T.66012606.T.T.T.T.T |
0 |
26920 |
0 |
26920 |
1,load a Taxonomy into Oracle
CREATE DIRECTORY xmldir AS '/home/testuser/joodhawk/test_xbrl_dir';
GRANT READ ON DIRECTORY XMLDIR TO PUBLIC WITH GRANT OPTION;
SELECT * FROM DBA_DIRECTORIES;2,place the Taxonomy file into UNIX -->
'/home/testuser/joodhawk/test_xbrl_dir';
(usually there are six file = 1xsd + 5xml)
3,Loading XBRL Taxonomies
request --> "XBRL - Load Taxonomy program" from the Submit Request window.
The program has three parameters:
Taxonomy Name: Enter a unique name for the taxonomy.
Taxonomy xsd File Name: Enter the file name, with or without the .xsd extension.
Taxonomy Description: Enter a description for the taxonomy.
4,check file load properly.
SELECT * FROM apps.RG_XBRL_TAXONOMIES;
5,removing XBRL Taxonomies:
request --> "XBRL - Remove Taxonomy"
Note : the Taxonomies should not be in service. or it alters:
--****************************************************************************************
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
SHRD0180: RG_XBRL_PKG.delete_taxonomy - Performing action >> Taxonomy test by
joodhawk1155 is still used in a row set...
--****************************************************************************************
Demo:
注意,这个过程是有顺序的:
也就是load文件的时候,需要有顺序,从最小的层次往上进行load.
xlink-2003-12-31.xsd
xl-2003-12-31
xbrl-linkbase-2003-12-31
最后才是这份clcid-pt-2005-07-07.xsd
同时,虽然前面3份出现了红色的error,但error是正常的,因为前面3份没有了xml的文档导致而成,
但是,数据依然是导入了.不影响我们的操作.
整个过程下来,现在可以选择rowset的xmlelement了.选择完毕保存,run报表,选择输出是xbrl,
这样就是一份完成的报告了.
Note:
1, 对于XBRL是不支持content set的这点要注意.
2, oracle的问题提醒:
XBRL - Load Taxonomy program 的load的request是有问题的.产生原因在于如下的function:
只有一行的funciton存在codeset问题. 导致读取lob的时候既不报错,同时读取的lob数据为空.
增加参数输入,进行对codeset指定为UTF8,导致正常工作.
--dbms_lob.loadfromfile(d_clob,xbfile,dbms_lob.getlength(xbfile));
v_src_offset number := 1;
v_dst_offset number := 1;
v_lang_ctx number := DBMS_LOB.default_lang_ctx;
v_warning INTEGER;
DBMS_LOB.loadclobfromfile(
dest_lob => d_clob,
src_bfile => xbfile,
amount => DBMS_LOB.getLength(xbfile),
dest_offset => v_dst_offset,
src_offset => v_src_offset,
bfile_csid => NLS_CHARSET_ID('UTF8'),
lang_context => v_lang_ctx,
warning => v_warning);
3,同时切记load的Taxonomy文档需要与db的codeset一致,不然也会出现问题.现在我采用的是UTF8编码.
4,其实unix上有个工具专门用来处理这个字符转换的.我发现了试用完毕不管事,我也不清楚为什么,下的命令如下
iconv -f UTF-8 -t UTF-16 clcid-pt-2005-07-07.xsd
故事缘由：
年终结转有个用户把 资产类的设置成 损益类的。
结转的时候，导致期初数错误。
我在网络上看到有其他的方法，不够都蛮复杂的。
http://www.itpub.net/thread-935866-1-1.html
下面是我的一次解决方法。
再次告诉大家一次，结转前一定要看清楚科目的类型，不然处理起来很复杂。
－－－－－－－－－－－－－－－－－－－
1,首先揪出哪些期末数不等于期初数的。
建议设置一个汇总模板，T.T.%.T.T.T.T.T
其中%按照自己的科目设置情况，可能是一级，二级，三级
SELECT c.begin_balance, c.segment3
FROM zzhawk_balances_v c
WHERE c.concatenated_segments LIKE ‘T.T.%.T.T.T.T.T’
AND c.currency_code = ‘CNY’
AND c.period_name = ‘2007-Q4′
AND c.account_type IN (’A',’L')
MINUS
SELECT c.begin_balance, c.segment3
FROM zzhawk_balances_v c
WHERE c.concatenated_segments LIKE ‘T.T.%.T.T.T.T.T’
AND c.currency_code = ‘CNY’
AND c.period_name = ‘2008-01′
AND c.account_type IN (’A',’L')
2，查出具体影响的 ccid，然后update balance 的栏位。
SELECT begin_balance_dr
FROM gl_balances g
WHERE g.code_combination_id = 47229 AND g.period_name = ‘2008-01′
BEGIN_BALANCE_DR
11340
11340
UPDATE gl_balances g
SET g.begin_balance_dr = 10500
WHERE g.code_combination_id = 47229 AND g.period_name = ‘2008-01′
840 那笔未正常转入：（从 0 改为 840）
UPDATE gl_balances g SET g.begin_balance_dr = 840
WHERE code_combination_id = 46952
AND g.period_name = ‘2008-01′;
外币的期初数 （从 0 改为 840）
UPDATE gl_balances g SET g.begin_balance_dr_beq = 840
WHERE code_combination_id = 46952
AND g.period_name = ‘2008-01′
AND g.currency_code = ‘RMB’;
3，查出会受到影响的account的汇总模板：
SELECT t.*
FROM apps.gl_summary_hierarchies c, gl_summary_templates t
WHERE ‘1221040100′ BETWEEN c.child_flex_value_low AND c.child_flex_value_high
AND t.template_id = c.template_id
4，从界面上查出汇总模板，删除，重新建立
到此为止，问题解决。
其中重点是第2步，要注意分析具体是update哪个account，并且注意是CR还是DR。
注意，重点栏位为：
begin_balance_dr
begin_balance_dr_beq
period_net_dr_beq
这个问题是昨天在生产的机器上遇见的，系统宕了半个多小时。
环境：
Db 9.2.0.4.0(DB_A)调用 9.2.0.6.0 (DB_A)的package A
Os： IBM/AIX RISC System/6000: Version 5.3
操作：
DB_A 的package正在调用（通过DB Link）DB_B 的package，此时package 正在编译更新。
现象：
通过JDBC调用
Could not roll back JDBC transaction; nested exception is java.sql.SQLException:
但是如果直接下
Select DB_B.package.fun(1) from dual 是可以调用成功的。
事故发生的主要error信息：
ORA-06508: PL/SQL: could not find program unit being calle
16:38:12 Execution failed: ORA-00600: internal error code, arguments: [17069], [0x700000234E82438], [], [], [], [], [], []
详细状态跟这个描述的一模一样。
http://forums.oracle.com/forums/thread.jspa?threadID=29415
最后的解决方法：
删除db link，重建 并且充命名 db link，结果是调用成功。
详细的解释，从网络上copy，这样是比较有道理的：
found on metalink
——————————————————–
The problem happens every time code in package A, running as part of a scheduled job, calls dbms_job.submit() with a one-time (next=null) job, also located in package A.
I found in some article or post on MetaLink that by converting the second parameter from the ORA-600 to hex and searching for it in the trace file near “INVL”, you can find the text name of the object that is reported invalid and causing the pinning miss.
This turned out to be package A - so in Oracle 8, the job queue fails when attempting to execute a job that runs a procedure in a package already running in another job process, as far as I can tell.
———————————————————-
ORA-600:[17069] is an error signalled due to a failure to pin a library cache object–possibly the q$space.segwatch procedure. Perhaps this procedure is/was invalid at the time or was being recompiled during the execution of this job.
———————————————————-
The ORA-600 17069 indicates there was a failure to pin a library cache object after 50 attempts. You can take the FFD8038 (hex conversion of [268271672]) to find which library object has problems. Look for FFD8038 in the trace file. If the Library object identified has a status = INVL, it is likely that the object (or dependent objects) have been dropped or recompiled during the execution of this process. This error will normally ocurr within long-running processes.
内容：简单介绍锁，并且demo一个解锁过程
平台： solaris 9 + 10g
锁总共分如下5种类型，级别是越来越高，越高级的锁的排他性越强。
* ROW SHARE
* ROW EXCLUSIVE
* SHARE UPDATE
* SHARE
* SHARE ROW EXCLUSIVE
* EXCLUSIVE
手动加锁语句：
LOCK TABLE [schema.]{table | view}[@dblink]
[, [schema.]{table | view}[@dblink] ]…
IN lockmode MODE
[NOWAIT]
例子：
LOCK TABLE zzhawk_employees IN ROW SHARE MODE
产生锁表的动作：
|
类型/动作
|
DML
|
DDL
|
LOCK_MODE
|
备注
|
|
ROW SHARE
|
Insert,update,delete
|
|
2
|
|
|
ROW EXCLUSIVE
|
Select for update
|
|
3
|
|
|
SHARE UPDATE
|
|
|
2
|
|
|
SHARE
|
|
|
4
|
|
|
SHARE ROW EXCLUSIVE
|
|
|
5
|
|
|
EXCLUSIVE
|
|
Drop table,alter table
|
6
|
|
几个关键View：
|
v$lock
|
列出系统所有的锁
|
|
|
v$locked_object
|
锁住的objects
|
|
|
dba_waiters
|
进入等待锁的session
|
|
|
DBA_BLOCKERS
|
正在使用锁的session
|
|
发现锁：
SELECT l.*, o.object_name, o.object_type
FROM v$locked_object l, all_objects o
WHERE l.object_id = o.object_id;
|
XIDUSN
|
XIDSLOT
|
XIDSQN
|
OBJECT_ID
|
SESSION_ID
|
ORACLE_USERNAME
|
OS_USER_NAME
|
PROCESS
|
LOCKED_MODE
|
OBJECT_NAME
|
OBJECT_TYPE
|
|
0
|
0
|
0
|
52697
|
149
|
APPS
|
lingn
|
3432:3264
|
0
|
ZZHAWK_EMPLOYEES
|
TABLE
|
|
0
|
0
|
0
|
52697
|
151
|
APPS
|
lingn
|
3716:3412
|
6
|
ZZHAWK_EMPLOYEES
|
TABLE
|
查询锁情况：
可以发现是149 等 151 释放
SELECT * FROM dba_waiters t WHERE t.lock_id1 = 52697;
|
WAITING_SESSION
|
HOLDING_SESSION
|
LOCK_TYPE
|
MODE_HELD
|
MODE_REQUESTED
|
LOCK_ID1
|
LOCK_ID2
|
|
149
|
151
|
DML
|
Exclusive
|
Row-S (SS)
|
52697
|
0
|
由此解锁：
杀死session_id = 151
SELECT SID, serial#, username
FROM v$session
WHERE TYPE = ‘USER’ AND SID = 151
|
SID
|
SERIAL#
|
USERNAME
|
|
151
|
83
|
APPS
|
执行杀死动作：
*** SCRIPT START : Session:APPS@DEV4(1) 24-¶þÔÂ-2008 16:32:37 ***
Processing …
ALTER SYSTEM KILL SESSION ‘151,83′
*** SCRIPT END : Session:APPS@DEV4(1) 24-¶þÔÂ-2008 16:32:37 ***
重新查询，可以发现149已经获取锁了。
SELECT l.*, o.object_name, o.object_type
FROM v$locked_object l, all_objects o
WHERE l.object_id = o.object_id;
|
XIDUSN
|
XIDSLOT
|
XIDSQN
|
OBJECT_ID
|
SESSION_ID
|
ORACLE_USERNAME
|
OS_USER_NAME
|
PROCESS
|
LOCKED_MODE
|
OBJECT_NAME
|
OBJECT_TYPE
|
|
0
|
0
|
0
|
52697
|
149
|
APPS
|
lingn
|
3432:3264
|
2
|
ZZHAWK_EMPLOYEES
|
TABLE
|
至此，锁已经解开。
如果还想具体知道是锁住了哪行的话，还可以如下查询：
SELECT s.sid,s.row_wait_block#,s.row_wait_row#