启动dos窗口输入sqlplus / as sysdba;启动sqlplus(或者用pl/sql工具以system用户登录数据库)
创建数据库空间
CREATE TABLESPACE NNC_DATA01 DATAFILE 'D:\app\pos\oradata\orcl\nnc_data0101.dbf' SIZE 200M
AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;
CREATE TABLESPACE NNC_DATA02 DATAFILE 'D:\app\pos\oradata\orcl\nnc_data0201.dbf' SIZE 100M
AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;
CREATE TABLESPACE NNC_DATA03 DATAFILE 'D:\app\pos\oradata\orcl\nnc_data0301.dbf' SIZE 200M
AUTOEXTEND ON NEXT 100M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K;
CREATE TABLESPACE NNC_INDEX01 DATAFILE 'D:\app\pos\oradata\orcl\nnc_index0101.dbf' SIZE 200M
AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
CREATE TABLESPACE NNC_INDEX02 DATAFILE 'D:\app\pos\oradata\orcl\nnc_index0201.dbf' SIZE 100M
AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
查询创建的表空间
select * from dba_directories
--1、创建一个system用户(创建NC65ZS_0409用户)
CREATE USER NC65ZS_0409 IDENTIFIED BY 1 DEFAULT TABLESPACE NNC_DATA01 TEMPORARY TABLESPACE temp;
--2、创建存放数据泵的目录(ghhrzui数据泵的路径D:\ghhr20160408)
查看目录:select * from dba_directories;
create directory ghhrzui as 'D:\ghhr20160408';
--3、赋予读、写等权限
---(为NC65ZS_0409用户赋予读取ghhrzui 数据泵路径的权限)
select username from dba_users;
GRANT dba to NC65ZS_0409;--读写数据权限
grant connect to NC65ZS_0409;--连接的权限
grant read,write on directory ghhrzui to NC65ZS_0409;--赋予读写数据泵目录的权限
--4、导入数据泵
--(DIRECTORY=ghhr ghhr=存放数据泵路径的别名 REMAP_SCHEMA、logfile去log日志里查询)
impdp NC65ZS_0409/1@orcl DIRECTORY=ghhrzui REMAP_SCHEMA=CAHRNCDB_20160401:NC65ZS_0409 DUMPFILE=CAHRNCDB_20160401_exp11g.dmp logfile=CAHRNCDB_20160401_exp11g.log;