Last updated on DECEMBER 25, 2019
- Oracle Truncate Date Format
- Oracle Truncate Table
- Trunc Sysdate Year
- Oracle Truncate Statement
- Oracle Truncate Partition
Applies to:
In Oracle, TRUNC(datetime, unit) function allows you to truncate a datetime value to the specified unit (set zero time, set the first day of the month i.e).In SQL Server, you can use various expressions using CONVERT function to get the same result. Oracle: ALTER SESSION SET NLSDATEFORMAT = 'YYYY-MM-DD HH24:MI:SS'; - Get current datetime with the time set to zero SELECT TRUNC (SYSDATE. The syntax for the TRUNC function in Oracle/PLSQL is: TRUNC( number , decimalplaces ) Parameters or Arguments number The number to truncate. Decimalplaces Optional. The number of decimal places to truncate to. This value must be an integer. If this parameter is omitted, the TRUNC function will truncate the number to 0 decimal places.
Oracle Database - Enterprise Edition - Version 11.2.0.2 to 11.2.0.3 [Release 11.2]Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Truncate Date Format
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
Goal
Oracle Truncate Table
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.
In previous release 11gR1 when a truncate was executed Oracle keeps the segment associated with a table. Oracle 11gR1 does provide the DROP STORAGE and REUSE STORAGE clauses but the DROP STORAGE clause only drops extents beyond the minimum extents.
Starting with Oracle 11gR2 (11.2.0.2), a TRUNCATE statement can also specify the DROP ALL STORAGE clause to release the space currently allocated for a table to the containing tablespace.
Solution
To view full details, sign in with your My Oracle Support account. |
Don't have a My Oracle Support account? Click to get started! |
Trunc Sysdate Year
In this Document| Goal |
Oracle Truncate Statement
Oracle Truncate Partition
| Solution |