咨询一道ocp的考题，关于truncate table

显示全部楼层 · 2014-5-9 08:24:37

TRUNCATE TABLE DEPT;
Which three are true about the SQL statement? (Choose three.)
A. It releases the storage space used by the table.
B. It does not release the storage space used by the table.
C. You can roll back the deletion of rows after the statement executes.
D. You can NOT roll back the deletion of rows after the statement executes.
E. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement
executes will display an error.
F. You must be the owner of the table or have DELETE ANY TABLE system privileges to
truncate the DEPT table
Answer: B, D, F
Explanation : does not release the storage space used by the table,
Can not rollback the deletion of rows after the statement executes,
You must be the owner of the table or have DELETE ANY TABLE
system privilege to truncate the DEPT table.
Incorrect answer :
A is not true
C is not true
E is not true
truncate table不是可以release空间的吗？为什么选b啊？应该是选a？
请各位dx指点一下，谢谢！

千问 · 2014-5-9 08:24:37

truncate table 有两种方式一种是释放空间，一种可以reuse。

千问 · 2014-5-9 08:24:37

TRUNCATE TABLE DEPT;
直接写truncate默认是那种方式呢？reuse吗？
这两种释放空间和reuse的有什么本质上的不同吗？一直都没明白这两个有什么不一样的
平常用的时候只是直接truncate table，只是在用toad截表的时候才看见有reuse的选项，但一致不知道是干啥的
请dx指点一下？？谢谢！

千问 · 2014-5-9 08:24:37

加storage reuse就不释放空间。
truncate table应该使hwm降低，而加上storage reuse则是不改变hwm。
请大家指教。
[ 本帖最后由 kdkd 于 2008-4-6 18:15 编辑 ]

千问 · 2014-5-9 08:24:37

学习中

千问 · 2014-5-9 08:24:37

貌似指定了storage后不能直接释放空间
只能reuse
最近在生产上truncate掉很多表后发现空间仍有显示空间占用
但事实是已经释放了，随便create表后可以正常使用这些空间的

千问 · 2014-5-9 08:24:37

Answer: A, D, F

千问 · 2014-5-9 08:24:37

版主解释一下，我也认为应该选A的，truncate不是释放了存储空间的吗

千问 · 2014-5-9 08:24:37

默认不加storage reuse，则是释放空间的。所以要选A

千问 · 2014-5-9 08:24:37

10g.
The REUSE STORAGE or DROP STORAGE options of the TRUNCATE statement control whether space currently allocated for a table or cluster is returned to the containing tablespace after truncation. The default option, DROP STORAGE, reduces the number of extents allocated to the resulting table to the original setting for MINEXTENTS. Freed extents are then returned to the system and can be used by other objects.
[ 本帖最后由 ritybaby 于 2008-5-1 17:41 编辑 ]