1.查看一个表空间，有多少个extents，多少blocks，多少空余空间。
select a.tablespace_name, sumblocks, nvl(usedextents, 0) usedextents,
nvl(usedblocks, 0) usedblocks, freeblocks,
nvl(usedblocks,0) + nvl(freeblocks,0)checkblocks,
sumblocks-nvl(usedblocks, 0)-nvl(freeblocks,0) minblocks
from (select tablespace_name, sum(blocks) sumblocks from dba_data_files
group by tablespace_name ) A,
(select tablespace_name, sum(extents) usedextents, sum(blocks) usedblocks from dba_segments
group by tablespace_name ) B,
(select tablespace_name, sum(blocks) freeblocks from dba_free_space
group by tablespace_name ) C
where a.tablespace_name=b.tablespace_name(+)
12 and a.tablespace_name=c.tablespace_name(+);
nvl(usedblocks, 0) usedblocks, freeblocks,
nvl(usedblocks,0) + nvl(freeblocks,0)checkblocks,
sumblocks-nvl(usedblocks, 0)-nvl(freeblocks,0) minblocks
from (select tablespace_name, sum(blocks) sumblocks from dba_data_files
group by tablespace_name ) A,
(select tablespace_name, sum(extents) usedextents, sum(blocks) usedblocks from dba_segments
group by tablespace_name ) B,
(select tablespace_name, sum(blocks) freeblocks from dba_free_space
group by tablespace_name ) C
where a.tablespace_name=b.tablespace_name(+)
12 and a.tablespace_name=c.tablespace_name(+);
TABLESPACE_NAME SUMBLOCKS USEDEXTENTS USEDBLOCKS FREEBLOCKS CHECKBLOCKS MINBLOCKS
------------------------------ ---------- ----------- ---------- ---------- ----------- ----------
SYSAUX 67840 5128 63576 4136 67712 128
UNDOTBS1 11520 36 2208 9184 11392 128
USERS 960 89 712 168 880 80
SYSTEM 90880 3042 89648 1104 90752 128
EXAMPLE 12800 884 9952 2720 12672 128
UNOTBS_NEW 12800 162 4536 8136 12672 128
6 rows selected.
SQL>
2.示例演示通过rowid得到数据块的相关信息（所在对象，文件，数据块）。
SQL> create table tt as select * from dba_objects where object_id < 10;
Table created.
SQL> select rowid,object_id,object_name from tt;
ROWID OBJECT_ID OBJECT_NAME
------------------ ---------- --------------------------------------------------------------------------------------------------------------------------------
AAASVGAABAAAVUZAAA 2 C_OBJ#
AAASVGAABAAAVUZAAB 3 I_OBJ#
AAASVGAABAAAVUZAAC 4 TAB$
AAASVGAABAAAVUZAAD 5 CLU$
AAASVGAABAAAVUZAAE 6 C_TS#
AAASVGAABAAAVUZAAF 7 I_TS#
AAASVGAABAAAVUZAAG 8 C_FILE#_BLOCK#
AAASVGAABAAAVUZAAH 9 I_FILE#_BLOCK#
8 rows selected.
select
rowid,dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id,
dbms_rowid.rowid_row_number(rowid) row_id
6 from tt;
ROWID OBJECT_ID FILE_ID BLOCK_ID ROW_ID
------------------ ---------- ---------- ---------- ----------
AAASVGAABAAAVUZAAA 75078 1 87321 0
AAASVGAABAAAVUZAAB 75078 1 87321 1
AAASVGAABAAAVUZAAC 75078 1 87321 2
AAASVGAABAAAVUZAAD 75078 1 87321 3
AAASVGAABAAAVUZAAE 75078 1 87321 4
AAASVGAABAAAVUZAAF 75078 1 87321 5
AAASVGAABAAAVUZAAG 75078 1 87321 6
AAASVGAABAAAVUZAAH 75078 1 87321 7
8 rows selected.
SQL> select object_name from dba_objects where object_id=75078;
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
TT
SQL>
3.示例说明consistent read,current read,logical read,phicical read的概念及关系。
SQL> set autotrace traceonly stat
SQL> alter system flush buffer_cache;
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from dba_objects;
72552 rows selected.
Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
7719 consistent gets
1469 physical reads
0 redo size
3472080 bytes sent via SQL*Net to client
53616 bytes received via SQL*Net from client
4838 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72552 rows processed
SQL> select * from dba_objects;
72552 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7715 consistent gets
0 physical reads
0 redo size
3472080 bytes sent via SQL*Net to client
53616 bytes received via SQL*Net from client
4838 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72552 rows processed
SQL>
逻辑读：current read又叫做db block gets，逻辑读=db block gets+consistent read
物理读：从磁盘中读到内存中
4.设计一个例子，演示PCTUSED和PCTFREE对数据操作的影响。
SQL> drop table tt purge;
Table dropped.
SQL> create table tt as select * from sys_objects where rownum < 500;
Table created.
SQL> select pct_free,pct_used,blocks,num_rows,avg_row_len from dba_tables where table_name='TT';
PCT_FREE PCT_USED BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
10 40
SQL> analyze table TT compute statistics;
Table analyzed.
SQL> select pct_free,pct_used,blocks,num_rows,avg_row_len from dba_tables where table_name='TT';
PCT_FREE PCT_USED BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
10 40 3 499 28
SQL> select pct_free,pct_used,blocks,num_rows,avg_row_len from dba_tables where table_name='TT';
PCT_FREE PCT_USED BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
10 40 3 499 28
SQL> INSERT INTO TT select * from TT where rownum<21;
20 rows created.
SQL> commit;
Commit complete.
SQL> analyze table TT compute statistics;
Table analyzed.
SQL> select pct_free,pct_used,blocks,num_rows,avg_row_len from dba_tables where table_name='TT';
PCT_FREE PCT_USED BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
10 40 4 520 28
SQL> CREATE TABLESPACE USER03 DATAFILE 'USER03' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED SEGMENT SPACE MANAGEMENT manual;
Tablespace created.
SQL> create table t03 tablespace USER03 as select * from dba_objects where rownum<500;
Table created.
SQL> select pct_free,pct_used,blocks,num_rows,avg_row_len from dba_tables where table_name='T03';
PCT_FREE PCT_USED BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
10 40
SQL> analyze table T03 compute statistics;
Table analyzed.
SQL> select pct_free,pct_used,blocks,num_rows,avg_row_len from dba_tables where table_name='T03';
PCT_FREE PCT_USED BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
10 40 7 499 87
SQL> INSERT INTO T03 select * from T03 where rownum<81;
80 rows created.
SQL> commit;
Commit complete.
SQL> analyze table T03 compute statistics;
Table analyzed.
SQL> select pct_free,pct_used,blocks,num_rows,avg_row_len from dba_tables where table_name='T03';
PCT_FREE PCT_USED BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
10 40 7 369 89
5.设计一个例子，演示数据块整理（合并）的效果。
SQL> create table t as select object_id,object_name from dba_objects;
Table created.
SQL> insert into t select object_id,object_name from dba_objects;
72552 rows created.
SQL> insert into t select object_id,object_name from dba_objects;
72552 rows created.
SQL> insert into t select object_id,object_name from dba_objects;
72552 rows created.
SQL> insert into t select object_id,object_name from dba_objects;
72552 rows created.
SQL> insert into t select object_id,object_name from dba_objects;
72552 rows created.
SQL> commit;
Commit complete.
SQL> ANALYZE TABLE T COMPUTE STATISTICS;
Table analyzed.
SQL> select blocks from user_tables where table_name='T';
BLOCKS
----------
2062
SQL> alter table t enable row movement;
Table altered.
SQL> alter table t shrink space;
Table altered.
SQL> ANALYZE TABLE T COMPUTE STATISTICS;
Table analyzed.
SQL> select blocks from user_tables where table_name='T';
BLOCKS
----------
1950