Truncate — 10th [truncate]

Introduction to Oracle TRUNCATE TABLE statement

When you want to delete all data from a table, you use the statement without the clause as follows:

1 DELETEFROMtable_name;

For a table with a small number of rows, the statement does a good job. However, when you have a table with a large number of rows, using the statement to remove all data is not efficient.

Oracle introduced the statement that allows you to delete all rows from a big table.

The following illustrates the syntax of the Oracle statement:

1
2
3
4

TRUNCATETABLEschema_name.table_name

MATERIALIZEDVIEWLOG]]
]STORAGE]

By default, to remove all rows from a table, you specify the name of the table that you want to truncate in the clause:

1 TRUNCATETABLEtable_name;

In this case, because we don’t specify the schema name explicitly,  Oracle assumes that we truncate the table from our own schema.

If a table has relationships with other tables via the foreign key constraints, you need to use the clause:

1
2

TRUNCATETABLEtable_name

CASCADE;

In this case, the statement deletes all rows from the , and recursively truncates down the associated tables in the chain.

Note that the statement requires the foreign key constraints defined with the clause to work.

The clause allows you to specify whether a materialized view log defined on the table is to be preserved or purged when the table is truncated. By default, the material view log is preserved.

The clause allows you to choose either drop or reuse storage freed by the truncated rows and associated indexes if any. By default, the storage is dropped.

Note that to truncate a table, it must be in your own schema or you must have the system privilege.

Truncate Table Using Database Roles

With the setup complete, let’s verify what we know, that db_owner works. Execute the following
to make the user a member of db_owner and then go and execute the test harness.

-- Make the user a member of db_owner
-- We know this works
EXEC sp_addrolemember @rolename = 'db_owner', @membername = 'TestUser';
GO 

Next, let’s try db_ddladmin. First we’ll revoke membership from the db_owner
role and then grant membership in the db_ddladmin role.

-- Remove user from db_owner and add to db_ddladmin
-- We know this works, too
EXEC sp_droprolemember @rolename = 'db_owner', @membername = 'TestUser';
GO 

EXEC sp_addrolemember @rolename = 'db_ddladmin', @membername = 'TestUser';
GO 

Try the test harness and you’ll see it works as expected.

RemarksRemarks

Инструкцию DROP TABLE нельзя использовать для удаления таблицы, на которую ссылается ограничение FOREIGN KEY.DROP TABLE cannot be used to drop a table that is referenced by a FOREIGN KEY constraint. Сначала следует удалить ссылающееся ограничение FOREIGN KEY или ссылающуюся таблицу.The referencing FOREIGN KEY constraint or the referencing table must first be dropped. Если и ссылающаяся таблица, и таблица, содержащая первичный ключ, удаляются с помощью одной инструкции DROP TABLE, ссылающаяся таблица должна быть первой в списке.If both the referencing table and the table that holds the primary key are being dropped in the same DROP TABLE statement, the referencing table must be listed first.

Несколько таблиц можно удалить из любой базы данных.Multiple tables can be dropped in any database. Если удаляемая таблица ссылается на первичный ключ другой таблицы, которая также удаляется, ссылающаяся таблица с внешним ключом должна стоять в списке перед таблицей, содержащей указанный первичный ключ.If a table being dropped references the primary key of another table that is also being dropped, the referencing table with the foreign key must be listed before the table holding the primary key that is being referenced.

При удалении таблицы относящиеся к ней правила и значения по умолчанию теряют привязку, а любые связанные с таблицей ограничения или триггеры автоматически удаляются.When a table is dropped, rules or defaults on the table lose their binding, and any constraints or triggers associated with the table are automatically dropped. Если таблица будет создана заново, нужно будет заново привязать все правила и значения по умолчанию, заново создать триггеры и добавить необходимые ограничения.If you re-create a table, you must rebind the appropriate rules and defaults, re-create any triggers, and add all required constraints.

При удалении всех строк в таблице с помощью инструкции DELETE tablename или TRUNCATE TABLE таблица продолжает существовать, пока она не будет удалена.If you delete all rows in a table by using DELETE tablename or use the TRUNCATE TABLE statement, the table exists until it is dropped.

Большие таблицы и индексы, использующие более 128 экстентов, удаляются в два этапа: логически и физически.Large tables and indexes that use more than 128 extents are dropped in two separate phases: logical and physical. На логическом этапе существующие единицы распределения, используемые в таблице, отмечаются для освобождения и остаются заблокированными до фиксации транзакции.In the logical phase, the existing allocation units used by the table are marked for deallocation and locked until the transaction commits. В физической фазе страницы IAM, помеченные для освобождения, физически удаляются пакетами.In the physical phase, the IAM pages marked for deallocation are physically dropped in batches.

При удалении таблицы, которая содержит столбец VARBINARY(MAX) с атрибутом FILESTREAM, не будут удалены никакие данные, которые хранятся в файловой системе.If you drop a table that contains a VARBINARY(MAX) column with the FILESTREAM attribute, any data stored in the file system will not be removed.

Важно!

Инструкции DROP TABLE и CREATE TABLE нельзя выполнять для одной таблицы в одном пакете.DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. В противном случае может произойти непредвиденная ошибка.Otherwise an unexpected error may occur.

RemarksRemarks

Конкретные изменения, вносимые в текущее значение идентификатора, зависят от определений параметров.The specific corrections made to the current identity value depend on the parameter specifications.

команда DBCC CHECKIDENTDBCC CHECKIDENT command Изменение текущего значения идентификатора или идентификаторовIdentity correction or corrections made
DBCC CHECKIDENT (table_name, NORESEED)DBCC CHECKIDENT ( table_name, NORESEED ) Текущее значение идентификатора не сбрасывается.Current identity value is not reset. Инструкция DBCC CHECKIDENT возвращает текущее значение идентификатора и текущее максимальное значение столбца идентификаторов.DBCC CHECKIDENT returns the current identity value and the current maximum value of the identity column. Если эти два значения не равны друг другу, необходимо сбросить значение идентификатора, чтобы избежать потенциальных ошибок или пропусков в последовательности значений.If the two values are not the same, you should reset the identity value to avoid potential errors or gaps in the sequence of values.
DBCC CHECKIDENT (table_name)DBCC CHECKIDENT ( table_name )или диспетчер конфигурации службorDBCC CHECKIDENT (table_name, RESEED)DBCC CHECKIDENT ( table_name, RESEED ) Если текущее значение идентификатора таблицы меньше, чем максимальное значение из содержащихся в столбце, оно устанавливается в максимальное значение в столбце идентификаторов.If the current identity value for a table is less than the maximum identity value stored in the identity column, it is reset using the maximum value in the identity column. См. раздел «Исключения» далее.See the ‘Exceptions’ section that follows.
DBCC CHECKIDENT (table_name, RESEED, new_reseed_value)DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value ) В качестве текущего значения идентификатора задается new_reseed_value.Current identity value is set to the new_reseed_value. Если со времени создания таблицы в нее не вставлялись строки или все строки были удалены с помощью инструкции TRUNCATE TABL, то первая строка, вставляемая после запуска инструкции DBCC CHECKIDENT, будет использовать значение new_reseed_value в качестве идентификатора.If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Если в таблице есть строки или если все строки были удалены с помощью инструкции DELETE, следующая строка вставляется со значением new_reseed_value и текущим шагом приращения.If rows are present in the table, or if all rows have been removed by using the DELETE statement, the next row inserted uses new_reseed_value + the current increment value. Если транзакция вставляет строку и впоследствии подверглась откату, то следующая вставленная строка использует new_reseed_value + текущий шаг значение так, как если бы строка была удалена.If a transaction inserts a row and is later rolled back, the next row inserted uses new_reseed_value + the current increment value as if the row had been deleted. Если таблица не пустая, установка значения идентификатора меньше, чем максимальное значение столбца идентификаторов может привести к одному из следующих условий.If the table is not empty, setting the identity value to a number less than the maximum value in the identity column can result in one of the following conditions:— Если в столбце идентификаторов существуют ограничения PRIMARY KEY или UNIQUE, при выполнении последующих операций вставки в таблицу будет сформировано сообщение об ошибке 2627, потому что созданное значение идентификатора будет конфликтовать с существующими значениями.-If a PRIMARY KEY or UNIQUE constraint exists on the identity column, error message 2627 will be generated on later insert operations into the table because the generated identity value will conflict with existing values.— Если ограничения PRIMARY KEY или UNIQUE отсутствуют, последующие операции вставки приведут к дублированию значений идентификаторов.-If a PRIMARY KEY or UNIQUE constraint does not exist, later insert operations will result in duplicate identity values.

RemarksRemarks

Инструкция TRUNCATE TABLE обладает следующими преимуществами по сравнению с инструкцией DELETE:Compared to the DELETE statement, TRUNCATE TABLE has the following advantages:

  • Используется меньший объем журнала транзакций.Less transaction log space is used.

    Инструкция DELETE производит удаление по одной строке и заносит в журнал транзакций запись для каждой удаляемой строки.The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. Инструкция TRUNCATE TABLE удаляет данные, освобождая страницы данных, используемые для хранения данных таблиц, и в журнал транзакций записывает только данные об освобождении страниц.TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

  • Обычно используется меньшее количество блокировок.Fewer locks are typically used.

    Если инструкция DELETE выполняется с блокировкой строк, для удаления блокируется каждая строка таблицы.When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. Инструкция TRUNCATE TABLE всегда блокирует таблицу (включая блокировку схемы (SCH-M)) и страницу, но не каждую строку.TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page but not each row.

  • В таблице остается нулевое количество страниц, без исключений.Without exception, zero pages are left in the table.

    После выполнения инструкции DELETE в таблице могут все еще оставаться пустые страницы.After a DELETE statement is executed, the table can still contain empty pages. Например, чтобы освободить пустые страницы в куче, необходима, как минимум, монопольная блокировка таблицы (LCK_M_X).For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. Если операция удаления не использует блокировку таблицы, таблица (куча) будет содержать множество пустых страниц.If the delete operation does not use a table lock, the table (heap) will contain many empty pages. В индексах после операции удаления могут оказаться пустые страницы, хотя эти страницы будут быстро освобождены процессом фоновой очистки.For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.

Инструкция TRUNCATE TABLE удаляет все строки таблицы, но структура таблицы и ее столбцы, ограничения, индексы и т. п. сохраняются.TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. Чтобы удалить не только данные таблицы, но и ее определение, следует использовать инструкцию DROP TABLE.To remove the table definition in addition to its data, use the DROP TABLE statement.

Если таблица содержит столбец идентификаторов, счетчик этого столбца сбрасывается до начального значения, определенного для этого столбца.If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. Если начальное значение не задано, используется значение по умолчанию, равное 1.If no seed was defined, the default value 1 is used. Чтобы сохранить столбец идентификаторов, используйте инструкцию DELETE.To retain the identity counter, use DELETE instead.

SQL References

SQL Keywords
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE

MySQL Functions
String Functions
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER

Numeric Functions
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE

Date Functions
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK

Advanced Functions
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION

SQL Server Functions
String Functions
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER

Numeric Functions
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN

Date Functions
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR

Advanced Functions
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME

MS Access Functions
String Functions
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase

Numeric Functions
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val

Date Functions
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year

Other Functions
CurrentUser
Environ
IsDate
IsNull
IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

Description

empties a table completely. It requires the privilege. See .

can also be specified in the form . (see Identifier Qualifiers).

Logically, is equivalent to a statement that deletes all rows, but there are practical differences under some circumstances.

will fail for an InnoDB table if any FOREIGN KEY constraints from other tables reference the table, returning the error:

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

Foreign Key constraints between columns in the same table are permitted.

For an InnoDB table, if there are no constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one. The AUTO_INCREMENT counter is reset by , regardless of whether there is a constraint.

The count of rows affected by is accurate only
when it is mapped to a statement.

For other storage engines, differs from
in the following ways:

  • Truncate operations drop and re-create the table, which is much
    faster than deleting rows one by one, particularly for large tables.
  • Truncate operations cause an implicit commit.
  • Truncation operations cannot be performed if the session holds an
    active table lock.
  • Truncation operations do not return a meaningful value for the number
    of deleted rows. The usual result is «0 rows affected,» which should
    be interpreted as «no information.»
  • As long as the table format file is valid, the
    table can be re-created as an empty table
    with , even if the data or index files have become
    corrupted.
  • The table handler does not remember the last
    used value, but starts counting
    from the beginning. This is true even for MyISAM and InnoDB, which normally
    do not reuse sequence values.
  • When used with partitioned tables, preserves
    the partitioning; that is, the data and index files are dropped and
    re-created, while the partition definitions (.par) file is
    unaffected.
  • Since truncation of a table does not make any use of ,
    the statement does not invoke triggers.
  • will only reset the values in the Performance Schema summary tables to zero or null, and will not remove the rows.

For the purposes of binary logging and replication, is treated as followed by (DDL rather than DML).

does not work on views. Currently, drops all historical records from a system-versioned table.

MariaDB starting with 10.3.0

WAIT/NOWAIT

Set the lock wait timeout. See WAIT and NOWAIT.

Oracle-mode

Oracle-mode from MariaDB 10.3 permits the optional keywords REUSE STORAGE or DROP STORAGE to be used.

TRUNCATE  tbl_name 

These have no effect on the operation.

Performance

is faster than DELETE, because it drops and re-creates a table.

With XtraDB/InnoDB, is slower if is set (the default since MariaDB 5.5). This is because unlinks the underlying tablespace file, which can be an expensive operation. See MDEV-8069 for more details.

The performance issues with can be exacerbated in cases where the InnoDB buffer pool is very large and is set. In that case, using DROP TABLE followed by CREATE TABLE instead of may perform better. Setting (it defaults to ON before MariaDB 10.5) can also help. In MariaDB 10.2.19 and later, this performance can also be improved by setting . See MDEV-9459 for more details.

Setting can also improve performance in general. See MDEV-16796 for more details.

АргументыArguments

database_namedatabase_nameИмя базы данных.Is the name of the database.

schema_nameschema_nameИмя схемы, которой принадлежит таблица.Is the name of the schema to which the table belongs.

table_nametable_nameИмя таблицы, которая должна быть усечена, или таблицы, из которой удаляются все строки.Is the name of the table to truncate or from which all rows are removed. table_name должно быть литералом.table_name must be a literal. table_name не может быть функцией OBJECT_ID() или переменной.table_name cannot be the OBJECT_ID() function or a variable.

WITH ( PARTITIONS ( { partition_number_expression> | range> } ) )WITH ( PARTITIONS ( { partition_number_expression> | range> } ) )Применимо к: SQL ServerSQL Server (с SQL Server 2016 (13.x);SQL Server 2016 (13.x) до текущей версии)Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x);SQL Server 2016 (13.x) through current version)

Указывает секции для усечения или секции, из которых удаляются все строки.Specifies the partitions to truncate or from which all rows are removed. Если таблица не секционирована, аргумент WITH PARTITIONS приведет к ошибке.If the table is not partitioned, the WITH PARTITIONS argument will generate an error. Если предложение WITH PARTITIONS не указано, будет усечена вся таблица.If the WITH PARTITIONS clause is not provided, the entire table will be truncated.

можно указать одним из следующих способов: can be specified in the following ways:

  • Указав номер секции, например Provide the number of a partition, for example:

  • Указав номера нескольких секций, разделив их запятыми, например Provide the partition numbers for several individual partitions separated by commas, for example:

  • Указав диапазоны секций и отдельные секции, например Provide both ranges and individual partitions, for example:

  • можно указать номерами секций, разделенными ключевым словом TO, например can be specified as partition numbers separated by the word TO, for example:

Для усечения секционированной таблицы таблицы и индексы должны быть выровнены (секционированы одной функцией секционирования).To truncate a partitioned table, the table and indexes must be aligned (partitioned on the same partition function).

EjemplosExamples

A.A. Truncar una tablaTruncate a Table

En el siguiente ejemplo se quitan todos los datos de la tabla .The following example removes all data from the table. Se incluyen instrucciones antes y después de la instrucción para comparar los resultados. statements are included before and after the statement to compare results.

B.B. Truncar la tabla de particionesTruncate Table Partitions

Se aplica a: SQL ServerSQL Server (desde SQL Server 2016 (13.x)SQL Server 2016 (13.x) hasta la versión actual)Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version)

En el ejemplo siguiente se trunca las particiones especificadas de una tabla con particiones.The following example truncates specified partitions of a partitioned table. La sintaxis de provoca que los números de partición 2, 4, 6, 7 y 8 se trunquen.The syntax causes partition numbers 2, 4, 6, 7, and 8 to be truncated.

Oracle TRUNCATE TABLE examples

Let’s look at some examples of using the statement.

A) Oracle simple example

The following statement creates a table named and copies data from the table in the sample database:

1
2
3
4
5
6

CREATETABLEcustomers_copy

AS
SELECT

*
FROM

customers;

To delete all rows from the table you use the following statement:

1 TRUNCATETABLEcustomers_copy;

B) Oracle example

First, let’s create and tables for the demonstration:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

CREATETABLEquotations(

quotation_noNUMERICGENERATEDBYDEFAULTASIDENTITY,

customer_idNUMERICNOTNULL,

valid_fromDATENOTNULL,

valid_toDATENOTNULL,

PRIMARYKEY(quotation_no)
);
 

CREATETABLEquotation_items(

quotation_noNUMERIC,

item_noNUMERIC,

product_idNUMERICNOTNULL,

qtyNUMERICNOTNULL,

priceNUMERIC(9,2)NOTNULL,

PRIMARYKEY(quotation_no,item_no),

CONSTRAINTfk_quotationFOREIGNKEY(quotation_no)

REFERENCESquotations

ONDELETECASCADE

);

Next, insert some rows into these two tables:

1
2
3
4
5
6
7
8
9
10
11

INSERTINTOquotations(customer_id,valid_from,valid_to)

VALUES(100,DATE’2017-09-01′,DATE’2017-12-01′);
 

INSERTINTOquotation_items(quotation_no,item_no,product_id,qty,price)

VALUES(1,1,1001,10,90.5);
 

INSERTINTOquotation_items(quotation_no,item_no,product_id,qty,price)

VALUES(1,2,1002,20,200.5);
 

INSERTINTOquotation_items(quotation_no,item_no,product_id,qty,price)

VALUES(1,3,1003,30,150.5);

Then, truncate the  table:

1 TRUNCATETABLEquotations;

The statement failed and Oracle returned the following error:

1 SQLError:ORA-02266:uniqueprimarykeysintablereferencedbyenabledforeignkeys

To fix this, you add the clause to the statement above:

1 TRUNCATETABLEquotationsCASCADE;

This statement deleted data from not only table but also table.

Finally, verify whether the data from both and are deleted or not:

1
2
3
4
5
6
7
8
9

SELECT

*
FROM

quotations;
 
SELECT

*
FROM

quotation_items;

Notice that if we did not specify the for the constraint, the statement above would fail.

In this tutorial, you have learned how to use Oracle statement to delete all data from a table faster and more efficiently.

  • Was this tutorial helpful?

ObservacionesRemarks

En comparación con la instrucción DELETE, TRUNCATE TABLE ofrece las siguientes ventajas:Compared to the DELETE statement, TRUNCATE TABLE has the following advantages:

  • Se utiliza menos espacio del registro de transacciones.Less transaction log space is used.

    La instrucción DELETE quita una a una las filas y graba una entrada en el registro de transacciones por cada fila eliminada.The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE quita los datos al cancelar la asignación de las páginas de datos utilizadas para almacenar los datos de la tabla y solo graba en el registro de transacciones las cancelaciones de asignación de páginas.TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

  • Por regla general, se utilizan menos bloqueos.Fewer locks are typically used.

    Si se ejecuta la instrucción DELETE con un bloqueo de fila, se bloquea cada fila de la tabla para su eliminación.When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE siempre bloquea la tabla (incluido un bloqueo de esquema (SCH-M)) y la página, pero no cada fila.TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page but not each row.

  • Las páginas cero se conservan en la tabla sin excepciones.Without exception, zero pages are left in the table.

    Después de ejecutar una instrucción DELETE, la tabla puede seguir conteniendo páginas vacías.After a DELETE statement is executed, the table can still contain empty pages. Por ejemplo, no se puede cancelar la asignación de las páginas vacías de un montón sin un bloqueo de tabla exclusivo (LCK_M_X) como mínimo.For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. Si en la operación de eliminación no se utiliza un bloqueo de tabla, la tabla (montón) contiene muchas páginas vacías.If the delete operation does not use a table lock, the table (heap) will contain many empty pages. En el caso de los índices, la operación de eliminación puede dejar páginas vacías, aunque la asignación de estas páginas se puede cancelar rápidamente mediante un proceso de limpieza en segundo plano.For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.

TRUNCATE TABLE quita todas las filas de una tabla, pero permanecen la estructura y sus columnas, las restricciones, los índices, etc.TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. Para quitar la definición de tabla además de los datos, utilice la instrucción DROP TABLE.To remove the table definition in addition to its data, use the DROP TABLE statement.

Si la tabla contiene una columna de identidad, el contador para dicha columna se restablece al valor de inicialización definido para ella.If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. Si no se define ningún valor de inicialización, se utiliza el valor predeterminado 1.If no seed was defined, the default value 1 is used. Para conservar el contador de identidad, utilice DELETE.To retain the identity counter, use DELETE instead.

Granting DELETE permissions to Truncate Table

Let’s attempt one final test, one we know should fail. Let’s revoke the ALTER
permission and grant DELETE.

-- What about straight DELETE rights?
REVOKE ALTER ON OBJECT::dbo.TestTable FROM TestUser;
GO 

GRANT DELETE ON OBJECT::dbo.TestTable TO TestUser;
GO 

When we execute the test harness, we get the same error as before, when the
user had no permissions at all. However, since the error indicates the object
might not exist, let’s build a second test harness, this time using DELETE and
see what happens:

-- Truncate table gave an interesting error. What about straight DELETE?
EXECUTE AS USER = 'TestUser';
GO 

DELETE FROM dbo.TestTable;
GO 

REVERT;
GO 

And when we execute this second test harness, we will have success. The table is
there and we have verified what we already knew: DELETE permissions isn’t
sufficient to execute TRUNCATE TABLE.

Next Steps
  • Read up on one technique to

    grant TRUNCATE TABLE through stored procedures.

  • Learn how you can cause

    stored procedures to execute in a different security context.

  • See a method for

    truncating all the tables in a given database.

About the author

K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.
View all my tips

Related Resources

Become a paid author

Пример

В SQL Server очистка таблицы — это быстрый способ очистить записи из таблицы, если вам не нужно беспокоиться об откате. Когда таблица очищается, удаление строк не регистрируется, поэтому откатывание невозможно. Очистка таблицы также намного проще, чем удаление таблицы и ее воссоздание.

Рассмотрим пример использования оператора TRUNCATE TABLE в SQL Server. Например:

Transact-SQL

TRUNCATE TABLE employees;

1 TRUNCATETABLEemployees;

В этом примере очистится таблица, называемая employees, и удалятся все записи из этой таблицы.

Это будет эквивалентно следующему оператору DELETE в SQL Server:

Transact-SQL

DELETE FROM employees;

1 DELETEFROMemployees;

Оба этих утверждения приведут к удалению всех данных из таблицы employees. Основное различие между ними состоит в том, что вы можете откатить оператор DELETE, если вы выберете, но вы не можете отменить TRUNCATE TABLE.

Давайте рассмотрим еще один пример, где мы используем префикс базы данных с именем таблицы. Например:

Transact-SQL

TRUNCATE TABLE totn.contacts;

1 TRUNCATETABLEtotn.contacts;

В этом примере очищается таблица, называемая contacts в базе данных, называемой totn.

Ссылка на основную публикацию