Oracle创建表空间、用户、分配权限、导出、导入数据

秒速五厘米 2022-06-03 04:23 460阅读 0赞

文章转载：https://segmentfault.com/a/1190000006704150      仅用于个人学习、收藏自查，谢谢原作者。

> 以系统管理员登录，命令：`sqlplus / as sysdba` \#首先需要 `su - oracle` 进入到oracle用户下

# 一、分为四步 #

/第1步：创建临时表空间 /

create temporary tablespace User_Temp tempfile '/u01/app/oracle/oradata/orcl/user_temp.dbf' size 200m autoextend on next 100m maxsize 20480m extent management local; 注:此步创建的是临时表空间，可以多个数据公用一个临时表空间，注意创建的大小即可，名称随意。

/第2步：创建数据表空间 /

create tablespace User_Data logging datafile '/u01/app/oracle/oradata/orcl/user_data.dbf' size 200m autoextend on next 100m maxsize 20480m extent management local; 注:此步注意数据表空间的名称最好与导出的备份文件所用的表空间名称一致，不一致容易报错！ 用UltraEdit打开dmp文件切换16进制编辑，搜索tablespace可以查看备份文件的表空间名称。

/第3步：创建用户并指定表空间/

create user User_Name identified by "Passwd" default tablespace user_data temporary tablespace user_temp; 注:用户名最好与导出的备份文件的数据库名保持一致！

/第4步：给用户授予权限 /

grant connect,resource,dba to User_Name;

# 二、其他： #

/修改用户密码 /

alter user [username] identified by [password];

/删除用户 /

drop user User_Name cascade;

/删除表空间 /

DROP TABLESPACE 表空间名 INCLUDING CONTENTS AND DATAFILES;

/清空某张表/

TRUNCATE TABLE tableName
    
    **tableName是要清空表的表名**

/清空当前用户下所有的表和数据的方法 /

> 使用场景：覆盖一个已存在的库，并且里边有数据，这时直接导入备份文件会报错，只能清空里边原有表和数据之后再导入；当然你也可以新建一个其他库来导入。

$ su - oracle  #切换到Oracle用户
    $ sqlplus / as sysdba  #登录数据库控制台
    SQL>select 'Drop table '||table_name||';' from all_tables where owner='User_Name';
    注：`User_Name`英文一定要大写；将会输出一批删除表的sql语句，这些SQL语句执行一下就可以了（需要有drop table的权限）
    
    建议使用sqldeveloper图形化客户端连接Oracle数据库执行删除语句。

# 三、数据库导入、导出 #

> 两种方式：exp/imp方式、数据库泵expdp/impdp方式，推荐用数据泵的方式，支持大数据量。

## 1、数据泵expdp/impdp ##

1）前期准备，dumpdir目录创建

以Linux系统为例
    $ mkdir /home/oracle/dumpdir
    #执行该命令之前，要首先在硬盘上创建'/home/oracle/dumpdir'数据库导入、导出所对应的文件夹
    $ su - oracle  #切换到Oracle用户
    $ sqlplus / as sysdba  #登录数据库控制台
    SQL>create directory dumpdir as '/home/oracle/dumpdir';  #创建数据泵导入、导出目录
    SQL>select * from dba_directories;  #查看一下是否已存在该目录
    -------------------------------------------------------------------------------
    SYS                            DUMPDIR
    /home/oracle/dumpdir
    
    SQL>grant read,write on directory dumpdir to dbname;
    #授权给操作用户这个dump目录的权限【dbname是一个已经存在的数据库用户】
    SQl>exit

2）expdp数据泵导出

expdp User_Name/Password@orcl directory=dumpdir dumpfile=User_Name.bak.2016.1.1.dmp logfile=User_Name.bak.2016.1.1.log
    
    注：几个要点，用户名密码不用说，orcl是你配置的实例名；dumpdir是个变量，就是上边那步创建的目录，可以多个，名称随意；
    备份完文件会存放在创建的/home/oracle/dumpdir目录下。

3）impdp数据泵导入

impdp User_Name/Password@orcl directory=dumpdir dumpfile=User_Name.bak.2016.1.1.dmp remap_schema=SOURCE_USER_Name:DEST_USER_Name
    
    注：前几个参数跟导出时一样，导入需要把备份文件放到dumpdir目录下；多了一个remap_schema，
    是填写备份文件的数据库用户名`SOURCE`和本机创建的数据库用户名`DEST`，这个在本机创建的用户名不一样的情况下需要用到。
    如果一样写成一样的就行了。

## 2）exp/imp方式 ##

exp命令交互导出

$ su - oracle $ exp  #Windows下cmd也是输入这个命令执行，下同。
    1.用户名:User_Name 回车
    2.口令：输入此用户名的口令，回车；
    3.输入数组提取缓冲区大小：直接回车，选择默认的4096；
    4.导出文件: EXPDAT.DMP > 填写导出生成的备份文件完整路径、文件名，注意相应路径要已存在，且要有足够空间，
    如：C:\backup\User_Name_2016.dmp ,回车；
    5.选择导出方式：直接回车，选择默认的按用户方式导出；
    6.导出权限：直接回车，选择默认的yes;
    7.导出表数据：直接回车，选择默认的yes; 
    8.导出权限：直接回车，选择默认的yes; 
    9.压缩区：直接回车，选择默认的yes; 
    10.要导出的用户：输入刚开始的用户名确认，注：一定要输入，不然没有数据导出！
    11.再次出现“要导出的用户”：此时直接回车即可；
    12.导出完成，其中提示：导出成功终止, 但出现警告；这种输入是正常的，说明导出操作成功完成；

imp导入

imp User_Name/Password@127.0.0.1:/orcl fromuser=User_Name touser=User_Name_New file=C:\backup\User_Name_2016.dmp co