Oracle 11.2.0.4 12.1 and 12.2.
You recently responded to a thread about reclaiming space.and suggested moving an object so it will not be at the end of a data file.
https://asktom.oracle.com/pls/apex/f?p=100:11:33323285896587::NO::: Is there a query to identify where in the data file an object is located and what objects can fit in open gaps.
An example would be very helpful.
Something like this perhaps
SQL> SELECT owner,
2 segment_name,
3 file_id,
4 block_id,
5 trunc(block_id*bs/1024)||'k' starting_pos,
6 case
7 when blocks*bs < 1024*1024 then trunc(blocks*bs/1024)||'k'
8 else trunc(blocks*bs/1024/1024)||'m'
9 end chunk_size
10 FROM dba_extents,
11 ( select block_size bs from dba_tablespaces where tablespace_name = 'USERS')
12 WHERE tablespace_name = 'USERS'
13 UNION ALL
14 SELECT '<free>',
15 ' ',
16 file_id,
17 block_id,
18 trunc(block_id*bs/1024)||'k' starting_pos,
19 case
20 when blocks*bs < 1024*1024 then trunc(blocks*bs/1024)||'k'
21 else trunc(blocks*bs/1024/1024)||'m'
22 end chunk_size
23 FROM dba_free_space,
24 ( select block_size bs from dba_tablespaces where tablespace_name = 'USERS')
25 WHERE tablespace_name = 'USERS'
26 ORDER BY file_id, block_id;
OWNER SEGMENT_NAME FILE_ID STARTING_POS CHUNK_SIZE
------------------------------ ------------------------------ ---------- ------------ ------------
SCOTT EMP 7 1024k 64k
SCOTT DEPT 7 1088k 64k
SCOTT SALGRADE 7 1152k 64k
SCOTT EMP_PK 7 1216k 64k
SCOTT DEPT_PK 7 1280k 64k
<free> 7 1344k 64k
MCDONAC PRODUCT 7 1408k 64k
MCDONAC EXT 7 1472k 64k
MCDONAC CUSTOMER 7 1536k 64k
<free> 7 1600k 64k
MCDONAC EXTERNAL_EMP 7 1664k 64k
MCDONAC T 7 1728k 64k
...
...