一线运维 DBA 五年经验常用 SQL 大全（一）.txt
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墨值下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
文档被以下合辑收录
评论