系統(tǒng)自帶表空間說明

SYSTEM      #系統(tǒng)表空間，是永久系統(tǒng)表空間，用于存儲SYS用戶的表、視圖、存儲過程對象。
UNDOTBS1    #存儲撤銷信息的undo表空間
SYSAUX      #輔助表空間
TEMP        #臨時表空間，用戶存儲SQL語句處理的表示索引信息
USERS       #永久表空間，存儲數(shù)據(jù)庫用戶創(chuàng)建的數(shù)據(jù)庫對象

創(chuàng)建表空間并且指定給用戶使用

--創(chuàng)建表空間
create tablespace SDA DATAFILE 'D:\WORK\ORACLE\ORADATA\ORCL\SDA.DBF' SIZE 10M;
--修改
alter database datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA.DBF' autoextend on;
--創(chuàng)建用戶
create user c##SDA identified by SDA default tablespace SDA;
--修改權(quán)限
grant dba to c##SDA;
grant connect to c##SDA;
grant resource to c##SDA;

--刪除用戶
--drop user c##SDA cascade;
--刪除表空間
--drop tablespace SDA including contents and datafiles;

查看表空間的使用情況

select a.tablespace_name,
       a.bytes / 1024 / 1024 "Sum MB",
       (a.bytes - b.bytes) / 1024 / 1024 "used MB",
       b.bytes / 1024 / 1024 "free MB",
       round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
  from (select tablespace_name, sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name, sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tablespace_name) b
 where a.tablespace_name = b.tablespace_name
 order by ((a.bytes - b.bytes) / a.bytes) desc
--考慮到自增長
SELECT UPPER(F.TABLESPACE_NAME) AS "表空間名稱",
       ROUND(D.AVAILB_BYTES, 2) AS "表空間大小(G)",
       ROUND(D.MAX_BYTES, 2) AS "最終表空間大小(G)",
       ROUND((D.AVAILB_BYTES - F.USED_BYTES), 2) AS "已使用空間(G)",
       TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) / D.AVAILB_BYTES * 100,
                     2),
               '999.99') AS "使用比",
       ROUND(F.USED_BYTES, 6) AS "空閑空間(G)",
       F.MAX_BYTES AS "最大塊(M)"
  FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024 * 1024), 6) USED_BYTES,
               ROUND(MAX(BYTES) / (1024 * 1024 * 1024), 6) MAX_BYTES
          FROM SYS.DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F,
       (SELECT DD.TABLESPACE_NAME,
               ROUND(SUM(DD.BYTES) / (1024 * 1024 * 1024), 6) AVAILB_BYTES,
               ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES)) /
                     (1024 * 1024 * 1024),
                     6) MAX_BYTES
          FROM SYS.DBA_DATA_FILES DD
         GROUP BY DD.TABLESPACE_NAME) D
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
 ORDER BY 4 DESC;
--簡單sql
SELECT A.TABLESPACE_NAME AS TABLESPACE_NAME,
       ROUND(A.BYTES / (1024 * 1024 * 1024), 2) AS "TOTAL(G)",
       ROUND(B.BYTES / (1024 * 1024 * 1024), 2) AS "USED(G)",
       ROUND(C.BYTES / (1024 * 1024 * 1024), 2) AS "FREE(G)",
       ROUND((B.BYTES * 100) / A.BYTES, 2) AS "% USED",
       ROUND((C.BYTES * 100) / A.BYTES, 2) AS "% 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;

查看表空間對應(yīng)數(shù)據(jù)文件的相關(guān)信息

select file_name,
       tablespace_name,
       bytes / 1024 / 1024 "bytes MB",
       maxbytes / 1024 / 1024 "maxbytes MB",
       autoextensible,
       increment_by
  from dba_data_files
 where tablespace_name = 'SDA';

查詢創(chuàng)建表空間的相關(guān)SQL

select dbms_metadata.get_ddl('TABLESPACE', 'SDA') from dual;

返回一個clob字段，參考信息如下：

  CREATE TABLESPACE "SDA" DATAFILE 
  'D:\WORK\ORACLE\ORADATA\ORCL\SDA.DBF' SIZE 10485760
  AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT 
 NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO
   ALTER DATABASE DATAFILE 
  'D:\WORK\ORACLE\ORADATA\ORCL\SDA.DBF' RESIZE 17571184640

在磁盤空間充足的情況下增加數(shù)據(jù)文件

--增加數(shù)據(jù)文件
alter tablespace SDA add 
datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA_001.DBF' 
size 10M;
--增加數(shù)據(jù)文件，并且自增長
alter tablespace SDA add 
datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA_001.DBF' 
size 10M autoextend on next 5M maxsize 1G;
--使原來的數(shù)據(jù)文件自增長
alter DATABASE
datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA_001.DBF' 
autoextend on next 5M maxsize 1G;
--調(diào)整原來數(shù)據(jù)文件的大小
alter DATABASE
datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA_001.DBF' 
RESIZE 1G;

注意：ORACLE支持的數(shù)據(jù)文件大小是由它的db_block_size和db_block的數(shù)量決定的。
     其中db_block(ORACLE塊)的數(shù)量是一個定值2**22-1(4194303).
     數(shù)據(jù)文件大小容量=塊數(shù)量*塊大小

驗證是否正確增加

select file_name,
       tablespace_name,
       bytes / 1024 / 1024 "bytes MB",
       maxbytes / 1024 / 1024 "maxbytes MB",
       autoextensible,
       increment_by
  from dba_data_files
 where tablespace_name = 'SDA';

刪除表空間的數(shù)據(jù)文件

alter tablespace SDA drop datafile 'D:\WORK\ORACLE\ORADATA\ORCL\SDA_001.DBF' 

數(shù)據(jù)文件遷移

windows

linux