Removing Data from a Table- Tables and Constraints
You can use either the DELETE statement or the TRUNCATE statement to remove records from a table. A DELETE statement is basically a way to change the data or in this case remove it, which is logged and can be rolled back or committed.
However, a TRUNCATE command has no way to roll back. TRUNCATE is considered changing a table or a DDL statement, even changes the previous size of the table, and it is almost like the table was just created. There are additional permissions that are needed to truncate a table instead of a delete.
You can’t truncate a table that has a primary key defined that is referenced by an enabled foreign key constraint in a child table, even if there are no rows in that table.
Because a TRUNCATE statement is DDL, you also can’t truncate two separate tables as one transaction. Compare this behavior with that of DELETE. Oracle does allow you to use the DELETE statement to remove rows from a parent table while the constraints are enabled that reference a child table. This is because DELETE generates undo, is read consistent, and can be rolled back.
Note Another way to remove data from a table is to drop and re-create the table. However, this means you also have to re-create any indexes, constraints, grants, and triggers that belong to the table.Additionally, when you drop a table, it is unavailable until you re-create it and reissue any required grants. Usually, dropping and re-creating a table are acceptable only in a development or test environment.
Moving a Table
Moving a table means either rebuilding the table in its current tables or building it in a different tablespace. You may want to move a table because its current tablespace has disk space storage issues or because you want to lower the table’s high-water mark.
Use the ALTER TABLE … MOVE statement to move a table from one tablespace to another. This example moves the INVENTORY table to the USERS tablespace:
SQL> alter table inventory move tablespace users;
You can verify that the table has been moved by querying USER_TABLES:
SQL> select table_name, tablespace_name from user_tables
where table_name=’INVENTORY’; TABLE_NAME
—————————–INVENTORY
Note The ALTER TABLE … MOVE statement does not allow DML to execute while it is running.There are some restrictions, but there is an ALTER TABLE … MOVE ONLINE statement that will not restrict access to the table or use the DBMS_ REDEFINITION package.
When you move a table, all its indexes are rendered unusable. This is because a table’s index includes the ROWID as part of the structure. The table ROWID contains information about the physical location.
Given that the ROWID of a table changes when the table moves from one tablespace to another (because the table rows are now physically located in different data files), any indexes on the table contain incorrect information. To rebuild the index, use the ALTER INDEX … REBUILD command.