oracle 数据库导入与导出

1、登录：

      sqlplus  */*@orcl as  sysdba

2、建库：

     --创建表空间 无限制表空间(最大32G)
    create tablespace SL_data datafile '/data/oracle/oradata/orcl/SL.dbf' size 100m autoextend on next 256m maxsize unlimited;
   --创建临时表空间
    create temporary tablespace SL_temp tempfile  '/data/oracle/oradata/orcl/SL_temp.dbf' size 100m    autoextend on    next 256m  maxsize 2048m;
    --创建用户
    CREATE USER SL  IDENTIFIED BY SL  DEFAULT   TABLESPACE SL_data  TEMPORARY TABLESPACE SL_temp;
    --赋权
    grant DBA,CREATE SESSION ,UNLIMITED TABLESPACE,CREATE VIEW,SELECT ANY TABLE,SELECT ANY DICTIONARY to SL;
    grant CREATE ANY PROCEDURE,CREATE ANY SEQUENCE,CREATE ANY TABLE to SL  with admin option;

3、导出

 sqlplus  ls/ls@orcl as  sysdba
  
  create directory  dmpdir  as 'd:\dmpdir';
  
  Grant read,write on directory dmpdir  to ls;
  
  select * from dba_directories;

  expdp ls/ls@orcl schemas=ls DIRECTORY=dmpdir dumpfile=ls20220401.dmp

4、导入

sqlplus  ls/ls@orcl as  sysdba
  create directory dmpdir  as 'd:\dmpdir';
  create tablespace ls_data  datafile 'D:\app\Administrator\oradata\orcl\ls.dbf' size 100m autoextend on next 256m maxsize unlimited;
   --创建临时表空间
    create temporary tablespace ls_temp tempfile  'D:\app\Administrator\oradata\orcl\ls_temp.dbf' size 100m    autoextend on    next 256m  maxsize 2048m;
    --创建用户
    CREATE USER ls  IDENTIFIED BY ls  DEFAULT   TABLESPACE ls_data  TEMPORARY TABLESPACE ls_temp;
    --赋权
    grant DBA,CREATE SESSION ,UNLIMITED TABLESPACE,CREATE VIEW,SELECT ANY TABLE,SELECT ANY DICTIONARY to ls;
    grant CREATE ANY PROCEDURE,CREATE ANY SEQUENCE,CREATE ANY TABLE to ls  with admin option;
    －－相同表空间   相同模式
  impdp ls/ls@orcl SCHEMAS=ls directory=dmpdir dumpfile=ls.DMP 
  -- 不同模式、不同表空间
  impdp ls/ls@MTGXGY directory=dmpdir dumpfile=ls.dmp REMAP_SCHEMA=ls(旧）:ls  REMAP_TABLESPACE=ls_data(旧）:ls_data
  --不同表空间
  impdp ls/ls@orcl directory=dmpdir dumpfile=ls.dmp  REMAP_TABLESPACE=ls(旧）:ls_data
  --不同模式
  impdp ls/ls@orcl directory=dmpdir dumpfile=ls.dmp REMAP_SCHEMA=ls(旧）:ls