Unable to extend table space

[Former Member]

Hi,

I am unable to extend the tablespace in oracle, and also all DBA jobs are failing like CHECKDB AND UPDATE STATISTICS.

When i run BRTOOLS with ora<SID> user on unix,

it gives the below error.

" BR1049W Not enough free space in /oracle/DEV/sapdata/sapdata2/sr3_35 for total maximum size of all database files of tablespace PSAPSR3 located on this disk volume, missing at least 151532.475 MB "

Pls see the file system size below

-

---

Filesystem size used avail capacity Mounted on

/dev/md/dsk/d6 20G 20M 19G 1% /oracle/DEV/saparch

/dev/md/dsk/d7 281M 118M 135M 47% /oracle/DEV/origlogA

/dev/md/dsk/d9 281M 101M 151M 41% /oracle/DEV/mirrlogA

/dev/md/dsk/d8 281M 118M 135M 47% /oracle/DEV/origlogB

/dev/md/dsk/d10 281M 101M 151M 41% /oracle/DEV/mirrlogB

/dev/md/dsk/d11 319G 203G 112G 65% /oracle/DEV/sapdata

pls advise.

Regards

Irfan

[former_member204746]

you need to change maximum size to a smaller value. you might want to do the same thing for other datafiles located on the same disk/partition.

[fidel_vales]

Hi,

Read the message carefully (please):

" BR1049W Not enough free space in /oracle/DEV/sapdata/sapdata2/sr3_35 for total maximum size of all database files of tablespace PSAPSR3 located on this disk volume, missing at least 151532.475 MB "

1 -> you have (some of) the datafiles from the tablespace PSAPSR3 located in /oracle/DEV/sapdata/

2 -> some of those datafiles are set to autoextend

3-> if ALL datafiles would grow to the MAXEXTEND size there would be not enough space on the filesystem (missing at least 151532.475 MB)

Hope it is clear the meaning. Now you have space (112Gb free) but you said that your files will use 150 GB more than that.

Solution => Review the MAXEXTEND settings and reduce it so the files can grow without filling totally the filesystem.

[Former Member]

Hi,

Thank you for your reply,

would you pleas assist me how to reduce the max size..

Thank you,

Irfan

[audunlea_hansen]

You can use:

alter database datafile '</full path/filename>' autoextend on maxsize 10G;

Regards

Audun

DBA

[former_member204746]

other way:

brspace -c -u / -f dfalter

choose datafile

change the maximum extent size.

[Former Member]

Hi Eric

Thank you for your reply

I executed the command mentioned by you,

then i selected the option 5 which is RESIZE DATA FILE

Then i selected the datafile .

below is the next screen, where MAXIMUM FILE IS 10 GB

-

---

1 * Current data file status (status) ....... [AUTOEXTEND]

2 * Current data file size in MB (currsize) . [2000]

3 * Alter data file action (action) ......... [resize]

4 # Maximum file size in MB (maxsize) ....... [10000]

5 # File increment size in MB (incrsize) .... [20]

6 ? New data file size in MB (size) ......... []

7 # New data file name (name) ............... []

8 # Force data file alter (force) ........... [no]

9 - SQL command (command) ................... [alter database datafile 'E:\ORAC

LE\DMP\SAPDATA1\SR3_1\SR3.DATA1' resize M]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

-

---

please let me know do i need to select the option 4 and reduce the maxsize from 10 GB to 5 GB.

Regards,

Irfan

[audunlea_hansen]

Hi!

If You have enough diskspace, I would choose 10G as maxsize.

An other thing I'd like to suggest You to change, is File increment size. It's not a good idea to set it to 20MB. For a tablespace of this size 100MB or 200MB are a much better value. For very small tablespaces, upto 2-300MB, an increment size of 20MB are ok.

Regards

Audun

DBA

[Former Member]

You can only change the option with (?) hence select 6 ? and provide the new size. when you continue you can see the SQL command which is going to execute.