tablespace extend problem

[Former Member]

I am trying to extend tablespace by adding a datafile.

I am using BRTOOLS.

But it is giving the following error:-

BR0280I BRSPACE time stamp: 2009-07-09 10.28.18

BR0370I Directory /oracle/GEP/sapreorg/seazbpkg created

BR0280I BRSPACE time stamp: 2009-07-09 10.28.19

BR0319I Control file copy created: /oracle/GEP/sapreorg/seazbpkg/cntrlGEP.old 30

BR0280I BRSPACE time stamp: 2009-07-09 10.28.19

BR1088I Extending tablespace PSAPSR3...

BR0280I BRSPACE time stamp: 2009-07-09 10.28.44

BR0301E SQL error -59 at location BrSqlExecute-1, SQL statement:

'/* BRSPACE */ alter tablespace PSAPSR3 add datafile '/oracle/GEP/sapdata1/sr3_'

ORA-00059: maximum number of DB_FILES exceeded

BR1017E Execution of SQL statement 'alter tablespace PSAPSR3 add datafile '/orad

BR0669I Cannot continue due to previous warnings or errors - you can go back ton

BR0280I BRSPACE time stamp: 2009-07-09 10.28.44

Kindly help...................

[Former Member]

U can increase by 256 to 512 size as per ur requirmnet. as per detail u have sufficint Database Diectory sapce volum in ur system

Thanks

presu

[Former Member]

Dear All,

Thanks a lot for your valuable replies.

My query is that My DB_FILE size is 254. Upto what extend I can increase it? Is there any standard method for the same.

[Former Member]

Hi bhaskar,

Actually DB_files reserv the space in SGA when database start so it should not much higher.i don't know exact calculation

but it depends on OS that why MAX value depends on OS.

[former_member204746]

I would extend to 512. This is the setting I use on my systems.

this being said, unless your system is very large, 256 should have been sufficient. what are the average size of your datafiles?

[Former Member]

Average data filee size is 175 - 200 GB. If require Can I extend it by DB26 tocde ?

please reply.

[former_member204746]

175GB is probably the tablespace size, or you DBG isr 256*175Gb hich translates to 44800GB or about 45TB.

What I want is the datafile size (it's the same as the size of file at OS level)

[Former Member]

Yes , we have 4 datafiles (SAP DATA1, 2,3,4) and each datafile is 200 GB .

[former_member204746]

again, SAPDATA are not datafiles, they are directories. and these directories wil lcontain files that Oarcle names datafiles.

so, again, what are the sizes of those files?

[Former Member]

Same thing u can do by brtools by 5th option alter datafiles--

---

> continue---> continue--

---

> 5th option Resize Datafile

Thanks

Presu

[Former Member]

Hi,

Fallow this procedure

Login with sys user.

SQL> SHOW parameter db_files

db_files integer <Current Value>

SQL> alter system set db_files = <New Value> scope = spfile;

SQL.> Shutdown immediate

SQL> Startup

SQL> Show parameter db_files

I hope by this ur problem will be solved.

Thanks

Presu

[Former Member]

Hi,

Ora- 00059 : increase DB_FIles

alter system set db_files=<NewValue> scope=spfile;

shutdown ;

startup

[sunny_pahuja2]

Hi Bhaskar,

Please check parameter as per SAP note 830576 and change this parameter accordingly.

You can check your parameters automatically from SQL script attached in SAP note 1171650

Thanks

Sunny

[Former Member]

Hi,

Check value of DB_FILES parameter from SAP Note 830576 - Parameter recommendations for Oracle 10g(if your DB is 10g) & change it accordingly

Note 398927 - ORA errors because dimensions of parameters are too small

Note 8812 - Increasing max. number of data files in Oracle

Hope it helps

Thanks

Sushil

[former_member603052]

Hi Bhaskar,

There is a parameter in init<sid>.ora which controls the number of datafiles that a database can open.

DB_FILES is the parameter whcih specifies the maximum number of database files that can be opened for this database.

So please kindly check the value and change it if it is reached.

Kalyan