V$INSTANCE displays the state of the current instance.
Column | Datatype | Description |
INSTANCE_NUMBER | NUMBER | Instance number used for instance registration (corresponds to the INSTANCE_NUMBER initialization parameter) |
INSTANCE_NAME | VARCHAR2(16) | Name of the instance |
HOST_NAME | VARCHAR2(64) | Name of the host machine |
VERSION | VARCHAR2(17) | Database version |
STARTUP_TIME | DATE | Time when the instance was started |
STATUS | VARCHAR2(12) | Status of the instance: · STARTED - After STARTUP NOMOUNT· MOUNTED - After STARTUP MOUNT or ALTER DATABASE CLOSE· OPEN - After STARTUP or ALTER DATABASE OPEN· OPEN MIGRATE - After ALTER DATABASE OPEN { UPGRADE | DOWNGRADE } |
PARALLEL | VARCHAR2(3) | Indicates whether the instance is mounted in cluster database mode ( YES) or not (NO) |
THREAD# | NUMBER | Redo thread opened by the instance |
ARCHIVER | VARCHAR2(7) | Automatic archiving status: · STOPPED· STARTED· FAILED - Archiver failed to archive a log last time but will try again within 5 minutes |
LOG_SWITCH_WAIT | VARCHAR2(15) | Event that log switching is waiting for: · ARCHIVE LOG· CLEAR LOG· CHECKPOINT· NULL - ALTER SYSTEM SWITCH LOGFILE is hung but there is room in the current online redo log |
LOGINS | VARCHAR2(10) | Indicates whether the instance is in unrestricted mode, allowing logins by all users ( ALLOWED, or in restricted mode, allowing logins by database administrators only (RESTRICTED) |
SHUTDOWN_PENDING | VARCHAR2(3) | Indicates whether a shutdown is pending ( YES) or not (NO) |
DATABASE_STATUS | VARCHAR2(17) | Status of the database: · ACTIVE· SUSPENDED· INSTANCE RECOVERY |
INSTANCE_ROLE | VARCHAR2(18) | Indicates whether the instance is an active instance ( PRIMARY_INSTANCE) or an inactive secondary instance (SECONDARY_INSTANCE), or UNKNOWN if the instance has been started but not mounted |
ACTIVE_STATE | VARCHAR2(9) | Quiesce state of the instance: · NORMAL· QUIESCING - ALTER SYSTEM QUIESCE RESTRICTED has been issued: no new user transactions, queries, or PL/SQL statements are processed in this instance. User transactions, queries, or PL/SQL statements issued before the ALTER SYSTEM QUIESCE RESTRICTED statement are unaffected. DBA transactions, queries, or PL/SQL statements are also unaffected.· A single QUIESCED - ALTER SYSTEM QUIESCE RESTRICTED has been issued: no user transactions, queries, or PL/SQL statements are processed. DBA transactions, queries, or PL/SQL statements are unaffected. User transactions, queries, or PL/ SQL statements issued after the ALTER SYSTEM QUIESCE RESTRICTED statement are not processed.ALTER SYSTEM QUIESCE RESTRICTED statement quiesces all instances in an Oracle RAC environment. After this statement has been issued, some instances may enter into a quiesced state before other instances; the system is quiesced when all instances enter the quiesced state. |
BLOCKED | VARCHAR2(3) | Indicates whether all services are blocked ( YES) or not (NO) |
Note:
- Querying
ACTIVE_STATEcolumn ofV$INSTANCEview reveals the current state of an instance. Its values are:
·
NORMAL·
QUIESCING: being quiesced, but some non-DBA sessions (username not as SYSTEM or SYS) are still active.·
QUIESCED: Quiesced; no non-DBA sessions are active or allowed.- After issuing ALTER SYSTEM SUSPEND / RESUME statement, query
DATABASE_STATUSconfirms the database status:SUSPENDED / ACTIVE. - Script using
V$INSTANCE
-- to find the uptime of an instance SELECT (SYSDATE - STARTUP_TIME) FROM V$INSTANCE; |
-- to show PGA statistics for each session and background process SELECT to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) || nvl(lower(ssn.machine), ins.host_name) "SESSION", to_char(prc.spid, '999999999') "PID/THREAD", to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE", to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE" FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc, v$instance ins, v$statname stat1, v$statname stat2 WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory' AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max' AND se1.sid = ssn.sid AND se2.sid = ssn.sid AND ssn.paddr = bgp.paddr (+) AND ssn.paddr = prc.addr (+) |
-- to show database license usage SELECT rpad(c.name||':',11) || rpad(' current logons=' || (to_number(b.sessions_current)),20) || 'cumulative logons=' || rpad(substr(a.value,1,10),10) || 'highwater mark=' || b.sessions_highwater || ' startup time=' || to_char(d.startup_time,'YYYY/MM/DD HH24:MI:SS') Information FROM V$SYSSTAT a, V$LICENSE b, V$DATABASE c, V$INSTANCE d WHERE a.name = 'logons cumulative'; |
-- to check if a database is in restricted mode SQL> select logins from v$instance;LOGINS----------RESTRICTED |
-- to check the database version select version from v$instance; |
-- check that the instance status is good for upgrading select instance_name, status, logins, ACTIVE_STATE, DATABASE_STATUS from V$instance; INSTANCE_NAME STATUS LOGINS ACTIVE_ST DATABASE_STATUS ---------------- ------------ ---------- --------- ----------------- Mydb OPEN MIGRATE RESTRICTED NORMAL ACTIVE |
-- query startup time select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time" from v$instance; |
-- calculate instance uptime select 'Hostname : ' || host_name , 'Instance Name : ' || instance_name , 'Started At : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime , 'Uptime : ' || floor(sysdate - startup_time) || ' days(s) ' || trunc( 24*((sysdate-startup_time) - trunc(sysdate-startup_time))) || ' hour(s) ' || mod(trunc(1440*((sysdate-startup_time) - trunc(sysdate-startup_time))), 60) ||' minute(s) ' || mod(trunc(86400*((sysdate-startup_time) - trunc(sysdate-startup_time))), 60) ||' seconds' uptime from v$instance; |
Oracle data dictionary views
Last updated: October 6 2009 Tuesday |
