ORA-01555

ORA-01555: snapshot too old: rollback segment number string with name "string" too small

Cause: rollback records needed by a reader for consistent read are overwritten by other writers

Action: If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments

All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

Last updated: 2009-10-29 Thursday

V$FILESPACE_USAGE

V$FILESPACE_USAGE summarizes space allocation information of each datafile and tempfile.

Column	Datatype	Description
TABLESPACE_ID	NUMBER	ID of the tablespace to which the file belongs
RFNO	NUMBER	Relative file number of the file
ALLOCATED_SPACE	NUMBER	Total allocated space in the file
FILE_SIZE	NUMBER	Current file size
FILE_MAXSIZE	NUMBER	Maximum file size
CHANGESCN_BASE	NUMBER	SCN base of the last change to the file
CHANGESCN_WRAP	NUMBER	SCN wrap of the last change to the file
FLAG	NUMBER	Flags for file attributes

Oracle data dictionary views

Oracle dynamic performance views

Last updated: 2009-10-27 Tuesday

V$DBLINK

 Thank you for visiting Spatial DBA - Oracle and ArcSDE.

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

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

V$DBLINK describes all database links (links with IN_TRANSACTION = YES) opened by the session issuing the query on V$DBLINK. These database links must be committed or rolled back before being closed.

Column	Datatype	Description
DB_LINK	VARCHAR2(128)	Name of the database link
OWNER_ID	NUMBER	Owner of the database link UID
LOGGED_ON	VARCHAR2(3)	Whether the database link is currently logged on
HETEROGENEOUS	VARCHAR2(3)	Whether the database link is heterogeneous
PROTOCOL	VARCHAR2(6)	Communication protocol for the database link
OPEN_CURSORS	NUMBER	Whether there are open cursors for the database link
IN_TRANSACTION	VARCHAR2(3)	Whether the database link is currently in a transaction
UPDATE_SENT	VARCHAR2(3)	Whether there has been an update on the database link
COMMIT_POINT_STRENGTH	NUMBER	Commit point strength of the transactions on the database link

Note:

1.      DBA_DB_LINKS describes all database links in the database. ALL_DB_LINKS describes the database links accessible to the current user. USER_DB_LINKS describes the database links owned by the current user.

2.       How to close and expire database link (Metalink 473635.1)

Database link is opened one for every session. Following are the ways links could be closed A) Only session which opened the database link can close it. Database link are closed when the session is closed.          select * from DBA_DB_LINKS   --->will show database links created          select * from V$DBLINK --->  will lists all open/active database links in your session B) Using ALTER SESSION or explicitly using command:             alter session close database link ;       or Use the following package:             dbms_session.close_database_link(); C) It is also possible to set idle_time limit to user under which connects dblink.         On the server side of dblink (target of dblink) issue:         create profile pidle limit idle_time 5; -- 5 minutes         alter user test profile pidle; -- user under which connects dblink         alter system set resource_limit=true; -- must be set to work idle_time limit         (or add resource_limit=true to init.ora or both)

 Oracle data dictionary views

Oracle dynamic performance views

Last updated: 2009-10-27 Tuesday

V$ROLLNAME

V$ROLLNAME lists the names of all online rollback segments. It can only be accessed when the database is open.

Column	Datatype	NULL	Description
USN	NUMBER		Rollback (undo) segment number
NAME	VARCHAR2(30)	NOT NULL	Rollback segment name

Note:

1.       Sample contents of V$ROLLNAME

SQL> select * from  V$ROLLNAME;          USN NAME ---------- ------------------------------          0 SYSTEM          1 _SYSSMU1$          2 _SYSSMU2$          3 _SYSSMU3$          4 _SYSSMU4$          5 _SYSSMU5$          6 _SYSSMU6$          7 _SYSSMU7$          8 _SYSSMU8$          9 _SYSSMU9$         10 _SYSSMU10$   11 rows selected.

2.       Scripts using V$ROLLNAME

-- Online Rollback Segments select d.segment_name, s.extents,        round((s.rssize/1024/1024))||'M',        d.initial_extent, d.next_extent,        d.pct_increase, d.min_extents,        d.max_extents, optsize, d.status from   v$rollname n, v$rollstat s, dba_rollback_segs d where  n.usn = s.usn and    d.segment_name = n.name(+);

-- show active sessions using rollback segments   select s.username, s.sid, rn.name, rs.extents, rs.status,        t.used_ublk, t.used_urec, do.object_name from   V$TRANSACTION t, V$SESSION s,        V$ROLLNAME rn, V$ROLLSTAT rs,        V$LOCKED_OBJECT lo, DBA_OBJECTS do where  t.addr = s.taddr and t.xidusn = rn.usn   and  rn.usn = rs.usn  and  t.xidusn = lo.xidusn(+)   and  do.object_id = lo.object_id;

Oracle data dictionary views

Oracle dynamic performance views

Last updated: 2009-10-27 Tuesday

V$BACKUP_PIECE

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

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

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

V$BACKUP_PIECE displays information about backup pieces from the control file. Each backup set consists of one or more backup pieces.

Column	Datatype	Description
RECID	NUMBER	Backup piece record ID
STAMP	NUMBER	Backup piece record stamp
SET_STAMP	NUMBER	Backup set stamp
SET_COUNT	NUMBER	Backup set count
PIECE#	NUMBER	Backup piece number (1-N)
COPY#	NUMBER	Indicates the copy number for backup pieces created with duplex enabled. 1 if the backup piece is not duplexed.
DEVICE_TYPE	VARCHAR2(17)	Type of the device on which the backup piece resides. Set to DISK for backup sets on disk. See Also: V$BACKUP_DEVICE
HANDLE	VARCHAR2(513)	Backup piece handle identifies the backup piece on restore
COMMENTS	VARCHAR2(64)	Comment returned by the operating system or storage subsystem. Set to NULL for backup pieces on disk. This value is informational only; not needed for restore.
MEDIA	VARCHAR2(65)	Name of the media on which the backup piece resides. This value is informational only; not needed for restore.
MEDIA_POOL	NUMBER	The media pool in which the copy resides. This is the same value that was entered in the POOL operand of the Recovery Manager BACKUP command.
CONCUR	VARCHAR2(3)	(YES | NO) Indicates whether the piece on a media that can be accessed concurrently
TAG	VARCHAR2(32)	Backup piece tag. The tag is specified at backup set level, but stored at piece level.
STATUS	VARCHAR2(1)	Indicates the status of the piece: A (available), D (deleted), or X (expired)
START_TIME	DATE	Starting time
COMPLETION_TIME	DATE	Completion time
ELAPSED_SECONDS	NUMBER	Number of elapsed seconds
DELETED	VARCHAR2(3)	(YES/NO) NO indicates that the file still exists. YES indicates the file no longer exists because it has been deleted.
BYTES	NUMBER	Size of the backup piece (in bytes)
IS_RECOVERY_DEST_FILE	VARCHAR2(3)	Indicates whether the file was created in the flash recovery area (YES) or not (NO)
RMAN_STATUS_RECID	NUMBER	Owning V$RMAN_STATUS record ID
RMAN_STATUS_STAMP	NUMBER	Owning V$RMAN_STATUS record stamp
COMPRESSED	VARCHAR2(3)	Indicates whether the backup piece is compressed (YES) or not (NO)
BACKED_BY_VSS	VARCHAR2(3)	Whether or not the file has been backed up by Volume Shadow Copy Service (VSS). This column is reserved for internal use.

Note:

1.       Click to view more RMAN related dynamic views.

2.       Scripts using V$BACKUP_PIECE

select RECID,SET_STAMP, TAG, STATUS, HANDLE from   v$backup_piece order by recid desc;

select recid, status, device_type, handle, completion_time from v$backup_piece;

-- Available backup sets contained in the control file SELECT bs.recid, bp.piece#, bp.copy#, bp.recid bp_key   , DECODE(bs.controlfile_included             , 'NO', '-'             , bs.controlfile_included) controlfile_included   , TO_CHAR(bs.completion_time, 'DD-MON-YYYY HH24:MI:SS') completion_time   , DECODE(   status             , 'A', 'Available'             , 'D', 'Deleted'             , 'X', 'Expired')  status   , handle handle FROM  v$backup_set bs, v$backup_piece bp WHERE bs.set_stamp = bp.set_stamp   AND bs.set_count = bp.set_count   AND bp.status IN ('A', 'X')   AND bs.controlfile_included != 'NO' ORDER BY bs.recid, piece#;

-- Available backup pieces contained in the control file   SELECT     bs.recid                                            bs_key   , bp.piece#                                           piece#   , bp.copy#                                            copy#   , bp.recid                                            bp_key   , DECODE(   status             , 'A', 'Available'             , 'D', 'Deleted'             , 'X', 'Expired')                           status   , handle                                              handle   , TO_CHAR(bp.start_time, 'mm/dd/yy HH24:MI:SS')       start_time   , TO_CHAR(bp.completion_time, 'mm/dd/yy HH24:MI:SS')  completion_time   , bp.elapsed_seconds                                  elapsed_seconds FROM     v$backup_set    bs   , v$backup_piece  bp WHERE       bs.set_stamp = bp.set_stamp   AND bs.set_count = bp.set_count   AND bp.status IN ('A', 'X') ORDER BY     bs.recid   , piece#;

-- Available automatic SPFILE files within all available (and expired) backup sets. SELECT     bs.recid                                               bs_key   , bp.piece#                                              piece#   , bp.copy#                                               copy#   , bp.recid                                               bp_key   , sp.spfile_included                                     spfile_included   , TO_CHAR(bs.completion_time, 'DD-MON-YYYY HH24:MI:SS')  completion_time   , DECODE(   status             , 'A', 'Available'             , 'D', 'Deleted'             , 'X', 'Expired')                              status   , handle                                                 handle FROM     v$backup_set                                           bs   , v$backup_piece                                         bp   ,  (select distinct           set_stamp         , set_count         , 'YES'     spfile_included       from v$backup_spfile)                                sp WHERE       bs.set_stamp = bp.set_stamp   AND bs.set_count = bp.set_count   AND bp.status IN ('A', 'X')   AND bs.set_stamp = sp.set_stamp   AND bs.set_count = sp.set_count ORDER BY     bs.recid   , piece#;

Oracle data dictionary views

Oracle dynamic performance views

Last updated: 2009-10-26 Monday