oracle11g新建用户,表空间,导入数据记录

上一篇记了如何在linux中搭建oracle环境,
环境搭完之后就试试把windows中导出的dmp文件导入linux的oracle

以管理员身份登陆sqlplus

[oracle@masterial ~]$ sqlplus /nolog
SQL> conn / as sysdba

---

新建用户

SQL> create user oratest identified by ******;

User created.

　
　
给用户授权

SQL> grant dba to oratest;

Grant succeeded.

　
　　
创建临时表空间

SQL> create temporary tablespace YIKANG_TEMP
  2  tempfile '/home/oracle/app/oracle/oradata/orcl/YIKANG_TEMP.dbf'
  3  size 500m
  4  autoextend on
  5  next 500m maxsize 20480m
  6  extent management local;

Tablespace created.

　
　　
创建表空间

SQL> create tablespace YIKANG logging
  2  datafile '/home/oracle/app/oracle/oradata/orcl/YIKANG.dbf'
  3  size 1024m
  4  autoextend on
  5  next 1024m maxsize 20480m
  6  extent management local;

Tablespace created.

需要注意的是创建的用户名和表空间名最好和windows的oracle用户名表空间名一致,否则导入的时候会报错
　
　　
给用户分配表空间

SQL> alter user oratest default tablespace YIKANG;

User altered.

导入表,导入之前把dmp文件放到$ORACLE_HOME/rdbms/log下

[oracle@masterial ~]$ imp oratest/******@orcl file='/home/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/log/ora_test.dmp' full=y;

　
　　
导出表

[oracle@masterial ~]$ exp oratest/******@orcl file=/home/oracle/db_back/yk.dmp

导出时如果报EXP-00091: Exporting questionable statistics.错误
则指定Linux系统的NLS_LANG环境变量为数据库的数据集

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@masterial ~]$ $export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
-bash: NLS_LANG=AMERICAN_AMERICA.ZHS16GBK: command not found
[oracle@masterial ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

搞定