Oracle Manage Tablespace

tablespace.sql

-- REF: http://docs.oracle.com/cd/B12037_01/server.101/b10739/undo.htm

-- SELECT * FROM DBA_DATA_FILES;

SELECT A.TABLESPACE_NAME
     , A.FILE_NAME
     , A.BYTES / (1024 * 1024) AS ALLOCATED_MB
     , B.FREE_BYTES / (1024 * 1024) AS FREE_MB
  FROM DBA_DATA_FILES A
     , (
       SELECT FILE_ID
            , SUM(BYTES) FREE_BYTES
         FROM DBA_FREE_SPACE B
        GROUP BY FILE_ID
       ) B
 WHERE A.FILE_ID = B.FILE_ID
 ORDER BY 3 DESC
;

--------------------------------------------------------------------------------

SHO PARAMETER UNDO;
SHOW PARAMETER UNDO_TABLESPACE;

CREATE UNDO TABLESPACE UNDO2
       DATAFILE 'D:\ORACLEXE\ORADATA\XE\UNDO2.DBF' SIZE 2M REUSE AUTOEXTEND ON;
       
ALTER SYSTEM SET UNDO_TABLESPACE = 'UNDO2';
DROP TABLESPACE UNDO1 INCLUDING CONTENTS AND DATAFILES;
ALTER SYSTEM SET UNDO_RETENTION = 2400;
ALTER DATABASE DATAFILE 'D:\ORACLEXE\ORADATA\XE\UNDO2.DBF' RESIZE 500M;
--------------------------------------------------------------------------------

SELECT TABLESPACE_NAME
     , SEGMENT_NAME
     , SEGMENT_TYPE AS TYPE
     , BYTES / (1024 * 1024) AS MB
  FROM DBA_SEGMENTS
 WHERE OWNER = 'BAY'
 ORDER BY 1 DESC, 4 DESC;

--------------------------------------------------------------------------------

SELECT TABLESPACE_NAME
     , SUM(SIZE_MB) AS SIZE_MB
  FROM (
        SELECT OWNER
             , TABLESPACE_NAME
             , COUNT(*) NUM_TABLES
             , SUM(BYTES) / (1024 * 1024) AS SIZE_MB
          FROM DBA_SEGMENTS
        --WHERE TABLESPACE_NAME = 'USERS'
         GROUP BY OWNER
                , TABLESPACE_NAME
         ORDER BY 1, 4 DESC
        )
  GROUP BY TABLESPACE_NAME
;

--------------------------------------------------------------------------------