Oracle cleans large tables and drops water levels

Background: A table of cleanup mechanism problems, resulting in the table data has been increasing, the table water level is very high, it is necessary to clean up the table data and drop the water level.

1.1 Pre-migration preparation

Step one, create a new table P_transaction_bak.

[Email protected]:~/orcale > Sqlplus test/test

sql> Create table P_transaction_bak
As
Select * from p_transaction where 1 = 0;
Sql> ALTER TABLE P_transaction_bak modify Operationdate DEFAULTsysdate;
Sql> ALTER TABLE P_transaction_bak modify INVALID DEFAULT 0;

Step two, the necessary valid data is inserted into the new table.

sql> altersession enable parallel DML;

Insert/*+ Append Parallel (p,8) */intop_transaction_bak P Select/*+ Parallel (n,8) */* fromp_transaction n
where To_char (n.operationdate, ' yyyy-mm-dd ') between
To_char (to_date (' 2016-06-27 ', ' yyyy-mm-dd '), ' yyyy-mm-dd ') and
To_char (to_date (' 2016-07-04 ', ' yyyy-mm-dd '), ' yyyy-mm-dd ');

Commit;

Step three, for the new table p_transaction

Table create primary key and index
Sql> Create unique index pk_po_transaction_new on P_transaction_bak (Streamingid) Tablespace portaloneindx parallel 8 online;
sql> ALTER TABLE P_transaction_bak add constraintpk_po_transaction_new primary key (Streamingid);
sql> alter index pk_po_transaction_new noparallel;
Sql> Create index ix_transaction_operationdate on P_transaction_bak (operationdate) Tablespace portaloneindx parallel 8 online;
sql> alter index ix_transaction_operationdate noparallel;

----END

1.2 Perform data Migration

Step one, stop the application

Step two, log in to the database SYSDBA user.

[Email protected]:~/orcale > Sqlplus/as sysdba

Step three, check the data table space and Index table space usage.

Sql>select Total Tablespace_name,
Round (total. MB, 2) as TOTAL_MB,
Round (total. Mb-free. MB, 2) as USED_MB,
Round (1-free. MB/ total. MB) * 100, 2) | | '% ' as used_pct
From (select Tablespace_name, sum (bytes)/1024/1024 as MB
From Dba_free_space
Group by Tablespace_name) free,
(select Tablespace_name, sum (bytes)/1024/1024 as MB
From Dba_data_files
Group by Tablespace_name) Total
where free.tablespace_name = total. tablespace_name;

Step four, insert data into the new table in parallel.

sql> altersession enable parallel DML;

Insert/*+ Append Parallel (p,8) */intop_transaction_bak P Select/*+ Parallel (n,8) */* fromp_transaction n
where To_char (n.operationdate, ' yyyy-mm-dd ') =
To_char (to_date (' 2016-07-05 ', ' yyyy-mm-dd '), ' yyyy-mm-dd ');

Commit;

Step five, back up the old table.

sql> rename p_transaction to P_transaction_old;

Step six, rename the new table.

Sql> Renamep_transaction_bak to P_transaction;

----END

1.3 Rollback the above operation [email protected]:~/orcale >sqlplus test/test

sql>renamep_transaction to p_transaction_new;
sql>rename p_transaction_old to p_transaction;
Sql>truncate table p_transaction_new;
sql>drop table p_transaction_new;

----END

Oracle cleans large tables and drops water levels