DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: add datafile to a tablespace?

  1. #1
    Join Date
    Oct 2002
    Posts
    391

    add datafile to a tablespace?

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

    thanks

  2. #2
    Join Date
    Apr 2002
    Posts
    17
    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 ....

  3. #3
    Join Date
    Oct 2002
    Posts
    391
    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

  4. #4
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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
    I'm stmontgo and I approve of this message

  5. #5
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    You can not drop a datafile without droping the tablespace it belong.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  6. #6
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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;
    I'm stmontgo and I approve of this message

  7. #7
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    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.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  8. #8
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    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
    I'm stmontgo and I approve of this message

  9. #9
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    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.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  10. #10
    Join Date
    Oct 2002
    Posts
    391
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width