Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to import and export Oracle partition table data". In the daily operation, I believe that many people have doubts about how to import and export Oracle partition table data. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to import and export Oracle partition table data". Next, please follow the editor to study!

Importing Oracle partition table data is one of the tasks that Oracle DBA often accomplishes. Partition tables can also be imported and exported as regular tables, but the import and export needs to take into account the particularity of partitions, such as partitioning indexes, migrating partitions to regular tables, or using the original partition tables to import into new partition tables. Using imp/exp,impdp/expdp to import and export is described below.

Partition table data

I. Import and export at the partition level

You can export one or more partitions, or all partitions (that is, the entire table).

You can import all partitions (that is, entire tables), one or more partitions, and subpartitions.

For tables that already have data, you need to use the parameter IGNORE=y when importing using imp, and use impdp, plus the table_exists_action=append | replace parameter.

Second, create a demonstration environment

1. View the version of the current database

Select * from v$version where rownum

< 2; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production 2.创建一个分区表 alter session set nls_date_format='yyyy-mm-dd'; create table tb_pt ( sal_date date not null, sal_id number not null, sal_row number(12) not null) partition by range(sal_date) ( partition sal_11 values less than(to_date('2017-01-01','yyyy-mm-dd')) , partition sal_12 values less than(to_date('2019-01-01','yyyy-mm-dd')) , partition sal_13 values less than(to_date('2023-01-01','yyyy-mm-dd')) , partition sal_14 values less than(to_date('2025-01-01','yyyy-mm-dd')) , partition sal_15 values less than(to_date('2028-01-01','yyyy-mm-dd')) , partition sal_16 values less than(to_date('2030-01-01','yyyy-mm-dd')) , partition sal_other values less than (maxvalue) ) nologging; 3.创建一个唯一索引 create unique index tb_pt_ind1 on tb_pt(sal_date) nologging; 4.为分区表生成数据 ---插入数据 insert into tb_pt select trunc(sysdate)+rownum, dbms_random.random, rownum from dual connect by level 'SCOTT',tabname =>

'TB_PT', estimate_percent = > 100 methodological opt = >' for all indexed columns',cascade= > TRUE,granularity= > 'ALL')

Use exp/imp to export and import partition table data

1. Export the entire partition table

Exp scott/tiger file='/home/oracle/dmp/tb_pt.dmp' log='/home/oracle/dmp/tb_pt.log' tables=tb_pt

Note: make sure that the database character set is consistent with the character set specified by the operating system environment variables.

two。 Export a single partition

[oracle@slient dmp] $exp scott/tiger file='/home/oracle/dmp/tb_pt_sal_16.dmp' log='/home/oracle/dmp/tb_pt_sal_16.log' tables=tb_pt:sal_16

Export: Release 11.2.0.4.0-Production on Wed Aug 9 19:15:06 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path...

. . Exporting table TB_PT

. . Exporting partition SAL_16 731 rows exported

EXP-00091: Exporting questionable statistics.

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

[oracle@slient dmp] $

The statistics error occurred again in the above export process, so collecting statistics on the object does not resolve the error, but you can add statistics=none to the exp command line, as follows:

Exp scott/tiger file='/home/oracle/dmp/tb_pt_sal_16.dmp' log='/home/oracle/dmp/tb_pt_sal_16.log' tables=tb_pt:sal_16 statistics=none

If you want to export multiple partitions, increase the number of partitions in the tables parameter. For example, tables= "(tb_pt:sal_15,tb_pt:sal_16)"

3. Use the imp tool to generate DDL statements that create partition tables

Imp scott/tiger tables=tb_pt indexfile='/home/oracle/dmp/cr_tb_pt.sql' file='/home/oracle/dmp/tb_pt.dmp' ignore=y

Here we add a parameter to imp: the imp statement indexfile='/home/oracle/dmp/cr_tb_pt.sql', will only generate the ddl statement of the partition table in the corresponding file. Then edit and create it.

4. Import a single partition (import a file using a previously backed up single partition)

-- implement truncate for partitions before import

Select count (1) from tb_pt partition (sal_16)

COUNT (1)

-

seven hundred and thirty one

Alter table tb_pt truncate partition sal_16

Table truncated.

Select count (1) from tb_pt partition (sal_16)

COUNT (1)

-

0

Imp scott/tiger tables=tb_pt:sal_16 file='/home/oracle/dmp/tb_pt_sal_16.dmp' ignore=y

Export: Release 11.2.0.1.0-Production on Wed Mar 9 13:55:39 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP

Data Mining and Real Application Testing o

Export file created by EXPORT:V11.02.00 via conventional path

Import done in US7ASCII character set and AL16UTF16 NCHAR character set

Import server uses ZHS16GBK character set (possible charset conversion)

. Importing SCOTT's objects into SCOTT

. Importing SCOTT's objects into SCOTT

. . Importing partition "TB_PT": "SAL_16"

IMP-00058: ORACLE error 1502 encountered

ORA-01502: index 'SCOTT.TB_PT_IND1' or partition of such index is in unusable state

Import terminated successfully with warnings.

After receiving the ORA-01502 error, let's check the status of the index and re-index it before performing the import

-- View the status of the index

Select index_name, status from dba_indexes where table_name='TB_PT'

INDEX_NAME STATUS

TB_PT_IND1 UNUSABLE

-- rebuild the index

Alter index TB_PT_IND1 rebuild online

Index altered.

-- successfully imported again

Imp scott/tiger tables=tb_pt:sal_16 file='/home/oracle/dmp/tb_pt_sal_16.dmp' ignore=y

Select count (*) from tb_pt partition (sal_16)

COUNT (*)

-

seven hundred and thirty one

5. Import the entire table

First of all, truncate the whole table

Truncate table tb_pt

Table truncated.

Imp scott/tiger tables=tb_pt file='/home/oracle/dmp/tb_pt.dmp' ignore=y indexes=y

Select count (1) from tb_pt partition (sal_other)

COUNT (1)

-

3473

4. Use expdp/impdb to import and export partition tables.

1. View directory settings for import and export

SQL > select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from dba_directories

OWNER DIRECTORY_NAME DIRECTORY_PATH

SYS UTLFILE / home/oracle

SYS XMLDIR / u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml

SYS ORACLE_OCM_CONFIG_DIR / u01/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/slient/state

SYS DATA_PUMP_DIR / u01/app/oracle/admin/test/dpdump/

SYS ORACLE_OCM_CONFIG_DIR2 / u01/app/oracle/product/11.2.0/dbhome_1/ccr/state

SQL > grant read,write on directory DATA_PUMP_DIR to scott

Grant succeeded.

two。 Create a local index for the partitioned table

Create index tb_pt_local_idx

On tb_pt (sal_id)

Local

(partition local1

Partition local2

Partition local3

Partition local4

Partition local5

Partition local6

Partition local7)

3. Export the entire table

Expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=tb_pt.dmp logfile=tb_pb.log tables=tb_pt parallel=3

4. Export multiple partitions

Expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=tb_pts.dmp logfile=tb_pt.log tables= (tb_pt:sal_16,tb_pt:sal_other) parallel=2

-bash: syntax error near unexpected token `('

Check the script for errors: it turns out that you need to put double quotation marks in parentheses in linux 5.

Expdp scott/tiger directory=DATA_PUMP_DIR dumpfile=tb_pts.dmp logfile=tb_pt.log tables= "(tb_pt:sal_16,tb_pt:sal_other)" parallel=2

5. Truncate partition sal_other

Alter table tb_pt truncate partition (sal_other)

Table truncated.

SQL > select count (*) from tb_pt partition (sal_other)

COUNT (*)

-

0

-- View the status of the index. TB_PT_IND1 is not available

SQL > select index_name,status,partitioned from dba_indexes where table_name='TB_PT'

INDEX_NAME STATUS PAR

-

TB_PT_IND1 UNUSABLE NO

TB_PT_LOCAL_IDX N/A YES

Select index_name, partition_name, status from dba_ind_partitions where index_owner='SCOTT'

INDEX_NAME PARTITION_NAME STATUS

-

TB_PT_LOCAL_IDX LOCAL1 USABLE

TB_PT_LOCAL_IDX LOCAL2 USABLE

TB_PT_LOCAL_IDX LOCAL3 USABLE

TB_PT_LOCAL_IDX LOCAL4 USABLE

TB_PT_LOCAL_IDX LOCAL5 USABLE

TB_PT_LOCAL_IDX LOCAL6 USABLE

TB_PT_LOCAL_IDX LOCAL7 USABLE

6. Import a single partition

Impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=tb_pts.dmp logfile=tb_pt_imp.log tables=tb_pt:sal_other skip_unusable_indexes=y table_exists_action=replace

SQL > select index_name,status,partitioned from dba_indexes where table_name='TB_PT'

INDEX_NAME STATUS PAR

-

TB_PT_IND1 VALID NO

TB_PT_LOCAL_IDX N/A YES

As you can see from the import above, although truncate partition is executed, the impdp import tool is used, and the parameter table_exists_action=replace can be used to avoid the problem of unique and primary key indexes that need to be rebuilt when using imp import. Note that if you do not use the table_exists_action=replace parameter, you will receive an ORA-39151 error, as follows:

ORA-39151: Table "SCOTT". "TB_PT" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

7. Import the entire table

Impdp scott/tiger directory=DATA_PUMP_DIR dumpfile=tb_pt.dmp logfile=tb_pt_fullimp.log tables=tb_pt skip_unusable_indexes=y table_exists_action=replace

Fifth, the function of parameter skip_unusable_indexes

SQL > show parameter skip

NAME TYPE VALUE

-

Skip_unusable_indexes boolean TRUE

This parameter allows you to delay the processing of the index when you import partition data, that is, import the data first and then rebuild the index partition.

When the import parameter skip_unusable_indexes is not specified in the command line import, the problems related to the index are determined according to the value of the database initialization parameter.

When the parameter skip_unusable_indexes is specified in the command line import, the value of that parameter takes precedence over the set value of the database initialization parameter.

Skip_unusable_indexes=y has no effect on unique index, because the unique index plays the role of constraint at this time, so the index must be updated when insert data.

For PK,unique index processing when a single partition is imported, the index must be rebuilt and then imported.

Using the impdp data pump for import and using the parameter table_exists_action=replace can solve the above problem, that is, ORA-01502 error.

At this point, the study on "how to import and export Oracle partition table data" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.