How to Change the AutoExtend "Unlimited" mode to particular Size in Tablespace???

[Former Member]

Hi,

In my ecc server oracle database PSAPSR3 Table space Auto extend mode shown as Unlimited ,how can change it a particular size.??? 

[Former Member]

Hi,

You can use  BRTOOLS options to switch off the autoextend for PSAPSR3 table space, later you can resize and fix a maximum size for the tablespace in the BRTOOLS SPACE MGT options.

Please let me know if you still want the step by step procedure.

Thanks ,

Ahamed.

[former_member188883]

Hi Arun,

You can make the autoextend off using

ALTER DATABASE DATAFILE '<path of datafile under PSAPSR3700>' AUTOEXTEND OFF;

Change the maxsize to 32GB  keeping the autoextend on feature using

alter database datafile '<path of datafile>' autoextend on maxsize 32000M;

You may write a script to include commands for all the datafiles within the particular tablespace.

Hope this helps.

Regards,

Deepak Kori

[Former Member]

Hi Deepak,

Thanks !!

But i need to change the PSAPSR3 Table space autoextend, and it contains 4 data files,which file can i  alter???

[Reagan]

Can you list all the datafiles of PSAPSR3 and their max size please ?

Regards

RB

[Former Member]

Hi Reagan,

[Reagan]

Tx DB02 - Current Sizes - Select "PSAPSR3" - Select "Data files / temp files" at the top

This will list all the data files of PSAPSR3 with the correct max size.

Regards

RB

[Reagan]

Or query the dba_data_files to check the max size

set numwidth 11

select MAXBYTES from dba_data_files where TABLESPACE_NAME='PSAPSR3';

Regards

RB

[Former Member]

Hi,

[former_member188883]

Hi Arun,

For your information

Data files are not exactly unlimited in size, so the term "Unlimited" refers to the ceiling your datafile is able to reach, and it depends on the Oracle Block Size. To find the absolute maximum file size multiply block size by 4194303. This is the actual maximum size. You may want to read the Metalink Note:112011.1

.

Please check what is the blocksize (db_block_size) parameter in initSID.ora file.

If the block size is 8192 then maximum datafile size can be 32 GB.

Please execute the command to have better understanding of MaxSize in GB

select MAXBYTES/1024/1024 from dba_data_files where TABLESPACE_NAME='PSAPSR3';

Once you get the max_size of datafiles under PSAPSR3 use the below command to set maxsize for each of the datafiles to restrict the growth.

alter database datafile '<path of datafile>' autoextend on maxsize 32G;

Note: new maxsize will depend on your db_block_size parameter.

Hope this helps.

Regards,

Deepak Kori

[Former Member]

Thanks !!!

For that above activity we need to down the sap???

[Reagan]

Hello

The output is not readable.

select MAXBYTES/1024/1024 from dba_data_files where TABLESPACE_NAME='PSAPSR3';

This will give the maxsize of all the datafiles in MB.

If they are are less than 32700 MB and if you want to change the max size of the datafiles then use BRTools

brspace -c force -u / -f dfalter -a autoext -t PSAPSR3 -file all_df -i 100 -m 32700

This will set the maxsize of all the datafiles or PSAPSR3 to 32700 MB with increment of 100 MB

You can do it online

Regards

RB

[former_member188883]

Hi Arun,

It will be good to have SAP application down to avoid any issues during the changes.

Regards,

Deepak Kori

[Reagan]

It will be good to have SAP application down to avoid any issues during the changes.

Why ? Extending the MaxSize of a datafile doesn't permit the database from writing data into the datafile.

Regards

RB

[former_member188883]

It will be good to have SAP application down to avoid any issues during the changes.

Why ? Extending the MaxSize of a datafile doesn't permit the database from writing data into the datafile.

I know it can be done online. Suggestion for having application down is to avoid any locking situation that may appear due to this activity. Just a safeguarding that's all.