Thursday, August 14, 2008

DBA_EXTENTS

Oracle 11gR1

DBA_EXTENTS describes the extents comprising the segments in all tablespaces in the database.

Note that if a datafile (or entire tablespace) is offline in a locally managed tablespace, you will not see any extent information. If an object has extents in an online file of the tablespace, you will see extent information about the offline datafile. However, if the object is entirely in the offline file, a query of this view will not return any records.

Related View

USER_EXTENTS describes the extents comprising the segments owned by the current user's objects. This view does not display the OWNER, FILE_ID, BLOCK_ID, or RELATIVE_FNO columns.

Column

Datatype

NULL

Description

OWNER

VARCHAR2(30)


Owner of the segment associated with the extent

SEGMENT_NAME

VARCHAR2(81)


Name of the segment associated with the extent

PARTITION_NAME

VARCHAR2(30)


Object Partition Name (Set to NULL for non-partitioned objects)

SEGMENT_TYPE

VARCHAR2(18)


Type of the segment: INDEX PARTITION, TABLE PARTITION

TABLESPACE_NAME

VARCHAR2(30)


Name of the tablespace containing the extent

EXTENT_ID

NUMBER


Extent number in the segment

FILE_ID

NUMBER


File identifier number of the file containing the extent

BLOCK_ID

NUMBER


Starting block number of the extent

BYTES

NUMBER


Size of the extent in bytes

BLOCKS

NUMBER


Size of the extent in Oracle blocks

RELATIVE_FNO

NUMBER


Relative file number of the first extent block

Note:

1. Use the query below to find this objects that is in a datafile. It lists all the segments contained in that datafile, the blockid where it starts, and how many blocks the segment contains. It shows the owner, segment name, and segment type.

select

owner ownr,

segment_name name,

segment_type type,

extent_id exid,

file_id fiid,

block_id blid,

blocks blks

from

dba_extents

where

file_id = 10

order by

block_id;

OWNER

SEGMENT_NAME

SEGMENT_TYPE

EXTENT_ID

FILE_ID

BLOCK_ID

BLOCKS

SDE

A101

TABLE

0

10

9

8

SDE

A101

TABLE

1

10

17

8

SDE

A101

TABLE

2

10

25

8

SDE

A101

TABLE

3

10

33

8

SDE

A101

TABLE

4

10

41

8

Oracle data dictionary views

Posted by Admin at 8/14/2008 04:29:00 PM 0 comments

Labels: ,

USER_PASSWORD_LIMITS

Oracle 11gR1

USER_PASSWORD_LIMITS describes the password profile parameters that are assigned to the user.

Column

Datatype

NULL

Description

RESOURCE_NAME

VARCHAR2(32)

NOT NULL

Name of the password resource

LIMIT

VARCHAR2(40)

Value of the resource limit

Note:

1.

select * from USER_PASSWORD_LIMITS;

RESOURCE_NAME

LIMIT

FAILED_LOGIN_ATTEMPTS

50

PASSWORD_LIFE_TIME

UNLIMITED

PASSWORD_REUSE_TIME

UNLIMITED

PASSWORD_REUSE_MAX

UNLIMITED

PASSWORD_VERIFY_FUNCTION

NULL

PASSWORD_LOCK_TIME

UNLIMITED

PASSWORD_GRACE_TIME

UNLIMITED

Oracle data dictionary views

Posted by Admin at 8/14/2008 04:29:00 PM 0 comments

Labels: ,

NLS_DATABASE_PARAMETERS

Oracle 11gR1

NLS_DATABASE_PARAMETERS lists permanent NLS parameters of the database.

Column

Datatype

NULL

Description

PARAMETER

VARCHAR2(30)

NOT NULL

Parameter name

VALUE

VARCHAR2(40)

Parameter value

Note:

1.

select * from nls_database_parameters;

PARAMETER

VALUE

NLS_NCHAR_CHARACTERSET

UTF8

NLS_LANGUAGE

AMERICAN

NLS_TERRITORY

AMERICA

NLS_CURRENCY

$

NLS_ISO_CURRENCY

AMERICA

NLS_NUMERIC_CHARACTERS

.,

NLS_CHARACTERSET

UTF8

NLS_CALENDAR

GREGORIAN

NLS_DATE_FORMAT

DD-MON-RR

NLS_DATE_LANGUAGE

AMERICAN

NLS_SORT

BINARY

NLS_TIME_FORMAT

HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT

DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT

HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT

DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY

$

NLS_COMP

BINARY

NLS_LENGTH_SEMANTICS

BYTE

NLS_NCHAR_CONV_EXCP

FALSE

NLS_RDBMS_VERSION

10.2.0.2.0

Oracle data dictionary views

Posted by Admin at 8/14/2008 04:28:00 PM 0 comments

Labels: ,

Tuesday, August 12, 2008

V$BLOCKING_QUIESCE

Oracle 11gR1

V$BLOCKING_QUIESCE indicates if a session is blocking, or would block, a quiesce operation.

Column

Datatype

Description

SID

NUMBER

Session identifier

Note:

A database can be put into a quiesced mode by issuing the statement:

ALTER SYSTEM QUIESCE RESTRICTED;

Non-DBA active sessions will continue until they become inactive. An active session is one that is currently inside of a transaction, a query, a fetch, or a PL/SQL statement; or a session that is currently holding any shared resources (e.g., enqueues). No inactive sessions are allowed to become active. The statement may take a long time for active sessions to become inactive. Querying V$BLOCKING_QUIESCE view returns the sessions that are blocking the quiesce operations.

More information can be revealed by joining with V$SESSION:

select bl.sid, user, osuser, type, program

from v$blocking_quiesce bl, v$session se

where bl.sid = se.sid;

Querying ACTIVE_STATE column of V$INSTANCE view reveals the current state of an instance. Its values are:

  • NORMAL: normal unquiesced state.
  • QUIESCING: being quiesced, but some non-DBA sessions (username not as SYSTEM or SYS) are still active.
  • QUIESCED: Quiesced; no non-DBA sessions are active or allowed.

A quiesced database can be restored to normal operation by issuing the following statement:

ALTER SYSTEM UNQUIESCE;

Oracle dynamic performance views

Posted by Admin at 8/12/2008 02:01:00 PM 0 comments

Labels: ,

DBA_ALERT_HISTORY

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

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


===============================================================
Oracle 11gR1
DBA_ALERT_HISTORY describes a time-limited history of alerts which are no longer outstanding.

Column
Datatype
NULL
Description
SEQUENCE_ID
NUMBER
NOT NULL
Alert sequence number
REASON_ID
NUMBER
ID of the alert reason
OWNER
VARCHAR2(30)
Owner of the object on which the alert was issued
OBJECT_NAME
VARCHAR2(513)
Name of the object
SUBOBJECT_NAME
VARCHAR2(30)
Name of the subobject
OBJECT_TYPE
VARCHAR2(64)
Object type
REASON
VARCHAR2(4000)
Reason for the alert
TIME_SUGGESTED
TIMESTAMP(6) WITH TIME ZONE
Time when the alert was last updated
CREATION_TIME
TIMESTAMP(6) WITH TIME ZONE
Time when the alert was first produced
SUGGESTED_ACTION
VARCHAR2(4000)
Advice of the recommended action
ADVISOR_NAME
VARCHAR2(30)
Name of the advisor to be invoked for more information
METRIC_VALUE
NUMBER
Value of the related metrics
MESSAGE_TYPE
VARCHAR2(12)
Message type:
· Notification
· Warning
MESSAGE_GROUP
VARCHAR2(64)
Name of the message group to which the alert belongs
MESSAGE_LEVEL
NUMBER
Severity message level (1 to 32)
HOSTING_CLIENT_ID
VARCHAR2(64)
ID of the client or security group to which the alert relates
MODULE_ID
VARCHAR2(64)
ID of the module that originated the alert
PROCESS_ID
VARCHAR2(128)
Process ID
HOST_ID
VARCHAR2(256)
DNS host name of the originating host
HOST_NW_ADDR
VARCHAR2(256)
IP or other network address of the originating host
INSTANCE_NAME
VARCHAR2(16)
Originating instance name
INSTANCE_NUMBER
NUMBER
Originating instance number
USER_ID
VARCHAR2(30)
User ID
EXECUTION_CONTEXT_ID
VARCHAR2(60)
ID of the thread of execution
ERROR_INSTANCE_ID
VARCHAR2(142)
ID of an error instance plus a sequence number
RESOLUTION
VARCHAR2(7)
Resolution:
· cleared
· N/A
Note:
1.
SELECT REASON_ID, OBJECT_NAME, OBJECT_TYPE, REASON, TIME_SUGGESTED, SUGGESTED_ACTION
FROM DBA_ALERT_HISTORY
ORDER BY TIME_SUGGESTED DESC;
REASON_ID
OBJECT_NAME
OBJECT_TYPE
REASON
TIME_SUGGESTED
SUGGESTED_ACTION
9
UNDO
TABLESPACE
Tablespace [UNDO] is [70 percent] full
2008-08-12 3:59:38.061726 AM -07:00
Add space to the tablespace
9
CADASTRE_TABLES
TABLESPACE
Tablespace [CADASTRE_TABLES] is [74 percent] full
2008-08-09 4:07:27.364360 PM -07:00
Add space to the tablespace
10
UNDO
TABLESPACE
Snapshot Too Old Error detected: SQL ID 9ama6jfu7cpkw, Snapshot SCN 0x08ca.4862a1e4, Recent SCN 0x08ca.4b064c83, Undo Tablespace UNDO, Current Undo Retention 70238.
2008-08-06 3:56:20.442627 PM -07:00
Please run undo advisor.
9
UNDO
TABLESPACE
Tablespace [UNDO] is [83 percent] full
2008-08-04 4:12:38.097134 PM -07:00
Add space to the tablespace
2. The following dictionary views provide information about server alerts:
  • DBA_THRESHOLDS lists the threshold settings defined for the instance.
  • DBA_OUTSTANDING_ALERTS describes the outstanding alerts in the database.
  • DBA_ALERT_HISTORY lists a history of alerts that have been cleared.
  • V$ALERT_TYPES provides information such as group and type for each alert.
  • V$METRICNAME contains the names, identifiers, and other information about the system metrics.
  • V$METRIC and V$METRIC_HISTORY views contain system-level metric values in memory.

Oracle data dictionary views

Posted by Admin at 8/12/2008 02:00:00 PM

Labels: ,

DBA_OUTSTANDING_ALERTS

Oracle 11gR1
DBA_OUTSTANDING_ALERTS describes alerts which the server considers to be outstanding.
Column
Datatype
NULL
Description
SEQUENCE_ID
NUMBER

Alert sequence number
REASON_ID
NUMBER
NOT NULL
ID of the alert reason
OWNER
VARCHAR2(30)

Owner of the object on which the alert was issued
OBJECT_NAME
VARCHAR2(513)

Name of the object
SUBOBJECT_NAME
VARCHAR2(30)

Name of the subobject
OBJECT_TYPE
VARCHAR2(64)

Object type
REASON
VARCHAR2(4000)

Reason for the alert
TIME_SUGGESTED
TIMESTAMP(6) WITH TIME ZONE

Time when the alert was last updated
CREATION_TIME
TIMESTAMP(6) WITH TIME ZONE

Time when the alert was first created
SUGGESTED_ACTION
VARCHAR2(4000)

Advice of the recommended action
ADVISOR_NAME
VARCHAR2(30)

Name of the advisor to be invoked for more information
METRIC_VALUE
NUMBER

Value of the related metrics
MESSAGE_TYPE
VARCHAR2(12)

Message type:
· Notification
· Warning
MESSAGE_GROUP
VARCHAR2(64)

Name of the message group to which the alert belongs
MESSAGE_LEVEL
NUMBER

Message severity level (1 to 32)
HOSTING_CLIENT_ID
VARCHAR2(64)

ID of the client or security group to which the alert relates
MODULE_ID
VARCHAR2(64)

ID of the module that originated the alert
PROCESS_ID
VARCHAR2(128)

Process ID
HOST_ID
VARCHAR2(256)

DNS host name of the originating host
HOST_NW_ADDR
VARCHAR2(256)

IP or other network address of the originating host
INSTANCE_NAME
VARCHAR2(16)

Originating instance name
INSTANCE_NUMBER
NUMBER

Originating instance number
USER_ID
VARCHAR2(30)

User ID
EXECUTION_CONTEXT_ID
VARCHAR2(60)

ID of the threshold of execution
ERROR_INSTANCE_ID
VARCHAR2(142)

ID of an error instance plus a sequence number
Note:
1.
select reason_id, object_name, object_type, reason, creation_time,
suggested_action, message_group
from DBA_OUTSTANDING_ALERTS
order by creation_time desc;
REASON
_ID
OBJECT
_NAME
OBJECT
_TYPE
REASON
CREATION
_TIME
SUGGESTED
_ACTION
MESSAGE
_GROUP
9
UNDO
TABLESPACE
Tablespace [UNDO] is [86 percent] full
2008-08-12 5:19:56.053828
AM -07:00
Add space to
the tablespace
Space
9
BUS_TABLES
TABLESPACE
Tablespace [BUS_TABLES] is
[85 percent] full
2008-08-09 9:18:30.787179
PM -07:00
Add space to
the tablespace
Space
9
BUS_INDEXES
TABLESPACE
Tablespace [BUS_INDEXES] is
[88 percent] full
2006-06-11 7:46:39.945133
AM -07:00
Add space to
the tablespace
Space
2. The following dictionary views provide information about server alerts:
  • DBA_THRESHOLDS lists the threshold settings defined for the instance.
  • DBA_OUTSTANDING_ALERTS describes the outstanding alerts in the database.
  • DBA_ALERT_HISTORY lists a history of alerts that have been cleared.
  • V$ALERT_TYPES provides information such as group and type for each alert.
  • V$METRICNAME contains the names, identifiers, and other information about the system metrics.
  • V$METRIC and V$METRIC_HISTORY views contain system-level metric values in memory.

Oracle data dictionary views


More Oracle DBA tips, please visit Oracle DBA Tips 

Posted by Admin at 8/12/2008 01:59:00 PM 0 comments

Labels: ,

Subscribe to: Posts (Atom)