|
第42/43次活动:2017-12-14(星期四晚上7:00-9:30)和2017-12-19(星期二晚上7:00-9:30)
文本原文:
- 1234 BAKER 10 9999 101 102 103
- 1234 JOKER 10 9999 777 888 999
- 2664 YOUNG 20 2893 425 abc 102
- 5321 OTOOLE 10 9999 321 55 40
- 2134 FARMER 20 4555 236 456
- 2414 LITTLE 20 5634 236 456 40
- 6542 LEE 10 4532 102 321 14
- 2849 EDDS xx 4555 294 40
- 4532 PERKINS 10 9999 40
- 1244 HUNT 11 3452 665 133 456
- 123 DOOLITTLE 12 9940 132
- 1453 MACDONALD 25 5532 200
复制代码 有问题的行:
- 1234 BAKER 10 9999 101 102 103
- --1234 JOKER 10 9999 777 888 999
- --2664 YOUNG 20 2893 425 abc 102
- 5321 OTOOLE 10 9999 321 55 40
- 2134 FARMER 20 4555 236 456
- 2414 LITTLE 20 5634 236 456 40
- 6542 LEE 10 4532 102 321 14
- --2849 EDDS xx 4555 294 40
- 4532 PERKINS 10 9999 40
- 1244 HUNT 11 3452 665 133 456
- 123 DOOLITTLE 12 9940 132
- 1453 MACDONALD 25 5532 200
复制代码 控制文件的写法:
- -- Copyright (c) 1991 by Oracle Corporation
- -- NAME
- -- ulcase5.ctl - <one-line expansion of the name>
- -- DESCRIPTION
- -- <short description of component this file declares/defines>
- -- RETURNS
- --
- -- NOTES
- -- <other useful comments, qualifications, etc.>
- -- MODIFIED (MM/DD/YY)
- -- ksudarsh 04/08/94 - merge changes from branch 1.3.710.1
- -- ksudarsh 02/21/94 - quote dat file
- -- ksudarsh 03/11/93 - make filename lowercase
- -- ksudarsh 11/06/92 - infile is ulcase5
- -- cheigham 08/28/91 - Creation
- --
- -- $Header: ulcase5.ctl,v 1.4 1994/04/08 13:44:31 ksudarsh Exp $ case5.ctl
- --
- -- Loads EMP records from first 23 characters
- -- Creates and loads PROJ records for each PROJO listed
- -- for each employee
- LOAD DATA
- INFILE 'ulcase5.dat'
- BADFILE 'ulcase5.bad'
- DISCARDFILE 'ulcase5.dis'
- REPLACE
- INTO TABLE EMP
- (EMPNO POSITION(1:4) INTEGER EXTERNAL,
- ENAME POSITION(6:15) CHAR,
- DEPTNO POSITION(17:18) CHAR,
- MGR POSITION(20:23) INTEGER EXTERNAL)
- INTO TABLE PROJ
- -- PROJ has two columns, both not null: EMPNO and PROJNO
- WHEN PROJNO != ' '
- (EMPNO POSITION(1:4) INTEGER EXTERNAL,
- PROJNO POSITION(25:27) INTEGER EXTERNAL) -- 1st proj
- INTO TABLE PROJ
- WHEN PROJNO != ' '
- (EMPNO POSITION(1:4) INTEGER EXTERNAL,
- PROJNO POSITION(29:31) INTEGER EXTERNAL) -- 2nd proj
- INTO TABLE PROJ
- WHEN PROJNO != ' '
- (EMPNO POSITION(1:4) INTEGER EXTERNAL,
- PROJNO POSITION(33:35) INTEGER EXTERNAL) -- 3rd proj
复制代码
sqlldr hr/oracle_4U control=ulcase5.ctl direct=y
写不写direct=y差别很大,写了能加载进尽量多的行(多表插入的时候,表是分开来看的)。
- select i.index_name , i.status
- from dba_indexes i
- where i.table_owner='HR' and i.table_name='EMP';
-
- select * from hr.emp order by 1;
-
- select * from dba_constraints c
- where c.table_name='EMP' and c.owner='HR';
-
- --------------
- select * from hr.proj order by 2;
-
-
复制代码 1Z0-052 17-29ppt里,少掉的文本:
- Today's Newly Hired Employees
- Dept Job Manager MgrNo Emp Name EmpNo Salary/Commission
- ---- -------- -------- ----- -------- ----- -----------------
- 20 Salesman Blake 7698 Shepard 8061 $1,600.00 (3%)
- Falstaff 8066 $1,250.00 (5%)
- Major 8064 $1,250.00 (14%)
- 30 Clerk Scott 7788 Conrad 8062 $1,100.00
- Ford 7369 DeSilva 8063 $800.00
- Manager King 7839 Provo 8065 $2,975.00
复制代码- SQL*Loader Control File (continued)
- 1 -- This is a sample control file
- 2 LOAD DATA
- 3 INFILE ’SAMPLE.DAT’
- 4 BADFILE ’sample.bad’
- 5 DISCARDFILE ’sample.dsc’
- 6 APPEND
- 7 INTO TABLE emp
- 8 WHEN (57) = ’.’
- 9 TRAILING NULLCOLS
- 10 (hiredate SYSDATE,
- deptno POSITION(1:2) INTEGER EXTERNAL(3)
- NULLIF deptno=BLANKS,
- job POSITION(7:14) CHAR TERMINATED BY WHITESPACE
- NULLIF job=BLANKS "UPPER(:job)",
- mgr POSITION(28:31) INTEGER EXTERNAL
- TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
- ename POSITION(34:41) CHAR
- TERMINATED BY WHITESPACE "UPPER(:ename)",
- empno POSITION(45) INTEGER EXTERNAL
- TERMINATED BY WHITESPACE,
- sal POSITION(51) CHAR TERMINATED BY WHITESPACE
- "TO_NUMBER(:sal,’$99,999.99’)",
- comm INTEGER EXTERNAL ENCLOSED BY ’(’ AND ’%’
- ":comm * 100"
- )
- The explanation of this sample control file (by line numbers) is as follows:
- 1. Comments can appear anywhere in the command section of the file, but they must not appear in the data. Precede any comment with two hyphens. All text to the right of the double hyphen is ignored until the end of the line.
- 2. The LOAD DATA statement indicates to SQL*Loader that this is the beginning of a new data load. If you are continuing a load that has been interrupted in progress, use the CONTINUE LOAD DATA statement.
- 3. The INFILE keyword specifies the name of a data file containing data that you want to load.
- 4. The BADFILE keyword specifies the name of a file into which rejected records are placed.
- 5. The DISCARDFILE keyword specifies the name of a file into which discarded records are placed.
- 6. The APPEND keyword is one of the options that you can use when loading data into a table that is not empty. To load data into a table that is empty, use the INSERT keyword.
- 7. The INTO TABLE keyword enables you to identify tables, fields, and data types. It defines the relationship between records in the data file and tables in the database.
- 8. The WHEN clause specifies one or more field conditions that each record must match before SQL*Loader loads the data. In this example, SQL*Loader loads the record only if the 57th character is a decimal point. That decimal point delimits dollars and cents in the field and causes records to be rejected if SAL has no value.
- 9. The TRAILING NULLCOLS clause prompts SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.
- 10. The remainder of the control file contains the field list, which provides information about column formats in the table that is being loaded.
复制代码- select * from dba_tablespaces;
- create directory dirorcl as 'c:\data\dirorcl';
- select * from database_properties;
- create tablespace tbszhs datafile 'C:\app\Administrator\oradata\orcl\tbszhs.dbf' size 10M ;
- create table hr.tzhs(a number ) tablespace tbszhs;
- insert into hr.tzhs values (100 ) ;
- alter tablespace tbszhs read only ;
- select * from v$transportable_platform;
- alter tablespace tbszhs read write ;
- -----------------------------------------------
- select * from dba_tablespaces;
- create directory dirutforcl as 'c:\data\dirutforcl';
- select * from dba_tables t where t.tablespace_name='TBSUTF8_11G';
- begin
- dbms_tts.transport_set_check('TBSUTF8_11G');
- end;
- select * from transport_set_violations;
- alter table sys.TUTF8_11G_SYS move tablespace system ;
- alter tablespace tbsutf8_11g read only;
- alter tablespace tbsutf8_11g read write;
- -----------------------------------------
- 数据泵导出实用程序提供了一种用于在 Oracle 数据库之间传输
- 数据对象的机制。该实用程序可以使用以下命令进行调用:
- 示例: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
- 您可以控制导出的运行方式。具体方法是: 在 'expdp' 命令后输入
- 各种参数。要指定各参数, 请使用关键字:
- 格式: expdp KEYWORD=value 或 KEYWORD=(value1,value2,...,valueN)
- 示例: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
- 或 TABLES=(T11,T12), 如果 T1 是分区表
- SERID 必须是命令行中的第一个参数。
- -----------------------------------------------------------------------------
- 以下是可用关键字和它们的说明。方括号中列出的是默认值。
- TTACH
- 连接到现有作业。
- 例如, ATTACH=job_name。
- OMPRESSION
- 减少转储文件大小。
- 有效的关键字值为: ALL, DATA_ONLY, [METADATA_ONLY] 和 NONE。
- ONTENT
- 指定要卸载的数据。
- 有效的关键字值为: [ALL], DATA_ONLY 和 METADATA_ONLY。
- ATA_OPTIONS
- 数据层选项标记。
- 有效的关键字值为: XML_CLOBS。
- IRECTORY
- 用于转储文件和日志文件的目录对象。
- UMPFILE
- 指定目标转储文件名的列表 [expdat.dmp]。
- 例如, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp。
- NCRYPTION
- 加密某个转储文件的一部分或全部。
- 有效的关键字值为: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY 和 NONE
- 。
- NCRYPTION_ALGORITHM
- 指定加密的方式。
- 有效的关键字值为: [AES128], AES192 和 AES256。
- NCRYPTION_MODE
- 生成加密密钥的方法。
- 有效的关键字值为: DUAL, PASSWORD 和 [TRANSPARENT]。
- NCRYPTION_PASSWORD
- 用于在转储文件中创建加密数据的口令密钥。
- STIMATE
- 计算作业估计值。
- 有效的关键字值为: [BLOCKS] 和 STATISTICS。
- STIMATE_ONLY
- 计算作业估计值而不执行导出。
- XCLUDE
- 排除特定对象类型。
- 例如, EXCLUDE=SCHEMA:"='HR'"。
- ILESIZE
- 以字节为单位指定每个转储文件的大小。
- LASHBACK_SCN
- 用于重置会话快照的 SCN。
- LASHBACK_TIME
- 用于查找最接近的相应 SCN 值的时间。
- ULL
- 导出整个数据库 [N]。
- ELP
- 显示帮助消息 [N]。
- NCLUDE
- 包括特定对象类型。
- 例如, INCLUDE=TABLE_DATA。
- OB_NAME
- 要创建的导出作业的名称。
- OGFILE
- 指定日志文件名 [export.log]。
- ETWORK_LINK
- 源系统的远程数据库链接的名称。
- OLOGFILE
- 不写入日志文件 [N]。
- ARALLEL
- 更改当前作业的活动 worker 的数量。
- ARFILE
- 指定参数文件名。
- UERY
- 用于导出表的子集的谓词子句。
- 例如, QUERY=employees:"WHERE department_id > 10"。
- EMAP_DATA
- 指定数据转换函数。
- 例如, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO。
- EUSE_DUMPFILES
- 覆盖目标转储文件 (如果文件存在) [N]。
- AMPLE
- 要导出的数据的百分比。
- CHEMAS
- 要导出的方案的列表 [登录方案]。
- OURCE_EDITION
- 用于提取元数据的版本。
- TATUS
- 监视作业状态的频率, 其中
- 默认值 [0] 表示只要有新状态可用, 就立即显示新状态。
- ABLES
- 标识要导出的表的列表。
- 例如, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995。
- ABLESPACES
- 标识要导出的表空间的列表。
- RANSPORTABLE
- 指定是否可以使用可传输方法。
- 有效的关键字值为: ALWAYS 和 [NEVER]。
- RANSPORT_FULL_CHECK
- 验证所有表的存储段 [N]。
- RANSPORT_TABLESPACES
- 要从中卸载元数据的表空间的列表。
- ERSION
- 要导出的对象版本。
- 有效的关键字值为: [COMPATIBLE], LATEST 或任何有效的数据库版本。
- -----------------------------------------------------------------------------
- 下列命令在交互模式下有效。
- 注: 允许使用缩写。
- DD_FILE
- 将转储文件添加到转储文件集。
- ONTINUE_CLIENT
- 返回到事件记录模式。如果处于空闲状态, 将重新启动作业。
- XIT_CLIENT
- 退出客户机会话并使作业保持运行状态。
- ILESIZE
- 用于后续 ADD_FILE 命令的默认文件大小 (字节)。
- ELP
- 汇总交互命令。
- ILL_JOB
- 分离并删除作业。
- ARALLEL
- 更改当前作业的活动 worker 的数量。
- EUSE_DUMPFILES
- 覆盖目标转储文件 (如果文件存在) [N]。
- TART_JOB
- 启动或恢复当前作业。
- 有效的关键字值为: SKIP_CURRENT。
- TATUS
- 监视作业状态的频率, 其中
- 默认值 [0] 表示只要有新状态可用, 就立即显示新状态。
- TOP_JOB
- 按顺序关闭作业执行并退出客户机。
- 有效的关键字值为: IMMEDIATE。
- :\Users\Administrator>expdp system/oracle_4U directory=dirorcl dumpfile=tbszhs.
- mp TRANSPORT_TABLESPACES=tbszhs
- xport: Release 11.2.0.1.0 - Production on 星期二 12月 19 19:17:20 2017
- opyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
- DE-28002: 操作产生了 ORACLE 错误 28002
- RA-28002: 7 天之后口令将过期
- 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
- ith the Partitioning, OLAP, Data Mining and Real Application Testing options
- 启动 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=dirorcl
- umpfile=tbszhs.dmp TRANSPORT_TABLESPACES=tbszhs
- 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
- 处理对象类型 TRANSPORTABLE_EXPORT/TABLE
- 处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
- 已成功加载/卸载了主表 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"
- *****************************************************************************
- YSTEM.SYS_EXPORT_TRANSPORTABLE_01 的转储文件集为:
- C:\DATA\DIRORCL\TBSZHS.DMP
- *****************************************************************************
- 可传输表空间 TBSZHS 所需的数据文件:
- C:\APP\ADMINISTRATOR\ORADATA\ORCL\TBSZHS.DBF
- 作业 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已于 19:18:01 成功完成
- :\Users\Administrator>rman target /
- 恢复管理器: Release 11.2.0.1.0 - Production on 星期二 12月 19 19:18:24 2017
- opyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
- 连接到目标数据库: ORCL (DBID=1347195613)
- MAN> convert tablespace tbszhs to platform 'Linux IA (64-bit)' format 'c:\tbszh
- .dbf';
- 启动 conversion at source 于 19-12月-17
- 使用目标数据库控制文件替代恢复目录
- 分配的通道: ORA_DISK_1
- 通道 ORA_DISK_1: SID=20 设备类型=DISK
- 通道 ORA_DISK_1: 启动数据文件转换
- 输入数据文件: 文件号=00006 名称=C:\APP\ADMINISTRATOR\ORADATA\ORCL\TBSZHS.DBF
- 已转换的数据文件 = C:\TBSZHS.DBF
- 通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
- 完成 conversion at source 于 19-12月-17
- MAN> exit
- 恢复管理器完成。
- :\Users\Administrator>export ORACLE_SID=utforcl
- export' 不是内部或外部命令,也不是可运行的程序
- 或批处理文件。
- :\Users\Administrator>set ORACLE_SID=utforcl
- :\Users\Administrator>expdp system/oracle_4U directory=dirutforcl dumpfile=tbsu
- f8_11g.dmp TRANSPORT_TABLESPACES=tbsutf8_11g
- xport: Release 11.2.0.1.0 - Production on 星期二 12月 19 19:30:14 2017
- opyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
- DE-28002: 操作产生了 ORACLE 错误 28002
- RA-28002: 7 天之后口令将过期
- 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
- ith the Partitioning, OLAP, Data Mining and Real Application Testing options
- RA-39002: 操作无效
- RA-39070: 无法打开日志文件。
- RA-39087: 目录名 DIRUTFORCL 无效
- :\Users\Administrator>expdp system/oracle_4U directory=dirutforcl dumpfile=tbsu
- f8_11g.dmp TRANSPORT_TABLESPACES=tbsutf8_11g
- xport: Release 11.2.0.1.0 - Production on 星期二 12月 19 19:31:10 2017
- opyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
- DE-28002: 操作产生了 ORACLE 错误 28002
- RA-28002: 7 天之后口令将过期
- 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
- ith the Partitioning, OLAP, Data Mining and Real Application Testing options
- 启动 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=dirutfor
- l dumpfile=tbsutf8_11g.dmp TRANSPORT_TABLESPACES=tbsutf8_11g
- 处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
- 处理对象类型 TRANSPORTABLE_EXPORT/TABLE
- 处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
- 已成功加载/卸载了主表 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"
- *****************************************************************************
- YSTEM.SYS_EXPORT_TRANSPORTABLE_01 的转储文件集为:
- C:\DATA\DIRUTFORCL\TBSUTF8_11G.DMP
- *****************************************************************************
- 可传输表空间 TBSUTF8_11G 所需的数据文件:
- C:\APP\ADMINISTRATOR\ORADATA\UTFORCL\TBSUTF8_11G01.DBF
- 作业 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已于 19:31:46 成功完成
- :\Users\Administrator>
- ------------------------------------------------------------
- Microsoft Windows [版本 6.1.7600]
- 版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
- C:\Users\Administrator>rman target /
- 恢复管理器: Release 11.2.0.1.0 - Production on 星期二 12月 19 19:31:28 2017
- Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
- 连接到目标数据库: UTFORCL (DBID=454057935)
- RMAN> convert tablespace tbszhs to platform '
- 2>
- 3>
- RMAN-00571: ===========================================================
- RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
- RMAN-00571: ===========================================================
- RMAN-00558: 分析输入命令时出错
- RMAN-01006: 在进行语法分析时发出出错信号
- RMAN-02002: 到达意外的输入文件结尾
- RMAN> convert tablespace tbszhs to platform 'Solaris[tm] OE (32-bit)' format 'c:
- \data\tbsutf8_11g.dbf' ;
- 启动 conversion at source 于 19-12月-17
- 使用目标数据库控制文件替代恢复目录
- 分配的通道: ORA_DISK_1
- 通道 ORA_DISK_1: SID=142 设备类型=DISK
- RMAN-00571: ===========================================================
- RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
- RMAN-00571: ===========================================================
- RMAN-03002: conversion at source 命令 (在 12/19/2017 19:32:42 上) 失败
- RMAN-20202: 在恢复目录中未找到表空间
- RMAN-06019: 无法转换表空间名称"TBSZHS"
- RMAN> convert tablespace tbsutf8_11g to platform 'Solaris[tm] OE (32-bit)' forma
- t 'c:\data\tbsutf8_11g.dbf' ;
- 启动 conversion at source 于 19-12月-17
- 使用通道 ORA_DISK_1
- 通道 ORA_DISK_1: 启动数据文件转换
- 输入数据文件: 文件号=00006 名称=C:\APP\ADMINISTRATOR\ORADATA\UTFORCL\TBSUTF8_11G
- 01.DBF
- 已转换的数据文件 = C:\DATA\TBSUTF8_11G.DBF
- 通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
- 完成 conversion at source 于 19-12月-17
- RMAN>
复制代码- [oracle@station90 ~]$ rman target /
- Recovery Manager: Release 11.2.0.3.0 - Production on Tue Dec 19 19:54:09 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- connected to target database: ORCL (DBID=1343950367)
- RMAN> CONFIGURE DEFAULT DEVICE TYPE TO disk;
- using target database control file instead of recovery catalog
- old RMAN configuration parameters:
- CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
- new RMAN configuration parameters:
- CONFIGURE DEFAULT DEVICE TYPE TO DISK;
- new RMAN configuration parameters are successfully stored
- RMAN> convert datafile '/u01/app/oracle/oradata/orcl/tbsutf8_11g.dbf' from platform 'Solaris[tm] OE (32-bit)' format '/u01/app/oracle/oradata/orcl/tbsutf8_11g2.dbf';
- Starting conversion at target at 2017-12-19:19:54:18
- allocated channel: ORA_DISK_1
- channel ORA_DISK_1: SID=73 device type=DISK
- allocated channel: ORA_DISK_2
- channel ORA_DISK_2: SID=130 device type=DISK
- allocated channel: ORA_DISK_3
- channel ORA_DISK_3: SID=202 device type=DISK
- allocated channel: ORA_DISK_4
- channel ORA_DISK_4: SID=15 device type=DISK
- allocated channel: ORA_DISK_5
- channel ORA_DISK_5: SID=68 device type=DISK
- allocated channel: ORA_DISK_6
- channel ORA_DISK_6: SID=139 device type=DISK
- allocated channel: ORA_DISK_7
- channel ORA_DISK_7: SID=204 device type=DISK
- allocated channel: ORA_DISK_8
- channel ORA_DISK_8: SID=14 device type=DISK
- channel ORA_DISK_1: starting datafile conversion
- input file name=/u01/app/oracle/oradata/orcl/tbsutf8_11g.dbf
- converted datafile=/u01/app/oracle/oradata/orcl/tbsutf8_11g2.dbf
- channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
- Finished conversion at target at 2017-12-19:19:54:21
- RMAN>
复制代码- impdp system/oracle_4U directory=dir1 dumpfile=tbsutf8_11g.dmp TRANSPORT_DATAFILES=/u01/app/oracle/oradata/orcl/tbsutf8_11g2.dbf
- Import: Release 11.2.0.3.0 - Production on Tue Dec 19 19:55:00 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- UDI-28002: operation generated ORACLE error 28002
- ORA-28002: the password will expire within 7 days
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
- Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=dir1 dumpfile=tbsutf8_11g.dmp TRANSPORT_DATAFILES=/u01/app/oracle/oradata/orcl/tbsutf8_11g2.dbf
- Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
- Processing object type TRANSPORTABLE_EXPORT/TABLE
- Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
- Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 19:55:02
复制代码- impdp system/oracle_4U directory=dir1 dumpfile=tbsutf8_11g.dmp TRANSPORT_DATAFILES=/u01/app/oracle/oradata/orcl/tbsutf8_11g2.dbf
- Import: Release 11.2.0.3.0 - Production on Tue Dec 19 19:55:00 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- UDI-28002: operation generated ORACLE error 28002
- ORA-28002: the password will expire within 7 days
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
- Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=dir1 dumpfile=tbsutf8_11g.dmp TRANSPORT_DATAFILES=/u01/app/oracle/oradata/orcl/tbsutf8_11g2.dbf
- Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
- Processing object type TRANSPORTABLE_EXPORT/TABLE
- Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
- Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 19:55:02
复制代码- [oracle@station90 dir1]$ impdp system/oracle_4U directory=dir1 dumpfile=tbszhs2.dmp remap_tablespace=tbszhs:example
- Import: Release 11.2.0.3.0 - Production on Tue Dec 19 20:02:23 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- UDI-28002: operation generated ORACLE error 28002
- ORA-28002: the password will expire within 7 days
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
- Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dir1 dumpfile=tbszhs2.dmp remap_tablespace=tbszhs:example
- Processing object type TABLE_EXPORT/TABLE/TABLE
- Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
- . . imported "HR"."TZHS" 5 KB 1 rows
- Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 20:02:25
复制代码- select * from dba_directories;
- create directory dir1 as '/home/oracle/dir1';
- select * from dba_tablespaces;
- select * from v$transportable_platform;
- select * from dba_tablespaces;
- alter tablespace tbsutf8_11g read write ;
- select * from hr.tutf8_11g;
- insert into hr.tutf8_11g values ( 1) ;
- select * from hr.tzhs;
- select tablespace_name from dba_tables t where t.table_name='TZHS';
- create directory dir2 as '/home/oracle/dir2';
复制代码- [oracle@station90 dir1]$ expdp system/oracle_4U directory=dir1 dumpfile=dir1:dir1_%U.dmp,dir2:dir2_%U.dmp parallel=4 full=y job_name=fulljob
- Export: Release 11.2.0.3.0 - Production on Tue Dec 19 20:10:12 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- UDE-28002: operation generated ORACLE error 28002
- ORA-28002: the password will expire within 7 days
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- Starting "SYSTEM"."FULLJOB": system/******** directory=dir1 dumpfile=dir1:dir1_%U.dmp,dir2:dir2_%U.dmp parallel 4 full=y job_name=fulljob
- Estimate in progress using BLOCKS method...
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
- Total estimation using BLOCKS method: 165.6 MB
- Processing object type DATABASE_EXPORT/TABLESPACE
- Processing object type DATABASE_EXPORT/PROFILE
- Processing object type DATABASE_EXPORT/SYS_USER/USER
- Processing object type DATABASE_EXPORT/SCHEMA/USER
- Processing object type DATABASE_EXPORT/ROLE
- Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
- Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
- Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
- Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
- Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
- Processing object type DATABASE_EXPORT/RESOURCE_COST
- Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
- Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
- Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
- Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
- Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
- Processing object type DATABASE_EXPORT/CONTEXT
- Export> help
- ------------------------------------------------------------------------------
- The following commands are valid while in interactive mode.
- Note: abbreviations are allowed.
- ADD_FILE
- Add dumpfile to dumpfile set.
- CONTINUE_CLIENT
- Return to logging mode. Job will be restarted if idle.
- EXIT_CLIENT
- Quit client session and leave job running.
- FILESIZE
- Default filesize (bytes) for subsequent ADD_FILE commands.
- HELP
- Summarize interactive commands.
- KILL_JOB
- Detach and delete job.
- PARALLEL
- Change the number of active workers for current job.
- REUSE_DUMPFILES
- Overwrite destination dump file if it exists [N].
- START_JOB
- Start or resume current job.
- Valid keyword values are: SKIP_CURRENT.
- STATUS
- Frequency (secs) job status is to be monitored where
- the default [0] will show new status when available.
- STOP_JOB
- Orderly shutdown of job execution and exits the client.
- Valid keyword values are: IMMEDIATE.
- Export> stop_job
- Are you sure you wish to stop this job ([yes]/no): yes
- [oracle@station90 dir1]$ expdp system/oracle_4U attach=fulljob
- Export: Release 11.2.0.3.0 - Production on Tue Dec 19 20:12:36 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- UDE-28002: operation generated ORACLE error 28002
- ORA-28002: the password will expire within 7 days
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- Job: FULLJOB
- Owner: SYSTEM
- Operation: EXPORT
- Creator Privs: TRUE
- GUID: 60B159517E2477ACE0535A00A8C0110D
- Start Time: Tuesday, 19 December, 2017 20:12:37
- Mode: FULL
- Instance: orcl
- Max Parallelism: 1
- EXPORT Job Parameters:
- Parameter Name Parameter Value:
- CLIENT_COMMAND system/******** directory=dir1 dumpfile=dir1:dir1_%U.dmp,dir2:dir2_%U.dmp parallel 4 full=y job_name=fulljob
- State: IDLING
- Bytes Processed: 0
- Current Parallelism: 1
- Job Error Count: 0
- Dump File: /home/oracle/dir1/dir1_%u.dmp
- Dump File: /home/oracle/dir1/dir1_01.dmp
- bytes written: 4,096
- Dump File: /home/oracle/dir2/dir2_01.dmp
- bytes written: 4,096
- Dump File: /home/oracle/dir2/dir2_%u.dmp
- Dump File: /home/oracle/dir1/parallel.dmp
- bytes written: 4,096
- Dump File: /home/oracle/dir1/4.dmp
- bytes written: 4,096
-
- Worker 1 Status:
- Process Name: DW00
- State: UNDEFINED
- Export> help
- ------------------------------------------------------------------------------
- The following commands are valid while in interactive mode.
- Note: abbreviations are allowed.
- ADD_FILE
- Add dumpfile to dumpfile set.
- CONTINUE_CLIENT
- Return to logging mode. Job will be restarted if idle.
- EXIT_CLIENT
- Quit client session and leave job running.
- FILESIZE
- Default filesize (bytes) for subsequent ADD_FILE commands.
- HELP
- Summarize interactive commands.
- KILL_JOB
- Detach and delete job.
- PARALLEL
- Change the number of active workers for current job.
- REUSE_DUMPFILES
- Overwrite destination dump file if it exists [N].
- START_JOB
- Start or resume current job.
- Valid keyword values are: SKIP_CURRENT.
- STATUS
- Frequency (secs) job status is to be monitored where
- the default [0] will show new status when available.
- STOP_JOB
- Orderly shutdown of job execution and exits the client.
- Valid keyword values are: IMMEDIATE.
- Export> START_JOB
- Export> status
- Job: FULLJOB
- Operation: EXPORT
- Mode: FULL
- State: EXECUTING
- Bytes Processed: 0
- Current Parallelism: 1
- Job Error Count: 0
- Dump File: /home/oracle/dir1/dir1_%u.dmp
- Dump File: /home/oracle/dir1/dir1_01.dmp
- bytes written: 937,984
- Dump File: /home/oracle/dir2/dir2_01.dmp
- bytes written: 4,096
- Dump File: /home/oracle/dir2/dir2_%u.dmp
- Dump File: /home/oracle/dir1/parallel.dmp
- bytes written: 4,096
- Dump File: /home/oracle/dir1/4.dmp
- bytes written: 4,096
-
- Worker 1 Status:
- Process Name: DW00
- State: EXECUTING
- Object Type: DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
- Completed Objects: 4
- Total Objects: 4
- Worker Parallelism: 1
- Export> status
- Job: FULLJOB
- Operation: EXPORT
- Mode: FULL
- State: EXECUTING
- Bytes Processed: 0
- Current Parallelism: 1
- Job Error Count: 0
- Dump File: /home/oracle/dir1/dir1_%u.dmp
- Dump File: /home/oracle/dir1/dir1_01.dmp
- bytes written: 937,984
- Dump File: /home/oracle/dir2/dir2_01.dmp
- bytes written: 4,096
- Dump File: /home/oracle/dir2/dir2_%u.dmp
- Dump File: /home/oracle/dir1/parallel.dmp
- bytes written: 4,096
- Dump File: /home/oracle/dir1/4.dmp
- bytes written: 4,096
-
- Worker 1 Status:
- Process Name: DW00
- State: EXECUTING
- Object Schema: OLAPSYS
- Object Name: CWM2$STOREDDIMLVLTPLS
- Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE
- Completed Objects: 223
- Worker Parallelism: 1
- Export>
复制代码
- [oracle@station90 dir1]$ expdp system/oracle_4U attach=fulljob
- Export: Release 11.2.0.3.0 - Production on Tue Dec 19 20:20:47 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- UDE-28002: operation generated ORACLE error 28002
- ORA-28002: the password will expire within 7 days
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- Job: FULLJOB
- Owner: SYSTEM
- Operation: EXPORT
- Creator Privs: TRUE
- GUID: 60B175B5B0920533E0535A00A8C07D0D
- Start Time: Tuesday, 19 December, 2017 20:18:09
- Mode: FULL
- Instance: orcl
- Max Parallelism: 8
- EXPORT Job Parameters:
- Parameter Name Parameter Value:
- CLIENT_COMMAND system/******** directory=dir1 dumpfile=dir1:dir1_%U.dmp,dir2:dir2_%U.dmp parallel=8 full=y job_name=fulljob
- State: STOP PENDING
- Bytes Processed: 123,975,896
- Percent Done: 99
- Current Parallelism: 8
- Job Error Count: 0
- Dump File: /home/oracle/dir1/dir1_%u.dmp
- Dump File: /home/oracle/dir1/dir1_01.dmp
- bytes written: 15,011,840
- Dump File: /home/oracle/dir2/dir2_%u.dmp
- Dump File: /home/oracle/dir2/dir2_01.dmp
- bytes written: 25,686,016
- Dump File: /home/oracle/dir1/dir1_02.dmp
- bytes written: 13,152,256
- Dump File: /home/oracle/dir2/dir2_02.dmp
- bytes written: 31,580,160
- Dump File: /home/oracle/dir1/dir1_03.dmp
- bytes written: 21,139,456
- Dump File: /home/oracle/dir2/dir2_03.dmp
- bytes written: 12,341,248
- Dump File: /home/oracle/dir1/dir1_04.dmp
- bytes written: 6,176,768
- Dump File: /home/oracle/dir2/dir2_04.dmp
- bytes written: 4,096
-
- Worker 1 Status:
- Process Name: DW00
- State: WORK WAITING
-
- Worker 2 Status:
- Process Name: DW01
- State: WORK WAITING
-
- Worker 3 Status:
- Process Name: DW02
- State: WORK WAITING
-
- Worker 4 Status:
- Process Name: DW03
- State: WORK WAITING
-
- Worker 5 Status:
- Process Name: DW04
- State: WORK WAITING
-
- Worker 6 Status:
- Process Name: DW05
- State: WORK WAITING
-
- Worker 7 Status:
- Process Name: DW06
- State: WORK WAITING
-
- Worker 8 Status:
- Process Name: DW07
- State: WORK WAITING
-
- Worker 9 Status:
- Process Name: DW08
- State: WORK WAITING
- Export>
复制代码 带并行度的作业不要去ctl+c中断。
parfile选择:
- directory=dirutforcl
- dumpfile=schema.dmp
- schemas=hr
- job_name=schemajob
- exclude=table:"in ('T05217')", procedure:"in ('SECURE_DML')"
- query='employees:where department_id=90'
- flashback_time='2017-12-19:20:43:36'
复制代码- [oracle@station90 ~]$ expdp system/oracle_4U parfile=parfile.ora
- Export: Release 11.2.0.3.0 - Production on Tue Dec 19 20:47:21 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- UDE-28002: operation generated ORACLE error 28002
- ORA-28002: the password will expire within 7 days
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- Starting "SYSTEM"."SCHEMAJOB": system/******** parfile=parfile.ora
- Estimate in progress using BLOCKS method...
- Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
- Total estimation using BLOCKS method: 960 KB
- Processing object type SCHEMA_EXPORT/USER
- Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
- Processing object type SCHEMA_EXPORT/ROLE_GRANT
- Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
- Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
- Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
- Processing object type SCHEMA_EXPORT/TABLE/TABLE
- Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
- Processing object type SCHEMA_EXPORT/TABLE/COMMENT
- Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
- Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
- Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
- Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
- Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
- Processing object type SCHEMA_EXPORT/VIEW/VIEW
- Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
- Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
- Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
- . . exported "HR"."PLAN_TABLE1" 22.73 KB 9 rows
- . . exported "HR"."COUNTRIES" 6.367 KB 25 rows
- . . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows
- . . exported "HR"."DEPT2" 7 KB 27 rows
- . . exported "HR"."EMP2" 16.80 KB 107 rows
- . . exported "HR"."EMPLOYEES" 9.445 KB 3 rows
- . . exported "HR"."IOTEMP" 7.976 KB 0 rows
- . . exported "HR"."JOBS" 6.992 KB 19 rows
- . . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows
- . . exported "HR"."LOCATIONS" 8.273 KB 23 rows
- . . exported "HR"."REGIONS" 5.476 KB 4 rows
- . . exported "HR"."T16949" 5 KB 1 rows
- . . exported "HR"."TUTF8_11G" 5.007 KB 1 rows
- . . exported "HR"."TZHS" 5 KB 1 rows
- Master table "SYSTEM"."SCHEMAJOB" successfully loaded/unloaded
- ******************************************************************************
- Dump file set for SYSTEM.SCHEMAJOB is:
- /home/oracle/dir1/schema.dmp
- Job "SYSTEM"."SCHEMAJOB" successfully completed at 20:47:52
- [oracle@station90 ~]$
复制代码 network link 建义用公共数据库链
源头用户user1目的地也要用user1进行:
- [oracle@station90 ~]$ expdp system/oracle_4U parfile=parfile.ora
- Export: Release 11.2.0.3.0 - Production on Tue Dec 19 20:47:21 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- UDE-28002: operation generated ORACLE error 28002
- ORA-28002: the password will expire within 7 days
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Data Mining
- and Real Application Testing options
- Starting "SYSTEM"."SCHEMAJOB": system/******** parfile=parfile.ora
- Estimate in progress using BLOCKS method...
- Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
- Total estimation using BLOCKS method: 960 KB
- Processing object type SCHEMA_EXPORT/USER
- Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
- Processing object type SCHEMA_EXPORT/ROLE_GRANT
- Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
- Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
- Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
- Processing object type SCHEMA_EXPORT/TABLE/TABLE
- Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
- Processing object type SCHEMA_EXPORT/TABLE/COMMENT
- Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
- Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
- Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
- Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
- Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
- Processing object type SCHEMA_EXPORT/VIEW/VIEW
- Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
- Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
- Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
- . . exported "HR"."PLAN_TABLE1" 22.73 KB 9 rows
- . . exported "HR"."COUNTRIES" 6.367 KB 25 rows
- . . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows
- . . exported "HR"."DEPT2" 7 KB 27 rows
- . . exported "HR"."EMP2" 16.80 KB 107 rows
- . . exported "HR"."EMPLOYEES" 9.445 KB 3 rows
- . . exported "HR"."IOTEMP" 7.976 KB 0 rows
- . . exported "HR"."JOBS" 6.992 KB 19 rows
- . . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows
- . . exported "HR"."LOCATIONS" 8.273 KB 23 rows
- . . exported "HR"."REGIONS" 5.476 KB 4 rows
- . . exported "HR"."T16949" 5 KB 1 rows
- . . exported "HR"."TUTF8_11G" 5.007 KB 1 rows
- . . exported "HR"."TZHS" 5 KB 1 rows
- Master table "SYSTEM"."SCHEMAJOB" successfully loaded/unloaded
- ******************************************************************************
- Dump file set for SYSTEM.SCHEMAJOB is:
- /home/oracle/dir1/schema.dmp
- Job "SYSTEM"."SCHEMAJOB" successfully completed at 20:47:52
- [oracle@station90 ~]$
复制代码
|
|