SQL> create or replace procedure proc_casekey_upstate
2 as
3 casekey char(14);
4 begin
5 for i in 1..10000 loop
6 casekey := 'TMP'||lpad(i,7,0);
7 insert into test_emp values(casekey, 1, sysdate);
8 end loop;
9 commit;
10 end;
11 /
Procedure created.
SQL> exec proc_casekey_upstate; ---执行存储过程插入数据
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(ownname =>'ADMIN',tabname =>'TEST_EMP',cascade=>TRUE); ---收集该表的统计信息
PL/SQL procedure successfully completed.
SQL> select B.SEGMENT_NAME, B.blocks,B.blocks * 8096 / 1024 / 1024, A.BLOCKS,A.blocks * 8096 / 1024 / 1024, A.EMPTY_BLOCKS from user_tables a, USER_SEGMENTS B WHERE TABLE_NAME = 'TEST_EMP' AND A.TABLE_NAME = B.SEGMENT_NAME;
SEGMENT_NAME BLOCKS B.BLOCKS*8096/1024/1024 BLOCKS A.BLOCKS*8096/1024/1024 EMPTY_BLOCKS
--------------------------------------------------------------------------------- ---------- ----------------------- ---------- ----------------------- ------------
TEST_EMP 1315840 10159.5313 46 .355163574 0
SQL> SELECT segment_name, SUM(bytes) / 1024 / 1024 Mbytes FROM dba_segments WHERE PARTITION_NAME = 'PT_1' GROUP BY segment_name;
SEGMENT_NAME MBYTES
--------------------------------------------------------------------------------- ----------
TEST_EMP 10288
BIN$VibHVCPfDL/gU8gCqMDDfw==$0 8
--注意：从上面可以看出，该表的大小是10G，但是该表占用blocks只有46个，显然 有很多空间没有被使用，
⑥：释放未被使用的空间（我对该操作过了10046事件，在实际操作中只需执行：alter table test_emp modify partition pt_1 deallocate unused; ）