Home Database Mysql Tutorial Oracle 在线重定义(将普通堆表转换成分区表)

Oracle 在线重定义(将普通堆表转换成分区表)

Jun 07, 2016 pm 05:07 PM
oracle database

1 创建测试表 用sys用户创建测试表 SQLgt; CREATE TABLE HR.ST (ID NUMBER, TIME DATE); Table created. SQLgt; INSERT INTO

1 创建测试表

用sys用户创建测试表

SQL> CREATE TABLE HR.ST (ID NUMBER, TIME DATE);

Table created.

SQL> INSERT INTO HR.ST SELECT ROWNUM, CREATED FROM DBA_OBJECTS;

50416 rows created.

SQL> commit;

Commit complete.

2 授权

SQL> grant execute on DBMS_REDEFINITION to hr;

Grant succeeded.

SQL> grant CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, SELECT ANY TABLE to hr;

Grant succeeded.

3 新建中间表(分区的表结构)

SQL> create table mid_st(id number primary key, time date) partition by range(time)

  2  (

  3    partition p1 values less than(to_date('2004-7-1', 'yyyy-mm-dd')),

  4    partition p2 values less than(to_date('2005-1-1', 'yyyy-mm-dd')),

  5    partition p3 values less than(to_date('2005-7-1', 'yyyy-mm-dd')),

  6    partition p4 values less than(maxvalue)

  7  );

Table created

4 验证

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'ST', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL procedure successfully completed

5 在线重定义

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'ST', 'MID_ST');

PL/SQL procedure successfully completed

6 同步数据(可选)

如果在执行DBMS_REDEFINITION.START_REDEF_TABLE()过程和执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程直接在重定义表上执行了大量的DML操作,那么可以选择执行一次或多次的SYNC_INTERIM_TABLE()过程,,以减少最后一步执行FINISH_REDEF_TABLE()过程时的锁定时间。

SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(USER, 'ST', 'MID_ST');

PL/SQL procedure successfully completed

7 结束重定义

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'ST', 'MID_ST');

PL/SQL procedure successfully completed

8 查看分区结果

SQL> select a.table_name, a.partition_name from user_tab_partitions a;

TABLE_NAME                     PARTITION_NAME

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

ST                             P1

ST                             P2

ST                             P3

ST                             P4

9 放弃在线重定义

可以在执行dbms_redefinition.start_redef_table之后到执行dbms_redefinition.finish_redef_table之前的时间里执行:

DBMS_REDEFINITION.abort_redef_table(user, 'st', 'mid_st')以放弃执行在线重定义。

10 常见问题

10.1 无主键

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'ST', DBMS_REDEFINITION.CONS_USE_PK);

BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE(user, 'ST', DBMS_REDEFINITION.CONS_USE_PK); END;

*

ERROR at line 1:

ORA-12089: cannot online redefine table "HR"."ST" with no primary key

ORA-06512: at "SYS.DBMS_REDEFINITION", line 137

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478

ORA-06512: at line 1

 

出错了, 该表上缺少主键, 为该表建主键. 再执行验证.

SQL> alter table st add constraint pk_t primary key(id);

Table altered.

 

用这句删除materialized view 即可继续进行

drop materialized view log on ;

drop materialized view log on t; OR drop materialized t;

10.2 未授权

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'ST', 'MID_ST');

begin DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'ST', 'MID_ST'); end;

ORA-01031: insufficient privileges

ORA-06512: at "SYS.DBMS_REDEFINITION", line 50

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343

ORA-06512: at line 2

linux

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to check which table space a table belongs to in Oracle How to check which table space a table belongs to in Oracle Jul 06, 2023 pm 01:31 PM

How to check which table space a table belongs to in Oracle: 1. Use the "SELECT" statement and specify the table name to find the table space to which the specified table belongs; 2. Use the database management tools provided by Oracle to check the table space to which the table belongs. Tools usually provide a graphical interface, making the operation more intuitive and convenient; 3. In SQL*Plus, you can view the table space to which the table belongs by entering the "DESCRIBEyour_table_name;" command.

How to connect to Oracle database using PDO How to connect to Oracle database using PDO Jul 28, 2023 pm 12:48 PM

Overview of how to use PDO to connect to Oracle database: PDO (PHPDataObjects) is an extension library for operating databases in PHP. It provides a unified API to access multiple types of databases. In this article, we will discuss how to use PDO to connect to an Oracle database and perform some common database operations. Step: Install the Oracle database driver extension. Before using PDO to connect to the Oracle database, we need to install the corresponding Oracle

How to retrieve only one piece of duplicate data in oracle How to retrieve only one piece of duplicate data in oracle Jul 06, 2023 am 11:45 AM

Steps for Oracle to fetch only one piece of duplicate data: 1. Use the SELECT statement combined with the GROUP BY and HAVING clauses to find duplicate data; 2. Use ROWID to delete duplicate data to ensure that accurate duplicate data records are deleted, or use "ROW_NUMBER" ()" function to delete duplicate data, which will delete all records except the first record in each set of duplicate data; 3. Use the "select count(*) from" statement to return the number of deleted records to ensure the result.

Implement data import into PHP and Oracle databases Implement data import into PHP and Oracle databases Jul 12, 2023 pm 06:46 PM

Implementing data import into PHP and Oracle databases In web development, using PHP as a server-side scripting language can conveniently operate the database. As a common relational database management system, Oracle database has powerful data storage and processing capabilities. This article will introduce how to use PHP to import data into an Oracle database and give corresponding code examples. First, we need to ensure that PHP and Oracle database have been installed, and that PHP has been configured to

How to use PHP and Oracle database connection pools efficiently How to use PHP and Oracle database connection pools efficiently Jul 12, 2023 am 10:07 AM

How to efficiently use connection pooling in PHP and Oracle databases Introduction: When developing PHP applications, using a database is an essential part. When interacting with Oracle databases, the use of connection pools is crucial to improving application performance and efficiency. This article will introduce how to use Oracle database connection pool efficiently in PHP and provide corresponding code examples. 1. The concept and advantages of connection pooling Connection pooling is a technology for managing database connections. It creates a batch of connections in advance and maintains a

Does oracle database require jdk? Does oracle database require jdk? Jun 05, 2023 pm 05:06 PM

The oracle database requires jdk. The reasons are: 1. When using specific software or functions, other software or libraries included in the JDK are required; 2. Java JDK needs to be installed to run Java programs in the Oracle database; 3. JDK provides Develop and compile Java application functions; 4. Meet Oracle's requirements for Java functions to help implement and implement specific functions.

How oracle determines whether a table exists in a stored procedure How oracle determines whether a table exists in a stored procedure Jul 06, 2023 pm 01:20 PM

Oracle's steps to determine whether a table exists in a stored procedure: 1. Use the "user_tables`" system table to query the table information under the current user, compare the incoming table name "p_table_name" with the "table_name" field, and if the conditions are met, then "COUNT(*)" will return a value greater than 0; 2. Use the "SET SERVEROUTPUT ON;" statement and the "EXEC`" keyword to execute the stored procedure and pass in the table name to determine whether the table exists.

How to use php to extend PDO to connect to Oracle database How to use php to extend PDO to connect to Oracle database Jul 29, 2023 pm 07:21 PM

How to use PHP to extend PDO to connect to Oracle database Introduction: PHP is a very popular server-side programming language, and Oracle is a commonly used relational database management system. This article will introduce how to use PHP extension PDO (PHPDataObjects) to connect to Oracle database. 1. Install the PDO_OCI extension. To connect to the Oracle database, you first need to install the PDO_OCI extension. Here are the steps to install the PDO_OCI extension: Make sure

See all articles