sql的grant语句用法_百度知道
sql的grant语句用法
小弟刚学sql希望回答者能告诉我具体用法和例题谢谢
我是菜鸟1楼的回答没看懂，希望能又更直观的答案
John，若要授予访问 Windows NT 内置本地组的权限，最严格的权限 (DENY) 优先起作用。语句 dbcreator processadmin securityadmin serveradmin bulkadmin ALTER DATABASE X
CREATE DATABASE X
BULK INSERT
RECONFIGURE
(1) 有关更多信息，请参见 DBCC 语句，该列上需要有 UPDATE 权限。如果用户激活应用程序角色。如果列列表未与 SELECT 和 UPDATE 权限一起提供、processadmin。有一些 Transact-SQL 语句不能被授予权限。两个特殊的安全帐户可用于 GRANT 语句，对象权限需要进一步授予不是组或角色的成员的用户。但是。(2) 有关更多信息, DELETEON authorsTO Mary，自动有权限执行此语句.；不可将权限授予其它数据库中的用户.n ] ) ] ON { table | view }
| ON { table | view } [ ( column [ ，此用户也没有此对象的访问权限、John 和 Tom. 在权限层次中授予对象权限下例显示权限的优先顺序。授予权限删除所授予级别（用户.。授予 public 角色的权限可应用于数据库中的所有用户，请指定在其上定义该组的域名或计算机名,，此角色有默示执行特殊语句的权限。对于语句权限。为使用 UPDATE 语句更新某个列。如果用户需要另一个数据库中的对象的权限。AS {group | role}指当前数据库中有执行 GRANT 语句权力的安全帐户的可选名。permission是当前授予的对象权限.n ]对象权限；执行这些语句要求有固定角色中的成员资格。security_account 必须在当前数据库中存在。说明
db_accessadmin 固定数据库角色的成员没有执行任何 Transact-SQL 语句的权限，指定的 security_account 是权限能影响到的唯一帐户、db_owner 和 db_ddladmin 角色的成员默认有CREATE TABLE 权限.。但是：GRANT
{ ALL [ PRIVILEGES ] | permission [ ，但它并不阻止用户访问该对象。security_account是权限将应用的安全帐户，将特定的权限授予用户 Mary，或对数据库执行管理活动的语句，对此用户通过该应用程序角色访问的任何对象。例如，他要将表 Plan_Data 上的 SELECT 权限授予用户 Jack。在标量值函数上授予的对象权限可以包括 EXECUTE 和 REFERENCES,;GRANT SELECT ON Plan_Data TO Accounting WITH GRANT OPTION&#47，表示此项可在逗号分隔的列表中重复。db_owner 或 db_securityadmin 角色的成员可授予其数据库中任何语句或对象上的任何权限。WITH GRANT OPTION 子句仅对对象权限有效，则在应用程序角色激活期间。语句列表可以包括.。因为只有用户（而不是组或角色）可执行 GRANT 语句、表值函数或视图上授予对象权限时。对象所有者可为他们所拥有的对象授予权限，请在该数据库中创建用户帐户. 用 AS 选项授予权限用户 Jean 拥有表 Plan_Data。Jean 将表 Plan_Data 的 SELECT 权限授予 Accounting 角色（指定 WITH GRANT OPTION 子句）。如果此用户不是运行此存储过程所需要的适当的固定服务器或数据库角色的成员, [Corporate&#92, CREATE TABLETO Mary，则此存储过程不会继续执行.、视图或表值函数中的所有列。但是在执行系统存储过程后，尽管一个用户可能被授予了对当前数据库中的指定对象的访问权限，但是如果此用户使用对此对象无访问权限的应用程序角色，GRANT 的作用为空。权限GRANT 权限依赖于所授予的语句权限和权限中涉及的对象。说明
diskadmin 和 setupadmin 固定服务器角色的成员没有权限执行任何 Transact-SQL 语句，而使 Jill 能够授予表的权限：SELECTGRANT在安全系统中创建项目。不需要权限即可执行的 Transact-SQL 语句有（已自动授予 public）。 说明
系统存储过程是例外，该列上需要有 SELECT 权限。在存储过程上授予的对象权限只可以包括 EXECUTE，或者授权用户帐户访问该数据库以及当前数据库，给 public 角色授予 SELECT 权限、DELETE，只有 sysadmin 角色成员可以使用 ALL。TO指定安全帐户列表。注释不允许有跨数据库权限; SQL Server&#8482。若权限被授予 SQL Server 角色或 Windows NT 组，他们只能执行特定的系统存储过程。每条需要权限的语句都有一个特定的角色集.n ] } TO security_account [ ;* User Jill *&#47。user_defined_function是当前数据库中授予权限的用户定义函数名。sp_helprotect 系统存储过程报告在数据库对象或用户上的权限。为在 SELECT 语句中访问某个列. 给 SQL Server 角色授予权限下面的示例将 CREATE TABLE 权限授予 Accounting 角色的所有成员，sysadmin 和 db_owner 角色成员和数据库对象所有者都可以使用 ALL; 用户。statement是被授予权限的语句,。首先。(2) 仅适用于 CREATE DATABASE 语句，用户必须添加为 serveradmin 角色中的成员;BobJ 授予多个语句权限。安全帐户可以是;BobJ]B, UPDATE、REFENENCES 或 UPDATE。为创建引用某个表的 FOREIGN KEY 约束,，那么该权限应用于表，因为 EXECUTE 权限已经授予 public 角色。REVOKE 语句可用于删除已授予的权限、组或角色）上的已拒绝权限或已废除权限。dbcreator。语法语句权限。需要权限的语句是那些在数据库中增加对象，请参见 DBCC 语句，然后依次输入反斜线和组名。sysadmin 和 db_owner 角色以及表的所有者默认有对表执行 SELECT 语句的权限。GRANT CREATE DATABASE。Jill 必须用 AS 子句来获得 Accounting 角色的授予权限。SQL Server 角色:BEGIN TRANSACTION COMMIT TRANSACTION PRINT RAISERROR ROLLBACK TRANSACTION SAVE TRANSACTION SET
有关执行系统存储过程所需权限的更多信息。示例A。column是当前数据库中授予权限的列名。&#47。table是当前数据库中授予权限的表名。Microsoft Windows NT&reg。为使用引用某个对象的 WITH SCHEMABINDING 子句创建 FUNCTION 或 VIEW，请指定 BUILTIN 而不是域名或计算机名： CREATE DATABASECREATE DEFAULTCREATE FUNCTIONCREATE PROCEDURECREATE RULECREATE TABLECREATE VIEWBACKUP DATABASEBACKUP LOG n一个占位符。 当权限被授予一个 SQL Server 用户或 Windows NT 用户帐户，只可执行特定的系统存储过程.n ] }
[ ( column [ 。stored_procedure是当前数据库中授予权限的存储过程名。若组或角色和它们的成员之间存在权限冲突。于是这些用户就有了对 authors 表的所有权限，将检查用户的角色成员资格。然后,。下面的固定数据库角色的成员有权限执行指定的 Transact-SQL 语句。当在表。列列表可以与 SELECT 和 UPDATE 权限一起提供。view是当前数据库中被授予权限的视图名.，使当前数据库中的用户得以处理当前数据库中的数据或执行特定的 Transact-SQL 语句。sysadmin 角色中的成员可在任何数据库中授予任何权限。WITH GRANT OPTION表示给予了 security_account 将指定的对象权限授予其它安全帐户的能力、INSERT。extended_procedure是当前数据库中授予权限的扩展存储过程名，sysadmin 固定服务器角色的成员有权限执行所有的 Transact-SQL 语句，该表上需要有 REFERENCES 权限。GRANT CREATE TABLE TO AccountingD，该对象上需要有 REFERENCES 权限。但是。授予 guest 用户的权限可为所有在数据库中没有用户帐户的用户使用： Microsoft&reg，除非已为该用户在当前数据库中创建或给予了访问权限，Jack 不是 Accounting 的成员，权限可影响到当前数据库中该组或该角色成员的所有用户。Windows NT 组, John.。因为对表 Plan_Data 用 GRANT 语句授予其他用户 SELECT 权限的权限是授予 Accounting 角色而不是显式地授予 Jill.n ] [ WITH GRANT OPTION ] [ AS { group | role } ]参数ALL表示授予所有可用的权限。因此.n ] ) ]
| ON { stored_procedure | extended_procedure }
| ON { user_defined_function }
} TO security_account [ 。例如，允许任何人去执行.，请参见相应的系统存储过程.。用户 Jill 是 Accounting 的成员;* User Jean *&#47, TomGOC. 授予语句权限下面的示例给用户 Mary 和 John 以及 Windows NT 组 Corporate&#92。USE pubsGOGRANT SELECTON authorsTO publicGO GRANT INSERT，sysadmin。在另一级别（诸如包含此用户的组或角色）上被拒绝的同一权限优先起作用；只能将当前数据库中的对象和语句的权限授予当前数据库中的用户，组或角色的特定成员授予组或角色权力之下的对象的权限、securityadmin 和 serveradmin 固定服务器角色的成员仅有权执行以下 Transact-SQL 语句,; 用户..，不能因为已授予 Accounting 角色中成员该权限，虽然在另一级别上所废除的同一权限仍然适用，权限列表可以包括这些权限中的一个或多个、角色或组，DENY 语句可用于防止用户通过 GRANT 语句获得权限给他们的用户帐户。当对象上的权限被授予一个组或角色时使用 AS.。PRIVILEGES是可以包含在符合 SQL-92 标准的语句中的可选关键字，若要执行 SHUTDOWN 语句。对于对象权限：GRANT { ALL | statement [ 。当授予某个 Windows NT 本地组或全局组权限时。语句 db_owner db_datareader db_datawriter db_ddladmin db_backupoperator db_securityadmin ALTER DATABASE X
ALTER FUNCTION X
ALTER PROCEDURE X
ALTER TABLE X (1)
ALTER TRIGGER X
ALTER VIEW X (1)
CHECKPOINT X
CREATE DEFAULT X
CREATE FUNCTION X
CREATE INDEX X (1)
CREATE PROCEDURE X
CREATE RULE X
CREATE TABLE X
CREATE TRIGGER X (1)
CREATE VIEW X
DELETE X (1)
X DENY on object X
DROP X (1)
EXECUTE X (1)
X GRANT on object X (1)
INSERT X (1)
READTEXT X (1)
REFERENCES X (1)
X REVOKE on object X (1)
SELECT X (1)
TRUNCATE TABLE X (1)
UPDATE X (1)
UPDATE STATISTICS X (1)
UPDATETEXT X (1)
WRITETEXT X (1)
(1) 权限也适用于对象所有者
其他类似问题
为您推荐：
其他2条回答
A. 授予语句权限
下面的示例给用户 Mary 和 John 以及 Windows NT 组 Corporate\BobJ 授予多个语句权限。
GRANT CREATE DATABASE, CREATE TABLE
TO Mary, John, [Corporate\BobJ]
B. 在权限层次中授予对象权限
下例显示权限的优先顺序。首先，给 public 角色授予 SELECT 权限。然后，将特定的权限授予用户 Mary、John 和 Tom。于是这些用户就有了对 authors 表的所有权限。
GRANT SELECT
ON authors
GRANT INSERT, UPDATE, DELETE
ON authors
TO Mary, John, Tom
C. 给 SQL Server 角色授予权限
下面的示例将 CREATE TABLE 权限授予 Accounting 角色的所有成员。
GRANT CREATE TABLE TO Accounting
D. 用 AS 选项授予权限
用户 Jean 拥有表 ...
grant的相关知识
等待您来回答
下载知道APP
随时随地咨询
出门在外也不愁oracle - Grant Select, Insert, Update to a Tablespace - Stack Overflow
to customize your list.
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.
J it only takes a minute:
Join the Stack Overflow community to:
Ask programming questions
Answer and help your peers
Get recognized for your expertise
I've got a lot of tables in a tablespace, nearly 100. I have to grant Select, Insert, Update privileges on all those tables to a user. Is it possible? When I write:
GRANT USE OF TABLESPACE MYTABLESPACE TO USERNAME
I get oracle error "invalid or missing privilege"
5,3811152114
Use the data dictionary view dba_tables (resp. all_tables, if you cannot access dba_tables):
l_SQL varchar2(4000);
for cur in (
select * from dba_tables where tablespace_name = 'mytablespace')
l_sql := 'grant select, insert, update on ' || cur.owner || '.' || cur.table_name || ' to myuser';
--dbms_output.put_line(l_SQL || ';');
execute immediate l_SQL;
If you just want to generate a script, comment out the execute immediate and un-comment the dbms_output.
18.3k63363
USE OF TABLESPACE is not a documented option, where did you find that?
You can do this to allow a user to create objects in a tablespace:
alter user username quota [amount]
To grant select, insert, update and delete on objects you have to run a separate grant command for each table:
grant select, insert, update, delete on mytable1
84.6k11134185
Your Answer
Sign up or
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Post as a guest
By posting your answer, you agree to the
Not the answer you're looking for?
Browse other questions tagged
The week's top questions and answers
Important community announcements
Questions that need answers
By subscribing, you agree to the
Stack Overflow works best with JavaScript enabledUpgrading from E-Business Suite 11i to R12 is a major step and some errors are to be expected during applying the English American patch driver, u6678700.drv. Some of the errors, seen during several upgrades on different customer sites, are listed below.
Seen errors during applying of upgrade patch driver, u6678700.drv
Unable to allocate memory in procedure aiumab().&
Error message:
Unable to allocate memory in procedure aiumab().
# Export next environment variable and start adpatch again.
export LDR_CNTRL=MAXDATA=0x
Reference:
Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.1.1) for IBM AIX on Power Systems (64-bit) (Doc ID )See under “KNOWN ISSUES =& Applying large Applications Patches”.
amviccn.sql fails with: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine&
Error message:
FAILED: file amviccn.sql on worker
sqlplus -s APPS/***** @/&middle tier&/apps/apps_st/appl/amv/12.0.0/patch/115/sql/amviccn.sql &un_amv &pw_amv
Connected.
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-00100: internal error, arguments : [51021],[drwaf.c],[1605],[],[]
ORA-06512: at “CTXSYS.DRUE”, line 160
ORA-06512: at “CTXSYS.TEXTINDEXMETHODS”, line 366
ORA-06512: at “SYS.DBMS_SQL”, line 1053
ORA-06512: at line 49
1. Abort the AutoPatch session of applying the u6678700.drv driver. The restore files are in place. Later on you will restart the AutoPatch session and the executable would continue where it left off.
2. Shutdown the database and database listener.
3. Source the RDBMS ORACLE_HOME Environment.
4. Execute the script $ORACLE_HOME/appsutil/install/adlnkoh.sh (or $ORACLE_HOME/appsutil/clone/adlnkoh.sh) to relink the RDBMS executables. Reference is : “How to relink an Applications Installation of Release 11i and Release 12”.
5. Confirm that the relink completed without any errors.
6. Startup the database and database listener.
7. From the applications terminal window, rerun adpatch. AutoPatch will identify that another session was running and will prompt you to continue where it left off. You should proceed as prompted.
8. Launch adctrl and restart the failed workers.
Reference:
Several Scripts Failing With A ORA-29855 Executing ODCIINDEXCREATE Routine During Upgrade To 12.1.1 (u6678700.drv) (Doc ID )
jtfDiagReportMig.sql fails with: ORA-20101: Exception while trying to migrate data from jtf_diagnostic_log to jtf_diagnostic_download_rpt&
Error message:
FAILED: file jtfDiagReportMig.sql on worker
sqlplus -s APPS/***** @/&middle tier&/apps/apps_st/appl/fnd/12.0.0/patch/115/sql/jtfDiagReportMig.sql &un_jtf &pw_jtf &un_apps &pw_apps
Connected.
ERROR at line 1:
ORA-20101: Exception while trying to migrate data from jtf_diagnostic_log to
jtf_diagnostic_download_rpt:ORA-04063: package body “APPS.FND_GLOBAL” has errors
ORA-06512: at line 21
1. Open a new shell and set the Applications environment using APPS&CONTEXT_NAME&.env
2. adjava -ms128m -mx256m -nojit oracle.apps.fnd.odf2.FndXdfCmp apps &PASS& apps &PASS& THIN “&machine.domain&:&PORT&:&SID&” view $FND_TOP/patch/115/xdf/fnd_user_resp_groups.xdf $FND_TOP/patch/115/xdf/xsl
3. adjava -ms128m -mx256m -nojit oracle.apps.fnd.odf2.FndXdfCmp apps &PASS& apps &PASS& THIN “&machine.domain&:&PORT&:&SID&” view $FND_TOP/patch/115/xdf/wf_user_role_assignments_v.xdf $FND_TOP/patch/115/xdf/xsl
4. adjava -ms128m -mx256m -nojit oracle.apps.fnd.odf2.FndXdfCmp apps &PASS& apps &PASS& THIN “&machine.domain&:&PORT&:&SID&” view $FND_TOP/patch/115/xdf/wf_user_roles.xdf $FND_TOP/patch/115/xdf/xsl
5. sqlplus apps/&PASS&
SQL&alter view fnd_user_resp_
6. Launch adctrl and restart the failed worker.
Reference:
12.1.1 Upgrade fails On “Jtfdiagreportmig.Sql” [ID ]
p002ncpls.sql fails with: ORA-04068: existing state of packages has been discarded&
Error message:
FAILED: file p002ncpls.sql on worker
sqlplus -s APPS/***** @/&middle tier&/apps/apps_st/appl/admin/&ENV&/out/p002ncpls.sql &un_apps CTXSYS &un_csSession altered.Package body created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure “PUBLIC.PLITBLM”
ORA-06508: PL/SQL: could not find program unit being called: “PUBLIC.PLITBLM”
ORA-06512: at “SYSTEM.AD_DDL”, line 165
ORA-06512: at line 2
1. Run $ORACLE_HOME/rdbms/admin/utlirp.sql
2. Run the following query to check for objects with a timestamp mismatch:
select do.obj# d_obj,do.name d_name, po.obj# p_obj,po.name p_name, to_char(p_timestamp,’DD-MM-YYYY HH24:MI:SS’) p_timestamp, to_char(po.stime ,’DD-MM-YYYY HH24:MI:SS’) stime, decode(sign(po.stime-p_timestamp),0,’SAME’,’*DIFFER*’) X,do.type#,po.type#
from sys.obj$ do, sys.dependency$ d, sys.obj$ po
where P_OBJ#=po.obj#(+)
and D_OBJ#=do.obj#
and do.status=1 /*dependent is valid*/
and po.status=1 /*parent is valid*/
and po.stime!=p_timestamp /*parent timestamp not match*/
order by 2,1;
3. Compile any objects that are returned by the above query
SQL& alter package SYS.PLITBLM
SQL& alter package SYS.DBMS_RESUMABLE
SQL& alter package SYS.DBMS_REPCAT_AUTH
SQL& alter package SYSTEM.DBMS_REPCAT_AUTH
Check if the objects are valid now.
4. Launch adctrl and restart the failed worker
Reference:
DBMS_METADATA Fails With ORA-04068 And ORA-04065 [ID ]
p001ncpls.sql fails with: ORA-00955: name is already used by an existing object&
Error message:
FAILED: file p001ncpls.sql on worker 16.
sqlplus -s APPS/***** @/&middle tier&/apps/apps_st/appl/admin/&ENV&/out/p001ncpls.sql
Session altered.
ERROR at line 1:
ORA-00955: name is already used by an existing object
ORA-06512: at line 21
Time when worker failed: Sat Jun 16 :21
1. Drop tables below:
drop table IGI.IGI_UPG_GL_BUDGET_ASSIGNMENT;
drop synonym APPS.IGI_UPG_GL_BUDGET_ASSIGNMENT;
select * from dba_objects where object_name=’IGI_UPG_GL_BUDGET_ASSIGNMENT’;
drop table IGI.IGI_UPG_GL_BUDORG_BC_OPTIONS;
drop synonym APPS.IGI_UPG_GL_BUDORG_BC_OPTIONS;
select * from dba_objects where object_name=’IGI_UPG_GL_BUDORG_BC_OPTIONS’;
drop table IGI.IGI_EFC_BUDGET_ASSIGNMENT_BCK;
drop synonym APPS.IGI_EFC_BUDGET_ASSIGNMENT_BCK;
select * from dba_objects where object_name=’IGI_EFC_BUDGET_ASSIGNMENT_BCK’;
drop table IGI.IGI_BUDORG_BC_OPTIONS_BCK;
drop synonym APPS.IGI_BUDORG_BC_OPTIONS_BCK;
select * from dba_objects where object_name=’IGI_BUDORG_BC_OPTIONS_BCK’;
drop table IGI.IGI_EFC_UPG_BACKUP_INFO;
drop synonym APPS.IGI_EFC_UPG_BACKUP_INFO;
select * from dba_objects where object_name=’IGI_EFC_UPG_BACKUP_INFO’;
drop table IGI.IGI_GL_BUDGET_ASSIGN_BCK;
drop synonym APPS.IGI_GL_BUDGET_ASSIGN_BCK;
select * from dba_objects where object_name=’IGI_GL_BUDGET_ASSIGN_BCK’;
2. Restart failed workers in adctrl.
Uploading file ibecitem.ldt fails with: ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE&
Error message:
FAILED: file ibecitem.ldt on worker
Uploading from the data file /&middletier&/apps/apps_st/appl/ibe/12.0.0/patch/115/import/US/ibecitem.ldt
Altering database NLS_LANGUAGE environment to AMERICAN
Dumping from LCT/LDT files (/&middle tier&/apps/apps_st/appl/ibc/12.0.0/patch/115/import/ibccitem.lct, /&middle tier&/apps/apps_st/appl/ibe/12.0.0/patch/115/import/US/ibecitem.ldt) to staging tables
Dumping LCT file /&middle tier&/apps/apps_st/appl/ibc/12.0.0/patch/115/import/ibccitem.lct into FND_SEED_STAGE_CONFIG
Dumping LDT file /&middle tier&/apps/apps_st/appl/ibe/12.0.0/patch/115/import/US/ibecitem.ldt into FND_SEED_STAGE_ENTITY
Dumped the batch (IBC_CONTENT_ITEMS 51 , IBC_CITEM_VERSIONS 52 ) into FND_SEED_STAGE_ENTITY
Uploading from staging tables
Error loading seed data for IBC_CITEM_VERSIONS:
CONTENT_ITEM_ID = 51, CITEM_VERSION_ID = 51,
ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE
Error loading seed data for IBC_CITEM_VERSIONS:
CONTENT_ITEM_ID = 52, CITEM_VERSION_ID = 52,
ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE
Concurrent request completed
1. Check status of Domain index:
SQL& select index_name, owner, status, DOMIDX_status, domidx_opstatus from dba_indexes where index_type=’DOMAIN’ and domidx_opstatus=’FAILED’;
INDEX_NAME
STATUS DOMIDX_STATUS DOMIDX
————————–
———–
——–
——————–
——
IGP_US_PORTFOLIOS_CTX1
IGP_US_PORTFOLIOS_CTX2
IBC_ATTRIBUTE_BUNDLES_CTX IBC
IGP_US_PORTFOLIOS_CTX3
2. Fix Intermedia Tables:
SQL& exec ctx_ddl.sync_index(‘IBC.IBC_ATTRIBUTE_BUNDLES_CTX’);
3. Restart failed workers in adctrl.
EGOSILDU.sql fails with: ORA-02149: Specified partition does not exist&
Error message:
FAILED: file EGOSILDU.sql on worker
sqlplus -s APPS/***** @/&middle tier&/apps/apps_st/appl/ego/12.0.0/patch/115/sql/EGOSILDU.sql &un_ego
ERROR at line 1:
ORA-02149: Specified partition does not exist
ORA-06512: at line 408
1. Check if the EGO product is installed/used.
You can use “$AD_TOP/sql/adutconf.sql” to find out if you are using the product.
2. If you have not installed EGO product or if you are not using “Advanced Product Catalog” functionality, you can skip the failed job. Use adctrl hidden option #8, to skip the failed job.
3. If you are using this product, Please raise an SR in order to get the detailed procedure to fix the issue listed in
Reference:
Patch 7303030 Fails on Egosildu.sql : ORA-02149 Specified Partition Does Not Exist [ID ]
zpbremcwm.sql fails with: ORA-06550: line 235, column 6: PLS-00904: insufficient privilege to access object OLAPSYS.CWM2_OLAP_CUBE&
Error message:
FAILED: file zpbremcwm.sql on worker
sqlplus -s APPS/***** @/&middle tier&/apps/apps_st/appl/zpb/12.0.0/patch/115/sql/zpbremcwm.sqlcwm2_olap_cube.drop_cube(G_SCHEMA, v_cube.name);
ERROR at line 235:
ORA-06550: line 235, column 6:
PLS-00904: insufficient privilege to access object OLAPSYS.CWM2_OLAP_CUBE
ORA-06550: line 235, column 6:
PL/SQL: Statement ignored
ORA-06550: line 239, column 6:
PLS-00904: insufficient privilege to access object OLAPSYS.CWM2_OLAP_DIMENSION
ORA-06550: line 239, column 6:
PL/SQL: Statement ignored
ORA-06550: line 243, column 6:
PLS-00904: insufficient privilege to access object OLAPSYS.CWM2_OLAP_CATALOG
ORA-06550: line 243, column 6:
PL/SQL: Statement ignored
ORA-06550: line 247, column 4:
PLS-00904: insufficient privilege to access object
OLAPSYS.CWM2_OLAP_METADATA_REFRESH
ORA-06550: line 247, column 4:
PL/SQL: Statement ignored
1. Grant privileges:
SQL& grant execute on OLAPSYS.CWM2_OLAP_METADATA_REFRESH
SQL& grant execute on OLAPSYS.CWM2_OLAP_CATALOG
SQL& grant execute on OLAPSYS.CWM2_OLAP_DIMENSION
SQL& grant execute on OLAPSYS.CWM2_OLAP_CUBE
2. Use adctrl option #2 to restart the failed worker.
Reference:
ZPBREMCWM.SQL Returns Error: PLS-00904: Insufficient Privilege To Access Object (Doc ID )
csmidxdel.sql fails with: ORA-06550: line 15, column 2: PLS-00201: identifier ‘CSM_UTIL_PKG.LOG’ must be declared&
Error message:
FAILED: file csmidxdel.sql on worker
sqlplus -s APPS/***** @/opt/ebsr12/apps/apps_st/appl/csm/12.0.0/patch/115/sql/csmidxdel.sql
CSM_UTIL_PKG.LOG(message =&’Dropping the index CSM_NEW_MESSAGES_ACC_U2′
ERROR at line 15:
ORA-06550: line 15, column 2:
PLS-00201: identifier ‘CSM_UTIL_PKG.LOG’ must be declared
PL/SQL: Statement ignored
Package CSM_UTIL_PKG doesn’t exist.
1. Check if the CSM product is installed/used.
You can use “$AD_TOP/sql/adutconf.sql” to find out if you are using the product.
2. If you have not installed CSM product, you can skip the failed job. Use adctrl hidden option #8, to skip the failed job.
3. If you are using this product, Please raise an SR in order to get the detailed procedure to fix the issue.
pechktsk.sql fails with: ORA-00979: not a GROUP BY expression&
Error message:
FAILED: file pechktsk.sql on worker
sqlplus -s APPS/***** @/opt/ebsr12/apps/apps_st/appl/per/12.0.0/patch/115/sql/pechktsk.sql
ERROR at line 1:
ORA-00979: not a GROUP BY expression
ORA-06512: at line 38
1. Check current version of pechktsk.sql:
strings -a $PER_TOP/patch/115/sql/pechktsk.sql |grep ‘$Header’
Download patch 8855023 if the version is lower than 120.0..
2. Backup current version of pechktsk.sql:
mv $PER_TOP/patch/115/sql/pechktsk.sql $PER_TOP/patch/115/sql/pechktsk.sql.old
3. Copy new version of pechktsk.sql from patch 8855023 to $PER_TOP/patch/115/sql:
cp &#3/per/patch/115/sql/pechktsk.sql $PER_TOP/patch/115/sql/pechktsk.sql
4. Check version of new pechktsk.sql:
strings -a $PER_TOP/patch/115/sql/pechktsk.sql |grep ‘$Header’
rem $Header: pechktsk.sql 120.0.
06:31:36 sidsaxen ship $
5. Launch adctrl and restart the failed worker
Reference:
Applying Patch 6678700 or Patch 3500000 Pechktsk.Sql Errors with: ORA-00979: Not A Group By Expression (Doc ID )
armquet.sql fails with: ORA-20000: Add subscriber Error ( AR_REV_REC_Q) : -4063 – ORA-04063: AR.AR_REV_REC_Q has errors&
Error message:
FAILED: file armquet.sql
sqlplus -s APPS/***** @/opt/ebsr12/apps/apps_st/appl/ar/12.0.0/patch/115/sql/armquet.sql &un_ar &pw_ar &un_ar
Connected.
PL/SQL procedure successfully completed.
ERROR at line 1:
ORA-20000: Add subscriber Error ( AR_REV_REC_Q) : -4063 – ORA-04063: AR.AR_REV_REC_Q has errors
ORA-06512: at line 18
1. Check if the status of the queue is invalid:
sqlplus apps
SQL& select owner, object_name, object_type, status from dba_objects where object_name=’AR_REV_REC_Q’;
OBJECT_NAME
OBJECT_TYPE
——— ——————-
—————-
———————
AR_REV_REC_Q
2. Drop the queue and the queue table:
SQL& exec dbms_aqadm.drop_queue(queue_name =&’AR.AR_REV_REC_Q’,auto_commit=&TRUE);
PL/SQL procedure successfully completed.
SQL& exec dbms_aqadm.drop_queue_table(queue_table=&’AR.AR_REV_REC_QT’,force=&TRUE);
PL/SQL procedure successfully completed.
3. Create the queue and queue table again:
SQL& @$AR_TOP/patch/115/sql/arcquet.sql apps &apps pwd& AR APPS_TS_QUEUES
4. Grant privileges and create synonym:
# Reference: $AR_TOP/patch/115/sql/arcques.sql:
sqlplus apps
SQL& execute AD_INST.DO_APPS_DDL(‘AR’, ‘GRANT ALL ON AR_REV_REC_QT TO ‘||’APPS’||’ WITH GRANT OPTION’);
SQL& create or replace synonym apps.AR_REV_REC_QT for ar.AR_REV_REC_QT;
5. Start the queue.
sqlplus apps
SQL& @$AR_TOP/patch/115/sql/arstque.sql AR
6. Verify that the queue is valid:
sqlplus apps
SQL& select owner, object_name, object_type, status from dba_objects where object_name=’AR_REV_REC_Q’;
OBJECT_NAME
OBJECT_TYPE
———
——————– —————– ———————
AR_REV_REC_Q
7. Launch adctrl and restart the failed worker
XNPQFIX.sql fails with: ORA-20001: Error While modifying queue XNP.XNP_OUT_MSG_Q, ORA-06512: at line 77&
Error message:
FAILED: file XNPQFIX.sql on worker 1.
sqlplus -s APPS/***** @/opt/ebsr12/apps/apps_st/appl/xnp/12.0.0/patch/115/sql/XNPQFIX.sql &un_xnp &pw_xnp &un_xnp
Connected.
ERROR at line 1:
ORA-20001: Error While modifying queue XNP.XNP_OUT_MSG_Q
ORA-06512: at line 77
1. Check current queue status:
sqlplus “/ as sysdba”
SQL& select owner, object_name, object_type, status from dba_objects where object_type=’QUEUE’ and object_name=’XNP_OUT_MSG_Q’;
OBJECT_NAME
OBJECT_TYPE
———-
———————— ——————-
———————
XNP_OUT_MSG_Q
2. Stop the queues:
SQL& exec dbms_aqadm.stop_queue(queue_name =& ‘XNP.XNP_OUT_MSG_Q’,wait =& FALSE);
SQL& exec dbms_aqadm.stop_queue(queue_name =& ‘XNP.XNP_OUT_MSG_EXCEPTION_Q’, wait =& FALSE);
3. Drop the queue:
SQL& exec dbms_aqadm.drop_queue(queue_name=& ‘XNP.XNP_OUT_MSG_Q’);
4. Create the queue:
SQL& exec dbms_aqadm.create_queue (queue_name
=& ‘XNP.XNP_OUT_MSG_Q’, queue_table =& ‘XNP.XNP_OUT_MSG_QTAB’, max_retries =& 5, retry_delay =& 3600, retention_time =& 0, auto_commit =& FALSE );
5. Start the queues:
SQL& exec dbms_aqadm.start_queue(queue_name =& ‘XNP.XNP_OUT_MSG_Q’);
SQL& exec dbms_aqadm.start_queue(queue_name =& ‘XNP.XNP_OUT_MSG_EXCEPTION_Q’, enqueue =& FALSE, dequeue =& TRUE);
6. Check current queue status:
SQL& select owner, object_name, object_type, status from dba_objects where object_type=’QUEUE’ and object_name=’XNP_OUT_MSG_Q’;
OBJECT_NAME
OBJECT_TYPE
———- —————————— —————–
—————-
XNP_OUT_MSG_Q
7. Launch adctrl and restart the failed worker
Reference:
SFM Event Manager Queue Service (SFM) Error ORA-00904: “TAB”.”USER_PROP”: INVALID IDENTIFIER (Doc ID ). The note is specifically written for Install Base users experiencing a specific error with the 2 queue services required for Install Base functionality but the same steps given in the solution can be used to rebuild the XNP queues.
PO_VENDORS_OBS_F1.xdf fails with: AD Worker error: The utility FndXdfCmp returned error for the above task.&
Error message:
FAILED: file PO_VENDORS_OBS_F1.xdf on worker
AD Worker error:
The utility FndXdfCmp returned error for the above task.
1. Drop index:
sqlplus apps/&apps pwd&
SQL& drop index PO.PO_VENDORS_F1;
2. Launch adctrl and restart the failed worker.
Reference:
Creation of Index PO_VENDORS_OBS_F1 Failed When Applying 12.1.1 Upgrade Patch 6678700 (Doc ID )
cstfifo.odf fails with: ORA-00979: not a GROUP BY expression&
Error Message:
FAILED: file cstfifo.odf
ORA-00979: not a GROUP BY expression
1. Download and unzip patch 9868229.
2. Backup current cstfifo.odf file.
cp $BOM_TOP/patch/115/odf/cstfifo.odf $BOM_TOP/patch/115/odf/cstfifo.odf.org
3. Copy cstfifo.odf file from patch 9868229 to the $BOM_TOP/patch/115/odf directory:
cp &#9/bom/patch/115/odf/cstfifo.odf $BOM_TOP/patch/115/odf/cstfifo.odf
4. Launch adctrl and restart the failed worker.
Reference:
This error can be avoided when applying patch 9868229 before upgrade patch 6678700.
12.1.1 Upgrade – cstfifo.odf fails with error ‘ORA-00979: not a group by expression’ (Doc ID )
ircntf.wft fails with: ITEM_TYPE ‘WFSTD’ is protected …&
Error message:
FAILED: file ircntf.wft
Access level: 20, Mode: UPGRADE
ITEM_TYPE ‘WFSTD’ is protected, no changes were saved.
ITEM_ATTRIBUTE ‘WFSTD/EVENTNAME’ is customized, no changes were saved.
ITEM_ATTRIBUTE ‘WFSTD/N’ is protected, no changes were saved.
1. Run WFLOAD with FORCE option as the applmgr user:
$FND_TOP/bin/WFLOAD apps/&apps pwd& 0 Y FORCE $IRC_TOP/patch/115/import/US/ircntf.wft
2. Use adctrl hidden option #8, to skip the failed job.
Reference:
IRCNTF.WFT Fails During R12.1.1 Upgrade (Doc ID ).
Oracle Support advice is to skip this error and complete the upgrade.
This error should not impact any iRecruitment functionality apart from Generic Notifications in iRecruitment which is introduced in 12.1. Furthermore they suggest to apply Patch 9371492: IRCNTF.WFT FAILS DURING R12.1.1 UPGRADE. This isn’t possible during deployment of patch 6678700, so I run WFLOAD with the FORCE option.
OKLIAADD.wft fails with: ITEM_TYPE ‘WFSTD’ is protected, no changes were saved…&
Error message:
FAILED: file OKLIAADD.wft on worker
Access level: 20, Mode: UPGRADE
ITEM_TYPE ‘WFSTD’ is protected, no changes were saved.
ITEM_ATTRIBUTE ‘WFSTD/N’ is protected, no changes were saved.
ITEM_ATTRIBUTE ‘WFSTD/REQUESTOR_USERNAME’ is protected, no changes were saved.
1. Run WFLOAD with FORCE option as the applmgr user:
$FND_TOP/bin/WFLOAD apps/&apps pwd& 0 Y FORCE $OKL_TOP/patch/115/import/US/OKLIAADD.wft
2. Use adctrl hidden option #8, to skip the failed job.
Reference:
IRCNTF.WFT Fails During R12.1.1 Upgrade (Doc ID ).
Oracle Support advice is to skip this error and complete the upgrade.
This error should not impact any iRecruitment functionality apart from Generic Notifications in iRecruitment which is introduced in 12.1. Furthermore they suggest to apply Patch 9371492: IRCNTF.WFT FAILS DURING R12.1.1 UPGRADE. This isn’t possible during deployment of patch 6678700, so I run WFLOAD with the FORCE option.
PAPWPARN.wft fails with: ITEM_TYPE ‘WFSTD’ is protected, no changes were saved…&
Error message:
FAILED: file PAPWPARN.wft on worker
Access level: 20, Mode: UPGRADE
ITEM_TYPE ‘WFSTD’ is protected, no changes were saved.
ITEM_ATTRIBUTE ‘WFSTD/N’ is protected, no changes were saved.
ITEM_ATTRIBUTE ‘WFSTD/REQUESTOR_USERNAME’ is protected, no changes were saved.
1. Run WFLOAD with FORCE option as the applmgr user:
$FND_TOP/bin/WFLOAD apps/&apps pwd& 0 Y FORCE $PA_TOP/patch/115/import/US/PAPWPARN.wft
2. Use adctrl hidden option #8, to skip the failed job.
Reference:
This error is described in MOS . PAPWPARN.WFT ERRORS ON UPGRADE TO 12.1.1 (Doc ID ). Oracle Support suggest to apply patch OFF:.1.1:PATCH 6678700 FAILS 1602 ORACLE WORKFLOW DEFINITION. This isn’t possible during deployment of patch 6678700, so I run WFLOAD with the FORCE option.
IGIGITRA.wft fails with: ORA-00001: unique constraint (APPLSYS.WF_MESSAGE_ATTRIBUTES_TL_U2) violated.&
Error message:
FAILED: file IGIGITRA.wft on worker
&#: Could not save.
&#: Could not save to database. MODE=UPGRADE EFFDATE=&null&
&#: Could not upload MESSAGE entity ‘ITRAPPRV/NOTIFY_PREPARER_OF_REJECTION’.
&#: Could not upload ATTRIBUTE entity ‘&null&/COMMENT’.
&#: Oracle Error: ORA-00001: unique constraint (APPLSYS.WF_MESSAGE_ATTRIBUTES_TL_U2) violated
ORA-06512: at “APPS.WF_LOAD”, line 1067
ORA-06512: at line 1.
SQL text: BEGIN WF_LOAD.UPLOAD_MESSAGE_ATTRIBUTE(:message_type, :message_name, :name, :display_name, :description, :sequence, :type, :subtype, :protect, :custom, :format, :default, :value_type, :attach, :level_error); END;
Oracle Workflow Definition Loader 2.6.4.0.
1. Update wf_message_attributes_tl:
sqlplus apps
SQL& UPDATE wf_message_attributes_tl
SET display_name =’Approver Comment’
WHERE message_type=’ITRAPPRV’
AND message_name in (‘NOTIFY_PREPARER_OF_APPROVAL’,
‘NOTIFY_PREPARER_OF_REJECTION’, ‘NOTIFY_PREPARER_NOT_SUBMITTED’)
AND name=’APPROVER_COMMENT’
AND display_name=’Comment’
AND language = ‘DK’;
2. Run WFLOAD with FORCE option as the applmgr user:
$FND_TOP/bin/WFLOAD apps/&apps pwd& 0 Y FORCE $IGI_TOP/patch/115/import/US/IGIGITRA.wft
3. Use adctrl hidden option #8, to skip the failed job.
Reference:
which is visible for Oracle Support only.
oklresp.ldt fails with: Error calling FNDLOAD function – ORA-01403: no data found&
Error message:
FAILED: file oklresp.ldt
Error calling FNDLOAD function.
Error loading seed data for FND_RESP_FUNCTIONS:
APPLICATION_SHORT_NAME = OKL, RESP_KEY = LEASE_TERMINATIONS, RULE_TYPE = M, ACTION = OKL_AM_REMARKET,
ORA-01403: no data found
Error loading seed data for FND_RESP_FUNCTIONS:
APPLICATION_SHORT_NAME = OKL, RESP_KEY = LEASE_CONTRACT_ADMINISTRATOR, RULE_TYPE = M, ACTION = OKL_AM_REMARKET,
ORA-01403: no data found
1. Check if the OKL product is installed/used.
You can use “$AD_TOP/sql/adutconf.sql” to find out if you are using the product.
2. If you have not installed OKL product, you can skip the failed job. Use adctrl hidden option #8, to skip the failed job.
3. If you are using this product, Please raise an SR in order to get the detailed procedure to fix.
MSDODPCODE.sql fails with: ORA-33292: Insufficient permissions to access analytic workspace APPS.ODPCODE&
Error message:
FAILED: file MSDODPCODE.sql on worker
sqlplus -s APPS/***** @/opt/ebsr12/apps/apps_st/appl/msd/12.0.0/patch/115/sql/MSDODPCODE.sql
ERROR at line 1:
ORA-33292: Insufficient permissions to access analytic workspace APPS.ODPCODE
using the specified access mode.
ORA-06512: at “SYS.DBMS_AW”, line 67
ORA-06512: at “SYS.DBMS_AW”, line 131
ORA-06512: at “SYS.DBMS_AW”, line 977
ORA-06512: at “SYS.DBMS_AW”, line 930
ORA-33262: Analytic workspace APPS.ODPCODE does not exist.
ORA-06512: at “SYS.DBMS_AW”, line 901
ORA-06512: at “APPS.MSD_AW_LOADER”, line 10
ORA-06512: at line 3
1. Unlock the OLAPSYS account:
sqlplus “/ as sysdba”
SQL& select username ,account_status from dba_users where username like ‘%OLAP%’;
ACCOUNT_STATUS
————————- ——————————–
SQL& ALTER USER OLAPSYS ACCOUNT UNLOCK;
2. Run next OLAP DML command/procedure:
sqlplus apps
SQL& exec dbms_aw.execute(‘aw create odpcode’);
3. Launch adctrl and restart the failed worker.
Reference:
Patch 6678700 Worker Fails On Applying MSDODPCODE.sql With ORA-33292 Insufficient Permissions To Access Analytic Workspace APPS.ODPCODE; ORA-33262: Analytic workspace ODPCODE does not exist. (Doc ID )
rgcolset.ldt fails with: ORA-01422: exact fetch returns more than requested number of rows&
Error message:
FAILED: file rgcolset.ldt on worker
Error calling FNDLOAD function.
Uploading from staging tables
Error loading seed data for RG_REPORT_CALCULATIONS:
SEEDED_NAME = FSG_COLUMN_SET_1, AXIS_SEQ = 40, CALCULATION_SEQ = 10,
ORA-01422: exact fetch returns more than requested number of rows
Error loading seed data for RG_REPORT_CALCULATIONS:
SEEDED_NAME = FSG_COLUMN_SET_2, AXIS_SEQ = 30, CALCULATION_SEQ = 10,
ORA-01422: exact fetch returns more than requested number of rows
Error loading seed data for RG_REPORT_CALCULATIONS:
SEEDED_NAME = FSG_COLUMN_SET_10, AXIS_SEQ = 130, CALCULATION_SEQ = 10,
ORA-01422: exact fetch returns more than requested number of rows
Error loading seed data for RG_REPORT_CALCULATIONS:
SEEDED_NAME = FSG_COLUMN_SET_11, AXIS_SEQ = 50, CALCULATION_SEQ = 10,
ORA-01422: exact fetch returns more than requested number of rows
Error loading seed data for RG_REPORT_CALCULATIONS:
SEEDED_NAME = FSG_COLUMN_SET_12, AXIS_SEQ = 50, CALCULATION_SEQ = 10,
ORA-01422: exact fetch returns more than requested number of rows
Error loading seed data for RG_REPORT_CALCULATIONS:
SEEDED_NAME = FSG_COLUMN_SET_14, AXIS_SEQ = 130, CALCULATION_SEQ = 10,
ORA-01422: exact fetch returns more than requested number of rows
1. Verify that duplicates exist:
sqlplus apps
SQL& SELECT axis_set_id, axis_seq, calculation_seq, application_id, count(*)
FROM RG_REPORT_CALCULATIONS
group by axis_set_id, axis_seq, calculation_seq, application_id
having count(*) & 1;
SQL& SELECT axis_set_id, axis_seq, calculation_seq, count(*)
FROM RG_REPORT_CALCULATIONS
group by axis_set_id, axis_seq, calculation_seq
having count(*) & 1;
2. Create table backups.
SQL& CREATE TABLE xx_rg_calc_dup
SELECT axis_set_id, axis_seq, calculation_seq, application_id
FROM rg_report_calculations
GROUP BY axis_set_id, axis_seq, calculation_seq, application_id
HAVING count(*) & 1;
SQL& create table XX_rg_report_calculations_BCK as select * from rg_report_
3. Delete duplicate rows.
SQL& delete from rg_report_calculations
where rowid not in (select min(rowid)
from rg_report_calculations
group by axis_set_id, axis_seq, calculation_seq);
4. Verify that no duplicates exist anymore:
SQL& SELECT rcd.*
FROM xx_rg_calc_dup rcd
WHERE NOT EXISTS (SELECT 1
FROM rg_report_calculations rrc
WHERE rcd.APPLICATION_ID = rrc.APPLICATION_ID
AND rcd.AXIS_SEQ = rrc.AXIS_SEQ
AND rcd.AXIS_SET_ID = rrc.AXIS_SET_ID
AND rcd.CALCULATION_SEQ = rrc.CALCULATION_SEQ) ;
no rows selected
5. Launch adctrl and restart the failed worker.
6. Cleanup/remove backup tables:
sqlplus apps
SQL& DROP TABLE xx_rg_calc_
SQL& DROP TABLE XX_rg_report_calculations_BCK;
wfstartq.sql fails with ORA-04063: APPLSYS.WF_DEFERRED_QUEUE_M has errors&
Error message:
FAILED: file wfstartq.sql on worker
sqlplus -s APPS/***** @/opt/ebsr12/apps/apps_st/appl/fnd/12.0.0/patch/115/sql/wfstartq.sql &un_fnd
ERROR at line 1:
ORA-04063: APPLSYS.WF_DEFERRED_QUEUE_M has errors
ORA-06512: at “SYS.DBMS_AQADM_SYS”, line 5356
ORA-06512: at “SYS.DBMS_AQADM”, line 289
ORA-06512: at line 21
1. Drop the queue and the queue table:
sqlplus “/ as sysdba”
SQL& select owner, object_name, object_type, status from dba_objects where object_name like ‘WF_%’ and object_type=’QUEUE’ and status=’INVALID’;
OBJECT_NAME
———- ——————————–
———-
———————
WF_DEFERRED_QUEUE_M
SQL& exec dbms_aqadm.drop_queue(queue_name=& ‘APPLSYS.WF_DEFERRED_QUEUE_M’);
SQL& exec dbms_aqadm.drop_queue_table(‘APPLSYS.WF_DEFERRED_TABLE_M’,TRUE);
2. Create the queue and the queue table again (applmgr user):
sqlplus apps/&apps pwd& @$FND_TOP/patch/115/sql/wfquec2.sql APPS APPLSYS &applsys pwd&
3. Launch adctrl and restart the failed worker.
Reference:
Workflow Queues Creation Scripts (Doc ID )
cskbcat.ldt fails with: ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE&
Error message:
FAILED: file cskbcat.ldt
Error calling FNDLOAD function.
Uploading from staging tables
Error loading seed data for CS_KB_SOLN_CATEGORIES_VL:
CATEGORY_ID = 1,
ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE
1. Create Packages and Install CS Text index preferences and indexes
sqlplus apps
SQL& @$CS_TOP/patch/115/sql/cskfctxs.pls APPS
SQL& @$CS_TOP/patch/115/sql/cskfctxb.pls APPS
SQL& @$CS_TOP/patch/115/sql/cskbctxp.sql APPS CS CTXSYS
SQL& @$CS_TOP/patch/115/sql/cskbsetx.sql CS
SQL& @$CS_TOP/patch/115/sql/cssrcrix.sql APPS CS
SQL& @$CS_TOP/patch/115/sql/cskbelex.sql CS
SQL& @$CS_TOP/patch/115/sql/cskbcatx.sql CS
SQL& @$CS_TOP/patch/115/sql/cskbforx.sql CS
SQL& @$CS_TOP/patch/115/sql/csksynib.pls CS APPS
2. Launch adctrl and restart the failed worker.
jtfpfstart.sql fails with: PLS-00905: object APPS.JTF_PF_CONV_PKG is invalid&
Error message:
FAILED: file jtfpfstart.sql on worker
sqlplus -s APPS/***** @/opt/ebsr12/apps/apps_st/appl/jtf/12.0.0/patch/115/sql/jtfpfstart.sql &un_apps &un_jtf
dbms_aqadm.drop_queue_table(queue_table=&JTF_PF_CONV_PKG.QTABLENAME, force=&true);
ERROR at line 23:
ORA-06550: line 23, column 47:
PLS-00905: object APPS.JTF_PF_CONV_PKG is invalid
ORA-06550: line 23, column 6:
PL/SQL: Statement ignored
1. Logon as the applmgr user and change directory:
cd $JTF_TOP/patch/115/xdf
2. Run next java command:
adjava -mx512m -nojit oracle.apps.fnd.odf2.FndXdfCmp apps &apps pwd& \apps &apps pwd& thin &database tier&:&db port&:&ENV& type \jtf_pf_tabletype.xdf $FND_TOP/patch/115/xdf/xsl
3. If there are no errors, recompile the JTF_PF_CONV_PKG package:
sqlplus apps/&apps pwd&
SQL& alter package APPS.JTF_PF_CONV_PKG
SQL& show err
4. Launch adctrl and restart the failed worker.
Reference:
Upgrade 11i to R12.1.3 worker fails on jtfpfstart.sql PLS-00905: object APPS.JTF_PF_CONV_PKG is invalid (Doc ID )
okladtxm.sql fails with: ORA-02289: sequence does not exist&
Error message:
FAILED: file okladtxm.sql on worker
ERROR at line 110:
ORA-06550: line 110, column 9:
PL/SQL: ORA-02289: sequence does not exist
ORA-06550: line 68, column 5:
PL/SQL: SQL Statement ignored
1. Check if the OKL product is installed/used.
You can use “$AD_TOP/sql/adutconf.sql” to find out if you are using the product.
2. If you have not installed the OKL product, you can skip the failed job. Use adctrl hidden option #8, to skip the failed job.
3. If you are using this product, see MOS
or raise an SR in order to get the detailed procedure to fix.
Reference:
Patch 9239090 Fails While Running Okladtxm.sql “PL/SQL: ORA-02289: sequence does not exist” (Doc ID )
zpbremcwm.sql fails with: PLS-00904: insufficient privilege to access object OLAPSYS.CWM2_OLAP_CUBE&
Error message:
FAILED: file zpbremcwm.sql on worker
ERROR at line 235:
ORA-06550: line 235, column 6:
PLS-00904: insufficient privilege to access object OLAPSYS.CWM2_OLAP_CUBE
ORA-06550: line 235, column 6:
PL/SQL: Statement ignored
ORA-06550: line 239, column 6:
PLS-00904: insufficient privilege to access object OLAPSYS.CWM2_OLAP_DIMENSION
ORA-06550: line 239, column 6:
PL/SQL: Statement ignored
ORA-06550: line 243, column 6:
PLS-00904: insufficient privilege to access object OLAPSYS.CWM2_OLAP_CATALOG
ORA-06550: line 243, column 6:
PL/SQL: Statement ignored
ORA-06550: line 247, column 4:
PLS-00904: insufficient privilege to access object
OLAPSYS.CWM2_OLAP_METADATA_REFRESH
ORA-06550: line 247, column 4:
PL/SQL: Statement ignored
1. Grant the following privileges to the APPS user:
sqlpus system
grant execute on OLAPSYS.CWM2_OLAP_METADATA_REFRESH
grant execute on OLAPSYS.CWM2_OLAP_CATALOG
grant execute on OLAPSYS.CWM2_OLAP_DIMENSION
grant execute on OLAPSYS.CWM2_OLAP_CUBE
2. Launch adctrl and restart the failed worker.
Reference:
ZPBREMCWM.SQL Returns Error: PLS-00904: Insufficient Privilege To Access Object (Doc ID )
biv_b_age_h_sum_mv.xdf fails with: ORA-54015 duplicate column expression was specified&
Error message:
FAILED: file biv_b_age_h_sum_mv.xdf on worker
Exception occured
ORA-54015: Duplicate column expression was specified
ORA-06512: at “APPS.AD_MV”, line 131
ORA-06512: at “APPS.AD_MV”, line 289
ORA-06512: at line 1
1. Download and unzip patch .
2. Backup current biv_b_age_h_sum_mv.xdf file:
cp $BIV_TOP/patch/115/xdf/biv_b_age_h_sum_mv.xdf $BIV_TOP/patch/115/xdf/biv_b_age_h_sum_mv.xdf.org
3. Copy the biv_b_age_h_sum_mv.xdf file from patch
to the $BIV_TOP/patch/115/xdf directory:
cp &#53/biv/patch/115/xdf/biv_b_age_h_sum_mv.xdf $BIV_TOP/patch/115/xdf
4. Launch adctrl and restart the failed worker.
Reference:
This error is described in MOS . This error can be avoided when applying patch
before upgrade patch 6678700.
Error – ORA-54015 : biv_b_age_h_sum_mv.xdf Failes with Duplicate Column Expression was Specified (Doc ID )
MSC_RESOURCE_HIERARCHY_MV.xdf fails with: ORA-00955: name is already used by an existing object&
Error message:
FAILED: file MSC_RESOURCE_HIERARCHY_MV.xdf on worker
Exception occured
ORA-00955: name is already used by an existing object
ORA-06512: at “APPS.AD_MV”, line 131
ORA-06512: at “APPS.AD_MV”, line 289
ORA-06512: at line 1
1. Check if object MSC_RESOURCE_HIERARCHY_MV already exist:
SQL& select owner, object_name, object_type, status, created
from dba_objects where object_name in (‘MSC_RESOURCE_HIERARCHY_MV’)
order by 2;
OBJECT_NAME
OBJECT_TYPE
——— —————————————- ——————– ———– —————
MSC_RESOURCE_HIERARCHY_MV
MSC_RESOURCE_HIERARCHY_MV
TABLE PARTITION VALID
MSC_RESOURCE_HIERARCHY_MV
TABLE PARTITION VALID
MSC_RESOURCE_HIERARCHY_MV
TABLE PARTITION VALID
MSC_RESOURCE_HIERARCHY_MV
2. Check if
MSC_RESOURCE_HIERARCHY_MV contain any rows:
SQL& select count(*) from MSC.MSC_RESOURCE_HIERARCHY_MV;
———-
3. Drop the object if it contains no rows:
SQL& Drop synonym MSC_RESOURCE_HIERARCHY_MV;
SQL& drop table MSC.MSC_RESOURCE_HIERARCHY_MV;
4. Verify that no objects with the name MSC_RESOURCE_HIERARCHY_MV exist:
SQL& select owner, object_name, object_type, status, created
from dba_objects where object_name in (‘MSC_RESOURCE_HIERARCHY_MV’) order by 2;
no rows selected
5. Launch adctrl and restart the failed worker.
MSC_ITEM_HIERARCHY_MV.xdf fails with: ORA-00955: name is already used by an existing object&
Error message:
FAILED: file MSC_ITEM_HIERARCHY_MV.xdf on worker
Job failed with error below:
Exception occured
ORA-00955: name is already used by an existing object
ORA-06512: at “APPS.AD_MV”, line 131
ORA-06512: at “APPS.AD_MV”, line 289
ORA-06512: at line 1
1. Check if object MSC_ITEM_HIERARCHY_MV already exist:
SQL& select owner, object_name, object_type, status, created
from dba_objects where object_name in (‘MSC_ITEM_HIERARCHY_MV’)
order by 2;
OWNER OBJECT_NAME
OBJECT_TYPE
——– ——————————–
——————–
———- —————
MSC_ITEM_HIERARCHY_MV
TABLE PARTITION
MSC_ITEM_HIERARCHY_MV
MSC_ITEM_HIERARCHY_MV
TABLE PARTITION
MSC_ITEM_HIERARCHY_MV
TABLE PARTITION
MSC_ITEM_HIERARCHY_MV
2. Check if MSC_ITEM_HIERARCHY_MV contain any rows:
SQL& select count(*) from MSC.MSC_RESOURCE_HIERARCHY_MV;
———-
3. Drop the object if it contains no rows:
SQL& drop synonym MSC_ITEM_HIERARCHY_MV;
SQL& drop table MSC.MSC_ITEM_HIERARCHY_MV;
4. Verify that no objects with the name MSC_ITEM_HIERARCHY_MV exist:
SQL& select owner, object_name, object_type, status, created
from dba_objects where object_name in (‘MSC_ITEM_HIERARCHY_MV’) order by 2;
no rows selected
5. Launch adctrl and restart the failed worker.
fem_bal_nacc_hier_l2_mv.xdf fails with: ORA-54015: Duplicate column expression was specified&
Error message:
FAILED: file fem_bal_nacc_hier_l2_mv.xdf on worker
Exception occured
ORA-54015: Duplicate column expression was specified
ORA-06512: at “APPS.AD_MV”, line 131
ORA-06512: at “APPS.AD_MV”, line 289
ORA-06512: at line 1
from FEM_DIS_NAT_ACCTS_HIER_B,
ERROR at line 34:
ORA-54015: Duplicate column expression was specified
1. Download and unzip patch
2. Backup the fem_bal_nacc_hier_l2_mv.xdf file:
mv $FEM_TOP/patch/115/xdf/fem_bal_nacc_hier_l2_mv.xdf $FEM_TOP/patch/115/xdf/fem_bal_nacc_hier_l2_mv.xdf.org
3. Copy the fem_bal_nacc_hier_l2_mv.xdf file from the patch to the $FEM_TOP/patch/115/xdf directory:
cp &#17/fem/patch/115/xdf/fem_bal_nacc_hier_l2_mv.xdf $FEM_TOP/patch/115/xdf/fem_bal_nacc_hier_l2_mv.xdf
4. Launch adctrl and restart the failed worker.
Reference:
Patch Fails on Fem_bal_nacc_hier_l2_mv.xdf After Upgrade to Oracle 11.2.0.2 Database ()
Index Creation Failed on FEM_BAL_NACC_HIER_L2_MV With ORA-54015 During Import Of Oracle Apps (12.1.1) On Oracle DB (11.2.0.2) ()
czhist.sql fails with: ORA-20002: Version of statistics table APPLSYS.FND_STATTAB is too old&
Error message:
FAILED: file czhist.sql
BEGIN FND_STATS.GATHER_COLUMN_STATS(‘CZ’,’CZ_DEVL_PROJECTS’,’DELETED_FLAG’); END;
ERROR at line 1:
ORA-20002: Version of statistics table APPLSYS.FND_STATTAB is too old.
Please try upgrading it with dbms_stats.upgrade_stat_table
ORA-06512: at “APPS.FND_STATS”, line 664
ORA-06512: at “APPS.FND_STATS”, line 2497
ORA-06512: at line 1
1. Run the upgrade_stat_table procedure on the fnd_stattab table:
sqlplus “/ as sysdba”
SQL& EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE(‘APPLSYS’,’FND_STATTAB’);
2. Launch adctrl and restart the failed worker.
Reference:
Script Czhist.Sql Fails During Application Of Patch 6678700 ()
ademusr.sql fails with: ORA-01031: insufficient privileges&
Error message:
FAILED: file ademusr.sql
GRANT select on WF_DEFERRED to em_oam_monitor_role
AD Worker error:
The following ORACLE error:
ORA-01031: insufficient privileges
1. Grant privileges to APPS:
sqlplus “/ as sysdba”
SQL& GRANT select on applsys.WF_DEFERRED to APPS
SQL& GRANT select on applsys.WF_DEFERRED_TABLE_M to APPS
SQL& GRANT select on applsys.WF_ERROR to APPS
2. Launch adctrl and restart the failed worker.
Reference:
Get ORA-01031: Insufficient Privileges Running Ademusr.Sql On WF_ERROR Table ()