Sql plus как подключиться к бд
SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database installation. It has a command-line user interface.
There is also the SQL*Plus Instant Client which is a standalone command-line interface available on platforms that support the OCI Instant Client. SQL*Plus Instant Client connects to any available Oracle database, but does not require its own Oracle database installation. See the Oracle Call Interface Programmer’s Guide for more information on the OCI Instant Client.
SQL*Plus has its own commands and environment, and it provides access to the Oracle Database. It enables you to enter and execute SQL, PL/SQL, SQL*Plus and operating system commands to perform the following:
Format, perform calculations on, store, and print from query results
Examine table and object definitions
Develop and run batch scripts
Perform database administration
You can use SQL*Plus to generate reports interactively, to generate reports as batch processes, and to output the results to text file, to screen, or to HTML file for browsing on the Internet. You can generate reports dynamically using the HTML output facility of SQL*Plus.
Who Can Use SQL*Plus
The SQL*Plus, SQL, and PL/SQL command languages are powerful enough to serve the needs of users with some database experience, yet straightforward enough for new users who are just learning to work with the Oracle Database.
The SQL*Plus language is easy to use. For example, to rename a column labeled LAST_NAME with the heading «Family Name», enter the command:
Similarly, to list column definitions for the EMPLOYEES table, enter the command:
How Can I Learn SQL*Plus
There are several sources available to assist you to learn SQL*Plus:
Part II of this Guide, Using SQL*Plus
Help for SQL*Plus, Command-line help
Oracle Database 12c: SQL Fundamentals
An instructor-led course run by Oracle. This is a comprehensive hands-on course taking the student through all aspects of using SQL*Plus to access Oracle Database.
More Oracle Database 12c Training
To find more useful Oracle courses, go to http://www.oracle.com/education .
How to Use the SQL*Plus Guide
This guide provides information about SQL*Plus that applies to all operating systems. It also includes some Windows and UNIX specific information. Some aspects of SQL*Plus may differ on each operating system. Operating system specific details are covered in the Oracle Database Installation Guide provided for your system. Use these operating system specific guides with this SQL*Plus User’s Guide and Reference .
Throughout this guide, examples showing how to enter commands use a common command syntax and a common set of sample tables. The tables are described in «About Sample Schemas and SQL*Plus» .
SQL*Plus Command-line Architecture
SQL*Plus command-line uses a two-tier model comprising:
Client (command-line user interface).
Database (Oracle Database).
The two tiers may be on the same computer.
SQL*Plus Client
The command-line user interface is the character-based terminal implementation.
Oracle Database
Oracle Database Net components provide communication between the SQL*Plus Client and Oracle Database.
SQL*Plus Prerequisites
SQL*Plus is a component of Oracle Database. SQL*Plus is installed by default when you install the Oracle Database.
Some aspects of Oracle Database and SQL*Plus differ from one computer and operating system to another. These topics are discussed in the Oracle Database Installation Guide for each operating system that SQL*Plus supports.
What is necessary before you can run SQL*Plus?
Install Oracle Database or Oracle Client. See the Oracle Database Installation Guide for your operating system available at http://www.oracle.com/technetwork/ .
Obtain an Oracle Database login username and password during installation or from your Database Administrator. See Login Username and Password.
Ensure a sample database is installed and that you have a login username and password for it. See About Sample Schemas and SQL*Plus.
Create a default database during installation or obtain the connection identifier for the Oracle Database you want to connect to from your Database Administrator. See About Connecting to a Database.
Ensure the database you want to connect to is started. See the STARTUP command.
SQL*Plus Date Format
The default date format in SQL*Plus is determined by the database NLS_DATE_FORMAT parameter and may use a date format displaying two digit years. You can use the SQL TO_CHAR function, or the SQL*Plus COLUMN FORMAT command in your SELECT statements to control the way dates are displayed in your report.
Starting SQL*Plus Command-line
The SQL*Plus executable is usually installed in $ORACLE_HOME /bin, which is usually included in your operating system PATH environment variable. You may need to change directory to the $ORACLE_HOME /bin directory to start SQL*Plus.
In the following examples, you are prompted to enter the database account password.
An example using an Easy Connection identifier to connect to the HR schema in the MYDB database running on mymachine is:
An example using a Net Service Name is:
Net Service Names can be stored in several places, including Oracle Names. See the Net Services Reference Guide for more information.
If you want to use Net Service Names configured in a local Oracle Net tnsnames.ora file, then set the environment variable TNS_ADMIN to the directory containing the tnsnames.ora file. For example, on UNIX, if your tnsnames.ora file is in /home/user1 and it defines the Net Service Name MYDB2:
This example assumes the ORACLE_HOME environment variable is set, and the $ORACLE_HOME/network/admin/tnsnames.ora or ORACLE_HOME\network\admin\tnsnames.ora file defines the Net Service Name MYDB3:
The TWO_TASK (on UNIX) or LOCAL (on Windows) environment variable can be set to a connection identifier. This removes the need to explicitly enter the connection identifier whenever a connection is made in SQL*Plus or SQL*Plus Instant Client. This UNIX example connects to the database known as MYDB4:
To start SQL*Plus and connect to the default database
- Open a UNIX or a Windows terminal and enter the SQL*Plus command:
You are prompted to enter your password.
Now you can start entering and executing SQL, PL/SQL and SQL*Plus statements and commands at the SQL> prompt.
Example — To start SQL*Plus and connect to a database other than the default
Open a UNIX or a Windows terminal and enter the SQL*Plus command:
You are prompted to enter your password.
About Starting SQL*Plus Instant Client
SQL*Plus Instant Client is the SQL*Plus command-line without the need to install Oracle Database. For information about using it, see Starting SQL*Plus Command-line.
Because SQL*Plus Instant Client does not include a database, it is always ‘remote’ from any database server. To connect to a database you must specify the database using an Oracle Net connection identifier.
If TNS_ADMIN is not set, then an operating system dependent set of directories is examined to find tnsnames.ora. This search path includes looking in the directory specified by the ORACLE_HOME environment variable for network/admin/tnsnames.ora. This is the only reason to set the ORACLE_HOME environment variable for SQL*Plus Instant Client. If ORACLE_HOME is set when running Instant Client applications, it must be set to a directory that exists.
About Connecting to a Different Database
From an existing command-line session, enter a CONNECT command in the form:
You are prompted to enter your password.
About Sample Schemas and SQL*Plus
Sample schemas are no longer included with the Oracle Database. Starting from Oracle Database 21c, you need to download sample schemas from the GitHub repository. Examples in this guide use the EMP_DETAILS_VIEW view of the Human Resources (HR) sample schema. This schema contains personnel records for a fictitious company. To view column details for the view, EMP_DETAILS_VIEW, enter
For more information about the sample schemas, see the Oracle Database Sample Schemas guide.
Unlocking the Sample Tables
The Human Resources (HR) Sample Schema is not installed as part of the default Oracle Database installation. Starting from Oracle Database 21c, you need to download the sample schemas from the GitHub repository. The HR account is locked by default.
You must unlock the HR account before you can use the HR sample schema. To unlock the HR account, log in as the SYSTEM user and enter the following command, where your_password is the password you want to define for the user HR:
For further information about unlocking the HR account, see the Oracle Database Sample Schemas guide. The HR user is primarily to enable you to access the HR sample schema and is necessary to enable you to run the examples in this guide.
Each table in the database is «owned» by a particular user. You may want to have your own copies of the sample tables to use as you try the examples in this guide. To get your own copies of the HR tables , see your DBA or see the Oracle Database Sample Schemas guide, or you can create the HR tables with the script HR_MAIN.SQL which is located in the following directory on UNIX:
And on the following directory on Windows:
To create the HR tables from command-line SQL*Plus, do the following:
- Ask your DBA for your Oracle Database account username and password.
- Login to SQL*Plus.
- On UNIX, enter the following command at the SQL*Plus prompt:
On Windows, enter the following command at the SQL*Plus prompt:
To remove the sample tables, perform the same steps but substitute HR_DROP.SQL for HR_MAIN.SQL.
Running your first Query
To describe a database object, for example, column details for EMP_DETAILS_VIEW, enter a DESCRIBE command like:
which produces the following output:
Description of the illustration describe-gif-guid-52bcb96b-39ab-4ac8-b94d-f08d7eb2691a.png
To rename the column headings, and to select data from the HR sample schema view, EMP_DETAILS_VIEW, enter
which produces the following output:
Description of the illustration selectout-gif-guid-b54d33f6-f484-4326-b036-d67785a7f82c.png
Connect to Oracle DB using sqlplus
I am using below command in Unix environment to connect to Oracle database:
But I am getting below error:
Please help me where I am doing mistake in using the command.
8 Answers 8
try this: sqlplus USER/PW@//hostname:1521/SID
Different ways to connect Oracle Database from Unix user are:
Easy way (using XE):
1). Configure your tnsnames.ora
You can replace HOST.DOMAIN.COM with IP address, the TCP port by default is 1521 (ckeck it) and look that name of this configuration is XE
2). Using your app named sqlplus:
SYSTEM should be replaced with an authorized USER, and put your password when prompt appear
3). See at firewall for any possibilities of some blocked TCP ports and fix it if appear
Sql plus как подключиться к бд
This chapter describes how to start, login, and connect to a database, how to get help, and how to exit SQL*Plus.
Specific topics discussed are:
3.1 Login Username and Password
When you start SQL*Plus, you need a username and password to login to an Oracle Database schema. Your username and password identify you as an authorized user of the Oracle Database schema.
The database administrator (DBA) is responsible for creating your database account with the necessary privileges and giving you the username and password that enables you to access your account.
Default logins are created and you are prompted for associated passwords during Oracle Database installation. Some of the default login usernames created are:
Logins are created and displayed in messages during Oracle Database installation.
For further information about the default logins, see Types of Oracle Database Users.
Once you have logged in, you can connect under a different username using the CONNECT command. The username and password must be valid for the database. For example, to connect the username TODD to the default database using the password FOX, you could enter
You are prompted to enter the password, FOX.
In the command-line interface, if you omit the username and password, SQL*Plus prompts you for them. Because CONNECT first disconnects you from your current database, you will be left unconnected to any database if you use an invalid username and password in your CONNECT command.
If you log on or connect as a user whose account has expired, you are prompted to change your password before you can connect.
If an account is locked, a message is displayed and connection as this user is not permitted until the account is unlocked by your DBA.
You can use the DISCONNECT command to disconnect from a database without leaving SQL*Plus.
3.1.1 Secure External Password Store
As a command-line alternative for large-scale deployments where applications use password credentials to connect to databases, it is possible to store such credentials in a client-side Oracle wallet. An Oracle wallet is a secure software container that is used to store authentication and signing credentials.
Storing database password credentials in a client-side Oracle wallet eliminates the need to embed usernames and passwords in application code, batch jobs, or scripts. This reduces the risk of exposing passwords in the clear in scripts and application code, and simplifies maintenance because you need not change your code each time usernames and passwords change. In addition, not having to change application code also makes it easier to enforce password management policies for these user accounts.
When you configure a client to use the external password store, applications can use the following syntax to connect to databases that use password authentication:
Note that you need not specify database login credentials in this CONNECT statement. Instead your system looks for database login credentials in the client wallet.
Oracle Database Administrator’s Guide for information about configuring your client to use secure external password store and for information about managing credentials in it.
3.1.2 Expired Password
In the command-line interface, if your password has expired, SQL*Plus prompts you to change it when you attempt to log in. You are logged in once you successfully change your password.
3.1.3 About Changing your Password
In the command-line interface, you can change your password with the PASSWORD command. See PASSWORD.
3.2 About Connecting to a Database
You must connect to an Oracle Database (instance) before you can query or modify data in that database. You can connect to the default database and to other databases accessible through your network. To connect to another database over a network, both databases must have Oracle Net configured, and have compatible network drivers. You must enter either a connection identifier or a net service name to connect to a database other than the default.
The connection identifier or net service name is entered:
as an argument to the SQLPLUS Program Syntax when starting a command-line session.
as an argument to the CONNECT command from a current session. For detailed usage, see Accessing a Container in a CDB in the Oracle Database Administrator’s Guide .
3.2.1 Net Service Name
Your DBA is responsible for creating the databases you use and defining net service names for them in the tnsnames.ora file.
A net service name definition in the tnsnames.ora file has the syntax:
To use a net service name (alias), it must have an entry in the tnsnames.ora file on the machine running SQL*Plus. An entry in tnsnames.ora is not required if you use a connection identifier.
Example 3-1 The tnsnames.ora entry for the sales database
Example 3-2 Start a command-line session to the sales database using the net service name
See Configuration Parameters and Configuration and Administration of Oracle Net Services for more information about database connections and net service name definitions.
3.2.2 Full Connection Identifier
Depending on your configuration, use the full connection identifier syntax like:
The SERVICE_NAME is the global database name entered during database creation. It combines a database name with a domain name. For example, the SERVICE_NAME sales.us.acme.com has a database name of sales and a domain of us.acme.com .
An INSTANCE_NAME is the name you give to the database instance during creation. It defaults to the SID you entered during database creation.
An Oracle System Identifier ( SID ) identifies a specific Oracle release 8.0 database instance.
You can optionally use an INSTANCE_NAME in place of the SERVICE_NAME phrase.
Use a SID in place of the SERVICE_NAME when connecting to an Oracle release 8.0 or earlier database.
Example 3-3 Full connection identifier for SALES1
3.2.3 Easy Connection Identifier
The easy or abbreviated connection identifier has the syntax:
Example 3-4 Start a command-line session to the sales database using the easy connection identifier
Example 3-5 CONNECT to the sales database using the easy connection identifier
When the password is omitted, the connect string needs to be quoted.
The easy connection identifier can be used wherever you can use a full connection identifier, or a net service name. The easy syntax is less complex, and no tnsnames.ora entry is required.
Starting with Oracle Database release 19c, the easy connect syntax has improved functionality and is called Easy Connect Plus. For more information, see About Easy Connect Plus.
3.2.4 Connectionless Session with /NOLOG
In the command-line interface, it is possible to start SQL*Plus without connecting to a database. This is useful for performing some database administration tasks, writing transportable scripts, or to use SQL*Plus editing commands to write or edit scripts.
You use the /NOLOG argument to the SQLPLUS command to start a connectionless command-line session. After SQL*Plus has started you can connect to a database with the CONNECT command.
Example 3-6 Start a connectionless SQL*Plus session with /NOLOG
3.3 About Starting SQL*Plus
If you are connecting to a remote Oracle database, make sure your Oracle Net software is installed and working properly. For more information, see Testing and Troubleshooting Oracle Net Services.
When you start a SQL*Plus command-line session, and after a CONNECT command in that session, the site profile, glogin.sql , and the user profile file, login.sql , are processed:
After SQL*Plus starts and connects, and prior to displaying the first prompt.
After SQL*Plus starts and connects, and prior to running a script specified on the command line.
Prior to the first prompt when /NOLOG is specified on the command line and no connection is made.
The site profile file, glogin.sql is processed first, then the user profile file, login.sql .
3.3.1 About Starting Command-line SQL*Plus
To begin using SQL*Plus, you must first understand how to start and stop SQL*Plus.
Make sure that SQL*Plus has been installed on your computer.
Log on to the operating system (if required).
Enter the command, SQLPLUS , and press Return.
Some operating systems expect you to enter commands in lowercase letters. If your system expects lowercase, enter the SQLPLUS command in lowercase.
SQL*Plus displays its version number, the current date, and copyright information, and prompts you for your username (the text displayed on your system may differ slightly):
Enter your username and press Return. SQL*Plus displays the prompt «Enter password:».
Enter your password and press Return again. For your protection, your password does not appear on the screen.
The process of entering your username and password is called logging in . SQL*Plus displays the version of Oracle Database to which you connected and the versions of available tools such as PL/SQL, and the local time of the last time you logged on.
Next, SQL*Plus displays the SQL*Plus command prompt :
The SQL*Plus command prompt indicates that SQL*Plus is ready to accept your commands.
If SQL*Plus does not start, you should see a message to help you correct the problem.
Example 3-7 Starting SQL*Plus
This example shows you how to start SQL*Plus :
3.3.2 About Getting Command-line Help
To access command-line help for SQL*Plus commands, type HELP or ? followed by the command name at the SQL command prompt or in the i SQL*Plus Workspace Input area. See the HELP command for more information. For example:
To display a list of SQL*Plus commands, type HELP followed by either TOPICS or INDEX. HELP TOPICS displays a single column list of SQL*Plus commands. HELP INDEX displays a four column list of SQL*Plus commands which fits in a standard screen. For example:
3.4 About Exiting SQL*Plus Command-line
If you cannot log in to SQL*Plus because your username or password is invalid or for some other reason, SQL*Plus returns an error status equivalent to an EXIT FAILURE command. See the EXIT command for further information.
When you are done working with SQL*Plus and wish to return to the operating system, enter EXIT or QUIT at the SQL*Plus prompt, or enter the end of file character, Ctrl+D on UNIX or Ctrl+Z on Windows.
SQL*Plus displays the version of Oracle Database from which you disconnected and the versions of tools available through SQL*Plus before you return to the operating system prompt.
3.5 SQL*Plus Program Syntax
You use the SQLPLUS command at the operating system prompt to start command-line SQL*Plus :
where: Options has the following syntax:
and markup_option consists of:
csv_option has the following syntax:
html_option has the following syntax:
where Logon has the following syntax:
where Start has the following syntax:
Including your password in plain text is a security risk. You can avoid this risk by omitting the password, and entering it only when the system prompts for it.
You have the option of entering logon. If you do not specify logon but do specify start, SQL*Plus assumes that the first line of the script contains a valid logon. If neither start nor logon are specified, SQL*Plus prompts for logon information.
3.5.1 Options
The following sections contain descriptions of SQLPLUS command options:
3.5.1.1 HELP Option
Displays the usage and syntax for the SQLPLUS command, and then returns control to the operating system.
3.5.1.2 VERSION Option
Displays the current version and level number for SQL*Plus, and then returns control to the operating system.
3.5.1.3 COMPATIBILITY Option
Sets the value of the SQLPLUSCOMPATIBILITY system variable to the SQL*Plus release specified by x . y [. z ]. Where x is the version number, y is the release number, and z is the update number. For example, 9.0.1 or 10.2. For more information, see the SET SQLPLUSCOMPAT[IBILITY] system variable.
3.5.1.4 LOGON Option
Specifies not to reprompt for username or password if the initial connection does not succeed. This can be useful in operating system scripts that must either succeed or fail and you don’t want to be reprompted for connection details if the database server is not running.
3.5.1.5 FAST Option
The FAST option improves general performance. This command line option changes the values of the following default settings:
3.5.1.6 MARKUP Options
You can use the MARKUP options to generate output in HTML or CSV (Character Separated Values) format, through a query or script.
MARKUP currently supports HTML 4.0 transitional, and the CSV format.
Use SQLPLUS -MARKUP to produce output in HTML or CSV format.
Depending on your operating system, the complete markup_option clause for the SQL*PLUS command may need to be contained in quotes.
For HTML output, use SQLPLUS -MARKUP HTML ON or SQLPLUS -MARKUP HTML ON SPOOL ON to produce standalone web pages. SQL*Plus will generate complete HTML pages automatically encapsulated with <HTML> and <BODY> tags. The HTML tags in a spool file are closed when SPOOL OFF is executed or SQL*Plus exits.
The -SILENT and -RESTRICT command-line options may be useful when used in conjunction with -MARKUP.
You can use MARKUP HTML ON to produce HTML output in either the <PRE> tag or in an HTML table. Output to a table uses standard HTML <TABLE>, <TR> and <TD> tags to automatically encode the rows and columns resulting from a query. Output to an HTML table is the default behavior when the HTML option is set ON. You can generate output using HTML <PRE> tags by setting PREFORMAT ON.
For CSV output, use SQLPLUS -MARKUP CSV ON to produce output in CSV format. You can specify the delimiter character by using the DELIMITER option. You can also output text without quotes by using QUOTE OFF.
Use the SHOW MARKUP command to view the status of MARKUP options.
The SQLPLUS -MARKUP command has the same functionality as the SET MARKUP command. These options are described in this section. For other information on the SET MARKUP command, see the SET command.
CSV is a mandatory MARKUP argument which specifies that the type of output to be generated is CSV. The optional CSV arguments, ON and OFF, specify whether or not to generate CSV output. The default is OFF. You can turn CSV output ON and OFF as required during a session.
HTML is a mandatory MARKUP argument which specifies that the type of output to be generated is HTML. The optional HTML arguments, ON and OFF, specify whether or not to generate HTML output. The default is OFF.
MARKUP HTML ON generates HTML output using the specified MARKUP options.
You can turn HTML output ON and OFF as required during a session.
The HEAD text option enables you to specify content for the <HEAD> tag. By default, text includes a default in-line cascading style sheet and title.
If text includes spaces, it must be enclosed in quotes. SQL*Plus does not test this free text entry for HTML validity. You must ensure that the text you enter is valid for the HTML <HEAD> tag. This gives you the flexibility to customize output for your browser or special needs.
The BODY text option enables you to specify attributes for the <BODY> tag. By default, there are no attributes. If text includes spaces, it must be enclosed in quotes. SQL*Plus does not test this free text entry for HTML validity. You must ensure that the text you enter is valid for the HTML <BODY> tag. This gives you the flexibility to customize output for your browser or special needs.
The TABLE text option enables you to enter attributes for the <TABLE> tag. You can use TABLE text to set HTML <TABLE> tag attributes such as BORDER, CELLPADDING, CELLSPACING and WIDTH. By default, the <TABLE> WIDTH attribute is set to 90% and the BORDER attribute is set to 1.
If text includes spaces, it must be enclosed in quotes. SQL*Plus does not test this free text entry for HTML validity. You must ensure that the text you enter is valid for the HTML <TABLE> tag. This gives you the flexibility to customize output for your browser or special needs.
ENTMAP ON or OFF specifies whether or not SQL*Plus replaces special characters <, >, » and & with the HTML entities <, >, " and & respectively. ENTMAP is set ON by default.
You can turn ENTMAP ON and OFF as required during a session. For example, with ENTMAP OFF, SQL*Plus screen output is:
With ENTMAP ON, SQL*Plus screen output is:
As entities in the <HEAD> and <BODY> tags are not mapped, you must ensure that valid entities are used in the MARKUP HEAD and BODY options.
If entities are not mapped, web browsers may treat data as invalid HTML and all subsequent output may display incorrectly. ENTMAP OFF enables users to write their own HTML tags to customize output.
ENTMAP only takes effect when the HTML option is set ON. For more information about using entities in your output, see the COLUMN command.
SPOOL ON or OFF specifies whether or not SQL*Plus writes the HTML opening tags, <HTML> and <BODY>, and the closing tags, </BODY> and </HTML>, to the start and end of each file created by the SQL*Plus SPOOL filename command. The default is OFF.
You can turn SPOOL ON and OFF as required during a session.
It is important to distinguish between the SET MARKUP HTML SPOOL option, and the SQLPLUS SPOOL filename command.
The SET MARKUP HTML SPOOL ON option enables the writing of the <HTML> tag to the spool file. The spool file is not created, and the header and footer tags enabled by the SET MARKUP HTML SPOOL ON option are not written to the spool file until you issue the SQLPLUS SPOOL filename command . See the SPOOL command for more information.
SQL*Plus writes several HTML tags to the spool file when you issue the SPOOL filename command.
When you issue any of the SQL*Plus commands: EXIT, SPOOL OFF or SPOOL filename, SQL*Plus appends the following end tags and closes the file:
You can specify <HEAD> tag contents and <BODY> attributes using the HEAD and BODY options
PREFORMAT ON or OFF specifies whether or not SQL*Plus writes output to the <PRE> tag or to an HTML table. The default is OFF, so output is written to a HTML table by default. You can turn PREFORMAT ON and OFF as required during a session.
To produce report output using the HTML <PRE> tag, you must set PREFORMAT ON. For example:
SQLPLUS -M «HTML ON PREFORMAT ON»
SET MARKUP HTML ON PREFORMAT ON
3.5.1.7 MARKUP Usage Notes
When MARKUP HTML ON PREFORMAT OFF is used, commands originally intended to format paper reports have different meaning for reports intended for web tables:
PAGESIZE is the number of rows in an HTML table, not the number of lines. Each row may contain multiple lines. The TTITLE, BTITLE and column headings are repeated every PAGESIZE rows.
LINESIZE may have an effect on data if wrapping is on, or for very long data. Depending on data size, output may be generated on separate lines, which a browser may interpret as a space character.
TTITLE and BTITLE content is output to three line positions: left, center and right, and the maximum line width is preset to 90% of the browser window. These elements may not align with the main output as expected due to the way they are handled for web output. Entity mapping in TTITLE and BTITLE is the same as the general ENTMAP setting specified in the MARKUP command.
If you use a title in your output, then SQL*Plus starts a new HTML table for output rows that appear after the title. Your browser may format column widths of each table differently, depending on the width of data in each column.
SET COLSEP, RECSEP and UNDERLINE only produce output in HTML reports when PREFORMAT is ON.
When MARKUP CSV ON is used, output from a query will be displayed in CSV format.
You can enable CSV markup while logging into a user session, by using the -M[ARKUP] CSV ON option at the SQL*Plus command line. For more information, see SQL*Plus Program Syntax. While logged in to a user session, you can enable CSV markup by using the SET MARKUP CSV ON command.
You can specify the delimiter character by using the DELIMITER option. You can also output text without quotes by using QUOTE OFF.
3.5.1.8 No Login Time Option
The last login time for non-SYS users is displayed when you log on. This feature is on by default. The last login time is displayed in local time format. You can use the -nologintime option to disable this security feature. After you login, the last login information is displayed
Last login time does not show when making a connection with the CONNECT command.
3.5.1.9 RESTRICT Option
Enables you to disable certain commands that interact with the operating system. This is similar to disabling the same commands in the Product User Profile (PUP) table. However, commands disabled with the -RESTRICT option are disabled even if there is no connection to a server, and remain disabled until SQL*Plus terminates.
If no -RESTRICT option is active, than all commands can be used, unless disabled in the PUP table.
Using SQL*Plus and Oracle Enterprise Manager
There are many ways to connect and work with Oracle databases, but most often the interface and SQL*Plus command set offered in Oracle are used for this purpose. The SQL*Plus interface, in fact, opens a window to an Oracle database and is therefore very widely used by Oracle developers to create SQL and PL/SQL program units. For Oracle database administrators, this interface is also a very valuable tool for the following reasons.
- It allows us to execute queries in SQL language and blocks of code in PL/SQL language (which is an advanced procedural version of SQL language offered in Oracle) and get results.
- It allows us to execute commands related to database administration and automate them.
- It allows you to start and finish the database operation.
- It provides a convenient way to create reports on database administration.
In this article, I will start talking about how to use SQL*Plus to perform typical Oracle database administration tasks, about important SQL*Plus commands, and, in brief, how to create reports using SQL*Plus. You may not need to use the SQL*Plus interface to create most reports, but knowing how its numerous tools for report generation work is not a problem at all.
START THE SQL*PLUS SESSION
The SQL*Plus interface is a utility that is most often used to connect and work with Oracle databases. It is supplied as part of the Oracle Database 11g server software as well as the Oracle Client software and the new Oracle Instant Client software.
After installing SQL*Plus on a server or client machine, the process of connecting to the server or client and starting an SQL*Plus session looks very simple. Because each SQL*Plus session involves establishing a database connection (unless the /NOLOG parameter is applied), all that is required to start SQL*Plus and connect to the database is a valid username and password.
CONFIGURING THE ENVIRONMENT
Before calling SQL*Plus, you need to correctly configure the Oracle environment, in particular, you need to set values for environment variables such as ORACLE_SID, ORACLE_HOME, and LD_LIBRARY_PATH. In addition, sometimes it is necessary to set values for such environment variables as NLS_LANG and ORA_NLS11.
If the correct values are not set for the required environment variables, an error will occur. For example, if you do not set the correct value for ORACLE_HOME before starting SQL*Plus, the following error will occur:
$ sqlplus
Error 6 initializing SQL*Plus
Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
Error 6 on SQL*Plus initialization
No sp1.msb file could be found.
SP2-0750: It may be necessary to specify a directory in ORACLE_HOME where
Oracle software installed
In case of the above error it is enough to set the value for the environment variable ORACLE_HOME:
$ export ORACLE_HOME= /u01/app/oracle/product/11.1.0/db_1
SQL*PLUS INSTANT CLIENT SOFTWARE
To use SQL*Plus to install the entire server software Oracle Database is not necessary. If you need to communicate via the SQL*Plus interface with a database that is located on another server, the SQL*Plus Instant Client software is enough.
With this software, you can connect to any Oracle database running on any operating system remotely by specifying its name and applying the Oracle network connection identifier. The only requirement to connect to a remote database in such a way is to specify the name of the remote database in the file tnsnames.ora.
That is why for SQL*Plus Instant Client it is required to specify the ORACLE_HOME environment variable. There is also a method that does not require the application of the tnsnames.ora file on the client-server. It is called the easy connect method. Below is an example showing how to use a simple connection ID to establish a connection on behalf of an OE user to the testdb database located on the server:
In this example, 1521 is the port used by the listener to receive connection requests.
STARTING AN SQL*PLUS SESSION FROM THE COMMAND LINE
Before connecting to an SQL*Plus session, you must first correctly configure the environment and specify which database on the server should be connected by default. This is done with the environment variable ORACLE_SID.
For example:
$ ORACLE_SID=orcl.
$ export ORACLE_SID
After specifying the database to be used by default (in the example above it is orcl) in the ORACLE_SID environment variable, you can access SQL*Plus from the command line invitation by simply entering the command sqlplus without a username and password. SQL*Plus will prompt you for a username and password. If a username is provided along with a command (e.g.: sqlplus salapati), SQL*Plus will prompt you to enter only the password. The database administrator shall log in on behalf of one of the administrative accounts.
Please, note! On UNIX servers, the entry shall always be in lower case. In Windows, the interface is not case sensitive. Except for this little detail, in all other respects, the command interface SQL*Plus works the same on Windows and all versions of UNIX and Linux platforms.
Of course, you can also enter a username and password directly when you call SQL*Plus, but then the password will be visible to others when you enter it. An example is given below:
$ sqlplus salapati/sammyy1
SQL>
Invitation to SQL (SQL>) means that connection to SQL*Plus is initiated and you can start typing SQL, PL/SQL and SQL*Plus commands and operators.
In order to connect to a different database than the default one, the following command shall be used:
Certain operations, such as starting and stopping work, are allowed to be performed only if you connect to SQL*Plus with SYSDBA or SYSOPER privileges. If you have SYSDBA (or SYSOPER) privileges, you can connect to SQL*Plus as follows:
$ sqlplus sys/sammyy1 AS SYSDBA
SQL> SHO USER
USER is "SYS"
SQL>
The AS design allows privileged connections to users who have been granted SYSDBA or SYSOPER system privileges.
If an authenticated operating system user account has been created in the database (formerly called OPS$ name; see Chapter 12), the connection can be established by simply slash (/) as shown below:
$ sqlplus /
SQL> SHO USER
USER is "OPS$ORACLE"
SQL>
You can also connect through the operating system authentication method by including the owner of Oracle software in the Database Administrators group (DBA):
$ sqlplus / AS SYSDBA
SQL> SHO USER
USER is "SYS"
SQL>
Please note that in all previous examples the database name was not specified when connecting via SLQ*Plus. This is explained by the fact that the connection was set to the default instance, i.e. the database indicated by the ORACLE_SID environment variable.
The database name need not be specified when using SQL*Plus to connect to the default database. To connect to another database accessible via the network, the connection identifier (network service name) shall be used.
For the note! The instance name, database name and service name may be the same or different.
From a theoretical point of view, you can connect to a database using the full syntax of the connection identifier, as shown in the following example, where the entire address is used to connect to the orcl database:
$ sqlplus salapati/sammyy1@(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)
(CONNECT_DATA= (SERVICE_NAME=orcl.mycompany.com))
However, by using the network service name defined in the network file tnsnames.ora, you can connect to the database in a simpler way:
In addition, a simple connection method may be used to connect to the database. The syntax of the simple connection method looks like this:
For example, here is how to connect to the orcl database using this method:
Note that in case of a simple connection method, you do not need a network file (tnsnames.ora).
Whichever of the above methods is used, the SQL*Plus session will eventually be successfully installed either with the default database or with the one specified in the connection identifier.
SETTING UP A CONNECTION USING THE CONNECT COMMAND
SQL*Plus supports the CONNECT command, which allows you to connect on behalf of another user after logging into SQL*Plus. The following is an example of using the CONNECT command to make a connection on behalf of another user:
SQL> CONNECT new_user/password.
Connected.
SQL>
The following example shows how to connect to another database in SQL*Plus by providing the connection identifier as part of the CONNECT command:
SQL> CONNECT salapati/sammyy1@orcl
Connected.
SQL>
Before connecting to another database, make sure that the tnsnames.ora file contains the necessary information about connecting to the remote database.
The CONNECT command can be used in SQL*Plus together with / AS SYSDBA and / AS SYSOPER syntax as shown below:
CONNECT sys/sammy1@prod1 as sysdba
CONNECT / AS SYSDBA
CONNECT user/password AS SYSDBA
CONNECT / AS SYSOPER
CONNECT user/password AS SYSOPER
Start the SQL*Plus session without establishing a connection to the database using the /NOLOG parameter.
The SQL*Plus session can also be started without establishing a database connection by specifying the /NOLOG parameter together with the command sqlplus.
This may be necessary, for example, when starting a database or simply to use the commands available in SQL*Plus to write or edit scripts. After starting the SQL*Plus session, the CONNECT command can always be used to connect to the database.
Below is an example of using the /NOLOG parameter:
$ sqlplus /NOLOG
SQL*Plus: Release 11.1.0.6.0 — Production on Wed Jan 2 18:35:25 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> SHO USER
USER is "
"
SQL> SHO SGA
SP2-0640: Not connected
SQL> CONNECT salapati/sammyy1
Connected.
SQL>
Connecting to SQL*Plus via Windows GUI
If you use the SQL*Plus GUI on your Windows machine, just click the SQL*Plus icon to start the SQL*Plus session and you will be prompted to enter your user name. Provided that the connection to the database is established through the corresponding entities in the tnsnames.ora file, you can start working with the SQL*Plus interface after entering the user name.
You can work with the SQL*Plus utility in either manual or scripted noninteractive mode. It goes without saying that vulnerable administrative tasks, such as database recovery, are better performed in the interactive mode. As for routine SQL processing operations, it is better to automate their execution with the help of scripts. In both cases, the commands themselves will look the same — the only difference will be the mode in which they are executed.
The syntax of the SQL*Plus connection command is shown below:
Just for the record! In Oracle Database 11g, the SQLPLUS command supports the new argument -F, allowing SQL*Plus to receive FAN events (Fast Application Notification) from the RAC database.
Connecting on behalf of a user with SYSOPER, SYSDBA or SYSASM privileges is necessary to perform privileged operations such as database shutdown and startup or database backup or restore. SYSAM privilege is new in Oracle Database 11g and is designed to separate the usual database administration operations from the Automatic Storage Management (ASM).
WORKING IN SQL*PLUS
After connecting to the SQL*Plus interface, you can start typing any SQL*Plus, SQL or PL/SQL commands in it. As will be explained later in this chapter, SQL statements end either with a semicolon (;) or with a slash (/) and PL/SQL code blocks end only with a slash (/). The output can either be viewed on the screen or written to a file if you wish. SQL*Plus commands always end with a newline character. When entering an SQL*Plus command, the SQL*Plus client program parses it and if it is an SQL or PL/SQL statement, sends it to the database server for processing.