Table of large table transformation ingredient area

First query the large table under the user

Method One:
Select Table_name,num_rows from User_tables order by num_rows Desc;

(Sort by number of rows in a table)
Method Two:

Create or Replace function count_rows (table_name in VARCHAR2)
return number
Is
Num_rows number;
stmt VARCHAR2 (2000);
Begin
stmt: = ' SELECT count (*) from ' | | table_name;
Execute immediate stmt into num_rows;
return num_rows;
End

Select Table_name,count_rows (table_name) nrows from User_tables order by nrows Desc;

Method Three:
(slightly different from the previous two, this is query by table size)
SELECT * FROM
(select Owner,segment_name,segment_type,sum (bytes)/1024/1024 object_size
From dba_segments where owner= ' GUOYU ' GROUP by Owner,segment_name,segment_type ORDER BY object_size Desc)
where RowNum < segment_type= ' TABLE ';

--------------------------------------------------------

---------------------------------------------------------

CREATE TABLE Customer_salesinfo_detailn
(
ID VARCHAR2 (+) NOT NULL,
Salesid VARCHAR2 (40),
SALESDATE DATE,
PRODUCTCODE VARCHAR2 (20),
QUANT number (10,2),
Retailprice number (10,2),
AMOUNT number (10,2),
BARCODE VARCHAR2 (18),
Mini_unit VARCHAR2 (18),
Discountamount number (10,2),
Product_type VARCHAR2 (18)
)
Partition by range (SALESDATE)
(
Partition sales_1402 values less than (to_date (' 2014-03-01 ', ' yyyy-mm-dd ')
, partition sales_1403 values less than (to_date (' 2014-04-01 ', ' yyyy-mm-dd ')
, partition sales_1404 values less than (to_date (' 2014-05-01 ', ' yyyy-mm-dd ')
, partition sales_1405 values less than (to_date (' 2014-06-01 ', ' yyyy-mm-dd ')
, partition sales_1406 values less than (to_date (' 2014-07-01 ', ' yyyy-mm-dd ')
, partition sales_1407 values less than (to_date (' 2014-08-01 ', ' yyyy-mm-dd ')
, partition sales_1408 values less than (to_date (' 2014-09-01 ', ' yyyy-mm-dd ')
, partition sales_1409 values less than (to_date (' 2014-10-01 ', ' yyyy-mm-dd ')
, partition sales_1410 values less than (to_date (' 2014-11-01 ', ' yyyy-mm-dd ')
, partition sales_1411 values less than (to_date (' 2014-12-01 ', ' yyyy-mm-dd ')
, partition sales_1412 values less than (to_date (' 2015-01-01 ', ' yyyy-mm-dd ')
, partition sales_1501 values less than (to_date (' 2015-02-01 ', ' yyyy-mm-dd ')
, partition sales_1502 values less than (to_date (' 2015-03-01 ', ' yyyy-mm-dd ')
, partition sales_1503 values less than (to_date (' 2015-04-01 ', ' yyyy-mm-dd ')
, partition sales_1504 values less than (to_date (' 2015-05-01 ', ' yyyy-mm-dd ')
, partition sales_1505 values less than (to_date (' 2015-06-01 ', ' yyyy-mm-dd ')
, partition sales_1506 values less than (to_date (' 2015-07-01 ', ' yyyy-mm-dd ')
, partition sales_1507 values less than (to_date (' 2015-08-01 ', ' yyyy-mm-dd ')
, partition sales_1508 values less than (to_date (' 2015-09-01 ', ' yyyy-mm-dd ')
, partition sales_1509 values less than (to_date (' 2015-10-01 ', ' yyyy-mm-dd ')
, partition sales_1510 values less than (to_date (' 2015-11-01 ', ' yyyy-mm-dd ')
, partition sales_1511 values less than (to_date (' 2015-12-01 ', ' yyyy-mm-dd ')
, partition sales_1512 values less than (to_date (' 2016-01-01 ', ' yyyy-mm-dd ')
, partition sales_1601 values less than (to_date (' 2016-02-01 ', ' yyyy-mm-dd ')
, partition sales_1602 values less than (to_date (' 2016-03-01 ', ' yyyy-mm-dd ')
, partition sales_1603 values less than (to_date (' 2016-04-01 ', ' yyyy-mm-dd ')
, partition sales_1604 values less than (to_date (' 2016-05-01 ', ' yyyy-mm-dd ')
, partition sales_1605 values less than (to_date (' 2016-06-01 ', ' yyyy-mm-dd ')
, partition sales_1606 values less than (to_date (' 2016-07-01 ', ' yyyy-mm-dd ')
, partition sales_1607 values less than (to_date (' 2016-08-01 ', ' yyyy-mm-dd ')
, partition sales_1608 values less than (to_date (' 2016-09-01 ', ' yyyy-mm-dd ')
, partition sales_1609 values less than (to_date (' 2016-10-01 ', ' yyyy-mm-dd ')
, partition sales_1610 values less than (to_date (' 2016-11-01 ', ' yyyy-mm-dd ')
, partition sales_1611 values less than (to_date (' 2016-12-01 ', ' yyyy-mm-dd ')
, partition sales_1612 values less than (to_date (' 2017-01-01 ', ' yyyy-mm-dd ')
, partition Sales_max values less than (MaxValue)
)
Tablespace Pos_tbs
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 64K
Next 1M
Minextents 1
Maxextents Unlimited
);

--Create/recreate primary, unique and foreign KEY constraints
ALTER TABLE Customer_salesinfo_detailn
Add primary key (ID)
Using index
Tablespace Pos_tbs
Pctfree 10
Initrans 2
Maxtrans 255
Storage
(
Initial 64K
Next 1M
Minextents 1
Maxextents Unlimited
);
-------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------

Method One: (online redefinition)

Check whether this table can be redefined on-line, no error indication can be, errors will give the wrong message:
sql> exec dbms_redefinition.can_redef_table (' Guoyu ', ' customer_salesinfo_detail ');
PL/SQL procedure successfully completed

To start an online redefinition:
sql> exec dbms_redefinition.start_redef_table (' Guoyu ', ' customer_salesinfo_detail ', ' Customer_salesinfo_detailn ‘);
PL/SQL procedure successfully completed

After you refresh the data using the Sync_interim_table module of the Dbms_redefinition package, the intermediate table can also see the data changes.
sql> exec dbms_redefinition.sync_interim_table (' Guoyu ', ' im_customer_salesinfo_detail ', ' customer_salesinfo_ Detailn ');
PL/SQL procedure successfully completed

End Online redefinition
sql> exec dbms_redefinition.finish_redef_table (' Guoyu ', ' im_customer_salesinfo_detail ', ' customer_salesinfo_ Detailn ');
PL/SQL procedure successfully completed

-----If performing an online redefinition does not succeed, it is possible that the re-check will fail, possibly because a materialized view has been built on the source table, and deleting the materialized view may be possible.
Drop materialized view Log on Customer_salesinfo_detail;
----If a redefinition intermediate error can be performed
Dbms_redifinition.abort_redef_table (' Guoyu ', ' customer_salesinfo_detail ', ' Customer_salesinfo_detailn ')
---This stored procedure to delete temporary objects resulting from online redefinition.




Method Two:
1. Inserting data
INSERT INTO Customer_salesinfo_detailn select * from Customer_salesinfo_detail;
Commit

2. Direct renaming will prompt ora-26563:renaming This table was not allowed to delete materialized views on the table.
Drop materialized view Log on Customer_salesinfo_detail;

ALTER TABLE Im_customer_salesinfo_detail Rename to Customer_salesinfo_detailo;
--and then rename

ALTER TABLE Im_customer_salesinfo_detailn Rename to Customer_salesinfo_detail;


-------------------------------------
------------------------------------------

To create an index:

Local index
--Create/recreate indexes
Drop index salesdetail_date;
Create INDEX salesdetail_date on Customer_salesinfo_detail (SALESDATE) Local
Tablespace Pos_tbs
Pctfree 10
Initrans 2
Maxtrans 255
Storage
(
Initial 64K
Next 1M
Minextents 1
Maxextents Unlimited
);
Drop index Salesdetail_saleid;
Create INDEX Salesdetail_saleid on Customer_salesinfo_detail (salesid) Local
Tablespace Pos_tbs
Pctfree 10
Initrans 2
Maxtrans 255
Storage
(
Initial 64K
Next 1M
Minextents 1
Maxextents Unlimited
);

Test:

Select COUNT (id) from Customer_salesinfo_detail partition (sales_1502);
Select COUNT (*) from Customer_salesinfo_detail;
To see if each partition data is correct
Sql> Select table_name, partition_name from user_tab_partitions where table_name = ' im_customer_salesinfo_detail ';

No problem, delete the original table
drop table Customer_salesinfo_detailo Purge; ---------------Insert Method
drop table Customer_salesinfo_detailn Purge; -----------------on-line redefinition method

Table of large table transformation ingredient area