我执行一个请求,同步 WF 局部表,结果报错
ORA-1653: unable to extend table APPLSYS.WF_LOCAL_ROLES_STAGE by 138255 in tablespace APPLSYSD
而该表空间空闲剩余3.7GB啊!!!
出现好几次了,每次要增加1GB的数据文件才行。
而且该表空间有时候一下增加1GB左右空间,有时候又自己变少了。
有人能帮助、解释一下吗?
解决过程：
1 try to resize the tbs's datafile,but no use
2 这是他的数据文件分布情况
3 有人建议扩大这个table的maxextents，我们不妨看看具体报错
ORA-1653: unable to extend table APPLSYS.WF_LOCAL_ROLES_STAGE by 138255 in tablespace APPLSYSD
题是很明显，使这个table要申请那么多空间，可是tbs不能满足，而非tbs由空间，可是table的maxextents到了，所以这种说法否掉
4 后来我建议对方察看这个tbs的连续的free_space,因为oracle存取数据的时候要找的是连续的space,但是对方察看后发现freespace也足够
5最后在metalink发现如下bug2812945
The table storage parameters are fixed in 11i.OWF.H - Bug 3258819
This problem was detected on Bug 2812945
When a next extent is required for any of the following tables is requested, as the PCTINCREASE is set to 50, it asks each time for a bigger space.
WF_LOCAL_ROLES
WF_LOCAL_USER_ROLES
WF_LOCAL_USER_ROLES_STAGE
WF_LOCAL_ROLES_STAGE
WF_LOCAL_ROLES_TL_STAGE
Feedback from development.
解决方案：
To implement the solution, please execute the following steps:
1. List storage parameters for wfds tables.
select table_name, partition_name, initial_extent, next_extent, pct_increase
from dba_tab_partitions
where table_name in ('WF_LOCAL_USER_ROLES_STAGE'
,'WF_LOCAL_USER_ROLES'
,'WF_LOCAL_ROLES_STAGE'
,'WF_LOCAL_ROLES'
,'WF_LOCAL_ROLES_TL'
,'WF_LOCAL_ROLES_TL_STAGE')
Union
select table_name, 'No partition', initial_extent, next_extent, pct_increase
from sys.dba_tables t
where table_name in ('WF_LOCAL_USER_ROLES_STAGE'
,'WF_LOCAL_ROLES_STAGE'
,'WF_LOCAL_ROLES_TL_STAGE')
order by 1, 2;
2. All partitions that have next extent over 16M or pctincrease different to 0 need to be modified.
Generate the statements to correct these storage parameters using this SQL statement:
Select 'Alter table APPLSYS.' || table_name || ' modify partition '
|| partition_name || ' storage ( next 16M pctincrease 0);' statement
from dba_tab_partitions
where table_name in ('WF_LOCAL_ROLES','WF_LOCAL_USER_ROLES ')
and (pct_increase <> 0 or next_extent > 16 * 1024 * 1024)
Union
select 'Alter table APPLSYS.' || table_name
|| ' storage clause (NEXT 16M pctincrease 0);'
from sys.dba_tables t
where table_name in ('WF_LOCAL_USER_ROLES_STAGE'
,'WF_LOCAL_ROLES_STAGE'
,'WF_LOCAL_ROLES_TL_STAGE')
and (pct_increase <> 0 or next_extent > 16 * 1024 * 1024)
order by 1;3. Run previous statements generated.
4. Rerun statement in step #1 to review the changes in the storage wfds table storage parameters.
5 让对方察看table的定义
SQL> select TABLE_NAME, INITIAL_EXTENT, next_extent
2 from all_tables
3 where table_name = 'WF_LOCAL_ROLES_STAGE';
.
.
TABLE_NAME INITIAL_EXTENT NEXT_EXTENT
--------------------- ----------------------- -----------------------
WF_LOCAL_ROLES_STAGE 40960 755048448
可以apply patch:Patch 2350501
打完后，应该是这样：
Change the next_extent to 5M as shown below
.
SQL> select TABLE_NAME, INITIAL_EXTENT, next_extent
2 from all_tables
3 where table_name = 'WF_LOCAL_ROLES_STAGE';
.
TABLE_NAME INITIAL_EXTENT NEXT_EXTENT
--------------------- ----------------------- -----------------------
WF_LOCAL_ROLES_STAGE 40960 5242880
嗬嗬，搞了半天居然是table的参数定义搞得鬼！
Remen
2006.8.19
|