查询表空间状态
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
