如何将一个普通的非分区表进行分区1 Export/import方式
参考文档：
如何将一个普通的非分区表进行分区 (Doc ID 1985005.1)
第一种方式： 通过 Export/import 方法进行转换
这种方法的实现是先 export/exp 一个非分区表，创建一个新的分区表，然后 import/imp 数据到新创建的分区表中。
- Exp你的非分区表：
[oracle@zc ~]$ exp partion/partion tables=PAR_TABLE file=PAR_TABLE.dmp
Export: Release 19.0.0.0.0 - Production on Mon Jan 20 17:41:52 2025
Version 19.3.0.0.0
Copyright © 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path …
. . exporting table PAR_TABLE 1002111 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.
- Drop 掉该非分区表：
SQL> drop table PAR_TABLE;
Table dropped.
- 重新创建该表成为一个分区表：
CREATE TABLE par_table (
a NUMBER, y number,
name VARCHAR2(100),date_used DATE)
PARTITION BY RANGE (date_used)
(PARTITION unpar_table_12 VALUES LESS THAN (TO_DATE(‘10/05/2015’, ‘DD/MM/YYYY’)),
PARTITION unpar_table_15 VALUES LESS THAN (TO_DATE(‘15/05/2015’, ‘DD/MM/YYYY’)),
PARTITION unpar_table_MX VALUES LESS THAN (MAXVALUE));
- 通过 import 的 ignore=y 方式来还原备份的数据：
[oracle@zc ~]$ imp partion/partion file=PAR_TABLE.dmp TABLES=PAR_TABLE ignore=y
Import: Release 19.0.0.0.0 - Production on Mon Jan 20 17:54:27 2025
Version 19.3.0.0.0
Copyright © 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Export file created by EXPORT:V19.00.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
IMP-00403:
Warning: This import generated a separate SQL file “import_sys” which contains DDL that failed due to a privilege issue.
. importing PARTION’s objects into PARTION
. importing PARTION’s objects into PARTION
. . importing table “PAR_TABLE” 1002111 rows imported
Import terminated successfully with warnings.
ignore=y 语句会让 import 忽略掉表的创建，直接加载所有数据。
- 查询分区信息：
SELECT partitioned FROM user_tables WHERE table_name = ‘PAR_TABLE’;
