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

[Former Member]

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?

[former_member188958]

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.

[Former Member]

Thanks, Bret. Got. Is it possible to find out what object/table/index data stored on the device? Looks like object binding to device through segment, if the segment extend more than one device, how to find out what kind of data stored on each device?

[former_member188958]

Hi Kent.

I get asked that question a lot.  My standard initial response is "Why do you care?" - because part of the benefit of the RDBMS is to generally free you of concerns over exactly where objects is stored, and objects are typically allocated extents on a number of different devices.  It is sort of like asking exactly where on a hard drive in a RAID system a particular byte of a text file is stored on.  There are rare conditions where someone has to be able to answer the question, but it usually doesn't matter to anyone using the file system.

However, as  I do get asked that qestion a lot, I actually can and will tell you just how to find out.  🙂 

See my document Which objects have allocations on a device? - SAP ASE - SCN Wiki

for the specifics.

Cheers,
-bret

[Former Member]

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.