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