Viewing Information About the Database

The following views provide information about the database content and structure.

View	Description
DATABASE_PROPERTIES	Displays permanent database properties.
GLOBAL_NAME	Displays the global database name.
V$DATABASE	Contains database information from the control file.

Note:

select * from GLOBAL_NAME;

GLOBAL_NAME

MYDB.US.ORACLE.COM

select dbid, name, created, log_mode, open_mode, dataguard_broker

from v$database;

DBID	NAME	CREATED	LOG_MODE	OPEN_MODE	DATAGUARD_BROKER
1234567890	MYDB	2003-09-20 7:21:27 AM	ARCHIVELOG	READ WRITE	DISABLED

select * from DATABASE_PROPERTIES order by property_name;

PROPERTY_NAME	PROPERTY_VALUE	DESCRIPTION
DBTIMEZONE	-07:00	DB time zone
DEFAULT_PERMANENT_TABLESPACE	USERS	Default Permanent Tablespace ID
DEFAULT_TBS_TYPE	SMALLFILE	Default tablespace type
DEFAULT_TEMP_TABLESPACE	TEMP	ID of default temporary tablespace
DICT.BASE	2	dictionary base tables version #
EXPORT_VIEWS_VERSION	8	Export views revision #
GLOBAL_DB_NAME	MYDB.US.ORACLE.COM	Global database name
NLS_CALENDAR	GREGORIAN	Calendar system
NLS_CHARACTERSET	UTF8	Character set
NLS_COMP	BINARY	NLS comparison
NLS_CURRENCY	$	Local currency
NLS_DATE_FORMAT	DD-MON-RR	Date format
NLS_DATE_LANGUAGE	AMERICAN	Date language
NLS_DUAL_CURRENCY	$	Dual currency symbol
NLS_ISO_CURRENCY	AMERICA	ISO currency
NLS_LANGUAGE	AMERICAN	Language
NLS_LENGTH_SEMANTICS	BYTE	NLS length semantics
NLS_NCHAR_CHARACTERSET	UTF8	NCHAR Character set
NLS_NCHAR_CONV_EXCP	FALSE	NLS conversion exception
NLS_NUMERIC_CHARACTERS	.,	Numeric characters
NLS_RDBMS_VERSION	10.2.0.2.0	RDBMS version for NLS parameters
NLS_SORT	BINARY	Linguistic definition
NLS_TERRITORY	AMERICA	Territory
NLS_TIMESTAMP_FORMAT	DD-MON-RR HH.MI.SSXFF AM	Time stamp format
NLS_TIMESTAMP_TZ_FORMAT	DD-MON-RR HH.MI.SSXFF AM TZR	Timestamp with timezone format
NLS_TIME_FORMAT	HH.MI.SSXFF AM	Time format
NLS_TIME_TZ_FORMAT	HH.MI.SSXFF AM TZR	Time with timezone format

Predefined User Accounts Provided by Oracle Database

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
==================================================================
 When Oracle Database is installed, the installation process creates a set of predefined accounts. These accounts can be classified as predefined administrative and non-administrative accounts.

Predefined Administrative Accounts

The set of predefined administrative accounts have special privileges required to administer areas of the database, such as the CREATE ANY TABLE or ALTER SESSION privilege, or EXECUTE privileges on packages owned by the SYS schema. The default tablespace for administrative accounts is either SYSTEM or SYSAUX.
To protect these accounts from unauthorized access, the installation process expires and locks most of these accounts. The database administrator is responsible for unlocking and resetting these accounts.

User Account	Description	Status After Installation
ANONYMOUS	Account that allows HTTP access to Oracle XML DB. It is used in place of the APEX_PUBLIC_USER account when the Embedded PL/SQL Gateway (EPG) is installed in the database. EPG is a Web server that can be used with Oracle Database. It provides the necessary infrastructure to create dynamic applications.	Expired and locked
CTXSYS	The account used to administer Oracle Text. Oracle Text enables you to build text query applications and document classification applications. It provides indexing, word and theme searching, and viewing capabilities for text.	Expired and locked
DBSNMP	The account used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database.	Open Password is created at installation or database creation time.
EXFSYS	The account used internally to access the EXFSYS schema, which is associated with the Rules Manager and Expression Filter feature. This feature enables you to build complex PL/SQL rules and expressions. The EXFSYS schema contains the Rules Manager and Expression Filter DDL, DML, and associated metadata.	Expired and locked
LBACSYS	The account used to administer Oracle Label Security (OLS). It is created only when you install the Label Security custom option.	Expired and locked
MDSYS	The Oracle Spatial and Oracle Multimedia Locator administrator account.	Expired and locked
MGMT_VIEW	An account used by Oracle Enterprise Manager Database Control.	Open Password is randomly generated at installation or database creation time. Users do not need to know this password.
OLAPSYS	The account that owns the OLAP Catalog (CWMLite). This account has been deprecated, but is retained for backward compatibility.	Expired and locked
OWBSYS	The account for administrating the Oracle Warehouse Builder repository. Access this account during the installation process to define the base language of the repository and to define Warehouse Builder workspaces and users. A data warehouse is a relational or multidimensional database that is designed for query and analysis.	Expired and locked
ORDPLUGINS	The Oracle Multimedia user. Plug-ins supplied by Oracle and third-party, format plug-ins are installed in this schema. Oracle Multimedia enables Oracle Database to store, manage, and retrieve images, audio, video, DICOM format medical images and other objects, or other heterogeneous media data integrated with other enterprise information.	Expired and locked
ORDSYS	The Oracle Multimedia administrator account.	Expired and locked
OUTLN	The account that supports plan stability. Plan stability prevents certain database environment changes from affecting the performance characteristics of applications by preserving execution plans in stored outlines. OUTLN acts as a role to centrally manage metadata associated with stored outlines.	Expired and locked
SI_INFORMTN_SCHEMA	The account that stores the information views for the SQL/MM Still Image Standard.	Expired and locked
SYS	An account used to perform database administration tasks.	Open Password is created at installation or database creation time.
SYSMAN	The account used to perform Oracle Enterprise Manager database administration tasks. The SYS and SYSTEM accounts can also perform these tasks.	Open Password is created at installation or database creation time.
SYSTEM	An account used to perform database administration tasks.	Open Password is created at installation or database creation time.
TSMSYS	An account used for transparent session migration (TSM).	Expired and locked
WK_TEST	The instance administrator for the default instance, WK_INST. After you unlock this account and assign this user a password, then you must also update the cached schema password using the administration tool Edit Instance Page. Ultra Search provides uniform search-and-location capabilities over multiple repositories, such as Oracle databases, other ODBC compliant databases, IMAP mail servers, HTML documents managed by a Web server, files on disk, and more.	Expired and locked
WKSYS	An Ultra Search database super-user. WKSYS can grant super-user privileges to other users, such as WK_TEST. All Oracle Ultra Search database objects are installed in the WKSYS schema.	Expired and locked
WKPROXY	An administrative account of Oracle9i Application Server Ultra Search.	Expired and locked
WMSYS	The account used to store Ultra Search system dictionaries and PL/SQL packages.	Expired and locked
XDB	The account used for storing Oracle XML DB data and metadata. Oracle XML DB provides high-performance XML storage and retrieval for Oracle Database data.	Expired and locked

Predefined Non-Administrative User Accounts

Default non-administrative user accounts are created when you install Oracle Database. Non-administrative user accounts only have the minimum privileges needed to perform their jobs. Their default tablespace is USERS.
To protect these accounts from unauthorized access, the installation process locks and expires these accounts immediately after installation. As the database administrator, you are responsible for unlocking and resetting these accounts.

User Account	Description	Status After Installation
APEX_PUBLIC_USER	The Oracle Database Application Express account. Use this account to specify the Oracle schema used to connect to the database through the database access descriptor (DAD). Oracle Application Express is a rapid, Web application development tool for Oracle Database.	Expired and locked
DIP	The Oracle Directory Integration and Provisioning (DIP) account that is installed with Oracle Label Security. This profile is created automatically as part of the installation process for Oracle Internet Directory-enabled Oracle Label Security.	Expired and locked
FLOWS_30000	The account that owns most of the database objects created during the installation of Oracle Database Application Express. These objects include tables, views, triggers, indexes, packages, and so on.	Expired and locked
FLOWS_FILES	The account that owns the database objects created during the installation of Oracle Database Application Express related to modplsql document conveyance, for example, file uploads and downloads. These objects include tables, views, triggers, indexes, packages, and so on.	Expired and locked
MDDATA	The schema used by Oracle Spatial for storing Geocoder and router data. Oracle Spatial provides a SQL schema and functions that enable you to store, retrieve, update, and query collections of spatial features in an Oracle database.	Expired and locked
ORACLE_OCM	The account used with Oracle Configuration Manager. This feature enables you to associate the configuration information for the current Oracle Database instance with OracleMetaLink. Then when you log a service request, it is associated with the database instance configuration information.	Expired and locked
PUBLIC	Account used for the PUBLIC user group. Oracle Universal Installer does not lock or expire this account upon installation. Its status is OPEN.	Expired and locked
SPATIAL_CSW_ADMIN_USR	The Catalog Services for the Web (CSW) account. It is used by Oracle Spatial CSW Cache Manager to load all record-type metadata and record instances from the database into the main memory for the record types that are cached.	Expired and locked
SPATIAL_WFS_ADMIN_USR	The Web Feature Service (WFS) account. It is used by Oracle Spatial WFS Cache Manager to load all feature type metadata and feature instances from the database into main memory for the feature types that are cached.	Expired and locked
XS$NULL	An internal account that represents the absence of a user in a session. Because XS$NULL is not a user, this account can only be accessed by the Oracle Database instance. XS$NULL has no privileges and no one can authenticate as XS$NULL, nor can authentication credentials ever be assigned to XS$NULL.	Expired and locked

V$SYSTEM_PARAMETER2

Oracle 11gR1

V$SYSTEM_PARAMETER2 displays information about the initialization parameters that are currently in effect for the instance, with each list parameter value appearing as a row in the view. A new session inherits parameter values from the instance-wide values.

Presenting the list parameter values in this format enables you to quickly determine the values for a list parameter. For example, if a parameter value is a, b, then the V$SYSTEM_PARAMETER view does not tell you if the parameter has two values (both a and b) or one value (a, b). V$SYSTEM_PARAMETER2 makes the distinction between the list parameter values clear.

Column	Datatype	Description
NUM	NUMBER	Parameter number
NAME	VARCHAR2(80)	Name of the parameter
TYPE	NUMBER	Parameter type: · 1 - Boolean · 2 - String · 3 - Integer · 4 - Parameter file · 5 - Reserved · 6 - Big integer
VALUE	VARCHAR2(4000)	Parameter value
DISPLAY_VALUE	VARCHAR2(4000)	Parameter value in a user-friendly format. For example, if the VALUE column shows the value 262144 for a big integer parameter, then the DISPLAY_VALUE column will show the value 256K.
ISDEFAULT	VARCHAR2(6)	Indicates whether the parameter is set to the default value (TRUE) or the parameter value was specified in the parameter file (FALSE)
ISSES_MODIFIABLE	VARCHAR2(5)	Indicates whether the parameter can be changed with ALTER SESSION (TRUE) or not (FALSE)
ISSYS_MODIFIABLE	VARCHAR2(9)	Indicates whether the parameter can be changed with ALTER SYSTEM and when the change takes effect: · IMMEDIATE - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect immediately. · DEFERRED - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions. · FALSE - Parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances.
ISINSTANCE_MODIFIABLE	VARCHAR2(5)	For parameters that can be changed with ALTER SYSTEm, indicates whether the value of the parameter can be different for every instance (TRUE) or whether the parameter must have the same value for all Real Application Clusters instances (FALSE). If the ISSYS_MODIFIABLE column is FALSE, then this column is always FALSE.
ISMODIFIED	VARCHAR2(8)	Indicates how the parameter was modified. If an ALTER SYSTEM was performed, the value will be MODIFIED.
ISADJUSTED	VARCHAR2(5)	Indicates whether Oracle adjusted the input value to a more suitable value (for example, the parameter value should be prime, but the user input a non-prime number, so Oracle adjusted the value to the next prime number)
ISDEPRECATED	VARCHAR2(5)	Indicates whether the parameter has been deprecated (TRUE) or not (FALSE)
ISBASIC	VARCHAR2(5)	Indicates whether the parameter is a basic parameter (TRUE) or not (FALSE)
DESCRIPTION	VARCHAR2(255)	Description of the parameter
ORDINAL	NUMBER	Position (ordinal number) of the parameter value. Useful only for parameters whose values are lists of strings.
UPDATE_COMMENT	VARCHAR2(255)	Comments associated with the most recent update

Note:

You can view parameter settings in several ways, as shown in the following table.

Method	Description
SHOW PARAMETERS	This SQL*Plus command displays the values of initialization parameters in effect for the current session.
SHOW SPPARAMETERS	This SQL*Plus command displays the values of initialization parameters in the server parameter file (SPFILE).
CREATE PFILE	This SQL statement creates a text initialization parameter file (PFILE) from the SPFILE or from the current in-memory settings. You can then view the PFILE with any text editor.
V$PARAMETER	This view displays the values of initialization parameters in effect for the current session.
V$PARAMETER2	This view displays the values of initialization parameters in effect for the current session. It is easier to distinguish list parameter values in this view because each list parameter value appears in a separate row.
V$SYSTEM_PARAMETER	This view displays the values of initialization parameters in effect for the instance. A new session inherits parameter values from the instance-wide values.
V$SYSTEM_PARAMETER2	This view displays the values of initialization parameters in effect for the instance. A new session inherits parameter values from the instance-wide values. It is easier to distinguish list parameter values in this view because each list parameter value appears in a separate row.
V$SPPARAMETER	This view displays the current contents of the SPFILE. The view returns FALSE values in the ISSPECIFIED column if an SPFILE is not being used by the instance.

Oracle dynamic performance views

V$SYSTEM_PARAMETER

Oracle 11gR1

V$SYSTEM_PARAMETER displays information about the initialization parameters that are currently in effect for the instance. A new session inherits parameter values from the instance-wide values.

Column	Datatype	Description
NUM	NUMBER	Parameter number
NAME	VARCHAR2(80)	Name of the parameter
TYPE	NUMBER	Parameter type: · 1 - Boolean · 2 - String · 3 - Integer · 4 - Parameter file · 5 - Reserved · 6 - Big integer
VALUE	VARCHAR2(4000)	Instance-wide parameter value
DISPLAY_VALUE	VARCHAR2(4000)	Parameter value in a user-friendly format. For example, if the VALUE column shows the value 262144 for a big integer parameter, then the DISPLAY_VALUE column will show the value 256K.
ISDEFAULT	VARCHAR2(9)	Indicates whether the parameter is set to the default value (TRUE) or the parameter value was specified in the parameter file (FALSE)
ISSES_MODIFIABLE	VARCHAR2(5)	Indicates whether the parameter can be changed with ALTER SESSION (TRUE) or not (FALSE)
ISSYS_MODIFIABLE	VARCHAR2(9)	Indicates whether the parameter can be changed with ALTER SYSTEM and when the change takes effect: · IMMEDIATE - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect immediately. · DEFERRED - Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions. · FALSE - Parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances.
ISINSTANCE_MODIFIABLE	VARCHAR2(5)	For parameters that can be changed with ALTER SYSTEm, indicates whether the value of the parameter can be different for every instance (TRUE) or whether the parameter must have the same value for all Real Application Clusters instances (FALSE). If the ISSYS_MODIFIABLE column is FALSE, then this column is always FALSE.
ISMODIFIED	VARCHAR2(8)	Indicates how the parameter was modified. If an ALTER SYSTEM was performed, the value will be MODIFIED.
ISADJUSTED	VARCHAR2(5)	Indicates whether Oracle adjusted the input value to a more suitable value (for example, the parameter value should be prime, but the user input a non-prime number, so Oracle adjusted the value to the next prime number)
ISDEPRECATED	VARCHAR2(5)	Indicates whether the parameter has been deprecated (TRUE) or not (FALSE)
ISBASIC	VARCHAR2(5)	Indicates whether the parameter is a basic parameter (TRUE) or not (FALSE)
DESCRIPTION	VARCHAR2(255)	Description of the parameter
UPDATE_COMMENT	VARCHAR2(255)	Comments associated with the most recent update
HASH	NUMBER	Hash value for the parameter name

Note:

You can view parameter settings in several ways, as shown in the following table.

Method	Description
SHOW PARAMETERS	This SQL*Plus command displays the values of initialization parameters in effect for the current session.
SHOW SPPARAMETERS	This SQL*Plus command displays the values of initialization parameters in the server parameter file (SPFILE).
CREATE PFILE	This SQL statement creates a text initialization parameter file (PFILE) from the SPFILE or from the current in-memory settings. You can then view the PFILE with any text editor.
V$PARAMETER	This view displays the values of initialization parameters in effect for the current session.
V$PARAMETER2	This view displays the values of initialization parameters in effect for the current session. It is easier to distinguish list parameter values in this view because each list parameter value appears in a separate row.
V$SYSTEM_PARAMETER	This view displays the values of initialization parameters in effect for the instance. A new session inherits parameter values from the instance-wide values.
V$SYSTEM_PARAMETER2	This view displays the values of initialization parameters in effect for the instance. A new session inherits parameter values from the instance-wide values. It is easier to distinguish list parameter values in this view because each list parameter value appears in a separate row.
V$SPPARAMETER	This view displays the current contents of the SPFILE. The view returns FALSE values in the ISSPECIFIED column if an SPFILE is not being used by the instance.

Oracle dynamic performance views