Showing posts with label scripts. Show all posts
Showing posts with label scripts. Show all posts

Tuesday, October 27, 2009

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

Posted by Admin at 10/27/2009 01:28:00 PM 0 comments

Labels: ,

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

Posted by Admin at 10/27/2009 12:57:00 PM 0 comments

Labels: ,

Thursday, December 11, 2008

DBA_REGISTRY

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
==================================================================
DBA_REGISTRY displays information about the components loaded into the database.

Related View
USER_REGISTRY displays information about the components loaded into the database that are owned by the current user.
Column
Datatype
NULL
Description
COMP_ID
VARCHAR2(30)
NOT NULL
Component identifier
COMP_NAME
VARCHAR2(255)

Component name
VERSION
VARCHAR2(30)

Component version loaded
STATUS
VARCHAR2(11)

Component status:
·         INVALID
·         VALID
·         LOADING
·         LOADED
·         UPGRADING
·         UPGRADED
·         DOWNGRADING
·         DOWNGRADED
·         REMOVING
·         REMOVED
MODIFIED
VARCHAR2(20)

Time when the component was last modified
NAMESPACE
VARCHAR2(30)
NOT NULL
Component namespace
CONTROL
VARCHAR2(30)
NOT NULL
User that created the component entry
SCHEMA
VARCHAR2(30)
NOT NULL
User that contains the objects for the component
PROCEDURE
VARCHAR2(61)

Validation procedure
STARTUP
VARCHAR2(8)

Indicates whether the component requires a startup after the upgrade (REQUIRED) or not
PARENT_ID
VARCHAR2(30)

Parent component identifier

Note:
1.       scripts using DBA_REGISTRY
--script to determine the status of the Oracle JVM and required packages

select comp_name, version, status
from   dba_registry;

select owner, status, count(*)
from   all_objects
where  object_type like '%JAVA%' group by owner, status;

select owner, object_type, count(*)
from   all_objects
where  object_type like '%JAVA%' and status <> 'VALID'
group by owner, object_type;

select owner, status, object_type, object_name
from   all_objects
where  object_name like'%DBMS_JAVA%';

select owner, status, object_type, object_name
from   all_objects
where  object_name like'%INITJVMAUX%';

select *
from v$sgastat
where POOL = 'java pool' or NAME = 'free memory';

show parameter pool_size

show parameter sga

select owner, object_type, status, dbms_java.longname(object_name)
from all_objects
where object_type like '%JAVA%' and status <> 'VALID';


2.       Example in Oracle Enterprise Edition
select comp_id, comp_name, version, status, schema, procedure
from   DBA_REGISTRY;

COMP_ID
COMP_NAME
VERSION
STATUS
SCHEMA
PROCEDURE
CATALOG
Oracle Database Catalog Views
10.2.0.2.0
VALID
SYS
DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATPROC
Oracle Database Packages and Types
10.2.0.2.0
VALID
SYS
DBMS_REGISTRY_SYS.VALIDATE_CATPROC
OWM
Oracle Workspace Manager
10.2.0.1.0
VALID
WMSYS
VALIDATE_OWM
JAVAVM
JServer JAVA Virtual Machine
10.2.0.2.0
VALID
SYS
INITJVMAUX.VALIDATE_JAVAVM
XML
Oracle XDK
10.2.0.2.0
VALID
SYS
XMLVALIDATE
CATJAVA
Oracle Database Java Packages
10.2.0.2.0
VALID
SYS
DBMS_REGISTRY_SYS.VALIDATE_CATJAVA
ORDIM
Oracle interMedia
10.2.0.2.0
VALID
ORDSYS
VALIDATE_ORDIM
SDO
Spatial
10.2.0.2.0
VALID
MDSYS
VALIDATE_SDO
CONTEXT
Oracle Text
10.2.0.2.0
VALID
CTXSYS
VALIDATE_CONTEXT
XDB
Oracle XML Database
10.2.0.2.0
VALID
XDB
DBMS_REGXDB.VALIDATEXDB
WK
Oracle Ultra Search
10.2.0.2.0
VALID
WKSYS
VALIDATE_WK
APS
OLAP Analytic Workspace
10.2.0.2.0
VALID
SYS
APS_VALIDATE
XOQ
Oracle OLAP API
10.2.0.2.0
VALID
SYS
XOQ_VALIDATE

3. Oracle Standard Edition: For components that are not supported by the Standard Edition, i.e., Oracle Data Mining, Oracle OLAP Catalog, Oracle OLAP Analytic Workspace, Oracle OLAP API, Oracle Spatial, the STATUS in the DBA_REGISTRY view is set to OPTION OFF.
select comp_id, comp_name, version, status, schema, procedure
from   DBA_REGISTRY;

COMP_ID
COMP_NAME
VERSION
STATUS
SCHEMA
PROCEDURE
CATALOG
Oracle Database Catalog Views
10.2.0.2.0
VALID
SYS
DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATPROC
Oracle Database Packages and Types
10.2.0.2.0
VALID
SYS
DBMS_REGISTRY_SYS.VALIDATE_CATPROC
OWM
Oracle Workspace Manager
10.2.0.1.0
VALID
WMSYS
VALIDATE_OWM
JAVAVM
JServer JAVA Virtual Machine
10.2.0.2.0
VALID
SYS
INITJVMAUX.VALIDATE_JAVAVM
XML
Oracle XDK
10.2.0.2.0
VALID
SYS
XMLVALIDATE
CATJAVA
Oracle Database Java Packages
10.2.0.2.0
VALID
SYS
DBMS_REGISTRY_SYS.VALIDATE_CATJAVA
ORDIM
Oracle interMedia
10.2.0.2.0
VALID
ORDSYS
VALIDATE_ORDIM
SDO
Spatial
9.2.0.7.0
OPTION OFF
MDSYS

CONTEXT
Oracle Text
10.2.0.2.0
VALID
CTXSYS
VALIDATE_CONTEXT
XDB
Oracle XML Database
10.2.0.2.0
VALID
XDB
DBMS_REGXDB.VALIDATEXDB

Oracle data dictionary views

Oracle dynamic performance views
Last updated: 2009-10-22 Thursday

Posted by Admin at 12/11/2008 03:46:00 PM

Labels: ,

Wednesday, September 10, 2008

V$SEGMENT_STATISTICS

V$SEGMENT_STATISTICS displays information about segment-level statistics.

Column
Datatype
Description
OWNER
VARCHAR2(30)
Owner of the object
OBJECT_NAME
VARCHAR2(30)
Name of the object
SUBOBJECT_NAME
VARCHAR2(30)
Name of the subobject
TABLESPACE_NAME
VARCHAR2(30)
Name of the table space to which the object belongs
TS#
NUMBER
Tablespace number
OBJ#
NUMBER
Dictionary object number of the object
DATAOBJ#
NUMBER
Data object number of the object
OBJECT_TYPE
VARCHAR2(18)
Type of the object
STATISTIC_NAME
VARCHAR2(64)
Name of the statistic
STATISTIC#
NUMBER
Statistic number
VALUE
NUMBER
Statistic value

Note:
1. Segment Waits
select      object_name
,     obj#
,     statistic_name
,     value
from  v$segment_statistics
where owner like '&owner'
and   statistic_name like '%waits%'
and   value > 0
order by statistic_name
,     value desc;
-- find the database table that is accessed the most: query the V$SEGMENT_STATISTICS view for this information by looking for tables that have a high number of physical reads and logical reads

SELECT t.owner,t.table_name,lr.value+pr.value AS total_reads
FROM  (SELECT owner, object_name, value FROM v$segment_statistics
        WHERE statistic_name='logical reads') lr,
      (SELECT owner, object_name, value FROM v$segment_statistics
        WHERE statistic_name='physical reads') pr,
       dba_tables t
WHERE lr.owner=pr.owner AND lr.object_name=pr.object_name
  AND lr.owner=t.owner AND lr.object_name=t.table_name
ORDER BY 3 desc;


Oracle dynamic performance views

Oracle data dictionary views


Last updated: 2009-11-05 Thursday

Posted by Admin at 9/10/2008 04:49:00 PM 0 comments

Labels: ,

Subscribe to: Posts (Atom)