`
csstome
  • 浏览: 1467793 次
  • 性别: Icon_minigender_1
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

导入导出 Oracle 分区表数据

 
阅读更多

--****************************

-- 导入导出 Oracle 分区表数据

--****************************

导入导入Oracle 分区表数据是Oracle DBA 经常完成的任务之一。分区表的导入导出同样普通表的导入导出方式,只不过导入导出需要考

虑到分区的特殊性,如分区索引,将分区迁移到普通表,或使用原始分区表导入到新的分区表。下面将描述使用imp/expimpdp/expdp导入导出

分区表数据。

有关分区表的特性请参考:

Oracle 分区表

SQL server 2005 切换分区表

SQL server 2005 基于已存在的表创建分区

有关导入导出工具请参考:

数据泵EXPDP 导出工具的使用

数据泵IMPDP 导入工具的使用

有关导入导出的官方文档请参考:

Original Export and Import

一、分区级别的导入导出

可以导出一个或多个分区,也可以导出所有分区(即整个表)

可以导入所有分区(即整个表),一个或多个分区以及子分区。

对于已经存在数据的表,使用imp导入时需要使用参数IGNORE=y,而使用impdp,加table_exists_action=append | replace 参数。

二、创建演示环境

1.查看当前数据库的版本

SQL> select * from v$version where rownum < 2;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

2.创建一个分区表

SQL> alter session set nls_date_format='yyyy-mm-dd';

SQL> 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('2012-01-01','YYYY-MM-DD')) ,

partition sal_12 values less than(to_date('2013-01-01','YYYY-MM-DD')) ,

partition sal_13 values less than(to_date('2014-01-01','YYYY-MM-DD')) ,

partition sal_14 values less than(to_date('2015-01-01','YYYY-MM-DD')) ,

partition sal_15 values less than(to_date('2016-01-01','YYYY-MM-DD')) ,

partition sal_16 values less than(to_date('2017-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.为分区表生成数据

SQL> INSERT INTO tb_pt

SELECT TRUNC(SYSDATE)+ROWNUM, dbms_random.random, ROWNUM

FROM dual

CONNECT BY LEVEL<=5000;

SQL> commit;

SQL> select count(1) from tb_pt partition(sal_11);

COUNT(1)

----------

300

SQL> select count(1) from tb_pt partition(sal_other);

COUNT(1)

----------

2873

SQL> select * from tb_pt partition(sal_12) where rownum < 3;

SAL_DATE SAL_ID SAL_ROW

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

01-JAN-12 -1.356E+09 301

02-JAN-12 -761530183 302

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

1.导出整个分区表

[oracle@node1 ~]$ exp scott/tiger file='/u02/dmp/tb_pt.dmp' log='/u02/dmp/tb_pt.log' tables=tb_pt

Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:52:18 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 done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...

. . exporting table TB_PT

. . exporting partition SAL_11 300 rows exported

. . exporting partition SAL_12 366 rows exported

. . exporting partition SAL_13 365 rows exported

. . exporting partition SAL_14 365 rows exported

. . exporting partition SAL_15 365 rows exported

. . exporting partition SAL_16 366 rows exported

. . exporting partition SAL_OTHER 2873 rows exported

EXP-00091: Exporting questionable statistics.

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

[oracle@node1 ~]$ oerr exp 00091

00091, 00000, "Exporting questionable statistics."

// *Cause: Export was able export statistics, but the statistics may not be

// usuable. The statistics are questionable because one or more of

// the following happened during export: a row error occurred, client

// character set or NCHARSET does not match with the server, a query

// clause was specified on export, only certain partitions or

// subpartitions were exported, or a fatal error occurred while

// processing a table.

// *Action: To export non-questionable statistics, change the client character

// set or NCHARSET to match the server, export with no query clause,

// export complete tables. If desired, import parameters can be

// supplied so that only non-questionable statistics will be imported,

// and all questionable statistics will be recalculated.

在上面的导出中出现了错误提示,即EXP-00091,该错误表明exp工具所在的环境变量中的NLS_LANGDB中的NLS_CHARACTERSET不一致

尽管该错误对最终的数据并无影响,但调整该参数来避免异常还是有必要的。因此需要将其设置为一致即可解决上述的错误提示。

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')

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

AMERICAN_AMERICA.ZHS16GBK

[oracle@node1 ~]$ export NLS_LANG='AMERICAN_AMERICA.ZHS16GBK'

经过上述设置之后再次导出正常,过程略。

2.导出单个分区

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

Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:52:38 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 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 366 rows exported

EXP-00091: Exporting questionable statistics.

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings

在上面的导出过程中再次出现了统计信息错误的情况,因此采取了对该对象收集统计信息,但并不能解决该错误,但在exp命令行中增

statistics=none即可,如下:

[oracle@node1 ~]$ exp scott/tiger file='/u02/dmp/tb_pt_sal_16.dmp' log='/u02/dmp/tb_pt_sal_16.log' /

> tables=tb_pt:sal_16 statistics=none

如果要导出多个分区,则在tables参数中增加分区数。如:tables=(tb_pt:sal_15,tb_pt:sal_16)

3.使用imp工具生成创建分区表的DDL语句

[oracle@node1 ~]$ imp scott/tiger tables=tb_pt indexfile='/u02/dmp/cr_tb_pt.sql' /

> file='/u02/dmp/tb_pt.dmp' ignore=y

Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:54:38 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)

. . skipping partition "TB_PT":"SAL_11"

. . skipping partition "TB_PT":"SAL_12"

. . skipping partition "TB_PT":"SAL_13"

. . skipping partition "TB_PT":"SAL_14"

. . skipping partition "TB_PT":"SAL_15"

. . skipping partition "TB_PT":"SAL_16"

. . skipping partition "TB_PT":"SAL_OTHER"

Import terminated successfully without warnings.

4.导入单个分区(使用先前备份的单个分区导入文件)

SQL> alter table tb_pt truncate partition sal_16; --导入前先将分区实现truncate

Table truncated.

SQL> select count(1) from tb_pt partition(sal_16);

COUNT(1)

----------

0

SQL> ho imp scott/tiger tables=tb_pt:sal_16 file='/u02/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.

收到了ORA-01502错误,下面查看索引的状态,并对其重建索引后再执行导入

SQL> select index_name ,status from dba_indexes where table_name='TB_PT'; --查看索引的状态

INDEX_NAME STATUS

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

TB_PT_IND1 UNUSABLE

SQL> alter index TB_PT_IND1 rebuild online; --重建索引

Index altered.

SQL> ho imp scott/tiger tables=tb_pt:sal_16 file='/u02/dmp/tb_pt_sal_16.dmp' ignore=y --再次导入成功

Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:56:15 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" 366 rows imported

Import terminated successfully without warnings.

SQL> select count(*) from tb_pt partition(sal_16);

COUNT(*)

----------

366

5.导入整个表

SQL> truncate table tb_pt; --首先truncate 整个表

Table truncated.

SQL> ho imp scott/tiger tables=tb_pt file='/u02/dmp/tb_pt.dmp' ignore=y indexes=y

Export: Release 11.2.0.1.0 - Production on Wed Mar 9 13:57:10 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_11" 298 rows imported

. . importing partition "TB_PT":"SAL_12" 366 rows imported

. . importing partition "TB_PT":"SAL_13" 365 rows imported

. . importing partition "TB_PT":"SAL_14" 365 rows imported

. . importing partition "TB_PT":"SAL_15" 365 rows imported

. . importing partition "TB_PT":"SAL_16" 366 rows imported

. . importing partition "TB_PT":"SAL_OTHER" 2875 rows imported

Import terminated successfully without warnings.

SQL> select count(1) from tb_pt partition(sal_other);

COUNT(1)

----------

2875

四、使用expdp/impdb来实现分区表的导入导出

1.查看导入导出的目录设置

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

DIRECTORY_NAME DIRECTORY_PATH

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

DMP /u02/dmp

2.为分区表创建一个本地索引

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.导出整个表

[oracle@node1 ~]$ expdp scott/tiger directory=dmp dumpfile=tb_pt.dmp logfile=tb_pb.log tables=tb_pt parallel=3

Export: Release 11.2.0.1.0 - Production on Wed Mar 9 14:04:28 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 options

Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=dmp dumpfile=tb_pt.dmp logfile=tb_pb.log tables=

tb_pt parallel=3

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 512 KB

. . exported "SCOTT"."TB_PT":"SAL_OTHER" 71.63 KB 2875 rows

. . exported "SCOTT"."TB_PT":"SAL_11" 12.54 KB 298 rows

. . exported "SCOTT"."TB_PT":"SAL_12" 14.22 KB 366 rows

. . exported "SCOTT"."TB_PT":"SAL_13" 14.18 KB 365 rows

. . exported "SCOTT"."TB_PT":"SAL_14" 14.18 KB 365 rows

. . exported "SCOTT"."TB_PT":"SAL_15" 14.19 KB 365 rows

. . exported "SCOTT"."TB_PT":"SAL_16" 14.23 KB 366 rows

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/u02/dmp/tb_pt.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:04:51

4.导出多个分区

[oracle@node1 ~]$ expdp scott/tiger directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt.log /

> tables=(tb_pt:sal_16,tb_pt:sal_other) parallel=2

Export: Release 11.2.0.1.0 - Production on Wed Mar 9 14:08:06 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 options

Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt.log

tables=(tb_pt:sal_16,tb_pt:sal_other) parallel=2 --*/

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 192 KB

. . exported "SCOTT"."TB_PT":"SAL_OTHER" 71.63 KB 2875 rows

. . exported "SCOTT"."TB_PT":"SAL_16" 14.23 KB 366 rows

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

/u02/dmp/tb_pts.dmp

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 14:08:17

5.截断分区sal_other

SQL> alter table tb_pt truncate partition(sal_other);

Table truncated.

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

COUNT(*)

----------

0

SQL> select index_name,status,partitioned from dba_indexes where table_name='TB_PT'; --查看索引的状态, TB_PT_IND1不可用

INDEX_NAME STATUS PAR

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

TB_PT_IND1 UNUSABLE NO

TB_PT_LOCAL_IDX N/A YES

SQL> 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.导入单个分区

[oracle@node1 ~]$ impdp scott/tiger directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt_imp.log /

> tables=tb_pt:sal_other skip_unusable_indexes=y table_exists_action=replace

Import: Release 11.2.0.1.0 - Production on Wed Mar 9 14:13:28 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 options

Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=dmp dumpfile=tb_pts.dmp logfile=tb_pt_imp.log

tables=tb_pt:sal_other skip_unusable_indexes=y table_exists_action=replace --*/

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT"."TB_PT":"SAL_OTHER" 71.63 KB 2875 rows

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 14:13:33

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

从上面的导入情况可以看出,尽管执行了truncate partition,然而使用impdp导入工具,并且使用参数table_exists_action=replace

可以避免使用imp导入时唯一和主键索引需要重建的问题。注意,如果没有使用table_exists_action=replace参数,将会收到ORA-39151

错误,如下

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

table_exists_action of skip

7.导入整个表

[oracle@node1 ~]$ impdp scott/tiger directory=dmp dumpfile=tb_pt.dmp logfile=tb_pt_fullimp.log /

> tables=tb_pt skip_unusable_indexes=y table_exists_action=replace

Import: Release 11.2.0.1.0 - Production on Wed Mar 9 14:17:35 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 options

Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=dmp dumpfile=tb_pt.dmp logfile=tb_pt_fullimp.log

tables=tb_pt skip_unusable_indexes=y table_exists_action=replace --*/

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT"."TB_PT":"SAL_OTHER" 71.63 KB 2875 rows

. . imported "SCOTT"."TB_PT":"SAL_11" 12.54 KB 298 rows

. . imported "SCOTT"."TB_PT":"SAL_12" 14.22 KB 366 rows

. . imported "SCOTT"."TB_PT":"SAL_13" 14.18 KB 365 rows

. . imported "SCOTT"."TB_PT":"SAL_14" 14.18 KB 365 rows

. . imported "SCOTT"."TB_PT":"SAL_15" 14.19 KB 365 rows

. . imported "SCOTT"."TB_PT":"SAL_16" 14.23 KB 366 rows

Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 14:17:40

五、参数skip_unusable_indexes的作用

SQL> show parameter skip

NAME TYPE VALUE

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

skip_unusable_indexes boolean TRUE

该参数允许在导入分区数据时延迟对索引的处理,即先将数据导入,导入后再来重建索引分区。

在命令行导入中未指定导入参数skip_unusable_indexes时,则对于索引相关的问题,根据数据库初始化参数的值来确定。

在命令行导入中如果指定了参数skip_unusable_indexes时,则该参数的值优先于数据库初始化参数的设定值。

skip_unusable_indexes=yunique index不起作用,因为此时的unique index扮演者constraint的作用,所以在insert数据时index必须被

更新。

对于单个分区导入时PKunique index的处理,必须先重建索引然后进行导入。

使用impdp数据泵实现导入并使用参数table_exists_action=replace可以解决上述问题,即ORA-01502错误。

六、更多参考

有关性能优化请参考

Oracle 硬解析与软解析

共享池的调整与优化(Shared pool Tuning)

Buffer cache 的调整与优化(一)

Oracle 表缓存(caching table)的使用

有关闪回特性请参考

Oracle 闪回特性(FLASHBACK DATABASE)

Oracle 闪回特性(FLASHBACK DROP & RECYCLEBIN)

Oracle 闪回特性(Flashback Query、Flashback Table)

Oracle 闪回特性(Flashback Version、Flashback Transaction)

有关基于用户管理的备份和备份恢复的概念请参考:

Oracle 冷备份

Oracle 热备份

Oracle 备份恢复概

Oracle 实例恢复

Oracle 基于用户管理恢复的处理(详细描述了介质恢复及其处理)

有关RMAN的恢复与管理请参考:

RMAN 概述及其体系结构

RMAN 配置、监控与管理

RMAN 备份详解

RMAN 还原与恢复

有关Oracle体系结构请参考:

Oracle 实例和Oracle数据库(Oracle系结构)

Oracle 表空间与数据文件

Oracle 密码文件

Oracle 参数文件

Oracle 数据库实例启动关闭过程

Oracle 联机重做日志文件(ONLINE LOG FILE)

Oracle 控制文件(CONTROLFILE)

Oracle 归档日志

分享到:
评论

相关推荐

    创建导入导出Oracle分区表数据[参照].pdf

    创建导入导出Oracle分区表数据[参照].pdf

    使用PLSQL导入导出oracle数据

    使用PLSQL导入导出oracle数据的几种方式说明

    oracle11g expdp impdp 分区表重映射导出导入数据迁移方案

    oracle expdp impdp 分区表重映射导出导入 数据迁移方案,以SI01用户为例子,将用户分区表导出后,将分区表重映射到新的表空间,完成数据迁移和检查。照方案例子按步去做,一定能成功。

    ORACLE 常用手册导入导出

    --导出ORACLE参数 参数 说明 USERID 确定执行导出实用程序的用户名和口令 BUFFER 确定导出数据时所使用的缓冲区大小,其大小用字节表示 FILE 指定导出的二进制文件名称,默认的扩展名是.dmp FULL 指定是否以全部...

    oracle 常用语句 oracle备份 oracle冷备份 oracle启停 oracle导出 oracle导入

    --导出ORACLE参数 参数 说明 USERID 确定执行导出实用程序的用户名和口令 ..................(见附件) --导出 --全库导出 ..................(见附件) --按用户方式导出 ..................(见附件) --按表方式导出 ...

    Oracle表分区 建表空间 创建用户

    Oracle的相关知识,建表空间,创建用户,给用户授权, 删除用户,给表多列加锁,导出和导入,范围分区,散列分区,列表分区,复合分区、、、

    oracle文本导入导出工具-ozoradts

    sub = 对应表分区 (可选) mode = 导入操作 INSERT/APPEND/REPLACE/TRUNCATE (默认INSERT) buffer = 转换缓冲区大小 (UNIT:MB 默认16) reclen = 记录最大长度,8-2048 (默认2048) parallel = 并行导入 (默认FALSE...

    oracle数据同步到Greenplum的python脚本

    DataBase:判断表是否存在、表或分区表是否有数据、是否为分区表、数据导出、表分区遍历、指定分区表导出等方法 py_main:主程序目录: Main_Mem.py:主程序目录: 运行:python3 Main_Mem.py ProcessLog1 ...

    Oracle数据库恢复工具Oracle Database Unloader(ODU)3.09

    ODU全称为Oracle Database Unloader,是类似于Oracle的DUL的软件,用于直接从Oracle数据库的数据文件中获取表数据。在各种原因造成的数据库不能打开时,用于抢救数据,最大限度地减少数据丢失。 ODU有什么功能特点...

    oracle数据库修复

    以纯文本导出时,能够自动生成建表的SQL语句和SQL*Loader导入所需的control文件 模拟Oracle的dump块功能,能够dump数据文件中的数据块 支持DESC表,以显示表的列定义 支持列出表的分区和子分区

    transferdb:Transferdb支持异步数据库模式转换,全量数据导入,导入和增量数据同步功能(Oracle数据库-> MySQL数据库)

    考虑到Oracle分区表特殊且MySQL数据库复杂分区可能不支持,分区表统一认为普通表转换,但是reverse阶段日志中会打印警告【分区表】,若有要求,建议反向之后检查,需手工转换 支持自定义配置表分段类型规则转换(表-...

    Oracle数据库管理员技术指南

    3.4.1 子分区的导出和导入 3.4.2 导出/导入多个转储文件 3.4.3 为卸载表的导出过程的选择语句 指定一个查询 3.4.4 导出/导入预计算优化程序统计 数据 3.4.5 可移动表空间 3.5 回顾 第4章 设计高可用性数据库...

    oracle讲义全内容

    1 oracle10 g数据库安装 启动与关闭 2 Oracle 10g数据库结构 2.3 数据字典 3 SQL语句基础 3.4ORACLE函数大全 ...10 导出与导入 11 oracle 10g RMAN使用简介 触发器属性 手工创建数据库完整步骤 行迁移行链接

    赤兔Oracle数据库恢复软件 v11.6.zip

    31.支持对误删除数据的恢复,即使被删除数据的表中有LOB列,即使被删除数据的表中对应row directory中所有记录的offset都已经完全被Oracle清除 赤兔Oracle数据库恢复软件软件安装   把下载好的安装包解压到...

    PRM-DUL Oracle(数据库恢复工具) v4.1.zip

    》》支持各种表,包括普通的HEAP表和聚簇(CLUSTER)表数据恢复 》》支持表被truncate后的数据恢复 》》支持表被drop后的数据恢复 》》支持在没有SYSTEM表空间和数据字典损坏的情况下的非字典模式数据恢复,并能...

    Oracle从基础到熟练(太实用了)

    一. oracle基础: ①:如何进入Oracle ②:常见的开发工具 ③:创建/DB/TB/用户 授权等操作 二. oracle体系结构组件 ①:oracle完整的体系结构 ②:oracle实例的组成 ... ①:导入导出/整个DB/表空间/表/用户 十三.其他

    oracle常用命令-一完整的电子商务网案例

    电子商务网站案例下载:(数据库脚本,java调用示例,.net...Oracle常用命令12(导入:imp、导出:exp) Oracle常用命令13(数据库的启动、关闭) Oracle常用命令14(.net / java代码调用(sql代码、程序包过程))

Global site tag (gtag.js) - Google Analytics