Skip to main content
Solved

Tablespace cleaning

  • January 6, 2023
  • 6 replies
  • 1042 views

Forum|alt.badge.img+13
  • Hero (Partner)
  • 158 replies

Hello to all,
Some time ago we encountered a problem.
Indeed, the tablespace "IFSAPP_DATA" was full.

So we decided to recover the name of the heaviest table.
It was the table "TRANSACTION_SYS_LOCAL_TAB" (table which contains all the background tasks).
Indeed, the number of tasks in this table was high.
So we changed some system parameters to reduce the number of tasks.

Today, checking the size of the "IFSAPP_DATA" tablespace, it still has the same size.
When I run this query : 

SELECT NUM_ROWS FROM user_tables where TABLESPACE_NAME = 'IFSAPP_DATA' and TABLE_NAME = 'TRANSACTION_SYS_LOCAL_TAB'

I see that there are still +800,000 rows in this table.
While in IFS, there are only 50 000 rows.

My question is: Is there a scheduled task in IFS to clean the tablespaces?
Thanks in advance. 

Best answer by Jonas Feigl

@TheoB Have a look at this article: http://www.dba-oracle.com/t_alter_table_shrink_space_compact.htm

 

So what you can do is to run “ALTER TABLE TRANSACTION_SYS_LOCAL_TAB SHRINK;” to lower the high water mark of the table and release the space (please note that this will not reduce the oracle data file size).

 

Indexes are automatically rebuilt when large amounts of data are deleted as long as you have the “Rebuild Index” job scheduled to run - which I hope you have!

View original
Did this topic help you find an answer to your question?

6 replies

Srikanth
Superhero (Partner)
Forum|alt.badge.img+18
  • Superhero (Partner)
  • 291 replies
  • January 6, 2023

The light and heavy cleanup scheduled tasks should handle this for you. Please ensure they are enabled and running.

Also look for Background Job related System Parameters to make sure the correct values are set for Error, Warning and Completed jobs. These values control how many days worth of background job records are retained in the system. 

If older information isn’t getting deleted by the cleanup routines, the jobs have been manually excluded from cleanup. This is an RMB option in Background Jobs screen.

Given the numbers you posted, I suspect that the cleanup routines aren’t running. 


Forum|alt.badge.img+16
  • Superhero (Partner)
  • 457 replies
  • January 9, 2023

Hi @TheoB 

 

You have to gather table statistics to update Num_Rows column. This value is not real num of rows. It is only used to estimate query plan, etc.

Moreover Heavy Cleanup only removes old rows but does not recover free space to tablespace.

The best way is to rebuild table and its indexes in this particular example.

 


Forum|alt.badge.img+13
  • Author
  • Hero (Partner)
  • 158 replies
  • January 11, 2023
knepiosko wrote:

Hi @TheoB 

 

You have to gather table statistics to update Num_Rows column. This value is not real num of rows. It is only used to estimate query plan, etc.

Moreover Heavy Cleanup only removes old rows but does not recover free space to tablespace.

The best way is to rebuild table and its indexes in this particular example.

 

Hi @knepiosko,

What do you mean by "rebuild table and its indexes in this particular example." ?
Is there an IFS procedure to do this?

Thanks for your help.


Jonas Feigl
Superhero (Employee)
Forum|alt.badge.img+20
  • Superhero (Employee)
  • 264 replies
  • Answer
  • January 11, 2023

@TheoB Have a look at this article: http://www.dba-oracle.com/t_alter_table_shrink_space_compact.htm

 

So what you can do is to run “ALTER TABLE TRANSACTION_SYS_LOCAL_TAB SHRINK;” to lower the high water mark of the table and release the space (please note that this will not reduce the oracle data file size).

 

Indexes are automatically rebuilt when large amounts of data are deleted as long as you have the “Rebuild Index” job scheduled to run - which I hope you have!


Forum|alt.badge.img+16
  • Superhero (Partner)
  • 457 replies
  • January 11, 2023

Hi @TheoB and @Jonas Feigl 

 

Rahter than SHRINK option I recommend MOVE command to rebuild LOB segment because in my opinion ARGUMENTS LOB column occupies most of space.

ALTER TABLE TRANSACTION_SYS_LOCAL_TAB MOVE LOB (ARGUMENTS) STORE AS SECUREFILE (TABLESPACE IFSAPP_LOB)

Shrink opiton is used in “Shrink lob segments” job but there is no sufficient effect of reducing size of LOB objects.

Rebuild indexes job is not scheduled by default. Manually rebuild index You could do on Solution Manager\System Information and Utilities\Oracle Objects\Oracle Index Management

 

By rebuild table I mean MOVE option:

ALTER TABLE IFSAPP.TRANSACTION_SYS_LOCAL_TAB MOVE

You have to rebuild all indexes on table after that.

 

 

 


Forum|alt.badge.img+13
  • Author
  • Hero (Partner)
  • 158 replies
  • January 11, 2023

Thank you for all this information, I will look into it.
Have a nice day!


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings