资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

立即前往
首页/文章专栏/Oracle 非分区表转换为分区表

Oracle 非分区表转换为分区表

IT那活儿 / 2324人阅读
Oracle 非分区表转换为分区表

点击上方“IT那活儿”，关注后了解更多内容，不管IT什么活儿，干就完了！！！





11g环境




1. 创建实验表和实验数据


----------
SQL> create table test_p (id number);
Table created.
SQL> declare
i number:=1;
begin
for i in 1..3000 loop
insert into scott.test_p values(i);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
2. 通过CTAS重建表为分区表
1）简答易用，采用DDL语句，产生的redo较少；
2）数据一致性差；
3）适于修改不频繁的表。
----------
SQL> create table test_p2
partition by range (id)
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (3000),
partition p4 values less than (maxvalue)
)
as
select * from test_p;
Table created.
3. 在线重定义online redefinition
3.1 为实验表创建索引
----------
SQL> create index t_p on test_p(id);
Index created.


3.2 检查实验表是否可以在线重定义

1）保证数据的一致性；
2）表必须有主键；
3）需要有中间表；
4）必须有足够两份表数据的空间。
通过报错了解，当前表缺少主键：
----------
SQL> exec dbms_redefinition.can_redef_table( scott,test_p,dbms_redefinition.cons_use_pk);
BEGIN dbms_redefinition.can_redef_table( scott,test_p,dbms_redefinition.cons_use_pk); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "SCOTT"."TEST_P" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 143
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1635
ORA-06512: at line 1
3.3 创建主键重新检查通过
----------
SQL> alter table test_p add constraint pt_p primary key(id);
Table altered.
SQL> exec dbms_redefinition.can_redef_table( scott,test_p,dbms_redefinition.cons_use_pk);
PL/SQL procedure successfully completed.
3.4 新建临时分区表并添加主键约束
----------
SQL> create table p_temp (id number)
partition by range (id)
(
partition p1 values less than(1000),
partition p2 values less than(2000),
partition p3 values less than(3000),
partition p4 values less than(maxvalue)
);
Table created.
SQL> alter table p_temp add constraint pp_temp primary key(id);
Table altered.
3.5 启动在线重定义
----------
SQL> exec dbms_redefinition.start_redef_table(scott, test_p, p_temp);
PL/SQL procedure successfully completed.
3.6 结束在线重定义并检查结果
----------
SQL> exec dbms_redefinition.finish_redef_table(scott, test_p, p_temp);
PL/SQL procedure successfully completed.
SQL> select table_name,partition_name from user_tab_partitions where table_name=TEST_P;
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TEST_P P1
TEST_P P2
TEST_P P3
TEST_P P4






12c环境下



相对于11g中的方法，12c的新特性更方便使用（利用12c的新特性）。
存在的部分限制：
  • 它不能被用来分割一个索引有序的表(IOT)；
  • 如果表有域索引，就不能使用它；
  • 只能在脱机模式下将表转换为reference-partitioned子表。
1. 创建实验表和数据
----------
SQL> create table test_p (id number);
Table created.
SQL> declare
i number:=1;
begin
for i in 1..3000 loop
insert into scott.test_p values(i);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
2. 查看当前表的分区情况
----------
SQL> select table_name,partition_name from user_tab_partitions where table_name=TEST_P;
no rows selected
3. 为实验表添加主键约束
----------
SQL> alter table test_p add constraint tp_p primary key (id);
Table altered.
4. 利用12c的新特性更改非分区表
----------
SQL> alter table test_p modify
partition by range (id)
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (3000),
partition p4 values less than (maxvalue)
)
update indexes (tp_p global);
Table altered.
5. 查看当前表的分区情况
----------
SQL> select table_name,partition_name from user_tab_partitions where table_name=TEST_P;
TABLE_NAME PARTITION_NAME
------------------------------
TEST_P P1
TEST_P P2
TEST_P P3
TEST_P P4






对分区表进行扩展的两种情况



1. 当最大的分区的值有界限
1.1 创建实验表
----------
SQL> create table test_p2 (id number)
partition by range (id)
(
partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (3000)
);
Table created.
1.2 查看当前分区情况：
----------
SQL> select table_name,partition_name from user_tab_partitions where table_name=TEST_P2;
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TEST_P2 P1
TEST_P2 P2
TEST_P2 P3
1.3 添加分区：
----------
SQL> alter table test_p2 add partition p4 values less than (4000);
Table altered.
1.4 查看添加后的分区：
----------
SQL> select table_name,partition_name from user_tab_partitions where table_name=TEST_P2;
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TEST_P2 P1
TEST_P2 P2
TEST_P2 P3
TEST_P2 P4
2. 当最大分区的值为maxvalue时
这时直接添加分区会返回报错，因为添加的分区的值应该大于上一个分区，但上一个分区为maxvalue显然通过这个方法无法实现分区的添加。
----------
SQL> alter table test_p add partition p5 values less than (4000);
alter table test_p add partition p5 values less than (4000)
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition


此时，可以通过分割存在maxvalue的分区来实现对分区的增加：


----------
SQL> alter table test_p split partition p4 at (4000) into (partition p5,partition p4);
Table altered.






Dblink的创建



1. 配置tns文件
2. 查看当前用户的权限
SQL> conn scott/oracle
Connected.
SQL> select * from user_sys_privs;
USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO


赋予当前用户创建dblink的权限：


----------
SQL> grant create public database link to scott;
Grant succeeded.
SQL> grant create database link to scott;
Grant succeeded.
再次确认权限：
----------
SQL> select * from user_sys_privs;

USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT CREATE DATABASE LINK NO
SCOTT UNLIMITED TABLESPACE NO
SCOTT CREATE PUBLIC DATABASE LINK NO
3. 创建公共dblink
4. 查看创建结果并测试



本文作者：王 刚

本文来源：IT那活儿（上海新炬王翦团队）

GPU云服务器 云服务器 oracle分区表 linux分区和磁盘格式转换 linux磁盘分区扩展分区 linux分区无硬盘分区

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129605.html

相关文章

  • DBASK问答集萃（2）

    摘要：新晋技术专家下面是墨天轮部分新晋的技术专家。大家可以点击往期阅读墨天轮技术专家邀请函了解详情，申请成为我们的技术专家，加入专家团队，与我们一起创建一个开放互助的数据库技术社区。新关联公众号墨天轮是一个开放互助的数据库技术社区。 引言 近期我们在DBASK小程序增加了数据库 MongoDB、Redis、 Elasticsearch、DB2、Weblogic 等新的的专题栏目和一些新的技术...

    liuchengxu 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师
我要私信

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<