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!!!

Related Posts by Categories