笔记分享(1) oracle常用查询语句
原创
以下的oracle常用查询笔记是我之前工作中用到过的. 其实常用的查询差不多就是这些.
基本上都能在网上找到, 说不定你还能看见很多眼熟的.
分享出来, 留个纪念.
后续还会分享其它的笔记, 基本上都是linux运维和数据库的.
2年的量, 不算多也不算少
#查询所有用户
select username from dba_users;
select username from all_users;
select USERNAME,ACCOUNT_STATUS,CREATED from dba_users order by 3;
#查看时间
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;
#按用户查看当前使用空间 #查看用户使用所有空间
select owner,sum(BYTES)/1024/1024 as SIZE_Mb from dba_segments where segment_type = 'TABLE' group by owner order by 2;
#查看用户所拥有表即其使用大小
select owner,segment_name,tablespace_name,BYTES/1024/1024 size_mb from dba_segments where segment_type='TABLE' and owner = 'DDCW'
#查看用户使用表空间（用户名必须大写）
select tablespace_name, sum(bytes) / (1024 * 1024) size_m from dba_segments where owner = 'SYS' group by tablespace_name
#查询用户临时表空间
SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;
#查询表空间状态
select tablespace_name,status from dba_tablespaces;
#查询temp表空间
select tablespace_name,file_name from dba_temp_files;
#查询temp表空间使用率
select tablespace_name,round(free_space/1024/1024/1024,2) "free(GB)",round(tablespace_size/1024/1024/1024,2) "total(GB)",round(nvl(free_space,0)*100/tablespace_size,3) "Free percent"from dba_temp_free_space;
#查看表字段
desc tablename
#表空间与用户
查询当前用户所有表
select table_name from user_tables;
查询所有表空间
select TABLESPACE_NAME, BLOCK_SIZE from dba_tablespaces;
查询所有表空间及大小：
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;
查询tmp表空间
SELECT TU.TABLESPACE_NAME AS "TABLESPACE_NAME",
TT.TOTAL - TU.USED AS "FREE(G)",
TT.TOTAL AS "TOTAL(G)",
ROUND(NVL(TU.USED, 0) / TT.TOTAL * 100, 3) AS "USED(%)",
ROUND(NVL(TT.TOTAL - TU.USED, 0) * 100 / TT.TOTAL, 3) AS "FREE(%)"
FROM (SELECT TABLESPACE_NAME,
SUM(BYTES_USED) / 1024 / 1024 / 1024 USED
FROM GV_$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) TU ,
(SELECT TABLESPACE_NAME,
SUM(BYTES) / 1024 / 1024 / 1024 AS TOTAL
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) TT
WHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME;
查询表空间使用情况
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes/1024/1024 total_M,
b.bytes/1024/1024 used_M,
c.bytes/1024/1024 free_M,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
SELECT a.tablespace_name,
TRUNC (tablespace_size * b.block_size / 1024 / 1024 )
"TOTAL(MB)",
TRUNC (used_space * b.block_size / 1024 / 1024 ) "USED(MB)",
TRUNC (
(TABLESPACE_SIZE - used_space) * b.block_size / 1024 / 1024 )
"FREE(MB)",
ROUND (USED_PERCENT , 2) "USED_PERCENT(%)"
FROM DBA_TABLESPACE_USAGE_METRICS a, dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY USED_PERCENT DESC;
SELECT a.tablespace_name "表空间名称",
TRUNC(tablespace_size * b.block_size / 1024 / 1024 / 1024) "表空间大小(GB)",
TRUNC(used_space * b.block_size / 1024 / 1024 /1024) "表空间使用量(GB)",
TRUNC((TABLESPACE_SIZE - used_space) * b.block_size / 1024 / 1024 /1024) "表空间剩余量(GB)",
ROUND(USED_PERCENT,2) "使用率(%)"
FROM DBA_TABLESPACE_USAGE_METRICS a, dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name
ORDER BY USED_PERCENT desc;
查看表空间所在位置
select file_id,FILE_NAME,bytes/1024/1024 from dba_data_files;
查看表所属表空间（表的名字大写）
SELECT TABLE_NAME,TABLESPACE_NAME FROM DBA_TABLES where table_name='T_COUSE';
查看表空间大小及位置
col file_name for a60;
set linesize 160;
select file_name,tablespace_name,bytes/1024/1024 from dba_data_files;
查看表空间下的用户
select distinct s.owner from dba_segments s where s.tablespace_name ='SYSTEM';
查看用户默认表空间
select default_tablespace from dba_users where username='SYSTEM';
#查看PDB名称：
select name from v$pdbs;
#查看links
select * from dba_db_links;
select * from dba_db_links;
#查看数据库模式，可交换状态
select database_role,open_mode, switchover_status from v$database;
#查看密码期限default
SELECT RESOURCE_NAME,substrb(LIMIT,1,16) LIMIT from dba_profiles where RESOURCE_NAME='PASSWORD_LIFE_TIME' and PROFILE='DEFAULT';
#设置密码期限无期限
ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME UNLIMITED;
#查看OMF
show parameter db_create_file_dest;
#查看数据库是否启动
select instance_name,status from v$instance;
#查看所有目录
set linesize 200
col OWNER format a20
col DIRECTORY_NAME format a30
col DIRECTORY_PATH format a100
select * from all_directories;
select * from dba_directories;
#查询文件名
select file_name from dba_data_files;
#查询字符集
select userenv('language') from dual;
#查看会话session
select username,count(username) from v$session where username is not null group by username;
#当前连接数：
select count(*) from v$process;
#查询数据库锁表的语句
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object));
#找出数据库的serial#
select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;
select t2.username,t2.sid,t2.serial#,t2.logon_time,t2.sid || ',' ||t2.serial#
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;
#杀死session
alter system kill session 'sid,serial#'
#查看expdp/impdp作业
$ expdp/impdp system/systemdb attach=expdp_lufei_luolong_job
#查询用户锁状态
select username,lock_date,account_status from dba_users;
#查看隐藏参数
col name for a50;
col value for a15;
select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx
and x.ksppinm in ('_b_tree_bitmap_plans','_optimizer_aggr_groupby_elim','_optimizer_adaptive_cursor_sharing','_optimizer_extended_cursor_sharing','_optimizer_extended_cursor_sharing_rel','_optimizer_mjc_enabled','_optimizer_use_feedback','_gc_policy_time','_gc_undo_affinity','_optimizer_ads_use_result_cache','_clusterwide_global_transactions')
order by
translate(x.ksppinm, ' _', ' ')
/
#查看隐藏参数_optimizer_aggr_groupby_elim
col name for a50;
col value for a15;
select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and x.ksppinm ='_optimizer_aggr_groupby_elim'
order by
translate(x.ksppinm, ' _', ' ')
/
#查看日志组
set line 200
set pagesize 20
col member for a60
select a.thread#,
a.group#,
b.member,
a.sequence#,
a.status,
a.archived,
a.bytes / 1024 / 1024 size_mb
from v$log a ,v$logfile b where a.group# = b.group# order by 1,2;
#查看表空间是不是自动扩展
select tablespace_name,AUTOEXTENSIBLE,INCREMENT_BY from dba_data_files;
#设置自动扩展
alter database datafile '/u01/app/oracle/oradata/test.dbf' autoextend on;
#查询EM
select dbms_xdb_config.gethttpsport() from dual;
#定位存在碎片的对象
select a.owner,
a.table_name,
a.num_rows,
a.avg_row_len,
round(a.avg_row_len * a.num_rows / 1024 / 1024, 2) real_bytes_MB,
round(b.seg_bytes_mb, 2) seg_bytes_mb,
decode(a.num_rows,
0,
100,
(1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 /
b.seg_bytes_mb,
2)) * 100) || '%' frag_percent
from dba_tables a,
(select owner, segment_name, sum(bytes / 1024 / 1024) seg_bytes_mb
from dba_segments
group by owner, segment_name) b
where a.table_name = b.segment_name
and a.owner = b.owner
and a.owner not in
('SYS', 'SYSTEM', 'OUTLN', 'DMSYS', 'TSMSYS', 'DBSNMP', 'WMSYS',
'EXFSYS', 'CTXSYS', 'XDB', 'OLAPSYS', 'ORDSYS', 'MDSYS', 'SYSMAN')
and decode(a.num_rows,
0,
100,
(1 - round(a.avg_row_len * a.num_rows / 1024 / 1024 /
b.seg_bytes_mb,
2)) * 100) > 30
order by b.seg_bytes_mb desc;
#查询sql执行计划(根据sql_id)
select * from table(dbms_xplan.display_cursor('5uv6crpj12y0p',NULL,'ADVANCED +PEEKED_BINDS'));
#根据sqlid查看sql
select a.* from v$sql a where a.SQL_ID='9pjy3pnf4d2vv'
#查询Oracle正在执行的sql语句及执行该语句的用户
SELECT b.sid oracleID,
b.username Oracle用户,
b.serial#,
spid 操作系统ID,
paddr,
sql_text 正在执行的SQL,
b.machine 计算机名
FROM v$process a, v$session b, v$sqlarea c
WHERE a.addr = b.paddr
AND b.sql_hash_value = c.hash_value;
#查看正在执行sql的发起者的发放程序
SELECT A.serial#,OSUSER 电脑登录身份,
PROGRAM 发起请求的程序,
USERNAME 登录系统的用户名,
SCHEMANAME,
B.Cpu_Time 花费cpu的时间,
STATUS,
B.SQL_TEXT 执行的sql
FROM V$SESSION A
LEFT JOIN V$SQL B ON A.SQL_ADDRESS = B.ADDRESS
AND A.SQL_HASH_VALUE = B.HASH_VALUE
ORDER BY b.cpu_time DESC
#查询当前正在执行的sql
SELECT a.program, b.spid, c.sql_text, c.SQL_ID
FROM v$session a, v$process b, v$sqlarea c
where a.paddr = b.addr
AND a.sql_hash_value = c.hash_value
AND a.username IS NOT NULL;
#查看总耗时前10的sql
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.elapsed_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;
#查看CPU耗时前10的sql
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.cpu_time desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;
#查看磁盘耗时前10的进程
select *
from (select v.sql_id,
v.child_number,
v.sql_text,
v.elapsed_time,
v.cpu_time,
v.disk_reads,
rank() over(order by v.disk_reads desc) elapsed_rank
from v$sql v) a
where elapsed_rank <= 10;
#db_links
--创建
create public database link to_oggd_ddcwd
connect to ddcwd identified by ygd
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.101.172)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = oggdpdb)))';
--删除
drop public database link to_oggd_ddcwd
--使用
select * from student@to_oggd_ddcwd
##expdp导数据 前的查询工作（根据对应情况在目标端创建相应表空间）：
#查询导出用户的表空间使用情况（包括索引）
select owner,segment_name,segment_type,tablespace_name,BYTES/1024/1024 size_mb from dba_segments where owner = 'DDCW';
#查询用户默认表空间和TEMP表空间
SELECT USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='DDCW';
#查询用户所拥有的角色权限(connect,dba,resource等)
select * from dba_role_privs where grantee='DDCW';
#查看用户的对象(表)权限（其它用户的表权限等）
select * from dba_tab_privs where grantee='DDCW';
#查看用户所拥有的系统权限（表空间TABLESPACE）
select * from dba_sys_privs where grantee='DDCW';
#查询目录
set linesize 200
col OWNER format a20
col DIRECTORY_NAME format a30
col DIRECTORY_PATH format a100
select * from all_directories;
#查看系统最大游标数
select value from v$parameter where name = 'open_cursors';
#查看各用户游标数
select user_name, count(*) from v$open_cursor group by user_name
#查看当前打开的游标数：
select count(*) from v$open_cursor;
#查找数据库各用户各个终端的缓存游标数
SELECT AA.USERNAME, AA.MACHINE, SUM(AA.VALUE)
FROM (SELECT A.VALUE, S.MACHINE, S.USERNAME
FROM V$SESSTAT A, V$STATNAME B, V$SESSION S
WHERE A.STATISTIC# = B.STATISTIC#
AND S.SID = A.SID
AND B.NAME = 'session cursor cache count') AA
GROUP BY AA.USERNAME, AA.MACHINE
ORDER BY AA.USERNAME, AA.MACHINE;
#查找数据库各用户各个终端的打开游标数
SELECT AA.USERNAME, AA.MACHINE, SUM(AA.VALUE)
FROM (SELECT A.VALUE, S.MACHINE, S.USERNAME
FROM V$SESSTAT A, V$STATNAME B, V$SESSION S
WHERE A.STATISTIC# = B.STATISTIC#
AND S.SID = A.SID
AND B.NAME = 'opened cursors current') AA
GROUP BY AA.USERNAME, AA.MACHINE
ORDER BY AA.USERNAME, AA.MACHINE;
#查询当前正在消耗临时空间的sql语句
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'
select v$sql.sql_id,v$sql.sql_fulltext,swa.TEMPSEG_SIZE/1024/1024 TEMPSEG_M, swa.*
from v$sql_workarea_active swa,v$sql where swa.sql_id=v$sql.sql_id and swa.NUMBER_PASSES>0
#统计每个用户使用表空间率
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 "用户","表空间名"
#数据缓冲区命中率（百分比小于90就要加大db_cache_size）
SELECT a.VALUE+b.VALUE logical_reads, c.VALUE phys_reads,
round(100*(1-c.value/(a.value+b.value)),2)||'%' hit_ratio
FROM v$sysstat a,v$sysstat b,v$sysstat c
WHERE a.NAME='db block gets'
AND b.NAME='consistent gets'
AND c.NAME='physical reads';
#查询归档日志切换频率
SELECT TO_CHAR(first_time, 'MM/DD') DAY,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '00', 1, 0)) H00,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '01', 1, 0)) H01,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '02', 1, 0)) H02,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '03', 1, 0)) H03,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '04', 1, 0)) H04,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '05', 1, 0)) H05,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '06', 1, 0)) H06,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '07', 1, 0)) H07,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '08', 1, 0)) H08,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '09', 1, 0)) H09,
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,
COUNT(*) TOTAL
FROM (SELECT ROWNUM RN, FIRST_TIME FROM V$LOG_HISTORY WHERE first_time>sysdate-18
and FIRST_TIME>ADD_MONTHS(SYSDATE,-1) ORDER BY FIRST_TIME)
GROUP BY TO_CHAR(first_time, 'MM/DD')
ORDER BY MIN(RN);
#查询没有索引的表
Select table_name from user_tables where table_name not in (select table_name from user_indexes)
Select table_name from user_tables where table_name not in (select table_name from user_ind_columns)
#AWR设置每隔30分钟收集一次报告，保留14天的报告
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>14*24*60, interval=>30); --sqlplus 执行
select * from dba_hist_wr_control;
#SQL导出awr html报告
select * from dba_hist_snapshot
select * from table(dbms_workload_repository.awr_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid))
select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid, DBID, INSTANCE_NUMBER, startsnapid,endsnapid));
#导出上一个小时的AWR
select * from table(dbms_workload_repository.awr_report_html((select DBID from dba_hist_baseline), (select INSTANCE_NUMBER from v$instance), (select SNAP_ID from
(select * from
(select tt.*, rownum rn from
(select SNAP_ID
from dba_hist_snapshot
order by SNAP_ID desc） tt
where rownum < 3）
where rn > 1)),(select END_SNAP_ID from dba_hist_baseline)))
#查看当前会话的SID
select * from V$MYSTAT where rownum<2
#查询时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual; //日期转化为字符串
select to_char(sysdate,'yyyy') as nowYear from dual; //获取时间的年
select to_char(sysdate,'mm') as nowMonth from dual; //获取时间的月
select to_char(sysdate,'dd') as nowDay from dual; //获取时间的日
select to_char(sysdate,'hh24') as nowHour from dual; //获取时间的时
select to_char(sysdate,'mi') as nowMinute from dual; //获取时间的分
select to_char(sysdate,'ss') as nowSecond from dual; //获取时间的秒
#字符串转换为日期
select to_date('2020-05-21 09:41:20','yyyy-MM-dd HH24:mi:ss') from dual;
#查询表空间30天内增长情况(这个挺好用的,我之前还写过把查询结果画成图)：
select a.name, b.*
from v$tablespace a,
(select tablespace_id,
trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) datetime,
max(tablespace_usedsize * 8 / 1024) used_size
from dba_hist_tbspc_space_usage
where trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) >
trunc(sysdate - 30)
group by tablespace_id,
trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))
order by tablespace_id,
trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'))) b
where a.ts# = b.tablespace_id order by name,datetime
#查询统计信息收集时间
select WINDOW_NAME, WINDOW_NEXT_TIME,AUTOTASK_STATUS from DBA_AUTOTASK_WINDOW_CLIENTS;
#收集统计信息
1.analyze table DDCW.ORA1 compute statistics;
2.EXEC DBMS_STATS.GATHER_TABLE_STATS('DDCW','STUINFO'); --sqlplus 上执行
#查询用户的索引
select * from user_indexes;
#查询表的索引
select * from user_ind_columns where table_name='STUINFO';
#logmnr分析日志, 官方讲得更细：
begin
dbms_logmnr.start_logmnr(
starttime=>to_date('2020-04-12 14:33:00','YYYY-MM-DD HH24:MI:SS'),
endtime=>to_date('2020-04-12 14:34:00','YYYY-MM-DD HH24:MI:SS'),
options=> dbms_logmnr.dict_from_online_catalog+dbms_logmnr.continuous_mine);
end;
--分组显示
select * from (
select table_name,operation,count(*) from V$LOGMNR_CONTENTS group by table_name,operation order by 3 desc
)
where rownum<=10 order by 1;
redo日志暴增会间接导致归档日志数量增加，严重的会导致撑爆归档日志的存储空间，导致无法连接数据库
#前20的LOB
SELECT *
FROM (SELECT l.owner,
l.table_name,
l.column_name,
l.segment_name,
l.tablespace_name,
ROUND(s.bytes/1024/1024,2) size_mb
FROM dba_lobs l
JOIN dba_segments s ON s.owner = l.owner AND s.segment_name = l.segment_name
ORDER BY 6 DESC)
WHERE ROWNUM <= 20;
#用户对表空间的权限
select qut.username,
qut.tablespace_name,
qut.bytes / 1024 / 1024 usedSIZE_MB,
qut.max_bytes / 1024 / 1024 maxSIZE_MB
from DBA_TS_QUOTAS qut where qut.username = upper('DDCW')
order by maxSIZE_MB desc;
#查询redo
select * from v$log;
select * from v$logfile;
#查看数据库版本
select * from v$version;
#查询安装组件
SET lines 90 NUMWIDTH 12 PAGES 10000 LONG 2000000000
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS';
COL version FORMAT a12
COL comp_id FORMAT a8
COL schema LIKE version
COL comp_name FORMAT a35
COL status FORMAT a12
SELECT comp_id,schema,status,version,comp_name
FROM dba_registry
ORDER BY 1;
#查询补丁
select patch_id, action, status from dba_registry_sqlpatch
#查看归档量
select max(first_time) max_first_time,
to_char(first_time, 'yyyy-mm-dd') day,
count(recid) count_number,
count(recid) * 500 size_mb --500是redo大小
from v$log_history
group by to_char (first_time, 'yyyy-mm-dd')
order by 1 desc;
#查看表空间
SELECT * FROM (
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
"USED_RATE(%)",
FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
NVL (FREE_SPACE, 0) || 'M' "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(+)
ORDER BY 1)
WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM','UNDOTBS1','TEMP');
#查询当前用户:
SELECT DECODE(USER, 'XS$NULL', XS_SYS_CONTEXT('XS$SESSION','USERNAME'), USER) FROM SYS.DUAL;原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读