DEFAULT DIRECTORY dir1
LOCATION ('COUNTRIES_EXT.dat') )
PARALLEL
REJECT LIMIT UNLIMITED
as
select COUNTRY_ID,COUNTRY_NAME,COUNTRY_REGION from SH.COUNTRIES;
4.2. Create another external table called COUNTRIES_EXT in the EMREP database owned by SYSTEM.The source of the data is the external file(s) created in the previous step.
CREATE TABLE sh.COUNTRIES_EXT (
COUNTRY_ID NUMBER,
COUNTRY_NAME VARCHAR2(40), COUNTRY_REGION VARCHAR2(20) )
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY dir1
LOCATION ('COUNTRIES_EXT.dat') )
PARALLEL
REJECT LIMIT UNLIMITED;
至此，数据仓库管理部分完成。
Section 5: 数据库管理
Transportable Tablespace
1. Use the import utility to import all of the objects contained in the sst.dmp file into the OLTP_USER schema in the PROD database.(The exported user was SST.)
2. Transport a copy of the OLTP tablespace from the PROD database to the EMREP database. After you have completed the task.the OLTP tablespace should be available for both reading and writing in both databases.All of the objects owned by the user OLTP_USER in the PROD database should be present in the EMREP database after the tablespace is transported.
参考文档：Administrator's Guide=> 8 Managing Tablespaces=> Transporting Tablespaces
Between Databases
题目说明：1.用Imp将sst.dmp文件的所有对象导入到OLTP_USER schema中（导出的用户是sst）
2.将PROD数据库中的OLTP表空间传输到EMREP数据库中，传输完成后，OLTP表空间必须在两个数据库均可读可写，所有在PROD数据库OLTP_USER用户下的对象必须在EMREP数据库中存在。
准备工作：
生成sst.dmp文件：
[oracle@rac1 scripts]$ export ORACLE_SID=PROD [oracle@rac1 scripts]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 9 10:17:58 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
SQL> create user sst identified by sst default tablespace oltp; User created.
SQL> grant dba to sst; Grant succeeded.
SQL> conn sst/sst Connected.
SQL> create table t as select * from all_objects; Table created.
[oracle@rac1 ~]$ exp sst/sst@prod file=sst.dmp
Export: Release 10.2.0.1.0 - Production on Fri Mar 9 10:26:24 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions . exporting foreign function library names for user SST . exporting PUBLIC type synonyms . exporting private type synonyms
. exporting object type definitions for user SST About to export SST's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions
. about to export SST's tables via Conventional Path ...
. . exporting table T 9612 rows exported . exporting synonyms . exporting views
. exporting stored procedures . exporting operators
. exporting referential integrity constraints . exporting triggers . exporting indextypes
. exporting bitmap, functional and extensible indexes . exporting posttables actions . exporting materialized views . exporting snapshot logs . exporting job queues
. exporting refresh groups and children . exporting dimensions
. exporting post-schema procedural objects and actions . exporting statistics
Export terminated successfully without warnings.
SQL> conn / as sysdba Connected.
SQL> drop user sst cascade; User dropped.
步骤：
1.创建oltp_user用户
SQL> create user oltp_user identified by oracle account unlock; User created.
2.给OLTP_USER赋予基本的权限
SQL> grant connect,resource to oltp_user; Grant succeeded.
3. 使用imp导入sst.dmp到oltp_user schema
[oracle@rac1 ~]$ imp system/oracle@prod file=sst.dmp buffer=100000 fromuser=sst touser=oltp_user
Import: Release 10.2.0.1.0 - Production on Fri Mar 9 10:34:51 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SST, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing SST's objects into OLTP_USER
. . importing table \ 9612 rows imported Import terminated successfully without warnings.
4.检查OLTP表空间是否是自包含表空间（要被传输的表空间中的对象没有引用被传输的表空间之外的对象，这种表空间就是自包含表空间） [oracle@rac1 ~]$ sqlplus sys/oracle@prod as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 9 10:36:17 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
SQL> exec dbms_tts.transport_set_check('OLTP',true); PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; no rows selected
[oracle@rac1 ~]$ mkdir dir
5.创建传输集的导出目录
SQL> create directory dir as '/home/oracle/dir'; Directory created.
6.将OLTP表空间置为只读模式 SQL> alter tablespace oltp read only; Tablespace altered.
7.生成传输表空间的传输集 [oracle@rac1 ~]$ expdp system/oracle@prod dumpfile=oltp.dmp directory=dir TRANSPORT_TABLESPACES=oltp;
Export: Release 10.2.0.1.0 - Production on Friday, 09 March, 2012 10:43:44
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
Starting \ system/********@prod dumpfile=oltp.dmp directory=dir TRANSPORT_TABLESPACES=oltp Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table \****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is: /home/oracle/dir/oltp.dmp
Job \
8.在EMREP数据库中创建用户oltp_user
[oracle@rac1 ~]$ export ORACLE_SID=EMREP [oracle@rac1 ~]$ sqlplus sys/oracle@emrep as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Mar 9 10:45:09 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
SQL> create user oltp_user identified by oracle; User created.
9.赋予oltp_user基本的权限 SQL> grant dba to oltp_user; Grant succeeded.
10.创建传输集导入的逻辑目录