oracle中删除yuj_[转载]Oracle中删除表操作trunc delete和
drop的说明
相同点:
2.drop，truncate都是DDL语句，执⾏后会⾃动提交。
不同点:
1. truncate和 delete只删除数据不删除表的结构(定义)
drop语句将删除表的结构被依赖的约束(constrain)，触发器(trigger)，索引(index); 依赖于该表的存储过程/函数将保留，但是变为invalid状态.
2.delete语句是dml，这个操作会放到rollback segement中，事务提交之后才⽣效；如果有相应的trigger，执⾏的时候将被触发.
truncate,drop是ddl, 操作⽴即⽣效，原数据不放到rollback segment中，不能回滚. 操作不触发trigger.
3.delete语句不影响表所占⽤的extent, ⾼⽔线(high watermark)保持原位置不动
显然drop语句将表所占⽤的空间全部释放
truncate 语句缺省情况下见空间释放到 minextents个 extent，除⾮使⽤reuse storage; truncate会将⾼⽔线复位(回到最开始).
4.速度,⼀般来说: drop>; truncate >; delete
5.安全性:⼩⼼使⽤drop 和truncate，尤其没有备份的时候.否则哭都来不及
使⽤上，想删除部分数据⾏⽤delete，注意带上where⼦句. 回滚段要⾜够⼤.
想删除表，当然⽤drop
想保留表⽽将所有数据删除. 如果和事务⽆关,⽤truncate即可. 如果和事务有关，或者想触发trigger，还是⽤delete.
如果是整理表内部的碎⽚，可以⽤truncate跟上reuse stroage，再重新导⼊/插⼊数据
DML DCL DDL TCL说明：
1. DDL
Data Definition Language (DDL) statements are
used to define the database structure or schema. Some examples:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all
spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
2. DML
Data Manipulation Language (DML) statements are used for
managing data within schema objects. Some examples:
SELECT - retrieve data from the a database
oracle中trunc函数用法INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the
records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
3. DCL
Data Control Language (DCL) statements. Some examples:
GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT
command
4. TCL
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.
COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you can
later roll back
ROLLBACK - restore database to original since the last
COMMIT
SET TRANSACTION - Change transaction options like isolation
level and what rollback segment to use

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。