Sunday, March 27, 2016

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

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

Issue while upgrading 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 there is no 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.

No comments:

Post a Comment