Ø USER_SEGMENTS

SQL> create table xs(id number,name char(10)) tablespace tools

2 ;

Table created

SQL> select * from user_segments where segment_name = 'XS';

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS BUFFER_POOL

-------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- -----------

XS TABLE TOOLS 65536 8 1 65536 1 2147483645 DEFAULT

SQL> select bytes/1024/1024 M from user_segments where segment_name = 'XS';

M

----------

0.0625

当初始建了个表后，从user_segments查询该表的大小，发现只是初始分配一个EXTENTS，一个扩展包含8个BLOCKS，一个块是8K，8*8*1024=65536，即BYTES的值。

又运行以下插入命令，现在有1045行数据了，当大小还是65536字节。没有扩展。

SQL> insert into xs select object_id,status from user_objects;

1045 rows inserted

SQL> select bytes/1024/1024 M from user_segments where segment_name = 'XS';

M

----------

0.0625

然后再插入很多数据：

SQL> insert into xs select object_id,status from user_objects;

1045 rows inserted

SQL> insert into xs select object_id,status from user_objects;

1045 rows inserted

SQL> insert into xs select object_id,status from user_objects;

1045 rows inserted

SQL> insert into xs select object_id,status from user_objects;

1045 rows inserted

。。。

再来查询大小：

SQL> select count(*) from xs

2 ;

COUNT(*)

----------

10450

SQL> select bytes/1024/1024 M from user_segments where segment_name = 'XS';

M

----------

0.3125

SQL> select * from user_segments where segment_name = 'XS';

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS BUFFER_POOL

-------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- -----------

XS TABLE TOOLS 327680 40 5 65536 1 2147483645 DEFAULT

发现现在有5个扩展EXTENTS了, 可见查询USER_SEGMENTS，查到的段的大小是个近似值，只是分配了多少个扩展EXTENTS，然后计算扩展EXTENTS的大小。

然后再来用delete删除数据：

SQL> delete from xs where rownum<7000;

6999 rows deleted

SQL> select count(*) from xs;

COUNT(*)

----------

3451

SQL> select * from user_segments where segment_name = 'XS';

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS BUFFER_POOL

-------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- -----------

XS TABLE TOOLS 327680 40 5 65536 1 2147483645 DEFAULT

由以上可以发现数据少很多了，但扩展还是不变，大小不变。即高水位的概念。

Ø user_ind_columns

SQL> desc user_ind_columns

Name Type Nullable Default Comments

--------------- -------------- -------- ------- ---------------------------------------------------------------

INDEX_NAME VARCHAR2(30) Y Index name

TABLE_NAME VARCHAR2(30) Y Table or cluster name

COLUMN_NAME VARCHAR2(4000) Y Column name or attribute of object column

COLUMN_POSITION NUMBER Y Position of column or attribute within index

COLUMN_LENGTH NUMBER Y Maximum length of the column or attribute, in bytes

CHAR_LENGTH NUMBER Y Maximum length of the column or attribute, in characters

DESCEND VARCHAR2(4) Y DESC if this column is sorted descending on disk, otherwise ASC

Ø v$bh和user_objects

select a.status,b.object_name from v$bh a, user_objects b where a.objd=b.object_id and b.OBJECT_TYPE='TABLE';

Ø user_free_space

查表空间里还有多少空闲空间

SQL> desc user_free_space

Name Type Nullable Default Comments

--------------- ------------ -------- ------- -------------------------------------------------

TABLESPACE_NAME VARCHAR2(30) Y Name of the tablespace containing the extent

FILE_ID NUMBER Y ID number of the file containing the extent

BLOCK_ID NUMBER Y Starting block number of the extent

BYTES NUMBER Y Size of the extent in bytes

BLOCKS NUMBER Y Size of the extent in ORACLE blocks

RELATIVE_FNO NUMBER Y Relative number of the file containing the extent