Hi,

Which version of IFS applications are you trying this on? 

We are using IFS App 9 UPDATE 7.

For the moment I have figured out following facts. I suspect that the reason might be that the storage for CLOB column cannot be extended. The definition in table FND_NOTE_PAGE_TAB says

TABLESPACE "IFSAPP_LOB" ENABLE STORAGE IN ROW CHUNK 8192
  NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES
  STORAGE(INITIAL 106496 NEXT 1048576 ...)

and in dba_free_space we can see that for tablespace IFSAPP_LOB largest portion has size 786432 bytes. It means that there is no 1MB size disk space piece which could be allocated for our CLOB column. I assume that the initial extent might be full.

Is this a good lead ?

yes that can be the problem. If ifsapp_lob is full, you won't be able to do any other operation that use the lob space. I.e. ordering reports. Are you able to order reports that are larger than 1 mb (xml size) ?

OK, I will try to find such a report. See result of the query on DBA_FREE_SPACE for tablespace IFSAPP_LOB below. It seems that only 3 MB is free in total.

The problem is that in our company there is no Oracle administrator, so probably I need to learn how to do it. Last time I did such kind of work 15 years ago, so I need to recall how to do it. Probably I need to apply:
ALTER TABLESPACE … ADD DATAFILE

or

ALTER DATABASE DATAFILE … RESIZE

I also don't know much about these dba things. Lets wait for an oracle guru here. We have a schedule task named shrink lob segments. This might claim more free space without needing to increase more space. But this should only be executed during a downtime where no other operations are running.

You can use the following query to find out the max size, allocated size of data files and see if they have been set to auto extensible:

select TABLESPACE_NAME, FILE_NAME, AUTOEXTENSIBLE, round(BYTES/1024/1024,2) Bytes,round(MAXBYTES/1024/1024,2) MaxBytes  from dba_data_files order by TABLESPACE_NAME; 

If IFSAPP_LOB table space is full, then you can add a new data file to the segment.

Add a new data file for a specific segment:

alter tablespace IFSAPP_LOB add datafile 'D:\Oradata\IFSPRD\IFSAPP_LOB03.DBF’ size 50M autoextend on next 20M maxsize 22000M;

Please note that you have to modify the above statement by giving appropriate values for the data file name/location and sizes(the values in bold italics).

Since this needs some DBA expertise, it is advised to acquire help from a DBA if you are not much familiar with this. You need to perform this during a maintenance down time. 

Hope this helps.

Increasing the table-space for IFS_LOB should resolve your issue.  But as @Charith has mentioned, 

this needs some level of DBA expertise. If this is a PROD, it is highly recommended to request assistance if you are not familiar with DB administration. 

I agree that the tablespace size is likely the issue but given your situation you should bring in a DBA (even remotely) to extend the tablespace appropriately.

The review and prep work should not take them long - less than an hour to review and prepare for the out-of-hours change.  The change itself should also be quick but may or may not require a brief outage.

While some of the input from prior replies is good, they do make some assumptions.  For example, if you are using ASM storage then the process to add storage is different than just adding a new datafile to a file system (which is what @Charith described).

It isn’t especially hard to do, however you do it, but the right solution needs to be used to avoid issues.

Nick

Yes, we contacted DBA from IFS. She says the same that we should add new datafile to tablespace IFSAPP_LOB. What I am not sure is whether I can do it during normal work and whether I should take the tablespace offline before adding new data file.

Another issue is why LOBs take 32 GB while IFSAPP_DATA has 6GB free space within 32 GB data file. Do you know any tool which I can use to find out what LOB columns use that space ? Maybe we need to delete or archive some data.

(added later) Here are 10 largest LOBS from my database. The largest one use 9 GB. Possible we can delete old reports. Any suggestions from experienced IFS administrators ?

Regards,

Marek

P.S. Thank you for all responses here !

You can identify what tables are located in a particular tablespace from the dba_tables view using something like the following command.  Based on the output you added later it looks like you might already have found something similar?

select table_name from dba_tables where tablespace_name='IFSAPP_LOB';

> Another issue is why LOBs take 32 GB while IFSAPP_DATA has 6GB free space within 32 GB data file.

There isn’t really any correlation between the two tablespaces.  If you have both an IFSAPP_LOB and an IFSAPP_DATA tablespace then most LOBs (Large Objects) will normally be stored in IFSAPP_LOB.  Either tablespace can fill up independently without any connection to the other one.  It doesn’t matter if IFSAPP_DATA has space free; if it is saving a LOB and the LOB is configured to be stored in IFSAPP_LOB then it will try to go to IFSAPP_LOB.

Hope that helps,

Nick

I would like to add one conclusion to this historic topic. The story finished happily as I extended the tablespace and pictures are now saved in yellow notes.

However I talked to my colleague - we worked together in IFS. He said that it is not good behavior that system does not give any error “no disk space” or similar. System just do not save the picture which is confusing for the user and administrator. It seems that saving of the yellow notes is done silently - not as the other data which is saved from the client. I guess this should be reported to IFS.

Anyone from IFS is here to react ?!