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