Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Best way to housekeep a tablespace

    Hi

    Using Oracle 9i.

    I have some logical volumes that are nearing capacity. Before adding more space, I can see that if some house-keeping is performed on the db, I can gain a few GB of free space.
    Part of this housekeeping is some tablespace mgmt. I have a couple of tablespaces which have 10 datafiles each, each 2GB in size but only 12% of that 2GB is used when I look at the tablespace in 'Toad'. Even when running a query.

    I also ran a script to find out if I could resize the datafiles but there is no free space at the end of the file.

    The next best option I'm thinking of is to Export the user, drop the existing one, re-create it and Import the user back. (Ideally I'd like to create another user first, import the data into this user and then drop the exported user if all is fine but for this I'll have to create the new user with a different name. Are there any downsides to this?

    Also, am I correct in saying I'll have to take the tablespace offline?

    Is there a better, simpler, more risk-free method for this?

    Regards
    Sheraz

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Move it!

    You could re-org the tables in that tablespace by "moving" them:

    1) Create new tablespace.
    2) Move the tables to new tablespace.
    3) Drop old tablespace.


    -- Or --

    Try "moving" the tables within same tablespace.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    Oracle will still use the unused space of the datafiles. Is there a problem with that?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is there a better, simpler, more risk-free method for this?
    Yes, do nothing & allow Oracle to manage tablespace internals.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    Yes, Oracle will still use the space but it's only like 12% in each of the 10 datafiles in 2 years so have been advised to use the free space in these datafiles for other data.

    For reorg, I understand there needs to be enough free space on the disk (logical vol) in my case. But there's not. LVOL is 97% full.

    I have enough free space on another lvol of abot the same size which is only 50% full.

    If I create a new tablespace, how will I move the tables? (I take it moving datafiles is different?)

    And I take it after I move the tables, I won't have to update anything else as all links to those tables will automatically be updated?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If Locally Managed Tablespace, then Oracle handles all these details.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Move it baby...

    To move tables:

    ALTER TABLE "SCOTT"."EMP" MOVE TABLESPACE NEW_TS_DAT1;

    To move indexes:

    ALTER INDEX "SCOTT"."EMP_IX0" REBUILD TABLESPACE NEW_TS_IDX1;

    To shrink:

    ALTER TABLE "SCOTT"."EMP" ENABLE ROW MOVEMENT;
    ALTER TABLE "SCOTT"."EMP" SHRINK SPACE;


    PS: If you omit the TABLESPACE clause it will reorg in the same tablespace.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Aug 2008
    Posts
    464
    Thanks for the syntax.

    Will

    ALTER TABLE "SCOTT"."EMP" MOVE TABLESPACE NEW_TS_DAT1;

    work even if the new tablespace is on a different logical volume?

    Also is the whole procedure to move tables first and then rebuild indexes with

    ALTER INDEX "SCOTT"."EMP_IX0" REBUILD TABLESPACE NEW_TS_IDX1;

    The tablespaces are locally managed here.

    Can I shrink tables in 9i?

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Yes

    Yes to all...


    PS: Ooops, not sure the "shrink" syntax will work in 9i.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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