window导入导出oracle数据库dmp备份文件命令行及常用命令和sql

业务需要创建多个数据库来分别存放不同的数据，再次整理一下数据ORCLE导入导出命令

1、导出备份

运行windows命令行窗口然后输入一下代码。file是文件绝对路径，扩展名为.DMP

exp [数据库账号]\[数据库密码]@[ip]/[数据库名] file="[文件路径]\文件名.DMP" 

exp cpdb/cpdb@172.16.8.139:1521/orcl file="d:20190413cpdb_backup.DMP"

2、创建表空间及新账户相关命令命令

使用有管理员权限的账号登录oracle（命令行登录或者使用plsql、DataGrip等oracle管理工具都行）执行以下sql来创建表空间和用户

-- 查询现有表空间及所在目录，方便下边创建新表空间时选择存放路径保持统一
select t1.name,t2.name from v$tablespace t1,v$datafile t2  where t1.ts# = t2.ts#;
-- 1、创建固定大小表空间并指定目录及大小
create tablespace cpdbd datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\cpdbd.dbf' size 2048M;
-- 2、创建动态自增表空间
create tablespace cpdbd datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\cpdbd.dbf'  size 500M autoextend on next 5M maxsize unlimited; 
  /*创建表空间*/
  create tablespace cpdbd
  /*表空间物理文件名称*/
  -- datafile 'cpdbd.dbf' 
  -- 这种方式指定表空间物理文件位置
  datafile 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\cpdbd.dbf' 
  -- 大小 500M，每次 5M 自动增大，最大不限制
  size 500M autoextend on next 5M maxsize unlimited; 

-- 删除表空间（表空间文件可以手动到目录中删除）
drop tablespace "NEWS_TABLESPACE";
-- 创建用户并指定表空间（每个用户都需要绑定一个表空间）
create user cpdbd identified by cpdbd default tablespace cpdbd;
-- 给用户赋链接和使用权限
grant connect,resource to cpdbd;
-- 用户赋dba权限
grant dba to cpdbd;

-- 其他常用命令：

-- 查看用户列表
select username from dba_users;

--修改密码
alter user system identified by 123456;

-- 删除用户 -级联（删除用户以及所有关联的数据库对象）
drop user cpdba cascade;

-- 删除表空间及文件
drop tablespace cpdba including contents and datafiles cascade constraints ;

3、新建数据库中导入数据库备份文件

imp [数据库账号]\[数据库密码]@[ip]/[数据库名] file="[文件路径]\文件名.DMP" fromuser=[开发环境的数据库用户名]  touser=[本地的数据库用户名] commit=yes ignore=yes

fromuser=user 指明来源用户（即需要导入备份文件中那个用户的数据，一般与touser配合使用更佳）
touser=user  指明目的用户（就是把现在dmp文件中的数据导入到目标库指定user用户下的库）
rows=y  是否上传表记录（确定导入的数据行）
commit=y 上传数据缓存区中记录上载后立即执行提交（表示每个数据缓冲满了之后提交一次，而不是导完一张表提交一次。这样会大大减少对系统回滚段等资源的消耗，对顺利完成导入是有益的）
freeback=10000  显示处理记录条数，缺省为0，即不显示
buffer=10240000  上载数据缓存区，以字节为单位，缺省依赖操作系统

导入有三种模式，分别为按照①full全部、②fromuser用户、③tables表三种模式导入。

indexes=n 指如果上传时索引已建立，此举项即使为n也无效，imp自动更新索引数据

①不指定用户导入（不推荐使用，稳妥起见使用方法二，指定用户名）

 Ⅰ 、备份时只备份的指定用户的数据（如本文中使用的导出语句）

-- 向新建账号导入数据库文件
-- 注意事项：1、192.168.1.145是数据库监听地址，如果你是本地导入，并且oracle监听了localhost时可以不写，直接写成cpdbd/cpdbd@orcl即可。
--          2、不写端口号
imp cpdbd/cpdbd@192.168.1.145/orcl  file=D:\oracledb_backup.dmp log=oradb.log full=y buffer=20480000 commit=y ignore=y feedback=10000

只要不报错 就ok了 !

②指定用户导入（推荐）

 Ⅰ 、备份时全数据库备份，导出时只需要导出指定用户的数据

 Ⅱ 、备份和还原时的用户名不同时

-- 向新建账号导入数据库文件
-- 注意事项：与方法Ⅰ 不同的是去掉了full=y 加上了fromuser=  touser= 指定来源和出处。
--      导入有三种模式，分别为按照①full全部、②fromuser用户、③tables表三种模式导入。这里用的指定用户的方式。
imp cpdbd/cpdbd@192.168.1.145/orcl  file=D:\oracledb_backup.dmp log=oradb.log fromuser=backdbuser touser=cpdbd buffer=20480000 commit=y ignore=y feedback=10000

 

表空间用户相关其他sql示例

1.查询  
/*查询所有表空间物理位置*/
  select name from v$datafile;
  /*查询当前用户的表空间*/
  select username,default_tablespace from user_users;
  /*修改用户的默认表空间*/
  alter user 用户名 default tablespace 新表空间; 
  /*查询所有的表空间*/
  select * from user_tablespaces; 
 -- 查看所有表空间详细信息
 SELECT * FROM dba_data_files;
  
  /* 删除表空间*/
  alter tablespace QCJ_TABLESPACE offline;
  drop tablespace QCJ_TABLESPACE including contents and datafiles;


2.查看各表空间分配情况。

select tablespace_name, sum(bytes) / 1024 / 1024  from dba_data_files  

 group by tablespace_name;  

3.查看各表空间空闲情况。

select tablespace_name, sum(bytes) / 1024 / 1024  from dba_free_space  group by tablespace_name;  

4.更改数据表大小(10G)

alter database datafile '/ora/oradata/radius/undo.dbf' resize 10240m；

5.设置表空间不足时自动增长

5.1查看表空间是否自动增长

SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;

5.2 设置表空间自动增长

ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON;//打开自动增长

ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON NEXT 200M ;//每次自动增长200m

ALTER DATABASE DATAFILE 'c:\SmartDB01.ora' AUTOEXTEND ON NEXT 200M MAXSIZE 1024M;//每次自动增长200m，数据表最大不超过1G