Delete truncate big data or delete Big Data Columns

Truncate of big data tables, column deletion, shrink recovery High Level

1. truncate operations on Big Data Tables

1. truncate related tables. truncate first deletes the space records occupied by the table in the data dictionary.

2. Release all data blocks occupied by the table

3. At last, the hwm of the table is minimized.

 

If your table is a huge table (dozens of GB), it takes a long time to release the data block, affecting your system usage.

Solution:

1. First, delete the records in the space occupied by the table in the data dictionary to minimize the hwm of the table.

Truncate table Doudou reuse storage;

2. The following command releases data blocks in the system space.

Alter table Doudou deallocate unused keep 1g;

Alter table Doudou deallocate unused keep 0 m;

Finally, all the space occupied by the table Doudou is released. Of course, if other users have inserted data into the table Doudou before running keep 0 MB, then all data blocks in the table Doudou will not be released, instead, it only releases useless data blocks.

 

Ii. Modification and deletion of large data table columns

 

1. Delete Columns

Alter table Doudou drop column wow; (the data and indexes of the wow column are also deleted)

Alter table Doudou drop column wow cascadeconstraints; (deleting wow columns, Data, indexes, and foreign key referenced columns of this column are all deleted)

Alter table Doudou drop column wow cascadeconstraints checkpoint 2000; (checkpoint 2000 generates full checkpoints, which can save undo resources)

 

2. Delete columns with large data volumes

 

When a column is deleted, Oracle locks the table. Other users cannot perform DML operations on the table. If there are many data rows in the table, it takes a long time to delete the table. Especially during peak hours, the impact is more serious. To this end, we can mark the column as unavailable first. This is logically, but it only updates the information in the data dictionary, so the speed is very fast. When the business is busy, we can physically Delete the space occupied by the column.

Alter table Doudou set unused column wow; (the wow column still exists physically, but compared with the user, this column is actually deleted .)

Alter table Doudou drop unused columns wow; the real physical deletion is completed. (When not busy)

Alter table Doudou drop unused columns wow checkpoint2000; saves undo resources.

 

3. high water level recovery with shrink

 

Conditions for using shrink:

1. The table's tablespace must use assm.

2. shrinking the table causes the data rows to be transferred between different data blocks. Therefore, row movement must be enabled on the scaled table. Alter talbe Doudou enable row movement; after the contraction, the row is moved to the end; alter table Doudou disable row movement;

 

Shrink process:

Alter table Doudou shrink space compact; compression stage (Oracle recommends compression during peak hours)

Alter table Doudou shrink space; contraction phase (Oracle recommends shrinking when you are not busy, which will produce exclusive locks, so other users cannot perform any operation on the table to be shrunk)

Alter table Doudou shrink space cascade; not only the Doudou table but also the Doudou table

Http://blog.csdn.net/dbadoudou/article/details/7703197