Oracle - Working With Databases
Monitoring databases and automating tasks with the OEM console
The Oracle Enterprise OEM console is a GUI management tool for Oracle. The user may monitor databases and schedule and automate tasks through the OEM console. The OEM may also be used as a front end manipulation tool for the Oracle database; its menus may be used to create and modify tablespaces, schemas, users, and database objects like tables. The console may be started by selecting Start -> Programs -> Oracle -> OraHome90 -> Enterprise Manager Console. We will use the standalone mode in this tutorial. The 'standalone mode' radio button should be selected in the pop up window that appears when OEM is first launched. The following is the initial screen:
Screenshot 2j: OEM Console View
Click on the '+' sign next to database to see your default database. If you are asked to log in, you may do so using a system account and the default password. The first thing you should do is expand the 'security' tab by clicking on the '+' symbol next to it. Next, click on the 'Sys' account. The account's details will load in the right hand window. Enter a new password and click 'Apply'. Follow the same procedure to change the password for 'SYSTEM'.
Screenshot 2k: Change Passwords
Familiarize yourself with the OEM console by navigating between the pull down menus and also within the database tree. Clicking on any item on the tree causes information about it to be displayed in the right hand window. Right clicking on the item shows the operations that may be performed with the item.
Most of the tools and services available through the pull down menus are also available through the tool bar buttons on the left hand side. The navigator menu allows users to connect to databases, add new databases to the tree and perform related operations. The Object menu allows users to create new objects, view details and so on and may be used as an alternative to the menu that pops up when the right mouse button is pressed on an object. The tools menu allows users to backup databases, manage net connections, open SQL*plus worksheets to issue SQL commands, analyze SQL statements and much more while the configuration menu allows users to store frequently used usernames and passwords, set fonts, formats and SQL logging parameters. In the next chapter, we will work with the OEM console and go over using the Database Configuration Assistant and using SQL commands to create a database.
SQL*Plus is a command line interpreter (CLI) used with Oracle. SQL*Plus may be accessed in several different ways. You may choose to use a SQL *Plus worksheet from the OEM console, open a SQL dialog through a 'Run SQL command line' tool that is part of the program group of an Oracle XE installation, or use the SQL->SQL Commands -> Enter Command path to enter commands and execute them on the web interface provided through Oracle XE's Database Home Page (another program within the Oracle XE group). Else, you could simply go to your oracle installation home directory in the DOS command prompt window (use start -> Run and type 'cmd' in the pop up and click OK to open up a DOS command window and use the 'cd' or 'chdir' command to go to the Oracle Installation home directory). Any of these methods may be used to run the Oracle and SQL exercises in this tutorial.
A transaction in Oracle is a unit of work. It may encompass a single or a set of SQL commands, DDL statements, a group of procedures, etc. Transactions in Oracle should be either confirmed to make changes permanent or cancelled to undo changes. Oracle uses the keywords COMMIT and ROLLBACK to confirm or undo transactions. COMMIT is issued by basically typing COMMIT at the SQL prompt in a SQL*Plus window; ROLLBACK is issued by typing ROLLBACK and pressing enter. This action reverses all changes from the previous commit or rollback onwards. Turning the AUTOCOMMIT feature on by typing 'SET AUTOCOMMIT ON' automatically confirms all commands without any need for a separate 'commit' command.
