标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,604)
- DB2 (22)
- MySQL (71)
- Oracle (1,468)
- Data Guard (50)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle 23ai (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (532)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- read_me_recover_tn勒索恢复
- WINDOWS 下用dg broker搭建ADG(单机to单机)
- 存储故障后oracle报—ORA-01122/ORA-01207故障处理
- Oracle 23ai rm redo*.log恢复
- Oracle 发布计划—包含Oracle 23ai版本
- Oracle 23ai 变化之—-默认数据文件变为bigfile
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
分类目录归档:逻辑备份/恢复
ORACLE 12C 在datapump方面增强参数
在阅读ORACLE 12C datapump相关文档之时,发现有两个比较欣喜的参数LOGTIME和SQLFILE,鉴于他们是12C新增加参数,对他们的使用方法和用途进行简单说明
LOGTIME参数
该参数可以用于expdp/impdp,主要作用是记录执行步骤的开始时间,精确到微秒,使用语法为
LOGTIME=[NONE | STATUS | LOGFILE | ALL] • NONE--No timestamps on status or log file messages (same as default) • STATUS--Timestamps on status messages only • LOGFILE--Timestamps on log file messages only • ALL--Timestamps on both status and log file messages
该参数主要在我们对于一些数据库迁移升级项目使用datapump的时候,在测试阶段能够通过该参数发现哪一步执行时间较长,然后对其调优减少执行时间;另外一点就是可以通过做减法精确到具体的时间(毫米),我们可以知道我们的datapump主要耗时在哪一步,做到心中有数不慌
LOGTIME测试
SQL> conn chf/xifenfei@pdb 已连接。 SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 3 PDB READ WRITE NO SQL> create directory temp as 'e:\'; 目录已创建。 SQL> create table t_xifenfei as select * from dba_objects; 表已创建。 SQL> create index ind_t_xifenfei on t_xifenfei(object_id); 索引已创建。 C:\Users\XIFENFEI>expdp chf/xifenfei@pdb dumpfile=t_xifenfei.dmp tables=t_xifenf ei logfile=t_xifenfei.log directory=temp REUSE_DUMPFILES=yes LOGTIME=all Export: Release 12.1.0.1.0 - Production on 星期日 7月 14 20:11:24 2013 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. 连接到: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 14-7月 -13 20:11:35.961: 启动 "CHF"."SYS_EXPORT_TABLE_01": chf/********@pdb dumpfile=t_xifenfei.dmp tables=t_xifenfei logfile=t_xifenfei.log directory=temp REUSE_DUMPFILES=yes LOGTIME=all 14-7月 -13 20:11:37.703: 正在使用 BLOCKS 方法进行估计... 14-7月 -13 20:11:40.636: 处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA 14-7月 -13 20:11:40.825: 使用 BLOCKS 方法的总估计: 13 MB 14-7月 -13 20:11:48.802: 处理对象类型 TABLE_EXPORT/TABLE/TABLE 14-7月 -13 20:11:54.543: 处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX 14-7月 -13 20:11:57.204: 处理对象类型 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS 14-7月 -13 20:11:59.269: 处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 14-7月 -13 20:11:59.306: 处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/MARKER 14-7月 -13 20:12:36.563: . . 导出了 "CHF"."T_XIFENFEI" 10.36 MB 90865 行 14-7月 -13 20:12:37.527: 已成功加载/卸载了主表 "CHF"."SYS_EXPORT_TABLE_01" 14-7月 -13 20:12:37.533: ****************************************************************************** 14-7月 -13 20:12:37.537: CHF.SYS_EXPORT_TABLE_01 的转储文件集为: 14-7月 -13 20:12:37.547: E:\T_XIFENFEI.DMP 14-7月 -13 20:12:37.577: 作业 "CHF"."SYS_EXPORT_TABLE_01" 已于 星期日 7月 14 20:12:37 2013 elapsed 0 00:01:06 成功完成
SQLFILE参数
该参数可以用于impdp,主要作用是未真实在目标端执行导入的情况下,生成sql文件包含该dmp文件的所有ddl语句,使用语法为
SQLFILE=[directory_object:]file_name
注意事项:
1.directory_object可以不和impdp的DIRECTORY参数不一致,如果是一样,directory_object可以省略
2.SQLFILE文件必须写入到磁盘之上,不能写入到ASM中
3.SQLFILE和QUERY参数冲突,不能同时使用
SQLFILE测试
C:\Users\XIFENFEI>impdp chf/xifenfei@pdb dumpfile=t_xifenfei.dmp tables=t_xifenfei logfile=t_xifenfei.log directory=temp sqlfile=t_xifenfei.sql Import: Release 12.1.0.1.0 - Production on 星期日 7月 14 20:42:13 2013 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. 连接到: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Produc tion With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt ions 已成功加载/卸载了主表 "CHF"."SYS_SQL_FILE_TABLE_01" 启动 "CHF"."SYS_SQL_FILE_TABLE_01": chf/********@pdb dumpfile=t_xifenfei.dmp ta bles=t_xifenfei logfile=t_xifenfei.log directory=temp sqlfile=t_xifenfei.sql 处理对象类型 TABLE_EXPORT/TABLE/TABLE 处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX 处理对象类型 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS 处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS 处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/MARKER 作业 "CHF"."SYS_SQL_FILE_TABLE_01" 已于 星期日 7月 14 20:42:25 2013 elapsed 0 00:00:08 成功完成
t_xifenfei.sql内容
-- CONNECT CHF ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 '; -- new object type path: TABLE_EXPORT/TABLE/TABLE CREATE TABLE "CHF"."T_XIFENFEI" ( "OWNER" VARCHAR2(128 BYTE), "OBJECT_NAME" VARCHAR2(128 BYTE), "SUBOBJECT_NAME" VARCHAR2(128 BYTE), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23 BYTE), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19 BYTE), "STATUS" VARCHAR2(7 BYTE), "TEMPORARY" VARCHAR2(1 BYTE), "GENERATED" VARCHAR2(1 BYTE), "SECONDARY" VARCHAR2(1 BYTE), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128 BYTE), "SHARING" VARCHAR2(13 BYTE), "EDITIONABLE" VARCHAR2(1 BYTE), "ORACLE_MAINTAINED" VARCHAR2(1 BYTE) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; -- new object type path: TABLE_EXPORT/TABLE/INDEX/INDEX CREATE INDEX "CHF"."IND_T_XIFENFEI" ON "CHF"."T_XIFENFEI" ("OBJECT_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARALLEL 1 ; ALTER INDEX "CHF"."IND_T_XIFENFEI" NOPARALLEL; -- new object type path: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS -- new object type path: TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS -- new object type path: TABLE_EXPORT/TABLE/STATISTICS/MARKER
使用dbms_pumpdata执行expdp操作
使用dbms_pumpdata执行expdp操作
set serverout on declare h1 number; -- Datapump handle dir_name varchar2(30); -- Directory Name job_status VARCHAR2(30); begin dir_name := 'DATA_PUMP_DIR'; h1 := dbms_datapump.open( operation =>'EXPORT', --是export还是impport --导出表配置 job_mode =>'TABLE', --job_mode可以为SCHEMA/TABLE等 --导出用户配置 job_mode =>'SCHEMA', remote_link => NULL, --是否使用dblink导出(就是NETWORK_LINK) job_name =>'TABLE_XFF' --job_name expdpjob的名称 ); dbms_datapump.add_file(handle =>h1, filename => 'XIFENFEI.DMP', directory => dir_name, filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE, reusefile => 1); -- value of 1 instructs to overwrite existing file dbms_datapump.add_file(handle =>h1, filename => 'XIFENFEI.LOG', directory => dir_name, filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE, reusefile => 1); --导出表配置 dbms_datapump.metadata_filter(handle =>h1, name => 'TABLE_FILTER', value => 'CHF.T_XIFENFEI'); --导出用户配置 dbms_datapump.metadata_filter (handle => dp_handle, name => 'SCHEMA_EXPR', value => 'IN (''CHF'')'); -- Start the job. dbms_datapump.start_job(h1); dbms_datapump.wait_for_job (handle => dp_handle, job_state => job_status); dbms_output.put_line ('DataPump Export - '||to_char(sysdate,'DD/MM/YYYY HH24:MI:SS')||' Status '||job_status); begin dbms_datapump.detach(handle => h1); end; end; /
何种情况下imp的fromuser/touser改变tablespace失效
exp/imp是大家在数据库迁移中最常见的工具,但是该工具对于表空间的转换不是很智能(最少没有datapump方便),使得很多人在导入数据的时候,吃够了表空间不存在的苦.这里有个细节:fromuser和touser在哪些情况下会失效.这里通过试验,简单证明了对于常见的lob对象和分区表对象的时候fromuser和touser修改表空间会失效.
exp/imp支持表空间变化
--创建测试用户 SQL> create user chf identified by xifenfei; User created. SQL> grant dba to chf; Grant succeeded. SQL> conn chf/xifenfei Connected. --创建测试对象 SQL> create table t_xifenfei01 tablespace users 2 as 3 select * from dba_objects; Table created. SQL> create index in_t_xifenfei01 on t_xifenfei01(object_id) tablespace xifenfei; Index created. SQL> create table t_xifenfei02 tablespace xifenfei 2 as 3 select * from dba_objects; Table created. SQL> create index in_t_xifenfei02 on t_xifenfei02(object_id) tablespace users; Index created. --查询测试对象分布表空间情况 SQL> select OWNER,table_name,TABLESPACE_NAME from dba_tables where table_name like 'T_XIFENFEI%'; OWNER TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ CHF T_XIFENFEI01 USERS CHF T_XIFENFEI02 XIFENFEI SQL> SELECT OWNER,INDEX_NAME,TABLESPACE_NAME FROM DBA_INDEXES WHERE INDEX_NAME LIKE 'IN_T_XIFENFEI%'; OWNER INDEX_NAME TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ CHF IN_T_XIFENFEI01 XIFENFEI CHF IN_T_XIFENFEI02 USERS --导出测试对象 [oracle@xifenfei ~]$ exp chf/xifenfei tables=t_xifenfei01,t_xifenfei02 file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log Export: Release 10.2.0.4.0 - Production on Thu Dec 15 07:33:27 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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 T_XIFENFEI01 50053 rows exported . . exporting table T_XIFENFEI02 50055 rows exported Export terminated successfully without warnings. --为了试验证实,离线该表涉及表空间 SQL> alter tablespace xifenfei read only; Tablespace altered. SQL> alter tablespace users read only; Tablespace altered. --创建新用户 SQL> create user chf1 identified by xifenfei; User created. SQL> grant dba to chf1; Grant succeeded. --创建新表空间 SQL> create tablespace xifenfei1 datafile '/u01/oracle/oradata/XFF/xifenfei02.dbf' size 10m autoextend on 2 next 10m maxsize 10g; Tablespace created. SQL> alter user chf1 default tablespace xifenfei1; User altered. --两个测试用户分别默认表空间 SQL> select username,default_tablespace from dba_users where username like 'CHF%'; USERNAME DEFAULT_TABLESPACE ------------------------------ ------------------------------ CHF USERS CHF1 XIFENFEI1 --导入测试数据 [oracle@xifenfei ~]$ imp chf1/xifenfei fromuser=chf touser=chf1 file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log Import: Release 10.2.0.4.0 - Production on Thu Dec 15 07:37:54 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by CHF, not by you import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing CHF's objects into CHF1 . . importing table "T_XIFENFEI01" 50053 rows imported . . importing table "T_XIFENFEI02" 50055 rows imported Import terminated successfully without warnings. --查询导入结果 SQL> SELECT OWNER,INDEX_NAME,TABLESPACE_NAME FROM DBA_INDEXES WHERE INDEX_NAME LIKE 'IN_T_XIFENFEI%' 2 and owner='CHF1'; OWNER INDEX_NAME TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ CHF1 IN_T_XIFENFEI01 XIFENFEI1 CHF1 IN_T_XIFENFEI02 XIFENFEI1 SQL> select OWNER,table_name,TABLESPACE_NAME from dba_tables where table_name like 'T_XIFENFEI%' 2 AND OWNER='CHF1'; OWNER TABLE_NAME TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ CHF1 T_XIFENFEI01 XIFENFEI1 CHF1 T_XIFENFEI02 XIFENFEI1
通过这里的试验证明:对于无lob对象的普通表和普通index使用fromuser和touser可以实现表空间完美变化
含LOB对象测试
--read write相关表空间 SQL> alter tablespace users read write; Tablespace altered. SQL> alter tablespace xifenfei read write; Tablespace altered. SQL> create tablespace xifenfei2 datafile '/u01/oracle/oradata/XFF/xifenfei03.dbf' size 10m; Tablespace created. SQL> conn chf/xifenfei Connected. --创建表,lob分别属于不同空间(数据导入到另外表空间) SQL> create table t_lob 2 (id number,clob1 clob,blob1 blob) tablespace users 3 LOB ("CLOB1") STORE AS ( TABLESPACE xifenfei) 4 LOB ("BLOB1") STORE AS ( TABLESPACE xifenfei1 ); Table created. SQL> select table_name,COLUMN_NAME,TABLESPACE_NAME from user_lobs; TABLE_NAME COLUMN_NAME TABLESPACE_NAME ------------------------------ --------------- ------------------------------ T_LOB CLOB1 XIFENFEI T_LOB BLOB1 XIFENFEI1 SQL> select tablespace_name from user_tables where table_name='T_LOB'; TABLESPACE_NAME ------------------------------ USERS --创建表和lob属于一个表空间(数据导入到另外表空间) SQL> create table t_lob_n 2 (id number,clob1 clob) tablespace users; Table created. SQL> select segment_name,segment_type,tablespace_name from user_segments where SEGMENT_NAME not like '%XIFENFEI%'; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME --------------------------------------------------------------------------------- ------------------ ---------------- SYS_IL0000051858C00002$$ LOBINDEX USERS SYS_LOB0000051858C00002$$ LOBSEGMENT USERS T_LOB_N TABLE USERS T_LOB TABLE USERS SYS_IL0000051851C00002$$ LOBINDEX XIFENFEI SYS_LOB0000051851C00002$$ LOBSEGMENT XIFENFEI SYS_IL0000051851C00003$$ LOBINDEX XIFENFEI1 SYS_LOB0000051851C00003$$ LOBSEGMENT XIFENFEI1 --表和lob不同表空间(数据导入到lob对应表空间) SQL> create table t_lob2 2 (id number,clob1 clob) tablespace users 3 LOB ("CLOB1") STORE AS ( TABLESPACE xifenfei2); Table created. SQL> select table_name,COLUMN_NAME,TABLESPACE_NAME from user_lobs; TABLE_NAME COLUMN_NAME TABLESPACE_NAME ------------------------------ --------------- ------------------------------ T_LOB_N CLOB1 USERS T_LOB CLOB1 XIFENFEI T_LOB BLOB1 XIFENFEI1 T_LOB2 CLOB1 XIFENFEI2 SQL> select segment_name,segment_type,tablespace_name from user_segments where SEGMENT_NAME not like '%XIFENFEI%'; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ T_LOB2 TABLE USERS SYS_IL0000051858C00002$$ LOBINDEX USERS SYS_LOB0000051858C00002$$ LOBSEGMENT USERS T_LOB_N TABLE USERS T_LOB TABLE USERS SYS_IL0000051851C00002$$ LOBINDEX XIFENFEI SYS_LOB0000051851C00002$$ LOBSEGMENT XIFENFEI SYS_IL0000051851C00003$$ LOBINDEX XIFENFEI1 SYS_LOB0000051851C00003$$ LOBSEGMENT XIFENFEI1 SYS_IL0000051863C00002$$ LOBINDEX XIFENFEI2 SYS_LOB0000051863C00002$$ LOBSEGMENT XIFENFEI2 11 rows selected. SQL> select table_name,COLUMN_NAME,TABLESPACE_NAME,SEGMENT_NAME from user_lobs; TABLE_NAME COLUMN_NAME TABLESPACE_NAME SEGMENT_NAME ------------------------------ --------------- ------------------------------ ------------------------------ T_LOB BLOB1 XIFENFEI1 SYS_LOB0000051851C00003$$ T_LOB CLOB1 XIFENFEI SYS_LOB0000051851C00002$$ T_LOB_N CLOB1 USERS SYS_LOB0000051858C00002$$ T_LOB2 CLOB1 XIFENFEI2 SYS_LOB0000051863C00002$$ --得到在默认情况下LOBINDEX和LOBSEGMENT在同一个表空间 --导出三种情况下lob表 [oracle@xifenfei ~]$ exp chf/xifenfei tables=t_lob_n file=/tmp/lob1.dmp log=/tmp/xifenfei.log indexes=y Export: Release 10.2.0.4.0 - Production on Thu Dec 15 08:57:38 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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 T_LOB_N 0 rows exported Export terminated successfully without warnings. [oracle@xifenfei ~]$ exp chf/xifenfei tables=t_lob file=/tmp/lob.dmp log=/tmp/xifenfei.log indexes=y Export: Release 10.2.0.4.0 - Production on Thu Dec 15 08:31:25 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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 T_LOB 0 rows exported Export terminated successfully without warnings. [oracle@xifenfei ~]$ exp chf/xifenfei tables=t_lob2 file=/tmp/lob2.dmp log=/tmp/xifenfei.log Export: Release 10.2.0.4.0 - Production on Thu Dec 15 16:23:18 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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 T_LOB2 0 rows exported Export terminated successfully without warnings. --修改default tablespace 和 read only相关表空间 SQL> alter user chf1 default tablespace xifenfei2; User altered. SQL> alter tablespace users read only; Tablespace altered. SQL> alter tablespace xifenfei read only; Tablespace altered. SQL> alter tablespace xifenfei1 read only; Tablespace altered. --导入lob表 [oracle@xifenfei ~]$ imp chf1/xifenfei tables=t_lob_n file=/tmp/lob1.dmp log=/tmp/xifenfei.log fromuser=chf touser=chf1 Import: Release 10.2.0.4.0 - Production on Thu Dec 15 08:58:12 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by CHF, not by you import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing CHF's objects into CHF1 IMP-00017: following statement failed with ORACLE error 1647: "CREATE TABLE "T_LOB_N" ("ID" NUMBER, "CLOB1" CLOB) PCTFREE 10 PCTUSED 40 I" "NITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 " "BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS LOB ("CLOB1") ST" "ORE AS (TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCA" "CHE LOGGING STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POO" "L DEFAULT))" IMP-00003: ORACLE error 1647 encountered ORA-01647: tablespace 'USERS' is read only, cannot allocate space in it Import terminated successfully with warnings. --使用fromuser和touser并未修改table segment初始化参数 [oracle@xifenfei ~]$ imp chf1/xifenfei tables=t_lob file=/tmp/lob.dmp log=/tmp/xifenfei.log fromuser=chf touser=chf1 Import: Release 10.2.0.4.0 - Production on Thu Dec 15 08:35:05 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by CHF, not by you import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing CHF's objects into CHF1 IMP-00017: following statement failed with ORACLE error 1647: "CREATE TABLE "T_LOB" ("ID" NUMBER, "CLOB1" CLOB, "BLOB1" BLOB) PCTFREE 10 " "PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELI" "ST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS LOB " "("CLOB1") STORE AS (TABLESPACE "XIFENFEI" ENABLE STORAGE IN ROW CHUNK 8192" " RETENTION NOCACHE LOGGING STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROU" "PS 1 BUFFER_POOL DEFAULT)) LOB ("BLOB1") STORE AS (TABLESPACE "XIFENFEI1" " "ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL" " 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))" IMP-00003: ORACLE error 1647 encountered ORA-01647: tablespace 'USERS' is read only, cannot allocate space in it Import terminated successfully with warnings. --结论同上 [oracle@xifenfei ~]$ imp chf1/xifenfei tables=t_lob2 file=/tmp/lob2.dmp log=/tmp/xifenfei.log fromuser=chf touser=chf1 Import: Release 10.2.0.4.0 - Production on Thu Dec 15 16:24:03 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by CHF, not by you import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing CHF's objects into CHF1 IMP-00017: following statement failed with ORACLE error 1647: "CREATE TABLE "T_LOB2" ("ID" NUMBER, "CLOB1" CLOB) PCTFREE 10 PCTUSED 40 IN" "ITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 B" "UFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS LOB ("CLOB1") STO" "RE AS (TABLESPACE "XIFENFEI2" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION N" "OCACHE LOGGING STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_" "POOL DEFAULT))" IMP-00003: ORACLE error 1647 encountered ORA-01647: tablespace 'USERS' is read only, cannot allocate space in it Import terminated successfully with warnings. --结论也同上
通过三种不同情况的table segment 和lob segment的分别表空间和导入表空间测试情况,可以判断出来在使用exp/imp迁移数据时候,如果遇到含lob字段表,不能通过fromuser和touser来实现修改,就算lob的表空间存在,或者lob和table segment是同一个表空间,而table segment的表空间不存在,依然会报错,导入不成功.
分区表测试
--read write 相关表空间 SQL> alter tablespace users read write; Tablespace altered. SQL> alter tablespace xifenfei read write; Tablespace altered. SQL> alter tablespace xifenfei1 read write; Tablespace altered. --创建分区表 SQL> conn chf/xifenfei Connected. SQL> create table tab_par 2 ( 3 F_KJND VARCHAR2(4) default ' ' not null, 4 F_CODE VARCHAR2(30) default ' ' not null, 5 F_KMBH VARCHAR2(30) default ' ' not null, 6 F_BKBH VARCHAR2(30) default ' ' not null, 7 UNIT_ID VARCHAR2(30) 8 ) 9 partition by range (F_KJND) 10 (partition TABL_NAME_PT_2009 values less than ('2010')tablespace users, 11 partition TABL_NAME_PT_2010 values less than ('2011')tablespace xifenfei, 12 partition TABL_NAME_PT_MAX values less than (MAXVALUE) tablespace xifenfei1 13 ); Table created. --查询分区分布 SQL> select PARTITION_NAME,TABLESPACE_NAME from ALL_TAB_PARTITIONS where TABLE_NAME='TAB_PAR'; PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ TABL_NAME_PT_2009 USERS TABL_NAME_PT_2010 XIFENFEI TABL_NAME_PT_MAX XIFENFEI1 --导出分区表 [oracle@xifenfei ~]$ exp chf/xifenfei tables=tab_par file=/tmp/tab_par.dmp log=/tmp/xifenfei.log Export: Release 10.2.0.4.0 - Production on Thu Dec 15 18:33:19 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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 TAB_PAR . . exporting partition TABL_NAME_PT_2009 0 rows exported . . exporting partition TABL_NAME_PT_2010 0 rows exported . . exporting partition TABL_NAME_PT_MAX 0 rows exported Export terminated successfully without warnings. --导入分区表 [oracle@xifenfei ~]$ imp chf1/xifenfei tables=tab_par file=/tmp/tab_par.dmp log=/tmp/xifenfei.log fromuser=chf touser=chf1 Import: Release 10.2.0.4.0 - Production on Thu Dec 15 18:33:52 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by CHF, not by you import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing CHF's objects into CHF1 . . importing partition "TAB_PAR":"TABL_NAME_PT_2009" 0 rows imported . . importing partition "TAB_PAR":"TABL_NAME_PT_2010" 0 rows imported . . importing partition "TAB_PAR":"TABL_NAME_PT_MAX" 0 rows imported Import terminated successfully without warnings. --导入成功 --查看导入进入表空间 SQL> select PARTITION_NAME,TABLESPACE_NAME from ALL_TAB_PARTITIONS where TABLE_NAME='TAB_PAR' and TABLE_OWNER='CHF1'; PARTITION_NAME TABLESPACE_NAME ------------------------------ ------------------------------ TABL_NAME_PT_2009 USERS TABL_NAME_PT_2010 XIFENFEI TABL_NAME_PT_MAX XIFENFEI1 --发现还是进入和以前相同的表空间,fromuser和touser未生效 SQL> DROP TABLE CHF1.TAB_PAR PURGE; Table dropped. --read only相关表空间测试 SQL> ALTER TABLESPACE USERS READ ONLY; Tablespace altered. SQL> ALTER TABLESPACE XIFENFEI READ ONLY; Tablespace altered. SQL> ALTER TABLESPACE XIFENFEI1 READ ONLY; Tablespace altered. --再次导入 [oracle@xifenfei ~]$ imp chf1/xifenfei tables=tab_par file=/tmp/tab_par.dmp log=/tmp/xifenfei.log fromuser=chf touser=chf1 Import: Release 10.2.0.4.0 - Production on Thu Dec 15 18:36:38 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path Warning: the objects were exported by CHF, not by you import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing CHF's objects into CHF1 IMP-00017: following statement failed with ORACLE error 1647: "CREATE TABLE "TAB_PAR" ("F_KJND" VARCHAR2(4) NOT NULL ENABLE, "F_CODE" VARC" "HAR2(30) NOT NULL ENABLE, "F_KMBH" VARCHAR2(30) NOT NULL ENABLE, "F_BKBH" V" "ARCHAR2(30) NOT NULL ENABLE, "UNIT_ID" VARCHAR2(30)) PCTFREE 10 PCTUSED 40" " INITRANS 1 MAXTRANS 255 TABLESPACE "USERS" LOGGING PARTITION BY RANGE ("F_" "KJND" ) (PARTITION "TABL_NAME_PT_2009" VALUES LESS THAN ('2010') PCTFREE " "10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FRE" "ELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS, " "PARTITION "TABL_NAME_PT_2010" VALUES LESS THAN ('2011') PCTFREE 10 PCTUSED" " 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROU" "PS 1 BUFFER_POOL DEFAULT) TABLESPACE "XIFENFEI" LOGGING NOCOMPRESS, PARTITI" "ON "TABL_NAME_PT_MAX" VALUES LESS THAN (MAXVALUE) PCTFREE 10 PCTUSED 40 IN" "ITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 B" "UFFER_POOL DEFAULT) TABLESPACE "XIFENFEI1" LOGGING NOCOMPRESS )" IMP-00003: ORACLE error 1647 encountered ORA-01647: tablespace 'USERS' is read only, cannot allocate space in it Import terminated successfully with warnings. --进步一证明分区表在导入的时候fromuser和touser未能改变其对应表空间
通过对分区表的测试,证明exp/imp在操作分区表的时候fromuser和touser也不能实现表空间的转换
在使用imp和exp实现数据迁移的时候,遇到我们常见的lob和分区表时候fromuser和touser修改表空间会失效,数据还是会导入到原对象锁对应的表空间,所以在处理含这些对象的数据迁移时,一般方法有:1.创建好这些对象所属表空间;2.先导出来这些对象对应的创建脚本,创建好这些对象,然后使用IGNORE=Y导入
发表在 逻辑备份/恢复
评论关闭