oracle表空间查询维护命令大全之三(临时表空间

页面导航:首页 > 数据库 > oracle > oracle表空间查询维护命令大全之三(临时表空间

oracle表空间查询维护命令大全之三(临时表空间

来源: 作者: 时间:2016-02-16 09:27 【

--UNDO表空间汇总--查看所有的表空间名字SELECT NAME FROM V$TABLESPACE;--创建新的UNDO表空间,并设置自动扩展参数; CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE D: ORACLE PRODUCT 10 1 0 O
--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;

Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<