oracle分区表和分区表间转换
查询分区表分区内容
|
1 |
select
*
from
emp2 partition(emp2_p3); |
1 创建分区表 emp1
|
1
2
3
4
5
6
7
8
9 |
CREATE
TABLE
emp1(empno NUMBER(4)
NOT
NULL
,ename VARCHAR2(10),sal NUMBER(7))PARTITION
BY
RANGE(ename)(partition emp2_p1
VALUES
LESS THAN (
'D'
),partition emp2_p2
VALUES
LESS THAN (
'Q'
),partition emp2_p3
VALUES
LESS THAN (MAXVALUE)); |
2 创建分区表 emp2
|
1
2
3
4
5
6
7
8
9 |
CREATE
TABLE
emp2(empno NUMBER(4)
NOT
NULL
,ename VARCHAR2(10),sal NUMBER(7))PARTITION
BY
RANGE(ename)(partition emp2_p1
VALUES
LESS THAN (
'D'
),partition emp2_p2
VALUES
LESS THAN (
'Q'
),partition emp2_p3
VALUES
LESS THAN (MAXVALUE)); |
3 创建非分区表
|
1
2
3
4
5
6 |
create
table
emp_tmp(empno NUMBER(4)
NOT
NULL
,ename VARCHAR2(10),sal NUMBER(7)); |
或
|
1
2 |
create
table
emp_test
as
select
*
from
emp1
where
1=0;(创建emp_test表和emp1表结构一样但没有数据)create
table
emp_test
as
select
*
from
emp1;(创建emp_test表和emp1一模一样) |
4 插入数据
|
1
2
3
4
5 |
insert
into
emp2
values
(1,
'Andy'
,575);insert
into
emp2
values
(2,
'Derek'
,570);insert
into
emp2
values
(3,
'Patrick'
,590);insert
into
emp2
values
(4,
'Richard'
,400);insert
into
emp1
values
(5,
'Heidi'
,666); |
5 将 emp2_p2 和 emp1_p2 通过 emp_tmp 进行交换
|
1
2
3 |
alter
table
emp2 exchange partition emp2_p2
with
table
emp_tmp
UPDATE
GLOBAL
INDEXES;alter
table
emp1 exchange partition emp2_p2
with
table
emp_tmp
UPDATE
GLOBAL
INDEXES;alter
table
emp2 exchange partition emp2_p2
with
table
emp_tmp
UPDATE
GLOBAL
INDEXES; |
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70013625/viewspace-3087335/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Oracle Procedure IV
下一篇：
oracle分区表和非分区表转换
请登录后发表评论
登录
全部评论
