逻辑表的导入导出，首先想到的是设置字符集；

查看字符集（数据库字符集）：

select * from V$NLS_PARAMETERS

NLS_CHARACTERSET：数据库字符集

NLS_NCHAR_CHARACTERSET：国家字符集

设置会话字符集：

[oracle@gc2 dump]$ export nls_lang=AMERICAN_AMERICAN.WE8ISO8859P1

到出一个用户的某一个表：

[oracle@gc2 dump]$ exp tables=EMP file=/home/oracle/dump/emp.dump

Export: Release 10.2.0.1.0 - Production on Wed Apr 29 16:36:46 2015

Username: scott

Password:tiger

把到出来的Scott的表EMP导入到新建的用户ljn：

新建用户：

SQL> create user ljn identified by ljn account unlock;

User created.

SQL> grant resource,connect to ljn;

Grant succeeded.

导入数据：

[oracle@gc2 dump]$ imp file=/home/oracle/dump/emp.dump fromuser=scott touser=ljn ignore=y
Import: Release 10.2.0.1.0 - Production on Wed Apr 29 16:39:52 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: ljn
Password: ljn

导出用户的全部数据：

[oracle@gc2 dump]$ exp file=/home/oracle/dump/scott.dump
Export: Release 10.2.0.1.0 - Production on Wed Apr 29 17:04:49 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: scott
Password: tiger

把Scott导出的数据导入ljn：

[oracle@gc2 dump]$ imp file=/home/oracle/dump/emp.dump fromuser=scott touser=ljn ignore=y
Import: Release 10.2.0.1.0 - Production on Wed Apr 29 17:05:38 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: sys as sysdba

Password:sys

因为ljn和scott是同级别的用户，所以需要一个权限比较高的用户把数据导入到ljn

导出表空间：

创建一个表空间：

SQL> create tablespace test_ljn datafile '/u01/app/oracle/oradata/EMREP/ljn.dbf' size 50m autoextend on;

Tablespace created.

[oracle@gc2 dump]$ exp TRANSPORT_TABLESPACE=y tablespaces=test_ljn file=/home/oracle/dump/test_ljn.dump
Export: Release 10.2.0.1.0 - Production on Wed Apr 29 17:29:53 2015
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: sys as sysdba
Password: sys
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
EXP-00008: ORACLE error 29335 encountered
ORA-29335: tablespace 'TEST_LJN' is not read only
ORA-06512: at "SYS.DBMS_PLUGTS", line 507
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully

把表空间设置为只读：

SQL> alter tablespace test_ljn read only;

Tablespace altered.

重新导出表空间：

[oracle@gc2 dump]$ exp transport_tablespace=y tablespaces=test_ljn file=/home/oracle/dump/test_ljn.dump

Export terminated successfully without warnings.

删除表空间：

SQL> drop tablespace test_ljn including contents and datafiles;

Tablespace dropped.

导入表空间：

[oracle@gc2 EMREP]$ imp TRANSPORT_TABLESPACE=y file=/home/oracle/dump/test_ljn.dump datafiles=/u01/app/oracle/oradata/EMREP/ljn.dbf ignore=y

导出整个数据库：

[oracle@gc2 EMREP]$ exp file=/home/oracle/dump/shujuku.dmp full=y

用户必须具有dba权限

ITPub博客

逻辑表的导入导出（exp、imp）

最新文章