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

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