DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, March 22, 2016

Not able to perform any file attachments in apex application

Issue Detail   :  Users are not able to attach any files in Apex application
Error Type     :  ORA-00600: internal error code, arguments: [ktspgfb-1].
User Level Problems :
11)     Users will not be able to attach any files.
DBA Level Problems :
11)     Bounce Apex will not work.
22)     Bounce the Database will not work.

ACTION  PLAN :
We can perform two action plans for this issue.
a)      We have to do expdp/impdp for the table FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$
b)     Or we can re-organize the table by moving it to other tablespace and vice versa.

Step  by  step  instruction  to  re-org  the  FILES.WWV_FLOW_FILE_OBJECTS$
1a)  We have a tablespace FM_BACKUP. So we decided to move the table to this tablespace.

2b)  Increase the FM_BACKUP and UNDO_APPSTS1 tablespace with 25 GB more.

3c)  Increase undo_retention from 1800 to 14400

Before Moving the table

ALTER USER FLOWS_FILES quota unlimited on FM_BACKUP;

SELECT 'ALTER TABLE '||OWNER|| '.'||TABLE_NAME||' MOVE TABLESPACE FM_BACKUP; ' FROM dba_tables  where TABLE_NAME='WWV_FLOW_FILE_OBJECTS$';

ALTER TABLE FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ MOVE TABLESPACE FM_BACKUP;

select 'alter table '||OWNER|| '.'||table_name||' move '||chr(10)||'LOB ('''||column_name||''') store as '||segment_name||chr(10)||'(tablespace FM_BACKUP);'
from dba_lobs where TABLE_NAME='WWV_FLOW_FILE_OBJECTS$';

alter table FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ move LOB ("BLOB_CONTENT") store as SYS_LOB0000403125C00017$$ (tablespace FM_BACKUP);

SELECT 'ALTER INDEX ' || OWNER ||'.'||index_name ||' REBUILD tablespace FM_BACKUP ONLINE COMPUTE STATISTICS;' from dba_indexes where table_name='WWV_FLOW_FILE_OBJECTS$';

ALTER INDEX FLOWS_FILES.WWV_FLOW_FILES_USER_IDX REBUILD tablespace FM_BACKUP ONLINE COMPUTE STATISTICS;

ALTER INDEX FLOWS_FILES.WWV_FLOW_FILES_FILE_IDX REBUILD tablespace FM_BACKUP ONLINE COMPUTE STATISTICS;

ALTER INDEX FLOWS_FILES.SYS_C00205743 REBUILD tablespace FM_BACKUP ONLINE COMPUTE STATISTICS;

ALTER INDEX FLOWS_FILES.WWV_FLOW_FILE_OBJ_PK REBUILD tablespace FM_BACKUP ONLINE COMPUTE STATISTICS;

ALTER INDEX FLOWS_FILES.SYS_IL0000403125C00017$$ REBUILD tablespace FM_BACKUP ON LINE COMPUTE STATISTICS;


Revert back to original Tablespace;

ALTER TABLE FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ MOVE TABLESPACE APEX;

alter table FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ move LOB ("BLOB_CONTENT") store as SYS_LOB0000403125C00017$$ (tablespace APEX);

ALTER INDEX FLOWS_FILES.WWV_FLOW_FILES_USER_IDX REBUILD tablespace APEX ONLINE COMPUTE STATISTICS;

ALTER INDEX FLOWS_FILES.WWV_FLOW_FILES_FILE_IDX REBUILD tablespace APEX ONLINE COMPUTE STATISTICS;

ALTER INDEX FLOWS_FILES.SYS_C00205743 REBUILD tablespace APEX ONLINE COMPUTE STATISTICS;

ALTER INDEX FLOWS_FILES.WWV_FLOW_FILE_OBJ_PK REBUILD tablespace APEX ONLINE COMPUTE STATISTICS;

ALTER INDEX FLOWS_FILES.SYS_IL0000403125C00017$$ REBUILD tablespace APEX ONLINE COMPUTE STATISTICS;


Revert back the undo retention to 1800


Issue Fixed:
---------------
FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ has been re-organized. Please check doing the attachments now.

No comments: