下面脚本可以检测datafile 中创建segment的次序，以利于以后move object 进而resize datafile大小之用

block_id * 8代表 blocksize=8092

SQL>SET ECHO off FEEDBACK off HEADING on NUMWIDTH 10
SQL>DEFINE TABLESPACE=’&1′;
SQL>DEFINE FILE_ID=&2;

———————————
DECLARE
v_prior_file_name VARCHAR2(513);
v_prior_owner VARCHAR2(30);
v_prior_segment VARCHAR2(30);
v_segment_kb NUMBER := 0;
v_start_kb NUMBER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE(RPAD(‘.’, 60, ‘ ‘) || ‘ Start Pos Length’);
DBMS_OUTPUT.PUT_LINE(RPAD(‘Owner and Segment Name’, 60) || ‘ (KB) (KB)’);
DBMS_OUTPUT.PUT_LINE(RPAD(‘-‘, 60, ‘-‘) || ‘ ———– ———‘);
FOR c_extents IN (SELECT e.*, d.file_name
FROM dba_data_files d
, (SELECT owner, segment_name, file_id, (block_id * 8) start_kb
, ((block_id + blocks – 1) * 8) end_kb
FROM dba_extents
WHERE tablespace_name = ‘&TABLESPACE’
AND file_id = &FILE_ID
UNION ALL
SELECT ‘*****Free’, ‘Space*****’, file_id, (block_id * 8) start_kb
, ((block_id + blocks – 1) * 8) end_kb
FROM dba_free_space
WHERE tablespace_name = ‘&TABLESPACE’
AND file_id = &FILE_ID) e
WHERE d.file_id = e.file_id
ORDER BY start_kb DESC)
LOOP
/*
* If the segment is the same as the previous, continue adding it’s space.
*/
IF c_extents.owner = v_prior_owner AND c_extents.segment_name = v_prior_segment
THEN
v_segment_kb := v_segment_kb + (c_extents.end_kb – c_extents.start_kb);
ELSE
/*
* If v_prior_owner IS NULL, then this is the first row read.
*/
IF v_prior_owner IS NOT NULL
THEN
DBMS_OUTPUT.PUT_LINE(RPAD(v_prior_owner || ‘.’ ||v_prior_segment, 60) || ‘ ‘ ||
TO_CHAR(v_start_kb, ‘9999999999’) || ‘ ‘ ||
TO_CHAR(v_segment_kb, ‘99999999’));
END IF;
v_start_kb := c_extents.start_kb;
v_prior_file_name := c_extents.file_name;
v_prior_owner := c_extents.owner;
v_prior_segment := c_extents.segment_name;
v_segment_kb := (c_extents.end_kb – c_extents.start_kb);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(RPAD(v_prior_owner || ‘.’ ||v_prior_segment, 60) || ‘ ‘ ||
TO_CHAR(v_start_kb, ‘9999999999’) || ‘ ‘ ||
TO_CHAR(v_segment_kb, ‘99999999’));
END;
/

example:

SQL> create table ceshi01 tablespace users as select * from dba_objects;

Table created.

SQL> set serveroutput on;
SQL>
SQL> SET ECHO off FEEDBACK off HEADING on NUMWIDTH 10
SQL> DEFINE TABLESPACE=’&1′;
SQL> DEFINE FILE_ID=&2
SQL> DECLARE
2 v_prior_file_name VARCHAR2(513);
3 v_prior_owner VARCHAR2(30);
4 v_prior_segment VARCHAR2(30);
5 v_segment_kb NUMBER := 0;
6 v_start_kb NUMBER := 0;
7 BEGIN
8 DBMS_OUTPUT.PUT_LINE(RPAD(‘.’, 60, ‘ ‘) || ‘ Start Pos Length’);
9 DBMS_OUTPUT.PUT_LINE(RPAD(‘Owner and Segment Name’, 60) || ‘ (KB) (KB)’);
10 DBMS_OUTPUT.PUT_LINE(RPAD(‘-‘, 60, ‘-‘) || ‘ ———– ———‘);
11 FOR c_extents IN (SELECT e.*, d.file_name
12 FROM dba_data_files d
13 , (SELECT owner, segment_name, file_id, (block_id * 16) start_kb
14 , ((block_id + blocks – 1) * 16) end_kb
15 FROM dba_extents
16 WHERE tablespace_name = ‘&TABLESPACE’
17 AND file_id = &FILE_ID
18 UNION ALL
19 SELECT ‘*****Free’, ‘Space*****’, file_id, (block_id * 16) start_kb
20 , ((block_id + blocks – 1) * 16) end_kb
21 FROM dba_free_space
22 WHERE tablespace_name = ‘&TABLESPACE’
23 AND file_id = &FILE_ID) e
24 WHERE d.file_id = e.file_id
25 ORDER BY start_kb DESC)
26 LOOP
27 /*
28 * If the segment is the same as the previous, continue adding it’s space.
29 */
30 IF c_extents.owner = v_prior_owner AND c_extents.segment_name = v_prior_segment
31 THEN
32 v_segment_kb := v_segment_kb + (c_extents.end_kb – c_extents.start_kb);
33 ELSE
34 /*
35 * If v_prior_owner IS NULL, then this is the first row read.
36 */
37 IF v_prior_owner IS NOT NULL
38 THEN
39 DBMS_OUTPUT.PUT_LINE(RPAD(v_prior_owner || ‘.’ ||v_prior_segment, 60) || ‘ ‘ ||
40 TO_CHAR(v_start_kb, ‘9999999999’) || ‘ ‘ ||
41 TO_CHAR(v_segment_kb, ‘99999999’));
42 END IF;
43 v_start_kb := c_extents.start_kb;
44 v_prior_file_name := c_extents.file_name;
45 v_prior_owner := c_extents.owner;
46 v_prior_segment := c_extents.segment_name;
47 v_segment_kb := (c_extents.end_kb – c_extents.start_kb);
48 END IF;
49 END LOOP;
50 DBMS_OUTPUT.PUT_LINE(RPAD(v_prior_owner || ‘.’ ||v_prior_segment, 60) || ‘ ‘ ||
51 TO_CHAR(v_start_kb, ‘9999999999’) || ‘ ‘ ||
52 TO_CHAR(v_segment_kb, ‘99999999’));
53 END;
54 /

. Start Pos Length
Owner and Segment Name (KB) (KB)
———————————————————— ———– ———
*****Free.Space***** 70656 31736
SYS.CESHI03 69632 17152
SYS.CESHI02 52224 17152
SYS.CESHI 34816 17152
SYS.CESHI01 17408 17152

PL/SQL procedure successfully completed.