ORA-15041 When creating a tablespace

[lekinho_]

Environment Information:
* Oracle version: 10g
* Operating system: RHEL5

I am trying to create a tablespace (Bigfile), but is giving the error

Select all

ora-15041: DISKGROUP SPACE EXHAUSTED. 

The file I'm trying to create has 200GB ... there is spacing from the disc. Can anyone tell me if I'm doing something wrong? Does just have any limitation on file size?

Thanks

[victorhugomuniz]

Select all

 
ORA-15041:  diskgroup space exhausted 
Cause: 	   The diskgroup ran out of space. 
Action: 	   Add more disks to the diskgroup, or delete some existing files.

Are you sure you have space? How did the space checked?

Select all

select group_number gn,name,type,state,total_mb,free_mb,usable_file_mb 
from v$asm_diskgroup

[lekinho_]

This query even .. has enough space space. So much so that I created 7 files of 30GB and it worked. But I could not create a bigfile of 200gb ... it must be some limitation that I really unknown

[diegopedrao]

cara,

for you to post the return of the select quoted quoted by Victor?

ATT,

Diego Monteiro

[lekinho_]

Claro

Select all

APDATA	EXTERN	MOUNTED	389116	111280	111280 
APIND	EXTERN	MOUNTED	204797	76694	76694 
APLOG	EXTERN	MOUNTED	51199	33980	33980 
LOGMULT	EXTERN	MOUNTED	51199	50477	50477

has enough space ... So much so I managed to create several files of 30GB ... but I could not create a 200GB archival ..

[diegopedrao]

Lekinho,

From what I saw in your Select, the free booked space for data is only 110GB so you can not create the 200GB archive.

ATT,

Diego Monteiro

[lekinho_]

The free space is 110 because I have already created the 7 tablespaces of 30GB: o)))
The total column_mb returns 390, which means that it had enough space when I tried, unsuccessfully, create the 200GB file. Oh, I had to choose to create several smaller tablespaces ..

[DBA_LUCAS]

Until where I know Oracle supports at the Maximo 32Girls for each date file, making it impossible for the creation of a datafile of 200Gigas ... if I am wrong to correspect me ...

[diegopedrao]

Lucas,

Taking into account a smallfiles bank Each Datafile contains about 4 million blocks, if your block is 8k (default), you may have the following account: 4194304 * 8192 = 34359738368, ie Each Datafile can reach ~ 34GB.

Another example would be if you had the block of 32K, so we would have the maximum size for each Datafile doing the same account:

4194304 * 32768 = 137438953472 Your Datafile could reach ~ 137GB, already being considered a bigfile.

But you can read more in the Oracle Database Release 2 manual in Physical Database Limits.


I hope I have healed the diction of all.

ATT,

Diego Monteiro

[DBA_LUCAS]

So I can make this change?

With which command do I change?
And which select returns my Datafile size setting?

[diegopedrao]

Luke,

After a bank created you can not change the block configuration, what you could do is export the base and create a new bank already with the altered parameter.

To verify this parameter, just run the SELECT below:

Select all

 
SELECT * 
  FROM V$PARAMETER 
 WHERE UPPER(NAME) = 'DB_BLOCK_SIZE'; 

Note: For the maximum block size it will depend on of yours.

ATT,

Diego Monteiro

[DBA_LUCAS]

I returned the value 3433134853, which account I do to know how many gigs supports?

[diegopedrao]

Lucas,

You took the hash column, you could do so take the value of the column bytes and multiplies by 4194304 (quantity of blocks by Datafile, according to Oracle parameter to Smallfiles) that will be equal to 34359738368. ie ~ 34GB.

ATT,

Diego Monteiro

[diegopedrao]

Luke,

follows the site with the Limit Manual:
http://download.oracle.com/docs/cd/B193 ... its002.htm
ATT,

Diego Monteiro

[lekinho_]

Thank you all for the answers. This solves my doubt. The limitation is given by the OS and the account to know the maximum size of a file is made by db_block_size.