Operating system user for Host cridentials for EM -Windows

You have to provide the 'Log on as a batch job' privilege:
1. Go to control panel/administrative tools
a. click on "local security policy"
b. click on "local policies"
c. click on "user rights assignments"
d. double click on "log on as a batch job"
e. click on "add" and add the user that was entered in the "normal username" or "privileged username" section of the EM Console.
2. Go to the Preferences link in the EM GUI
a. click on Preferred Credentials (link on the left menu)
b. under "Target Type: Host" click on "set credentials"
c. enter the OS user who has logon as a batch job privilege into the "normal username" and "normal password" fields
3. Test the connectiona.
while in the Set Credentials window,
click on "Test"

SQL Processing Concepts
Before tuning the SQL in your applications, you should understand the Oracle Server SQL (Structured Query Language) processing scheme. This appendix introduces the basics of SQL processing and outlines the general phases through which each SQL statement goes. Topics include
SQL Statement Execution
DML Statement Processing
DDL Statement Processing
SQL Statement Execution

Figure B-1 outlines the stages commonly used to process and execute a SQL statement. In some cases, Oracle might execute these steps in a slightly different order. For example, the DEFINE stage could occur just before the FETCH stage, depending on how you wrote your code.
The following sections describe each phase of SQL statement processing for each type of SQL statement. As you read this information, remember that for many Oracle tools, several of the phases are performed automatically. Most users need not be concerned with or aware of this level of detail. However, you might find this information useful when writing Oracle applications.

Figure B-1: The Stages in Processing a SQL Statement
DML Statement Processing
This section describes a simplified look at what happens during the execution of a SQL statement. Queries (SELECTs) require additional steps as shown in Figure B-1; refer to the section "Query Processing" on page B-6 for more information.
Assume that you are using a Pro*C program to increase the salary for all employees in a department. Also assume that the program you are using has made a connection to Oracle and that you are connected to the proper schema to update the EMP table. You might embed the following SQL statement in your program: EXEC SQL UPDATE emp SET sal = 1.10 * sal
WHERE deptno = :dept_number;
DEPT_NUMBER is a program variable containing a value for department number. When the SQL statement is executed, the value of DEPT_NUMBER is used, as provided by the application program.
The next four sections explain what happens in each of the first four phases of DML statement processing. The same four phases are also necessary for each type of statement processing.
Stage 1: Create a Cursor
A program interface call creates a cursor. The cursor is created independently of any SQL statement; it is created in expectation of any SQL statement. In most applications, cursor creation is automatic. However, in precompiler programs, cursor creation can occur implicitly, or explicitly by declaring a cursor.
Stage 2: Parse the Statement
During parsing, the SQL statement is passed from the user process to Oracle and a parsed representation of the SQL statement is loaded into a shared SQL area. Many errors can be caught during this phase of statement processing. Parsing is the process of
translating a SQL statement, verifying it to be a valid statement
performing data dictionary lookups to check table and column definitions
acquiring parse locks on required objects so that their definitions do not change during the statement's parsing
checking privileges to access referenced schema objects
determining the optimal execution plan for the statement
loading it into a shared SQL area
for distributed statements, routing all or part of the statement to remote nodes that contain referenced data
A SQL statement is parsed only if a shared SQL area for an identical SQL statement does not exist in the shared pool. In this case, a new shared SQL area is allocated and the statement is parsed. For more information about shared SQL, refer to Chapter 10, "Managing SQL and Shared PL/SQL Areas".
The parse phase includes processing requirements that need to be done only once no matter how many times the statement is executed. Oracle translates each SQL statement only once, re-executing that parsed statement during subsequent references to the statement.
Although the parsing of a SQL statement validates that statement, parsing only identifies errors that can be found before statement execution. Thus, certain errors cannot be caught by parsing. For example, errors in data conversion or errors in data (such as an attempt to enter duplicate values in a primary key) and deadlocks are all errors or situations that can only be encountered and reported during the execution phase.
Query Processing
Queries are different from other types of SQL statements because they return data as results if they are successful. Whereas other statements return simply success or failure, a query can return one row or thousands of rows. The results of a query are always in tabular format, and the rows of the result are fetched (retrieved), either a row at a time or in groups.
Several issues relate only to query processing. Queries include not only explicit SELECT statements but also the implicit queries in other SQL statements. For example, each of the following statements requires a query as a part of its execution: INSERT INTO table SELECT ...
UPDATE table SET x = y WHERE ...
DELETE FROM table WHERE ...
CREATE table AS SELECT ...
In particular, queries
require read consistency
can use temporary segments for intermediate processing
can require the describe, define, and fetch phases of SQL statement processing
Stage 3: Describe Results
The describe phase is only necessary if the characteristics of a query's result are not known; for example, when a query is entered interactively by a user.
In this case, the describe phase is used to determine the characteristics (datatypes, lengths, and names) of a query's result.
Stage 4: Defining Output
In the define phase for queries, you specify the location, size, and datatype of variables defined to receive each fetched value. Oracle performs datatype conversion if necessary.
Stage 5: Bind Any Variables
At this point, Oracle knows the meaning of the SQL statement but still does not have enough information to execute the statement. Oracle needs values for any variables listed in the statement; in the example, Oracle needs a value for DEPT_NUMBER.
This process is called binding variables. A program must specify the location (memory address) where the value can be found. End users of applications might be unaware that they are specifying bind variables because the Oracle utility might simply prompt them for a new value.
Because you specify the location (binding by reference), you need not rebind the variable before re-execution. You can change its value and Oracle looks up the value on each execution, using the memory address.
Unless they are implied or defaulted, you must also specify a datatype and length for each value if Oracle needs to perform datatype conversion. For more information about specifying a datatype and length for a value, refer to the following publications:
the Programmer's Guide to the Oracle Precompilers when using an Oracle precompiler (see "Dynamic SQL Method 4")
the Programmer's Guide to the Oracle Call Interface
Stage 6: Execute the Statement
At this point, Oracle has all necessary information and resources, so the statement is executed. If the statement is a query or an INSERT statement, no rows need to be locked because no data is being changed. If the statement is an UPDATE or DELETE statement, however, all rows that the statement affects are locked from use by other users of the database, until the next COMMIT, ROLLBACK or SAVEPOINT for the transaction. This ensures data integrity.
For some statements you can specify a number of executions to be performed. This is called array processing. Given n number of executions, the bind and define locations are assumed to be the beginning of an array of size n.
Stage 7: Parallelize the Statement
When using the parallel query option, Oracle can parallelize queries and certain DDL operations. Parallelization causes multiple query servers to perform the work of the query so that the query can complete faster. Index creation and creating a table with a subquery can also be parallelized. Refer to Chapter 18, "Parallel Query Tuning", for more information on parallel query processing.
Stage 8: Fetch Rows of a Query Result
In the fetch phase, rows are selected and ordered (if requested by the query), and each successive fetch retrieves another row of the result, until the last row has been fetched.
DDL Statement Processing
The execution of DDL statements differs from the execution of DML statements and queries because the success of a DDL statement requires write access to the data dictionary. For these statements, the parse phase actually includes the parsing, data dictionary lookup, and execution.
Transaction management, session management, and system management SQL statements are processed using the parse and execute phases. To re-execute them, simply perform another execute.
In addition to determining which types of actions form a transaction, when you design an application you must also determine when it is useful to use the BEGIN_DISCRETE_TRANSACTION procedure to improve the performance of short, non-distributed transactions.

Host Credential for EM

The administrator needs to do additional configuration specifically on MS Windows is:

A. Set the OS username as a value for "Log on as a batch job" privilege.
For Windows 2000, go to Control Panel -> Administrative Tools -> Local Security Policy -> Local
Policies -> User Right Assignment and set the the OS username as a value for
"Log on as a batch job" privilege.

B. Check the check box "Allow service to interact with desktop" for BI Forms 10g Process Manager.
For Windows 200, go to Control Panel -> Administrative Tools -> Services ->
OraclexxxxxxProcessManager (the process manager service for BI Forms Home) -> Double
click it -> Click "Log on" tab page and check "Allow service to interact with desktop" check box


oracle 10.2.0 g on window vista. i can not set the host credential for EM. I Tried with following steps :
1. Control Panel, Administrative Tools.
2. Click on Local Security Policy.
3. Local Policies, User Rights Assignment.
4. Double click, 'Log on as a batch job'
here add username
i use 2 users : samir and oracle both users are member of ora_dba group.
First time it accepts the username and password but thenafter windows display an error message
"OEM executable stopped working and was closed. A problem caused the application to stop working correctly. Window will notify you if solution is available"

Locally/Dictionary Managed Tablespace

Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces.
By declaring a tablespace as DICTIONARY managed, you are specifying that extent management for segments in this tablespace will be managed using the following dictionary tables: sys.fet$, sys.uet$
A Locally Managed Tablespace is a tablespace that manages its own extents by maintaining a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks

password file & operating system authentication

Oracle's password file

If the DBA wants to start up an Oracle instance there must be a way for Oracle to authenticate this DBA. That is if (s)he is allowed to do so. Obviously, his password can not be stored in the database, because Oracle can not access the database before the instance is started up. Therefore, the authentication of the DBA must happen outside of the database. There are two distinct mechanisms to authenticate the DBA: using the password file or through the operating system.
The init parameter remote_login_passwordfile specifies if a password file is used to authenticate the DBA or not. If it set either to shared or exclusive a password file will be used.
Default location and file name
The default location for the password file is: $ORACLE_HOME/dbs/orapw$ORACLE_SID on Unix and %ORACLE_HOME%\database\PWD%ORACLE_SID%.ora on Windows.
Deleting a password file
If password file authentication is no longer needed, the password file can be deleted and the init parameter remote_login_passwordfile set to none.
Password file state
If a password file is shared or exclusive is also stored in the password file. After its creation, the state is shared. The state can be changed by setting remote_login_passwordfile and starting the database. That is, the database overwrites the state in the password file when it is started up.
A password file whose state is shared can only contain SYS.
Creating a password file
Password files are created with the orapwd tool.
Adding Users to the password file
Users are added to the password file when they're granted the SYSDBA or sysoper privilege.
SYS@ora10> show user;
USER is "SYS"
SYS@ora10> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE

SYS@ora10> grant SYSDBA to rene;

Grant succeeded.

SYS@ora10> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
RENE TRUE FALSE

SYS@ora10> grant SYSOPER to rene;

Grant succeeded.

SYS@ora10> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
RENE TRUE TRUE

SYS@ora10> revoke SYSDBA from rene;

Revoke succeeded.

SYS@ora10> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
RENE FALSE TRUE

SYS@ora10> revoke SYSOPER from rene;

Revoke succeeded.

SYS@ora10> select * from v$pwfile_users;

USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
OS authentication
It's possible to have the operating system check if a user may log on a database or not. The idea is, if the user already knew the password to log on to the operating system, he doesn't have to know a password on the database. Such a user is created like so:
create user ops$ identified externally
Note, the string ops$ must be identical to the value of os_authent_prefix in the initialization parameter.

Setting REMOTE_LOGIN_ PASSWORDFILE
In addition to creating the password file, you must also set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to the appropriate value. The values recognized are:
· NONE: Setting this parameter to NONE causes Oracle Database to behave as if the password file does not exist. That is, no privileged connections are allowed over nonsecure connections.
· EXCLUSIVE: (The default) An EXCLUSIVE password file can be used with only one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.
· SHARED: A SHARED password file can be used by multiple databases running on the same server. However, the file cannot be modified. This means that you cannot add users to a SHARED password file. All users needing SYSDBA or SYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.
Nonsecure Remote Connections
To connect to Oracle Database as a privileged user over a nonsecure connection, you must be authenticated by a password file. When using password file authentication, the database uses a password file to keep track of database usernames that have been granted the SYSDBA or SYSOPER system privilege. This form of authentication is discussed in "Using Password File Authentication".
Local Connections and Secure Remote Connections
You can connect to Oracle Database as a privileged user over a local connection or a secure remote connection in two ways:
· If the database has a password file and you have been granted the SYSDBA or SYSOPER system privilege, then you can connect and be authenticated by a password file.
· If the server is not using a password file, or if you have not been granted SYSDBA or SYSOPER privileges and are therefore not in the password file, you can use operating system authentication. On most operating systems, authentication for database administrators involves placing the operating system username of the database administrator in a special group, generically referred to as OSDBA. Users in that group are granted SYSDBA privileges. A similar group, OSOPER, is used to grant SYSOPER privileges to users.
Using Operating System Authentication
This section describes how to authenticate an administrator using the operating system.
OSDBA and OSOPER
Two special operating system groups control database administrator connections when using operating system authentication. These groups are generically referred to as OSDBA and OSOPER. The groups are created and assigned specific names as part of the database installation process. The specific names vary depending upon your operating system and are listed in the following table:
Operating System Group
UNIX User Group
Windows User Group
OSDBA
dba
ORA_DBA
OSOPER
oper
ORA_OPER

The default names assumed by the Oracle Universal Installer can be overridden. How you create the OSDBA and OSOPER groups is operating system specific.
Membership in the OSDBA or OSOPER group affects your connection to the database in the following ways:
· If you are a member of the OSDBA group and you specify AS SYSDBA when you connect to the database, then you connect to the database with the SYSDBA system privilege.
· If you are a member of the OSOPER group and you specify AS SYSOPER when you connect to the database, then you connect to the database with the SYSOPER system privilege.
· If you are not a member of either of these operating system groups and you attempt to connect as SYSDBA or SYSOPER, the CONNECT command fails.
See Also:
Your operating system specific Oracle documentation for information about creating the OSDBA and OSOPER groups
Preparing to Use Operating System Authentication
To enable operating system authentication of an administrative user:
1. Create an operating system account for the user.
2. Add the account to the OSDBA or OSOPER operating system defined groups.
Connecting Using Operating System Authentication
A user can be authenticated, enabled as an administrative user, and connected to a local database by typing one of the following SQL*Plus commands:CONNECT / AS SYSDBACONNECT / AS SYSOPER
For a remote database connection over a secure connection, the user must also specify the net service name of the remote database:CONNECT /@net_service_name AS SYSDBACONNECT /@net_service_name AS SYSOPER

Temporary Tables

Creation Of Temporary Tables
The data in a temporary table is private for the session that created it and can be session-specific or transaction-specific. If the data is to deleted at the end of the transaction the table should be defined as follows:
CREATE GLOBAL TEMPORARY TABLE my_temp_table ( column1 NUMBER, column2 NUMBER) ON COMMIT DELETE ROWS;
If on the other hand that data should be preserved until the session ends it should be defined as follows:
CREATE GLOBAL TEMPORARY TABLE my_temp_table ( column1 NUMBER, column2 NUMBER) ON COMMIT PRESERVE ROWS;