前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >一线运维 DBA 五年经验常用 SQL 大全(一)

一线运维 DBA 五年经验常用 SQL 大全(一)

作者头像
JiekeXu之路
发布2021-02-23 15:13:19
1.2K0
发布2021-02-23 15:13:19
举报
文章被收录于专栏:JiekeXu之路

大家好,我是 JiekeXu,分开这么久,很高兴又和大家见面了,欢迎点击上方蓝字关注我,标星或置顶,更多干货第一时间到达!

本文 SQL 均是在运维工作中总结整理而成的,对于运维 DBA 来说可提高很大工作效率,当然如果你全部能够背下来那就牛逼了,如果不能,建议收藏下来慢慢看,每条 SQL 的使用频率都很高,肯定能够帮助到你。

当然,由于本编辑器原因以下 SQL 可能出现格式错误不能执行,导致出错,这里将其保存至文本文件中方便复制粘贴执行,如有需要的小伙伴们在本公众号【JiekeXu之路】扫描最下方二维码后台回复 【SQL大全】 即可获取,这里也有 2020 年的精华文章汇总,如有需要可点击此处查看【精华文章】

1.查看表空间使用率

代码语言:javascript
复制
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查询单个表空间使用率

代码语言:javascript
复制
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.查看临时表空间数据文件位置,大小,及是否自动扩展

代码语言:javascript
复制
select tablespace_name,file_name,bytes/1024/1024 mb ,autoextensible 
from dba_data_files 
where tablespace_name in ('') order by tablespace_name;
代码语言:javascript
复制
select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible 
from dba_temp_files;
代码语言:javascript
复制
--查看所有临时表空间大小
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_HEADERGROUP BY TABLESPACE_NAME) F 
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

3.查看ASM磁盘空间。

代码语言:javascript
复制
select name,state,type,free_mb,total_mb,usable_file_mb from 
v$asm_diskgroup;

4.查询oracle的连接数

代码语言:javascript
复制
select count(*) from v$session;

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

代码语言:javascript
复制
select username,count(username) from v$session 
where username is not null group by username;

6.查看回收站

代码语言:javascript
复制
show recyclebin

7.清空回收站

代码语言:javascript
复制
PURGE recyclebin

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

代码语言:javascript
复制
 drop table tableName purge;

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

代码语言:javascript
复制
select
'select dbms_metadata.get_ddl('||''''||'TABLE'||''''||','||''''||table_name||''''||') from dual;'||chr(10)||'select '||''''||'/'||''''|| ' from dual;' 
from user_tables;

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

代码语言:javascript
复制
select dbms_metadata.get_ddl('USER','USERNAME') from dual;

11.查询普通用户语句

代码语言:javascript
复制
select username from dba_users where account_status='OPEN';

12.修改数据文件大小

代码语言:javascript
复制
alter database datafile '&path_name' resize 10G;
alter database datafile &{file_id} resize 10G;

13.添加数据文件

代码语言:javascript
复制
alter tablespace &tablespace_name ADD datafile '&datafile_name' SIZE xxx;

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

代码语言:javascript
复制
ALTER TABLESPACE &tablespace_name ADD TEMPFILE '&datafile_name' SIZE xxx;

15.大文件表空间扩容

代码语言:javascript
复制
ALTER TABLESPACE &tablespace_name RESIZE xxx;

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

代码语言:javascript
复制
show parameter dump
select * from v$diag_info;

17.创建用户

代码语言:javascript
复制
create user username identified  by password default tablespace dbdbs;

18.创建组

代码语言:javascript
复制
groupadd  -g 1000 oinstall

19.赋权

代码语言:javascript
复制
grant dba to user

20.查出锁的会话

代码语言:javascript
复制
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.查询数据库中所有用户下占用物理空间内存大小

代码语言:javascript
复制
select owner,sum(bytes)/1024/1024 MB 
from dba_segments group by owner;

22.日志切换

代码语言:javascript
复制
alter system switch logfile;

23.查看归档是否开启

代码语言:javascript
复制
archive log list;
select log_mode from v$database;

24.开启归档

代码语言:javascript
复制
alter system set log_archive_dest_1='LOCATION=+ARCH' scope=both sid='*';
shu immediate
startup mount 
alter database archivelog
alter database open

25.监听注册

代码语言:javascript
复制
sho parameter local_listener
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.数据库注册监听

代码语言:javascript
复制
alter system register

27.创建DBLINK

代码语言:javascript
复制
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)) 
) 
(CONNECT_DATA = 
(SERVICE_NAME = JiekeXuDB) 
) 
)';

28.删除DBLINK

代码语言:javascript
复制
drop public database link &dblinkname;

29.查询DBLINK

代码语言:javascript
复制
select * from dba_db_links

30.查询本地用户登陆进程

代码语言:javascript
复制
ps -ef | grep LOCAL=NO 
ps -ef | grep LOCAL=NO | grep -v grep |awk '{print $2}'|wc -l

31.批量杀进程执行 ,注意检查oracle_sid。

代码语言:javascript
复制
ps -ef|grep $ORACLE_SID|grep -v ora_|grep LOCAL=NO|awk '{print $2}'|xargs kill -9

32.查询系统检查点SCN的命令

代码语言:javascript
复制
select CHECKPOINT_CHANGE# from v$database;

33.查询控制文件中数据文件的SCN命令

代码语言:javascript
复制
select name,file#,checkpoint_change# from v$datafile;

34.数据文件头部的SCN号

代码语言:javascript
复制
select name,checkpoint_change# from v$datafile_header;

35. 控制文件中的数据文件终止scn

代码语言:javascript
复制
select name,last_change# from v$datafile;

36.解锁用户命令

代码语言:javascript
复制
alter user 用户名 account unlock; 
----这个只会解锁账号,而不会取消密码过期。

37.设置密码无期限

代码语言:javascript
复制
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; 
--将概要文件(如default)的密码有效期由默认的180天修改成“无限制”(修改之后不需要重启动数据库,会立即生效)

38.查询字符集命令

代码语言:javascript
复制
select userenv ('language') from dual;

39.查询数据库里某个表空间里所占用的大小:

代码语言:javascript
复制
select OWNER,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 as MB 
from dba_segments where TABLESPACE_NAME='01' order by 4 desc;

40关于DG认清主备库。

代码语言:javascript
复制
select d.NAME,d.LOG_MODE,d.OPEN_MODE,d.PROTECTION_MODE,d.DATABASE_ROLE from v$database d;
名字,日志模式,开启模式,保护模式,数据库角色。其中数据库角色就是看主备库的.
PRIMARY为主库 PHYSICAL STANDBY物理备库,LOGICAL STANDBY逻辑备库.
在备库中查询:
show parameter fal_server
得出的值在与tns中找相匹配的server_name然后在寻找对应的IP地址。
开启恢复
SQL> recover managed standby database disconnect from session using current logfile;

ADG相关命令:
--查看数据库状态
select name,db_unique_name,database_role,protection_mode,open_mode from v$database; 
--查看相关进程
select process,status,sequence# from v$managed_standby;
--关闭mrp功能
recover managed standby database cancel; 
--开启当前日志应用
recover managed standby database disconnect from session using current logfile;
---开启不同步当前日志应用
alter database recover managed standby database disconnect from session;
--查看归档路径状态
select dest_id,error,status,log_sequence,applied_scn from v$archive_dest;
---查询传输至备库的日志是否应用。
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

41.添加ASM磁盘所需命令

代码语言:javascript
复制
在AIX系统中查询新加入的盘符 lspv
在AIX系统中查询盘空间大小:getconf DISK_SIZE /dev/hdisk1
在数据库中查询ASM的中是否使用了此硬盘:
col PATH for a44
select GROUP_NUMBER,DISK_NUMBER,TOTAL_MB/1024,FREE_MB/1024,NAME,FAILGROUP,PATH,FAILGROUP_TYPE 
from v$asm_disk;

42. 使用轻松连接模式远程登录数据库

代码语言:javascript
复制
sqlplus sjglt/sjglt@1X.1XX.1X6.16:1521/orcl

43. 更改备份保存时间

代码语言:javascript
复制
alter system set control_file_record_keep_time=39;

44.查看asm磁盘使用哪些盘命令:

代码语言:javascript
复制
col name for a20
col path for a20
col FAILGROUP for a20
select GROUP_NUMBER,DISK_NUMBER,TOTAL_MB/1024,FREE_MB/1024,NAME,FAILGROUP,PATH,FAILGROUP 
from v$asm_disk;

45.查询oracle连接最大值命令:

代码语言:javascript
复制
select resource_name,MAX_UTILIZATION,LIMIT_VALUE from 
v$resource_limit where resource_name in ('processes','sessions');

--MAX_UTILIZATION代表数据库自上次启动以来达到的最大值,
--LIMIT_VALUE代表设置的最大值。

46.查询锁SQL:

代码语言:javascript
复制
select 'blocker('||lb.sid||':'||sb.username||')-sql:'|| qb.sql_text blockers,
'waiter ('||lw.sid||':'||sw.username||')-sql:'|| qw.sql_text 
waiters
from v$lock lb,
v$lock lw,
v$session sb,
v$session sw,
v$sql qb,
v$sql qw
where lb.sid=sb.sid
and lw.sid=sw.sid
and sb.prev_sql_addr=qb.address
and sw.sql_address=qw.address
and lb.id1=lw.id1
and sw.lockwait is not null
and sb.lockwait is null
and lb.block=1 ;

47.查询数据库数据量

代码语言:javascript
复制
select sum(bytes)/1024/1024/1024 total_gb from dba_segments

48.查看 SQL 内容

代码语言:javascript
复制
select sql_fulltext from v$sqlarea where sql_id='&sql_id'

49.查看 SQL 的执行计划

代码语言:javascript
复制
select * from v$sql_plan where sql_id='&sql_id';

50.查看绑定变量

代码语言:javascript
复制
select * from v$sql_bind_capture sbc where sql_id='&sql_id';

51.关于rman归档问题

代码语言:javascript
复制
rman> crosscheck archivelog all;
rman> delete noprompt expired archivelog all;
rman> delete force noprompt archivelog until time 'sysdate - 30';
单节点:
delete noprompt archivelog until sequence ${Seq};

rac(thread后面加节点数):
delete noprompt archivelog until sequence 1 thread ${CurThread};

删除7天前的归档:
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

按时间删除归档:
delete force archivelog all completed before 'SYSDATA-1/24';

查看归档每天切换频率以及大小
select max (first_time) max_first_time,
to_char (first_time, 'yyyy-mm-dd') day,
count (recid) count_number,
count (recid) * 200 size_mb
from v$log_history
group by to_char (first_time, 'yyyy-mm-dd')
order by 1;

按天计算日志生成量
set pages 9999
select to_char(first_time,'yyyymmdd'),count(*) 
from v$log_history 
group by to_char(first_time,'yyyymmdd') 
order by 1;

52.查看数据库引起锁表的SQL语句:

代码语言:javascript
复制
SELECT A.USERNAME,
A.MACHINE,
A.PROGRAM,
A.SID,
A.SERIAL#,
A.STATUS,
C.PIECE,
C.SQL_TEXT
FROM V$SESSION A,
V$SQLTEXT C
WHERE     A.SID IN (SELECT DISTINCT T2.SID
FROM V$LOCKED_OBJECT T1,
V$SESSION T2
WHERE T1.SESSION_ID = T2.SID)
AND A.SQL_ADDRESS = C.ADDRESS(+)
ORDER BY C.PIECE;

53.查询DB负载情况

代码语言:javascript
复制
SELECT *
  FROM ( SELECT A.INSTANCE_NUMBER,
               A.SNAP_ID,
               B.BEGIN_INTERVAL_TIME + 0 BEGIN_TIME,
               B.END_INTERVAL_TIME + 0 END_TIME,
               ROUND(VALUE - LAG( VALUE, 1 , '0')
                     OVER(ORDER BY A.INSTANCE_NUMBER, A.SNAP_ID)) "DB TIME"
          FROM (SELECT B.SNAP_ID,
                       INSTANCE_NUMBER,
                       SUM(VALUE ) / 1000000 / 60 VALUE
                  FROM DBA_HIST_SYS_TIME_MODEL B
                 WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
                   AND UPPER (B.STAT_NAME) IN UPPER(('DB TIME' ))
                 GROUP BY B.SNAP_ID, INSTANCE_NUMBER) A,
               DBA_HIST_SNAPSHOT B
         WHERE A.SNAP_ID = B.SNAP_ID
           AND B.DBID = (SELECT DBID FROM V$DATABASE)
           AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER)
 WHERE TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD') = TO_CHAR(SYSDATE , 'YYYY-MM-DD')
 ORDER BY BEGIN_TIME;

54.杀应用连接

代码语言:javascript
复制
 ps -ef |grep LOCAL=NO |awk '{print $2}'|xargs kill -9

55.查询某个时间点的scn

代码语言:javascript
复制
select timestamp_to_scn(to_timestamp('2021-01-06 10:00:00','yyyy-mm-dd hh24:mi:ss')) 
from dual;

56.闪回到某个scn点:(闪回前请先进行备份)

代码语言:javascript
复制
alter table tb_flash1 enable row movement
flashback table tb_flash1 to 115398523;

57.查询数据库最近一次重启时间

代码语言:javascript
复制
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select startup_time from v$instance;

58.查看日志每小时文件切换频率

代码语言:javascript
复制
column h0 format 999
column h1 format 999
column h2 format 999
column h3 format 999
column h4 format 999
column h5 format 999
column h6 format 999
column h7 format 999
column h8 format 999
column h9 format 999
column h10 format 999
column h11 format 999
column h12 format 999
column h13 format 999
column h14 format 999
column h15 format 999
column h16 format 999
column h17 format 999
column h18 format 999
column h19 format 999
column h20 format 999
column h21 format 999
column h22 format 999
column h23 format 999
column avg format 999.99
column day format a6
SELECT TRUNC (first_time) "Date", TO_CHAR (first_time, 'Dy') "Day", COUNT (1) "Total",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) h0,
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23", to_char(ROUND (COUNT (1) / 24, 2),'fm99999999990.00') "Avg"
FROM gv$log_history
WHERE first_time >= trunc(SYSDATE) - 30
and thread# = inst_id
GROUP BY TRUNC (first_time), TO_CHAR (first_time, 'Dy')
ORDER BY 1 DESC;

59.更改会话时间显示格式

代码语言:javascript
复制
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

60.查阻塞

代码语言:javascript
复制
col username for a15
col program for a30
col event for a30
select sid,serial#,username,blocking_session,last_call_et,prev_sql_id,final_blocking_session,event,machine,program,WAIT_TIME 
from gv$session where blocking_session is not null;

61.查看表的统计信息

代码语言:javascript
复制
select OWNER,TABLE_NAME,TABLESPACE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN,SAMPLE_SIZE,ROW_MOVEMENT,PARTITIONED,GLOBAL_STATS,
USER_STATS,to_char(LAST_ANALYZED,'YYYY-MM-DD') 
from dba_tables where  table_name='&1';

62.查看执行计划

代码语言:javascript
复制
select * from table(dbms_xplan.display_cursor('&sql_id',null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));

63.查看 SQL 执行计划是否发生改变

代码语言:javascript
复制
select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') TIMESTAMP 
from dba_hist_sql_plan 
where SQL_ID='&1' 
order by TIMESTAMP;

64.SYS 临时切换到普通用户

代码语言:javascript
复制
alter session set current_schema=Scott;

65.根据PID查登陆用户及时间

代码语言:javascript
复制
select s.SID,s.SERIAL#,s.LOGON_TIME,s.OSUSER,s.USERNAME,s.STATUS,s.PROGRAM from v$session s where s.SID in(
select sid from v$session where paddr 
in (select addr from v$process where spid in (  
30342,
30076,
28599,
13151 )
 ));

66.杀会话

代码语言:javascript
复制
select 'alter system kill session '''||sid||','||serial#||'''; 'from v$session s  
where s.STATUS='INACTIVE'  and LOGON_TIME<=(sysdate-1);
代码语言:javascript
复制
 select 'kill -9 '||spid from v$process where addr in (select paddr from v$session where sid in (
 select sid from v$session s  
 where s.STATUS='INACTIVE'  and LOGON_TIME<=(sysdate-1/24)
 ));
代码语言:javascript
复制
select 'alter system kill session '''||sid||','||serial#||'''; 'from v$session 
where SID in (select BLOCKER_SID from v$wait_chains where  IN_WAIT_SECS>100);

======================================================

第二部分

======================================================

本文 SQL 命令由于编辑器原因可能出现格式错误不能执行,这里将其保存至文本文件中方便复制粘贴执行,如有需要的小伙伴们在本公众号【JiekeXu之路】扫描最下方二维码 后台回复 【SQL大全】 即可获取。

1, 清除 column 格式

代码语言:javascript
复制
clear  columns

2, 创建永久性表空间

代码语言:javascript
复制
create tablespace myspace 
datafile '文件路径'
size 文件大小
autoextend on next 自动增加大小
maxsize 文件最大值
代码语言:javascript
复制
语法说明:
1>,temporary|undo(创建撤销表空间)
2>,teblespace_name 
3>,datafile|tempfile'file_name'
4>,size
5>,reuse 若存在,则删掉并重新创建
6>,autoextend off|on
7>, next number 自动扩展的大小
8>,maxsize unlimited|number 指定数据文件最大大小
9>,mininum extent number 盘区可以分配到的最小尺寸
10>,blocksize number 设置数据块大小
11>,online|offline 
12>,logging|nologging
13>,force logging 强制表空间数据库对象任何操作都产生日志,否定 12
14,default storage storage 指定保存在表空间中的数据库对象默认存储参数
15>,compress|nocompress 是否压缩数据(消除列中的重复值)
16>,permanent|temporary 指定表空间中数据的保存形式
17>,extent management dictionary(数据字典形式管理)|local(本地化形式管理)
18>,autoallocate|uniform size number 右边为指定表中盘区大小 
19>,segment space management auto |manual 指定表空间中段的管理方式

3,查看表空间属性

代码语言:javascript
复制
SELECT * FROM  dba_tablespace wheretablespace_name = '表空间名';

4, 修改表空间状态、名字

代码语言:javascript
复制
alter tablespace 表空间名 表空间状态;
alter tablespace 表空间名 1 rename to 表空间名 2;

5,查看闪回区 、快速恢复区空间使用率

代码语言:javascript
复制
select sum(percent_space_used)||'%' "已使用空间比例" 
from V$RECOVERY_AREA_USAGE;
或
select round(100*(a.space_used/space_limit),2)||'%' "已使用空间比例",a.* 
from v$recovery_file_dest a;

6,利用数据字典查看空闲空间信息

代码语言:javascript
复制
SELECT * FROM dba_free_space
 WHERE tablespace_name = '表空间名称';

7, 利用数据字典查看表空间数据文件信息

代码语言:javascript
复制
SELECT * FROM dba_data_files wheretablespace_name = '表空间名称';

8, 修改表空间对应的数据文件大小

代码语言:javascript
复制
alter database
datafile '表空间文件路径名称'
resize 大小

9、根据 sid 查 spid 或根据 spid 查 sid

代码语言:javascript
复制
select s.sid,s.serial#,p.spid,s.terminal,s.LOGON_TIME,s.status,s.PROGRAM,s.CLIENT_IDENTIFIER,s.machine,s.action,s.MODULE,s.PROCESS "客户端机器进程号",s.osuser from v$session s,v$process p 
where  s.paddr=p.addr and s.sid=XX or p.spid=YY;

10、查询堵塞别的会话超过30分钟且自身是不活动的会话

代码语言:javascript
复制
select username,sid,serial#,status,seconds_in_wait,LAST_CALL_ET from v$session where sid in (select FINAL_BLOCKING_SESSION from v$session 
where state='WAITING' and BLOCKING_SESSION_STATUS='VALID' and FINAL_BLOCKING_SESSION_STATUS='VALID') and status='INACTIVE' 
and sql_id is null and seconds_in_wait>1800;

11、查询正在执行的 SCHEDULER_JOB

代码语言:javascript
复制
select owner,job_name,sid,b.SERIAL#,b.username,spid from ALL_SCHEDULER_RUNNING_JOBS,v$session b,v$process  
where session_id=sid and paddr=addr;

12、查询正在执行的 dbms_job

代码语言:javascript
复制
select job,b.sid,b.SERIAL#,b.username,spid from DBA_JOBS_RUNNING a ,v$session b,v$process  
where a.sid=b.sid and paddr=addr;

13、查询当前正在消耗临时空间的 SQL 语句

代码语言:javascript
复制
Select distinct se.username,se.sid,
su.blocks * to_number(rtrim(p.value))/1024/1024 as space_G,
su.tablespace,sql_text
from V$TEMPSEG_USAGE su, v$parameter p, v$session se, v$sql s
where p.name = 'db_block_size' and su.session_addr=se.saddr
     and su.sqlhash=s.hash_value and su.sqladdr=s.address and se.STATUS='ACTIVE';

14、查看临时表空间使用率

代码语言:javascript
复制
SELECT temp_used.tablespace_name,round(total),used,
           round(total - used) as "Free",
           round(nvl(total-used, 0) * 100/total,1) "Free percent"
      FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used
              FROM GV$TEMP_SPACE_HEADER
             GROUP BY tablespace_name) temp_used,
           (SELECT tablespace_name, SUM(decode(autoextensible,'YES',MAXBYTES,bytes))/1024/1024 total
              FROM dba_temp_files
             GROUP BY tablespace_name) temp_total
     WHERE temp_used.tablespace_name = temp_total.tablespace_name
或
SELECT a.tablespace_name, round(a.BYTES/1024/1024) total_M, round(a.bytes/1024/1024 - nvl(b.bytes/1024/1024, 0)) free_M,
round(b.bytes/1024/1024) used,round(b.using/1024/1024) using
  FROM (SELECT   tablespace_name, SUM (decode(autoextensible,'YES',MAXBYTES,bytes)) bytes FROM dba_temp_files GROUP BY tablespace_name) a,
       (SELECT   tablespace_name, SUM (bytes_cached) bytes,sum(bytes_used) using FROM v$temp_extent_pool GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+);

15、查看当前会话的 SID

代码语言:javascript
复制
select * from V$MYSTAT where rownum<2;

16、统计每个用户使用表空间率

代码语言:javascript
复制
SELECT c.owner                                  "用户",
       a.tablespace_name                        "表空间名",
       total/1024/1024                          "表空间大小M",
       free/1024/1024                           "表空间剩余大小M",
       ( total - free )/1024/1024               "表空间使用大小M",
       Round(( total - free ) / total, 4) * 100 "表空间总计使用率   %",
       c.schemas_use/1024/1024                  "用户使用表空间大小M",
       round((schemas_use)/total,4)*100         "用户使用表空间率  %"      
FROM   (SELECT tablespace_name,
               Sum(bytes) free
        FROM   DBA_FREE_SPACE
        GROUP  BY tablespace_name) a,
       (SELECT tablespace_name,
               Sum(bytes) total
        FROM   DBA_DATA_FILES
        GROUP  BY tablespace_name) b,
       (Select owner ,Tablespace_Name,
                Sum(bytes) schemas_use 
        From Dba_Segments 
        Group By owner,Tablespace_Name) c
WHERE  a.tablespace_name = b.tablespace_name
and a.tablespace_name =c.Tablespace_Name
order by "用户","表空间名";

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2021-01-26,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 JiekeXu之路 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档