Oracle常用sql语句记录持续更新

置顶软茸兔

已于 2024-05-13 11:21:14 修改

阅读量953

点赞数 1

分类专栏： Oracle 文章标签： Oracle Sql语句

于 2019-05-07 15:47:45 首次发布

本文链接：https://blog.csdn.net/Lizi_TT/article/details/89922594

版权

Oracle 专栏收录该内容

49 篇文章 0 订阅

订阅专栏

查看表空间

set linesize 300
set pagesize 300
col name for a30
SELECT d.tablespace_name name,
       d.status status,
       d.contents type,
       d.extent_management extent_mgt,
       d.segment_space_management segment_mgt,
       NVL(a.bytes, 0) / 1024 / 1024 ts_size,
       NVL(f.bytes, 0) / 1024 / 1024 free,
       NVL(a.bytes, 0)/1024/1024 - NVL(f.bytes, 0)/1024/1024 used,
       NVL(f.bytes / a.bytes * 100, 0) pct_free
  FROM sys.dba_tablespaces d,
       (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes
          from dba_free_space
         group by tablespace_name) f
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = f.tablespace_name(+)
   AND NOT
        (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.tablespace_name name,
       d.status status,
       d.contents type,
       d.extent_management extent_mgt,
       d.segment_space_management segment_mgt,
       NVL(a.bytes, 0) / 1024 / 1024 ts_size,
       NVL(a.bytes - NVL(t.bytes, 0), 0) / 1024 / 1024 free,
       NVL(t.bytes, 0) / 1024 / 1024 used,
       NVL((a.bytes - NVL(t.bytes, 0)) / a.bytes * 100, 0) pct_free
  FROM sys.dba_tablespaces d,
       (select tablespace_name, sum(bytes) bytes 
          from dba_temp_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes_cached) bytes
          from v$temp_extent_pool
         group by tablespace_name) t
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = t.tablespace_name(+)
   AND d.extent_management like 'LOCAL'
   AND d.contents like 'TEMPORARY'
 ORDER BY 2,9;

数据文件信息

select file_id,tablespace_name,bytes/1024/1024/1024,autoextensible,maxbytes/1024/1024/1024 from dba_data_files;

数据文件使用率

select /*+ ordered use_hash(a,b,c) */
 a.file_id,
 a.file_name,
 a.filesize,
 b.freesize,
 (a.filesize - b.freesize) usedsize,
 c.hwmsize,
 c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
 a.filesize - c.hwmsize canshrinksize
  from (select file_id, file_name, round(bytes / 1024 / 1024) filesize
          from dba_data_files) a,
       (select file_id, round(sum(dfs.bytes) / 1024 / 1024) freesize
          from dba_free_space dfs
         group by file_id) b,
       (select file_id, round(max(block_id) * 8 / 1024) HWMsize
          from dba_extents
         group by file_id) c
 where a.file_id = b.file_id
   and a.file_id = c.file_id
 order by unsedsize_belowhwm desc

rman备份信息查询

set linesize 300 
set pagesize 300
col TIME_TAKEN_DISPLAY for a8 
col "INPUT(SUM)" for a9 
col "OUTPUT(SUM)" for a10 
col status for a30 
col "INPUT(s)" for a9
col "OUTPUT(s)" for a9 
col OUTPUT_DEVICE_TYPE for a5 
col INPUT_TYPE for a15 
col start_time for a20 
col end_time for a20 
SELECT session_key, 
TO_CHAR (START_TIME, 'yyyy-mm-dd hh24:mi:ss') start_time, 
TO_CHAR (end_time, 'yyyy-mm-dd hh24:mi:ss') end_time, 
TIME_TAKEN_DISPLAY, 
INPUT_BYTES_DISPLAY "INPUT(SUM)", 
OUTPUT_BYTES_DISPLAY "OUTPUT(SUM)", 
OUTPUT_DEVICE_TYPE, 
STATUS, 
INPUT_TYPE, 
INPUT_BYTES_PER_SEC_DISPLAY "INPUT(s)", 
OUTPUT_BYTES_PER_SEC_DISPLAY "OUTPUT(s)" 
FROM v$rman_backup_job_details 
where TO_CHAR (START_TIME, 'yyyy-mm-dd') >  to_char(sysdate-15,'yyyy-mm-dd')
order by session_key ;

DG同步查询

select dest_id,thread#,max(sequence#) 
from v$archived_log
where dest_id=1
group by thread#,dest_id
union
select dest_id,thread#,max(sequence#) 
from v$archived_log
where dest_id=2 and applied='YES'
group by thread#,dest_id;

备库

 select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');

监控正在运行的sql

col inst_sid heading "INST_ID|:SID" format a7
col username format a10
col machine format a12
col sql_exec_start   heading "SQL|START|D HH:MM:SS" format a11
col sql_id format a13
col sql_text format a40
col event format a33
col wait_sec heading "WAIT|(SEC)" format 99999
set linesize 200

select ses.inst_id||chr(58)||ses.sid as inst_sid
   ,username
   ,(sysdate - sql_exec_start) day(1) to second(0) as sql_exec_start
   ,ses.sql_id
   ,substr(sql.sql_text,1,40) sql_text
   ,substr
      (case time_since_last_wait_micro
         when 0 then (case wait_class when 'Idle' then 'IDLE: '||event else event end)
         else 'ON CPU'
         end
      ,1,33) event
   ,(case time_since_last_wait_micro
      when 0 then wait_time_micro
      else time_since_last_wait_micro
      end) /1000000 wait_sec
from gv$session ses,gv$sqlstats sql 
where ses.inst_id||chr(58)||ses.sid <> sys_context ('USERENV','INSTANCE')||chr(58)||sys_context ('USERENV','SID')
   and username is not null
   and status='ACTIVE'
   and ses.sql_id=sql.sql_id (+)
order by sql_exec_start,
   username,ses.sid,
   ses.sql_id;

检查是否有dx锁(事务锁)

column event format a30  
  column sess format a20
  set linesize 150
  break on id1 skip 1
select decode(request,0,'Holder:',' Waiter:') || s.inst_id || ':' || s.sid||','|| s.serial# sess,
      id1, id2, lmode, request, l.type, ctime, s.sql_id, s.event,s.last_call_et
-- ,s.service_name
  from gv$lock l, gv$session s
  where (id1, id2, l.type) in
    (select id1, id2, type from gv$lock where request>0
    )
   and l.sid=s.sid
   and l.inst_id=s.inst_id    
  order by id1, ctime desc, request;
  
set linesize 300 pagesize 300
 column USERNAME format a20  
 column MACHINE format a20
 column PROGRAM format a30

 select SID,SERIAL#,COMMAND,STATUS,SQL_ID,PREV_SQL_ID,USERNAME,MACHINE,PROGRAM from gv$session where inst_id=2 and sid=1157;
 
 select inst_id,sql_text from gv$sqltext where sql_id='31vantjnzcydp' order by inst_id,piece;

通过sid查询该会话持有锁的对象

set linesize 300 pagesize 300
col 机器名 for a20
col 终端用户名 for a10
col 登录机器用户名 for a10
col 登录用户 for a10

SELECT l.session_id sid,
       s.serial#,
       l.INST_ID,  
       l.locked_mode 锁模式,  
       l.oracle_username 登录用户,  
       l.os_user_name 登录机器用户名,  
       s.machine 机器名,  
       s.terminal 终端用户名,  
       o.object_name 被锁对象名,
       s.STATUS,  
       s.logon_time 登录数据库时间  
FROM gv$locked_object l, all_objects o, gv$session s  
WHERE l.object_id = o.object_id  
   AND l.session_id = s.sid  
   AND s.sid=??;

单实例-查询某表的锁信息

col username for a10
col program for a20
col machine for a40
set linesize 200
SELECT T2.USERNAME,T2.SID,T2.SERIAL#,T2.LOGON_TIME,T2.MACHINE,T2.PROGRAM
FROM
V$LOCKED_OBJECT T1,V$SESSION T2
WHERE
T1.SESSION_ID=T2.SID AND T1.OBJECT_ID =
(SELECT OBJECT_ID FROM ALL_OBJECTS WHERE OWNER='NEWROOT' AND OBJECT_NAME ='QUEUE_IMAP_FETCH');


select b.spid,a.osuser,b.program 
  from v$session a,v$process b 
 where a.paddr=b.addr 
   and a.sid=3914;

SELECT Upper(F.TABLESPACE_NAME)         "表空间名",
       D.TOT_GROOTTE_MB                 "表空间大小(M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
       To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
       || '%'                           "使用比",
       F.TOTAL_BYTES                    "空闲空间(M)",
       F.MAX_BYTES                      "最大块(M)"
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 "使用比" desc;

set linesize 300
set pagesize 300
col file_name for a50
select file_name,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024/1024,AUTOEXTENSIBLE,maxBYTES/1024/1024/1024 from dba_data_files order by 3,4;


select 'alter index '||owner||'.'||index_name||' rebuild online;' from dba_indexes where status='UNUSABLE';

日志切换

set linesize 230
SELECT  THREAD#,trunc(first_time) "Date",
to_char(first_time, 'Dy') "Day",
substr(count(1),1,5) as "Total",
substr(SUM(decode(to_char(first_time, 'hh24'),'00',1,0)),1,3) as "h00",
substr(SUM(decode(to_char(first_time, 'hh24'),'01',1,0)),1,3) as "h01",
substr(SUM(decode(to_char(first_time, 'hh24'),'02',1,0)),1,3) as "h02",
substr(SUM(decode(to_char(first_time, 'hh24'),'03',1,0)),1,3) as "h03",
substr(SUM(decode(to_char(first_time, 'hh24'),'04',1,0)),1,3) as "h04",
substr(SUM(decode(to_char(first_time, 'hh24'),'05',1,0)),1,3) as "h05",
substr(SUM(decode(to_char(first_time, 'hh24'),'06',1,0)),1,3) as "h06",
substr(SUM(decode(to_char(first_time, 'hh24'),'07',1,0)),1,3) as "h07",
substr(SUM(decode(to_char(first_time, 'hh24'),'08',1,0)),1,3) as "h08",
substr(SUM(decode(to_char(first_time, 'hh24'),'09',1,0)),1,3) as "h09",
substr(SUM(decode(to_char(first_time, 'hh24'),'10',1,0)),1,3) as "h10",
substr(SUM(decode(to_char(first_time, 'hh24'),'11',1,0)),1,3) as "h11",
substr(SUM(decode(to_char(first_time, 'hh24'),'12',1,0)),1,3) as "h12",
substr(SUM(decode(to_char(first_time, 'hh24'),'13',1,0)),1,3) as "h13",
substr(SUM(decode(to_char(first_time, 'hh24'),'14',1,0)),1,3) as "h14",
substr(SUM(decode(to_char(first_time, 'hh24'),'15',1,0)),1,3) as "h15",
substr(SUM(decode(to_char(first_time, 'hh24'),'16',1,0)),1,3) as "h16",
substr(SUM(decode(to_char(first_time, 'hh24'),'17',1,0)),1,3) as "h17",
substr(SUM(decode(to_char(first_time, 'hh24'),'18',1,0)),1,3) as "h18",
substr(SUM(decode(to_char(first_time, 'hh24'),'19',1,0)),1,3) as "h19",
substr(SUM(decode(to_char(first_time, 'hh24'),'20',1,0)),1,3) as "h20",
substr(SUM(decode(to_char(first_time, 'hh24'),'21',1,0)),1,3) as "h21",
substr(SUM(decode(to_char(first_time, 'hh24'),'22',1,0)),1,3) as "h22",
substr(SUM(decode(to_char(first_time, 'hh24'),'23',1,0)),1,3) as "h23"
FROM    V$log_history
group by trunc(first_time), to_char(first_time, 'Dy'),THREAD#
Order by 1,2;   

select 'thread 1' from dual;
select (max(first_time) - min(first_time))*24*60/count(1) "switch_rate(min)" from gv$log_history where first_time > sysdate - 7 and thread# = 1;

select 'thread 2' from dual;
select (max(first_time) - min(first_time))*24*60/count(1) "switch_rate(min)" from gv$log_history where first_time > sysdate - 7 and thread# = 2;

表空间数据增长量

SET LINESIZE 500
     SET PAGESIZE 1000
       select S.SNAP_ID,
               T.NAME,
             S.RTIME,
              TABLESPACE_SIZE*8/1024 TABLESPACE_SIZE_MB,TABLESPACE_USEDSIZE*8/1024 TABLESPACE_USEDSIZE_MB,
              (TABLESPACE_USEDSIZE - LAG(TABLESPACE_USEDSIZE, 1, NULL)    OVER(ORDER BY S.SNAP_ID))*8/1024  AS DIFF_MB 
          from V$TABLESPACE T, DBA_HIST_TBSPC_SPACE_USAGE S
         where T.TS# = S.TABLESPACE_ID
           and T.name = UPPER('&tbs');

DG检查

select dest_id,thread#,max(sequence#) 
from v$archived_log
where dest_id=1
group by thread#,dest_id
union
select dest_id,thread#,max(sequence#) 
from v$archived_log
where dest_id=2 and applied='YES'
group by thread#,dest_id;


 select PROCESS,STATUS,SEQUENCE#  from v$managed_standby;
set linesize 300 pagesize 300
col dest_name for a20
 select dest_name,archived_thread#,archived_seq#,applied_thread#,applied_seq#,db_unique_name from v$archive_dest_status where status='VALID';

select timestamp,message from v$dataguard_status;

select recovery_mode from v$archive_dest_status where dest_id=2;

闪回信息检查

set linesize 300 pagesize 300
select * from v$flash_recovery_area_usage;
select name,storage_size from v$restore_point;

执行时间超3小时的sql_id

select * from (select v.sql_id,
ROUND(v.ELAPSED_TIME / 1000000 / 3600 /(CASE
               WHEN (EXECUTIONS = 0 OR NVL(EXECUTIONS, 1 ) = 1) THEN
                1
               ELSE
                EXECUTIONS
             END),2) as TIMEH
from v$sql v  ) a where a.TIMEH > 3.0;

僵死进程

select spid from v$process where addr not in (select paddr from v$session);

select 'kill -9 '||spid from v$process
where addr in
(select addr from v$process where pid<>1
 minus
 select paddr from v$session);

重建失效对象

 select 'alter '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;' from dba_objects where status !='VALID' and owner not in ('SYS','SYSTEM') and object_type in ('TRIGGER','VIEW','PROCEDURE','FUNCTION','SYNONYM') order by 1;

注意 package body需要使用  ALTER PACKAGE my_package COMPILE BODY;  重建

具有较多段的对象

select segment_name, tablespace_name, extents from dba_segments where owner not in ('SYS','SYSTEM') and extents >200

查询ASM 磁盘组的用量情况

SELECT NAME,TOTAL_MB,FREE_MB,ROUND(((TOTAL_MB-FREE_MB)/TOTAL_MB)*100,2) "%" FROM  v$ASM_DISKGROUP;

查看某用户下所有对象的空间占用量：

select OWNER,t.segment_name,t.segment_type,sum(t.bytes/1024/1024) MB
from dba_segments t
where t.owner='OSS' 
and t.segment_type='TABLE'
and sum(t.bytes/1024/1024)>1024
group by OWNER,t.segment_name,t.segment_type
order by MB desc;

查看表空间中所有对象大小

SELECT t.owner, t.segment_name,SUM(bytes)/1024/1024 From dba_segments t 
WHERE t.tablespace_name = 'MIGBI'
GROUP BY t.owner,t.segment_name
ORDER BY SUM(bytes) desc;

按userid条件查询所有用户所有对象的空间占用量：

select a.OWNER,a.segment_name,a.segment_type,sum(a.bytes/1024/1024) MB
from dba_segments a,dba_users b
where a.owner=b.username
and a.segment_type='TABLE'
and b.user_id>='48'
and a.bytes>='104857600'
group by a.OWNER,a.segment_name,a.segment_type
order by a.owner,MB desc;

查看某用户下所有对象的空间占用量，以逗号结尾

SELECT T.OWNER||','||T.SEGMENT_TYPE||','||T.SEGMENT_NAME||','||SUM(BYTES)/1024/1024||',' FROM DBA_SEGMENTS T
WHERE T.TABLESPACE_NAME='OSS_NEW'
GROUP BY t.owner,T.SEGMENT_TYPE,t.segment_name
ORDER BY SUM(BYTES) DESC;

b2e889febd26278d7ffa9ec1233b0962c
b2e889febd6278d7ffa9ec1233b0962c

select to_char(pushuser_id) "userid",phone_type,device_id,to_char(update_time,'yyyymmdd')"uptime",device_token from multi_devices where pushuser_id=13910296832 order by update_time;

批量生成一天的awr报告

1.生成查询语句
#此处是按照默认的快照间隔时间,生成前一天所有的awr报告  的查询语句
select
'spool awr_'||dbid||'_'||instance_number||'_'||b_snap_id||'_'||e_snap_id||'.html'
||chr(10)||
'select output from table(dbms_workload_repository.awr_report_html('||dbid||', '||instance_number||','||b_snap_id||','||e_snap_id||'));'
||chr(10)||
'spool off'  
from
(select dbid,instance_number,snap_id b_snap_id,lead(snap_id,1,0) over(partition by instance_number order by snap_id) e_snap_id
from dba_hist_snapshot
where begin_interval_time>=trunc(sysdate-1)
and begin_interval_time<=trunc(sysdate))
where e_snap_id!=0;

2.产生报告
ORACLE_SID=ct6601sb
sqlplus -s / as sysdba
set linesize 1000;
set echo off;
set heading off;
粘贴步骤1生成的语句执行即可.

修改索引所属表空间

alter index BEIJING.SYS_C0066352 rebuild tablespace bjlog;

查看用户表、索引、分区表占用空间

select segment_name, sum(bytes)/1024/1024 Mbytese from user_segments group by segment_name;

分区表TABLE PARTITION占用空间

select segment_name,sum(bytes)/1024/1024 Mbytes from user_segments where segment_type='TABLE PARTITION' group by segment_name;

查看索引占用的空间

select * from (select segment_name,segment_type,sum(bytes)/1024/1024 as MB 
from user_segments where segment_type='INDEX'
group by segment_name,segment_type order by MB desc )
create user wang identified by wangwei 
default tablespace users;

查询分区表的所属表空间

select distinct TABLESPACE_NAME from dba_segments where SEGMENT_NAME='PE_CMPOP';

分区表的分区添加和删除

alter table api_docc_log drop partition P_202501;
alter table api_docc_log drop partition P_202502;

alter table api_docc_log add partition P_202501 VALUES LESS THAN ('2025-02-01') TABLESPACE bjlog;
alter table api_docc_log add partition P_202502 VALUES LESS THAN ('2025-03-01') TABLESPACE bjlog;

根据userid查询所有业务用户表记录数的总和：
SELECT SUM(NUM_ROWS)
FROM
(select owner,table_name,NUM_ROWS from all_tables where owner
in
(select USERNAME from all_users where user_id<=94 and user_id>=84)
order by num_rows);

给某个表添加列，设定默认值和非空：
ALTER TABLE MULTI_DEVICES ADD DEVICE_TOKEN_ID VARCHAR2(32) DEFAULT 1 NOT NULL;

查询数据库中所有所信息的SID、序列号、用户、登陆时间、类型、机器名、表名
col username for a10
col program for a20
col machine for a40
col object_name for a20
set linesize 200
select t2.username,t2.sid,t2.serial#,t2.logon_time,t2.type,t3.object_name,t2.machine
from v $locked_object t1,v$ session t2,all_objects t3
where t1.session_id=t2.sid and t1.object_id=t3.object_id order by t2.logon_time;

查看存储过程内容
SELECT text FROM user_source WHERE NAME = ‘Procedure Name’ ORDER BY line;

重建失效索引
select ‘alter index ‘||owner||’.’||index_name||’ rebuild online;’ from dba_indexes where status=‘UNUSABLE’;

搜集表统计信息

exec dbms_stats.gather_table_stats(ownname => ‘A4LOG’,tabname => ‘A4_SYS_ACSLOG’,estimate_percent => 10,method_opt=> ‘for all indexed columns’);

查看自动收集任务及状态

select client_name,status from Dba_Autotask_Client where client_name=‘auto optimizer stats collection’;

停止自动收集任务

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => ‘auto optimizer stats collection’,
operation => NULL, window_name => NULL);
END;
/

PL/SQL procedure successfully completed.

启动自动收集任务

BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => ‘auto optimizer stats collection’,
operation => NULL, window_name => NULL);
END;
/

查看自动收集任务历史执行状态

SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed FROM dba_autotask_client_history WHERE client_name like ‘%stats%’;

查看自动收集任务执行时间窗口

select WINDOW_NAME, WINDOW_NEXT_TIME , WINDOW_ACTIVE,OPTIMIZER_STATS from DBA_AUTOTASK_WINDOW_CLIENTS order by WINDOW_NEXT_TIME ;

检查及修改awr快照保留时间
select * from dba_hist_wr_control;

修改保留10天
exec dbms_workload_repository.modify_snapshot_settings(retention=>102460);

查看数据库会话数
select inst_id,SESSIONS_CURRENT,SESSIONS_HIGHWATER,SESSIONS_MAX from gv$license;

进程与会话的关系
sessions=(1.1*process+5) 10g应该

DG读写切换
to_snapshot
#!/bin/bash

source /home/oracle/.bash_profile
echo
echo “##############start time date '+%Y%m%d %H:%M'####################”

sqlplus -s / as sysdba << EOF
recover managed standby database cancel;
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
alter database open;
exit
EOF

echo “##############end time date '+%Y%m%d %H:%M'####################”

to_adg
#!/bin/bash

source /home/oracle/.bash_profile
echo
echo “##############start time date '+%Y%m%d %H:%M'####################”

sqlplus -s / as sysdba << EOF
shutdown immediate;
startup mount;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
shutdown immediate;
startup;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
exit
EOF

echo “##############end time date '+%Y%m%d %H:%M'####################”

查看还原点
select name,storage_size from v$restore_point;

搜集db_time
set lines 155
col dbtime for 999,999.99
col begin_timestamp for a40
select * from (
select begin_snap, end_snap, timestamp begin_timestamp, inst, a/1000000/60 DBtime from
(
select
e.snap_id end_snap,
lag(e.snap_id) over (order by e.snap_id) begin_snap,
lag(s.end_interval_time) over (order by e.snap_id) timestamp,
s.instance_number inst,
e.value,
nvl(value-lag(value) over (order by e.snap_id),0) a
from dba_hist_sys_time_model e, DBA_HIST_SNAPSHOT s
where s.snap_id = e.snap_id
and e.instance_number = s.instance_number
and to_char(e.instance_number) like nvl(‘&instance_number’,to_char(e.instance_number))
and stat_name = ‘DB time’
)
where begin_snap between nvl(‘&begin_snap_id’,0) and nvl(‘&end_snap_id’,99999999)
and begin_snap=end_snap-1
order by dbtime desc
)
where rownum < 31
/

临时段占用
set linesize 300 pagesize 300
select USERNAME,SQL_ID,TABLESPACE,SEGTYPE,EXTENTS,BLOCKS from v$tempseg_usage;

表元数据

select dbms_metadata.get_ddl(‘类型’,‘表名’,‘用户’) from dual;

分区表检查
set linesize 300 pagesize 300
col table_owner for a20
col table_name for a20
col partition_name for a20
col tablespace_name for a20
select table_owner,table_name,partition_name,tablespace_name,num_rows,last_analyzed from dba_tab_partitions where table_name=‘A4_SYS_ACSLOG’;

查找表FKXXX的外键
select a.constraint_name, a.table_name, b.constraint_name
from user_constraints a, user_constraints b
where a.constraint_type = ‘R’ and b.constraint_type = ‘P’ and a.r_constraint_name = b.constraint_name

– P 代表主键， R 代表外键
约束
select constraint_name from dba_cons_columns where table_name=‘表名’;

停用job
EXEC DBMS_JOB.BROKEN(774, TRUE);

BEGIN
DBMS_SCHEDULER.DISABLE(name => ‘UPDATE_DQ_DEP_RANGE’);
END;

set linesize 300
col current_scn for 99999999999999
select current_scn from v$database;

查看数据库状态
set linesize 300 pagesize 300
select NAME,LOG_MODE,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,DATAGUARD_BROKER,GUARD_STATUS,FLASHBACK_ON from v$database;

查看数据库audit信息

set linesize 300 pagesize 300
col os_username for a20
col userhost for a30
col returncode for a20
select username “username”,
to_char(timestamp, ‘DD-MON-YYYY HH24:MI:SS’) “time_stamp”,
action_name “statement”,
os_username “os_username”,
userhost “userhost”,
returncode || decode(returncode,
‘1004’,
‘-Wrong Connection’,
‘1005’,
‘-NULL Password’,
‘1017’,
‘-Wrong Password’,
‘1045’,
‘-Insufficient Priviledge’,
‘0’,
‘-Login Accepted’,
‘–’) “returncode”
from sys.dba_audit_session
where to_char(timestamp,‘yyyymmdd’)>=‘20191206’
and returncode <> 0
order by timestamp;

获取隐含参数

col name for a30;
col value for a10;
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 $k s pp i 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_mjc_enabled’
order by
translate(x.ksppinm, ’ _', ’ ')
/

创建DBLINK
create database link LINK_JSADMIN connect to jsadmin identified by JLjs0dam using ‘(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 11.11.227.234)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = doea5db)))’;

create database link LK_T_JSADMIN connect to jsadmin identified by D0ejsadm using ‘(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.99.233)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))’;

恢复数据 remap表

impdp “‘/ as sysdba’” directory=JSADMIN_DIR dumpfile=JSADMIN.2020-06-01.dump tables=jsadmin.JJ_GCSG_ZLYS_GCZLKZZLHCJL_SUB remap_table=jsadmin.JJ_GCSG_ZLYS_GCZLKZZLHCJL_SUB:JJ_SUB_519 logfile=JJ_GCSG_ZLYS_GCZLKZZLHCJL_SUB_519.log

关闭正在执行的sql

1、找出正在执行的job编号和会话编号
SELECT * FROM dba_jobs_running;
2、停止job的运行
SELECT SID ,serial# FROM v $sess i o nW H ERES I D =^{'}$ SID’;

EXEC dbms_job.broken(&JOB,TRUE );–一定要先broken
ALTER SYSTEM KILL SESSION ‘&sid,&session’ ;–杀掉job进程

补充同义词
select ‘grant select on a5admin.’||table_name||’ to a5query;’ from dba_tables where owner=‘A5ADMIN’ and table_name not in (select table_name from dba_synonyms where owner=‘A5QUERY’);
select ‘create synonym a5query.’||table_name||’ for a5admin.‘||table_name||’;’ from dba_tables where owner=‘A5ADMIN’ and table_name not in (select table_name from dba_synonyms where owner=‘A5QUERY’);

select ‘grant select on jsadmin.’||table_name||’ to jjquery;’ from dba_tables where owner=‘JSADMIN’ and table_name not in (select table_name from dba_synonyms where owner=‘JJQUERY’);
select ‘create synonym jjquery.’||table_name||’ for jsadmin.‘||table_name||’;’ from dba_tables where owner=‘JSADMIN’ and table_name not in (select table_name from dba_synonyms where owner=‘JJQUERY’);

select ‘grant select on a5dhadm.’||table_name||’ to a5query;’ from dba_tables where owner=‘A5DHADM’ and table_name not in (select table_name from dba_synonyms where owner=‘A5QUERY’);
select ‘create synonym a5query.’||table_name||’ for a5dhadm.‘||table_name||’;’ from dba_tables where owner=‘A5DHADM’ and table_name not in (select table_name from dba_synonyms where owner=‘A5QUERY’);

select ‘grant select on dqtdsde.’||table_name||’ to dqtdread;’ from dba_tables where owner=‘DQTDSDE’ and (table_name like ‘FC_%’ or table_name like ‘TD_%’)and table_name not in (select table_name from dba_synonyms where owner=‘DQTDREAD’);
select ‘create synonym dqtdread.’||table_name||’ for dqtdsde.‘||table_name||’;’ from dba_tables where owner=‘DQTDSDE’ and (table_name like ‘FC_%’ or table_name like ‘TD_%’)and table_name not in (select table_name from dba_synonyms where owner=‘DQTDREAD’);

A5井下作业只读用户权限补充
select ‘grant select on ‘||owner||’.’||table_name||’ to dh_query;’ from dba_tables where owner=‘A5DHADM’;
select ‘create synonym DH_QUERY.’||table_name||’ for A5DHADM.‘||table_name||’;’ from dba_tables where owner=‘A5DHADM’ and table_name not in (select table_name from dba_synonyms where owner=‘DH_QUERY’);

手动刷新物化视图：
list指定物化视图名称，method为刷新方式

BEGIN
DBMS_MVIEW.REFRESH ( list => ‘SN_DJSB_CX’, Method =>‘COMPLETE’, refresh_after_errors => True);
end;
/

获取sql执行计划

锁表
SELECT /*+ RULE */
S.USERNAME,
DECODE(L.TYPE, ‘TM’, ‘TABLE LOCK’, ‘TX’, ‘ROW LOCK’, NULL) LOCK_LEVEL,
O.OWNER,
O.OBJECT_NAME,
O.OBJECT_TYPE,
S.SID,
S.SERIAL#,
S.INST_ID,
S.TERMINAL,
S.MACHINE,
S.PROGRAM,
S.OSUSER,
C.PIECE,
C.SQL_TEXT V_SQL
FROM GV $SESS I ONS, G V$ LOCK L, DBA_OBJECTS O, GV$SQLTEXT C
WHERE L.SID = S.SID
AND L.ID1 = O.OBJECT_ID(+)
AND S.USERNAME IS NOT NULL
AND S.SQL_ADDRESS = C.ADDRESS(+)
AND O.OBJECT_NAME = ‘CYGC_SCRSJ’ --AND O.OBJECT_TYPE=‘TABLE’ --AND S.MACHINE<>‘rq114’
ORDER BY S.MACHINE, S.SID, S.SERIAL#, C.PIECE;

数据库全备脚本
#!/bin/bash
export NLS_DATE_FORMAT=‘YYYY-MM-DD HH24:MI:SS’
PATH= $P A T H :$ HOME/bin
export PATH
export ORACLE_SID=qhsde
ORACLE_BASE=/u01
ORACLE_HOME=/u01/product/12.1.0.2/dbhome_1
export PATH= $ORACLE_HOME/bin:$ PATH:/usr/expect/bin
rman target / nocatalog log /data/backup/rman_qhsde_full.log append<<EOF
crosscheck archivelog all;
delete noprompt expired archivelog all;
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup as compressed backupset database format ‘/data/backup/full_%d_%T_%s_%p’;
backup current controlfile format ‘/data/backup/ctl_%d_%T_%s_%p’;
backup as compressed backupset archivelog all format ‘/data/backup/arch_%d_%T_%s_%p’;
}
report obsolete;
delete noprompt obsolete;
crosscheck backupset;
delete noprompt expired backupset;
delete noprompt archivelog all completed before ‘sysdate-2’;
EOF

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 31 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

01 00 * * * /home/oracle/backup/rman_full_qhsde.sh >> /home/oracle/backup/rman_full_qhsde.sh.log 2>&1

常规表空间使用率查询

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss' ) from dual ;
prompt ##########TABLESPACE
set linesize 120
set pagesize 10000
col TABLESPACE format a20
            SELECT A.TABLESPACE,A.total,A.total-b.free,b.free,ROUND((b.free/A.total)*100,2)
        FROM ( SELECT tablespace_name TABLESPACE,SUM(bytes/1024/1024) total
                FROM dba_data_files 
                WHERE tablespace_name NOT IN ('UNDOTBS1','UNDOTBS2')
                and   tablespace_name not like 'TEMP%' --ADD BY LJ
              GROUP BY tablespace_name ) A,
             ( SELECT tablespace_name TABLESPACE,ROUND(SUM(bytes/1024/1024)) free
                FROM dba_free_space
              GROUP BY tablespace_name ) B
       WHERE A.TABLESPACE = B.TABLESPACE(+)
      -- AND ROUND((b.free/A.total)*100) <11
       ORDER BY 5 asc;

临时表空间使用率查询

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(%)", 
    SPACE - USED_SPACE "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,
    ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE 
   FROM V$SORT_USAGE 
  GROUP BY TABLESPACE) F
   WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)
  order by "USED_RATE(%)" desc;

会话信息查询

select STATUS,count(*) from gv$session group by STATUS;

失效对象

SELECT owner, object_name, object_type
  FROM dba_objects
 WHERE status = 'INVALID'
   and owner not in ('SYS', 'SYSTEM')

等待事件信息查询

select
   ash.event,
   sum(ash.wait_time +
   ash.time_waited) ttl_wait_time
from
   v$active_session_history ash
where
   ash.sample_time between sysdate - 60/1440 and sysdate
group by
   ash.event
order by 2;

事务top cpu sql

select * from 
(select sql_text, 
round(cpu_time/1000000) cpu_time, 
round(elapsed_time/1000000) elapsed_time, 
disk_reads, 
buffer_gets, 
rows_processed 
from v$sqlarea 
order by cpu_time desc, disk_reads desc
) 
where rownum < 10;
 exit ;

查询job信息：

SELECT JOB,LOG_USER, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN FROM DBA_JOBS;

最后搜集统计信息的时间

select OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED from dba_tables where TABLE_NAME='APTJRNTRAN';
select OWNER,INDEX_NAME,NUM_ROWS,LAST_ANALYZED,pct_free,status from dba_indexes where INDEX_NAME='PK_APTJRNTRAN';

表，索引大小

select segment_name, bytes/1024/1024
from dba_segments
where segment_type = 'TABLE'
and segment_name='APTJRNTRAN'
/

select segment_name, bytes/1024/1024
from dba_segments
where segment_type = 'INDEX'
and segment_name='PK_APTJRNTRAN'
/

表和索引的元数据

set long 99999
select dbms_metadata.get_ddl('INDEX','PK_APTJRNTRAN','OWNER') from dual;

set long 99999
select dbms_metadata.get_ddl('TABLE','APTJRNTRAN','OWNER') from dual;

查看表的统计信息（包含时间间隔）

col  owner for a20
col PARTITION_NAME for a25
col  SUBPARTITION_NAME for a25
col STATS_UPDATE_TIME for a40
col  INTERVAL for a40
select owner,
      table_name,
      partition_name,
      subpartition_name,
      stats_update_time,
      stats_update_time - lag(stats_update_time, 1, null) over(partition by owner, table_name order by stats_update_time) interval
 from DBA_TAB_STATS_HISTORY
where owner = '&user_name'
  and table_name = '&table_name'
order by owner, table_name, stats_update_time desc;

软茸兔

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
2
评论
Oracle常用sql语句记录持续更新

常规表空间使用率查询select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss' ) from dual ;prompt ##########TABLESPACEset linesize 120set pagesize 10000col TABLESPACE format a20 SELECT A.TABLESPACE,A.tota...
复制链接

扫一扫