Oracle命令合集

select pool, sum(bytes) / 1024 / 1024
from v$sgastat
where pool is not null
group by pool;

2、SAG缓存池

select sum(pins) as 总Pins,
sum(pinhits）as 总Hit,
sum(reloads) as 总Reloads,
trunc(sum(reloads) / sum(pins) * 100, 10) || '%' as 丢失率, --小于1%
trunc(sum(pinhits - reloads) / sum(pins) * 100, 10) || '%' as 命中率 --大于90%
from v$librarycache;

select '归档日志大小' as 类型, percent_space_used || '%' as 数值
from v$flash_recovery_area_usage u
where u.file_type = upper('ARCHIVED LOG')
union all
select '有效的归档日志数量' as 类型, to_char(count(*)) as 数值
from v$archived_log c
where c.name is not null

2、进入服务器Rman删除归档日志（示例为删除30天前的归档日志）

su - oracle
rman target sys/pass
delete noprompt archivelog all completed before 'sysdate-30';

lsnrctl start
lsnrctl status
lsnrctl stop

startup; 
--或启动到mount 再开启数据库
startup mount; 
alter database open；

shutdown normal;
shutdown immediate;
shutdown transactional;
shutdown abort;
startup restrict; --启动到restrict模式

以下是示例
1、直接启动

SQL> startup;
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 2925568 bytes
Variable Size 905970944 bytes
Database Buffers 33554432 bytes
Redo Buffers 5459968 bytes
Database mounted.
Database opened.

2、启动到mounted，再打开数据库

SQL> startup mount;
ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size 2925568 bytes
Variable Size 905970944 bytes
Database Buffers 33554432 bytes
Redo Buffers 5459968 bytes
Database mounted.
SQL> alter database open;
Database altered.

3、关闭数据库

SQL> shutdown normal;
Database closed.
Database dismounted.
ORACLE instance shut down.

--启动整个数据库 
srvctl start database -d orcl
--启动单个实例 
srvctl start instance -d orcl -i orcl1
--关闭整个数据库 
srvctl stop database -d orcl
--关闭单个实例 
srvctl stop instance -d orcl -i orcl1

2、启动和关闭监听

--启动所有节点上的监听器
srvctl start listener -l listener
--启动特定节点上的监听器
srvctl start listener -l listener -n node1
--关闭所有节点上的监听器
srvctl stop listener -l listener
--关闭特定节点上的监听器
srvctl stop listener -l listener -n node1

3、启动所有数据库和所有数据库监听

srvctl start database -d ORCL
srvctl start listener
srvctl start scan
srvctl start scan_listener
--查看状态
srvctl status listener
su - grid
crsctl stat res -t

select 'alter system kill session ''' || b.sid || ',' || b.serial# || ' ' ||
       'immediate' || ';'
  from v$locked_object a, v$session b
 where a.session_id = b.sid
   and b.program = 'w3wp.exe'
 group by b.sid, b.serial#;

2、直接查询现在锁定的表

select * from v$locked_object

3、查询IP信息

select v.client_info
  from v$session v
 where v.sid = '3101';

4、查询杀锁

select regexp_substr(sys_context('USERENV', 'INSTANCE_NAME'), '\d+') as 实例,
       nvl(a.oracle_username, a.os_user_name) as 用户名,
       b.serial# as serial,
       b.sid as sid,
       nvl(b.client_info, a.os_user_name) as ip地址,
       a.locked_mode as 锁等级,
       decode(b.client_info,
              null,
              '',
              'alter system kill session ''' || b.sid || ',' || b.serial# ||
              ''' ' || 'immediate' || ';') as 杀锁命令
  from v$locked_object a, v$session b
 where a.session_id = b.sid
 group by a.oracle_username,
          a.os_user_name,
          a.locked_mode,
          b.serial#,
          b.sid,
          b.client_info

analyze table [TableName]compute statistics for all indexes;
analyze index [IndexName]validate structure;

2、查询索引

select ids.name,
       trunc(ids.del_lf_rows / ids.lf_rows * 100, 5) as rebuild_key, --超过20必须重建 
       ids.height, --≥4需重建 
       ids.blks_gets_per_access, --随机索引预期行数（越小越好） 
       ids.lf_rows, --叶行数（索引中的值） 
       ids.br_rows, --B树中的分支行数 
       ids.del_lf_rows, --已删除的叶行数 
       ids.distinct_keys, --索引总类数 
       ids.rows_per_key --索引平均行数
  from index_stats ids

3、重建索引 indexname替换索引名字 重建完成后需要重启PLSQL

alter index indexname rebuild;
alter index indexname rebuild online;