前言
Oracle数据库操作:死锁处理、表空间增加、dmp导入导出(window、linux)
场景:创建一个新项目,数据库基于现有的库进行新建
博客地址:芒果橙的个人博客 【http://mangocheng.com】
文章目录
- 前言
- 一、ORA-02049:解决分布式事务问题
- 二、表空间操作:查询、新增、调整大小、删除
- 1. 查询表空间使用情况
- 2. 数据文件路径
- 3. 增加表空间大小
- 4. 调整表空间大小
- 5. 删除表空间中的数据文件
- 6. 查询表空间的相关信息
- 三、导入dmp-linux/window环境
- 1. linux环境
- 2. window环境
- 3. 导入新库,且创建新用户(window、linux都验证过)
一、ORA-02049:解决分布式事务问题
- 查询进程信息,所有锁
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;
-
查询具体锁表的进程
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 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;
-
将死锁进程杀掉: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)
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. 查询表空间的相关信息
select tablespace_name,file_name from dba_data_files;
select tablespace_name,status from dba_tablespaces;
select default_tablespace from dba_users where username='SYS';
三、导入dmp-linux/window环境
1. linux环境
- 导入参数可参考:expdp / impdp 用法详解
- 拷贝文件到相关目录
- 连接Oracle环境:sqlplus / as sysdba
- 查询相关目录:select * from dba_directories;
- 执行语句
impdp username/password directory=backup dumpfile=USR_2023_2_1.dmp REMAP_SCHEMA=USR_DEV:USR_DEV_20230201
- 若是导入到新用户,需修改用户密码
-- 通过管理员进行修改
1.su oracle;
2.sqlplus /nolog;
3.connect/as sysdba;
6.alter user 用户名 identified by 新密码;
4.ALTER USER 用户名 ACCOUNT UNLOCK;
5.commit;
2. window环境
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
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
-
实例:根据已有的基础库,拷贝创建一个新数据库(不同名,不同表空间,不同用户)
su - oracle
sqlplus /nolog
conn /as sysdba
select name from v$tempfile;
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;
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;
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;
create user ZZ_EXPERT_TEST identified by ZZ_EXPERT_TEST default tablespace zz_expert_test_data temporary tablespace zz_expert_test_temp;
grant resource,connect,dba to ZZ_EXPERT_TEST;
create directory DATA_DIR as 'E:\app\Administrator\dump\data_dir';
grant read,write on directory DATA_DIR to ZZ_EXPERT_TEST;
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;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)