oracle常用查询语句

1. 杀会话，必须要时刻背上来：
   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;

2. 查看表空间：
   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 ch2 type disk;
allocate channel ch3 type disk;
allocate channel ch4 type disk;
allocate channel ch5 type disk;
allocate auxiliary channel ch6 type disk;
allocate auxiliary channel ch7 type disk;
allocate auxiliary channel ch7 type disk;
allocate auxiliary channel ch8 type disk;
duplicate target database for standby from active database;
release channel ch2;
release channel ch3;
release channel ch4;
release channel ch5;
release channel ch6;
release channel ch7;
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.后续不断更新中。。。。。。。。。。。。。。。