一、记录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 文件路径 查看文件大小文章来源:https://www.toymoban.com/news/detail-472724.html
三、shell连接Oracle数据库执行sql或存储过程文章来源地址https://www.toymoban.com/news/detail-472724.html
#!/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"
到了这里,关于Oracle和Linux运维笔记的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!