Error - Gather statistics for SYS schema as part of upgrade to 10.2.0.4

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 SYSTEM
ORA-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 TEMP
ORA-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.

Temporary tablespace usage in percentage ---
select 100*(u.tot/d.tot) "pct_temp_used" FROM
     (select sum(u.blocks) tot from v$tempseg_usage u) u,
     (select sum(d.blocks) tot from dba_temp_files d) d

Temporary tablespace usage----
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

Finally decided add a tempfile of 1G

ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp03.dbf' SIZE 1G;

Run the command again using

$ sqlplus "/ as sysdba"
SQL> shutdown normal;
SQL> startup restrict;
SQL> @?/rdbms/admin/adstats.sql 

--- adstats.sql started at 2010-10-30 13:49:15 ---

Checking for the DB version and collecting statistics ...

PL/SQL procedure successfully completed.

--- adstats.sql ended at 2010-10-30 14:29:36 ---

Commit complete.


Cheers!!!

How to reset Workflow Administrator in 11i

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.

update wf_resources
set text = 'SYSADMIN'
where name = 'WF_ADMIN_ROLE';
commit;

This action will assign your workflow administrator role to your SYSADMIN user.  After updating it, if you want to change it, please do it via the workflow configuration page using the SYSADMIN user.

2. Retest the issue.

3. Migrate the solution as appropriate to other environments.


Cheers!!!