1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

DB segments that resides in the end of datafile - 9i

Discussion in 'Server Administration and Options' started by Mirano, Feb 20, 2009.

  1. Mirano

    Mirano Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    I'm trying to find out segments that resides in the end of the datafiles. I would believe that there is a way how to identify them based on decode of ROWID but I can't find it anywhere. Could you please help?
     
  2. UnixGuru

    UnixGuru Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    hmmm, i haven't tried it but perhaps you can use the information in user_segments and user tables to find the segments at the end based on the extents.
     
  3. Mirano

    Mirano Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    I got it, I will just verify if it really shows proper informations :)
    Code (Text):

    SELECT *
      FROM (SELECT   owner, segment_name, segment_type, block_id
                FROM dba_extents
               WHERE file_id = (SELECT file_id
                                  FROM dba_data_files
                                 WHERE file_name = &file)
            ORDER BY block_id DESC)
     WHERE ROWNUM <= 10
     
  4. UnixGuru

    UnixGuru Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    hmmm i ran the query, it's a smart idea ordering by block_id and taking the first 10 rows but it will run extremely slow specially for large datafiles. Morever you might want to hardcode or at least get the path in another way because asking for filename in a parameter makes it difficult for a user to type the whole path. I kept typing the dbf file name to no avail, untill i checked out how the filename was stored.
     
  5. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Mirano,
    Is there any special reason why you want those segments ?
     
  6. Mirano

    Mirano Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    yes.. I'm going to do reorganization in DB and then I would like to downsize some datafiles. There was cleanup which deleted a lot of rows so I'm pretty sure of how much space will be saved by reorganization of particular segments but I'm not sure which datafiles can be downsized. Well and when I have these 2 informations... how much I save by reorganization and which datafiles I can downsize I can create plan to achieve best price/effort/effect ratio.
     
  7. Mirano

    Mirano Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Well it will be cool and I will not do it manually. I'm about to create piece of plsql code and will automate it.
     
  8. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Why not recreate the tablspaces if the cleanup resulted in large amount of free space ? That would be lot more simpler.
     
  9. Mirano

    Mirano Active Member

    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    55
    Sorry for the delay... too many projects... but all managed so back to entertainment ..
    It is not possible. I have tested it and it took quite a lot of time. I don't wan't to do this online and don't have downtime. Well so I will do it in couple of phases using top->down approach.