Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    11

    Unanswered: locally managed tablespace.

    Hi,

    Can we convert an existing tablespace to a locally managed one ? Or is it possible only when we create a new table space ?

    Thanks.

  2. #2
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi,

    Create a new tablespace that is locally managed - you might want to look at automatic segment space management while your at it (are you 9i?).

    Then:

    alter table TABLE_NAME move tablespace NEW_TABLESPACE;

    You can use spooling and || to generate the script to move all necessary tables.

    spool move.sql
    select 'alter table '||owner||'.'||table_name||' move tablespace NEW_TABLESPACE;'
    from dba_tables
    where owner='OWNER_NAME';
    spool off

    Rgs,
    Breen.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    and dont forget to rebuild the indexes (just do a query against dba_indexes for all invalid indexes and get it to write the sql as Breen has shown for moving the tables).

    Alan

Posting Permissions

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