QUESTION NO: 1
eva luate 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
It is a DDL statement and cannot be rolled back D正确，C错误
A TRUNCATE statement also specifies whether space currently allocated for the table is returned to the containing tablespace after truncation. A正确。B错误
You can truncate any table or cluster in your own schema. Any user who has the DROP ANY TABLE system privilege can truncate a table or cluster in any schema. F正确。
sys@TESTDB>conn scott/tiger
Connected.
scott@TESTDB>select * from dept_new
2 ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 IT CHINA
60 CLERK BEIJING
6 rows selected.
scott@TESTDB>TRUNCATE TABLE dept_new;
Table truncated.
scott@TESTDB>select * from dept_new;
no rows selected
scott@TESTDB>desc dept_new; desc不会报错。E错误。
Name Null Type
------------------------------------------------------------- - -------- ------------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
scott@TESTDB>rollback; --不能rollback
Rollback complete.
scott@TESTDB>select * from dept_new; --没有返回
no rows selected
Using TRUNCATE
You can delete all rows of the table using the TRUNCATE statement. For example, the following statement truncates the emp table:
TRUNCATE TABLE emp;Using the 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. It is a DDL statement and cannot be rolled back. A TRUNCATE statement does not affect any structures associated with the table being truncated (constraints and triggers) or authorizations. A TRUNCATE statement also specifies whether space currently allocated for the table is returned to the containing tablespace after truncation.
You can truncate any table or cluster in your own schema. Any user who has the DROP ANY TABLE system privilege can truncate a table or cluster in any schema.
Before truncating a table or clustered table containing a parent key, all referencing foreign keys in different tables must be disabled. A self-referential constraint does not have to be disabled.
As a 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.
A hash cluster cannot be truncated, nor can tables within a hash or index cluster be individually truncated. Truncation of an index cluster deletes all rows from all tables in the cluster. If all the rows must be deleted from an individual clustered table, use the DELETE statement or drop and re-create the table.
The TRUNCATE statemen