Oracle数据库操作：死锁处理、表空间增加、dmp导入导出（window、linux）

前言

Oracle数据库操作死锁处理、表空间增加、dmp导入导出window、linux
场景创建一个新项目数据库基于现有的库进行新建
博客地址芒果橙的个人博客 【http://mangocheng.com】

一、ORA-02049解决分布式事务问题

1. 查询进程信息所有锁

-- 查询进程信息
SELECT S.USERNAME,
       DECODE(L.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
       O.OWNER,
       O.OBJECT_NAME,
       O.OBJECT_TYPE,
       S.SID,
       S.SERIAL#,
       S.TERMINAL,
       S.MACHINE,
       S.PROGRAM,
       S.OSUSER
  FROM V$SESSION S, V$LOCK L, DBA_OBJECTS O
 WHERE L.SID = S.SID
   AND L.ID1 = O.OBJECT_ID(+)
   AND S.USERNAME IS NOT NULL;

2. 查询具体锁表的进程

   -- 锁表进程
   SELECT s.sid, s.serial#
     FROM v$locked_object lo, dba_objects ao, v$session s
    WHERE ao.object_id = lo.object_id
      AND lo.session_id = s.sid;
   
   -- 更详细的查询一般用上面的即可
   -- 以下的语句可以查询到谁锁了表而谁在等待。 如果有子节点则表示有等待发生
   select /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username user_name,
          o.owner,
          o.object_name,
          o.object_type,
          s.sid,
          s.serial#
   from v$locked_object l,dba_objects o,v$session s
   where l.object_id=o.object_id
   and l.session_id=s.sid
   order by o.object_id,xidusn desc;
   

3. 将死锁进程杀掉ALTER SYSTEM KILL SESSION ‘sid,serial’;

   -- 强制关闭进程
   ALTER SYSTEM KILL SESSION '715,58391';
   

二、表空间操作查询、新增、调整大小、删除

1. 查询表空间使用情况

-- 查询表空间使用情况
SELECT TABLESPACE_NAME "表空间",
       To_char(Round(BYTES / 1024, 2), '99990.00')
       || ''           "实有",
       To_char(Round(FREE / 1024, 2), '99990.00')
       || 'G'          "现有",
       To_char(Round(( BYTES - FREE ) / 1024, 2), '99990.00')
       || 'G'          "使用",
       To_char(Round(10000 * USED / BYTES) / 100, '99990.00')
       || '%'          "比例"
FROM   (SELECT A.TABLESPACE_NAME                             TABLESPACE_NAME,
               Floor(A.BYTES / ( 1024 * 1024 ))              BYTES,
               Floor(B.FREE / ( 1024 * 1024 ))               FREE,
               Floor(( A.BYTES - B.FREE ) / ( 1024 * 1024 )) USED
        FROM   (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                       Sum(BYTES)      BYTES
                FROM   DBA_DATA_FILES
                GROUP  BY TABLESPACE_NAME) A,
               (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                       Sum(BYTES)      FREE
                FROM   DBA_FREE_SPACE
                GROUP  BY TABLESPACE_NAME) B
        WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME)
--WHERE TABLESPACE_NAME LIKE 'CDR%' --这一句用于指定表空间名称
ORDER  BY Floor(10000 * USED / BYTES) DESC;

2. 数据文件路径

-- 数据文件路径
select tablespace_name, file_id,file_name, 
round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name;

3. 增加表空间大小

-- 增加表空间大小通过增加数据文件不自动增长
alter tablespace system add datafile '/data/oracle/oradata/orcl/system02.dbf' size 6g autoextend off;

4. 调整表空间大小

-- 调整表空间大小通过调整表数据文件
ALTER DATABASE datafile  '/data/oracle/oradata/orcl/system01.dbf' RESIZE 8G;

5. 删除表空间中的数据文件

-- 删除表空间中的数据文件减少表空间大小
alter tablespace 表空间 drop datafile 'testfile2.dbf';

6. 查询表空间的相关信息

--1、查询所有表空间及对应的路径
select tablespace_name,file_name from dba_data_files;
--2、查询所有表空间的状态信息
select tablespace_name,status from dba_tablespaces;
--3、查询指定用户的数据表空间
select default_tablespace from dba_users where username='SYS';

三、导入dmp-linux/window环境

1. linux环境

• 导入参数可参考expdp / impdp 用法详解

1. 拷贝文件到相关目录
   1. 连接Oracle环境sqlplus / as sysdba
   2. 查询相关目录select * from dba_directories;
2. 执行语句

-- 导入到不存在的用户最后一个参数前面为源库用户名后面为新用户名
impdp username/password directory=backup dumpfile=USR_2023_2_1.dmp REMAP_SCHEMA=USR_DEV:USR_DEV_20230201

3. 若是导入到新用户需修改用户密码

-- 通过管理员进行修改
1.su oracle
2.sqlplus /nolog;
3.connect/as sysdba;
6.alter user 用户名 identified by 新密码
4.ALTER USER 用户名 ACCOUNT UNLOCK;
5.commit;

2. window环境

# cmd控制台操作
1. exp导出imp改成exp
IMP USR_ZFJOA/USR_ZFJOA@192.168.0.196:1521/orcl file=C:\Users\Administrator\Desktop\ZFJ\2021_02_01\USR_ZFJOA_20210131_2300000.DMP full=y;

2. expdp导出:impdp改成expdp
# directory在E:\app\Administrator\dump\backup\oracle

impdp USR_ZFJOA/USR_ZFJOA@192.168.0.196:1521/ORCL dumpfile=USR_ZFJOA_20210131_2300000.DMP directory=BACKUP remap_schema=USR_ZFJOA:USR_ZFJOA  table_exists_action=replace

3. 导入新库且创建新用户window、linux都验证过

• 注意点linux上文件名是区分大小写的

• 更多信息可参考导入数据库 、window导入dmp

• 实例根据已有的基础库拷贝创建一个新数据库不同名不同表空间不同用户

  -- 新库名ZZ_EXPERT_TEST
  -- 1.切换到oracle用户下 linux环境window直接从第2步开始)      
  su - oracle
  -- 2.登录sqlplus控制台、终端
  sqlplus /nolog
  -- 3.使用sysdba登录
  conn /as sysdba
  -- 4.查询表空间存储位置
  select name from v$tempfile;
  -- 5、创建临时表空间zz_expert_test_temp   
  create temporary tablespace zz_expert_test_temp tempfile 'E:\APP\ORACLE\ORADATA\ORCL\ZZ_EXPERT_TEST_TEMP_01.dbf' size 1G reuse autoextend on next 20m maxsize unlimited;
  -- 6、创建数据表空间zz_expert_test_data
  create tablespace zz_expert_test_data datafile 'E:\APP\ORACLE\ORADATA\ORCL\ZZ_EXPERT_TEST_DATA_01.dbf' size 1G reuse autoextend on next 40M maxsize unlimited;
  -- 7、创建索引表空间zz_expert_test_idx
  create tablespace zz_expert_test_idx logging datafile 'E:\APP\ORACLE\ORADATA\ORCL\ZZ_EXPERT_TEST_IDX_01.dbf' size 100m autoextend on next 32m maxsize 2048m extent management local;
  -- 8、创建用户并分配表空间ZZ_EXPERT_TEST
  create user ZZ_EXPERT_TEST identified by ZZ_EXPERT_TEST default tablespace zz_expert_test_data temporary tablespace zz_expert_test_temp;
  -- 9、赋权dba给用户
  grant resource,connect,dba to ZZ_EXPERT_TEST;
  -- 10、创建文件目录
  create directory DATA_DIR as 'E:\app\Administrator\dump\data_dir';
  -- 11、给用户赋文件目录的读写权限
  grant read,write on directory DATA_DIR to ZZ_EXPERT_TEST;
  -- 12、导入dmp文件
  -- 方式一同名同库同空间的没试过
  -- impdp aml/aml@orcl directory=DATA_DIR dumpfile=aml_v2.dmp
  -- 方式二不同名不同表空间不同用户
  impdp USR_OA_PURE_TEST/ZZ_EXPERT_TEST@orcl transform=segment_attributes:n directory=DATA_DIR dumpfile=USR_OA_PURE_TEST_20220808.dmp  remap_tablespace=OA_DATA:ZZ_EXPERT_TEST_DATA remap_schema=USR_OA_PURE_TEST:ZZ_EXPERT_TEST CLUSTER=N logfile=exdp-test.log;
  -- 已经存在是否覆盖table_exists_action = replace
  -- 参数值source:target源库信息、新库信息
  -- remap_tablespace、remap_schema替换数据空间、用户