DBA 可以使用以下语法收回一个段中未使用的数据扩展:
ALTER TABLE table_name DEALLOCATE UNUSED;[11:13:05] 1 create table t10 (id number);
[11:13:05] 2 begin
[11:13:05] 3 for i 1..1000000
[11:13:05] 4 loop
[11:13:05] 5 insert into t10 values(i);
[11:13:05] 6 end loop;
[11:13:05] 7 commit;
[11:13:05] 8 end;
[11:13:05] 9 /
[11:17:19]SQL> select count(*) from t10;
[11:17:20]
[11:17:20] COUNT(*)
[11:17:20]----------
[11:17:20] 1000000[11:18:03]SQL> select segment_name,bytes/1024/1024 from user_segments;[11:18:20]SEGMENT_NA BYTES/1024/1024
[11:18:20]T10 13
[11:32:32]SQL> ALTER TABLE t10 DEALLOCATE UNUSED;
[11:32:32]
[11:32:32]Table altered.
[11:32:35]SQL> select segment_name,bytes/1024/1024 from user_segments;
[11:32:35]
[11:32:35]SEGMENT_NA BYTES/1024/1024
[11:32:35]T10 13没有效果啊!!!truncate 一把立马见效。
ALTER TABLE table_name DEALLOCATE UNUSED;[11:13:05] 1 create table t10 (id number);
[11:13:05] 2 begin
[11:13:05] 3 for i 1..1000000
[11:13:05] 4 loop
[11:13:05] 5 insert into t10 values(i);
[11:13:05] 6 end loop;
[11:13:05] 7 commit;
[11:13:05] 8 end;
[11:13:05] 9 /
[11:17:19]SQL> select count(*) from t10;
[11:17:20]
[11:17:20] COUNT(*)
[11:17:20]----------
[11:17:20] 1000000[11:18:03]SQL> select segment_name,bytes/1024/1024 from user_segments;[11:18:20]SEGMENT_NA BYTES/1024/1024
[11:18:20]T10 13
[11:32:32]SQL> ALTER TABLE t10 DEALLOCATE UNUSED;
[11:32:32]
[11:32:32]Table altered.
[11:32:35]SQL> select segment_name,bytes/1024/1024 from user_segments;
[11:32:35]
[11:32:35]SEGMENT_NA BYTES/1024/1024
[11:32:35]T10 13没有效果啊!!!truncate 一把立马见效。
你delete大部分数据之后再看看
SQL> create table t10 (id number);Table created.SQL>
SQL>
SQL> select * from t10;no rows selectedSQL> begin
2 for i in 1..1000000
3 loop
4 insert into t10 values(i);
5 end loop;
6 commit;
7 end;
8 /PL/SQL procedure successfully completed.SQL>
SQL> select count(*) from t10; COUNT(*)
----------
1000000SQL> col segment_name format a10;
SQL> select segment_name,bytes/1024/1024 from user_segments;SEGMENT_NA BYTES/1024/1024
---------- ---------------
T 1
T2 .0625
T1 1
T3 1
BIN$k/ls0o .0625
pS3ezgQAoK
6Cpujg==$0T5 .0625
P 1
T10 138 rows selected.SQL>
SQL>
SQL> delete from t10;1000000 rows deleted.SQL> commit;Commit complete.SQL>
SQL> select count(*) from t10; COUNT(*)
----------
0SQL> select segment_name,bytes/1024/1024 from user_segments;SEGMENT_NA BYTES/1024/1024
---------- ---------------
T 1
T2 .0625
T1 1
T3 1
BIN$k/ls0o .0625
pS3ezgQAoK
6Cpujg==$0T5 .0625
P 1
T10 138 rows selected.SQL>
SQL>
SQL> alter table t10 move;Table altered.SQL>
SQL> select segment_name,bytes/1024/1024 from user_segments;SEGMENT_NA BYTES/1024/1024
---------- ---------------
T 1
T2 .0625
T1 1
T3 1
BIN$k/ls0o .0625
pS3ezgQAoK
6Cpujg==$0T5 .0625
P 1
T10 18 rows selected.
这下全了,我前面delete 的,但是这个命令没有用,用了alter table t10 move; 可以降低highwater.我就奇怪了这个sql怎么不行,我看过联机文档,没有看出什么道道!
--shrink方式呢?
alter table t10 enable row movement;
alter table t10 shrink space;--ALTER TABLE table_name DEALLOCATE UNUSED;我测试下呢
alter table t10 enable row movement;
alter table t10 shrink space;SQL> --alter table t10 move;
SQL>
SQL> select segment_name,bytes/1024/1024 from user_segments;SEGMENT_NA BYTES/1024/1024
---------- ---------------
T 1
T2 .0625
T1 1
T3 1
BIN$k/ls0o .0625
pS3ezgQAoK
6Cpujg==$0T5 .0625
P 1
T10 18 rows selected.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> CREATE TABLE t10(id number);表已创建。SQL> BEGIN
2 FOR i IN 1 .. 10000 LOOP
3 INSERT INTO t10 VALUES (i);
4 END LOOP;
5 COMMIT;
6 END;
7 /PL/SQL 过程已成功完成。SQL> col segment_name format a15;
SQL> col bytes format 99999999;
SQL> SELECT t.segment_name, t.BYTES FROM user_segments t WHERE t.segment_name = upper('t10');SEGMENT_NAME BYTES
--------------- ---------
T10 196608SQL> DELETE FROM t10;已删除10000行。SQL> ALTER TABLE t10 deallocate unused;表已更改。SQL> SELECT t.segment_name, t.BYTES FROM user_segments t WHERE t.segment_name = upper('t10');SEGMENT_NAME BYTES
--------------- ---------
T10 196608SQL> ALTER TABLE t10 allocate extent;表已更改。SQL> SELECT t.segment_name, t.BYTES FROM user_segments t WHERE t.segment_name = upper('t10');SEGMENT_NAME BYTES
--------------- ---------
T10 262144SQL> ALTER TABLE t10 deallocate unused;表已更改。SQL> SELECT t.segment_name, t.BYTES FROM user_segments t WHERE t.segment_name = upper('t10');SEGMENT_NAME BYTES
--------------- ---------
T10 196608SQL>
alter table table_name deallocate unused keep 0这样就会释放hwm上的空间