小七驿站-优快云博客

原创 MySQL数据库中根据列中逗号进行转行

select distinct a.id,substring_index(substring_index(a.value,',',b.help_topic_id+1),',',-1) from table a join mysql.help_topic b on b.help_topic_id < (length(a.expert_major) - length(replace(a.value,',',''))+1)where expert_major <> '';

2020-12-21 14:29:54 370

原创 postgresql数据库查询库大小、索引大小、表大小

--查看所有数据库大小select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database;--查看指定表中所有索引大小select pg_size_pretty(pg_indexes_size('test'));--查看指定schema 里所有的索引大小，按从大到小的顺序排列。select indexrelname, pg_size_pretty(p

2020-12-17 14:36:25 1000

原创 cron服务

cron是一个linux下的定时执行工具，可以在无需人工干预的情况下运行作业。service crond start //启动服务　　service crond stop //关闭服务　　service crond restart //重启服务　　service crond reload //重新载入配置　　service crond status //查看服务状态...

2020-12-17 14:21:07 266

原创 Oracle中取前六个月的日期sql

select sysdate,add_months(sysdate,-12),to_char(trunc(add_months(sysdate,-12)),'yyyy-mm-dd')||' 00:00:00' from dual;

2020-12-10 16:13:35 2665

原创 Oracle中捕捉运行很久的sql

select username, sid, opname, round(sofar * 100 / totalwork, 0) || '%'as progress, time_remaining, sql_textfrom v$session_longops, v$sqlwhere time_remaining <>0and sql_address = addressand sql_hash_value = hash_

2020-12-10 16:12:01 221

原创 Oracle中查询表空间使用情况

select a.tablespace_name "表空间名称",100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100, 2) "占用率(%)", round(a.bytes_alloc / 1024 / 1024, 2) "容量(M)", round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) "空闲(M)", round((a.bytes_alloc - nvl(b.

2020-12-10 16:11:22 130

原创 Oracle查询出一个表中最后一位不是数字的所有值

select a from(select substr(trim(字段名称),-1) as a from 表名称)a where a.a <'0' or a.a >'9'for update

2020-12-10 16:10:27 955

原创 Oracle中模糊查询出所有带下划线的数据

SELECT t.字段名 FROM 表名 t where t.字段名 like'%/_%' escape '/'

2020-12-10 16:09:01 1931

原创 Oracle恢复以及禁用索引

--禁用索引--ALTER INDEX INDEXNAME UNUSABLE;--恢复索引--ALTER INDEX INDEXNAME REBUILD;

2020-12-10 16:06:41 443

原创 Oracle查询表死锁语句

select object_name,s.SID,s.SERIAL#,p.SPID from V$LOCKED_OBJECT l,dba_objects o,v$session s,v$process pwhere l.OBJECT_ID=o.OBJECT_IDand l.SESSION_ID=s.SIDand s.PADDR=p.ADDR

2020-12-10 16:03:14 224

baidu_41109740的博客