专家精讲Oracle数据库管理与维护

最新推荐文章于 2021-04-03 14:02:38 发布

转载最新推荐文章于 2021-04-03 14:02:38 发布 · 96 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/zouzhi/p/5191428.html

本文提供了Oracle数据库管理和监控的关键SQL命令。包括检查数据库状态、监听器状态、表空间使用情况及对象有效性。适用于DBA日常维护工作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、确认所有Oracle数据库状态

SELECT INSTANCE_NAME，STATUS FROM V$INSTANCE;

2、确认所以Oraclel监听器（Listener）状态

lsnrct status

3、开启监听器指令

lsnrctl start

4、关闭监听器指令

lsnrctl stop

5、检查表空间的使用情况

SELECT a.Tablespace_Name AS "TABLESPACE NAME", a.Total_Size AS "TOTAL SIZE",
       Round(b.Total_Free_Size, 1) AS "TOTAL FREE SIZE",
       Round((a.Total_Size - b.Total_Free_Size), 2) AS "USED SIZE",
       To_Char(100 * b.Total_Free_Size / a.Total_Size, '99.99') || '%' AS "PERCENT FREE"
  FROM (SELECT Tablespace_Name, SUM(Bytes) / 1024 / 1024 AS Total_Size
           FROM Dba_Data_Files
          GROUP BY Tablespace_Name) a,
       (SELECT Tablespace_Name, SUM(Bytes) / 1024 / 1024 AS Total_Free_Size
           FROM Dba_Free_Space
          GROUP BY Tablespace_Name) b
 WHERE a.Tablespace_Name = b.Tablespace_Name;

6、检查是否有新增的失效对象（只针对FUNCTION、PACKAGE、PACKAGE_BODY、PROCEDURE、TRIGGER和VIEW等对象显示）

SELECT Owner Object_Owner, Object_Name, Object_Type, Status
   FROM Dba_Objects
  WHERE Owner NOT IN ('SYS', 'SYSTEM')
    AND Status = 'INCALID'
  ORDER BY Owner, Object_Type, Object_Name

转载于:https://www.cnblogs.com/zouzhi/p/5191428.html