Release 8.1.7
Part Number A82950-01
Library |
Product |
Contents |
Index |
| SQL*Plus
User's Guide and Reference
Release 8.1.7 Part Number A82950-01 |
|
This chapter explains how to access databases through SQL*Plus, and discusses the following topics:
Read this chapter while sitting at your computer and try
out the example shown. Before beginning, make sure you have access to the
sample tables described in Chapter 1.
In order to access data in a given database, you must first
connect to the database. When you start SQL*Plus, you normally connect
to your default Oracle database under the username and password you enter
while starting. Once you have logged in, you can connect under a different
username with the SQL*Plus 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
CONNECT TODD/FOX
If you omit the username and password, SQL*Plus prompts you
for them. You also have the option of typing only the username following
CONNECT and omitting the password (SQL*Plus then prompts for the password).
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,
SQL*Plus prompts you 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 disconnect the username currently connected to Oracle
without leaving SQL*Plus by entering the SQL*Plus command DISCONNECT at
the SQL*Plus command prompt.
The default database is configured at an operating system
level by setting operating system environment variables, symbols or, possibly,
by editing an Oracle specific configuration file. Refer to your Oracle
documentation for your operating system for more information.
Many large installations run Oracle on more than one computer.
Such computers are often connected in a network, which permits programs
on different computers to exchange data rapidly and efficiently. Networked
computers can be physically near each other, or can be separated by large
distances and connected by telecommunication links.
Databases on other computers or databases on your host computer
other than your default database are called remote databases. You
can access remote databases if the desired database has Net8 and both databases
have compatible network drivers.
You can connect to a remote database in one of two ways:
To connect to a remote database using CONNECT, include a Net8 database specification in the CONNECT command in one of the following forms (the username and password you enter must be valid for the database to which you wish to connect):
SQL*Plus prompts you for a password as needed, and connects
you to the specified database.
Like any database connection, if you log on or connect as
a user whose account has expired, SQL*Plus prompts you 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.
When you connect to a remote database in this manner, you
can use the complete range of SQL and SQL*Plus commands and PL/SQL blocks
on the database.
The exact string you enter for the service name depends upon
the Net8 protocol your computer uses. For more information, see CONNECT
in Chapter 8 and the Net8 manual appropriate
for your protocol, or contact your DBA.
To connect to a remote database when you start SQL*Plus, include the Net8 service name in your SQLPLUS command in one of the following forms:
You must use a username and password valid for the remote
database and substitute the appropriate service name for the remote database.
SQL*Plus prompts you for username and password as needed, starts SQL*Plus,
and connects you to the specified database. This is the database used until
you CONNECT to another database, DISCONNECT, or leave SQL*Plus.
Like any database connection, if you log on or connect as
a user whose account has expired, SQL*Plus prompts you 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.
Once again, you can manipulate tables in the remote database
directly after you connect in this manner.
|
Note: Do not confuse the @ symbol of the connect identifier with
the @ command used to run a command file.
|
Use the SQL*Plus COPY command to copy data between databases and between tables on the same database. With the COPY command, you can copy data between databases in the following ways:
You enter the COPY command in the following form:
SQL> COPY FROM database TO database action - > destination_table (column_name, column_name, - > column_name ...) USING query
Here is a sample COPY command:
SQL> COPY FROM SCOTT/TIGER@BOSTONDB - > TO TODD/FOX@CHICAGODB - > CREATE NEWDEPT (DNUMBER, DNAME, CITY) - > USING SELECT * FROM DEPT
To specify a database in the FROM or TO clause, you must
have a valid username and password for the local and remote database(s)
and know the appropriate Net8 service name(s). COPY obeys Oracle security,
so the username you specify must have been granted access to tables for
you to have access to tables. For information on what databases are available
to you, contact your DBA.
When you copy to your local database from a remote database,
you can omit the TO clause. When you copy to a remote database from your
local database, you can omit the FROM clause. When you copy between remote
databases, you must include both clauses. However, including both clauses
benefits the readibility of your scripts.
The COPY command behaves differently based on whether the
destination table already exists and on the action clause you enter (CREATE
in the example above). For more information, see the section "Controlling
Treatment of the Destination Table" later in this chapter.
By default, the copied columns have the same names in the
destination table that they have in the source table. If you want to give
new names to the columns in the destination table, enter the new names
in parentheses after the destination table name. If you enter any column
names, you must enter a name for every column you are copying.
Here is an example of a COPY command that copies only two columns from the source table, and copies only those rows in which the value of DEPTNO is 30:
SQL> COPY FROM SCOTT/TIGER@BOSTONDB - > REPLACE EMPCOPY2 - > USING SELECT ENAME, SAL - > FROM EMPCOPY - > WHERE DEPTNO = 30
You may find it easier to enter and edit long COPY commands
in command files rather than trying to enter them directly at the command
prompt.
You control the treatment of the destination table by entering
one of four control clauses--REPLACE, CREATE, INSERT, or APPEND.
The REPLACE clause names the table to be created in the destination database and specifies the following actions:
You can use the CREATE clause to avoid accidentally writing over an existing table. CREATE specifies the following actions:
Use INSERT to insert data into an existing table. INSERT specifies the following actions:
Use APPEND when you want to insert data in an existing table, or create a new table if the destination table does not exist. APPEND specifies the following actions:
To copy EMP from a remote database into a table called EMPCOPY
on your own database, enter the following command:
|
Note: See your DBA for an appropriate username, password, and
service name for a remote computer that contains a copy of EMP.
|
SQL> COPY FROM SCOTT/TIGER@BOSTONDB - > CREATE EMPCOPY - > USING SELECT * FROM EMP
SQL*Plus displays the following messages:
Array fetch/bind size is 20. (arraysize is 20) Will commit when done. (copycommit is 0) Maximum long size is 80. (long is 80)
SQL*Plus then creates the table EMPCOPY, copies the rows, and displays the following additional messages:
Table EMPCOPY created. 14 rows selected from SCOTT@BOSTONDB. 14 rows inserted into EMPCOPY. 14 rows committed into EMPCOPY at DEFAULT HOST connection.
In this COPY command, the FROM clause directs COPY to connect
you to the database with the specification D:BOSTON-MFG as SCOTT, with
the password TIGER.
Notice that you do not need a semicolon at the end of the
command; COPY is a SQL*Plus command, not a SQL command, even though it
contains a query. Since most COPY commands are longer than one line, you
must use a hyphen (-), optionally preceded by a space, at the end of each
line except the last.
The first three messages displayed by COPY show the values
of SET command variables that affect the COPY operation. The most important
one is LONG, which limits the length of a LONG column's value. (LONG is
a datatype, similar to CHAR.) If the source table contains a LONG column,
COPY truncates values in that column to the length specified by the system
variable LONG.
The variable ARRAYSIZE limits the number of rows that SQL*Plus
fetches from the database at one time. This number of rows makes up a batch.
The variable COPYCOMMIT sets the number of batches after which COPY commits
changes to the database. (If you set COPYCOMMIT to zero, COPY commits changes
only after all batches are copied.) For more information on the variables
of the SET command, including how to change their settings, see the SET
command in Chapter 8.
After listing the three system variables and their values,
COPY tells you if a table was dropped, created, or updated during the copy.
Then COPY lists the number of rows selected, inserted, and committed.
You can refer to another user's table in a COPY command by
qualifying the table name with the username, just as you would in your
local database, or in a query with a database link.
For example, to make a local copy of a table named DEPT, owned by the username ADAMS on the database associated with the Net8 connect identifier BOSTONDB, you would enter
SQL> COPY FROM SCOTT/TIGER@BOSTONDB - > CREATE EMPCOPY2 - > USING SELECT * FROM ADAMS.DEPT
Of course, you could get the same result by instructing COPY
to log in to the remote database as ADAMS. You cannot do that, however,
unless you know the password associated with the username ADAMS.
You can copy data from one table to another in a single database (local or remote). To copy between tables in your local database, specify your own username and password and the service name for your local database in either a FROM or a TO clause (omit the other clause):
SQL> COPY FROM SCOTT/TIGER@MYDATABASE - > INSERT EMPCOPY2 - > USING SELECT * FROM EMP
To copy between tables on a remote database, include the same username, password, and service name in the FROM and TO clauses:
SQL> COPY FROM SCOTT/TIGER@BOSTONDB - > TO SCOTT/TIGER@BOSTONDB - > INSERT EMPCOPY2 - > USING SELECT * FROM EMP
|
Copyright © 2000 Oracle Corporation. All Rights Reserved. |
|