Library |
Product |
Contents |
Index |
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.
For example, to connect the username TODD to the default database using the password FOX, you could enter
SQL> CONNECT TODD/FOXIf 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.
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 SQL*Net and both databases have compatible network drivers.
You can connect to a remote database in one of two ways:
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 database specification depends upon the SQL*Net protocol your computer uses. For more information, see CONNECT and the SQL*Net manual appropriate for your protocol, or contact your DBA.
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 string with the @ command used to run a command file.
COPY FROM database TO database action - destination_table (column_name, column_name, - column_name ...) USING queryHere is a sample COPY command:
COPY FROM SCOTT/TIGER@BOSTONDB - TO TODD/FOX@CHICAGODB - CREATE NEWDEPT (DNUMBER, DNAME, CITY)- USING SELECT * FROM DEPTTo 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 database specification(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.
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). See "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.
Note: To enable the copying of data between Oracle and non-Oracle databases, NUMBER columns are changed to DECIMAL columns in the destination table. Hence, if you are copying between Oracle databases, a NUMBER column with no precision will be changed to a DECIMAL(38) column. When copying between Oracle databases, you should use SQL commands (CREATE TABLE AS and INSERT) or you should ensure that your columns have a precision specified.
The USING clause specifies a query that names the source table and specifies the data that COPY copies to the destination table. You can use any form of the SQL SELECT command to select the data that the COPY command copies.
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 = 30You 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.
The REPLACE clause names the table to be created in the destination database and 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 database specification for a remote computer that contains a copy of EMP.
SQL> COPY FROM SCOTT/TIGER@BOSTONDB - > CREATE EMPCOPY - > USING SELECT * FROM EMPSQL*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 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.
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.
For example, to make a local copy of a table named DEPT, owned by the username ADAMS on the database associated with the SQL*Net connect string BOSTONDB, you would enter
SQL> COPY FROM SCOTT/TIGER@BOSTONDB - > CREATE EMPCOPY2 - > USING SELECT * FROM ADAMS.DEPTOf 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.
SQL> COPY FROM SCOTT/TIGER@MYDATABASE - > INSERT EMPCOPY2 - > USING SELECT * FROM EMPTo copy between tables on a remote database, include the same username, password, and database specification in the FROM and TO clauses:
SQL> COPY FROM SCOTT/TIGER@BOSTONDB - > TO SCOTT/TIGER@BOSTONDB - > INSERT EMPCOPY2 - > USING SELECT * FROM EMP
Prev Next |
Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |