DBA_CONSTRAINTS

Oracle 11gR1

DBA_CONSTRAINTS describes all constraint definitions on all tables in the database.

Related Views

· ALL_CONSTRAINTS describes constraint definitions on tables accessible to the current user.

· USER_CONSTRAINTS describes constraint definitions on tables in the current user's schema.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Owner of the constraint definition
CONSTRAINT_NAME	VARCHAR2(30)	NOT NULL	Name of the constraint definition
CONSTRAINT_TYPE	VARCHAR2(1)		Type of constraint definition: · C (check constraint on a table) · P (primary key) · U (unique key) · R (referential integrity) · V (with check option, on a view) · O (with read only, on a view)
TABLE_NAME	VARCHAR2(30)	NOT NULL	Name associated with the table (or view) with constraint definition
SEARCH_CONDITION	LONG		Text of search condition for a check constraint
R_OWNER	VARCHAR2(30)		Owner of table referred to in a referential constraint
R_CONSTRAINT_NAME	VARCHAR2(30)		Name of the unique constraint definition for referenced table
DELETE_RULE	VARCHAR2(9)		Delete rule for a referential constraint (CASCADE or NO ACTION)
STATUS	VARCHAR2(8)		Enforcement status of constraint (ENABLED or DISABLED)
DEFERRABLE	VARCHAR2(14)		Whether the constraint is deferrable
DEFERRED	VARCHAR2(9)		Whether the constraint was initially deferred
VALIDATED	VARCHAR2(13)		Whether all data obeys the constraint (VALIDATED or NOT VALIDATED)
GENERATED	VARCHAR2(14)		Whether the name of the constraint is user or system generated
BAD	VARCHAR2(3)		A YES value indicates that this constraint specifies a century in an ambiguous manner. To avoid errors resulting from this ambiguity, rewrite the constraint using the TO_DATE function with a four-digit year. See Also: the TO_DATE function in Oracle Database SQL Language Reference and Oracle Database Advanced Application Developer's Guide
RELY	VARCHAR2(4)		Whether an enabled constraint is enforced or unenforced. See Also: the constraints in Oracle Database SQL Language Reference
LAST_CHANGE	DATE		When the constraint was last enabled or disabled
INDEX_OWNER	VARCHAR2(30)		Name of the user owning the index
INDEX_NAME	VARCHAR2(30)		Name of the index (only shown for unique and primary-key constraints)
INVALID	VARCHAR2(7)		Whether the constraint is invalid
VIEW_RELATED	VARCHAR2(14)		Whether the constraint depends on a view

DBA_OBJECTS

DBA_OBJECTS describes all objects in the database. Its columns are the same as those in "ALL_OBJECTS".

Related Views

·         ALL_OBJECTS describes all objects accessible to the current user.

·         USER_OBJECTS describes all objects owned by the current user. This view does not display the OWNER column.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)	NOT NULL	Owner of the object
OBJECT_NAME	VARCHAR2(30)	NOT NULL	Name of the object
SUBOBJECT_NAME	VARCHAR2(30)		Name of the subobject (for example, partition)
OBJECT_ID	NUMBER	NOT NULL	Dictionary object number of the object
DATA_OBJECT_ID	NUMBER		Dictionary object number of the segment that contains the object
			Note: OBJECT_ID and DATA_OBJECT_ID display data dictionary metadata. Do not confuse these numbers with the unique 16-byte object identifier (object ID) that the Oracle Database assigns to row objects in object tables in the system.
OBJECT_TYPE	VARCHAR2(19)		Type of the object (such as TABLE, INDEX)
CREATED	DATE	NOT NULL	Timestamp for the creation of the object
LAST_DDL_TIME	DATE	NOT NULL	Timestamp for the last modification of the object resulting from a DDL statement (including grants and revokes)
TIMESTAMP	VARCHAR2(20)		Timestamp for the specification of the object (character data)
STATUS	VARCHAR2(7)		Status of the object (VALID, INVALID, or N/A)
TEMPORARY	VARCHAR2(1)		Whether the object is temporary (the current session can see only data that it placed in this object itself)
GENERATED	VARCHAR2(1)		Indicates whether the name of this object was system generated (Y) or not (N)
SECONDARY	VARCHAR2(1)		Whether this is a secondary object created by the ODCIIndexCreate method of the Oracle Data Cartridge (Y | N)
NAMESPACE	NUMBER		Namespace for the object
EDITION_NAME	VARCHAR2(30)		Name of the Application Edition where the object is identified

Note:

1.       For indexes of types IOT - NESTED and LOB, you can get the whole information from DBA_INDEXES rather than using DBA_OBJECTS.

2.       When TIMESTAMP and LAST_DDL_TIME columns will differ? (Metalink note 309235.1)

·         LAST_DDL_TIME：Timestamp for the last modification of the object resulting from a DDL command (including grants and revokes) ·         TIMESTAMP：Timestamp for the specification of the object Example 1:    Created a table called YY SQL> select TO_CHAR(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') DDL, timestamp from dba_objects where object_name='YY';  DDL                        TIMESTAMP --------------------    ------------------- 30-MAR-2005 21:08:34    2005-03-30:21:08:34                 As it can be seen when the table is created both has the same value SQL> GRANT SELECT ON YY TO PUBLIC;   <-----------DDL performed Grant succeeded. SQL> select TO_CHAR(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') DDL,timestamp from dba_objects where object_name='YY'; DDL                         TIMESTAMP --------------------     ------------------- 30-MAR-2005 21:13:44    2005-03-30:21:08:34                   It is seen that last_ddl_time is different. TIMESTAMP remains same as object creation Now we will see under what circumstances last_ddl_time and timestamp will differ. 1.       When DDL is performed on the dependent objects of the table. For example when an index is created or altered. 2.       When a table is truncated. 3.       When roles/privileges are granted/revoked. Example 2: A small example to better understand point 1 : SQL> CREATE TABLE YY(A NUMBER); Table created. SQL> select TO_CHAR(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') DDL,timestamp from dba_objects where object_name='YY'; DDL                                         TIMESTAMP --------------------                    ------------------- 31-MAR-2005 15:16:54                    2005-03-31:15:16:54 SQL> CREATE INDEX YYI ON YY(A); Index created. SQL> select TO_CHAR(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') DDL, timestamp from dba_objects where object_name='YY';  DDL                                                TIMESTAMP --------------------                              -------------- 31-MAR-2005 15:21:53                          2005-03-31:15:16:54 It can be clearly seen that though we did not perform any DDL operation on the table but the creation of an index on the table changed its last_ddl_time. Timestamp does not change.

3.       Scripts using DBA_OBJECTS

--Distribution of objects and data: Which schemas are taking up all of the space select  obj.owner, obj_cnt, decode(seg_size, NULL, 0, seg_size) "size MB" from    (select owner, count(*) obj_cnt from dba_objects group by owner) obj,         (select owner, ceil(sum(bytes)/1024/1024) seg_size             from dba_segments group by owner) seg where  obj.owner  = seg.owner(+) order  by 3 desc ,2 desc, 1

--Is JAVA installed in the database? This will return 9000'ish if it is... select        count(*) from   dba_objects where object_type like '%JAVA%' and    owner = 'SYS'

--to determining which segments have many buffers in the pool SELECT   o.OBJECT_NAME, COUNT(*) NUMBER_OF_BLOCKS   FROM   DBA_OBJECTS o, V$BH bh  WHERE   o.DATA_OBJECT_ID = bh.OBJD    AND   o.OWNER != 'SYS' GROUP BY o.OBJECT_NAME ORDER BY COUNT(*) desc;

-- report the type and count of objects by user   select owner,        sum(decode(object_type,'TABLE',1,0)) tables,        sum(decode(object_type,'INDEX',1,0)) indexes,        sum(decode(object_type,'SYNONYM',1,0)) synonyms,        sum(decode(object_type,'SEQUENCE',1,0)) sequences,        sum(decode(object_type,'VIEW',1,0)) views,        sum(decode(object_type,'CLUSTER',1,0)) clusters,        sum(decode(object_type,'DATABASE LINK',1,0)) database_links,        sum(decode(object_type,'PACKAGE',1,0)) packages,        sum(decode(object_type,'PACKAGE BODY',1,0)) package_bodies,        sum(decode(object_type,'PROCEDURE',1,0)) procedures from dba_objects group by owner;

-- The script produces information about locks being held or waited on in the database     select B.SID, C.USERNAME, C.OSUSER, C.TERMINAL,        DECODE(B.ID2, 0, A.OBJECT_NAME,'Trans-'||to_char(B.ID1)) OBJECT_NAME,        B.TYPE,        DECODE(B.LMODE,0,'--Waiting--',                       1,'Null',                       2,'Row Share',                       3,'Row Excl',                       4,'Share',                       5,'Sha Row Exc',                       6,'Exclusive',                         'Other') "Lock Mode",        DECODE(B.REQUEST,                       0,' ',                       1,'Null',                       2,'Row Share',                       3,'Row Excl',                       4,'Share',                       5,'Sha Row Exc',                       6,'Exclusive',                      'Other') "Req Mode"   from DBA_OBJECTS A, V$LOCK B, V$SESSION C where  A.OBJECT_ID(+) = B.ID1   and  B.SID = C.SID   and  C.USERNAME is not null order by B.SID, B.ID2;

-- The script generates scripts to compile all invalid objects in the database   select     decode(OBJECT_TYPE, 'PACKAGE BODY', 'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',                                         'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' ) from  dba_objects where STATUS = 'INVALID' and       OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW' ) order by OBJECT_TYPE, OBJECT_NAME;

Oracle data dictionary views

Oracle dynamic performance views

More Oracle DBA tips, please visit Oracle DBA Tips 

DBA_SEGMENTS

Oracle 11gR1

DBA_SEGMENTS describes the storage allocated for all segments in the database.

Related View

USER_SEGMENTS describes the storage allocated for the segments owned by the current user's objects. This view does not display the OWNER, HEADER_FILE, HEADER_BLOCK, or RELATIVE_FNO columns.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)		Username of the segment owner
SEGMENT_NAME	VARCHAR2(81)		Name, if any, of the segment
PARTITION_NAME	VARCHAR2(30)		Object Partition Name (Set to NULL for non-partitioned objects)
SEGMENT_TYPE	VARCHAR2(18)		Type of segment: · NESTED TABLE · TABLE · TABLE PARTITION · CLUSTER · LOBINDEX · INDEX · INDEX PARTITION · LOBSEGMENT · TABLE SUBPARTITION · INDEX SUBPARTITION · LOB PARTITION · LOB SUBPARTITION · ROLLBACK · TYPE2 UNDO · DEFERRED ROLLBACK · TEMPORARY · CACHE · SPACE HEADER · UNDEFINED
SEGMENT_SUBTYPE	VARCHAR2(10)		Subtype of LOB segment: SECUREFILE, ASSM, MSSM, and NULL
TABLESPACE_NAME	VARCHAR2(30)		Name of the tablespace containing the segment
HEADER_FILE	NUMBER		ID of the file containing the segment header
HEADER_BLOCK	NUMBER		ID of the block containing the segment header
BYTES	NUMBER		Size, in bytes, of the segment
BLOCKS	NUMBER		Size, in Oracle blocks, of the segment
EXTENTS	NUMBER		Number of extents allocated to the segment
INITIAL_EXTENT	NUMBER		Size in bytes requested for the initial extent of the segment at create time. (Oracle rounds the extent size to multiples of 5 blocks if the requested size is greater than 5 blocks.)
NEXT_EXTENT	NUMBER		Size in bytes of the next extent to be allocated to the segment
MIN_EXTENTS	NUMBER		Minimum number of extents allowed in the segment
MAX_EXTENTS	NUMBER		Maximum number of extents allowed in the segment
MAX_SIZE	NUMBER		Maximum number of blocks allowed in the segment
RETENTION	VARCHAR2(7)		Retention option for SECUREFILE segment
MINRETENTION	NUMBER		Minimum retention duration for SECUREFILE segment
PCT_INCREASE	NUMBER		Percent by which to increase the size of the next extent to be allocated
FREELISTS	NUMBER		Number of process freelists allocated to this segment
FREELIST_GROUPS	NUMBER		Number of freelist groups allocated to this segment
RELATIVE_FNO	NUMBER		Relative file number of the segment header
BUFFER_POOL	VARCHAR2(7)		Default buffer pool for the object

ORA-04031

ORA-04031: unable to allocate string bytes of shared memory ("string","string","string","string")

Cause: More shared memory is needed than was allocated in the shared pool.

Action: If the shared pool is out of memory, either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters "shared_pool_reserved_size" and "shared_pool_size". If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size".


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

ORA-03135

ORA-03135: connection lost contact

Cause: 1) Server unexpectedly terminated or was forced to terminate.
2) Server timed out the connection.

Action: 1) Check if the server session was terminated.
2) Check if the timeout parameters are set properly in sqlnet.ora.



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

ORA-01149

ORA-01149: cannot shutdown - file string has online backup set

Cause: An attempt to shut down normally found that an online backup is still in progress.

Action: End the backup of the offending tablespace and retry this command.



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