Test the table space function for oracle transmission (including detailed procedures)

Test the table space function of oracle transmission (including detailed procedures) 1. Export the table space from the source server to the source server: 10.1.122.55 target server: 10.1.122.540. note: If the character set is not set here, an error will be reported during the import. For details, see the end of the article. Suse11sp2 :~ # Export LANG = AMERICAN_AMERICA.AL32UTF8suse11sp2: ~> Export NLS_LANG = AMERICAN_AMERICA.AL32UTF8 suse11sp2: ~> Sqlplus/as sysdba SQL * Plus: Release 11.2.0.3.0 Production on Wed Jul 24 14:45:47 2013 Copyright (c) 1982,201 1, Oracle. all rights reserved. 1. prepare the tablespace Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit ProductionWith the Partitioning, OLAP, data Mining and Real Application Testing options SQL> create tablespace aaa datafile '/oracle/oradata/aa. dbf'size 100 M; Table Space created. SQL> CREATE USER aaa IDENTIFIED BY aaaDEFAULT TABLESPACE aaaTEMPORARY TABLESPACE temp; 2 3 User created. SQL> GRANT CONNECT, RESOURCE TO aaa; Grant succeeded. SQL> REVOKE UNLIMITED TABLESPACE FROM aaa; Revoke succeeded. SQL> ALTER USER aaa QUOTA UNLIMITED ON aaa; User altered. SQL> conn aaa/aaa; Connected. SQL> create table a1 (id varchar2 (10), name varchar2 (20); Table created. SQL> ins Ert into a1 values ('01', 'lurou'); 1 row created. SQL> insert into a1 values ('02', 'Hello, DBA! '); 1 row created. SQL> COMMIT; Commit complete. SQL> select * from a1; ID NAME ---------- ------------------ 01 lurou02 hello, DBA! SQL> 2. check SQL> conn/as sysdbaConnected before transmission. SQL> execute sys. dbms_tts.transport_set_check ('aaa', true); PL/SQL procedure successfully completed. SQL> select * from sys. transport_set_violations; no rows selected SQL> 3. set the tablespace to read-only SQL> alter Tablespace aaa read only; tablespace altered. SQL> commit; Commit complete. SQL> 4. export tablespace 1) in an episode, you must use sys dbasuse11sp2: ~> Exp system/its7888 $ tablespaces = aaa file =/tmp/aaatts. dmp transport_tablespace = y Export: Release 11.2.0.3.0-Production on Wed Jul 24 14:58:19 2013 Copyright (c) 1982,201 1, Oracle and/or its affiliates. all rights reserved. connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsEXP-0004 4: must be connected 'as sysdba' to do Point-in-time Recovery or Transportable Tablespace importEXP-00000: Export terminated unsuccessfully 2) Export successful suse11sp2: ~> Exp \ 'sys/its7888 $ as sysdba \ 'tablespaces = aaa file =/tmp/aaatts. dmp transport_tablespace = y Export: Release 11.2.0.3.0-Production on Wed Jul 24 14:59:13 2013 Copyright (c) 1982,201 1, Oracle and/or its affiliates. all rights reserved. connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing opti OnsExport done in AL32UTF8 character set and UTF8 NCHAR character setNote: table data (rows) will not be exportedAbout to export transportable tablespace metadata... for tablespace AAA .... exporting cluster definitions. exporting table definitions .. exporting table A1. exporting referential integrity constraints. exporting triggers. end transportable tablespace metadata exportExport terminated su Ccessfully without warnings. suse11sp2: ~> 2. Import the tablespace to the target server. 5. copy exported dmp files and data files to scp/oracle/oradata/aa on the target server. dbf oracle@10.1.122.54:/oracle/oradatascp/tmp/aaatts. dmp oracle@10.1.122.54:/tmp 6. create user SQL> create User bbb identified by bbb; user created. SQL> grant connect, resource to bbb; Grant succeeded. SQL> commit; Commit complete. 7. import tablespace imp \ 'sys/its7888 $ as sysdba \ 'tablespaces = aaa transport_tablespace = y file =/tmp/aaatts. dmp datafiles =/CMDL E/oradata/aa. dbf fromuser = aaa touser = bbb SQL> conn aaa/aaaConnected. SQL> select * from a1 2; ID NAME ---------- ------------------ 01 lurou02 hello, DBA! SQL> conn/as sysdbaConnected. SQL> drop tablespace aaa including contents and datafiles; Tablespace dropped. ------------------------ 3. suse11sp2: ~> Exp \ 'sys/its7888 $ as sysdba \ 'tablespaces = aaa file =/tmp/aaatts. dmp transport_tablespace = y Export: Release 11.2.0.3.0-Production on Wed Jul 24 15:31:57 2013 Copyright (c) 1982,201 1, Oracle and/or its affiliates. all rights reserved. connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing opti OnsExport done in US7ASCII character set and UTF8 NCHAR character setserver uses AL32UTF8 character set (possible charset conversion) Note: table data (rows) will not be converted to export transportable tablespace metadata... for tablespace AAA .... exporting cluster definitions. exporting table definitions .. exporting table A1. exporting referential integrity constraints. exporting triggers. End transportable tablespace metadata exportExport terminated successfully without warnings. suse11sp2: ~> Imp userid = system/its7888 $ tablespaces = aaa transport_tablespace = y file =/tmp/aaatts. dmp datafiles =/oracle/oradata/aa. dbf Import: Release 11.2.0.3.0-Production on Wed Jul 24 09:22:57 2013 Copyright (c) 1982,201 1, Oracle and/or its affiliates. all rights reserved. connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT: via conventional pathimport done in US7ASCII character set and character NCHAR character setimport server uses character set (possible charset conversion) IMP-00053: Import mode incompatible with Export dump fileIMP-00000: Import terminated