Oracle语句

--查找数据文件指标及路径
select b.file_id　　文件ID,
　　b.tablespace_name　　表空间,
　　b.file_name　　　　　物理文件名,
　　b.bytes　　　　　　　总字节数,
　　(b.bytes-sum(nvl(a.bytes,0)))　　　已使用,
　　sum(nvl(a.bytes,0))　　　　　　　　剩余,
　　sum(nvl(a.bytes,0))/(b.bytes)*100　剩余百分比
　　from dba_free_space a,dba_data_files b
　　where a.file_id=b.file_id
　　group by b.tablespace_name,b.file_name,b.file_id,b.bytes
　　order by b.tablespace_name

--表空间容量指标查询
SELECT TABLESPACE_NAME "表空间",
       To_char(Round(BYTES / 1024, 2), '99990.00')
       || ''           "实有",
       To_char(Round(FREE / 1024, 2), '99990.00')
       || 'G'          "现有",
       To_char(Round(( BYTES - FREE ) / 1024, 2), '99990.00')
       || 'G'          "使用",
       To_char(Round(10000 * USED / BYTES) / 100, '99990.00')
       || '%'          "比例"
FROM   (SELECT A.TABLESPACE_NAME  TABLESPACE_NAME,
            Floor(A.BYTES / ( 1024 * 1024 ))              BYTES,
            Floor(B.FREE / ( 1024 * 1024 ))               FREE,
            Floor(( A.BYTES - B.FREE ) / ( 1024 * 1024 )) USED
    FROM   (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                    Sum(BYTES)      BYTES
            FROM   DBA_DATA_FILES
            GROUP  BY TABLESPACE_NAME) A,
            (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                    Sum(BYTES)      FREE
            FROM   DBA_FREE_SPACE
            GROUP  BY TABLESPACE_NAME) B
    WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME)
--WHERE TABLESPACE_NAME LIKE 'SPACE_UCITY%' --这一句用于指定表空间名称
ORDER  BY Floor(10000 * USED / BYTES) DESC;

-----查看组的信息（总大小）
select name,total_mb, free_mb from v$asm_diskgroup;
---查看磁盘成员信息分别大小
select name,total_mb,free_mb from v$asm_disk;

----查看ASM硬盘剩余量
select name,total_mb/1024 || 'G' as total_size , free_mb/1024 || 'G' as free_size from v$asm_diskgroup;

--新增数据文件，并且允许数据文件自动增长
alter tablespace XXXXXXXXXXXXXX add datafile 
'+DATA/GISDB/DATAFILE/XXXXXXXXXXXXXX05.dbf' size 10g
autoextend on next 100M maxsize unlimited;

--查看XXXXXXXXXXXXXX表空间剩余量
select b.file_name as PhysicalFileName,b.bytes/1024/1024/1024 as TotalByte,(b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100 as UsedPercent from dba_free_space a,dba_data_files b where a.file_id=b.file_id and b.tablespace_name = 'XXXXXXXXXXXXXX' group by b.tablespace_name,b.file_name,b.file_id,b.bytes;