小白
-
----注意的问题:
内存设置,blocks大小,版本检查
环境变量 裸设备等 root和oracle权限。
如果是RAC集群还需要安装集群软件,IP等等 多了去了
查看表空间划分情况:
查询表空间大小和利用率代码如下:
SELECT /*+ NO_MERGE(D) NO_MERGE(A) NO_MERGE(F) NO_MERGE(U) NO_MERGE(O) */ D.TABLESPACE_NAME "表空间名", D.BLOCK_SIZE/1024 "块大小(KB)",D.INITIAL_EXTENT/1024 "初始分配大小(KB)",
ROUND(NVL(A.BYTES /1024 /1024,0) ,2) "大小(MB)",
ROUND(DECODE(D.CONTENTS, "UNDO", NVL(U.BYTES, 0) / 1024 / 1024,NVL(A.BYTES - NVL(F.BYTES, 0), 0) / 1024 / 1024) ,2) "占用量(MB)",
TO_CHAR(ROUND(DECODE(D.CONTENTS, "UNDO", NVL(U.BYTES / A.BYTES * 100, 0),NVL((A.BYTES - NVL(F.BYTES, 0)) / A.BYTES * 100, 0)) ,2),"999.99")||"%" "占用率(MB)",
ROUND(DECODE(D.CONTENTS, "UNDO", NVL(A.BYTES - NVL(U.BYTES, 0), 0) / 1024 / 1024,NVL(F.BYTES, 0) / 1024 / 1024) ,2) "空闲空间(MB)",
D.STATUS "状态",A.AUTOEXTENSIBLE "是否自动扩展",D.LOGGING "是否记录日志", A.COUNT "数据文件", D.CONTENTS "类型",
D.EXTENT_MANAGEMENT "区管理", D.SEGMENT_SPACE_MANAGEMENT "段管理"
FROM SYS.DBA_TABLESPACES D,
(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, COUNT(FILE_ID) COUNT,
CASE WHEN SUM(DECODE(AUTOEXTENSIBLE,"YES",10000,"NO",1,AUTOEXTENSIBLE))>=10000 THEN "YES" ELSE "NO" END||CASE WHEN MOD(SUM(DECODE(AUTOEXTENSIBLE,"YES",10000,"NO",1,AUTOEXTENSIBLE)),10000)>0 THEN CHR(13)||CHR(38)||CHR(13)||"NO" ELSE "" END AS AUTOEXTENSIBLE
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES
FROM DBA_UNDO_EXTENTS
WHERE STATUS IN ("UNEXPIRED", "EXPIRED")
GROUP BY TABLESPACE_NAME) U
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+) AND D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
AND D.TABLESPACE_NAME = U.TABLESPACE_NAME(+) AND NOT (D.EXTENT_MANAGEMENT = "LOCAL" AND D.CONTENTS = "TEMPORARY")
--AND D.TABLESPACE_NAME LIKE "%UNDO%"
UNION ALL
SELECT /*+ NO_MERGE(D) NO_MERGE(A) NO_MERGE(T) */ D.TABLESPACE_NAME "表空间名", D.BLOCK_SIZE/1024 "块大小(KB)",D.INITIAL_EXTENT/1024 "初始分配大小(KB)",
ROUND(NVL(A.BYTES /1024 /1024,0) ,2) "大小(MB)",
ROUND(NVL(T.BYTES, 0) / 1024 / 1024 ,2) "占用量(MB)",
TO_CHAR(ROUND(NVL(T.BYTES / A.BYTES * 100, 0) ,2),"999.99")||"%" "占用率(MB)",
ROUND((NVL(A.BYTES, 0) / 1024 / 1024 - NVL(T.BYTES, 0) / 1024 / 1024) ,2) "空闲空间(MB)",
D.STATUS "状态",A.AUTOEXTENSIBLE "是否自动扩展",D.LOGGING "是否记录日志", A.COUNT "数据文件", D.CONTENTS "类型",
D.EXTENT_MANAGEMENT "区管理", D.SEGMENT_SPACE_MANAGEMENT "段管理"
FROM SYS.DBA_TABLESPACES D,
(SELECT TABLESPACE_NAME, SUM(BYTES) BYTES, COUNT(FILE_ID) COUNT,
CASE WHEN SUM(DECODE(AUTOEXTENSIBLE,"YES",10000,"NO",1,AUTOEXTENSIBLE))>=10000 THEN "YES" ELSE "NO" END||CASE WHEN MOD(SUM(DECODE(AUTOEXTENSIBLE,"YES",10000,"NO",1,AUTOEXTENSIBLE)),10000)>0 THEN CHR(13)||CHR(38)||CHR(13)||"NO" ELSE "" END AS AUTOEXTENSIBLE
FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) A,
(SELECT SS.TABLESPACE_NAME, SUM((SS.USED_BLOCKS * TS.BLOCKSIZE)) BYTES
FROM GV$SORT_SEGMENT SS, SYS.TS$ TS
WHERE SS.TABLESPACE_NAME = TS.NAME
GROUP BY SS.TABLESPACE_NAME) T
WHERE D.TABLESPACE_NAME = A.TABLESPACE_NAME(+) AND D.TABLESPACE_NAME = T.TABLESPACE_NAME(+)
AND D.EXTENT_MANAGEMENT = "LOCAL"
AND D.CONTENTS = "TEMPORARY"
--AND D.TABLESPACE_NAME LIKE :2
ORDER BY 1 Asc,6 DESC;
出投笔记
-
应该是刚接触,要学的很多。
先去学oracle安装,在学oracle的sql,关注系统环境相关的sql。
这样相关的问题就都有了答案。
这个不是答案,是方法、路径。自个捕鱼去吧。