Oracle 常用命令 & SQL - 運行管理 & SGA & PGA & CURSOR & Instance & 檢查篇 (Management & Check section)

運行檢查 相關指令

-- 檢查已啟動的 Instance (RAC)

$ srvctl status database -d $ORACLE_UNQNAME
Instance oraext1 is running on node oraext1
Instance oraext2 is running on node oraext2
Instance oraext3 is running on node oraext3
Instance oraext4 is running on node oraext4

-- 查Database狀態 (single instance)

set line 200
col force_logging format a10
select instance_name, open_mode, log_mode, force_logging from v$database, v$instance

-- 查table每一個block所儲存row的數量
-- replace [schema].[table_name]

select dbms_rowid.rowid_block_number(rowid) blockno,count(*)
from [schema].[table_name]
group by dbms_rowid.rowid_block_number(rowid);

-- 開啟 archivelog mode

SQL> shutdown immediate;
SQL> startup mount;

-- Check parameter log_archive_dest_1 is set
SQL> show parameter log_archive_dest_1;
-- Set log_archive_dest_1 if not set
SQL> alter system set log_archive_dest_1='LOCATION=+FRADG/archivelog' scope=both sid='*';

SQL> alter database archivelog;
SQL> alter database open;

-- 查正在執行的SQL

set line 200
col username format a13
col osuser format a13
select s.username,s.sid,s.osuser,t.sql_id,sql_text
from v$sqltext_with_newlines  t,v$session s
where t.address = s.sql_address
and t.hash_value = s.sql_hash_value
and s.sid != sys_context('userenv','sid')
order by s.sid,t.piece;

-- 查已開啟cursor量

Select 'session_cached_cursors' Parameter,
       Lpad(Value, 5) Value,
        Decode(Value, 0, ' n/a', To_Char(100 * Used / Value, '990') || '%') Usage
  From (Select Max(s.Value) Used
        From V$statname n, V$sesstat s
        Where n.Name = 'session cursor cache count'
          And s.Statistic# = n.Statistic#),
        (Select Value From V$parameter Where Name = 'session_cached_cursors')
        Union All
        Select 'open_cursors',
               Lpad(Value, 5),
               To_Char(100 * Used / Value, '990') || '%'
        From (Select Max(Sum(s.Value)) Used
              From V$statname n, V$sesstat s
              Where n.Name In ('opened cursors current', 'session cursor cache count')
                And s.Statistic# = n.Statistic#
              Group By s.Sid),
             (Select Value From V$parameter Where Name = 'open_cursors');

-- 查PGA統計 v$PGASTAT

col name format a40
select * from v$pgastat;