oracle 分区表空间迁移,oracle分区表的迁移

最新推荐文章于 2023-12-23 09:36:40 发布

weixin_39639049

最新推荐文章于 2023-12-23 09:36:40 发布

阅读量626

点赞数

文章标签： oracle 分区表空间迁移

本文详细介绍了如何使用Oracle数据库的exp/imp工具导出和导入分区表的数据。首先展示了分区表的基本信息，然后设置导出目录，并分别导出整个表和所有分区。接着，通过网络将导出文件传输到目标主机，并在目标数据库中导入所有分区。最后，验证了导入数据的正确性。整个过程涵盖了Oracle数据库管理中的关键操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

使用exp/imp导出导入分区表数据

1.分区表状态

SQL> select count(*) from test123;

COUNT(*)

----------

262145-----------------------------表的总条目

SQL> select count(1) from test123 partition(p1);

COUNT(1)

----------

262144-----------------------------p1分区所占条目

SQL> select count(1) from test123 partition(p2);

COUNT(1)

----------

1-----------------------------p2分区所占条目

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions;

TABLE_NAME PARTITION_NAME

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

TABLESPACE_NAME

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

TEST123 P1

TS01

TEST123 P2

TS02

TEST123 P3

TS03

TABLE_NAME PARTITION_NAME

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

TABLESPACE_NAME

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

TEST123 PMAX

TS04

2.导入、导出目录的设置

SQL> select directory_name,directory_path from dba_directories where DIRECTORY_NAME='DUMP_DIR';

DIRECTORY_NAME DIRECTORY_PATH

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

DUMP_DIR /home/oracle/dump

3.导出整个表

[oracle@jokesql2 admin]$ exp \'sys/123 as sysdba\' file='/home/oracle/dump' tables=test123

Export: Release 10.2.0.1.0 - Production on Mon Dec 17 22:32:54 2012

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table TEST123

. . exporting partition P1 262144 rows exported

. . exporting partition P2 1 rows exported

. . exporting partition P3 0 rows exported

. . exporting partition PMAX 0 rows exported

Export terminated successfully without warnings.

4.导出所有分区

[oracle@jokesql2 ~]$ exp \'sys/123 as sysdba\' file='/home/oracle/test123_p1' tables='(test123:p1,test123:p2,test123:p3,test123:pmax)'

Export: Release 10.2.0.1.0 - Production on Mon Dec 17 22:47:21 2012

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table TEST123

. . exporting partition P1 262144 rows exported

. . exporting partition P2 1 rows exported

. . exporting partition P3 0 rows exported

. . exporting partition PMAX 0 rows exported

Export terminated successfully without warnings.

5.将导出文件拷贝到目标主机目录

[oracle@jokesql2 ~]$ scp test* oracle@192.168.18.116:/home/oracle

oracle@192.168.18.116's password:

test123.dmp 100% 3856KB 3.8MB/s 00:00

test123_p1.dmp 100% 3856KB 3.8MB/s 00:01

6.导入所有分区(前提要有相应的表空间，我是采用手工方式建立的)

[oracle@jokesql admin]$ imp \'sys/123 as sysdba\' tables='(test123:p1,test123:p2,test123:p3,test123:pmax)' rows=y file='/home/oracle/test123_p1.dmp'

Import: Release 10.2.0.1.0 - Production on Mon Dec 17 23:19:22 2012

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses WE8ISO8859P1 character set (possible charset conversion)

export client uses WE8ISO8859P1 character set (possible charset conversion)

. importing SYS's objects into SYS

. . importing partition "TEST123":"P1" 262144 rows imported

. . importing partition "TEST123":"P2" 1 rows imported

. . importing partition "TEST123":"P3" 0 rows imported

. . importing partition "TEST123":"PMAX" 0 rows imported

Import terminated successfully without warnings.

能够看到分区条目了

SQL> select count(1) from test123 partition(p1);

COUNT(1)

----------

262144

7.导入整个表

[oracle@jokesql admin]$ imp \'sys/123 as sysdba\' tables=test123 rows=y file='/home/oracle/test123.dmp'

Import: Release 10.2.0.1.0 - Production on Mon Dec 17 23:24:13 2012

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses WE8ISO8859P1 character set (possible charset conversion)

export client uses WE8ISO8859P1 character set (possible charset conversion)

. importing SYS's objects into SYS

IMP-00015: following statement failed because the object already exists:

"CREATE TABLE "TEST123" ("ID" NUMBER, "CREATEDATE" DATE) PCTFREE 10 PCTUSED"

" 40 INITRANS 1 MAXTRANS 255 TABLESPACE "SYSTEM" LOGGING PARTITION BY RANGE "

"("CREATEDATE" ) (PARTITION "P1" VALUES LESS THAN (TO_DATE(' 2001-01-01 00:"

"00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 10 PC"

"TUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 9437184 FREELISTS 1 FREELI"

"ST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS01" LOGGING NOCOMPRESS, PART"

"ITION "P2" VALUES LESS THAN (TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD H"

"H24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 10 PCTUSED 40 INITRANS 1 MA"

"XTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL "

"DEFAULT) TABLESPACE "TS02" LOGGING NOCOMPRESS, PARTITION "P3" VALUES LESS T"

"HAN (TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA"

"R=GREGORIAN')) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITI"

"AL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS0"

"3" LOGGING NOCOMPRESS, PARTITION "PMAX" VALUES LESS THAN (MAXVALUE) PCTFRE"

"E 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 F"

"REELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS04" LOGGING NOCOMPRESS "

")"

Import terminated successfully with warnings.

查看

SQL> select count(*) from test123;

COUNT(*)

----------

262145