个默认的表空间的参数. 可以通过查看视图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;
|