Resize Operation Completed For File# (Doc ID 1982901.1)

In 12c and above, you may see messages similar to the following message in the alert log

Resize Operation Completed For File# 23, old size 1024K, new size 2048K

That means the data file is defined as auto extensible and when data in the file is increasing and the size of the file needs to be increased accordingly to hold data.

It is a diagnostic enhancement to record automatic datafile resize operations in the alert log.

Recently I ran into an quirky thing: while I was watching an alert log of a database, I noticed that the message kept popping out for the same data file which is the data file for the system tablespace.

Normally the size of system tablespace is quite stable and won’t increase too much in a short period. Unless there was something unusual happening? After looking around, I found someone was trying to use CTAS to create a backup of a big table. The problem was he was connecting as the user system which has system tablespace as the default tablespace. And the backup of the table was being created in the system tablespace! That caused the system data file were being increased constantly.

Lesson learned here, as an administrator, be careful which user you are logging as and your operations might have unexpected negative effects on the system.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s