- 浏览: 246345 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
nodonkey:
貌似还是不行,再等等吧,amfphp要出2.0了
amfphp1.9与php5.3.X版本不兼容 -
live711:
请问amfphp与php5.3.X搭配能用了吗?
amfphp1.9与php5.3.X版本不兼容 -
zhousheng193:
非常感谢!
安装flash player debug版本遇到的一些问题 -
sp42:
谢谢提示,我遇到的也是不能加密,用MAC地址代替之。
DI-624+A路由器韧体升级解决经常掉线的问题(转) -
心似海:
不错,要挖去了,哈哈
深入sql之merge into
TRUNCATE
Caution: You cannot roll back aTRUNCATE
statement. |
Purpose
Use the TRUNCATE
statement to remove all rows from a table or cluster. By default,
Oracle Database also deallocates all space used by the removed rows
except that specified by the MINEXTENTS
storage parameter and 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
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.
See Also:
|
Prerequisites
To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP
ANY
TABLE
system privilege.
Syntax
truncate::=
Description of the illustration truncate.gif
Semantics
Specify the schema and name of the table to be truncated. This table cannot be part of a cluster. If you omit schema
, then Oracle Database assumes the table is in your own cluster.
-
You can truncate index-organized tables and temporary tables. When you truncate a temporary table, only the rows created during the current session are removed.
-
Oracle Database changes the
NEXT
storage parameter oftable
to be the size of the last extent deleted from the segment in the process of truncation. -
Oracle Database also automatically truncates and resets any existing
UNUSABLE
indicators for the following indexes ontable
: range and hash partitions of local indexes and subpartitions of local indexes. -
If
table
is not empty, then the database marksUNUSABLE
all nonpartitioned indexes and all partitions of global partitioned indexes on the table. -
For a domain index, this statement invokes the appropriate truncate routine to truncate the domain index data.
-
If a regular or index-organized table contains LOB columns, then all LOB data and LOB index segments are truncated.
-
If
table
is partitioned, then all partitions or subpartitions, as well as the LOB data and LOB index segments for each partition or subpartition, are truncated.
Note:
When you truncate a table, Oracle Database automatically removes all data in the table's indexes and any materialized view direct-pathINSERT
information held in association with the table. This information is independent of any materialized view log. If this direct-pathINSERT
information is removed, then an incremental refresh of the materialized view may lose data.
-
You cannot individually truncate a table that is part of a cluster. You must either truncate the cluster, delete all rows from the table, or drop and re-create the table.
-
You cannot truncate the parent table of an enabled referential integrity constraint. You must disable the constraint before truncating the table. An exception is that you can truncate the table if the integrity constraint is self-referential.
-
If a domain index is defined on
table
, then neither the index nor any index partitions can be markedIN_PROGRESS
.
MATERIALIZED VIEW LOG Clause
The MATERIALIZED
VIEW
LOG
clause lets you specify whether a materialized view log defined on the
table is to be preserved or purged when the table is truncated. This
clause permits materialized view master tables to be reorganized
through export or import without affecting the ability of primary key
materialized views defined on the master to be fast refreshed. To
support continued fast refresh of primary key materialized views, the
materialized view log must record primary key information.
Note: The keywordSNAPSHOT
is supported in place of MATERIALIZED
VIEW
for backward compatibility. |
PRESERVE
Specify PRESERVE
if any materialized view log should be preserved when the master table is truncated. This is the default.
PURGE
Specify PURGE
if any materialized view log should be purged when the master table is truncated.
See Also: Oracle Database Advanced Replication for more information about materialized view logs and theTRUNCATE
statement |
Specify the schema and name of the cluster to be truncated. You can
truncate only an indexed cluster, not a hash cluster. If you omit schema
, then the database assumes the cluster is in your own schema.
When you truncate a cluster, the database also automatically deletes all data in the indexes of the cluster tables.
STORAGE Clauses
The STORAGE
clauses let you determine what happens to the space freed by the truncated rows. The DROP
STORAGE
clause and REUSE
STORAGE
clause also apply to the space freed by the data deleted from associated indexes.
DROP STORAGE
Specify DROP
STORAGE
to deallocate all space from the deleted rows from the table or cluster except the space allocated by the MINEXTENTS
parameter of the table or cluster. This space can subsequently be used
by other objects in the tablespace. Oracle Database also sets the NEXT
storage parameter to the size of the last extent removed from the segment in the truncation process. This is the default.
REUSE STORAGE
Specify REUSE
STORAGE
to retain the space
from the deleted rows allocated to the table or cluster. Storage values
are not reset to the values when the table or cluster was created. This
space can subsequently be used only by new data in the table or cluster
resulting from insert or update operations. This clause leaves storage
parameters at their current settings.
If you have specified more than one free list for the object you are truncating, then the REUSE
STORAGE
clause also removes any mapping of free lists to instances and resets the high-water mark to the beginning of the first extent.
Examples
Truncating a Table: Example
The following statement removes all rows from a hypothetical copy of the sample table hr.employees
and returns the freed space to the tablespace containing employees
:
TRUNCATE TABLE employees_demo;
The preceding statement also removes all data from all indexes on employees
and returns the freed space to the tablespaces containing them.
Retaining Free Space After Truncate: Example
The following statement removes all rows from all tables in the personnel
cluster, but leaves the freed space allocated to the tables:
TRUNCATE CLUSTER personnel REUSE STORAGE;
The preceding statement also removes all data from all indexes on the tables in the personnel
cluster.
Preserving Materialized View Logs After Truncate: Example
The following statements are examples of truncate statements that preserve materialized view logs:
TRUNCATE TABLE sales_demo PRESERVE MATERIALIZED VIEW LOG; TRUNCATE TABLE orders_demo;
发表评论
-
alter table move 与shrink space的区别
2012-03-06 13:51 2244转自:http://hi.baidu.co ... -
mysqlsla来分析MYSQL的性能及索引
2011-01-17 19:56 1321— Slow log: mysqlsla -lt slow ... -
六款常用mysql slow log分析工具的比较
2011-01-17 19:06 1281转自:http://www.iteye.com/topi ... -
MySQL的大小写敏感性
2011-01-12 14:05 1057转自: http://www.zeali.net/ent ... -
如何查看mysql的版本
2010-05-22 11:52 22037如果我们想要查看mysql数据库的版本有以下四种方法: ... -
MySQL 数据库的备份和恢复
2010-03-19 13:44 927转自 忧里修斯 http://tec ... -
mysql使用show命令以及replace函数批量修改数据
2010-03-19 13:37 2415一.mysql的show命令 a. show tables或 ... -
MySQL中的ROWNUM的实现
2010-01-26 13:43 2206本文转自 http://blog.csdn.net/ACMA ... -
一个MySQL死锁问题的分析及解决
2010-01-20 12:50 1261转自http://java-guru.iteye.com/bl ... -
MySQL 死锁分析
2010-01-20 12:42 33411.MySQL锁和死锁的理解: ... -
sql 按指定顺序排序
2010-01-19 10:53 21831、在ORACLE中使用Decode Decode实 ... -
MYSQL 事务管理
2009-10-26 19:48 1064mysql_query("BEGIN"); ... -
delete 符合条件的记录中的前几条或者重复记录
2009-09-03 20:04 2073今天写代码,遇到了这个问题,只能删除符合条件的记录中的某几条. ... -
sql update delete 中 使用 inner join
2009-08-24 11:38 7199SQL中使用update inner join和delet ... -
What is the difference between VARCHAR, VARCHAR2 ?
2009-06-01 09:43 934Both CHAR and VARCHAR2 types ar ... -
Oracle index
2009-05-15 10:50 0索引是一种可以提高查 ... -
Views and Materialized Views 整理
2009-04-10 14:29 1147Views and Mat ... -
IN and EXISTS, NOT IN AND NOT EXISTS
2009-04-10 14:28 1443Functionally, they are the same ... -
深入sql之merge into
2009-01-08 16:38 4783转自 逆水流沙 http://hi.baidu.com/wen ... -
Oracle日期函数操作(收集整理版)
2008-12-04 16:50 2799经常在平时的开发中要用到oracle的日期函数,每次都要上 ...
相关推荐
在Oracle数据库中,`TRUNCATE TABLE`是一个用于删除表中所有数据的命令,它与`DELETE`语句不同,不记录任何删除操作,并且在大多数情况下执行速度更快。然而,一旦执行`TRUNCATE TABLE`,数据通常无法通过常规的...
本文档将详细介绍如何为特定用户授予截断`SYS.AUD$`表的权限,并提供一系列与之相关的Oracle数据库管理和优化操作。 #### 关键知识点 ##### 1. 授予用户截断审计表的权限 为了确保只有授权的用户能够截断审计表`...
在Oracle数据库管理中,有时需要为特定用户授权执行`TRUNCATE TABLE`操作,以便他们在自己的职责范围内能够清理数据而不影响其他用户。然而,Oracle不支持直接为用户授予针对单个表的`TRUNCATE`权限,而是通常将更...
在Oracle数据库中,`TRUNCATE TABLE`命令用于删除表中的所有数据,但它与`DELETE`语句有所不同。`TRUNCATE`操作是DDL(Data Definition Language)的一部分,而`DELETE`是DML(Data Manipulation Language)。由于`...
execute immediate 'truncate table sys.aud$'; end; ``` 在创建了存储过程后,需要赋予存储过程的执行权限给相应的用户。例如,可以使用以下语句赋予 qzj_orcl 用户执行存储过程的权限: ```sql grant execute ...
为了应对这类问题,可以通过创建Oracle系统触发器来实现对`DROP TABLE`和`TRUNCATE TABLE`命令的监控与阻止。以下详细介绍两种不同的Oracle版本(10g和8i)下如何创建相应的系统触发器。 ### Oracle 10g环境下的...
在Oracle数据库中,`TRUNCATE TABLE`是一个用于删除表中所有数据的命令,与`DELETE`语句不同,`TRUNCATE`不记录任何删除操作,因此它不能通过闪回查询(Flashback Query)来恢复。当你执行`TRUNCATE TABLE`后,数据...
在Oracle数据库中,`TRUNCATE TABLE`命令用于删除表中的所有数据,但保留表结构。与`DELETE`语句不同,`TRUNCATE`不记录任何删除操作,因此无法通过常规的事务回滚来恢复数据。然而,Oracle提供了一些高级恢复功能,...
Oracle 中 Truncate 表的恢复方法 Oracle 数据库中,Truncate 表是一种高效的删除表操作,但是它不会产生日志记录和回滚段空间的使用,无法使用闪回恢复。因此,需要使用 LogMiner 快速定位 Truncate 表的 SCN,并...
PRM DUL for oracle恢复被truncate截断掉的表 Oracle DBA神器:PRM灾难恢复工具,Schema级别数据恢复。PRM For Oracle Database – schema级别oracle数据库数据恢复特性 ,PRM即ParnassusData Recovery Manager是...
在Oracle数据库中,`TRUNCATE TABLE`语句用于删除表中的所有行,其执行速度远快于使用`DELETE`语句。这是因为`TRUNCATE`会删除表的数据段并重置表的高水位线(High Water Mark, HWM),而不会记录每一行的删除动作到...
根据提供的文件信息,以下是从标题、描述以及部分代码示例中提炼出的关于Oracle数据库中与表(Table)操作相关的常用命令及其详细解释: ### 1. 创建表(Create Table) **命令格式**: ``` sql> create table table_...
Oracle 数据库删除数据 Delete 语句和 Truncate 语句的对比 Delete 语句和 Truncate 语句是 Oracle 数据库中两种常用的删除数据的方法,但是它们之间存在着明显的差异。 一、Delete 语句 Delete 语句是一种 DML...
6. **数据泵导出导入(Data Pump Export/Import)**:在Oracle中,你可以使用Data Pump工具导出部分数据,然后再导入,以覆盖被TRUNCATE的表。这种方法适用于部分数据丢失的情况。 7. **用户自定义脚本**:"FY_...
在Oracle数据库管理中,删除数据是一项常见的操作,通常有两种主要的方式:Delete语句和Truncate语句。这两种语句在功能上相似,都是用来移除表中的数据,但在执行效率和资源消耗上存在显著差异。 一、Delete语句 ...