add datafile

I need to add a datafile to the tablespace. Please let me know the steps to do that.

$ sqlplus system/manager@orcl

SQL> alter tablespace tablespace_name add datafile
'../../../name.dbf' size 1000M;

SQL> exit;

Thanks.
I need to set next and max values. It doesn't let me do that.

Check the SQL refrence on Oracle's Technet for your database version for instruction on how to use any command you need.

ALTER TABLESPACE surv_data
ADD DATAFILE '/oracle/oradata/wwwp/data/surv_data_01.dbf' size 25616 K
AUTOEXTEND ON NEXT 5120 K MAXSIZE 2048016 K
MINIMUM EXTENT 160 K

min extent is valid only from 8.x+

OK. I added a datafile using command:
'alter tablespace /tablespace name/ add datafile /datafile name/ size 50m;
For some reason through GUI tool it says my datafile size is unlimited. ??? Is it right?
How do I look it up through sqlplus?
Also, I would like to alter it using: alter database datafile /datafile name/ autoextend off next 50m maxsize 500m and get ORA-00900: invalid SQL statement
When I set autoextend on it works fine.
What am I missing here?

ALTER DATABASE
DATAFILE '/xxxx/yyyyy/wwww/wwww/xxxx_yyyy_01.dbf'
RESIZE 9999 M
AUTOEXTEND OFF NEXT 9999K MAXSIZE 99999K
MINIMUM EXTENT 99K

1. select file_name, bytes/1024/1024 mb, status, autoextensible
from dba_data_files

2. You only use next 50m maxsize 500m when autoextend is on.

Quote:

---

Originally posted by mary
OK. I added a datafile using command:
'alter tablespace /tablespace name/ add datafile /datafile name/ size 50m;
For some reason through GUI tool it says my datafile size is unlimited. ??? Is it right?

---

No, if you set it to 50M then it is 50M. It's either a bug of your GUI tool or you've overlooked something. Are you sure it is not showing unlimited for MAXSIZE, not for file size?

Quote:

---

Originally posted by mary

How do I look it up through sqlplus?

---

select bytes from dba_data_files where file_name = 'YOUR_NEW_FILE'

Quote:

---

Originally posted by mary
Also, I would like to alter it using: alter database datafile /datafile name/ autoextend off next 50m maxsize 500m and get ORA-00900: invalid SQL statement
When I set autoextend on it works fine.
What am I missing here?

---

What is the point in seting the autoextent parameters (NEXT, MAXSIZE) if you switch autoextending off? Oracle obviously finds no good reason to set those two parameters if you set autoextending of at the same time.

shreddy's command will only work if autoextend is ON.
Thank you people.

hi,

Generally whenever we create a tablespace OR add a datafile, we keep autoextend off. 'Cause it will keep on increasing and you OS will start giving error at one time.

So use some gui tools like SQL Navigator or DBTools to see how much your tablespace is filled.