Chapter 3. Connecting and Configuring Oracle Database 11g XE

Motivation is what gets you started. Habit is what keeps you going. - Unknown

This chapter focuses on establishing a connection to Oracle Database 11g XE from client machines. There are different ways in which you can establish a connection to the database; for example, local and remote connections. We will explore these in detail. Also, we will learn to configure the database listener. Oracle memory management is another topic that will be covered in this chapter. The following topics will be covered in this chapter:

• Local database connections

• Oracle Net Listener

  • Configuring Oracle Net Listener

  • Viewing the status of Oracle Net Listener

  • Starting and stopping the listener

  • Configuring the tnsnames.ora file

• Remote database connections

• Changing the SGA and PGA sizes

Local database connections

Establishing a connection locally means running the SQL command-line utility on the same computer where Oracle Database 11g XE is installed, and initiating...

Local database connections

Establishing a connection locally means running the SQL command-line utility on the same computer where Oracle Database 11g XE is installed, and initiating a database connection command using the valid database credentials.

The following screenshot is taken from Oracle Database 11g XE on Windows, and demonstrates establishing a local database connection to the database:

Navigate to the Oracle Database 11g XE database home and start the sqlplus session with the /nolog option. Using the connect statement establish a connection to the database by supplying the valid database username and password. As shown in the preceding screenshot, connect as SYSTEM user with the password entered while installing Oracle Database 11g XE.

ORA_DBA is a local Windows group that gets automatically created when you install Oracle Database XE and your Windows username automatically gets added to this group. Members of ORA_DBA can connect to the Oracle Database XE without a password, as...

Remote database connections

Establishing a connection remotely means running the SQL command-line utility on a different host other than the Oracle Database 11g XE machine and initiating a database connection command using the valid database credentials over the network using a connect string.

The following screenshot is taken from Oracle Database 11g XE on Windows, and demonstrates establishing a remote database connection to the database:

As discussed earlier, a connect string defines which database server to connect to, on what port, and using what protocol. In the preceding example, xe is the name of the connect string.

Alternatively, we can connect to the database by providing the database host name and port as shown in the following screenshot:

The database hostname name and port information is basically encapsulated in the connect string.

Using the local sqlplus utility we can connect to the database via listener; thus, simulating remote client connections. To achieve this, we need to...

Changing SGA and PGA sizes

Oracle instance is made up of background processes and the shared memory (SGA). The background processes perform the maintenance tasks that are required to keep the database running. These background processes operate on the allocated shared memory. For example, one of the background process (PMON) is responsible for cleaning up (releasing locks and resources) after abnormally terminated database connections.

There are two types of memory that Oracle instance allocates:

• System Global Area (SGA): This is a shared memory area that contains database buffers, shared SQL and PL/SQL, and other control information for the instance.

• Process Global Area (PGA): This memory is private to a single process. PGA keeps process-specific information such as Oracle shared resources being used by a process, operating system resources used by the process, and other database session related information.

Oracle Database XE uses Automatic Memory Management (AMM) . This means Oracle Database...

Summary

In this chapter we learned how to establish a database connection on the local host as well as from the remote host. You should now be familiar with the Oracle Net Listener concepts and configuration. In this chapter, we have also learned about Oracle memory management and the procedure to change memory settings.

In the next chapter we will interact with the database by writing SQL queries. Some of the most commonly used SQL functions will also be discussed. We will create a sample table in the next chapter and learn to insert, modify, and delete records from this table.