DB Creation   «Prev  Next»

Lesson 2SQL*Plus
ObjectiveLearn what you can do with SQL *Plus

Connect to Oracle Shared Server using SQL

To connect to Oracle Shared Server using SQL*Plus, you can use the following steps:
  1. Open a command prompt or terminal window.
  2. Navigate to the directory where SQL*Plus is installed.
  3. Start SQL*Plus by running the following command:
    • Windows: `sqlplus`
    • Unix and Linux: `sqlplus /nolog`
  4. If you are connecting to a remote database, you will need to specify the TNS name[1] of the database. You can do this by using the `SET SERVERNAME` command. For example, to connect to the database named `ORCL` on the server `localhost`, you would run the following command:
    SQL> SET SERVERNAME=localhost/ORCL;
    
  5. Once you have specified the TNS name of the database, you can connect to the database using the `CONNECT` command. For example, to connect to the database `ORCL` as the user `scott`, you would run the following command:
     
    SQL> CONNECT scott@localhost/ORCL;
    
  6. If you are connecting to a database that is using shared servers, you may need to specify the `SHARED_SERVERS` parameter in the `CONNECT` command. For example, to connect to the database `ORCL` using shared servers, you would run the following command:
     
    SQL> CONNECT scott@localhost/ORCL AS SYSDBA SHARED_SERVERS;
    
  7. If you are connecting to a database that is using a wallet, you will need to specify the `WALLET_LOCATION` parameter in the `CONNECT` command. For example, to connect to the database `ORCL` using a wallet located in the directory `/home/user/wallet`, you would run the following command:
    SQL> CONNECT scott@localhost/ORCL AS SYSDBA SHARED_SERVERS WALLET_LOCATION=/home/user/wallet;
    

Once you have successfully connected to the database, you can start running SQL commands.
Example:
$ sqlplus /nolog
SQL> SET SERVERNAME=localhost/ORCL;
SQL> CONNECT scott@localhost/ORCL SHARED_SERVERS;
Enter password: tiger

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0 - 64bit Production

SQL>

Oracle Server Manager retired with the release of Oracle 9i

Oracle Server Manager (OSM) was officially retired by Oracle Corporation in the Oracle Database 10g release, which was introduced in 2003. This means that OSM was no longer included or supported in any subsequent releases of the Oracle database. Oracle Server Manager was a tool used for 1) managing and 2) monitoring Oracle databases. It provided database administrators with a command-line interface for performing tasks such as creating and configuring databases, managing users and security, and monitoring database performance.
However, as newer releases of Oracle were introduced, Oracle Server Manager was replaced by other tools such as Oracle Enterprise Manager (OEM) and Oracle Grid Control[2]. These newer tools provided more advanced features and a graphical user interface (GUI) for managing Oracle databases, making them more user-friendly and easier to use than OSM.
It is worth noting that while Oracle Server Manager is no longer supported by Oracle Corporation, there may still be organizations using the tool. However, these organizations should be aware that the software is no longer maintained and may contain security vulnerabilities or compatibility issues with newer versions of the Oracle database.
In summary, Oracle Server Manager was retired by Oracle Corporation in the Oracle Database 10g release, which was introduced in 2003. Since then, newer tools such as Oracle Enterprise Manager and "Oracle Grid Control" have replaced it as the primary tools for managing and monitoring Oracle databases.

Server Manager was Oracle's DBA tool prior to Oracle 9i

Server Manager was Oracle's database administration tool. The graphical component of Server Manager (Server Manager/GUI) allowed users to perform database administration tasks with the convenience of a graphical user interface (GUI). The line mode component of Server Manager (Server Manager/LineMode) provides a line mode interface.
It was used as a command-line utility to manage an Oracle database to perform the following operations:
  1. Starting a database
  2. Shutting down a database
  3. Running various Oracle-supplied DBA scripts
  4. Creating a database
  5. Executing SQL commands for the purpose of creating and managing users, space, tables, and other database objects.

'SQL Plus' and 'SQL *Plus'

SQL Plus and SQL*Plus are often used interchangeably, but they do not necessarily designate the same thing. SQL Plus is a command-line interface tool that allows users to interact with the Oracle Database Management System (DBMS). It is a component of Oracle's client-server software package and has been included with Oracle since its earliest versions.
SQL*Plus, on the other hand, is an enhanced version of SQL Plus that was introduced with Oracle 6 in the late 1980s. It adds several features to SQL Plus, including more advanced formatting options, the ability to execute SQL scripts, and improved error handling.
Despite their differences, many people use the terms SQL Plus and SQLPlus interchangeably because SQLPlus is simply an upgraded version of SQL Plus. However, it is worth noting that the enhanced features of SQL*Plus may not be available in all versions of Oracle, so it is important to check the specific version being used to ensure compatibility.
In conclusion, while SQL Plus and SQLPlus are related tools for working with Oracle databases, they are not exactly the same thing. SQLPlus is an upgraded version of SQL Plus that includes additional features, but both tools are often used interchangeably in casual conversation.

There was a GUI version of Server Manager available that was only supported under UNIX and only used X-Windows. The GUI version provided the following extra functionality:
  1. A real-time monitoring function
  2. GUI screens to create and modify users, tables, indexes, tablespaces, and so forth.

Oracle retired support for Server Manager with Oracle 10g and began promoting Enterprise Manager as the GUI tool of choice for managing Oracle databases. The command-line functionality of Server Manager was incorporated into SQL*Plus, and Server Manager is no longer shipped or supported in Oracle.

Server Manage no longer released beginning with Oracle 9i

Oracle Server Manager was officially retired by Oracle Corporation in the Oracle Database 10g, which was introduced in 2003. Oracle no longer supports Server Manager and it is recommended that you use
  1. SQL*Plus
  2. Oracle Application Express's SQL Workshop
  3. Query Builder
  4. SQL Workshop script editor
Support has dropped off for the GUI version, which was previously available for several platforms, including Windows. The GUI version of Server Manager is no longer supported under UNIX using X-Windows. Enterprise Manager is Oracle's strategic product for GUI-based management of Oracle Databases, so you would best spend your time learning Oracle Enterprise Manager.

Command-line Tool

DBAs need a command-line tool to use and Oracle recognizes this. With the release of Oracle8i, all the commands previously specific to Server Manager have been implemented in SQL*Plus. This is a good move on Oracle's part, because for the most part, the two products are redundant. Only a few commands are unique to Server Manager. Consolidating the two products gives Oracle one less tool to worry about maintaining, and it removes a source of potential confusion for users, as some DBA scripts are designed to run from SQL*Plus, and others are designed to run from Server Manager. Oracle's long-term strategy is to make SQL*Plus the only command-line interface for the Oracle database. Even though Server Manager is being phased out, don't worry about anything you are learning in this module becoming obsolete. All the Server Manager commands work the same in SQL*Plus as they do in Server Manager. In fact, if you happen to be running Oracle8i, you should try using SQL*Plus as you work through this module.
In this course we will be using SQL*Plus because it can be used with all current releases of Oracle. If you are using the latest version of Oracle, the same commands should work within your SQL*Plus environment.
[1]TNS Name: TNS Name (Transparent Network Substrate Name) is a user-defined alias for an Oracle database connection. It is used to simplify the process of connecting to a database, as it allows users to specify a single name instead of having to remember all of the necessary connection parameters, such as the hostname, port number, and service name.
[2]Oracle Grid Control:Oracle Grid Control is a comprehensive management platform for Oracle RDBMS. It provides a centralized console for monitoring, managing, and provisioning Oracle databases, as well as other Oracle products such as Oracle WebLogic Server and Oracle Fusion Middleware.

SEMrush Software 2SEMrush Software Banner 2