Shulou(Shulou.com)06/01 Report--

Oracle table is located in a table space, space has been occupied, other table space has surplus space. Additional tablespaces exist for implementation tables.

Solution 1: Rebuilding partition table from original table

1: temp, existing field id,time;

2:temp exists;

3: Create a new partition table temp_new based on the original table temp

CREATE TABLE temp_new (ID, TIME) PARTITION BY RANGE (TIME)

(

PARTITION P1 VALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')) TABLESPACE space01,

PARTITION P2 VALUES LESS THAN (TO_DATE('2005-1-1', 'YYYY-MM-DD')) TABLESPACE space02,

PARTITION P3 VALUES LESS THAN (TO_DATE('2005-7-1', 'YYYY-MM-DD')) TABLESPACE space03,

PARTITION P4 VALUES LESS THAN (MAXVALUE) TABLESPACE space04

)

AS SELECT ID, TIME FROM temp;

4: Renaming table names

RENAME temp TO temp_old;

RENAME temp_new TO temp;

5: Query partition record storage

SELECT COUNT(1) FROM temp PARTITION (space01);

SELECT COUNT(1) FROM temp PARTITION (space02);

SELECT COUNT(1) FROM temp PARTITION (space03);

SELECT COUNT(1) FROM temp PARTITION (space04);

SELECT COUNT(1) FROM temp_old;

--The total number of records stored in partitions is the same as the original record number, cross-table space partition storage succeeded.

A little bit: the method is simple and easy to use, using DDL statements, will not produce UNDO, but only a small number of REDO, relatively high efficiency, and after the completion of the table data has been distributed to various partitions.

Disadvantages: Additional consideration is required for database consistency. Consistency cannot be guaranteed by manually locking tables.

Apply to modify the table infrequently, in idle operation, table data volume should not be too large.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.