1,表空间汇总

--创建表空间的脚本

CREATE TABLESPACE TEST_FILE

LOGGING

DATAFILE '/U02/APP/ORACLE/ORADATA/ITSDB/TEST_FILE.DBF'

SIZE 10M  

AUTOEXTEND ON  

NEXT 10M MAXSIZE 10240M  

EXTENT MANAGEMENT LOCAL;



--查看用户的默认表空间

SELECT T.USERNAME,T.DEFAULT_TABLESPACE FROM DBA_USERS  T



--改动用户的默认表空间TABLESPACE_B

ALTER   USER   USER_A   DEFAULT   TABLESPACE   TABLESPACE_B



--用户在表空间上放开限制

ALTER   USER   USER_A   QUOTA   UNLIMITED   ON   TABLESPACE_B;  



--回收权限

REVOKE   UNLIMITED   TABLESPACE   ON   TABLESPACE_A   FROM   USER_A



--以使USER_A帐户不能在TABLESPACE_A上创建不论什么对象。

ALTER   USER   USER_A   QUOTA   0   ON   TABLESPACE_A





--移动表的表空间

ALTER TABLE CQRM.CQ_FLIGHTS_SEATS_SEQUENCE MOVE TABLESPACE CQRM;COMMIT;



--批量移动表空间的语句

SELECT 'ALTER TABLE '||TABLE_NAME||' MOVE TABLESPACE CQRM;COMMIT;' FROM USER_TABLES WHERE TABLESPACE_NAME='TEST';



--在表空间上重建索引

SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD TABLESPACE CQRM;COMMIT;' FROM DBA_INDEXES WHERE  TABLE_NAME IN ('')

AND OWNER='TEST'





SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MOVE TABLESPACE TEST;COMMIT;' FROM DBA_TABLES WHERE TABLESPACE_NAME='TEST1' AND OWNER='TEST'



2,改变表空间状态



1.使表空间脱机



ALTER TABLESPACE GAME OFFLINE;



假设是意外删除了数据文件，则必须带有RECOVER选项



ALTER TABLESPACE GAME OFFLINE FOR RECOVER;



2.使表空间联机



ALTER TABLESPACE GAME ONLINE;



3.使数据文件脱机



ALTER DATABASE DATAFILE 3 OFFLINE;



4.使数据文件联机



ALTER DATABASE DATAFILE 3 ONLINE;



5.使表空间仅仅读



ALTER TABLESPACE GAME READ ONLY;



6.使表空间可读写



ALTER TABLESPACE GAME READ WRITE;



删除表空间



DROP TABLESPACE DATA01 INCLUDING CONTENTS AND DATAFILES;



扩展表空间



3,首先查看表空间的名字和所属文件



SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME,

ROUND(BYTES/(1024*1024),0) TOTAL_SPACE

FROM DBA_DATA_FILES

ORDER BY TABLESPACE_NAME;



1.添加数据文件

ALTER TABLESPACE GAME

ADD DATAFILE '/ORACLE/ORADATA/DB/GAME02.DBF' SIZE 1000M;



2.手动添加数据文件尺寸

ALTER DATABASE DATAFILE '/ORACLE/ORADATA/DB/GAME.DBF'

RESIZE 4000M;



3.设定数据文件自己主动扩展

ALTER DATABASE DATAFILE '/ORACLE/ORADATA/DB/GAME.DBF'

AUTOEXTEND ON NEXT 100M

MAXSIZE 10000M;

4.更改数据文件的位置

ALTER TABLESPACE APP_DATA RENAME DATAFILE  

'' TO ''





--整合表空间的碎片

ALTER TABLESPACE TABLESPACENAME COALESCE

此语句是整合表空间的碎片添加表空间的连续性，可是他不会收缩一个文件的大小的。

设定后查看表空间信息



SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,

(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"

FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;



--查看表空间使用状况



SELECT UPPER(F.TABLESPACE_NAME) "表空间名",

       D.TOT_GROOTTE_MB "表空间大小(G)",

       D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(G)",

       CASE

         WHEN D.TOT_GROOTTE_MB = 0 THEN

          0

         ELSE

          TO_NUMBER(TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) /

                                  D.TOT_GROOTTE_MB * 100,

                                  2),

                            '990.99'))

       END "使用比",

       F.TOTAL_BYTES "空暇空间(G)",

       F.MAX_BYTES "最大块(G)"

  FROM (SELECT TABLESPACE_NAME,

               ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 2) TOTAL_BYTES,

               ROUND(MAX(BYTES) / (1024 * 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 * 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 4 DESC

--也能够从视图DBA_TABLESPACE_USAGE_METRICS  查询假设表空间不是自己主动扩展则两者查询结果同样，假设是自己主动扩展的话则两者有偏差，以上面的查询为准





--查看用户使用了那些表空间

SELECT OWNER, OBJECT_TYPE, TABLESPACE_NAME

  FROM (SELECT DISTINCT 'TABLE' OBJECT_TYPE, OWNER, TABLESPACE_NAME

          FROM DBA_TABLES

        UNION

        SELECT DISTINCT 'INDEX' OBJECT_TYPE, OWNER, TABLESPACE_NAME

          FROM DBA_INDEXES)

 WHERE TABLESPACE_NAME IS NOT NULL

   AND OWNER = 'EZOFFICE'

 ORDER BY 1, 2, 3;

 

 

 SELECT T.SEGMENT_NAME, T.TABLESPACE_NAME, BYTES / 1024 / 1024

  FROM DBA_SEGMENTS T

 WHERE T.SEGMENT_NAME IN

       ('')

   AND OWNER = 'USER'



--能够查看回收的数据文件大小

SELECT 'ALTER DATABASE DATAFILE ''' || A.FILE_NAME || ''' RESIZE ' ||

       ROUND(A.FILESIZE - (A.FILESIZE - C.HWMSIZE - 100) * 0.8) || 'M;',

       A.FILESIZE || 'M' AS "数据文件的总大小",

       C.HWMSIZE || 'M' AS "数据文件的有用大小"

  FROM (SELECT FILE_ID, FILE_NAME, ROUND(BYTES / 1024 / 1024) AS FILESIZE

          FROM DBA_DATA_FILES) A,

       (SELECT FILE_ID, ROUND(MAX(BLOCK_ID) * 8 / 1024) AS HWMSIZE

          FROM DBA_EXTENTS

         GROUP BY FILE_ID) C

 WHERE A.FILE_ID = C.FILE_ID

AND A.FILESIZE - C.HWMSIZE > 100;

下一篇会继续讲述uodo表空间