hi, what is the sql statement to add a datafile to a tablespace?
thanks
Printable View
hi, what is the sql statement to add a datafile to a tablespace?
thanks
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 ....
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
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 not drop a datafile without droping the tablespace it belong.
you can but you definitely shouldn'tQuote:
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
ALTER DATABASE DATAFILEOFFLINE DROP;
Wrong, It just take a datafile offline in unarchived mode instead of dropping it. Try to query the V$DATAFILE after your action.Quote:
Originally posted by stmontgo
you can but you definitely shouldn't
ALTER DATABASE DATAFILEOFFLINE DROP;
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)Quote:
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.
that file is a distant memory, you can't get it back
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.
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?