Oracle常操作（表空间相关）

—查看表空间所在位置
select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.TS#=t2.TS#;

—修改表空间大小
1、新建表空间文件：
alter tablespace [表空间名] add datafile ‘/home/oralce/mydb/user02.dbf’ size 1024M;
2、直接修改大小：
alter database datafile ‘/home/oralce/mydb/user01.dbf’ resize 3000M;

—查询表空间使用情况
SELECT Upper(F.TABLESPACE_NAME)         “表空间名”,
       D.TOT_GROOTTE_MB                 “表空间大小(M)”,
       D.TOT_GROOTTE_MB – F.TOTAL_BYTES “已使用空间(M)”,
       To_char(Round(( D.TOT_GROOTTE_MB – F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), ‘990.99’)
       || ‘%’                           “使用比”,
       F.TOTAL_BYTES                    “空闲空间(M)”,
       F.MAX_BYTES                      “最大块(M)”
FROM   (SELECT TABLESPACE_NAME,
               Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
               Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
        FROM   SYS.DBA_FREE_SPACE
        GROUP  BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
        FROM   SYS.DBA_DATA_FILES DD
        GROUP  BY DD.TABLESPACE_NAME) D
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER  BY 1

—查询表空间的空闲空间
select tablespace_name, count(*) AS extends,round(sum(bytes) / 1024 / 1024, 2) AS MB,sum(blocks) AS blocks from dba_free_space group BY tablespace_name;

—查询表空间的总大小
select tablespace_name, sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name;

—查询表空间是否自动增长
SELECT FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE FROM DBA_DATA_FILES

—查询索引信息
select table_name,index_name,status from user_indexes;