OCP-1Z0-新051-61题版本-1
QUESTION NO: 1
Evaluate the SQL statement:
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: A,D,F
答案解析：
参考：http://docs.oracle.com/cd/E11882_01/server.112/e25494/general.htm#ADMIN11534
DROP ANY TABLE system privilege can truncate a table or cluster in any schema. F正确。
TRUNCATE statement.
For example, the following statement truncates the emp table:
TRUNCATE statement provides a fast, efficient method for deleting all rows from a table or cluster. A TRUNCATE statement does not generate any undo information and it commits immediately. TRUNCATE statement also specifies whether space currently allocated for the table is returned to the containing tablespace after truncation.
DROP ANY TABLE system privilege can truncate a table or cluster in any schema.
TRUNCATE statement deletes rows from a table, triggers associated with the table are not fired. Also, a TRUNCATE statement does not generate any audit information corresponding to DELETE statements
if auditing is enabled. Instead, a single audit record is generated for the TRUNCATE statement being issued.
DELETE statement or drop and re-create the table.
TRUNCATE statement has several options that control whether space currently allocated for a table or cluster is returned to the containing tablespace after truncation.
TRUNCATE options are:
-
DROPALLSTORAGEdrops the segment. In addition to theTRUNCATETABLEstatement,DROPALLSTORAGEalso applies to theALTERTABLETRUNCATE(SUB)PARTITIONstatement. This option also drops any dependent object segments associated with the partition being truncated.DROPALLSTORAGEis not supported for clusters.Note:
This functionality is available with Oracle Database 11g release 2 (11.2.0.2). TRUNCATE TABLE emp DROP ALL STORAGE; -
TRUNCATE CLUSTER emp_dept REUSE STORAGE;REUSE STORAGEspecifies that all space currently allocated for the table or cluster remains allocated to it. For example, the following statement truncates theemp_deptcluster, leaving all extents previously allocated for the cluster available for subsequent inserts and deletes:
DROP STORAGE, the default option, 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.