Oracle使用集锦

常用经典SQL

• 查看表空间名称及空间使用率

select ts.tablespace_name AS tablespace,
       ts.contents AS TYPE,
       round(size_info.megs_used/1024/1024) AS "used_size(MB)",
      round(  size_info.max/1024/1024) AS "total_size(MB)",
       -- size_info.megs_free AS free_bytes,
     round((max - size_info.megs_used)/1024/1024) AS "free_size(MB)",
       round((megs_used / Max) * 100)||'%' AS used_pct,
       DECODE(ts.status, 'ONLINE', 1, 'OFFLINE', 2, 'READ ONLY', 3, 0) AS status
  From (select a.tablespace_name,
               round(a.bytes_alloc) megs_alloc,
               round(nvl(b.bytes_free, 0)) megs_free,
               round((a.bytes_alloc - nvl(b.bytes_free, 0))) megs_used,
               round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_Free,
               100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) Pct_used,
               round(maxbytes) Max
          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 ts.name tablespace_name,
                       sum(fs.blocks) * ts.blocksize bytes_free
                  from DBA_LMT_FREE_SPACE fs, sys.ts$ ts
                 where ts.ts# = fs.tablespace_id
                 group by ts.name, ts.blocksize) b
         where a.tablespace_name = b.tablespace_name(+)
        union all
        select h.tablespace_name,
               round(sum(h.bytes_free + h.bytes_used)) megs_alloc,
               round(sum((h.bytes_free + h.bytes_used) -
                         nvl(p.bytes_used, 0))) megs_free,
               round(sum(nvl(p.bytes_used, 0))) megs_used,
               round((sum((h.bytes_free + h.bytes_used) -
                          nvl(p.bytes_used, 0)) /
                     sum(h.byt