Oracle常用脚本

数据库表空间使用情况查询

select a.file_id "FileNo",
       a.tablespace_name "Tablespace_name",
       a.bytes "Bytes",
       a.bytes - sum(nvl(b.bytes, 0)) "Used",
       sum(nvl(b.bytes, 0)) "Free",
       round(sum(nvl(b.bytes, 0)) / a.bytes * 100, 2) "%free"
  from dba_data_files a, dba_free_space b
 where a.file_id = b.file_id(+)
 group by a.tablespace_name, a.file_id, a.bytes
 order by a.tablespace_name, "%free" asc;

Oracle版本查询

select *
  from nls_database_parameters
 where parameter in ('NLS_RDBMS_VERSION');

优化器模式查询

select a.NAME,a.VALUE
  from v$parameter a
 where lower(a.name) like '%optimizer_mode%';

检查数据库表实例内存分配情况

-- db_cache_size = 0未设置高速缓存，查 sga_max_size 大小
select name, value
  from v$parameter
 where lower(name) in ('shared_pool_size',
                       'db_cache_size',
                       'large_pool_size',
                       'java_pool_size',
                       'sga_target',
                       'sga_max_size');

检查数据库的processes等参数设置

select a.NAME, a.VALUE
  from v$parameter a
 where lower(a.name) in ('sessions', 'open_cursors', 'processes');

检查数据库cursor_sharing参数

select a.NAME, a.VALUE
  from v$parameter a
 where lower(a.name) in ('cursor_sharing');

检查数据库remote_dependencies_mode参数

select a.NAME, a.VALUE
  from v$parameter a
 where lower(a.name) in ('remote_dependencies_mode');