oracle 查看 ek约束,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;