Oracle和Linux运维笔记

一、记录Oracle常用SQL语句

1.for循环插入数据，该写法需要优化，插入1亿多条数据需要7个小时左右

declare

begin
	FOR a IN (SELECT DISTINCT day_id FROM 表名b ORDER BY DAY_ID) LOOP
		INSERT INTO 表名a a SELECT * FROM 表名b b WHERE b.day_id= a.day_id;
		COMMIT;
	END LOOP;
END;

2.检查表字段是否使用Oracle关键字

select * from v$reserved_words where keyword
in(
select COLUMN_NAME
from all_tab_columns
where table_name = '表名'
);

3.某个字段数据按长度排序查询表中数据

select * from 表名 t order by nvl(length(trim(t.字段名)),0) desc

4.根据表中时间字段查询数据

SELECT* FROM 表名 as2 WHERE as2.CREATE_TIME > TO_DATE('2023-02-21', 'yyyy-mm-dd');

5.查锁表和杀锁表语句

SELECT
	sess. SID,
	sess.serial#,
	lo.oracle_username,
	lo.os_user_name,
	ao.object_name,
	lo.locked_mode
FROM
	v$locked_object lo,
	dba_objects ao,
	v$session sess
WHERE
	ao.object_id = lo.object_id
AND lo.session_id = sess. SID;

alter system kill session '1943, 34927';

select * from dba_ddl_locks where name='存储过程名';

select t.sid, t.serial#,p.PID,p.SPID
from v$session t 
join v$process p 
on t.PADDR=p.ADDR
where t.sid='2672';   

alter system kill session '2672, 63911';

6.临时表空间

select  * from v$tempfile

alter tablespace TEMP add tempfile '/data/app/oracle/oradata/silent/temp02.dbf' size 1G; 增加文件
alter database tempfile '/data/app/oracle/oradata/silent/temp02.dbf' resize 16G; 扩展大小

7.查询和删除重复数据

select 
	* 
from 
	表名a
where 
	(a.字段名a, a.字段名b) 
in  
	(select 字段名a, 字段名b from 表名a group by 字段名a, 字段名b having count(*) > 1)
ORDER BY a.字段名a, a.字段名b;



-- 记录删除数量
delete from 表名a t
 where t.rowid not in
       (select max(rowid) from 表名a t1 group by a.字段名a, a.字段名b);

8.查询加多线程操作

select /*+ full(a) full(b) full(c) use_hash(a,b) */ 

二、Linux命令记录

df -h                        查看磁盘空间
cp -R 文件名           备份文件
rm -rf 文件名           删除文件

du -sh 文件路径      查看文件大小

三、shell连接Oracle数据库执行sql或存储过程

#!/bin/bash
source ~/.bash_profile

user_name=用户名
user_pass=密码
produre_name=存储过程名
statis_sign=日期参数
sql_str=`
sqlplus $user_name/$user_pass@//ip:1521/"数据库名" <<EOF
set linesize 800;
set long 2048576;
set serveroutput on;
select * from  $user_name.表名 WHERE num=1;
var oi_return number;
var vi_return varchar2(3000);
call  $user_name.$produre_name($statis_sign,:oi_return,:vi_return);
exit
EOF`
echo "$sql_str"