2 查看字符集
318525ac3329de1039.png (22.96 KB, 下载次数: 428)
2018-4-3 15:51 上传
2查看字符集
SQL> shutdownimmediate;
SQL> startupmount
SQL> ALTERSYSTEM ENABLE RESTRICTED SESSION;
SQL> alter database open;
SQL> ALTERDATABASE CHARACTERSET ZHS16GBK;
SQL> ALTERDATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;
如果报错：ORA-12712，可以跳过超集检查：
SQL> ALTERDATABASE character set INTERNAL_USE ZHS16GBK;
SQL> select* from nls_database_parameters;
内容略
SQL> shutdownimmediate;
SQL> startup
SQL>conn/ as sysdba
Connected.
SQL> createtablespace app1tbs datafile '/u01/app/oracle/oradata/sztech1/app1tbs.dbf' size20m;
Tablespacecreated.
SQL> createtablespace app2tbs datafile '/u01/app/oracle/oradata/sztech1/app2tbs.dbf' size20m;
Tablespacecreated.
SQL> create user user_app1 identified by user_app1 default tablespaceapp1tbs;
User created.
SQL> create user user_app2 identified by user_app2 default tablespaceapp2tbs;
User created.
SQL> grant connect,resource to user_app1;
Grant succeeded.
SQL> grant connect,resource to user_app2;
Grant succeeded.
SQL> create table user_app1.app1_tab tablespace app1tbs as select *from scott.emp;
Table created.
SQL> create table user_app2.app2_tab tablespace app2tbs as select *from scott.dept;
Table created.
SQL> execute dbms_tts.transport_set_check('app1tbs',true)
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
SQL> execute dbms_tts.transport_set_check('app2tbs',true)
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
结论：这个表空间集不存在违背自包含的条件，可以作为一个可传输表空间集
1.将表空间设置为只读状态
SQL> alter tablespace app1tbs read only;
Tablespace altered.
SQL> alter tablespace app2tbs read only;
Tablespace altered.
SQL> create directory dump_dir as '/u01/app/oracle/dumpdir';
Directory created.
SQL> select directory_name,directory_path from dba_directories;
DIRECTORY_NAME DIRECTORY_PATH
------------------------------------------------------------
DUMP_DIR /u01/app/oracle/dumpdir
[oracle@dbserver dumpdir]$ expdp system/oracle directory=dump_dirdumpfile=dmpfile.dmp transport_tablespaces='app1tbs,app2tbs';
5.复制数据文件到/u01/app/oracle/dumpdir目录下$ cp app1tbs.dbf /u01/app/oracle/dumpdir/
$ cp app2tbs.dbf /u01/app/oracle/dumpdir/
2.目标库创建用户
SQL> create user user_app1identified by user_app1；
用户已创建。
SQL> create user user_app2identified by user_app2;
用户已创建。
SQL> grant connect,resource touser_app1;
授权成功。
SQL> grant connect,resource touser_app2;
授权成功。
3.开始导入
导入成功！
4.将导入后的表空间更改为读写模式
SQL> select tablespace_name,statusfrom dba_tablespaces;
TABLESPACE_NAME STATUS
---------------------------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
APP1TBS READ ONLY
APP2TBS READ ONLY
已选择8行。
SQL> alter tablespace app1tbs readwrite;
表空间已更改。
SQL> alter tablespace app2tbs readwrite;
表空间已更改。
SQL> alter user user_app1 defaulttablespace app1tbs;
用户已更改。
SQL> alter user user_app2 defaulttablespace app2tbs;
用户已更改。
SQL> SELECTd.username,d.default_tablespace FROM dba_users d where d.username li
ke 'USER_%' ;
USERNAME DEFAULT_TABLESPACE
------------------------------------------------------------
USER_APP2 APP2TBS
USER_APP1 APP1TBS
八、查看导入后结果SQL>select * from user_app1.app1_tab;
内容略
已选择14行。
至此说明源Linux（32-bit）库已经导入到windows 64位库
------------------------- end ----------------------------------
希望各位老师指点！
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |