Oracle数据库操作（plsql）：死锁处理、表空间增加、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替换数据空间、用户