Skip to Content
author's profile photo Former Member
Former Member

how to reuse on device space with overwrite data on it?

Suppose I have a database mydb with some named segments created.

the segment extended many times for space issue. One device allocated for different segment.

Then finally this device space is over, If check the freepages on this fragment on this device, I get something like:

dbname devname segname free_pgs mydb mydevice default 0 mydb mydevice default 0 mydb mydevice indexes 0 mydb mydevice indexes 0 mydb mydevice myseg1 0 mydb mydevice myseg1 0 mydb mydevice myseg2 0 mydb mydevice myseg2 0 mydb mydevice system 0 mydb mydevice system 0

but when I check the segment space, for example, for myseg1, it is said there are many space(like 90%) is for unused or other.

So if the device is used before for this data, then removed from a segment. Then put it back, is it possible to reuse this device? like something data has been written to the device, then removed it, then put it back, then treat it as empty space to override data on this device?

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Posted on May 13, 2014 at 09:21 PM

    Removing a segment from a device has no effect on the data on the device. Extents that are allocated to objects on the segment remain allocated to those objects. The segment only affects which device(s) future allocations to the objects placed o the segment go. To actually free space on the device, you have to delete or truncate data from those tables, or cause the data to move elsewhere (once the segment is dropped from the device, rebuilding the clustered index on an APL table or running REORG REBUILD on a DOL table will cause all the data except for text/image/log to be moved to the devices that are currently part of the segment.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Bret Halford

      Thanks, Bret. I will check your doc carefully. I ask this question is because of following I don't understand:

      1. in mydb, there is a default segment system. it across 2 devices: the info from sp_helpsegment 'system' is:

      device size free_pages

      dev1 2000.0MB 491760

      dev2 4000.0MB 264

      but when I check the space with DBArtisan, see the screenshot:

      less than 150M data total. 97% marked as other. but on dev2, only 264 free pages . That's why I'm confused. That's why I want to know what's data stored on dev2. As developer tell me, dev2 should not have much data on it.

      1.JPG (32.0 kB)

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.