New truncate table feature (DROP ALL STORAGE) in Oracle 11gR2 11.2.0.2
New truncate table feature (DROP ALL STORAGE) in Oracle 11gR2 11.2.0.2
The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is truncated to the nearest day. TRUNC removes the decimal part for both negatives and positives. Therefore, TRUNC(-x) is equal to -TRUNC(x). ROUND returns the closest value and when both upper and lower values are equidistant, it rounds up to the higher positive value or the lower negative value, i.e. The purpose of the Oracle TRUNC function is to truncate either a number or a date value. The TRUNC function works on two different data types – number or date. This means you can provide it with either of these two data types, and the TRUNC function will perform slightly differently and give you a result for that data type.
Oracle Truncate Table Privilege
When I run the following in an Oracle shell it works fine. Truncate table tablename But when I try to put it in a stored procedure. CREATE OR REPLACE PROCEDURE test IS BEGIN truncate table tablename; END test; / it fails with. ERROR line 3, col 14, endingline 3, endingcol 18, Found 'table', Expecting: @ ROW or (.
Oracle Truncate String
Starting with Oracle 11gR2 11.2.0.2 there is a new clause added to the truncate table SQL statement allowing to release the space allocated even for extents corresponding to the minextents storage clause. Prior to 11.2.0.2 truncate table statement with its different flavor of clauses was used to delete all the rows from the table and possibly release the space but up to the space allocated with minextents. Now in 11.2.0.2 a new clause DROP ALL STORAGE for the truncate table SQL statement allows releasing even the space allocated to store the extents specified by minextents storage clause. However this clause is a subject to the same restrictions that apply to the deferred segment creation. More on the deferred segment creation can be found in a previous post here.
Oracle Truncate Table Drop Storage
Let’s create a table and see the new truncate table SQL statement feature. Notice in the example below that due to the deferred segment creation an extent is allocated only after the insert as the parameter deferred_segment_creation is set to true. It is only until when DROP ALL STORAGE is used than all extents are de-allocated and the space is released.