loading and unloading data using external table - 使用外部表装载和卸载数据

---------- Forwarded message ----------
From: wen xie
Date: Tue, Dec 16, 2008 at 19:14
Subject: Fwd: loading and unloading data using external table
To: xiewenxiewen at googlemail.com

---------- Forwarded message ----------
From: XIE WEN-MFK346
Date: Tue, Dec 16, 2008 at 18:50
Subject: loading and unloading data using external table
To: wen xie




loading and unloading data using external table


10g外部表提供了将数据导出的功能

1. 建目录, 授权

conn / as sysdba
create or replace directory dp_dir as '/home/oracle/temp';
grant read,write on directory dp_dir to a;
grant read,write on directory dp_dir to b;

SQL> conn / as sysdba
Connected.
SQL> create or replace directory dp_dir as '/home/oracle/temp';

Directory created.

SQL> grant read,write on directory dp_dir to a;

Grant succeeded.

SQL> grant read,write on directory dp_dir to b;

Grant succeeded.

SQL>

2. 建测试表

conn a/a
drop table t;
create table t as select * from all_objects;
select count(*) from t;

SQL> conn a/a
Connected.
SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table t as select * from all_objects;

Table created.

SQL> select count(*) from t;

COUNT(*)
----------
9447

SQL>

3. 导出到外部表

建立外部表, 用oracle_datapump方式导出

drop table ext_t;
create table ext_t
organization external (
type oracle_datapump
default directory dp_dir
access parameters (nologfile)
location ('ext_t.dmp')
)
reject limit unlimited
as
select * from t
/
select count(*) from ext_t;

SQL> drop table ext_t;
drop table ext_t
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table ext_t
organization external (
type oracle_datapump
default directory dp_dir
access parameters (nologfile)
location ('ext_t.dmp')
)
reject limit unlimited
as
select * from t
/
2 3 4 5 6 7 8 9 10 11
Table created.

SQL> select count(*) from ext_t;

COUNT(*)
----------
9447

SQL>

生成了ext_t.dmp文件
!ls -lrt ~/temp/*.dmp

SQL> !ls -lrt ~/temp/*.dmp
-rw-r----- 1 oracle oinstall 872448 Dec 16 09:57 /home/oracle/temp/ext_t.dmp

SQL>

4. 取得表结构

下一步建表用

conn a/a
set long 4000 pages 9999 line 140
select dbms_metadata.get_ddl('TABLE','EXT_T') from dual;

SQL> conn a/a
Connected.
SQL> set long 4000 pages 9999 line 140
SQL> select dbms_metadata.get_ddl('TABLE',

'EXT_T') from dual;

DBMS_METADATA.GET_DDL('TABLE','EXT_T')
--------------------------------------------------------------------------------

CREATE TABLE "A"."EXT_T"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(30),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "DP_DIR"
ACCESS PARAMETERS
( nologfile )
LOCATION
( 'ext_t.dmp'
)
)
REJECT LIMIT UNLIMITED



SQL>

或
conn b/b
desc all_objects

SQL> conn b/b
Connected.
SQL> desc all_objects
Name Null? Type
------------------------------

----------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED NOT NULL DATE
LAST_DDL_TIME NOT NULL DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

SQL>

5. 建外部表, 导入数据

字段名和顺序要跟导出表一致

drop table ext_t1;
create table ext_t1 (
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
SUBOBJECT_NAME VARCHAR2(30),
OBJECT_ID VARCHAR2(30),
DATA_OBJECT_ID VARCHAR2(30),
OBJECT_TYPE VARCHAR2(30),
CREATED VARCHAR2(30),
LAST_DDL_TIME VARCHAR2(30),
TIMESTAMP VARCHAR2(30),
STATUS VARCHAR2(30),
TEMPORARY VARCHAR2(30),
GENERATED VARCHAR2(30),
SECONDARY VARCHAR2(30)
)
organization external (
type oracle_datapump
default directory dp_dir
access parameters (nologfile)
location ('ext_t.dmp')
)
reject limit unlimited
/
select count(*) from ext_t1;

SQL> drop table ext_t1;
drop table ext_t1
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table ext_t1 (
2 OWNER VARCHAR2(30),
3 OBJECT_NAME VARCHAR2(30),
4 SUBOBJECT_NAME VARCHAR2(30),
5 OBJECT_ID VARCHAR2(30),
6 DATA_OBJECT_ID VARCHAR2(30),
7 OBJECT_TYPE VARCHAR2(30),
8 CREATED VARCHAR2(30),
9 LAST_DDL_TIME VARCHAR2(30),
10 TIMESTAMP VARCHAR2(30),
11 STATUS VARCHAR2(30),
12 TEMPORARY VARCHAR2(30),
13 GENERATED VARCHAR2(30),
14 SECONDARY VARCHAR2(30)
15 )
16 organization external (
17 type oracle_datapump
18 default directory dp_dir
19 access parameters (nologfile)
20 location ('ext_t.dmp')
21 )
22 reject limit unlimited
23 /

Table created.

SQL> select count(*) from ext_t1;

COUNT(*)
----------
9447

SQL>

6. 不能用impdp导入

报错格式不一致

conn b/b
!impdp b/b directory=dp_dir dumpfile=ext_t.dmp tables=ext_t nologfile=y remap_schema=a:b

SQL> conn b/b
Connected.
SQL> !impdp b/b directory=dp_dir dumpfile=ext_t.dmp tables=ext_t nologfile=y remap_schema=a:b

Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 16 December, 2008 10:42:22

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31619: invalid dump file "/home/oracle/temp/ext_t.dmp"



SQL>

set serveroutput on size 1000000
exec system.show_dumpfile_info(p_dir=> 'dp_dir', p_file=> 'ext_t.dmp')

SQL> set serveroutput on size 1000000
SQL> exec system.show_dumpfile_info(p_

dir=> 'dp_dir', p_file=> 'ext_t.dmp')
----------------------------------------------------------------------------
Purpose..: Obtain details about export dumpfile. Version: 19-MAR-2008
Required.: RDBMS version: 10.2.0.1.0 or higher
. Export dumpfile version: 7.3.4.0.0 or higher
. Export Data Pump dumpfile version: 10.1.0.1.0 or higher
Usage....: execute show_dumfile_info('DIRECTORY', 'DUMPFILE');
Example..: exec show_dumfile_info('MY_DIR', 'expdp_s.dmp')
----------------------------------------------------------------------------
Filename.: ext_t.dmp
Directory: dp_dir
Disk Path: /home/oracle/temp
Filetype.: 1 (Export Data Pump dumpfile)
----------------------------------------------------------------------------
...File Version....: 1.1 (Oracle10g Release 2: 10.2.0.x)
...Master Present..: 1 (Yes)
...GUID............: 5E27FE1E5914DA98E040A8C0100B6117
...File Number.....: 1000001
...Characterset ID.: 873 (AL32UTF8)
...Creation Date...: Tue Dec 16 09:57:52 2008
...Flags...........: 10
...Job Name........:
...Platform........: x86_64/Linux 2.4.xx
...Language........: AL32UTF8
...Block size......: 4096
...Metadata Compres: 1 (Yes)
...Job Version.....: 10.02.00.03.00
...Max Items Code..: 15
----------------------------------------------------------------------------

PL/SQL procedure successfully completed.

SQL>

外部链接:

Unloading and Loading Data with the ORACLE_DATAPUMP Access Driver

Using External Tables to Load and Unload Data

Unloading data using External table and Oracle_Datapump access Driver

Populating External Table

How to Gather the Header Information and the Content of an Export Dumpfile ?

GET_DUMPFILE_INFO Procedure

Xie Wen (谢文)
Network & Operations,
Multimedia Applications & Services (MDB) MOTOROLA Inc.
NO.104 mail box,
8th floor, Motorola Tower,
No. 1 Wang Jing East Road, Chao Yang District,
Beijing 100102 P. R. China
e-mail wenxie at motorola.com