add datafile to a tablespace?

[yls177]

hi, what is the sql statement to add a datafile to a tablespace?

thanks

[sezgin]

try this

ALTER TABLESPACE "INDX" ADD DATAFILE '/home/oracle/oradata/orabg/t1.dbf' SIZE 5M

or see oracle documentation for ALTER TABLESPACE "your tablespace name" ADD DATAFILE ....

[yls177]

i was thinking is it possible to add a datafile to a tablespace and then remove it without affecting anything at all? the purpose is for practise my oracle administration?

thanks

[stmontgo]

you will be practicing your oracle administration on the street if you start removing datafiles from tablespaces. If you are successful
in removing a DF from a TS through an OFFLINE DROP you need to also drop and recreate the TS

[Calvin_Qiu]

You can not drop a datafile without droping the tablespace it belong.

[stmontgo]

Originally posted by stmontgo
you will be practicing your oracle administration on the street if you start removing datafiles from tablespaces. If you are successful
in removing a DF from a TS through an OFFLINE DROP you need to also drop and recreate the TS

you can but you definitely shouldn't

ALTER DATABASE DATAFILE OFFLINE DROP;

[Calvin_Qiu]

Originally posted by stmontgo
you can but you definitely shouldn't

ALTER DATABASE DATAFILE OFFLINE DROP;

Wrong, It just take a datafile offline in unarchived mode instead of dropping it. Try to query the V$DATAFILE after your action.

[stmontgo]

Originally posted by Calvin_Qiu
Wrong, It just take a datafile offline in unarchived mode instead of dropping it. Try to query the V$DATAFILE after your action.

ok so what, for all intents and purposes unless you online the file before you cycle through the online redo logs (in NO ARC mode)
that file is a distant memory, you can't get it back

[Calvin_Qiu]

Action: ALTER DATABASE DATAFILE OFFLINE DROP;

What will you get:

1, ARCHIVELOG Mode: Simply take the datefile offline immediately, need media recovery when you bring it online again;

2, NOARCHIVELOG Mode: Take the datafile offline immediately, and it is possible that you can never bring it online again because of the absent of logfiles. The DROP clause doese not remove the datafile from the database, and the datafile will remain in dictionary with a status RECOVER until you drop the tablespace in which the datafile resides.

[yls177]

so to practise the adding of a datafile to a tablespace is dangerous and should not be done at all...


i am not sure about this. but we should drop the datafile first before the tablespace?