分享一些oracle常用的视图和操作
官方网址
https://docs.oracle.com/database/121/REFRN/toc.htm
查询数据库逻辑大小
select sum(bytes/1024/1024/1024) GB from DBA_SEGMENTS;
查询数据库物理大小
select sum(bytes/1024/1024/1024) GB from DBA_data_files;
查询数据库用户的逻辑大小
select OWNER,sum(BYTES/1024/1024/1024) GB from dba_segments where OWNER='YWGGFW' group by OWNER;
dg排错
select * from v$dataguard_status
select dest_name,status,error from v$archive_dest where dest_id=2;
查询表个数
select count(*) from dba_tables where owner='YGYW_USERCS';
查看资源限制
select * from v$resource_limit;
查询对象数是否一致（排除回收站对象）
select owner,object_type,count(*) from dba_objects
where object_name not like 'BIN$%'
and owner in ('DZDZ_TEMP','XTGL','DZDZ_CKTS','DZDZ_SJCS','DZDZ_YDPT','DZDZ_SJCJ','FPCY','GGS','ZKCX', 'DZDZ','DZDZ_LSSJ')
group by owner,object_type
order by 1,2;
查看数据库是readonly还是readwrite
select OPEN_MODE from v$database;
查看控制文件scn
select CHECKPOINT_CHANGE# from v$datafile;
查看数据文件scn
select CHECKPOINT_CHANGE#,FUZZY from v$datafile_header;
比对对象个数
SELECT D.OWNER,COUNT(1)
FROM dba_objects d
WHERE d.OWNER in ('YWWWW')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner)
GROUP BY D.OWNER
ORDER BY D.OWNER;
查询对象大小
select bytes/1024/1024/1024 from dba_segments where segment_name='PRICE' and owner='ZQ' and segment_type='TABLE';
查询会话
SELECT SID, SERIAL#, PADDR FROM V$SESSION
WHERE USERNAME = USER;
查看会话占用cpu高的
SELECT s.sid, s.serial#, p.spid ,s.status as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;
查询分区表
DBA_TAB_PARTITIONS
查询索引分区
dba_ind_partitions
查看数据库启动状态
select status from v$instance;
查看所有用户名
select * from all_users;
select username from DBA_USERS order by created;
查询directory
select * from dba_directories;
查询过去发生的事件（锁）
select instance_number,session_id,sql_id,machine,event,BLOCKING_INST_ID,blocking_session,to_char(sample_time,'yyyy-MM-dd HH24:mi:ss') from DBA_HIST_ACTIVE_SESS_HISTORY where sample_time>to_date('2019-11-22 17:00:00','yyyy-MM-dd HH24:mi:ss') and sample_time<to_date('2019-11-22 18:00:00','yyyy-MM-dd HH24:mi:ss') and wait_class<>'Idle' and event='enq: TX - row lock contention';
查询归档量
SELECT SUM(BLOCKS *BLOCK_SIZE )/1024/1024/1024 AS "Size(G)",TRUNC(completion_time) FROM v$archived_log GROUP BY TRUNC(completion_time) order by TRUNC(completion_time);
查找dump
select event,count(*) from gv$session_wait where wait_class<>'Idle' group by event;
查询视图创建DDL语句
select DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW/VIEW/TABLE','TABLENAME','USER') from dual;
查看用户a
select * from dba_sys_privs where grantee='USERNAME';
查看表执行计划
select * from table(dbms_xplan.display_awr('823mnkcytq2mm'));
查看当前的执行计划（last execution)
SELECT RPAD('Inst: ' || v.inst_id, 9) || ' ' ||
RPAD('Child: ' || v.child_number, 11) inst_child,
t.plan_table_output
FROM gv$sql v,
TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all',
NULL,
'ADVANCED ALLSTATS LAST -Projection -Outline -Note',
'inst_id = ' || v.inst_id ||
' AND sql_id = ''' || v.sql_id ||
''' AND child_number = ' || v.child_number)) t
WHERE v.sql_id = '854szqxypxu4r'
AND v.loaded_versions > 0;
查看当前的执行计划（all execution)
SELECT RPAD('Inst: ' || v.inst_id, 9) || ' ' ||
RPAD('Child: ' || v.child_number, 11) inst_child,
t.plan_table_output
FROM gv$sql v,
TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all',
NULL,
'ADVANCED ALLSTATS -Projection -Outline -Note',
'inst_id = ' || v.inst_id ||
' AND sql_id = ''' || v.sql_id ||
''' AND child_number = ' || v.child_number)) t
WHERE v.sql_id = '854szqxypxu4r'
AND v.loaded_versions > 0
AND v.executions > 1;
查看历史的执行计划
SELECT t.plan_table_output
FROM (SELECT DISTINCT sql_id, plan_hash_value, dbid
FROM dba_hist_sql_plan
WHERE sql_id = 'f5yun5dynkskv') v,
TABLE(DBMS_XPLAN.DISPLAY_AWR(v.sql_id,
v.plan_hash_value,
null,
'ADVANCED ALLSTATS')) t;
查看失败登陆用户信息
select userid, userhost, terminal, clientid from aud$ where returncode=1017;
查询数据库化身号
select * from v$database_Incarnation;
查询生产用户
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set line 200
SELECT d.username,
d.default_tablespace,
d.temporary_tablespace,
d.account_status,
d.created
from dba_users d
where d.account_status = 'OPEN' and d.username not like '%SYS%'
order by d.CREATED desc;
查询各个用户表个数
SELECT D.OWNER,COUNT(1)
FROM dba_objects d
WHERE d.OWNER in ('GSCW2019','CETRCWCM','ZJSJWLJG','JSEARCH','JCMS','GWJH','HZMCCLIENT','HZMCMEMAUDIT','HZMCASSET','HZCX','ARCHIVE','MCCH','SPOTLIGHT','TYYHTMP','GSCW','DRB','SZZS_QUERY','WLJG','MBKHNEW','ADMIN' ,'FUND','YGGS','TRSWCM_PLUGINS','BASE','RS330000','TRSWCM52','EXOA','ORT','CENTERSPEEDWAY')
and d.OWNER not in ('PUBLIC')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME AND D.OWNER=B.owner)
GROUP BY D.OWNER
ORDER BY D.OWNER;
查看用户对应的所有表和对应表空间
select table_name,tablespace_name from all_tables where owner='YWGGFW';
查询数据文件
set linesize 6000
col name format a60;
select file#,name,bytes from v$datafile;
col file_name format a60;
select file_name,tablespace_name,bytes from DBA_DATA_FILES;
查看dg启用模式
select DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
查询控制文件
select * from v$controlfile;
查看最新应用时间
select max(to_char(first_time,'yyyymmdd hh24:mi:ss')) from v$archived_log where applied='YES' and dest_id=2;
查看数据文件当前大小和最大可扩
SELECT R1.*,R2.MAX_SIZE_GB FROM
(SELECT /*+ ORDERED */
A.TABLESPACE_NAME TABLESPACE_NAME,
ROUND(A.BYTES / 1024 / 1024 / 1024, 2) CURRENT_SIZE_GB,
ROUND((A.BYTES - B.BYTES) / 1024 / 1024 / 1024, 2) USED_SIZE_GB,
ROUND(B.BYTES / 1024 / 1024 / 1024, 2) FREE_SIZE_GB,
ROUND(((A.BYTES - B.BYTES) / A.BYTES) * 100, 2) PERCENT_USED_RATE
FROM (SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, MAX(BYTES) LARGEST
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
ORDER BY A.TABLESPACE_NAME) R1,
(SELECT /*+ ORDERED */
D.TABLESPACE_NAME TABLESPACE_NAME,
ROUND(SUM(D.BYTES) / 1024 / 1024 / 1024, 2) CURRENT_SIZE_GB,
ROUND(SUM(D.MAXBYTES) / 1024 / 1024 / 1024, 2) MAX_SIZE_GB
FROM SYS.DBA_DATA_FILES D,
V$DATAFILE V,
(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_block_size') E
WHERE (D.FILE_NAME = V.NAME)
GROUP BY D.TABLESPACE_NAME) R2
WHERE R1.TABLESPACE_NAME = R2.TABLESPACE_NAME;
查询参数文件
Show parameter pfile;
查看日志文件
select * from v$logfile;
查找globlename
select * from global_name;
查看视图
Select owner,view_name from DBA_VIEWS;
查看索引
select owner,index_name,table_name from dba_indexes where owner=' ';
查看是否开启闪回
select log_mode,open_mode,flashback_on from v$database;
查询表所在的块
select rowid,dbms_rowid.rowid_relative_fno(rowid) rel_fno,dbms_rowid.rowid_block_number(rowid) blockno,dbms_rowid.rowid_row_number(rowid) rowno from jiujian;
查看表删除记录
select * from user_recyclebin;
判断是否陈旧
select owner, table_name, object_type, stale_stats, num_rows, last_analyzed
from dba_tab_statistics s
where owner = 'SCOTT'
and (owner, table_name) in
(select object_owner, object_name
from v$sql_plan
where sql_id = '8276t1g7j3ksh'
and object_type like '%TABLE%'
union
select table_owner, table_name
from dba_indexes
where owner = 'SCOTT'
and (owner, index_name) in
(select object_owner, object_name
from v$sql_plan
where sql_id = '8276t1g7j3ksh'
and object_type like '%INDEX%'));
查看sga组件
select * from v$sgainfo;
查询数据库组件
set linesize 300
col comp_name for a40
col comp_id for a10
select comp_id,comp_name,version,status from dba_registry;
查看数据库补丁版本
set linesize 500
col version for a10
col bundle_series for a8
col comments for a20
select version, bundle_series, comments from dba_registry_history;
set linesize 100
col product for a40
col version for a15
col status for a20
select * from product_component_version;
查看每日归档量
select to_char(completion_time,'yyyy-mm-dd') as date1,count(0) as cnt,round(sum((blocks *block_size)/1024/1024)) as mb from v$archived_log group by to_char(completion_time,'yyyy-mm-dd') order by date1 desc;
查询每小时归档量
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select logtime,
count(*),
round(sum(blocks * block_size) / 1024 / 1024/1024) mbsize
from (select trunc(first_time, 'hh') as logtime, a.BLOCKS, a.BLOCK_SIZE
from v$archived_log a
where a.DEST_ID = 1
and a.FIRST_TIME > trunc(sysdate-1))
group by logtime
order by logtime desc;
查看数据块损坏
select tablespace_name,segment_type,owner,segment_name from dba_extents where file_id=38 and 295529 between block_id AND block_id + blocks - 1;
查看表空间
select * from v$tablespace
SELECT UPPER(F.TABLESPACE_NAME) ,
D.TOT_GROOTTE_MB ,
D.TOT_GROOTTE_MB - F.TOTAL_BYTES ,
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') ,
F.TOTAL_BYTES ,
F.MAX_BYTES
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
查看表空间是否自增长
select file_name,autoextensible,increment_by from dba_data_files;
查看表最后统计信息时间
select LAST_ANALYZED from dba_tables where table_name='ZY_DETAIL_CHARGE';
收集表的统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'JASON’,
estimate_percent => 100,
method_opt => 'for all columns size 1',
no_invalidate => FALSE,
cascade => TRUE);
END;
/
收集索引统计信息
BEGIN
SYS.DBMS_STATS.GATHER_INDEX_STATS (OwnName => 'ABC',
IndName => 'IDX_FUNC_ABC',
Estimate_Percent => 10,
Degree => SYS.DBMS_STATS.DEFAULT_DEGREE,
No_Invalidate => FALSE);
END;
/
强制走索引
SELECT /*+index(t pk_emp)*/* FROM EMP T
--强制索引，/*.....*/第一个星星后不能有空格，里边内容结构为：加号index(表名 空格 索引名)。
--如果表用了别名，注释里的表也要使用别名
从内存中生成spfile
create spfile from memory;
Explain for查看执行计划方法
select * from table(dbms_xplan.display('plan_table'));
Explain plan for
sql语句
查询创建用户的语句
select dbms_metadata.get_ddl('USER',ORIG.username) from dba_users ORIG;
select 'grant '||PRIVILEGE||' to '||GRANTEE||
decode(ADMIN_OPTION,'NO',';','YES',' with admin option;')
"Grant dba_sys_privs DDL"
from dba_sys_privs where grantee=&UserName;
查看dg进程
select process,status from gv$managed_standby;
查看密码文件
select * from v$pwfile_users;
查询job运行日志
select owner,job_name,status,actual_start_date from dba_scheduler_job_run_details where job_name='JOB_NAME' order by log_date desc;
查看用户关联哪几个表空间：
select tablespace_name, sum(bytes) / (1024*1024*1024) size_G
from dba_segments
where owner = 'YLR'
group by tablespace_name;
rman恢复进度
select inst_id,sid,serial# opname,round (sofar/totalwork *100,2) from gv$session_longops where opname not like '%aggregate%' and totalwork !=0 and sofar <> totalwork;
查询scn是否一致（DG切换）
select FILE#,CHECKPOINT_CHANGE#,FUZZY from v$datafile_header;
1、在以上图中使用系统进程PID查询对应的物理地址
SELECT v.addr,v.* FROM v$process v
WHERE v.SPID = '5256';
2、通过该物理地址查找对应的SQL_ID
SELECT t.SQL_ID,t.* FROM v$session t
WHERE t.paddr= '000000025C5EB9F8';
3、通过SQL_ID来查找对应的SQL语句
SELECT sql_text FROM v$sql
WHERE sql_id = 'dqu970xzs3gpv';
###最近1分钟cpu消耗最高的sql
SELECT ASH.INST_ID,
ASH.SQL_ID,
(SELECT VS.SQL_TEXT
FROM GV$SQLAREA VS
WHERE VS.SQL_ID = ASH.SQL_ID
AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
ASH.SESSION_INFO,
COUNTS,
PCTLOAD * 100 || '%' PCTLOAD
FROM (SELECT ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
(ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
ASH.SESSION_TYPE) SESSION_INFO,
COUNT(*) COUNTS,
ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), 2) PCTLOAD,
DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER
FROM GV$ACTIVE_SESSION_HISTORY ASH
WHERE ASH.SESSION_TYPE <> 'BACKGROUND'
AND ASH.SESSION_STATE = 'ON CPU'
AND SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
GROUP BY ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
ASH.SQL_OPNAME,
(ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' ||
ASH.MACHINE || '--' || ASH.CLIENT_ID || '--' ||
ASH.SESSION_TYPE)) ASH
WHERE RANK_ORDER <= 10
ORDER BY COUNTS DESC;
SELECT ASH.INST_ID,
ASH.SQL_ID,
(SELECT VS.SQL_TEXT
FROM GV$SQLAREA VS
WHERE VS.SQL_ID = ASH.SQL_ID
AND ASH.INST_ID = VS.INST_ID) SQL_TEXT,
ASH.SQL_CHILD_NUMBER,
ASH.SESSION_INFO,
COUNTS,
PCTLOAD * 100 || '%' PCTLOAD
FROM (SELECT ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
(ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' || '--' || ASH.CLIENT_ID || '--' ||
ASH.SESSION_TYPE) SESSION_INFO,
COUNT(*) COUNTS,
ROUND(COUNT(*) / SUM(COUNT(*)) OVER(), 2) PCTLOAD,
DENSE_RANK() OVER(ORDER BY COUNT(*) DESC) RANK_ORDER
FROM GV$ACTIVE_SESSION_HISTORY ASH
WHERE ASH.SESSION_TYPE <> 'BACKGROUND'
AND ASH.SESSION_STATE = 'ON CPU'
AND SAMPLE_TIME > SYSDATE - 1 / (24 * 60)
GROUP BY ASH.INST_ID,
ASH.SQL_ID,
ASH.SQL_CHILD_NUMBER,
(ASH.MODULE || '--' || ASH.ACTION || '--' || ASH.PROGRAM || '--' || '--' || ASH.CLIENT_ID || '--' ||
ASH.SESSION_TYPE)) ASH
WHERE RANK_ORDER <= 10
ORDER BY COUNTS DESC;
一、查询执行最慢的sql
select *
from (select sa.SQL_TEXT,
sa.SQL_FULLTEXT,
sa.EXECUTIONS "执行次数",
round(sa.ELAPSED_TIME / 1000000, 2) "总执行时间",
round(sa.ELAPSED_TIME / 1000000 / sa.EXECUTIONS, 2) "平均执行时间",
sa.COMMAND_TYPE,
sa.PARSING_USER_ID "用户ID",
u.username "用户名",
sa.HASH_VALUE
from v$sqlarea sa
left join all_users u
on sa.PARSING_USER_ID = u.user_id
where sa.EXECUTIONS > 0
order by (sa.ELAPSED_TIME / sa.EXECUTIONS) desc)
where rownum <= 50;
二、查询次数最多的 sql
select *
from (select s.SQL_TEXT,
s.EXECUTIONS "执行次数",
s.PARSING_USER_ID "用户名",
rank() over(order by EXECUTIONS desc) EXEC_RANK
from v$sql s
left join all_users u
on u.USER_ID = s.PARSING_USER_ID) t
where exec_rank <= 100;
查看steamspool实际大小
select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ 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 like '%streams_pool%'；
扫一扫
188
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
