Saturday, 30 October 2010

Today I have an issue while upgrading one of my 11i CU2 instance database to 10.2.0.4, as part of the upgradation process (Note:1135973.1) need to Gather statistics for SYS schema using adstats.sql in database restrict mode. But after sometimes getting errors like

--- adstats.sql started at 2010-10-30 11:52:47 ---

Checking for the DB version and collecting statistics ...

declare
*ERROR at line 1:ORA-12801: error signaled in parallel query server P002 ORA-1652: unable to extend temp segment by 106496 in tablespace SYSTEMORA-06512: at "SYS.DBMS_STATS", line 13591 ORA-06512: at "SYS.DBMS_STATS", line 13937 ORA-06512: at "SYS.DBMS_STATS", line 14015 ORA-06512: at "SYS.DBMS_STATS", line 13974 ORA-06512: at line 2 ORA-06512: at line 33

So checked the temporary tablespace for system, found that not TEMP.

select username,default_tablespace,temporary_tablespace from dba_users dba_users
where username in ('SYS','SYSTEM')

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
SYSTEM SYSTEM SYSTEM
SYS SYSTEM SYSTEM

so I have changed the default temporary tablespace to temp using

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Now its showing correctly

USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
SYSTEM SYSTEM TEMP
SYS SYSTEM TEMP

Again connected database using sysdba privilages and run the adstats.sql again. But this time getting

ORA-12801: error signaled in parallel query server P002 ORA-1652: unable to extend temp segment by 16 in tablespace TEMPORA-06512: at "SYS.DBMS_STATS", line 13591 ORA-06512: at "SYS.DBMS_STATS", line 13937 ORA-06512: at "SYS.DBMS_STATS", line 14015 ORA-06512: at "SYS.DBMS_STATS", line 13974 ORA-06512: at line 2 ORA-06512: at line 33

while checking usage of temporary tablespace, usage nearing about 100% and suddenly getting this error.

Thursday, 28 October 2010

Recently one of my development instance having issue coz of change the Workflow Administrator privilege to user.

Finally managed with this solution,

1. To change the workflow administrator role, the System Administrator:Any security group responsibility must be assigned to yourself. Try to assign this responsibility to your user in order to change the workflow administrator.
OR
If you cannot assign it to you for some reason, you will need to update it from sqlplus.