This is the 29th day of my participation in the August Wenwen Challenge.More challenges in August

preface

Why convert an ordinary table to a partitioned table? What are some ways to do that?

• As one of the three components of Oracle, partitioned table plays an important role in Oracle database.

What are the advantages of partitioned tables?

• Common table to partitioned table: The application is not aware, DML statements can access partitioned table without modification.
• High availability: The unavailability of some partitions does not affect the use of the entire partition table.
• Easy management: You can perform DDL operations on partitions separately, including rebuilding indexes or expanding partitions, without affecting the use of partitioned tables.
• Reduce OLTP system resource contention: because DML operates over many segments.

The online redefinition method can be used to convert partitioned tables online. The process is simple and the conversion can be performed quickly.

Introduce a,

DBMS_REDEFINITION (online redefinition) :

• Supported Database versions: Oracle Database-Enterprise Edition – Version 9.2.0.4 and later
• Online redefinition is done through materialized views.

Some restrictions for using online redefinition:

• There must be enough table space to hold twice as much data as the table.
• Primary key columns cannot be modified.
• Tables must have primary keys.
• The online redefinition must be performed under the same user.
• The tables under the SYS and SYSTEM users cannot be redefined online.
• Nologging cannot be used for online redefinition.
• If the intermediate table has new columns, it cannot have a NOT NULL constraint

DBMS_REDEFINITION package:

• ABSORT_REDEF_TABLE: clears redefinition errors and suspends redefinition;
• CAN_REDEF_TABLE: checks whether the table can be redefined. If the stored procedure is successfully executed, the table can be redefined.
• COPY_TABLE_DEPENDENTS: Synchronize index and dependent object (index, constraint, trigger, permission, etc.);
• FINISH_REDEF_TABLE: online redefinition is complete.
• REGISTER_DEPENDENTS_OBJECTS: Register a dependent object such as an index, constraint, trigger, etc.
• START_REDEF_TABLE: starts online redefinition.
• SYNC_INTERIM_TABLE: synchronizes data incrementally.
• UNREGISTER_DEPENDENT_OBJECT: Does not register a dependent object, such as an index, constraint, or trigger.

Second, the actual combat

Test environment database installation:

• 11G:./ oracleshellinstall. sh -i 10.211.55.111

For more details on how scripts are used, subscribe to the column:Oracle install scripts with one click.

How to obtain scripts:

• GitHub Keep updating at 🔥
• Gitee Keep updating at 🔥

1 Build test data

Create test tablespace and user:

sqlplus / as sysdba
create tablespace PAR;
create user par identified by par;
grant dba to par;
Copy the code

Create test table:

sqlplus par/par
create table lucifer(
id number(8) PRIMARY KEY,
name varchar2(20) not null,
par_date date)
tablespace PAR;
comment on table lucifer is 'lucifer table';
comment on column lucifer.name is 'name';
comment on column lucifer.par_date is 'Partition date';
create index id_name on lucifer(name) tablespace par;
Copy the code

Insert test data:

sqlplus par/par
begin
  for i in0.. 24 loop insert into lucifer values (i,'lcuifer_' || i,
       add_months(to_date('2021-1-1'.'yyyy-mm-dd'), i));
  end loop;
  commit;
end;
/
Copy the code

As you can see, the test data has been constructed, and it’s time to go live.

2 Check whether the definition can be modified

Select * from table where primary key (s) exists;

sqlplus / as sysdba
## Check the primary key
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 'LUCIFER';
Copy the code

Confirm if redefinition can be done without primary key using rowid:

sqlplus / as sysdba
exec dbms_redefinition.can_redef_table('PAR'.'LUCIFER');
Copy the code

Execution without an error indicates that the table can be redefined online.

Create intermediate table (partitioned table structure)

Create a partitioned table structure with PL/SQL package:

sqlplus par/par
BEGIN
  ctas_par(p_tab        => 'lucifer',
           p_part_colum => 'par_date',
           p_part_nums  => 24,
           p_tablespace => 'par');
END;
/
Copy the code

Note: PL/SQL packages refer to:Oracle common tables are converted to partitioned tables on a monthly basis, and partitioned tables are generated by one-click PLSQL packages

Create intermediate partition table lucifer_PAR:

create table lucifer_par
(
  id       NUMBER(8),
  name     VARCHAR2(20),
  par_date DATE
)
partition BY RANGE(par_date)(
partition lucifer_P202101 values less than (TO_DATE('2021-02-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202102 values less than (TO_DATE('2021-03-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202103 values less than (TO_DATE('2021-04-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202104 values less than (TO_DATE('2021-05-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202105 values less than (TO_DATE('2021-06-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202106 values less than (TO_DATE('2021-07-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202107 values less than (TO_DATE('2021-08-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202108 values less than (TO_DATE('2021-09-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202109 values less than (TO_DATE('2021-10-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202110 values less than (TO_DATE('2021-11-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202111 values less than (TO_DATE('2021-12-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202112 values less than (TO_DATE('2022-01-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202201 values less than (TO_DATE('2022-02-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202202 values less than (TO_DATE('2022-03-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202203 values less than (TO_DATE('2022-04-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202204 values less than (TO_DATE('2022-05-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202205 values less than (TO_DATE('2022-06-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202206 values less than (TO_DATE('2022-07-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202207 values less than (TO_DATE('2022-08-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202208 values less than (TO_DATE('2022-09-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202209 values less than (TO_DATE('2022-10-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202210 values less than (TO_DATE('2022-11-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202211 values less than (TO_DATE('2022-12-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202212 values less than (TO_DATE('2023-01-01 00:00:00'.'SYYYY-MM-DD HH24:MI:SS'.'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_MAX values less than (maxvalue) tablespace par)
             enable row movement
             tablespace par;
Copy the code

As above, if unique indexes and constraints are not added, they are automatically copied, and the middle table of the partitioned table structure has been generated.

4 Check whether row migration is enabled in the intermediate table

select row_movement from dba_tables where table_name='LUCIFER' and owner='PAR';
select row_movement from dba_tables where table_name='LUCIFER_PAR' and owner='PAR';
Copy the code

5 Collect table statistics

To ensure accurate data, collect statistics before you start:

sqlplus / as sysdba
exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8');exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER_PAR',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8');Copy the code

6 Start online redefinition

sqlplus / as sysdba
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('PAR'.'LUCIFER'.'LUCIFER_PAR');
Copy the code

7 Copy table attributes to exclude indexes

Select automatic replication table property to manually create local index (Local)

• Advantages: You only need to focus on index omissions, and do not need to focus on triggers, permissions, constraints, and other dependencies.
• Disadvantages: Manually create indexes and manually rename indexes.

May refer to:COPY_TABLE_DEPENDENTS for Oracle online redefine

sqlplus par/par
SET SERVEROUTPUT ON
DECLARE
  l_errors  NUMBER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname            => USER,
    orig_table       => 'LUCIFER',
    int_table        => 'LUCIFER_PAR',
    copy_indexes     => 0,
    copy_triggers    => TRUE,
    copy_constraints => TRUE,
    copy_privileges  => TRUE,
    ignore_errors    => FALSE,
    num_errors       => l_errors,
    copy_statistics  => FALSE,
    copy_mvlog       => FALSE);
    
  DBMS_OUTPUT.put_line('Errors=' || l_errors);
END;
/
Copy the code

If no error is reported during execution, it indicates normal.

8 Create local indexes for intermediate tables

Alter table LUCIFER_PAR create index:

create index ID_NAME_PAR on LUCIFER_PAR(NAME) tablespace PAR local parallel 8;
Copy the code

Note: The index name must be different from the original index name.

9 Cancel index parallelism

If parallel creation is enabled during index creation, the index parallelism must be cancelled:

sqlplus / as sysdba
select 'alter index '||owner||'. '||index_name||' noparallel; '
from dba_indexes 
where table_name = 'LUCIFER_PAR' and owner= 'PAR';
Copy the code

10 Synchronize data (reduces table lock time at the end of the redefinition process)

sqlplus / as sysdba
BEGIN
dbms_redefinition.sync_interim_table(
uname => 'PAR',
orig_table => 'LUCIFER',
int_table => 'LUCIFER_PAR');
END;
/
Copy the code

Note: This step is done to reduce the amount of time the table is locked at the end of the redefinition.

11 Collect statistics about the intermediate table

To prepare for the following synchronization data, collect intermediate table statistics:

sqlplus / as sysdba
exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER_PAR',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8');Copy the code

12 End redefinition (End redefinition requires locking the table. The specific time depends on the size of the table.)

sqlplus / as sysdba
BEGIN
dbms_redefinition.finish_redef_table(
uname => 'PAR',
orig_table => 'LUCIFER',
int_table => 'LUCIFER_PAR');
END;
/
Copy the code

13 Check whether the partition table has been converted

sqlplus par/par
select owner,table_name,partitioned from user_tables where table_name in ('LUCIFER'.'LUCIFER_PAR');
Copy the code

As above, the LUCIFER table has been redefined online to a partitioned table structure.

14 Manually rename the index

1. At this time, the original table name has been converted to an intermediate table. Rename the original table to another name

sqlplus / as sysdba
ALTER index PAR.ID_NAME RENAME TO ID_NAME_BAK;
Copy the code

2. Rename The index of the new partitioned table. Because the index name of the new partitioned table is the index name of the intermediate table, you need to manually rename the index

sqlplus / as sysdba
ALTER index PAR.ID_NAME_PAR RENAME TO ID_NAME;
Copy the code

15 Check whether invalid indexes exist

sqlplus / as sysdba
SELECT owner index_owner, index_name, index_type,'N/A' partition_name,status,table_name,tablespace_name,
  'alter index '||owner||'. '||index_name||' rebuild; ' rebuild_index
  FROM dba_indexes
WHERE status = 'UNUSABLE'
UNION ALL
SELECT a.index_owner,a.index_name,b.index_type,a.partition_name,a.status,b.table_name,a.tablespace_name,
'alter index '||a.index_owner||'. '||a.index_name||' rebuild partition '||a.partition_name||' ;' rebuild_index
  FROM dba_ind_partitions a, dba_indexes b
WHERE a.index_name = b.index_name
   AND a.index_owner = b.owner
   AND a.status = 'UNUSABLE'
UNION ALL
SELECT owner index_owner,a.index_name,b.index_type,'N/A' partition_name,a.status,b.table_name,NULL,
'alter index '||a.index_owner||'. '||a.index_name||' rebuild subpartition '||a.subpartition_name||'; ' rebuild_index
  FROM dba_ind_subpartitions a, dba_indexes b
WHERE a.index_name = b.index_name
   AND a.index_owner = b.owner
   AND a.status = 'UNUSABLE';
Copy the code

16 Check whether row_movement is enabled after the switchover

sqlplus / as sysdba
select owner,table_name,row_movement from dba_tables where table_name in ('LUCIFER'.'LUCIFER_PAR') and owner='PAR';
Copy the code

17 Check invalid objects

## Invalid object compiledsqlplus / as sysdba @? /rdbms/admin/utlrp.sql select'alter '||object_type||' '||owner||'. '||object_name||' compile; '
from  dba_objects t
where t.status = 'INVALID' order by 1;
Copy the code

18 Collect statistics

sqlplus / as sysdba
exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8');Copy the code

19 Insert test data

sqlplus par/par
begin
  for i in100.. 124 loop insert into lucifer values (i,'lcuifer_' || i,
       add_months(to_date('2021-5-1'.'yyyy-mm-dd'), i));
  end loop;
  commit;
end;
/
Copy the code

20 Query data distribution in the partition table

sqlplus par/par
SELECT COUNT(*) FROM  LUCIFER;
SELECT * FROM  LUCIFER PARTITION(LUCIFER_P202101);
SELECT * FROM  LUCIFER PARTITION(LUCIFER_P202201);
SELECT * FROM  LUCIFER PARTITION(LUCIFER_MAX);
Copy the code

You can see that the data is already evenly distributed among the different subpartitions by date. At this point, the online redefinition is complete and the partitioned table has been successfully transformed.

Refer to MOS documentation:

• How To Partition Existing Table Using DBMS_REDEFINITION (Doc ID 472449.1)
• COPY_TABLE_DEPENDENTS for Oracle online redefine

---

This is the end of sharing ~

If you think the article is helpful to you, please like it, favorites it, pay attention to it, comment on it, and support it four times with one button. Your support is the biggest motivation for my creation.