Tablespace question:

[webfuture]

Hi all,

I started a table space with this script
CREATE TABLESPACE "test"
LOGGING
DATAFILE 'E:\ORACLE\ORADATA\ORA9I\test.ora' SIZE 100M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100K SEGMENT SPACE
MANAGEMENT AUTO

Everything went fine for a while, but now I have 4 tablespace named test, test_01, test_02, test_03 of 2gig size all full and no new file will be created.

It's like there is an 8Gig limit.

What's wrong with this?

Thanks,

Simon

 

[hoinz]

What do you mean by "no new file will be created" ?
New files will never be auto-created.
You have to enter an sql command to create them.
What happens if you try it? Any error messages?
Perhaps your file system is full?

hope this helps

 

[webfuture]

My file system has 133Gig free. I never created the file 01, 02 ,03. They where created automatically.

I have a maxsize of 2048K on each and autoextend is on. But now it will not go to more files.

This is the DDL of the table space.

ALTER TABLESPACE "test"
ADD
DATAFILE 'E:\ORACLE\ORADATA\ORA9I\test.ORA' SIZE
2097120K REUSE AUTOEXTEND
ON NEXT 104K MAXSIZE 2048M

I am not to hot on oracle, still learning...

Thanks for any help

Simon

 

[hoinz]

ok, I presume files 01, 02, 03 were created during installation.

But what error message do you get when you type this
ALTER TABLESPACE ... ??

 

[alexhu]

Hoinz ir correct - Oracle does not create data files by itself.

Your tablespace statement is also a little suspect. The DDL in your second example is modifying not creating.

You add another file at 2,097,120k (2GB) put it in autoextend, then limit it to 2GB. WHY?

If this is a company system then someone else added the other tablespaces, if not then you did.

Are you confusing tablespaces with datafiles ?

Alex

 

[webfuture]

I have ran this without any problem...

ALTER TABLESPACE "test"
ADD
DATAFILE 'E:\ORACLE\ORADATA\ORA9I\test_04.ORA' SIZE
1024K REUSE AUTOEXTEND ON NEXT 104K MAXSIZE 2048M
/

I now have space to grow...

It's funny, I don't remember creating the other files manually.

Question: If autoextend is on, should not it create the new files automatically when the maxsize is reached?

Thanks,

Simon

 

[hoinz]

no,
autoextend does not mean autocreate.

 

[webfuture]

Another question if I may...

If I delete users/objects from this tablespace, do I need to do something to recover the space or it's automatic?

Thanks,

Simon

 

[hoinz]

pace of deleted objects is freed automatically;
Users do not occupy space, only their objects do.
Be aware that the space of deleted rows within a table is not freed, but it may be reused for the same table. For more info read about reorganisation.

hope this helps,
and bye,
I will go away now.

 

[SantaMufasa]

It's funny, I don't remember creating the other files manually.

Unless I am misunderstanding which other files you are talking about, you created the other files manually when you created the tablespace:

CREATE TABLESPACE "test" 
   LOGGING 
   [b]DATAFILE 'E:\ORACLE\ORADATA\ORA9I\test.ora' SIZE 100M[/b] 
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100K SEGMENT SPACE 
   MANAGEMENT  AUTO

Also, you maybe are already aware of these characteristics about tablespace datafiles:

1. You can add as many datafiles to a tablespace as you wish.
2. You do not need to create the datafile at their target sizes; you can create them at a small (e.g. 10M) size, and set the files to automatically grow in specific increments (e.g. 10M) up to a specific maximum (e.g. 2000M). Using this technique consumes filesystem disk space on a just-in-time basis.

Let us know if this information is useful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[webfuture]

Thanks to all.... I understand better now. I have a couple of hours per month to manage an oracle server. Everytime I go back I am lost...

This is the price to pay for doing too many things.

Simon