Oracle - 表空間相關常用操作語句

一、建立表空間

        CREATE TABLESPACE DATA_TBS 
        LOGGING 
        DATAFILE '/usr/oracle/oradata/DATA_TBS_01.DBF' 
        SIZE 1500M 
        AUTOEXTEND ON 
        NEXT 100M MAXSIZE 10000M 
        EXTENT MANAGEMENT LOCAL;

二、檢視錶空間

        -- 檢視所有使用者的表空間
        SELECT TABLESPACE_NAME, CONTENTS, STATUS  FROM DBA_TABLESPACES;

        -- 檢視錶空間的資料檔案
        SELECT TABLESPACE_NAME, FILE_NAME, AUTOEXTENSIBLE, MAXBYTES,USER_BYTES, ONLINE_STATUS FROM DBA_DATA_FILES;
        -- 檢視錶空間的臨時檔案
        SELECT TABLESPACE_NAME, FILE_NAME, AUTOEXTENSIBLE, MAXBYTES,USER_BYTES, STATUS FROM DBA_TEMP_FILES;

三、配置預設表空間

        -- 修改資料庫預設表空間(在沒有為使用者指定預設表空間時，使用者使用資料庫的預設表空間)
        ALTER DATABASE DEFAULT TABLESPACE USERS;

        -- 建立使用者時指定使用者的預設表空間(DATA_TBS是資料表空間，TEMP_TBS是臨時表空間)
        CREATE USER TETUSER IDENTIFIED BY USERPWD DEFAULT TABLESPACE DATA_TBS TEMPORARY TABLESPACE TMEP_TBS;

        -- 修改使用者預設的資料表空間
        ALTER USER TETUSER DEFAULT TABLESPACE DATA_TBS;

        -- 修改使用者預設的臨時表空間
        ALTER USER TETUSER DEFAULT TEMPORARY TABLESPACE TMEP_TBS;

四、設定表空間屬性

        -- 修改表空間名稱
        ALTER TABLESPACE DATA_TBS RENAME TO DATA_TBS2; 
        
         -- 回收表空間
        REVOKE UNLIMITED TABLESPACE FROM 表空間名;
        ALTER USER 使用者名稱 QUOTA 0 ON 表空間名;
        
        -- 設定DATA_TBS表空間的可用性為離線
        ALTER TABLESPACE DATA_TBS OFFLINE;
        -- 設定DATA_TBS表空間的可用性為聯機  
        ALTER TABLESPACE DATA_TBS ONLINE; 
        
        -- 設定DATA_TBS表空間的可讀性為只可讀
        ALTER TABLESPACE DATA_TBS READ ONLY;
        -- 設定DATA_TBS表空間的可讀性為可讀寫
        ALTER TABLESPACE DATA_TBS READ WRITE;

五、表空間擴容

        -- 手工改變已存在資料檔案的大小
        ALTER DATABASE DATAFILE '/usr/oracle/oradata/DATA_TBS_01.DBF' RESIZE 20G;

        -- 允許已存在的資料檔案自動增長
        ALTER DATABASE DATAFILE '/usr/oracle/oradata/DATA_TBS_01.DBF' AUTOEXTEND ON NEXT 10M MAXSIZE 20G;

        -- 給表空間增加資料檔案
        ALTER TABLESPACE DATA_TBS ADD DATAFILE '/usr/oracle/oradata/DATA_TBS_02.DBF' SIZE 20G;
 
        -- 新增資料檔案，並且允許資料檔案自動增長
        ALTER TABLESPACE DATA_TBS ADD DATAFILE '/usr/oracle/oradata/DATA_TBS_02.DBF' SIZE 50M AUTOEXTEND ON NEXT 10M MAXSIZE 20G;

六、刪除表空間

        -- 刪除表空間資料檔案(不能夠刪除表空間中的第一個資料檔案，否則相當於刪除了整個表空間)
        ALTER TABLESPACE TABLESPACE_NAME DROP DATAFILE'FILENAME.DBF';

        -- 刪除整個表空間
        DROP TABLESPACE TABS2 INCLUDING CONTENTS; -- 刪除表空間和段
        DROP TABLESPACE TABS2 INCLUDING CONTENTS AND DATAFILES;  -- 將刪除表空間和段以及資料檔案

七、獲取已存在的表空間的建立語句

        SELECT DBMS_METADATA.GET_DDL('TABLESPACE', 'DATA_TBS') FROM DUAL;