Shulou(Shulou.com)06/01 Report--

Transport_Tablespace-EXP/IMP

Import the T1 table under the 192.168.3.199 database and the chenjc user into the 192.168.3.198 database and the chenjc user by transferring the tablespace (EXP/IMP)

Check the operating system version, database version

192.168.3.199

[oracle@ogg1 ~] $cat / etc/issue

Oracle Linux Server release 6.3

SQL > select * from v$version where rownum select * from v$version where rownum create tablespace chenjc datafile'/ u01 autoextend on

Tablespace created.

SQL > create user chenjc identified by chenjc default tablespace chenjc

User created.

SQL > grant connect,resource,dba to chenjc

Grant succeeded.

SQL > conn chenjc/chenjc

Connected.

SQL > create table T1 as select level id,sysdate as t_date from dual connect by level conn / as sysdba

Connected.

SQL > execute dbms_tts.transport_set_check (ts_list= > 'CHENJC',incl_constraints= > TRUE)

PL/SQL procedure successfully completed.

SQL > select * from transport_set_violations

No rows selected

/ * No return record, indicating that the transmission tablespace condition is met * /

4 set the tablespace to be transferred as read-only

SQL > alter tablespace chenjc read only

Tablespace altered.

Fifth, export the original data of the table space to be transmitted through the exp tool.

[oracle@ogg1 ~] $exp "'sys/oracle as sysdba'" file=chenjc.dmp log=chenjc.log transport_tablespace=y tablespaces=chenjc

Export: Release 11.2.0.3.0-Production on Mon Aug 3 09:40:25 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing 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 CHENJC...

. Exporting cluster definitions

. Exporting table definitions

. . Exporting table T1

. Exporting referential integrity constraints

. Exporting triggers

. End transportable tablespace metadata export

Export terminated successfully without warnings.

/ * double quotation marks + single quotation marks * /

/ *

Analog platform conversion (this step is not required for transmission on the same platform)

SQL > col platform_name for A35

SQL > select * from v$transportable_platform order by platform_id

RMAN > convert tablespace "TESTSPACE" to platform 'Microsoft Windows IA (32-bit)' format'd:\ TESTSPACE01.DBF'-- this is the destination address of the translation

, /

Copy the database file and the exported tablespace original file to the 192.168.3.198 server

[oracle@ogg1 ~] $scp chenjc.dmp 192.168.3.198:/home/oracle/

[oracle@ogg1 ~] $scp / u01/app/oracle/oradata/orcl/chenjc01.dbf 192.168.3.198:/home/oracle/

192.168.3.198

[oracle@ogg2 ~] $mv chenjc* / u01/app/oracle/oradata/orcl/

[oracle@ogg2 ~] $cd / u01/app/oracle/oradata/orcl/

[oracle@ogg2 orcl] $ll-rth

.

-rw-r--r-- 1 oracle oinstall 16K Aug 3 09:43 chenjc.dmp

-rw-r- 1 oracle oinstall 31m Aug 3 09:44 chenjc01.dbf

.

Seven target database create users, specify tablespaces (the target database cannot have tablespaces with the same name as the tablespaces to be transmitted)

SQL > create user chenjc identified by chenjc default tablespace users

User created.

SQL > grant connect,resource,dba to chenjc

Grant succeeded.

Import tablespaces through the imp tool

[oracle@ogg2 orcl] $imp "'sys/oracle as sysdba'" file=chenjc.dmp log=chenjc.log

Tablespaces=chenjc datafiles='/u01/app/oracle/oradata/orcl/chenjc01.dbf' transport_tablespace=y

Import: Release 11.2.0.3.0-Production on Mon Aug 3 10:14:15 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 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 CHENJC's objects into CHENJC

. . Importing table "T1"

. Importing SYS's objects into SYS

Import terminated successfully without warnings.

/ * datafiles must be an absolute path * /

9 modify the user default tablespace

SQL > alter user chenjc default tablespace chenjc

User altered.

Ten View

SQL > select name from v$dbfile

NAME

/ u01/app/oracle/oradata/orcl/system.dbf

/ u01/app/oracle/oradata/orcl/sysaux.dbf

/ u01/app/oracle/oradata/orcl/undotbs01.dbf

/ u01/app/oracle/oradata/orcl/user01.dbf

/ u01/app/oracle/oradata/orcl/ggm01.dbf

/ u01/app/oracle/oradata/orcl/chenjc01.dbf

6 rows selected.

SQL > conn chenjc/chenjc

SQL > select id,to_char (tasking date, dating, etc.) from T1 where rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.