Oracle Asm Notes

Oracle Automatic Storage Management : notes Use the following table to determine the minimum number of disks and the minimum disk space requirements for the installation: Redundancy Level

Minimum Number of Disks

Data Files Recovery Files Both File Types

External

1

1.15 GB

2.3 GB

3.45 GB

Normal

2

2.3 GB

4.6 GB

6.9 GB

High

3

3.45 GB

6.9 GB

10.35 GB

Table 6-1 Tablespaces and Descriptions Tablespace Description EXAMPLE

This tablespace contains the sample schemas that Oracle includes with the database. The sample schemas provide a common platform for examples. Oracle documentation and educational materials contain examples based on the sample schemas.

SYSAUX

This is an auxiliary tablespace to the SYSTEM tablespace. Some components and products that used the SYSTEM tablespace or their own tablespaces prior to Oracle Database 10g now use the SYSAUX tablespace. Using SYSAUX reduces the load on the SYSTEM tablespace and reduces maintenance because there are fewer tablespaces to monitor and maintain. Every Oracle Database 10g or higher level database must have a SYSAUX tablespace. Components that use SYSAUX as their default tablespace during installation include Automatic Workload Repository, Oracle Streams, Oracle Text, and Enterprise Manager Repository. For more information, see the Oracle Database Administrator's Guide.

SYSTEM

This tablespace is always created at database creation. Oracle uses it to manage the database. It contains the data dictionary, which is the central set of tables and views used as a read-only reference for a particular database. It also contains various tables and views that contain administrative information about the database. These are all contained in the SYS schema, and can only be accessed by user SYS or other administrative users with the required privilege.

TEMP

This tablespace stores temporary data generated when processing SQL statements. For example, this tablespace would be used for query sorting. Every database should have a temporary tablespace that is assigned to users as their temporary tablespace. In the preconfigured database, the TEMP tablespace is specified as the default temporary tablespace. This means that if no temporary tablespace is specified when a user account is created, then Oracle assigns this tablespace to the user.

UNDOTBS1 This is the undo tablespace used by the database server to store undo information. See "Managing Undo for Your Database" to understand how an Oracle database uses the undo tablespace. Every database must have an undo tablespace. USERS

This tablespace is used to store permanent user objects and data. Like the TEMP tablespace, every database should have a tablespace for permanent user data that is assigned to users. Otherwise, user objects will be created in the SYSTEM tablespace, which is not good practice. In the preconfigured database, USERS is assigned the default tablespace, and space for all objects created by non-system users comes from this tablespace. For system users, the default permanent tablespace remains SYSTEM.

Reclaiming Wasted Space Over time, inserts, updates, and deletes (DML operations) to objects within a tablespace can create pockets of empty space that individually are not big enough to be reused. Collectively, these pockets

can combine to form large amounts of wasted space. The resulting sparsely-populated objects can suffer performance degradation during queries and DML operations. An Oracle database enables you reclaim wasted space in the following ways: Shrink Operation Data is compacted to the front of the table. Free space can either be returned to the tablespace or kept in the table for future inserts. A shrink operation on a table does not affect DML operations to the table during the operation. Reorganization Operation The object is re-created in a different part of the tablespace. This operation has the desirable side effect of compacting the object. For this operation to succeed, however, the tablespace must already have free space equal to the size of the object.

Viewing Segment Advisor Recommendations The Segment Advisor identifies objects such as tablespaces and tables that have unused space you can reclaim. It performs its analysis by examining usage and growth statistics and by sampling the data in the object. It is configured to run automatically at regular intervals, and you can also run it manually. Table 7-1 Administrative User Accounts Provided by Oracle Database Username

Password

Description

CTXSYS

CTXSYS

The Oracle Text account

DBSNMP

DBSNMP

The account used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database

MDDATA

MDDATA

The schema used by Oracle Spatial for storing Geocoder and router data

MDSYS

MDSYS

The Oracle Spatial and Oracle interMedia Locator administrator account

DMSYS

DMSYS

The data mining account. DMSYS performs data mining operations.

OLAPSYS

MANAGER

The account used to create OLAP metadata structures. This account owns the OLAP Catalog (CWMLite).

ORDPLUGINS

ORDPLUGINS

The Oracle interMedia user. Plugins supplied by Oracle and third party format plugins are installed in this schema.

ORDSYS

ORDSYS

The Oracle interMedia administrator account

OUTLN

OUTLN

The account that supports plan stability. Plan stability enables you to maintain the same execution plans for the same SQL statements. OUTLN acts as a role to centrally manage metadata associated with stored outlines.

Username

Password

Description

SI_INFORMTN_SCHEMA

SI_INFORMTN_SCHEMA

The account that stores the information views for the SQL/MM Still Image Standard

SYS

CHANGE_ON_INSTALL

The account used to perform database administration tasks

SYSMAN

CHANGE_ON_INSTALL

The account used to perform Oracle Enterprise Manager database administration tasks. Note that SYS and SYSTEM can also perform these tasks.

SYSTEM

MANAGER

Another account used to perform database administration tasks

Table 7-2 Privileges and Roles Privilege or Role System privilege

Object privilege

Role

Description

Examples

An Oracle-defined privilege The following are examples of system privileges that can usually granted only to and by be granted to users: administrators. System privileges enable users to perform specific • CREATE TABLE allows grantee to create tables database operations. in the grantee's schema.

A privilege that controls access to a specific object.

A group of privileges or other roles

•

CREATE USER allows grantee to create users in the database.

•

CREATE SESSION allows grantee to connect to an Oracle database to create a user session.

The following examples are object privileges that can be granted to users: •

SELECT ON hr.employees TO myuser

•

INSERT ON hr.employees TO myuser

The following examples are Oracle-defined roles: •

•

•

CONNECT is a role that Enterprise Manager automatically grants to a user when you create a user as shown in "Creating Users". This role has the CREATE SESSION privilege. RESOURCE extends the privileges of a user beyond those granted by the CONNECT role. It includes CREATE PROCEDURE, CREATE TRIGGER, and other system privileges. DBA is the standard role that can be granted by an administrator to another administrator. It includes all system privileges and should only be granted to the most trusted and qualified of users. Assigning this role to a user enables the user to administer the database.

You can create your own roles if you have been granted this privilege.