Solved

Tablespace cleaning

  • 6 January 2023
  • 6 replies
  • 526 views
T
Rank
Userlevel 5
Badge +11

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. 

icon

Best answer by Jonas Feigl 11 January 2023, 10:30

View original

6 replies

Srikanth
Rank
Userlevel 6
Badge +18

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. 

Cheers, Srikanth
K
Rank
Userlevel 5
Badge +15

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.

 

Piotr --> https://www.linkedin.com/in/piotr-skowronek-8425aa6/
T
Rank
Userlevel 5
Badge +11

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
Rank
Userlevel 5
Badge +17

@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!

K
Rank
Userlevel 5
Badge +15

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.

 

 

 

Piotr --> https://www.linkedin.com/in/piotr-skowronek-8425aa6/
T
Rank
Userlevel 5
Badge +11

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

Reply


Terms & ConditionsCookie settings