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;
2. 建测试表
conn a/a
drop table t;
create table t as select * from all_objects;
select count(*) from t;
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;
生成了ext_t.dmp文件
!ls -lrt ~/temp/*.dmp
4. 取得表结构
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>
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>
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>
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>
-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',
SQL> conn a/a
Connected.
SQL> set long 4000 pages 9999 line 140
SQL> select dbms_metadata.get_ddl('TABLE',
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>
DBMS_METADATA.GET_DDL('TABLE',
------------------------------
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
------------------------------
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>
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>
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>
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_
SQL> set serveroutput on size 1000000
SQL> exec system.show_dumpfile_info(p_
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>
------------------------------
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............: 5E27FE1E5914DA98E040A8C0100B61
...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
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
No comments:
Post a Comment