Oracle 查询表空间状态

查询表空间状态

---

SELECT     /*+ first_rows */
      d  .tablespace_name,
         ROUND (NVL (a.bytes/1024/1024, 0)) AS "total(mb)",
         ROUND (DECODE (d.contents,
                    'UNDO', NVL (u.bytes/a.bytes * 100, 0),
                    NVL ( (a.bytes - NVL (f.bytes, 0))/a.bytes * 100, 0)))
            AS "used_rate",
         a.autoext AS "auto_extend",
         d.status "status",
         a.COUNT AS "files",
         d.contents AS "type",
         d.extent_management AS "extent_mgr",
         d.segment_space_management AS "segment_mgr"         /*, d.encrypted*/
  FROM   sys.dba_tablespaces d,
         (  SELECT   tablespace_name,
                     SUM (bytes) bytes,
                     COUNT (file_id) COUNT,
                     DECODE (SUM (DECODE (autoextensible, 'NO', 0, 1)),
                             0, 'NO',
                             'YES')
                        autoext
              FROM   dba_data_files
          GROUP BY   tablespace_name) a,
         (  SELECT   tablespace_name, SUM (bytes) bytes
              FROM   dba_free_space
          GROUP BY   tablespace_name) f,
         (  SELECT   tablespace_name, SUM (bytes) bytes
              FROM   (  SELECT   tablespace_name, SUM (bytes) bytes, status
                          FROM   dba_undo_extents
                         WHERE   status = 'ACTIVE'
                      GROUP BY   tablespace_name, status
                      UNION ALL
                        SELECT   tablespace_name, SUM (bytes) bytes, status
                          FROM   dba_undo_extents
                         WHERE   status = 'UNEXPIRED'
                      GROUP BY   tablespace_name, status)
          GROUP BY   tablespace_name) u
 WHERE       d.tablespace_name = a.tablespace_name(+)
         AND d.tablespace_name = f.tablespace_name(+)
         AND d.tablespace_name = u.tablespace_name(+)
         AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')
UNION ALL
SELECT   d.tablespace_name,
         NVL (a.bytes/1024/1024, 0),
         ROUND (NVL (t.bytes/a.bytes * 100, 0)),
         a.autoext,
         d.status,
         a.COUNT,
         d.contents,
         d.extent_management,
         d.segment_space_management                          /*, d.encrypted*/
  FROM   sys.dba_tablespaces d,
         (  SELECT   tablespace_name,
                     SUM (bytes) bytes,
                     COUNT (file_id) COUNT,
                     DECODE (SUM (DECODE (autoextensible, 'NO', 0, 1)),
                             0, 'NO',
                             'YES')
                        autoext
              FROM   dba_temp_files
          GROUP BY   tablespace_name) a,
         (  SELECT   ss.tablespace_name,
                     SUM ( (ss.used_blocks * ts.blocksize)) bytes
              FROM   gv$sort_segment ss, sys.ts$ ts
             WHERE   ss.tablespace_name = ts.name
          GROUP BY   ss.tablespace_name) t
 WHERE       d.tablespace_name = a.tablespace_name(+)
         AND d.tablespace_name = t.tablespace_name(+)
         AND d.extent_management = 'LOCAL'
         AND d.contents = 'TEMPORARY'

添加表空间

---

select * from dba_data_files d where d.tablespace_name='PIAS_ASSIGNEE_DATA'
alter tablespace "PIAS_ASSIGNEE_DATA" add datafile '/home/oracle/oradata/hostmet/PIAS_ASSIGNEE_DATA002.dbf' size 2000M autoextend on next 10M;

表空间物理位置信息

---

select tablespace_name ,sum(bytes) / 1024 / 1024 as MB　from dba_data_files group by tablespace_name order by tablespace_name asc;
select tablespace_name,file_name from dba_data_files;
select * from dba_tablespaces