【ORACLE数据库】-导入导出过程中get DDL复现过程
在处理问题的过程中,客户反馈在ORACLE
数据库中导入导出分区表,get 表的DDL,会出现表的所有分区信息,事实真的如此吗?复现过程如下:
1、创建基础表数据
select version from v$instance;
--结果:19.0.0.0.0
--oracle创建表
CREATE TABLE FUT_BGL_FQB_CJJG_001 (
SALES_ID NUMBER PRIMARY KEY,
SALEMAN VARCHAR2(10), -- Oracle中通常使用VARCHAR2
SALEDATE DATE,
SALECOUNT INT,
CITY VARCHAR2(10)
)
PARTITION BY RANGE (SALEDATE)
INTERVAL (NUMTOYMINTERVAL(1, 'YEAR'))
(
PARTITION P1 VALUES LESS THAN (TO_DATE('2019-09-01', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2020-09-01', 'YYYY-MM-DD'))
);
--查询表的分区情况
SELECT table_name, partition_name, high_value
FROM dba_tab_partitions
WHERE table_name = 'FUT_BGL_FQB_CJJG_001'
ORDER BY partition_position;
--结果:p1和p2两个分区
--插入数据触发自动分区
insert into FUT_BGL_FQB_CJJG_001 (SALES_ID,SALEDATE) values(1,TO_DATE('2020-09-02', 'YYYY-MM-DD'));
insert into FUT_BGL_FQB_CJJG_001 (SALES_ID,SALEDATE) values(2,TO_DATE('2021-09-02', 'YYYY-MM-DD'));
insert into FUT_BGL_FQB_CJJG_001 (SALES_ID,SALEDATE) values(3,TO_DATE('2022-09-02', 'YYYY-MM-DD'));
insert into FUT_BGL_FQB_CJJG_001 (SALES_ID,SALEDATE) values(4,TO_DATE('2023-09-02', 'YYYY-MM-DD'));
--提交
commit;
--查询表中数据
select * from FUT_BGL_FQB_CJJG_001;
--结果
--1 2020-09-02 00:00:00
--2 2021-09-02 00:00:00
--3 2022-09-02 00:00:00
--4 2023-09-02 00:00:00
--查询表的分区情况
SELECT table_name, partition_name, high_value
FROM dba_tab_partitions
WHERE table_name = 'FUT_BGL_FQB_CJJG_001'
ORDER BY partition_position;
--结果:6个分区
--FUT_BGL_FQB_CJJG_001 P1 TO_DATE(' 2019-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
--FUT_BGL_FQB_CJJG_001 P2 TO_DATE(' 2020-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
--FUT_BGL_FQB_CJJG_001 SYS_P261 TO_DATE(' 2021-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
--FUT_BGL_FQB_CJJG_001 SYS_P262 TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
--FUT_BGL_FQB_CJJG_001 SYS_P263 TO_DATE(' 2023-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
--FUT_BGL_FQB_CJJG_001 SYS_P264 TO_DATE(' 2024-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
--get表的ddl
--参考链接:https://blog.csdn.net/whl826661099/article/details/89337633
select dbms_metadata.get_ddl('TABLE','FUT_BGL_FQB_CJJG_001') from dual;
--只能看到创建表时两个原始分区的DDL
2、导出导入
--⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐导出⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐--参考链接:https://www.cnblogs.com/Jingkunliu/p/13705626.html
--创建目录
create or replace directory dmp as '/u01/app/oracle/exp/' --查看:SELECT * FROM dba_directories;
--提交
commit;
--导出,加上directory=dmp会报错,命令如下:
--expdp SYSTEM/abc123@LOCALHOST/orcl directory=dmp dumpfile=export.dmp logfile=export.log tables=FUT_BGL_FQB_CJJG_001
--不加directory=dmp,会自动的导出到/u01/app/oracle/admin/orcl/dpdump/下,并不会导出到/u01/app/oracle/exp/下
expdp SYSTEM/abc123@LOCALHOST/orcl dumpfile=export.dmp logfile=export.log tables=FUT_BGL_FQB_CJJG_001
--无报错
--⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐删除表⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐
drop TABLE FUT_BGL_FQB_CJJG_001;
--⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐导入⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐
--参考链接:https://www.cnblogs.com/farmer-y/p/5888432.html
--参考链接:https://blog.csdn.net/leftfist/article/details/80738170
--导入,加上directory=dmp会报错,命令如下:impdp SYSTEM/abc123@LOCALHOST/orcl directory=dmp dumpfile=export.dmp tables=FUT_BGL_FQB_CJJG_001
--不加directory=dmp,会自动的从/u01/app/oracle/admin/orcl/dpdump/下导入
impdp SYSTEM/abc123@LOCALHOST/orcl dumpfile=export.dmp tables=FUT_BGL_FQB_CJJG_001
--. . imported "SYSTEM"."FUT_BGL_FQB_CJJG_001":"P1" 0 KB 0 rows
--. . imported "SYSTEM"."FUT_BGL_FQB_CJJG_001":"P2" 0 KB 0 rows
--. . imported "SYSTEM"."FUT_BGL_FQB_CJJG_001":"SYS_P261" 6.781 KB 1 rows
--. . imported "SYSTEM"."FUT_BGL_FQB_CJJG_001":"SYS_P262" 6.781 KB 1 rows
--. . imported "SYSTEM"."FUT_BGL_FQB_CJJG_001":"SYS_P263" 6.781 KB 1 rows
--. . imported "SYSTEM"."FUT_BGL_FQB_CJJG_001":"SYS_P264" 6.781 KB 1 rows
--补充:分区表只能通过impdp导入,imp导入会报错
--查询表中数据
select * from FUT_BGL_FQB_CJJG_001;
--结果
--1 2020-09-02 00:00:00
--2 2021-09-02 00:00:00
--3 2022-09-02 00:00:00
--4 2023-09-02 00:00:00
--查询表的分区情况
SELECT table_name, partition_name, high_value
FROM dba_tab_partitions
WHERE table_name = 'FUT_BGL_FQB_CJJG_001'
ORDER BY partition_position;
--结果:6个分区
--FUT_BGL_FQB_CJJG_001 P1 TO_DATE(' 2019-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
--FUT_BGL_FQB_CJJG_001 P2 TO_DATE(' 2020-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
--FUT_BGL_FQB_CJJG_001 SYS_P261 TO_DATE(' 2021-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
--FUT_BGL_FQB_CJJG_001 SYS_P262 TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
--FUT_BGL_FQB_CJJG_001 SYS_P263 TO_DATE(' 2023-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
--FUT_BGL_FQB_CJJG_001 SYS_P264 TO_DATE(' 2024-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
--get表的ddl
select dbms_metadata.get_ddl('TABLE','FUT_BGL_FQB_CJJG_001') from dual;
--只能看到创建表时两个原始分区的DDL
3、结论
ORACLE
数据库导出导入过程中能看到创建表时两个原始分区的DDL,不能看到插入数据触发系统自动分区的内容,客户反馈内容错误。