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

ゝ一世哀愁。 2022-02-20 09:15 1266阅读 0赞

业务需要创建多个数据库来分别存放不同的数据，再次整理一下数据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