通常mysql可以通过转储为sql的方式导出数据,且在新的数据库中在导入sql数据。但是对于oracle数据库时,对于数据量过大的情况,通过转储为sql文件的方式基本上是不支持的,一般都是需要通过oracle导出的dmp文件方式实现,但是跨库使用不同的用户名时,由于oracle库表空间的存在,也时常会造成数据导入存在部分失败的情况,以下是个人实践的几个经验。
1、通过navicate连接oracle数据库,直接复制表粘贴到目标的数据库
这种方法可以避免使用表空间造成的问题,因为会连同表空间一起复制到目标数据库中,亲测可行,但是由于navicate处理sql语言的方式比较慢,所以过程会比较耗时,建议可以通过查询表数据数据量的方式,把数据量比较小的表直接复制,数据量较大的表通过plsql导出后在导入。
过程中可能会出现的问题:
ORA-01653: 表 无法通过 128 (在表空间 USERS 中) 扩展
解决方法:
(1)、扩展表空间
select * from dba_data_files where tablespace_name = 'USERS';
alter database datafile '/home/oracle/oradata/ORCL/users01.dbf' autoextend on next 50M maxsize unlimited;
上一句查询表空间中,把file_name值赋值到下一句中,执行下一句会自动给表空间扩展50M的存储。但是一个dbf文件会有上限,很可能造成扩展无校的情况,此时可以通过给表空间增加一个dbf文件的方式,实现数据的扩展。
ALTER TABLESPACE "USERS" ADD DATAFILE '/home/oracle/oradata/ORCL/users02.dbf' SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;
以上为给表空间users新增一个存储的dbf文件。
(2)、oracle创建用户,赋权限
create user shenzhen identified by beijing;
grant connect,resource,dba to shenzhen;
(3)、使用管理员权限打开cmd,进行导入和导出oracle的dmp备份文件,需要环境变量和oracle环境
exp导出,需要使用imp进行导入;如果是expdp导出,则需要使用impdp导入,登录oracle数据库导出是expdp的方式,工具(plsql)等导出是exp方式。
导出
exp whsffz/whsffz@10.204.27.3:1521/orcl file=F:\11\21.dmp log=F:\11\21.log
exp whsffz/WHSFFZ2020@10.204.20.20:1521/orcl file=F:\11\20_bak.dmp log=F:\11\21_bak.log
导入dmp文件(使用oracle导出的dmp文件,需要使用impdp命令)
imp whsffz_test/whsffz_test@10.204.20.20:1521/orcl file=F:\11\21.dmp full=y ignore=y
(4)、使用oracle导出(非plsql等工具)
这种方式对于数据量比较大时,执行的还是很快的,不然通过plsql去导出的话,时间太慢,还容易出错。
首先,登录linux服务器
其次,切换oracle用户
su oracle
执行导出命令(使用系统管理员的账户和密码,schemas为选择的用户,expdir为导出目录,dumpfile为导出文件名)
expdp sysname/password schemas=user directory=expdir dumpfile=2020-8-7.dmp
然后进入导出文件的目录,找到文件,如果很大,建议压缩后在传出来
gzip 2020-8-7.dmp
然后拖出来就可以了。
(5)、导入本地oracle数据库
首先,一定要把另一个oracle导出的dmp文件,放到系统文件夹中,不是任意的文件夹都可以的,如果不知道,可以通过如下方式
用管理员身份打开cmd,通过sqlplus查看系统的dmp文件夹
set ORACLE_SID=orcl
sqlplus / as sysdba
连接后,执行select * from dba_directories;查看系统的相关路径
个人是把dmp文件放到下图的路径中的
执行导入命令
impdp loginname/password@ip:port/orcl file=D:\oracle_database\admin\orcl\dpdump\2020-11-12.dmp full=y ignore=y
上一步执行完成,在用链接工具查看,已经出来数据了。
三、表空间相关
1、查看表空间名称,使用大小,位置等信息,下两条sql
select
t.tablespace_name,
round(sum(bytes/(1024*1024)),0) ts_size
from
dba_tablespaces t,
dba_data_files d where t.tablespace_name = d.tablespace_name
group by t.tablespace_name; select
tablespace_name,
file_id, file_name,
round(bytes/(1024*1024),0) total_space
from
dba_data_files
order by tablespace_name;
2、创建表空间
create tablespace AAA datafile '/data/oracle/product/11.2.0/db_1/dbs/AAA.dbf'
size 1000M autoextend on next 100M;
3、如果已经创建了用户,给用户添加默认表空间(第一个AAA为登录用户,第二个为密码,第三个为表空间)
alter user AAA identified by AAA default tablespace AAA;
4、删除表空间(需要先删除使用了表空间的数据表,如果还删除不了就要删除表空间的用户)
drop tablesp AAA;
或drop tablespace AAA INCLUDING CONTENTS and datafiles;