oracle資料表空間查詢維護命令大全之三(暫存資料表空間)史上最全，oracle命令大全

oracle資料表空間查詢維護命令大全之三(暫存資料表空間)史上最全，oracle命令大全
--UNDO資料表空間匯總
--查看所有的資料表空間名字
SELECT NAME FROM V$TABLESPACE;
--建立新的UNDO資料表空間，並設定自動擴充參數;
　CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS02.DBF' SIZE 10M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
--注意：在OPEN狀態下某些時刻只能用一個UNDO資料表空間，如果要用建立的資料表空間，必須切換到該資料表空間:
ALTER SYSTEM SET UNDO_TABLESPACE = UNDO2;
--修改為自動管理
ALTER SYSTEM SET UNDO_MANAGEMENT = AUTO SCOPE = SPFILE;

修改UNDO的資料表空間管理方式為MANUAL ALTER SYSTEM SET UNDO_MANAGEMENT = MANUAL SCOPE = SPFILE;
--修改

--等待原UNDO資料表空間所有UNDO SEGMENT OFFLINE;
SELECT USN,
       XACTS,
       STATUS,
       RSSIZE / 1024 / 1024 / 1024,
       HWMSIZE / 1024 / 1024 / 1024,
       SHRINKS
  FROM V$ROLLSTAT
 ORDER BY RSSIZE;
--刪除資料表空間
DROP TABLESPACE UNDO1 INCLUDING CONTENTS AND DATAFILES;
--更改UODO資料表空間的大小
ALTER DATABASE DATAFILE '/U2/ORADATA/SPRING/UNDOTBS01.DBF' RESIZE 1024M;

SELECT TABLESPACE_NAME, SEGMENT_TYPE, OWNER, SEGMENT_NAME
  FROM DBA_EXTENTS
 WHERE FILE_ID = 8
   AND 565129 BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;

1, 暫存資料表空間的主要作用： 索引CREATE或REBUILD； ORDER BY 或 GROUP BY； DISTINCT 操作； UNION 或 INTERSECT 或 MINUS； SORT - MERGE JOINS； ANALYZE.
  SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;
查看預設暫存資料表空間
  SELECT *
    FROM DATABASE_PROPERTIES
   WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';
--建立暫存資料表空間

CREATE TEMPORARY TABLESPACE TEMP_DATA TEMPFILE '/ORACLE/ORADATA/DB/TEMP_DATA.DBF' SIZE 50M

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/U02/ORADATA/ORCL/ORCL/TEMP01.DBF' SIZE 6144M, '/U02/ORADATA/ORCL/ORCL/TEMP02.DBF' SIZE 6144M;

--修改暫存資料表空間的大小
ALTER DATABASE TEMPFILE '/U2/ORADATA/SPRING/TEMP_DATA.DBF' RESIZE 1024M;

CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '/U02/ORADATA/ORCL/ORCL/TEMP101.DBF' SIZE 4056M;

--修改資料庫的預設暫存資料表空間
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

--刪除暫存資料表空間
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

--清理暫存資料表空間
ALTER TABLESPACE TEMP SHRINK SPACE KEEP 20M;
--自動將資料表空間的臨時檔案縮小到最小可能的大小
ALTER TABLESPACE TEMP SHRINK TEMPFILE ’ / U02 / ORACLE / DATA / LMTEMP02.DBF’;

2, 暫存資料表空間過大，重新暫存資料表空間的具體步驟匯總：

1.建立中轉暫存資料表空間 CREATETEMPORARYTABLESPACETEMP1 TEMPFILE '/ORACLE/ORADATA/SECOOLER/TEMP02.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

2.改變預設暫存資料表空間為剛剛建立的新暫存資料表空間TEMP1 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;
驗證使用者的暫存資料表空間為TEMP1

  SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;

3.刪除原暫存資料表空間 DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

4.重建暫存資料表空間 CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/ORACLE/ORADATA/SECOOLER/TEMP01.DBF' SIZE 512M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

5.重設預設暫存資料表空間為建立的TEMP資料表空間 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
驗證使用者的暫存資料表空間為TEMP

  SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;

3、查看誰在用暫存資料表空間
  SELECT SE.USERNAME,
         SE.SID,
         　　SE.SERIAL#,
         　　SE.SQL_ADDRESS,
         　　SE.MACHINE,
         　　SE.PROGRAM,
         　　SU.TABLESPACE,
         　SU.SEGTYPE,
         　　SU.CONTENTS 　FROM V$SESSION SE,
         　　V$SORT_USAGE SU 　　WHERE SE.SADDR = SU.SESSION_ADDR;
4、查看暫存資料表空間TEMP空閑情況
  SELECT TABLESPACE_NAME,
         FILE_ID,
         BYTES_USED / 1024 / 1024,
         BYTES_FREE / 1024 / 1024
    FROM V$TEMP_SPACE_HEADER;

5, 具體到某個SID暫存資料表空間使用方式
  SELECT B.TABLESPACE,
         B.SEGFILE#,
         B.SEGBLK#,
         B.BLOCKS,
         B.BLOCKS * 32 / 1024 / 1024,
         A.SID,
         A.SERIAL#,
         A.USERNAME,
         A.OSUSER,
         A.STATUS,
         C.SQL_TEXT,
         B.CONTENTS
    FROM V$SESSION A, V$SORT_USAGE B, V$SQL C
   WHERE A.SADDR = B.SESSION_ADDR
     AND A.SQL_ADDRESS = C.ADDRESS(+)
   ORDER BY B.BLOCKS                  DESC
            
                             在建立使用者時,
            有一個預設的資料表空間的參數. 可以通過查看視圖DATABASE_PROPERTIES可以看到相應的資訊.
            
             SELECT A.PROPERTY_NAME, A.PROPERTY_VALUE
               FROM DATABASE_PROPERTIES A
              WHERE A.PROPERTY_NAME LIKE '%DEFAULT%';


6, /*查看暫存資料表空間總體使用方式*/
  SELECT TMP_TBS.TABLESPACE_NAME,
         SUM(TMP_TBS.TOTAL_MB) TOTAL_MB,
         SUM(USED_TOT.USED_MB) USED_MB,
         SUM(USED_TOT.USED_MB) / SUM(TMP_TBS.TOTAL_MB) * 100 USED_PERSENT
    FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_MB
            FROM DBA_TEMP_FILES
           GROUP BY TABLESPACE_NAME) TMP_TBS,
         (SELECT TMP_USED.TABLESPACE,
                 SUM(TMP_USED.BLOCKS * PARA.DB_BLOCK_SIZE) / 1024 / 1024 USED_MB
            FROM V$SORT_USAGE TMP_USED,
                 (SELECT VALUE DB_BLOCK_SIZE
                    FROM V$PARAMETER
                   WHERE NAME = 'DB_BLOCK_SIZE') PARA
           GROUP BY TMP_USED.TABLESPACE) USED_TOT
   WHERE TMP_TBS.TABLESPACE_NAME = USED_TOT.TABLESPACE(+)
   GROUP BY TMP_TBS.TABLESPACE_NAME;

7, /*查看暫存資料表空間中排序段和資料區段的使用方式*/
  SELECT TMP_TBS.TABLESPACE_NAME,
         USED_TOT.SEGTYPE TEMP_SEG_TYPE,
         SUM(TMP_TBS.TOTAL_MB) TOTAL_MB,
         SUM(USED_TOT.USED_MB) USED_MB,
         SUM(USED_TOT.USED_MB) / SUM(TMP_TBS.TOTAL_MB) * 100 USED_PERSENT
    FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 TOTAL_MB
            FROM DBA_TEMP_FILES
           GROUP BY TABLESPACE_NAME) TMP_TBS,
         (SELECT TMP_USED.TABLESPACE,
                 TMP_USED.SEGTYPE,
                 SUM(TMP_USED.BLOCKS * PARA.DB_BLOCK_SIZE) / 1024 / 1024 USED_MB
            FROM V$SORT_USAGE TMP_USED,
                 (SELECT VALUE DB_BLOCK_SIZE
                    FROM V$PARAMETER
                   WHERE NAME = 'DB_BLOCK_SIZE') PARA
           GROUP BY TMP_USED.TABLESPACE, TMP_USED.SEGTYPE) USED_TOT
   WHERE TMP_TBS.TABLESPACE_NAME = USED_TOT.TABLESPACE(+)
   GROUP BY TMP_TBS.TABLESPACE_NAME, USED_TOT.SEGTYPE;


怎為ORACLE資料表空間或暫存資料表空間增加資料檔案？

背景：當通過ORACLE中的create table ... as select 語句建立一張新表時，新表的資料量為比較大，如10億，這時SQL*Plus很可能就會提示“ORA-01653: ...”錯誤資訊。這個錯誤資訊暗示資料表空間大小不夠，需要為資料表空間增加資料檔案。分析：1. 查詢資料表空間剩餘位元組大小SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS FREE SPACE(M)FROM DBA_FREE_SPACEWHERE TABLESPACE_NAME = '&tablespace_name'GROUP BY TABLESPACE_NAME;註：如果是暫存資料表空間，請查詢DBA_TEMP_FREE_SPACESELECT TABLESPACE_NAME, FREE_SPACE/1024/1024 AS FREE SPACE(M)FROM DBA_TEMP_FREE_SPACEWHERE TABLESPACE_NAME = '&tablespace_name';2. 如果不知道資料表空間資料檔案目錄規劃，可以先查詢出資料表空間所有資料檔案SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS BYTES(M)FROM DBA_DATA_FILESWHERE TABLESPACE_NAME = '&tablespace_name';註：如果是暫存資料表空間，請查詢DBA_TEMP_FILESSELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS SPACE(M)FROM DBA_TEMP_FILESWHERE TABLESPACE_NAME = '&tablespace_name';3. 為空白間不足的資料表空間增加資料檔案ALTER TABLESPACE &tablespace_name ADD DATAFILE '&datafile_name' SIZE 2G;註：如果要為暫存資料表空間擴容，使用下面的語句
 
Oracle的UNDO資料表空間與暫存資料表空間

首先肯定不是一個概念，UNDO資料表空間用於存放UNDO資料,當執行DML操作時,oracle會將這些操作的舊資料寫入到UNDO段,以保證可以復原或者一致讀等，而暫存資料表空間主要用來做查詢和存放一些緩衝區資料。你聽說UNDO也是暫存資料表可能是因為這兩個資料表空間都不會永久儲存資料的原因