欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

oracle 查询表空间使用情况与查询有哪些数据库实例在运行

程序员文章站 2024-02-18 09:45:04
...

oracle 查询表空间使用情况与查询有哪些数据库实例在运行

oracle 查询表空间使用情况与查询有哪些数据库实例在运行

查询表空间使用情况

  

select a.tablespace_name "表空间名称",

  100-round((nvl(b.bytes_free,0)/a.bytes_alloc)*100,2) "占用率(%)",

  round(a.bytes_alloc/1024/1024,2) "容量(M)",

  round(nvl(b.bytes_free,0)/1024/1024,2) "空闲(M)",

  round((a.bytes_alloc-nvl(b.bytes_free,0))/1024/1024,2) "使用(M)",

  Largest "最大扩展段(M)",

  to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "采样时间"

  from (select f.tablespace_name,

  sum(f.bytes) bytes_alloc,

  sum(decode(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) maxbytes

  from dba_data_files f

  group by tablespace_name) a,

  (select f.tablespace_name,

  sum(f.bytes) bytes_free

  from dba_free_space f

  group by tablespace_name) b,

  (select round(max(ff.length)*16/1024,2) Largest,

  ts.name tablespace_name

  from sys.fet$ ff, sys.file$ tf,sys.ts$ ts

  where ts.ts#=ff.ts# and ff.file#=tf.relfile# and ts.ts#=tf.ts#

  group by ts.name, tf.blocks) c

  where a.tablespace_name = b.tablespace_name and a.tablespace_name = c.tablespace_name

  

25. 查询表空间的碎片程度

  

select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name

  having count(tablespace_name)>10;

  alter tablespace name coalesce;

  alter table name deallocate unused;

  create or replace view ts_blocks_v as

  select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space

  union all

  select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;

  select * from ts_blocks_v;

  select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space

  group by tablespace_name;

  

26。查询有哪些实例在运行

  

select inst_name from v$active_instances;

  ===========================================================

  ######### 创建数据库----look $ORACLE_HOME/rdbms/admin/buildall.sql #############

  create database db01

  maxlogfiles 10

  maxdatafiles 1024

  maxinstances 2

  logfile

  GROUP 1 ('/u01/oradata/db01/log_01_db01.rdo') SIZE 15M,

  GROUP 2 ('/u01/oradata/db01/log_02_db01.rdo') SIZE 15M,

  GROUP 3 ('/u01/oradata/db01/log_03_db01.rdo') SIZE 15M,

  datafile 'u01/oradata/db01/system_01_db01.dbf') SIZE 100M,

  undo tablespace UNDO

  datafile '/u01/oradata/db01/undo_01_db01.dbf' SIZE 40M