Friday, August 8, 2008

V$TIMEZONE_NAMES

Oracle 11gR1
V$TIMEZONE_NAMES displays valid time zone names.
Column
Datatype
Description
TZNAME
VARCHAR2(64)
Time zone region (for example, US/Pacific)
TZABBREV
VARCHAR2(64)
Corresponding daylight abbreviation (for example, PDT)
Notes:
Two time zone files are included in the Oracle home directory. The default time zone file is $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat. A smaller time zone file can be found in $ORACLE_HOME/oracore/zoneinfo/timezone.dat.
-rw-r--r-- 1 oracle dba 408315 Oct 19 2007 timezlrg.dat
-rw-r--r-- 1 oracle dba 160733 Oct 19 2007 timezone.dat
Environment variable: TZ=America/Vancouver
To view the time zone names in the file being used by the database, use the following query:
select * from V$TIMEZONE_NAMES;
TZNAME
TZABBREV
Africa/Algiers
LMT
Africa/Algiers
PMT
Africa/Algiers
WET
Africa/Algiers
WEST
Africa/Algiers
CET
Africa/Algiers
CEST
Africa/Cairo
LMT
Africa/Cairo
EET
Africa/Cairo
EEST
Africa/Casablanca
LMT
Africa/Casablanca
WET
Africa/Casablanca
WEST
……
……

Oracle dynamic performance views


More Oracle DBA tips, please visit Oracle DBA Tips 

V$SYSAUX_OCCUPANTS

Oracle 11gR1

V$SYSAUX_OCCUPANTS displays SYSAUX tablespace occupant information.

Column

Datatype

Description

OCCUPANT_NAME

VARCHAR2(64)

Occupant name

OCCUPANT_DESC

VARCHAR2(64)

Occupant description

SCHEMA_NAME

VARCHAR2(64)

Schema name for the occupant

MOVE_PROCEDURE

VARCHAR2(64)

Name of the move procedure; null if not applicable

MOVE_PROCEDURE_DESC

VARCHAR2(64)

Description of the move procedure

SPACE_USAGE_KBYTES

NUMBER

Current space usage of the occupant (in KB)

Note:

select occupant_name, occupant_desc, schema_name

from V$SYSAUX_OCCUPANTS

order by occupant_name;

OCCUPANT_NAME

OCCUPANT_DESC

SCHEMA_NAME

AO

Analytical Workspace Object Table

SYS

EM

Enterprise Manager Repository

SYSMAN

EM_MONITORING_USER

Enterprise Manager Monitoring User

DBSNMP

EXPRESSION_FILTER

Expression Filter System

EXFSYS

JOB_SCHEDULER

Unified Job Scheduler

SYS

LOGMNR

LogMiner

SYSTEM

LOGSTDBY

Logical Standby

SYSTEM

ODM

Oracle Data Mining

DMSYS

ORDIM

Oracle interMedia ORDSYS Components

ORDSYS

ORDIM/PLUGINS

Oracle interMedia ORDPLUGINS Components

ORDPLUGINS

ORDIM/SQLMM

Oracle interMedia SI_INFORMTN_SCHEMA Components

SI_INFORMTN_SCHEMA

SDO

Oracle Spatial

MDSYS

SM/ADVISOR

Server Manageability - Advisor Framework

SYS

SM/AWR

Server Manageability - Automatic Workload Repository

SYS

SM/OPTSTAT

Server Manageability - Optimizer Statistics History

SYS

SM/OTHER

Server Manageability - Other Components

SYS

STATSPACK

Statspack Repository

PERFSTAT

STREAMS

Oracle Streams

SYS

TEXT

Oracle Text

CTXSYS

TSM

Oracle Transparent Session Migration User

TSMSYS

ULTRASEARCH

Oracle Ultra Search

WKSYS

ULTRASEARCH_DEMO_USER

Oracle Ultra Search Demo User

WK_TEST

WM

Workspace Manager

WMSYS

XDB

XDB

XDB

XSAMD

OLAP Catalog

OLAPSYS

XSOQHIST

OLAP API History Tables

SYS

Oracle dynamic performance views

Oracle Database Components and the SYSAUX Tablespace

The SYSAUX tablespace is always created at database creation to serve as an auxiliary tablespace to the SYSTEM tablespace. Thus less tablespaces are needed, and the load on the SYSTEM tablespace is reduced.

The following table lists the components that use the SYSAUX tablespaces as their default tablespaces during installation, and the tablespace in which they were stored in earlier releases.

Component Using SYSAUX

Tablespace in Earlier Release

Analytical Workspace Object Table

SYSTEM

Enterprise Manager Repository

OEM_REPOSITORY

LogMiner

SYSTEM

Logical Standby

SYSTEM

OLAP API History Tables

CWMLITE

Oracle Data Mining

ODM

Oracle Spatial

SYSTEM

Oracle Streams

SYSTEM

Oracle Text

DRSYS

Oracle Ultra Search

DRSYS

Oracle interMedia ORDPLUGINS Components

SYSTEM

Oracle interMedia ORDSYS Components

SYSTEM

Oracle interMedia SI_INFORMTN_SCHEMA Components

SYSTEM

Server Manageability Components

New in Oracle Database 10g

Statspack Repository

User-defined

Oracle Scheduler

New in Oracle Database 10g

Workspace Manager

SYSTEM

Oracle dynamic performance view V$SYSAUX_OCCUPANTS displays SYSAUX tablespace occupant information.

select occupant_name, occupant_desc, schema_name

from V$SYSAUX_OCCUPANTS

order by occupant_name;

OCCUPANT_NAME

OCCUPANT_DESC

SCHEMA_NAME

AO

Analytical Workspace Object Table

SYS

EM

Enterprise Manager Repository

SYSMAN

EM_MONITORING_USER

Enterprise Manager Monitoring User

DBSNMP

EXPRESSION_FILTER

Expression Filter System

EXFSYS

JOB_SCHEDULER

Unified Job Scheduler

SYS

LOGMNR

LogMiner

SYSTEM

LOGSTDBY

Logical Standby

SYSTEM

ODM

Oracle Data Mining

DMSYS

ORDIM

Oracle interMedia ORDSYS Components

ORDSYS

ORDIM/PLUGINS

Oracle interMedia ORDPLUGINS Components

ORDPLUGINS

ORDIM/SQLMM

Oracle interMedia SI_INFORMTN_SCHEMA Components

SI_INFORMTN_SCHEMA

SDO

Oracle Spatial

MDSYS

SM/ADVISOR

Server Manageability - Advisor Framework

SYS

SM/AWR

Server Manageability - Automatic Workload Repository

SYS

SM/OPTSTAT

Server Manageability - Optimizer Statistics History

SYS

SM/OTHER

Server Manageability - Other Components

SYS

STATSPACK

Statspack Repository

PERFSTAT

STREAMS

Oracle Streams

SYS

TEXT

Oracle Text

CTXSYS

TSM

Oracle Transparent Session Migration User

TSMSYS

ULTRASEARCH

Oracle Ultra Search

WKSYS

ULTRASEARCH_DEMO_USER

Oracle Ultra Search Demo User

WK_TEST

WM

Workspace Manager

WMSYS

XDB

XDB

XDB

XSAMD

OLAP Catalog

OLAPSYS

XSOQHIST

OLAP API History Tables

SYS

ORA-09275

 Thank you for visiting Spatial DBA - Oracle and ArcSDE.

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

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


ORA-09275 : Connect internal is not a valid DBA connection
Cause: CONNECT INTERNAL is no longer supported for DBA connections.
Action: If NTS is enabled, you can connect to the database as CONNECT / AS SYSDBA or CONNECT / AS SYSOPER. If NTS is not enabled, you can connect as CONNECT SYS AS SYSDBA. You can also connect as an existing user with the appropriate password.
Note:
CONNECT INTERNAL and CONNECT INTERNAL/PASSWORD are not supported in Oracle9i. Instead, the following are the exact equivalents:
CONNECT/ as SYSDBA
CONNECT username/password AS SYSDBA
% sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Aug 8 10:02:13 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> connect internal
Enter password:
ERROR:
ORA-09275: CONNECT INTERNAL is not a valid DBA connection
SQL> conn / as sysdba
Connected.
SQL>
Last updated: August 24, 2009
All Oracle errors in the blog can be found at: Oracle errors
All ESRI ArcSDE errors in the blog can found at: ArcSDE Errors

PRODUCT_COMPONENT_VERSION

PRODUCT_COMPONENT_VERSION contains version and status information for component products.
Column
Datatype
NULL
Description
PRODUCT
VARCHAR2(64)
Product name
VERSION
VARCHAR2(64)
Version number
STATUS
VARCHAR2(64)
Status of release
Note:
The following query checks the current Oralce Database release number:
SQL> select * from product_component_version;
PRODUCT VERSION STATUS
---------------------------------------- --------------- --------------------
NLSRTL 11.1.0.6.0 Production
Oracle Database 11g Enterprise Edition 11.1.0.6.0 64bit Production
PL/SQL 11.1.0.6.0 Production
TNS for Solaris: 11.1.0.6.0 Production
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Solaris: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

Oracle dynamic performance views


More Oracle DBA tips, please visit Oracle DBA Tips 

Oracle Parameters by Functional Category

■ ANSI Compliance

BLANK_TRIMMING

■ Backup and Restore

BACKUP_TAPE_IO_SLAVES

RECYCLEBIN

TAPE_ASYNCH_IO

■ BFILEs

SESSION_MAX_OPEN_FILES

■ Buffer Cache and I/O

DB_nK_CACHE_SIZE

DB_BLOCK_BUFFERS

DB_BLOCK_SIZE

DB_CACHE_ADVICE

DB_CACHE_SIZE

DB_FILE_MULTIBLOCK_READ_COUNT

DB_KEEP_CACHE_SIZE

DB_RECYCLE_CACHE_SIZE

DB_WRITER_PROCESSES

DBWR_IO_SLAVES

DISK_ASYNCH_IO

FILESYSTEMIO_OPTIONS

READ_ONLY_OPEN_DELAYED

USE_INDIRECT_DATA_BUFFERS

■ Cursors and Library Cache

CURSOR_SHARING

CURSOR_SPACE_FOR_TIME

OPEN_CURSORS

SESSION_CACHED_CURSORS

■ Database/Instance Identification

DB_DOMAIN

DB_NAME

INSTANCE_NAME

■ Diagnostics and Statistics

BACKGROUND_CORE_DUMP

BACKGROUND_DUMP_DEST

CORE_DUMP_DEST

DB_BLOCK_CHECKING

DB_BLOCK_CHECKSUM

EVENT

MAX_DUMP_FILE_SIZE

SHADOW_CORE_DUMP

STATISTICS_LEVEL

TIMED_OS_STATISTICS

TIMED_STATISTICS

TRACE_ENABLED

TRACEFILE_IDENTIFIER

USER_DUMP_DEST

■ Distributed, Replication

COMMIT_POINT_STRENGTH

DISTRIBUTED_LOCK_TIMEOUT

GLOBAL_NAMES

HS_AUTOREGISTER

OPEN_LINKS

OPEN_LINKS_PER_INSTANCE

REPLICATION_DEPENDENCY_TRACKING

■ File Locations, Names, and Sizes

AUDIT_FILE_DEST

BACKGROUND_CORE_DUMP

BACKGROUND_DUMP_DEST

CONTROL_FILES

CORE_DUMP_DEST

DB_CREATE_FILE_DEST

DB_CREATE_ONLINE_LOG_DEST_n

DB_FILES

DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE

FILE_MAPPING

IFILE

LOG_ARCHIVE_DEST_n

SPFILE

■ Globalization

NLS_CALENDAR

NLS_COMP

NLS_CURRENCY

NLS_DATE_FORMAT

NLS_DATE_LANGUAGE

NLS_DUAL_CURRENCY

NLS_ISO_CURRENCY

NLS_LANGUAGE

NLS_LENGTH_SEMANTICS

NLS_NCHAR_CONV_EXCP

NLS_NUMERIC_CHARACTERS

NLS_SORT

NLS_TERRITORY

NLS_TIMESTAMP_FORMAT

NLS_TIMESTAMP_TZ_FORMAT

■ Java

JAVA_MAX_SESSIONSPACE_SIZE

JAVA_POOL_SIZE

JAVA_SOFT_SESSIONSPACE_LIMIT

■ Job Queues

JOB_QUEUE_PROCESSES

■ License Limits

LICENSE_MAX_SESSIONS

LICENSE_MAX_USERS

LICENSE_SESSIONS_WARNING

■ Memory

LARGE_POOL_SIZE

SHARED_POOL_RESERVED_SIZE

SHARED_POOL_SIZE

■ Miscellaneous

AQ_TM_PROCESSES

COMPATIBLE

FIXED_DATE

■ Networking

LOCAL_LISTENER

REMOTE_LISTENER

SERVICE_NAMES

■ Objects and LOBs

OBJECT_CACHE_MAX_SIZE_PERCENT

OBJECT_CACHE_OPTIMAL_SIZE

■ OLAP

OLAP_PAGE_POOL_SIZE

■ Optimizer

OPTIMIZER_DYNAMIC_SAMPLING

OPTIMIZER_FEATURES_ENABLE

OPTIMIZER_INDEX_CACHING

OPTIMIZER_INDEX_COST_ADJ

OPTIMIZER_MODE

OPTIMIZER_SECURE_VIEW_MERGING

QUERY_REWRITE_ENABLED

QUERY_REWRITE_INTEGRITY

STAR_TRANSFORMATION_ENABLED

■ Parallel Execution

PARALLEL_ADAPTIVE_MULTI_USER

PARALLEL_EXECUTION_MESSAGE_SIZE

PARALLEL_MAX_SERVERS

PARALLEL_MIN_PERCENT

PARALLEL_MIN_SERVERS

PARALLEL_THREADS_PER_CPU

■ PL/SQL

PLSQL_NATIVE_LIBRARY_DIR

PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT

PLSQL_V2_COMPATIBILITY

REMOTE_DEPENDENCIES_MODE

UTL_FILE_DIR

■ PL/SQL Compiler

PLSQL_CCFLAGS

PLSQL_CODE_TYPE

PLSQL_DEBUG

PLSQL_OPTIMIZE_LEVEL

PLSQL_WARNINGS

NLS_LENGTH_SEMANTICS

■ SGA Memory

DB_nK_CACHE_SIZE

DB_CACHE_ADVICE

DB_CACHE_SIZE

HI_SHARED_MEMORY_ADDRESS

JAVA_POOL_SIZE

LOCK_SGA

OLAP_PAGE_POOL_SIZE

PRE_PAGE_SGA

SGA_MAX_SIZE

SGA_TARGET

SHARED_MEMORY_ADDRESS

■ Real Application Clusters

ACTIVE_INSTANCE_COUNT

CLUSTER_DATABASE

CLUSTER_DATABASE_INSTANCES

CLUSTER_INTERCONNECTS

GC_FILES_TO_LOCKS

INSTANCE_GROUPS

INSTANCE_NUMBER

MAX_COMMIT_PROPAGATION_DELAY

PARALLEL_INSTANCE_GROUP

THREAD

■ Redo Logs, Archiving, and Recovery

CONTROL_FILE_RECORD_KEEP_TIME

DB_CREATE_ONLINE_LOG_DEST_n

DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE

FAST_START_MTTR_TARGET

LOG_ARCHIVE_CONFIG

LOG_ARCHIVE_DEST_n

LOG_ARCHIVE_DEST_STATE_n

LOG_ARCHIVE_DUPLEX_DEST

LOG_ARCHIVE_FORMAT

LOG_ARCHIVE_MAX_PROCESSES

LOG_ARCHIVE_MIN_SUCCEED_DEST

LOG_ARCHIVE_TRACE

LOG_BUFFER

LOG_CHECKPOINT_INTERVAL

LOG_CHECKPOINT_TIMEOUT

LOG_CHECKPOINTS_TO_ALERT

RECOVERY_PARALLELISM

■ Resource Manager

RESOURCE_LIMIT

RESOURCE_MANAGER_PLAN

■ Security and Auditing

AUDIT_FILE_DEST

AUDIT_SYS_OPERATIONS

AUDIT_SYSLOG_LEVEL

AUDIT_TRAIL

O7_DICTIONARY_ACCESSIBILITY

OS_AUTHENT_PREFIX

OS_ROLES

RDBMS_SERVER_DN

REMOTE_LOGIN_PASSWORDFILE

REMOTE_OS_AUTHENT

REMOTE_OS_ROLES

SQL92_SECURITY

■ Sessions and Processes

CPU_COUNT

PROCESSES

SESSIONS

■ Shared Server Architecture

CIRCUITS

DISPATCHERS

MAX_DISPATCHERS

MAX_SHARED_SERVERS

SHARED_SERVER_SESSIONS

SHARED_SERVERS

■ Standby Database

ARCHIVE_LAG_TARGET

DB_FILE_NAME_CONVERT

DB_UNIQUE_NAME

DG_BROKER_CONFIG_FILEn

DG_BROKER_START

FAL_CLIENT

FAL_SERVER

LOG_FILE_NAME_CONVERT

STANDBY_ARCHIVE_DEST

STANDBY_FILE_MANAGEMENT

■ Temporary Sort Space

BITMAP_MERGE_AREA_SIZE

CREATE_BITMAP_AREA_SIZE

HASH_AREA_SIZE

PGA_AGGREGATE_TARGET

SORT_AREA_RETAINED_SIZE

SORT_AREA_SIZE

WORKAREA_SIZE_POLICY

■ Transactions

COMMIT_WRITE

DML_LOCKS

FAST_START_PARALLEL_ROLLBACK

TRANSACTIONS

■ Undo Management

RESUMABLE_TIMEOUT

ROLLBACK_SEGMENTS

TRANSACTIONS_PER_ROLLBACK_SEGMENT

UNDO_MANAGEMENT

UNDO_RETENTION

UNDO_TABLESPACE

Oracle initializatoin parameters