-----数据库慢需要查看活跃会话等待事件：
select sql_id,event,count(*) from v$session where username is not null and status='ACTIVE' group by sql_id,event order by 3;
-----连接数状态：
select inst_id,status,count(*) from gv$session where type<>'BACKGROUND' group by inst_id,status order by 1,3;
-----查看全表扫：
set linesize 200 pagesize 40
column program format a16
column machine format a15
column object_name format a20
column object_owner format a15
select s.inst_id,s.sid,s.program,s.machine,s.sql_id,p.object_name,p.object_owner,p.cost from gv$session s, gv$sql_plan p where s.inst_id=p.inst_id and s.sql_id=p.sql_id and p.operation = 'TABLE ACCESS' and p.options = 'FULL' and p.object_owner<>'SYS' and p.cost>100000 order by p.cost, s.inst_id,s.sid;
-----查看某个语句的sid：
select sid,serial# from v$session where sql_id in (select sql_id from v$sqlarea where sql_text like 'select count(*) as col_0_0_ from T_CC_BILLMAINDATA tccbillmai0_, T_CC_BILL tccbill1_%');
-----批量杀某个语句：
select 'alter system kill session '''||sid||','||serial#||'''; 'from v$session where sql_id in (select sql_id from v$sqlarea where sql_text like 'select count(*) as col_0_0_ from T_CC_BILLMAINDATA tccbillmai0_, T_CC_BILL tccbill1_%')
----批量杀操作系统进程：
select 'kill -9 ' || spid from v$process where addr in (select paddr from v$session where sql_id='xxxxxx' );
----批量在系统里杀阻塞;
select 'kill -9 '||p.spid from v$session s, v$process p where type<>'BACKGROUND' and s.paddr=p.addr and s.sid in (select FINAL_BLOCKING_session from v$session );
1、查看数据文件信息：G col file_name for a55 select tablespace_name,file_name,bytes/1024/1024/1024 gb,AUTOEXTENSIBLE from dba_data_files where tablespace_name='&1';
2、查看ASM2磁盘组信息： select group_number,name,total_mb/1024 total_gb,free_mb/1024 free_gb,TYPE from v$asm_diskgroup;
3、查看ASM磁盘组磁盘的信息 col PATH for a33 col NAME for a15 col FAILGROUP for a15 select GROUP_NUMBER,DISK_NUMBER,TOTAL_MB/1024,FREE_MB/1024,NAME,FAILGROUP,PATH,FAILGROUP_TYPE from v$asm_disk where GROUP_NUMBER='2';
3、查看表空间大小：
SELECT a.tablespace_name,round(total/1024/1024) "avail",round(free/1024/1024) "free",ROUND((total-free)/total,4)*100 "percent" 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 WHERE a.tablespace_name=b.tablespace_name ORDER BY 4;
查看归档生成量：
select trunc(FIRST_TIME,'dd'),sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024 from v$archived_log group by trunc(FIRST_TIME,'dd') order by 1;
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++++++++++++++++++++
①查看数据库sga，pga的使用情况：
select name,total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused from (select 'SGA' name,(select sum(value/1024/1024) from v$sga) total, (select sum(bytes/1024/1024) from v$sgastat where name='free memory')free from dual) union select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2)pctused from ( select 'PGA' name,(select value/1024/1024 total from v$pgastat where name='aggregate PGA target parameter')total, (select value/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual);
②查看shared_pool使用情况：
select name,round(total,2) total,round((total-free),2) used,round(free,2) free,round((total-free)/total*100,2) pctused from ( select 'Shared pool' name,(select sum(bytes/1024/1024) from v$sgastat where pool='shared pool')total, (select bytes/1024/1024 from v$sgastat where name='free memory' and pool='shared pool') free from dual)
③查看shared_pool中大于10m的sql： SELECT substr(sql_text,1,100) "Stmt",count(*), sum(sharable_mem) "Mem",sum(users_opening) "Open",sum(executions) "Exec" FROM v$sql GROUP BY substr(sql_text,1,100) HAVING sum(sharable_mem)>10000000;
④查看占用pga的进程： set lines 200 col name for a30 Select Server, Osuser, Name, Value / 1024 / 1024 Mb, s.Sql_Id, Spid, s.sid From V$session s, V$sesstat St, V$statname Sn, V$process p Where St.Sid = s.Sid And St.Statistic# = Sn.Statistic# And Sn.Name Like 'session pga memory' And p.Addr = s.Paddr Order By Value Desc;
⑤根据spid查看对应的sql语句： select sql_text from v$sqlarea where (address, hash_value) in (select /*+unnest*/DECODE(sql_hash_value, 0, prev_sql_addr, sql_address),DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value) from v$session where sid = (select sid from v$session where paddr = (select ADDR from v$process where SPID = '22675464'))); +++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++++++
-----批量授权：
select 'grant select on PICCTJ02_NEW.' || tname || ' to b;' from tab; select 'GRANT SELECT ON PICCHB02_NEW.'||table_name||' to JXHB_NEW_QRY;' from dba_tables where OWNER='PICCHB02_NEW';
4、统计活动的undo select sum(bytes /(1024*1024*1024)) from dba_undo_extents where status='ACTIVE';
5、查看大于20M的文件 find / -type f -size +20M -print0 | xargs -0 du -h | sort -nr
6、查看shared_pool的大小 select sum(bytes)/1024/1024/1024 from v$sgastat where pool='shared pool'; 查看空闲的： select * from v$sgastat where name = 'free memory' and pool = 'shared pool';
7、查看占用内存100k的sql语句： select sql_text ,sharable_mem from v$sql where sharable_mem > '100000' order by sharable_mem
8、查看字符集 select userenv('language') from dual; select * from nls_database_parameters;
9、Oracle查询temp表空间的名字和位置 select tablespace_name,file_name from dba_temp_files; col FILE_NAME for a55 select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024/1024 total_gb,USER_BYTES/1021/1024/1024 gb from dba_temp_files;
Oracle查询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; 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_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);
查看回收站大小：
select sum(SPACE)*8/1024 M from dba_recyclebin;
10、查看补丁版本
set line 150
col ACTION_TIME for a30
col ACTION for a8
col NAMESPACE for a8
col VERSION for a10
col BUNDLE_SERIES for a5
col COMMENTS for a20
select * from dba_registry_history;
查看数据库备份情况：
col START_TIME for a30
col END_TIME for a30
col status for a10
select SESSION_KEY,INPUT_TYPE,STATUS,to_char(START_TIME, 'yyyy-mm-dd hh24:mi') START_TIME,to_char(END_TIME, 'yyyy-mm-dd hh24:mi') END_TIME,ELAPSED_SECONDS / 3600 from v$rman_backup_job_details where to_char(START_TIME, 'yyyy-mm-dd hh24:mi') > to_char(sysdate-12,'yyyy-mm-dd hh24:mi') order by SESSION_KEY;
11、查看补丁版本：
ZB23NXYD2:/app/product/11.2.0/db/OPatch$opatch lsinventory
12、查看锁表
SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username, l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id AND l.session_id = s.sid ORDER BY sid, s.serial# ;
select SID,SERIAL#,USERNAME,PROGRAM,MACHINE,SQL_ID,PREV_SQL_ID,last_call_et,STATUS from v$session where SID in (select SESSION_ID from v$locked_object);
查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句：
SELECT l.session_id sid,s.serial#,l.locked_mode,l.oracle_username,s.user#, l.os_user_name,s.machine,s.terminal,a.sql_text,a.action FROM v$sqlarea a,v$session s,v$locked_object l WHERE l.session_id = s.sid AND s.prev_sql_addr = a.address ORDER BY sid, s.serial#; a.object_name
查看视图对应的表：
select * from dba_dependencies where NAME='视图名' and TYPE='VIEW';
=======根据操作系统进程spid查看数据库sid及serial#：
SELECT s.username,s.sid,s.serial# FROM gv$session s,gv$process p WHERE s.paddr=p.addr AND p.spid='7733928';
=======查看数据库进程的状态（比如kill的时候last_call_et可观察时间）：
select inst_id,status,last_call_et from gv$session where sid=4268;
13、杀锁命令 alter system kill session '2176,167'
批量杀进程： select 'alter system kill session '''||sid||','||serial#||'''; 'from v$session where SQL_ID='drdph1c2z2c4s';
15、查看表大小 select TABLESPACE_NAME,OWNER,SEGMENT_NAME,sum(BYTES)/1024/1024 total_mb from dba_segments where TABLESPACE_NAME='CARDW02' group by TABLESPACE_NAME,OWNER,SEGMENT_NAME;
16、查看兼容版本（grid的） select name,compatibility,database_compatibility from v$asm_diskgroup;
查看集群版本： crsctl query crs softwareversion
17、查看aix操作系统的资源情况
prtconf|more HP：machinfo WIN：msinfo32 SUSE：cat /proc/cpuinfo （model name ） # 查看物理CPU个数
cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l # 查看每个物理CPU中core的个数(即核数)
cat /proc/cpuinfo| grep "cpu cores"| uniq # 查看逻辑CPU的个数
cat /proc/cpuinfo| grep "processor"| wc -l #查看CPU信息（型号）
cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
---查看资源使用情况： HP：glance/top AIX：nmon/topas
---查看内存大小： HP： /usr/contrib/bin/machinfo | grep -i Memory AIX: /usr/sbin/lsattr -E -l sys0 -a realmem
---查看swap分区： HP：/usr/sbin/swapinfo -a AIX：/usr/sbin/lsps -s
18、新建用户 alter user mcms_rb account unlock identified by &PASSWORD; 19、查看数据量： select sum(bytes)/1024/1024 mb from dba_segments; 查询数据库历史数据量 数据库一天的数据增量 select sum(TABLESPACE_USEDSIZE)*8/1024/1024,substr(rtime,0,10) from DBA_HIST_TBSPC_SPACE_USAGE where snap_id in (select ma:@snap_id) from DBA_HIST_TBSPC_SPACE_USAGE group by substr(rtime,0,10) ) --and TABLESPACE_ID=8 group by substr(rtime,0,10) order by 1 ;
20、查看REDOLOG大小 select group#,members,bytes/1024/1024,status from v$log;
21、清理垃圾文件 cd &DIR find ./ -ctime +3 |xargs rm
22、ASM磁盘 --- 检查磁盘大小（单位M） bootinfo -s hdisk0 --- 查看磁盘的详细信息 lsattr -El hdisk0 --- 检查权限 ls -l /dev/hdisk* 【排序查看ls -ltr /dev |grep rhdisk】 --- 检查PVID lspv | grep hdiskn --- 检查保留策略 lsattr -E -l hdisk5 | grep reserve_policy --- 查看磁盘是否为共享磁盘 lsattr -El hdisk0 比对两个主机对应的磁盘号是否一致：unique_id --- 查看磁盘是否可用 lspv 看PVID是否为none，若为none则数据库可用，再查看数据库当前有没有使用，若没有则可用来扩容ASM磁盘组
23、按用户查看占用多少内存 svmon -U grid -w |more svmon -U oracle -w | more
24、查看用户下有多少进程 svmon -PO unit=GB |grep aioserver |wc svmon -PO unit=GB |grep oracle |wc select round(BYTES/1024/1024,2)||'M' from user_segments where segment_name='TUSER_SYNC_CHESHANG'; select segment_name,tablespace_name,bytes B, bytes/1024 KB, bytes/1024/1024 MB from user_segments where segment_type='TABLE' and tablespace_name='USERS'
lsvg |lsvg -i -p lsdev -c disk
@?/rdbms/admin/awrrpt.sql
==============expdp========== 查看导出相关目录权限：
select privilege,directory_name,directory_path from user_tab_privs t,all_directories d where t.table_name(+)=d.directory_name order by 2,1;
select * from dba_directories;
======================
1、修改LINUX操作系统/dev/shm文件系统大小 开机自启动： tmpfs /dev/shm tmpfs defaults,size=20G 0 0
立即生效： mount -t tmpfs shmfs -o size=20g /dev/shm
25、查看服务器底层用的存储类型： lscfg -vpl hdisk40
26、生成AWR报告 @?/rdbms/admin/awrrpt.sql
27、查看ASM磁盘挂载时间： set lines 500 pages 2000 col g_name format a10 col g_n format 99 col d_n format 999 col m_status format a7 col mo_status format a7 col h_status format a11 col name format a20 col path format a20 col failgroup format a15 select g.group_number g_n, g.disk_number d_n, g.name name, g.failgroup, g.mount_status m_status, g.header_status h_status, g.mode_status mo_status, g.path , to_char(g.mount_date, 'YYYY/MM/DD HH24:MI:SS') m_date from v$asm_disk g order by g_n, d_n
28、查看用户权限： select privilege from role_sys_privs where role in ( select granted_role from dba_role_privs where grantee='ZYKBTSDATA' union all select privilege from dba_sys_privs where grantee='ZYKBTSDATA';
23、修改磁盘盘号（将hdisk2改为hdisk10）： rendev -l hdisk2 -n hdisk10 查看进程： set pages 9999 set lines 200 select process,client_process,sequence#,thread#,status from v$managed_standby;
24、查看FTP服务是否开启
----AIX操作系统： $
lssrc -s inetd （查看守护进程是否开启） $
lssrc -t ftp （查看服务是否开启）
AIX查看虚拟内存使用率高的情况：
svmon -U -t 10 -O summary=basic -i 60|more
===================新加命令============
1、查看undo表空间的真实利用情况：
select tablespace_name,status,round(sum(bytes) / 1048576, 2) size_mb,count(*) extent_count from dba_undo_extents group by tablespace_name, status order by tablespace_name, status;
2、表空间无法自动扩展：ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM，查看限制的最大值maxbytes ： select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 gb,MAXBYTES/1024/1024/1024 maxgb from dba_data_files where tablespace_name='SYSTEM';
set linesize 200
set pagesize 200
column file_name format a50
column total_MB format 999999999;
column used_MB format 999999999;
column free_MB format 999999999;
select c.TABLESPACE_NAME,c.SEGMENT_SPACE_MANAGEMENT,d.total_GB,d.used_GB,d.free_GB,d.use_precent "Userd_%",c.EXTENT_MANAGEMENT from dba_tablespaces c,
(select b.tablespace_name,round(sum(b.bytes)/1024/1024/1024,2) total_GB,round((sum(b.bytes)-sum(nvl(a.bytes,0)))/1024/1024/1024,2) used_GB,round(sum(nvl(a.bytes,0))/1024/1024/1024,2) free_GB,round((sum(b.bytes)-sum(nvl(a.bytes,0)))/sum(b.bytes),4)*100 use_precent from (select tablespace_name,file_id,sum(bytes) bytes from dba_free_space group by tablespace_name,file_id ) a, dba_data_files b where a.file_id(+)=b.file_id and a.tablespace_name(+)=b.tablespace_name group by b.tablespace_name order by use_precent) d
where c.TABLESPACE_NAME=d.TABLESPACE_NAME
order by 6;
=========杀锁表：
ps -ef |grep LOCAL=NO |awk '{print $2}'|xargs kill -9
+++++++++++++++++++ AIX修改主句名：
chdev -l inet0 -a hostname=&new_hostname hostname &new_hostname
重启操作系统：nohup shutdown -Fr & ++++++++++++++++++++++ ============================================
--查询数据库负载
set pages 9999
set lines 200
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
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;
--查两个节点连接数
select INST_ID,count(*) from gv$session group by inst_id;
--查看历史达到的最大连接数（MAX_UTILIZATION为自数据库上次启动以来达到的最大值，LIMIT_VALUE代表设置的最大值）
select resource_name,MAX_UTILIZATION,LIMIT_VALUE from v$resource_limit where resource_name in ('processes','sessions');
--查当前的等待事件
col wait_class for a20
set lines 200 pages 200
col event for a60
select event,count(*),wait_class from v$session_wait group by event,wait_class order by 3;
--查看被锁的表
col OBJECT_NAME for a20 col OWNER for a20 set line 300 select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;
--查看连接的进程
SELECT sid, serial#, username, osuser FROM v$session;
--查看那个用户那个进程照成死锁
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
select ' alter system kill session '''||b.sid||','||b.serial#||''' immediate;' from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine, s.terminal, s.logon_time, l.type FROM v$session s, v$lock l WHERE s.sid = l.sid AND s.username IS NOT NULL ORDER BY sid;
查看数据库表中的列字段有没有BLOB：
select distinct ('TABLE "' || a.OWNER || '"."' || a.TABLE_NAME || '"') from sys.all_tab_columns a where a.OWNER = 'xxxxxx'--用户名 and a.TABLE_NAME in (select t.TABLE_NAME from sys.all_tab_columns t where t.OWNER = 'xxxxxx'--用户名 and t.DATA_TYPE in ( 'BLOB'))
查看表空间碎片率：
select a.tablespace_name,sqrt(max(a.blocks)/sum(a.blocks))*(100/sqrt(sqrt(count(a.blocks)))) FSFI from dba_free_space a,dba_tablespaces b where a.tablespace_name=b.tablespace_name and b.contents not in ('SYSTEM','UNDOTBS2','USERS','SYSAUX','UNDOTBS1') group by a.tablespace_name order by 2;
alter session set current_schema=Schema
这个命令可以用sysdba用然后想切到哪个用户就切到哪个用户 不需要密码就能切
========== 查看10949event是否设置：
SQL> oradebug setmypid oradebug eventdump session; 10949 trace name context forever, level 1
如果有值说明设置了 当前正在执行的sql，以及cpu消耗
set echo off feedback off timing off pause off
set pages 100 lines 180 trimspool on trimout on space 1 recsep off
col username format a13
col prog format a10 trunc
col sql_text format a40 trunc
col sid format a12
col sql_id format a16
col child for 99999
col execs format 9999999
col sqlprofile format a22
col avg_ela for 999999.99
col last_ela for 999999
col event format a15
col EXECUTIONS for 99999999
col cpu_time for 9999999999999
select /*+ rule */ sid||','||serial# sid, substr(a.event,1,15) event, b.sql_id||','||child_number sql_id, b.plan_hash_value, b.executions execs, (b.elapsed_time/decode(nvl(b.executions,0),0,1,b.executions))/1000000 avg_ela, last_call_et last_ela, b.sql_text, c.cpu_time, c.EXECUTIONS
from v$session a, v$sql b,v$sqlarea c
where status = 'ACTIVE'
and username is not null
and a.sql_id = b.sql_id
and a.sql_id = c.sql_id
and a.sql_child_number = b.child_number
and b.sql_text not like '%from v$session a, v$sql b%'
and a.program not like '%(P%)'
order by b.plan_hash_value,last_call_et,sql_id, sql_child_number;
col name for a20
select group_number,name,total_mb/1024 total_gb,free_mb/1024 free_gb,TYPE from v$asm_diskgroup;
---------ADG----------------
--1.查询主备的同步情况
set linesize 150;
set pagesize 20;
column name format a13;
column value format a20;
column unit format a30;
column TIME_COMPUTED format a30;
select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');
col name for a80
set lines 200
select name,first_time from v$archived_log where name is not null and name!='dbstd' order by first_time;
备库查询
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; select THREAD#,count(FIRST_TIME) from v$archived_log where APPLIED='NO' group by THREAD#; select THREAD#,min(FIRST_TIME) from v$archived_log where APPLIED='NO' group by THREAD#; select THREAD#,min(SEQUENCE#) from v$archived_log where APPLIED='NO' group by THREAD#; select BACKUP_COUNT from v$archived_log where THREAD#=&1 and SEQUENCE#=&2;
--2.查询备库的进程状态
SELECT PROCESS, STATUS,SEQUENCE#,thread# FROM V$MANAGED_STANDBY;
--3.查询备库的角色
set linesize 150;
column DBNAME format a6;
column DBUNAME format a10;
column cftype format a8;
column OPEN_MODE format a20;
column DATABASE_ROLE format a18;
select name dbname,db_unique_name dbuname,controlfile_type cftype,database_role,open_mode from v$database;
--4.查询备库的日志应用模式
SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=1;
RECOVERY_MODE --------------------------------------------------------------------- MANAGED REAL TIME APPLYs
--5.开启日志应用进程：
--应用stanby 实时同步
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
并行应用日志：
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE using current logfile DISCONNECT FROM SESSION parallel 8;
--6.取消日志应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
--7. 查看ADG日志差：
SELECT /*+rule*/ ARCH.THREAD# "Thread",
ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM (SELECT THREAD#, SEQUENCE#
FROM V$ARCHIVED_LOG
WHERE (THREAD#, FIRST_CHANGE#) IN
(SELECT THREAD#, MAX(FIRST_CHANGE#)
FROM V$ARCHIVED_LOG
GROUP BY THREAD#)
GROUP BY THREAD#, SEQUENCE#) ARCH,
(SELECT THREAD#, SEQUENCE#
FROM V$LOG_HISTORY
WHERE (THREAD#, FIRST_CHANGE#) IN
(SELECT THREAD#, MAX(FIRST_CHANGE#)
FROM V$LOG_HISTORY
GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
col "hidden parameter" for a30
col value for a20
select ksppinm as "hidden parameter", ksppstvl as "value" from x$ksppi join x$ksppcv using (indx) where ksppinm like '\_%' escape '\' and ksppinm like '%asm_hb%' order by ksppinm;
'
select TABLE_NAME,TABLESPACE_NAME from dba_tables where TABLESPACE_NAME=' 大写的表空间名';
select A.Segment_Name, A.size_total || 'M'
from (Select Segment_Name, Sum(bytes) / 1024 / 1024 size_total ,tablespace_name
From User_Extents
Group By Segment_Name) A
order by size_total desc;
select A.Segment_Name, A.size_total || 'M',tablespace_name from (select A.Segment_Name, A.size_total || 'M'
from (Select Segment_Name, Sum(bytes) / 1024 / 1024 size_total ,tablespace_name
From User_Extents
Group By Segment_Name) A
order by size_total desc),(select TABLESPACE_NAME from dba_tables where TABLESPACE_NAME='IDX_SPACE') where A.Segment_Name='CRM_ITF_CUSTOMER_VALUES';
select aa.Segment_Name, aa.size_total || 'M',tablespace_name from (select A.Segment_Name, A.size_total || 'M'
from (Select Segment_Name, Sum(bytes) / 1024 / 1024 size_total ,tablespace_name
From User_Extents
Group By Segment_Name) A
order by size_total desc) aa,(select TABLESPACE_NAME from dba_tables where TABLESPACE_NAME='IDX_SPACE') where aa.Segment_Name='CRM_ITF_CUSTOMER_VALUES'; set linesize 200
col owner format a20
col table_name format a30
SELECT es.OWNER ,es.TABLE_NAME ,round(s.bytes / 1024 / 1024, 0) "allocate_size(M)" ,round(es."size" / 1024 / 1024, 0) AS "Estemated_Size(M)" ,round(((s.bytes - es."size") / 1024 / 1024), 0) AS "Waste_Size(M)" ,round(1 - es."size" / s.bytes, 2) AS "Wasted_percent"
FROM ( SELECT OWNER ,TABLE_NAME ,AVG_ROW_LEN * NUM_ROWS*1.15 AS "size" FROM dba_tables WHERE NUM_ROWS > 0 AND COMPRESSION != 'ENABLED' ) es ,( SELECT OWNER ,segment_name ,sum(bytes) bytes FROM dba_segments GROUP BY OWNER ,segment_name ) s
WHERE s.OWNER = es.OWNER AND s.segment_name = es.table_name AND round(1 - es."size" / s.bytes, 2) > 0.2 AND round(((s.bytes - es."size") / 1024 / 1024 / 1024), 0) > 20
ORDER BY s.bytes - es."size" DESC;
表空间增长
col name for a50
set linesize 400 pagesize 400
select a.name, b.*
from v$tablespace a,
(select tablespace_id,
trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) datetime,
round(max(tablespace_usedsize * 8 / 1024),2) used_size_MB
from dba_hist_tbspc_space_usage
where trunc(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss')) >
trunc(sysdate - 10)
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 and a.name='&tbs';
添加裸设备至磁盘组 查询是否扫盘
ls -lrt /dev/mapper/asm*
查看磁盘大小（root）
lvmdiskscan
set lines 300
col PATH for a30
col name for a11
select HEADER_STATUS,GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,Name name,OS_MB,PATH from v$asm_disk; （以上命令查看asm磁盘组信息，所属为空的则是新添加的磁盘
然后根据提供的路径查看权限是否正确，若不对及时与设备组进行联系
）
alter diskgroup DATA add disk '/dev/dm-*','/dev/dm-*';
alter diskgroup DATA REBALANCE POWER 10;
（进行添加组信息，DATA_COG, 此磁盘名称需要和上条命令对应进行查看，路径也需要和对应的信息进行查看
第二条命令是让数据平衡分配，负载均衡）
select MOUNT_STATUS,HEADER_STATUS,STATE,REDUNDANCY,NAME,PATH from v$asm_disk_stat; set lines 300 pages 1000 col ERROR_CODE for a10 select * from v$asm_operation;
select OWNER,SEGMENT_NAME,SEGMENT_TYPE,sum(bytes/1024/1024/1024) "size(GB)"
from dba_segments where TABLESPACE_NAME='APP_PICCCPI' and SEGMENT_TYPE='TABLE' group by OWNER ,SEGMENT_NAME,SEGMENT_TYPE having sum(bytes/1024/1024/1024)>10
order by 4 desc; select owner, segment_name, segment_type, sum(bytes/1024/1024/1024) "size(GB)"
from dba_segments
where tablespace_name='USER_DATA5' and SEGMENT_TYPE='TABLE'
group by owner, segment_name, segment_type
having sum(bytes/1024/1024/1024)> 10
order by 4 desc;
sudo -l
###不能使用
!ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}' |xargs kill -9
kill -9 `ps -ef | grep LOCAL=NO | grep -v grep |cut -c9-22`
crsctl stat res -t
srvctl stop listener -n node1?
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system checkpoint;
create pfile='/home/oracle/bakpfile02.ora' from spfile;
alter database backup controlfile to trace as '/home/oracle/conctlbak20.ctl';
shutdown immediate
crsctl stat res -t
需要sudo权限
sudo crsctl stop crs
sudo crsctl stop his (has)
rman target /
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
SET NEWNAME FOR DATABASE TO '+xxxx_DATA/';
restore database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
} 查进度
select sid,
serial#,
sql_id,
context,
sofar,
totalwork,
round(sofar/totalwork*100,2) "%_complete" from v$session_longops where opname not like '%aggregate%' and totalwork !=0
and sofar<>totalwork; select GROUP_NUMBER,NAME,TYPE,STATE,round(TOTAL_MB/1024,2) TOTAL_GB,round(FREE_MB/1024,2) FREE_GB,
round(USABLE_FILE_MB/1024,2) USABLE_GB,round((TO 2 TAL_MB-nvl(FREE_MB,0))/decode(nvl(TOTAL_MB,1),0,1,nvl(TOTAL_MB,1)),4)*100 "Userd_%" from v$asm_diskgroup_stat; 归档时间段 select logtime, count(*), round(sum(blocks * block_size) / 1024 / 1024 / 1024) gbsize 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; lob大小 select distinct ab.TABLE_NAME, ab.SEGMENT_NAME, ab.TABLESPACE_NAME, ab.OWNER, sum(ac.BYTES / 1024 / 1024 / 1024) GB from dba_lobs ab, DBA_SEGMENTS ac where ab.OWNER IN (SELECT USERNAME FROM DBA_USERS A WHERE A.account_status = 'OPEN' AND A.default_tablespace NOT IN ('SYSTEM', 'USERS', 'SYSAUX')) and ab.SEGMENT_NAME = ac.segment_name and ab.OWNER = ac.owner group by ab.TABLE_NAME, ab.SEGMENT_NAME, ab.TABLESPACE_NAME, ab.OWNER order by 5 desc; 表大小
select * from (SELECT OWNER,TABLE_NAME,SUM(tabsize) table_size from ( SELECT owner,SEGMENT_NAME as table_name,SUM(BYTES)/1024/1024/1024 as tabsize FROM DBA_SEGMENTS WHERE (OWNER,SEGMENT_NAME) IN ( SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER NOT IN ('SYS','SYSTEM','DBSNMP')) GROUP BY SEGMENT_NAME,owner union ALL select lob.owner,lob.table_name,sum(seg.bytes)/1024/1024/1024 as tabsize from dba_lobs lob,dba_segments seg where lob.segment_name=seg.segment_name and lob.OWNER NOT IN ('SYS','SYSTEM','DBSNMP') group by lob.owner,lob.table_name ) GROUP BY OWNER,TABLE_NAME ORDER BY 3 DESC) where table_size>50 ; 查看当前运行sql SELECT 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;
===============================================================================================================================================
根据cpu消耗查看sql
set linesize 160 pagesize 100
col event format a50;
col
select inst,state,event,count(*)
from
(SELECT
s.inst_id AS inst,
CASE WHEN s.state = 'WAITING' THEN s.state ELSE 'CPU/RUN QUEUE' END AS state,
CASE WHEN s.state = 'WAITING' THEN s.event ELSE 'NOT IN A WAIT' END AS event
FROM gv$session s
WHERE s.status = 'ACTIVE'
and s.type<>'BACKGROUND'
)
group by inst,state,event
order by inst,count(*);
select FINAL_BLOCKING_INSTANCE,FINAL_BLOCKING_session from gv$session where event='enq: TX - row lock contention';
查出来的值如：1,1356
set trimspool on
set trimspool on
set trim on
set pages 0
set linesize 1000
set long 1000000
set longchunksize 1000000
select st.inst_id,
st.sql_id,
st.sql_text
from gv$session s,gv$sqlarea st where s.inst_id=1 and s.sid=1356 and st.inst_id=s.inst_id and st.sql_id=s.sql_id;
=========================================================================================
select count(*) from v$active_session_history where sample_time between to_date('2022-10-14 12:00','YYYY-MM-DD HH24:MI') and to_date('2022-10-14 12:11','YYYY-MM-DD HH24:MI') ;
查看时间段内连接数
==========================================================================================================
查看归档生成，小时
select logtime,
count(*),
round(sum(blocks * block_size) / 1024 / 1024 / 1024) gbsize
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;
lob大小
select distinct ab.TABLE_NAME,
ab.SEGMENT_NAME,
ab.TABLESPACE_NAME,
ab.OWNER,
sum(ac.BYTES / 1024 / 1024 / 1024) GB
from dba_lobs ab, DBA_SEGMENTS ac
where ab.OWNER IN (SELECT USERNAME
FROM DBA_USERS A
WHERE A.account_status = 'OPEN'
AND A.default_tablespace NOT IN
('SYSTEM',
'USERS',
'SYSAUX'))
and ab.SEGMENT_NAME = ac.segment_name
and ab.OWNER = ac.owner
group by ab.TABLE_NAME, ab.SEGMENT_NAME, ab.TABLESPACE_NAME, ab.OWNER
order by 5 desc;
表大小大于指定值
select * from (SELECT OWNER,TABLE_NAME,SUM(tabsize) table_size from (
SELECT owner,SEGMENT_NAME as table_name,SUM(BYTES)/1024/1024/1024 as tabsize FROM DBA_SEGMENTS WHERE (OWNER,SEGMENT_NAME) IN (
SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER NOT IN ('SYS','SYSTEM','DBSNMP'))
GROUP BY SEGMENT_NAME,owner
union ALL
select lob.owner,lob.table_name,sum(seg.bytes)/1024/1024/1024 as tabsize from dba_lobs lob,dba_segments seg
where lob.segment_name=seg.segment_name and lob.OWNER NOT IN ('SYS','SYSTEM','DBSNMP') group by lob.owner,lob.table_name
)
GROUP BY OWNER,TABLE_NAME ORDER BY 3 DESC)
where table_size>50 ;
查看当前sql和连接机器
set pagesize 111
col OSUSER for a11
col SQL for a222
col STATUS for a11
col SCHEMANAME for a22
col USERNAME for a11
col PROGRAM for a33
set line 444
SELECT 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;
查看表上的索引
select INDEX_NAME, INDEX_TYPE,TABLE_OWNER,TABLE_NAME,PARTITIONED,STATUS from dba_indexes where TABLE_name = 'xxx' order by 3,4;
指定时间内sql
select a.SQL_TEXT,a.FIRST_LOAD_TIME from v$sqlarea a
where
to_date(a.FIRST_LOAD_TIME,'YYYY-MM-DD HH24:MI:SS')>= to_date('2023-10-10 12:00:00','YYYY-MM-DD HH24:MI:SS')
and
to_date(a.FIRST_LOAD_TIME,'YYYY-MM-DD HH24:MI:SS')<= to_date('2023-10-10 16:30:00','YYYY-MM-DD HH24:MI:SS');
--------
select sql_id, session_id, session_serial#, sql_exec_id, count(*)
from dba_hist_active_sess_history
where to_char(sample_time, 'yyyymmdd hh24:mi:ss') between
'20231010 12:00:00' and '20231010 16:30:00'
and sql_id is not null
group by sql_id, session_id, session_serial#, sql_exec_id
having count(*) > 1
order by count(*);
查看lob字段超过指定大小
select * from (SELECT OWNER,TABLE_NAME,SUM(tabsize) table_size from (
SELECT owner,SEGMENT_NAME as table_name,SUM(BYTES)/1024/1024/1024 as tabsize FROM DBA_SEGMENTS WHERE (OWNER,SEGMENT_NAME) IN (
SELECT OWNER,TABLE_NAME FROM DBA_TABLES WHERE OWNER NOT IN ('SYS','SYSTEM','DBSNMP'))
GROUP BY SEGMENT_NAME,owner
union ALL
select lob.owner,lob.table_name,sum(seg.bytes)/1024/1024/1024 as tabsize from dba_lobs lob,dba_segments seg
where lob.segment_name=seg.segment_name and lob.OWNER NOT IN ('SYS','SYSTEM','DBSNMP') group by lob.owner,lob.table_name
)
GROUP BY OWNER,TABLE_NAME ORDER BY 3 DESC)
where table_size>5 ;
对活跃会话进行kill拼接语句
set heading off feedback off pagesize 0 verify off echo off
select 'alter system kill session '''||s.sid||','||s.serial#||''' immediate;'
from v\$session s
where s.status='ACTIVE'
AND s.type<>'BACKGROUND';
exit
####卸载节点
dbca -silent -deleteInstance -nodeList host2 -gdbName xxx -instanceName xxx2 -sysDBAUserName sys -sysDBAPassword xxxx560
互信
./sshUserSetup.sh -user grid -hosts "xxxx1 xxxx2" -advanced -confirm -noPromptPassphrase
