Oracle利用传输表空间导出导入数据的步骤1

1.ltdb导出

root@t2000 # su - oracle

Sun Microsystems Inc. SunOS 5.10 Generic January 2005

$ ORACLE_SID=LTDB

$ export ORACLE_SID

$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 15 15:54:18 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn / as sysdba

Connected.

SQL> create tablespace TSTS datafile '/tmp/LTDB/TSTS01.DBF' size 10M ,'/tmp/LTDB/TSTS02.DBF' size 10M;

Tablespace created.

SQL> create user ts identified by ts default tablespace tsts;

User created.

SQL> grant connect to ts;

Grant succeeded.

SQL> grant resource to ts;

Grant succeeded.

SQL> conn ts/ts

Connected.

SQL> create table t1(c1 varchar(255),c2 varchar(255));

Table created.

SQL> insert into t1 select sys_guid(),sys_guid() from dual connect by level <=10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> select * from t1 where rownum<=5;

C1 C2

-------------------------------- --------------------------------

163FD8C45C092993E04400144F025BB6 163FD8C45C0A2993E04400144F025BB6

163FD8C45C0B2993E04400144F025BB6 163FD8C45C0C2993E04400144F025BB6

163FD8C45C0D2993E04400144F025BB6 163FD8C45C0E2993E04400144F025BB6

163FD8C45C0F2993E04400144F025BB6 163FD8C45C102993E04400144F025BB6

163FD8C45C112993E04400144F025BB6 163FD8C45C122993E04400144F025BB6

SQL> conn system/sys

Connected.

SQL> alter tablespace tsts read only

SQL> /

Tablespace altered.

SQL> ho

$ exp userid=\'sys/sys as sysdba\' tablespaces=tsts file=/tmp/tsts.dmp l transport_tablespace=y

Export: Release 10.2.0.1.0 - Production on Thu Jun 15 16:20:29 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

Note: table data (rows) will not be exported

About to export transportable tablespace metadata...

For tablespace TSTS ...

. exporting cluster definitions

. exporting table definitions

. . exporting table

. exporting referential integrity constraints

. exporting triggers

. end transportable tablespace metadata export

Export terminated successfully without warnings.

2.epras导入

=============================================

root@t2000 # su - oracle

Sun Microsystems Inc. SunOS 5.10 Generic January 2005

$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 15 16:26:11 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn / as sysdba

Connected.

SQL> ho

复制数据文件，此时数据库ltdb的tsts表空间还是read only

$ cp /tmp/LTDB/TS*DBF /oracle

建立目标数据库上的用户，如果不建立，导入时需要加touser=参数，否则会出错

SQL> create user ts identified by ts;

User created.

SQL> ho

SQL> revoke connect from ts;

Revoke succeeded.

SQL> revoke resource from ts;

Revoke succeeded.

SQL> ho

$ imp userid=\'sys/sys as sysdba\' tablespaces=tsts file=/tmp/tsts.dmp transport_tablespace=y datafiles=/oracle/TSTS01.DBF, /oracle/TSTS02.DBF

Import: Release 10.2.0.1.0 - Production on Thu Jun 15 18:24:06 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

About to import transportable tablespace(s) metadata...

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing SYS's objects into SYS

. importing SYS's objects into SYS

. importing TS's objects into TS

. . importing table "T1"

. importing SYS's objects into SYS

Import terminated successfully without warnings.

$ exit

SQL> alter tablespace tsts read write;

Tablespace altered.

SQL> select table_name from all_tables where owner='TS';

TABLE_NAME

------------------------------

T1

SQL> grant connect to TS;

Grant succeeded.

SQL> grant resource to TS;

Grant succeeded.

SQL> conn ts/ts

Connected.

SQL> set lines 132

SQL> set pages 9999

SQL> col c1 format a40

SQL> col c2 format a40

SQL> select * from t1 where rownum<=5;

C1 C2

---------------------------------------- ----------------------------------------

163FD8C45C092993E04400144F025BB6 163FD8C45C0A2993E04400144F025BB6

163FD8C45C0B2993E04400144F025BB6 163FD8C45C0C2993E04400144F025BB6

163FD8C45C0D2993E04400144F025BB6 163FD8C45C0E2993E04400144F025BB6

163FD8C45C0F2993E04400144F025BB6 163FD8C45C102993E04400144F025BB6

163FD8C45C112993E04400144F025BB6 163FD8C45C122993E04400144F025BB6

SQL>

上一页  [1] [2] 

【责编:Ken】