 |
|
Oracle Tips by Burleson |
DROP/DELETE/TRUNCATE Tables
As we said at the beginning of the chapter,
creating a table is DDL. Dropping a table is also a DDL statement, because it
modifies an object in the database rather that data in an object.
Each of these commands will either remove the
table from the database or remove the rows from the table. If we want
to remove the table and all it’s data from the database, we use the
drop command.
SQL> drop
table t1;
Table dropped.
The DROP
command is used to remove any
database object from the database. It works by removing the object
definition. Since the object no longer exists, it can no longer be
used. The drop command is DDL
because it defines objects in the
database. Anytime you execute a DDL command, you implicitly issue a
commit and the statement cannot be rolled back. This is discussed in
the last section of this chapter.
The DELETE
command will not remove the table
but will remove all the rows in the table.
SQL> select
count(*) from t1;
COUNT(*)
----------
10
1 row selected.
SQL> delete t1;
10 rows
deleted.
SQL>
rollback;
Rollback complete.
SQL> delete from t1;
10 rows
deleted.
In this example, there are ten rows in table T1.
The DELETE T1 command deletes all ten rows. The DELETE command is a
DML command that does not issue an implicit commit. For that
reason, I can rollback the DELETE. The DELETE FROM T1 command is the
equivalent of the DELETE T1 command and can also be rolled back. We
will cover deleting rows in detail later. The rollback command will
be covered in the last section of this chapter.
The above text is
an excerpt from:
Easy Oracle SQL
Get Started Fast Writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
by John Garmany
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
