Ø 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