表空间管理的相关资料

--查看用户表占用率

SELECT   owner, tablespace_name, ROUND (SUM (BYTES) / 1024 / 1024, 2) "USED(M)"
    FROM dba_segments
GROUP BY owner, tablespace_name
ORDER BY SUM (BYTES) DESC;

 

--表空间占有率
Select D.TABLESPACE_NAME, SPACE "SUM_SPACE(M)", BLOCKS SUM_BLOCKS, SPACE -
        NVL(FREE_SPACE,
            0) "USED_SPACE(M)", ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100,
              2) "USED_RATE(%)", FREE_SPACE "FREE_SPACE(M)"
  From (Select TABLESPACE_NAME, ROUND(Sum(BYTES) / (1024 * 1024), 2) SPACE, Sum(BLOCKS) BLOCKS
           From DBA_DATA_FILES
          Group By TABLESPACE_NAME) D, (Select TABLESPACE_NAME, ROUND(Sum(BYTES) /
                       (1024 * 1024),
                       2) FREE_SPACE
           From DBA_FREE_SPACE
          Group By TABLESPACE_NAME) F
 Where D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
Union All --if have tempfile  
Select D.TABLESPACE_NAME, SPACE "SUM_SPACE(M)", BLOCKS SUM_BLOCKS, USED_SPACE "USED_SPACE(M)", ROUND(NVL(USED_SPACE,
                  0) /
              SPACE * 100,
              2) "USED_RATE(%)", NVL(FREE_SPACE, 0) "FREE_SPACE(M)"
  From (Select TABLESPACE_NAME, ROUND(Sum(BYTES) / (1024 * 1024), 2) SPACE, Sum(BLOCKS) BLOCKS
           From DBA_TEMP_FILES
          Group By TABLESPACE_NAME) D, (Select TABLESPACE_NAME, ROUND(Sum(BYTES_USED) /
                       (1024 * 1024),
                       2) USED_SPACE, ROUND(Sum(BYTES_FREE) /
                       (1024 * 1024),
                       2) FREE_SPACE
           From V$TEMP_SPACE_HEADER
          Group By TABLESPACE_NAME) F
 Where D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);

 

--查看信息
SELECT TABLESPACE_NAME,BYTES/1024/1024 MB,FILE_NAME FROM DBA_DATA_FILES order by 1,3;

 

--删除用户脚本

drop user scott cascade;

 

--删除表空间  
--首先要删除该表空间上的所有用户
alter tablespace pis offline;
DROP TABLESPACE pis INCLUDING CONTENTS AND DATAFILES;

 

--根据前面所列出的表空间名和对应的数据文件，到内对应路径查看，该文件是否已经被删除，如果没有被删除，通过系统删除！

 

-- 表空间
--创建表空间

--查看数据文件信息
SELECT TABLESPACE_NAME,BYTES/1024/1024 MB,FILE_NAME FROM DBA_DATA_FILES order by 1,3;

--创建表空间
create tablespace elarp datafile '/u02/oradata/ora10g/elarp.dbf' SIZE 3000M autoextend off;
--调整表空间数据文件大小
alter database datafile '/u02/oradata/ora10g/elarp.dbf' resize 300M;
--取消数据文件自动扩展
alter database datafile '/u02/oradata/ora10g/users01.dbf' autoextend OFF;
--增加表空间数据文件
alter tablespace USERS add datafile '/u02/oradata/ora10g/users02.dbf' SIZE 20480m autoextend off;
alter tablespace USERS add datafile '/u02/oradata/ora10g/users03.dbf' SIZE 20480m autoextend off;