OCP-1Z0-新051-61题版本-1

ddlddx0000

　　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
　　It is a DDL statement and cannot be rolled back  D正确，C错误
　　A TRUNCATE statement also specifies whether space currently allocatedfor 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. Itis a DDL statement and cannot be rolled back. A TRUNCATE statement does not affect any structures associated with the table being truncated (constraintsand 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 cantruncate 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 statementsif 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 froman individual clustered table, use the DELETE statement or drop and re-create the table.
The TRUNCATE statement has several optionsthat control whether space currently allocated for a table or cluster is returned to the containing tablespace after truncation.
These options also apply to any associated indexes. When a table or cluster is truncated, all associated indexes are also truncated. The storage parameters for a truncated table, cluster, or associated indexes are not changedas a result of the truncation.
These TRUNCATE options are:

• 　　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.
  
• 　　DROP ALL STORAGE drops the segment. In addition to the TRUNCATE TABLE statement, DROP ALL STORAGE also applies to the ALTER TABLE TRUNCATE(SUB)PARTITION statement.This option also drops any dependent object segments associated with the partition being truncated.
  　　DROP ALL STORAGE is 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;
  
• 　　REUSE STORAGE specifies that all space currently allocated for the table or cluster remains allocated to it. For example, the following statement truncates the emp_dept cluster, leaving all extents previously allocated for the clusteravailable for subsequent inserts and deletes:
  TRUNCATE CLUSTER emp_dept REUSE STORAGE;
  

         版权声明：本文为博主原创文章，未经博主允许不得转载。