1、imp/exp导入导出
1.1、exp导出
exp sourceuser/sourcepwd@orcl buffer=64000 file=D:\DB.dmp owner=sourceuser
1.2、imp导入
--导入sql文件
sqlplus username/password@orcl @D:\DB.sql
--导入dmp文件
imp username/password@orcl buffer=64000 file=D:\DB.dmp fromuser=sourceuser touser=username ignore=y
2、impdp/expdp 导入导出
2.1、expdp 导出
①、创建逻辑目录,最好以system等管理员创建
--drop directory dmp_dir
create directory dmp_dir as 'D:\Users\Tony\Desktop\db';
select * from dba_directories;
②、导出
expdp TEST/TEST@orcl schemas=TEST DIRECTORY=dmp_dir DUMPFILE=TEST0812.dmp logfile=TEST0812DMP.log
2.2、impdp导入
①、将dmp文件放入select * from dba_directories查询出来DATA_PUMP_DIR对应的位置。
select * from dba_directories;
②、导入
impdp TEST/TEST@orcl file=TEST0812.dmp REMAP_SCHEMA=TEST:TEST2 logfile=TEST0812DMP.log full=y
注意:TEST2会自动建立,其权限和使用的表空间与TEST相同,但此时用TEST2无法登录,必须修改TEST2的密码
impdp/expdp 导入导出参考:
https://blog.csdn.net/qq_25034619/article/details/81382084
https://blog.csdn.net/weixin_43822280/article/details/123472262
3、创建表空间
create tablespace TS_DEMO_DAT
datafile 'D:\app\Tony\oradata\orcl\TS_DEMO_DAT.DBF'
size 100m autoextend on
next 100m maxsize 4G
logging online
extent management local
autoallocate blocksize 8K
segment space
management auto
flashback on;
4、创建临时表空间
create temporary tablespace TS_DEMO_TMP
tempfile 'D:\app\Tony\oradata\orcl\TS_DEMO_TMP.DBF'
size 100m autoextend on
next 100m maxsize 4G
extent management local;
5、创建用户并指定表空间
create user username identified by password
default tablespace TS_DEMO_DAT
temporary tablespace TS_DEMO_TMP;
6、给用户授予权限
grant connect,resource,dba to username;
7、删除用户
drop user username cascade;
8、删除表空间及其关联的物理文件
drop tablespace TS_DEMO_DAT including contents and datafiles;
drop tablespace TS_DEMO_TMP including contents and datafiles;
环境说明:以上都是在Oracle11g环境执行的。
参考:
清理oracle数据库空间
http://t.zoukankan.com/eavn-p-4365170.html