V$INSTANCE

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 - Database is in a normal state. ·         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. ·         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. A single 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:

1. Querying ACTIVE_STATE column of V$INSTANCE view reveals the current state of an instance. Its values are:

·         NORMAL: normal unquiesced state.

·         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.

2. After issuing ALTER SYSTEM SUSPEND / RESUME statement, query DATABASE_STATUS confirms the database status: SUSPENDED / ACTIVE.
3. 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

Oracle dynamic performance views

Last updated: October 6 2009 Tuesday

V$OPEN_CURSOR

V$OPEN_CURSOR lists cursors that each user session currently has opened and parsed.

Column	Datatype	Description
SADDR	RAW(4 | 8)	Session address
SID	NUMBER	Session identifier
USER_NAME	VARCHAR2(30)	User that is logged in to the session
ADDRESS	RAW(4 | 8)	Used with HASH_VALUE to uniquely identify the SQL statement being executed in the session
HASH_VALUE	NUMBER	Used with ADDRESS to uniquely identify the SQL statement being executed in the session
SQL_ID	VARCHAR2(13)	SQL identifier of the SQL statement being executed in the session
SQL_TEXT	VARCHAR2(60)	First 60 characters of the SQL statement that is parsed into the open cursor
LAST_SQL_ACTIVE_TIME	DATE	Time when this cursor was last executed
SQL_EXEC_ID	NUMBER	If the open cursor is executing, then the SQL execution identifier for that execution (see V$SQL_MONITOR)

Note:

1.       V$OPEN_CURSOR related queries:

·          Count open cursors by username and SID select user_name, SID, count(*) cursors from V$OPEN_CURSOR group by User_Name, SID order by User_Name, SID;

·          displays information on cursor usage for the current session select * from V$SESSION_CURSOR_CACHE;

·          displays information on cursor usage for the system. select * from V$SYSTEM_CURSOR_CACHE;

·          list current users and the sql executing select SID, User_Name, SQL_Text from V$OPEN_CURSOR order by SID, User_Name, SQL_Text;

·          list details of current open cursors select user_name,to_char(sysdate,'hh24:mi:ss') col1, sql_text from V$OPEN_CURSOR;

·          query the subtotal and total usage of open cursors select user_name, count(*) from v$open_cursor group by rollup(user_name) order by count(*);

·          find out the transaction id for a particular DML statement that a user is running; this query can be used to find out the DML statement that a user is currently running select t.XIDUSN || '.' || t.XIDSLOT || '.' || t.XIDSQN TRANSACTION_ID, q.sql_text from   v$transaction t, v$session s, v$open_cursor o, v$sqltext q where  t.addr = s.taddr and s.saddr = o.saddr and        s.sid = o.sid and o.address = q.address and        o.hash_value = q.hash_value and s.username = 'USERNAME' order by q.address, q.hash_value, q.piece;

2.       Useful joins with V$OPEN_CURSOR

·          SID: Join to V$SESSION.SID ·          SADDR: Join to V$SESSION.SADDR ·          (ADDRESS,HASH_VALUE): Join to V$SQL.(ADDRESS,HASH_VALUE) ·          (ADDRESS,HASH_VALUE): Join to V$SQLTEXT.(ADDRESS,HASH_VALUE)

3.       Parameter related: OPEN_CURSORS

4.       Oracle error related:

ORA-1000: maximum open cursors exceeded ------------------------------------------------------------------------ Cause:  A host language program attempted to open too many cursors.         The initialization parameter OPEN_CURSORS determines the maximum         number of cursors per user. Action: Modify the program to use fewer cursors.         If this error occurs often, shut down Oracle, increase the value of         OPEN_CURSORS, and then restart Oracle.

Last updated: Thursday, October 1, 2009

Oracle data dictionary views

Oracle dynamic performance views

V$TABLESPACE

V$TABLESPACE displays tablespace information from the control file.

Column	Datatype	Description
TS#	NUMBER	Tablespace number
NAME	VARCHAR2(30)	Tablespace name
INCLUDED_IN_DATABASE_BACKUP	VARCHAR2(3)	Indicates whether the tablespace is included in full database backups using the BACKUP DATABASE RMAN command (YES) or not (NO); NO only if the CONFIGURE EXCLUDE RMAN command was used for this tablespace
BIGFILE	VARCHAR2(3)	Indicates whether the tablespace is a bigfile tablespace (YES) or a smallfile tablespace (NO)
FLASHBACK_ON	VARCHAR2(3)	Indicates whether the tablespace participates in FLASHBACK DATABASE operations (YES) or not (NO)
ENCRYPT_IN_BACKUP	VARCHAR2(3)	Indicates whether encryption is turned ON or off at the tablespace level: ON - Encryption is turned ON at the tablespace level OFF - Encryption is turned OFF at the tablespace level NULL - Encryption is neither explicitly turned on nor off at the tablespace level (default or when cleared)

Last updated: Tuesday, September 22, 2009

Oracle data dictionary views

Oracle dynamic performance views

RMAN dynamic views

The following Oracle dynamic views can be used to obtain RMAN information stored in the control file:

·         V$ARCHIVED_LOG: show which archives have been created, backed up, and cleared in the database.

·         V$BACKUP_CORRUPTION: show which blocks have been found to be corrupt during a backup of a backup set.

·         V$BACKUP_DATAFILE: useful for creating equal-sized backup sets by determining the number of blocks in each datafile. It can also help you find the number of corrupt blocks in the datafile.

·         V$BACKUP_DEVICE: display information about supported backup devices. DISK is not returned because it is always available.

·         V$BACKUP_FILES: display information about all RMAN backups (image copies and backup sets) and archived logs. The view simulates the LIST BACKUP and LIST COPY commands.

·         V$BACKUP_PIECE: show backup pieces created for backup sets.

·         V$BACKUP_REDOLOG: show archived logs stored in backup sets.

·         V$BACKUP_SET: show backup sets that have been created.

·         V$BACKUP_SPFILE: display information about server parameter files in backup sets.

·         V$COPY_CORRUPTION: show which blocks have been found to be corrupt during an image copy.

·         V$DATABASE_BLOCK_CORRUPTION: list database blocks marked as corrupt during the most recent RMAN backup.

·         V$RMAN_CONFIGURATION: list information about RMAN persistent configuration settings.

·         V$PROXY_ARCHIVEDLOG, V$PROXY_DATAFILE: proxy settings for RMAN.

·         V$BACKUP_ASYNC_IO, V$BACKUP_SYNC_IO: backup performance statistics