查看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 .050403226ADMIN_TBS2 8 6400 .125EXAMPLE 8728 4194302 .208091835SYSAUX 29720 4194302 .708580355SYSTEM 60680 4194302 1.44672463TEMP 0 4194302 0TEST 29184 4194302 .695801113UNDOTBS1 176 4194302 .004196169USERS 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 7168SQL> 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.8207169SQL> select count(*)*6/1024/1024 from test03;COUNT(*)*6/1024/1024-------------------- 24收缩表空间shrink spaceSQL> 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个数据块！！！moveSQL> 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(*)---------- 262144SQL> 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 512SQL> 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 0SQL> 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.5SQL> select count(*)*6/1024/1024 from test;COUNT(*)*6/1024/1024-------------------- 1.5SQL> 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 512SQL> 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 0SQL> 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 .050403226ADMIN_TBS2 8 6400 .125EXAMPLE 8728 4194302 .208091835SYSAUX 29984 4194302 .714874608SYSTEM 60680 4194302 1.44672463TEMP 128 4194302 .003051759TEST 512 4194302 .012207037UNDOTBS1 81656 4194302 1.94683168USERS 400 4194302 .009536748SQL> 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 8SQL> 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 0SQL> 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 .050403226ADMIN_TBS2 8 6400 .125EXAMPLE 8728 4194302 .208091835SYSAUX 29984 4194302 .714874608SYSTEM 60680 4194302 1.44672463TEMP 128 4194302 .003051759TEST 8 4194302 .000190735UNDOTBS1 8232 4194302 .196266268USERS 400 4194302 .009536748SQL> alter table test nocompress;Table altered.
转载于:https://www.cnblogs.com/iyoume2008/p/4690685.html
