- 杀会话，必须要时刻背上来： select 'alter system kill session ''' || sid|| ',' || serial# || ''' immediate;' from gv$session where username = 'username' and status = 'ACTIVE';
查杀指定的等待事件： select 'alter system kill session ''' || sid|| ',' || serial# || ''' immediate;' from v$session where event = 'latch: row cache objects'; select 'alter system kill session ''' || sid|| ',' || serial# || ''' immediate;' from gv$session where event = 'latch: row cache objects' and inst_id = 2;
查杀指定的sql： select 'alter system kill session ''' || sid|| ',' || serial# || ''' immediate;' from gv$session where sql_id = 'dnwqt3wq75993';
alter system kill session '568,12353' immediate;
- 查看表空间： SELECT total.tablespace_name, Round(total.MB, 2) AS Total_MB, Round(total.MB - free.MB, 2) AS Used_MB, Round(( 1 - free.MB / total.MB ) * 100, 2) || '%' AS Used_Pct FROM (SELECT tablespace_name, Sum(bytes) / 1024 / 1024 AS MB FROM dba_free_space GROUP BY tablespace_name) free, (SELECT tablespace_name, Sum(bytes) / 1024 / 1024 AS MB FROM dba_data_files GROUP BY tablespace_name) total WHERE free.tablespace_name = total.tablespace_name;
3.收集统计信息： BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'username', TABNAME => 't_name', DEGREE => 128, CASCADE => TRUE, FORCE => TRUE);
END;
5.dataguard类：
alter database recover managed standby database using current logfile disconnect;
alter database recover managed standby database disconnect from session;
alter database recover managed standby database cancel;
配置：
alter system set LOG_ARCHIVE_DEST_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=pri';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=std LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) compression=enable DB_UNIQUE_NAME=std';
alter system set log_archive_config='dg_config=(pri,std)';
备库查看没有应用的日志：
select l.SEQUENCE#,l.THREAD#,l.name,l.APPLIED from gv$archived_log l where l.NAME is not null and applied = 'NO' order by l.SEQUENCE# desc ；
select * from v$archive_gap;
select * from v$managed_standby where process = 'MRP0';
select * from v$dataguard_stats;
orapwd file=orapsid password=password#123 entries=10 force=y ignorecase=y
rman target sys/password@primarydb auxiliary sys/password@standbydb
run {
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate channel ch3 type disk;
allocate channel ch4 type disk;
allocate auxiliary channel ch5 type disk;
allocate auxiliary channel ch6 type disk;
allocate auxiliary channel ch7 type disk;
allocate auxiliary channel ch8 type disk;
duplicate target database for standby from active database;
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
release channel ch5;
release channel ch6;
release channel ch7;
release channel ch8;
}
6.查看lob大小：
select s.TABLE_NAME,d.segment_name,s.TABLESPACE_NAME,sum(trunc(d.BYTES/1024/1024/1024)) from user_segments d,user_lobs s where d.segment_name = s.SEGMENT_NAME
group by d.segment_name,s.TABLE_NAME,s.TABLESPACE_NAME order by 4 desc；
7.根据sid找找spid，进行操作系统层面kill -9 spid:
select a.spid,b.sid,b.serial#,b.username from v$process a,v$session b where a.addr=b.paddr and b.status='KILLED'; select b.spid,a.osuser,b.program from v$session a,v$process b where a.paddr=b.addr and a.sid=4901
8.select * from nls_database_parameters; export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
9.后续不断更新中。。。。。。。。。。。。。。。
