Resizing a datafile

[former_member759680]

Hello,

We need to reduce the size of datafile, using BRTOOLS.

For that do we need to stop SAP?

Is the database restarted for resizing a datafile?

Thanks.

[stefan_koehler]

Hello Gautam,

> For that do we need to stop SAP?

No, you can resize a data file online.

> Is the database restarted for resizing a datafile?

No.

Maybe you will receive an ORA-03297, if you try to shrink the data file too much: http://ora-03297.ora-code.com/

Regards

Stefan

[former_member759680]

Thanks for the reply.

Can you tell me how to determine which datafiles can be reduced in size?

Also, I wanted to confirm - We can remove the Autoextend attribute of the datafile, without the need to stop the database, right?

thanks.

[stefan_koehler]

Hello Gautam,

> Can you tell me how to determine which datafiles can be reduced in size?

Please check metalinknote #130866.1 - it provides two sql scripts to determine the HWM of a datafile. If you don't have access to metalink by your own account .. you can check sapnote #758563 for access.

> We can remove the Autoextend attribute of the datafile, without the need to stop the database, right?

Yes, you are correct.

Regards

Stefan

[former_member759680]

Thanks a tn for your guidance Stefan.

We have decided that -

1. Disable AUTOEXTEND of datafiles today

2. Move few datafiles to another drive over teh weekend (since that woudl require downtime.

3. Enable AUtoextend again after the movement.

Regarding Autoextend attribute -

I am using brtools for this activity.

Can we disable autoextend of a datafile, if its status is 'ONLINE

Or do we need to change the status first?

Thanks.

[former_member204746]

you can disable autoextend on a datafile even if it's online.

After this, I would check if your tablespace is not too full. By disabling autoextend, you also disable the ability for the datafile to grow. So, you are probably close to being 100% full. So, add a datafile elsewhere or extend your existing datafile.