Friday, July 3, 2009

V$WAIT_CHAINS

V$WAIT_CHAINS displays information about blocked sessions. A wait chain is comprised of sessions that are blocked by one another. Each row represents a blocked and blocker session pair. If a wait chain is not a cyclical wait chain, then the last row for the chain does not have a blocker.

Column

Datatype

Description

CHAIN_ID

NUMBER

A number identifying the wait chain

CHAIN_IS_CYCLE

VARCHAR2(5)

Indicates whether the final blocked session in the wait chain is blocked by the initial blocked session (TRUE) or not (FALSE)

CHAIN_SIGNATURE

VARCHAR2(801)

An Oracle-specific text signature of the wait chain. This signature can be used to identify similar wait chains.

CHAIN_SIGNATURE_HASH

NUMBER

A numeric representation of CHAIN_SIGNATURE

INSTANCE

NUMBER

Blocked session's instance identifier

OSID

VARCHAR2(25)

Blocked session's operating system process identifier

PID

NUMBER

Blocked session's Oracle process identifier

SID

NUMBER

Blocked session's Oracle session identifier

SESS_SERIAL#

NUMBER

Blocked session's Oracle session serial number

BLOCKER_IS_VALID

VARCHAR2(5)

Indicates whether the blocked session has a blocker (TRUE) or not (FALSE)

BLOCKER_INSTANCE

NUMBER

Blocker session's instance identifier; NULL if BLOCKER_IS_VALID is FALSE

BLOCKER_OSID

VARCHAR2(25)

Blocker session's operating system process identifier; NULL if BLOCKER_IS_VALID is FALSE

BLOCKER_PID

NUMBER

Blocker session's Oracle process identifier; NULL if BLOCKER_IS_VALID is FALSE

BLOCKER_SID

NUMBER

Blocker session's Oracle session identifier; NULL if BLOCKER_IS_VALID is FALSE

BLOCKER_SESS_SERIAL#

NUMBER

Blocker session's Oracle session serial number; NULL if BLOCKER_IS_VALID is FALSE

BLOCKER_CHAIN_ID

NUMBER

If not NULL, then the blocker session is a member of another chain specified by this chain identifier. For the remaining wait chain information, see the wait chain with the specified CHAIN_ID.

IN_WAIT

VARCHAR2(5)

Indicates whether the blocked session is in a wait (TRUE) or not (FALSE)

TIME_SINCE_LAST_WAIT_SECS

NUMBER

Number of seconds since the last time the blocked session waited; NULL if IN_WAIT is TRUE

WAIT_ID

NUMBER

A number identifying the wait; NULL if IN_WAIT is FALSE

WAIT_EVENT

NUMBER

Resource or event number for which the blocked session is waiting; NULL if IN_WAIT is FALSE

WAIT_EVENT_TEXT

VARCHAR2(64)

Resource or event for which the blocked session is waiting; NULL if IN_WAIT is FALSE

P1

NUMBER

First additional wait parameter; NULL if IN_WAIT is FALSE

P1_TEXT

VARCHAR2(64)

Description of the first additional wait parameter; NULL if IN_WAIT is FALSE

P2

NUMBER

Second additional wait parameter; NULL if IN_WAIT is FALSE

P2_TEXT

VARCHAR2(64)

Description of the second additional wait parameter; NULL if IN_WAIT is FALSE

P3

NUMBER

Third additional wait parameter; NULL if IN_WAIT is FALSE

P3_TEXT

VARCHAR2(64)

Description of the third additional wait parameter; NULL if IN_WAIT is FALSE

IN_WAIT_SECS

NUMBER

Seconds the blocked session has been in the current wait; NULL if IN_WAIT is FALSE

TIME_REMAINING_SECS

NUMBER

Seconds remaining until the blocked session ends its wait (-1 if the blocked session can indefinitely wait); NULL if IN_WAIT is FALSE

NUM_WAITERS

NUMBER

Number of sessions waiting for the blocked session

ROW_WAIT_OBJ#

NUMBER

Object ID for the table containing the row specified in ROW_WAIT_ROW#; NULL if IN_WAIT is FALSE

ROW_WAIT_FILE#

NUMBER

Identifier for the datafile containing the row specified in ROW_WAIT_ROW#; NULL if IN_WAIT is FALSE. This column is valid only if the blocked session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not -1.

ROW_WAIT_BLOCK#

NUMBER

Identifier for the block containing the row specified in ROW_WAIT_ROW#; NULL if IN_WAIT is FALSE. This column is valid only if the blocked session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not -1.

ROW_WAIT_ROW#

NUMBER

Current row being locked; NULL if IN_WAIT is FALSE. This column is valid only if the blocked session is currently waiting for another transaction to commit and the value of ROW_WAIT_OBJ# is not -1.

Note:

1. V$WAIT_CHAINS is new in 11g.

Oracle data dictionary views

Oracle dynamic performance views

Oracle Process and Session Data Dictionary Views

The following are the Oracle data dictionary views that can help you manage processes and sessions.

View

Description

V$PROCESS

Contains information about the currently active processes

V$SESSION

Lists session information for each current session

V$SESS_IO

Contains I/O statistics for each user session

V$SESSION_LONGOPS

Displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution. More operations are added for every Oracle Database release.

V$SESSION_WAIT

Displays the current or last wait for each session

V$SESSION_WAIT_HISTORY

Lists the last ten wait events for each active session

V$WAIT_CHAINS

Displays information about blocked sessions

V$SYSSTAT

Contains session statistics

V$RESOURCE_LIMIT

Provides information about current and maximum global resource utilization for some system resources

V$SQLAREA

Contains statistics about shared SQL areas. Contains one row for each SQL string. Provides statistics about SQL statements that are in memory, parsed, and ready for execution

Oracle data dictionary views

Oracle dynamic performance views

Monday, June 22, 2009

How to replace undo tablespace in an Oracle database


Task: replace UNDO tablespace in an Oracle database
Steps:
1. See the UNDO settings in the database:
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- --------------------
undo_management string AUTO
undo_retention integer 10800
undo_tablespace string UNDO
2. Determine the size of the UNDO datafiles:
SQL> select bytes, name from v$datafile where upper(name) like '%UNDO%';

BYTES NAME
-------------------- -----------------------------------------------
1433075712 /fs/u02/oradata/testdb/undo_01.dbf
3. Create a new UNDO tablespace according to the requirement:

CREATE UNDO TABLESPACE UNDOTBS1 datafile '/fs/u02/oradata/testdb/undotbs01.dbf' size 2000m;

Tablespace created.
4. Edit the init.ora file and change the parameter "undo_tablespace=UNDOTBS1" and "undo_management=MANUAL". Note that a pfile may need to be created first by using "create pfile=… from spfile=…". "undo_management=MANUAL" ensures the old rollback segments can be taken offline and avoids editing the pfile and restarting the instance again.
5. Shut down the databse cleanly by performing a shutdown immediate.

SQL> shutdown immediate

6. Startup the database (specify the pfile if needed.)
SQL> startup
7. Confirm the new tablespace is in use:
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
8. Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline. The segments in the new tablespace may also show offline:

SQL>select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
------ ------------------------------ ------------------------------ ----------------
SYS SYSTEM SYSTEM ONLINE
PUBLIC _SYSSMU1$ UNDO OFFLINE
PUBLIC _SYSSMU2$ UNDO OFFLINE
PUBLIC _SYSSMU3$ UNDO OFFLINE
PUBLIC _SYSSMU4$ UNDO OFFLINE
PUBLIC _SYSSMU5$ UNDO OFFLINE
PUBLIC _SYSSMU6$ UNDO OFFLINE
PUBLIC _SYSSMU7$ UNDO OFFLINE
PUBLIC _SYSSMU8$ UNDO OFFLINE
PUBLIC _SYSSMU9$ UNDO OFFLINE
PUBLIC _SYSSMU10$ UNDO OFFLINE
PUBLIC _SYSSMU11$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU12$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU13$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU14$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU15$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU16$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU17$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU18$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU19$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU20$ UNDOTBS1 OFFLINE
21 rows selected.
If the old segments are online, then they must be taken offline as follows:
SQL>alter rollback segment "_SYSSMU3$" offline;

9. Drop the old undo tablespace and remove from the OS directory:
SQL> drop tablespace UNDO;
Tablespace dropped.

SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;
OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
------ ------------------------------ ------------------------------ ----------------
SYS SYSTEM SYSTEM ONLINE
PUBLIC _SYSSMU11$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU12$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU13$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU14$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU15$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU16$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU17$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU18$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU19$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU20$ UNDOTBS1 OFFLINE
11 rows selected.
SQL> shutdown immediate
10. Modify the init.ora file to change undo_management to AUTO and restart the database. spfile may need to be created. :
SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;
OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
------ ------------------------------ ------------------------------ ----------------
SYS SYSTEM SYSTEM ONLINE
PUBLIC _SYSSMU11$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU12$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU13$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU14$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU15$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU16$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU17$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU18$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU19$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU20$ UNDOTBS1 ONLINE
11 rows selected.


More Oracle DBA tips, please visit Oracle DBA Tips  

Tuesday, June 9, 2009

DBA_NETWORK_ACLS

 Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.

====================================================================



DBA_NETWORK_ACLS describes the access control list assignments to network hosts.
Column
Datatype
NULL
Description
HOST
VARCHAR2(1000)
NOT NULL
Network host
LOWER_PORT
NUMBER(5)
Lower bound of the port range
UPPER_PORT
NUMBER(5)
Upper bound of the port range
ACL
VARCHAR2(4000)
Path of the access control list
ACLID
RAW(16)
NOT NULL
Object ID of the access control list
Note:
1. There are two data dictionary views related to ACL: DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES. ACLs are stored in XML DB, which must be installed for the use of ACLs. Two steps are needed to create ACL. The first step is to create the actual ACL and define privileges for it:
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => ’file_name.xml’,
description => ’file description’,
principal => ’user_or_role’,
is_grant => TRUE|FALSE,
privilege => ’connect|resolve’,
start_date => null|timestamp_with_time_zone,
end_date => null|timestamp_with_time_zone);
END;
The second step is to assign network hosts to the ACL:
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => ’file_name.xml’,
host => ’network_host’,
lower_port => null|port_number,
upper_port => null|port_number);
END;

Oracle data dictionary views

Oracle dynamic performance views