Results 1 to 7 of 7

Thread: tablespace

  1. #1
    Join Date
    Aug 2003
    Posts
    123

    Unanswered: tablespace

    I have created a user xxx on tablespace_1 and temp tablespace tmp_1.
    To load 50m test data I have extended the tablespace_1 with 5 datafile each of 2GB. now I have removed the testdata I need only 100MB of one datafile, my question how to remove the datafile without affecting the database.

    --Jaggu

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Hi,

    This is what I read in the Oracle documentation

    <<<ORACLE DOCUMENTATION>>>
    Dropping Datafiles
    There is no SQL statement that specifically drops a datafile. The only means of dropping a datafile is to drop the tablespace that contains the datafile. For example, if you want to remove a datafile from a tablespace, you could do the following:

    1.Create a new tablespace
    2.Move the data from the old tablespace to the new one
    4.Drop the old tablespace
    >>>ORACLE DOCUMENTATION<<<

  3. #3
    Join Date
    Aug 2003
    Posts
    123
    Hi,
    How to Move the data from the old tablespace to the new one.
    can you pls. give me the dtls.

    --Jaggu

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You use alter table <tablename> move tablespace <new tablespace>

    BUT dont forget to rebuild all indexes associated with the tables as they will become invalid.

    Write a sql query against dba_tables to write your 'alter table move' commands for all tables belonging to your old tablespace. And you can do a similar thing to rebuild all indexes where status<>'VALID'

    Alan

  5. #5
    Join Date
    Aug 2003
    Posts
    123
    Alan,
    Is there any way to move the data from one datafile to another

    --Jaggu

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    From the user owning the tables ...

    sql> select 'alter table '||table_name||' move tablespace tablespacename;' from user_tables where tablespace_name = 'CURRENTTABLESPACENAME';

    REPLACE the tablespacename with the new tablespace name that you want to move to.
    REPLACE the currenttablespacename with the existing tablespace that you want to move from.

    you can spool the output to a file if there are 50... and run the spool file.

    sql> spool c:\table_move.sql
    sql> select 'alter table '||table_name||' move tablespace tablespacename;' from user_tables where tablespace_name = 'CURRENTTABLESPACENAME';
    sql> spool off;

    also find all indexes associated with the tables.... They ALL will have
    to be rebuilt !!!

    select index_name from user_indexes where table_name in (select
    table_name from user_tables where tablespace_name = 'CURRENTTABLESPACENAME';

    HTH
    Gregg

  7. #7
    Join Date
    Aug 2003
    Posts
    123
    Alan/Greg

    Thank you very much for your suggestion.

    --Jaggu

Posting Permissions

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