同步WF時(shí)總提示APPLSYSD表空間不足

1、求助 同步WF時(shí)總提示APPLSYSD表空間不足 我執(zhí)行一個(gè)請(qǐng)求,同步 WF 局部表,結(jié)果報(bào)錯(cuò)ORA-1653: unable to extend table APPLSYS.WF_LOCAL_ROLES_STAGE by 138255 in tablespace APPLSYSD而該表空間空閑剩余3.7GB啊!出現(xiàn)好幾次了,每次要增加1GB的數(shù)據(jù)文件才行。而且該表空間有時(shí)候一下增加1GB左右空間,有時(shí)候又自己變少了。有人能幫助、解釋一下嗎?解決過程：1 try to resize the tbs's datafile,but no use2 這是他的數(shù)據(jù)文件分布情況3 有人建議擴(kuò)大這

2、個(gè)table的maxextents，我們不妨看看具體報(bào)錯(cuò)ORA-1653: unable to extend table APPLSYS.WF_LOCAL_ROLES_STAGE by 138255 in tablespace APPLSYSD題是很明顯，使這個(gè)table要申請(qǐng)那么多空間，可是tbs不能滿足，而非tbs由空間，可是table的maxextents到了，所以這種說法否掉4 后來我建議對(duì)方察看這個(gè)tbs的連續(xù)的free_space,因?yàn)閛racle存取數(shù)據(jù)的時(shí)候要找的是連續(xù)的space,但是對(duì)方察看后發(fā)現(xiàn)freespace也足夠5最后在metalink發(fā)現(xiàn)如下bug2812945C

3、auseWF_LOCAL_ROLESWF_LOCAL_USER_ROLESWF_LOCAL_USER_ROLES_STAGEWF_LOCAL_ROLES_STAGEWF_LOCAL_ROLES_TL_STAGEFeedback 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_e

4、xtent, pct_increasefrom dba_tab_partitionswhere 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')Unionselect table_name, 'No partition', initi

5、al_extent, next_extent, pct_increasefrom sys.dba_tables twhere 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

6、 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);' statementfrom dba_tab_partitionswhere table_name in ('WF_LOCAL_ROLES'

7、,'WF_LOCAL_USER_ROLES ')and (pct_increase <> 0 or next_extent > 16 * 1024 * 1024)Unionselect 'Alter table APPLSYS.' | table_name | ' storage clause (NEXT 16M pctincrease 0);'from sys.dba_tables twhere table_name in ('WF_LOCAL_USER_ROLES_STAGE','WF_LOCAL_R

8、OLES_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 讓對(duì)方察看table的定義SQL> select TABLE_NAM

9、E, INITIAL_EXTENT, next_extent2 from all_tables3 where table_name = 'WF_LOCAL_ROLES_STAGE'.TABLE_NAME INITIAL_EXTENT NEXT_EXTENT - - - WF_LOCAL_ROLES_STAGE 40960 755048448 可以apply patch:Patch 2350501打完后，應(yīng)該是這樣：Change the next_extent to 5M as shown below .SQL> select TABLE_NAME, INITIAL_EXTENT, next_extent2 from all_tables3 where ta