Truncating a table -Definition - Database

Q.  What is truncating a table?
- Published on 25 Aug 15

a. Data alone deleted, Performs an automatic commit
b. Data alone deleted, Doesn’t perform automatic commit
c. Table structure + Data are deleted
d. Table structure alone deleted, Performs an automatic commit

ANSWER: Data alone deleted, Performs an automatic commit
 

    Discussion

  • Nirja Shah   -Posted on 04 Nov 15
    - Use the TRUNCATE TABLE statement to remove all rows from a table. By default, Oracle Database also performs the following tasks:

    1. Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter

    2. Sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process

    - Removing rows with the TRUNCATE TABLE statement can be more efficient than dropping and re-creating a table.

    - Dropping and re-creating a table invalidates dependent objects of the table, requires you to regrant object privileges on the table, and requires you to re-create the indexes, integrity constraints, and triggers on the table and respecify its storage parameters.

    - Truncating has none of these effects.

    - Removing rows with the TRUNCATE TABLE statement can be faster than removing all rows with the DELETE statement, especially if the table has numerous triggers, indexes, and other dependencies.

Post your comment / Share knowledge


Enter the code shown above:

(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)