一线运维 dba 五年经验常用 sql 大全（一）.txt -m6米乐安卓版下载

m6米乐安卓版下载-米乐app官网下载

一线运维 dba 五年经验常用 sql 大全（一）.txt

jiekexu

985

12页

37次

2021-03-11

5墨值下载

一线运维 dba 五年经验常用 sql 大全（一）

作者：jiekexu

原文链接：https://mp.weixin.qq.com/s/yrtqgr9nk-yx4bkth9nvxq

来源 | jiekexu dba之路（id: jiekexu_it）

转载请联系授权 | (微信id：jiekexu_dba)

本文 sql 均是在运维工作中总结整理而成的，对于运维 dba 来说可提高很大工作效率，当然如果你全部能

够背下来那就牛逼了，

如果不能，建议收藏下来慢慢看，每条 sql 的使用频率都很高，肯定能够帮助到你。

当然，由于本编辑器原因以下 sql 可能出现格式错误不能执行，导致出错，这里将其保存至文本文件中方便

复制粘贴执行，

如有小伙伴感觉不错，可关注公众号【jiekexu dba之路】一起交流学习。

1.查看表空间使用率。

set line 220

select total.tablespace_name,round(total.mb, 2) as total_mb,round(total.mb -

free.mb, 2) as used_mb,round((1 - free.mb / total.mb) * 100, 2) || '%' as

used_pct

from (select tablespace_name,sum(bytes) / 1024 / 1024 as mb from dba_free_space

group by tablespace_name) free,

(select tablespace_name,sum(bytes) / 1024 / 1024 as mb from dba_data_files group

by tablespace_name) total

where free.tablespace_name = total.tablespace_name order by used_pct desc;

1.1查询单个表空间使用率。

select total.tablespace_name,

round(total.mb, 2) as total_mb,

round(total.mb - free.mb, 2) as used_mb,

round((1 - free.mb / total.mb) * 100, 2) || '%' as used_pct from

(select tablespace_name,sum(bytes) / 1024 / 1024 as mb from dba_free_space where

tablespace_name='tbl_space' group by tablespace_name) free,

(select tablespace_name,sum(bytes) / 1024 / 1024 as mb from dba_data_files where

tablespace_name='tbl_space' group by tablespace_name) total

where free.tablespace_name = total.tablespace_name order by used_pct desc;

2.查看临时表空间数据文件位置，大小，及是否自动扩展。

select tablespace_name,file_name,bytes/1024/1024 mb ,autoextensible from

dba_data_files where tablespace_name in ('') order by tablespace_name;

select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from

dba_temp_files;

--查看所有临时表空间大小

select d.tablespace_name,space "sum_space(m)",blocks sum_blocks,

used_space "used_space(m)",round(nvl(used_space,0)/space*100,2) "used_rate(%)",

nvl(free_space,0) "free_space(m)"

from

(select tablespace_name,round(sum(bytes)/(1024*1024),2) space,sum(blocks) blocks

from dba_temp_files

group by tablespace_name) d,

(select tablespace_name,round(sum(bytes_used)/(1024*1024),2) used_space,

round(sum(bytes_free)/(1024*1024),2) free_space

from v$temp_space_header

group by tablespace_name) f

where d.tablespace_name = f.tablespace_name( );

3.查看asm磁盘空间。

select name,state,type,free_mb,total_mb,usable_file_mb from v$asm_diskgroup;

4.查询oracle的连接数

select count(*) from v$session;

5.查看不同用户的连接数

select username,count(username) from v$session where username is not null group

by username;

6.查看回收站

show recyclebin

7.清空回收站

purge recyclebin

8.删除表，不进入回收站

drop table tablename purge;

9.查询用户下所有创建表的语句

select

'select dbms_metadata.get_ddl('||''''||'table'||''''||','||''''||

table_name||''''||') from dual;'||chr(10)||'select '||''''||'/'||''''|| ' from

dual;'

from user_tables;

10.查询当时创建用户的语句

select dbms_metadata.get_ddl('user','username') from dual;

11.查询普通用户语句

select username from dba_users where account_status='open';

12.修改数据文件大小

alter database datafile '&path_name' resize 10g;

alter database datafile &{file_id} resize 10g;

13.添加数据文件

alter tablespace &tablespace_name add datafile '&datafile_name' size xxx;

14.临时表空间扩容，填加临时表空间数据文件

alter tablespace &tablespace_name add tempfile '&datafile_name' size xxx;

15.大文件表空间扩容

alter tablespace &tablespace_name resize xxx;

16.查询告警日志文件位置

show parameter dump

select * from v$diag_info;

17.创建用户

create user username identified by password default tablespace dbdbs;

18.创建组

groupadd -g 1000 oinstall

19.赋权

grant dba to user

20.查出锁的会话

select b.username,b.sid,b.serial#,logon_time from v$lock_object a,v$session b

where a.session_id = b.sid order by b.logon_time;

21.查询数据库中所有用户下占用物理空间内存大小

select owner,sum(bytes)/1024/1024 mb from dba_segments group by owner;

22.日志切换

alter system switch logfile;

23.查看归档是否开启

archive log list;

select log_mode from v$database;

24.开启归档

alter system set log_archive_dest_1='location= arch' scope=both sid='*';

shu immediate

startup mount

alter database archivelog

alter database open

25.监听注册

alter system set local_listener='(address = (protocol = tcp)(host = jiekexu)

(port = 1522))';

alter system set local_listener='(address = (protocol = tcp)(host = 10.x.x.6)

(port = 1521))' sid='jiekedbr2' scope=both;

26.数据库注册监听

alter system register;

27.创建dblink

create public database link ho

connect to skdata identified by oracle

using '(description =

(address_list =

(address = (protocol = tcp)(host = 10.x.x.6)(port = 1521))

of 12

5墨值下载

oracle

文档被以下合辑收录

jiekexu dba之路（共191篇）

jiekexu：来自西北，90 后，获得 11g ocp、11g ocm 认证，墨天轮 mvp，墨天轮 2021 十大突出贡献人物之一，此专栏记录一个菜鸟 dba 学习成长之路，欢迎关注公众号 jiekexu dba之路，一起学习进步。

关注

一线运维 dba 五年经验常用 sql 大全（一）.txt -m6米乐安卓版下载

文档被以下合辑收录

评论