This discussion is archived
1 Reply Latest reply: Jan 10, 2013 10:32 AM by rp0428 RSS

how to find out which objects are at the end of the datafiles

943841 Newbie
Currently Being Moderated
My Scenario is having a tablespace of 65G but the data is 5G and that is at the end of datafile for that we are not able to resize the datafile our though is to find out what are the objects at that end of datafiles and will either alter move <owner.tablename> move or alter index <owner.index> rebuld online.

For that how to find out which objects are at the end of the datafiles.
  • 1. Re: how to find out which objects are at the end of the datafiles
    rp0428 Guru
    Currently Being Moderated
    >
    My Scenario is having a tablespace of 65G but the data is 5G and that is at the end of datafile for that we are not able to resize the datafile our though is to find out what are the objects at that end of datafiles and will either alter move <owner.tablename> move or alter index <owner.index> rebuld online.

    For that how to find out which objects are at the end of the datafiles.
    >
    You may want to copy this and add it to your toolkit

    See 'What's at the End of the File?' in this Tom Kyte article from the Sept 2004 Oracle Magazine
    http://www.oracle.com/technetwork/issue-archive/o54asktom-086284.html

    And this AskTom blog shows you how to generate a script containing ALTER statements toresize your datafiles to the smallest possible. You can 'pick and choose' from the ALTER statements to do what you want.

    Then of course, you can use techniques from this article by Oracle ACE, and noted author, Jonathan Lewis
    http://jonathanlewis.wordpress.com/2010/02/06/shrink-tablespace/

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points