查看表空间
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
l
o
c
k
e
d
o
b
j
e
c
t
t
1
,
v
locked_object t1,v
lockedobjectt1,vsession 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
p
p
i
x
,
s
y
s
.
x
ksppi x, sys.x
ksppix,sys.xksppcv 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
s
e
s
s
i
o
n
W
H
E
R
E
S
I
D
=
′
session WHERE SID ='
sessionWHERESID=′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
S
E
S
S
I
O
N
S
,
G
V
SESSION S, GV
SESSIONS,GVLOCK 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
:
PATH:
PATH:HOME/bin
export PATH
export ORACLE_SID=qhsde
ORACLE_BASE=/u01
ORACLE_HOME=/u01/product/12.1.0.2/dbhome_1
export PATH=
O
R
A
C
L
E
H
O
M
E
/
b
i
n
:
ORACLE_HOME/bin:
ORACLEHOME/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;