查看oracle给 对象分配的空间
SQL> select segment_name,segment_type,tablespace_name,bytes/1024/1024 "MB" ,blocks from user_segments where segment_name='TEST';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MB BLOCKS
--------------- ------------------ ------------------------------ ---------- ----------
TEST03 TABLE TEST 28 3584
查看该对象实际占用的空间
SQL> select num_rows,avg_row_len,num_rows*avg_row_len/1024/1024 "MB" from user_tables where table_name='TEST';
NUM_ROWS AVG_ROW_LEN MB
---------- ----------- ----------
2070296 7 13.8207169
根据每行的长度 以及 行数 大致获得 该表的空间
SQL> select count(*)*6/1024/1024 from test;
COUNT(*)*6/1024/1024
--------------------
12
查看表空间的使用量
SQL> select tablespace_name,used_space,tablespace_size,used_percent from dba_tablespace_usage_metrics;
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
ADMIN_TBS 8 15872 .050403226
ADMIN_TBS2 8 6400 .125
EXAMPLE 8728 4194302 .208091835
SYSAUX 29720 4194302 .708580355
SYSTEM 60680 4194302 1.44672463
TEMP 0 4194302 0
TEST 29184 4194302 .695801113
UNDOTBS1 176 4194302 .004196169
USERS 400 4194302 .009536748
第2次操作
SQL> delete from test03;
2097152 rows deleted.
SQL> insert into test03 select * from test03;
2097152 rows created.
SQL> commit;
Commit complete.
SQL> select segment_name,segment_type,tablespace_name,bytes/1024/1024 "MB" ,blocks from user_segments where segment_name='TEST03';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MB BLOCKS
--------------- ------------------ ------------------------------ ---------- ----------
TEST03 TABLE TEST 56 7168
SQL> select num_rows,avg_row_len,num_rows*avg_row_len/1024/1024 "MB" from user_tables where table_name='TEST03';
NUM_ROWS AVG_ROW_LEN MB
---------- ----------- ----------
2070296 7 13.8207169
SQL> select count(*)*6/1024/1024 from test03;
COUNT(*)*6/1024/1024
--------------------
24
收缩表空间
shrink space
SQL> alter table test03 enable row movement;
Table altered.
SQL> alter table test03 shrink space;
Table altered.
SQL> analyze table test03 compute statistics;
Table analyzed.
SQL> select segment_name,segment_type,tablespace_name,bytes/1024/1024 "MB" ,blocks from user_segments where segment_name='TEST03';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MB BLOCKS
--------------- ------------------ ------------------------------ ---------- ----------
TEST03 TABLE TEST 55.1875 7064
仅仅收缩了 104个数据块！！！
move
SQL> alter table test03 move tablespace admin_tbs;
Table altered.
SQL> alter table test03 disable row movement;
Table altered.
SQL> select segment_name,segment_type,tablespace_name,bytes/1024/1024 "MB" ,blocks from user_segments where segment_name='TEST03';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MB BLOCKS
--------------- ------------------ ------------------------------ ---------- ----------
TEST03 TABLE ADMIN_TBS 56 7168
第3次
SQL> insert into test values('oracle');
1 row created.
SQL> /
1 row created.
SQL> insert into test select * from test;
2 rows created.
SQL> /
4 rows created.
SQL> /
8 rows created.
SQL> /
16 rows created.
SQL> /
32 rows created.
SQL> /
64 rows created.
SQL> /
128 rows created.
SQL> /
256 rows created.
SQL> /
512 rows created.
SQL> /
1024 rows created.
SQL> /
2048 rows created.
SQL> /
4096 rows created.
SQL> /
8192 rows created.
SQL> /
16384 rows created.
SQL> /
32768 rows created.
SQL> /
65536 rows created.
SQL> /
131072 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from test;
COUNT(*)
----------
262144
SQL> select segment_name,segment_type,tablespace_name,bytes/1024/1024 "MB" ,blocks from user_segments where segment_name='TEST';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MB BLOCKS
--------------- ------------------ ------------------------------ ---------- ----------
TEST TABLE TEST 4 512
SQL> select num_rows,avg_row_len,num_rows*avg_row_len/1024/1024 "MB" from user_tables where table_name='TEST';
NUM_ROWS AVG_ROW_LEN MB
---------- ----------- ----------
0 0 0
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select num_rows,avg_row_len,num_rows*avg_row_len/1024/1024 "MB" from user_tables where table_name='TEST';
NUM_ROWS AVG_ROW_LEN MB
---------- ----------- ----------
262144 10 2.5
SQL> select count(*)*6/1024/1024 from test;
COUNT(*)*6/1024/1024
--------------------
1.5
SQL> delete from test;
262144 rows deleted.
SQL> commit;
Commit complete.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select segment_name,segment_type,tablespace_name,bytes/1024/1024 "MB" ,blocks from user_segments where segment_name='TEST';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MB BLOCKS
--------------- ------------------ ------------------------------ ---------- ----------
TEST TABLE TEST 4 512
SQL> select num_rows,avg_row_len,num_rows*avg_row_len/1024/1024 "MB" from user_tables where table_name='TEST';
NUM_ROWS AVG_ROW_LEN MB
---------- ----------- ----------
0 0 0
SQL> select tablespace_name,used_space,tablespace_size,used_percent from dba_tablespace_usage_metrics;
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
ADMIN_TBS 8 15872 .050403226
ADMIN_TBS2 8 6400 .125
EXAMPLE 8728 4194302 .208091835
SYSAUX 29984 4194302 .714874608
SYSTEM 60680 4194302 1.44672463
TEMP 128 4194302 .003051759
TEST 512 4194302 .012207037
UNDOTBS1 81656 4194302 1.94683168
USERS 400 4194302 .009536748
SQL> alter table test move compress;
Table altered.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select segment_name,segment_type,tablespace_name,bytes/1024/1024 "MB" ,blocks from user_segments where segment_name='TEST';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MB BLOCKS
--------------- ------------------ ------------------------------ ---------- ----------
TEST TABLE TEST .0625 8
SQL> select num_rows,avg_row_len,num_rows*avg_row_len/1024/1024 "MB" from user_tables where table_name='TEST';
NUM_ROWS AVG_ROW_LEN MB
---------- ----------- ----------
0 0 0
SQL> select tablespace_name,used_space,tablespace_size,used_percent from dba_tablespace_usage_metrics;
TABLESPACE_NAME USED_SPACE TABLESPACE_SIZE USED_PERCENT
------------------------------ ---------- --------------- ------------
ADMIN_TBS 8 15872 .050403226
ADMIN_TBS2 8 6400 .125
EXAMPLE 8728 4194302 .208091835
SYSAUX 29984 4194302 .714874608
SYSTEM 60680 4194302 1.44672463
TEMP 128 4194302 .003051759
TEST 8 4194302 .000190735
UNDOTBS1 8232 4194302 .196266268
USERS 400 4194302 .009536748
SQL> alter table test nocompress;
Table altered.