Sqlplus login \ Connection command, Sqlplus command of the use of the entire _oracle

Our usual DML, DDL, and DCL statements are sql*plus statements that, when executed, can be stored in an area of memory called SQL Buffer, and can only hold a recently executed SQL statement, and we can save SQL in SQL buffer Statements are modified and then executed again, Sqlplus typically deal with the database.

Common:

Sqlplus Username/password such as: Ordinary user login Sqlplus Scott/tiger

Sqlplus username/password@net_service_name such as: Sqlplus SCOTT/TIGER@ORCL
Sqlplus Username/password as SYSDBA: Sqlplus sys/admin as Sysdba

Sqlplus Username/password@//host:port/sid

NOTE: SYS and system need to log in as Sysdba

After entering Sql*plus, you can use Conn to connect to other users, such as: Conn Sys/admin as Sysdba

In DOS environment, enter "Sqlplus/?" as follows:

C:\Documents and Settings\hh>sqlplus/?
Sql*plus:release 11.2.0.1.0 Production on Saturday August 13 16:56:46 2011
Copyright (c) 1982, Oracle. All rights reserved.
Sql*plus:release 11.2.0.1.0 Production
Copyright (c) 1982, Oracle. All rights reserved.
Use Sql*plus to execute SQL, Pl/sql, and Sql*plus statements.
Usage 1:sqlplus-h | -V
-h displays Sql*plus version and usage help.
-V Displays the Sql*plus version.

Usage 2:sqlplus [[<option>] [{logon |/nolog}] [<start>]]

<option>: [-C <version>] [-l] [M "<options>"] [r <level>] [-S]

   -C <version>   Sets the compatibility of the affected command to <version> the specified version. This version has a "x.y[.z]" format. For example,-C 10.2.0
   -l             Try to log on only once instead of prompting again when an error occurs.
   -M "<options>" sets the automatic HTML markup for the output. The format of the options is:
                    HTML [on| OFF] [head text] [body text] [TABLE text][entmap {on| Off}] [SPOOL {on| Off}] [Pre[format] {on| Off}]
   -R <level>     sets the restricted mode to disable the Sql*plus command that interacts with the file system. The level can be 1, 2, or 3. The maximum limit level is-R 3, which disables all user commands that interact with the file system.
   -s             set silent mode, This pattern hides the Sql*plus flag of the command, prompts and echoes the display.

 <logon>: {<username>[/<password>][@<connect_identifier>] |/}[as {SYSDBA | Sysoper | SYSASM}] [Edition=value]
   Specify the database account user name, the password, and the connection identifier for the database connection. If there is no connection identifier, Sql*plus will connect to the default database. The
   as SYSDBA, as Sysoper and as sysasm options are database administrative permissions. The
   <connect_identifier> can be in the form of a Net service name or an easy connection.
     @[<net_service_name> | [//] host[:P Ort]/<service_name>]
       <net_service_name> is the simple name of the service, it resolves to a connection descriptor.
       Example: Connect to a database using a net service name, and the database Net service name is ORCL.
          sqlplus MYUSERNAME/MYPASSWORD@ORCL

host specifies the hostname or IP address of the database server computer.
PORT Specifies the listening ports on the database server.
<service_name> Specifies the service name of the database to be accessed.
Example: Connect to a database with an easy connection and the service name is ORCL.
Sqlplus MYUSERNAME/MYPASSWORD@HOST/ORCL
The/nolog option launches the Sql*plus without connecting to the database.
EDITION Specifies the value of the session version.
<start>: @<url>|<filename>[.<ext>] [<parameter> ...]
Runs the specified Sql*plus script from the WEB server (URL) or local file system (FILENAME.EXT) using the specified parameters that will be assigned to the substitution variable in the script.
After starting sql*plus and executing the CONNECT command, the site profile (for example, $ORACLE _home/sqlplus/admin/glogin.sql) and user profiles, such as Login.sql in the working directory, are run. These files contain the Sql*plus command.

In addition to Sqlplus statements, other statements executed in Sql*plus are called Sql*plus commands. After they have been executed, there is no memory area for SQL buffer, and they are typically used to format the results of the output to make the report available.

Here are some common Sql*plus commands:

1. Execute a SQL script file
Sql>start file_name
sql>@ file_name
We can save multiple SQL statements in a text file so that when you execute all the SQL statements in this file, use any of the above commands, which is similar to the batch processing in DOS.
2. Edit the current input
Sql>edit
3. Rerun the SQL statement that was last run
sql>/
4. Output the displayed content to the specified file
Sql> SPOOL file_name
All the content on the screen is included in the file, including the SQL statement you entered.
5. Turn off spool output
Sql> SPOOL off
The output is not seen in the output file until the spool output is turned off.
6. Display the structure of a table
Sql> DESC TABLE_NAME
7. Col command:
The display form of the primary formatted column.
The command has many options, as follows:
Col[umn] [{column|expr} [option ...]]
Option options can be the following clauses:
Ali[as] Alias
Cle[ar]
Fold_a[fter]
Fold_b[efore]
For[mat] Format
Hea[ding] Text
Jus[tify] {l[eft]| c[enter]| c[entre]| R[ight]}
Like {Expr|alias}
Newl[ine]
New_v[alue] Variable
nopri[nt]| PRI[NT]
Nul[l] Text
Old_v[alue] Variable
on| Off
wra[pped]| wor[d_wrapped]| Tru[ncated]
1). Change the default column headings
COLUMN column_name HEADING column_heading
For example:
Sql>select * FROM Dept;
DEPTNO dname LOC
---------- ---------------------------- ---------
Ten ACCOUNTING NEW YORK
Sql>col LOC Heading Location
Sql>select * FROM Dept;
DEPTNO dname Location
--------- ---------------------------- -----------
Ten ACCOUNTING NEW YORK
2. Change the column name ename to the new column name Employee name and place the new column name on two lines:
Sql>select * from EMP
Department name Salary
---------- ---------- ----------
Ten AAA 11
sql> COLUMN ename HEADING ' employee| Name '
Sql>select * from EMP
Employee
Department name Salary
---------- ---------- ----------
Ten AAA 11
Note:the col heading turn into the two from one line.
3). Change the display length of the column:
For[mat] Format
Sql>select empno,ename,job from EMP;
EMPNO ename JOB
---------- ---------- ---------
7369 SMITH Clerk
7499 ALLEN Salesman
7521 WARD Salesman
sql> Col ename format A40
EMPNO ename JOB
---------- ---------------------------------------- ---------
7369 SMITH Clerk
7499 ALLEN Salesman
7521 WARD Salesman
4). Set the alignment of column headings
Jus[tify] {l[eft]| c[enter]| c[entre]| R[ight]}
Sql> Col ename Justify Center
Sql>/
EMPNO ename JOB
---------- ---------------------------------------- ---------
7369 SMITH Clerk
7499 ALLEN Salesman
7521 WARD Salesman
For columns of type number, the column headings default to the right, and other types of column headings default to the left
5. Do not let a column appear on the screen
nopri[nt]| PRI[NT]
sql> Col Job Noprint
Sql>/
EMPNO ename
---------- ----------------------------------------
7369 SMITH
7499 ALLEN
7521 WARD
6). Format the display of the Number type column:
sql> COLUMN SAL FORMAT $99,990
Sql>/
Employee
Department Name Salary Commission
---------- ---------- --------- ----------
ALLEN $1,600 300
7. When displaying column values, if the column value is a null value, replace the null value with the text value
COMM Nul[l] Text
Sql>col COMM Nul[l] Text
. Sets the wrapping style for a column
wra[pped]| wor[d_wrapped]| Tru[ncated]
COL1
--------------------
How ARE?
Sql>col COL1 FORMAT A5
Sql>col COL1 Wrapped
COL1
-----
How A
RE YO
U
sql> COL COL1 word_wrapped
COL1
-----
How
ARE
You?
sql> COL COL1 word_wrapped
COL1
-----
How A
9). Display the current display property value of a column
Sql> COLUMN column_name
10). Set the display property of all columns to the default value
Sql> Clear COLUMNS
8. Block out the same values shown in a column
Break on Break_column
Sql> Break on DEPTNO
Sql> SELECT DEPTNO, ename, SAL
From EMP
WHERE SAL < 2500
Order BY DEPTNO;
DEPTNO ename SAL
---------- ----------- ---------
Ten CLARK 2450
MILLER 1300
SMITH 800
ADAMS 1100
9. In the display of the same values shown in a column above, insert n blank rows whenever the value of the column changes before the value changes.
Break on Break_column SKIP n
Sql> Break on DEPTNO SKIP 1
Sql>/
DEPTNO ename SAL
---------- ----------- ---------
Ten CLARK 2450
MILLER 1300
SMITH 800
ADAMS 1100
10. Show the settings for the break
Sql> break
11. Delete Settings for 6, 7
Sql> Clear BREAKS
Set command:
This command contains a number of subcommand commands:
SET system_variable Value
System_variable value can be one of the following clauses:
appi[nfo]{on| Off|text}
Array[size] {15|n}
auto[commit]{on| Off| Imm[ediate]|n}
Autop[rint] {on| OFF}
AutoRecovery [on| OFF]
Autot[race] {on| Off| Trace[only]} [Exp[lain]] [Stat[istics]]
Blo[ckterminator] {. | C
CMDS[EP] {; |c| on| OFF}
colsep {_|text}
com[patibility]{v7| v8| NATIVE}
Con[cat] {. | c| on| OFF}
Copyc[ommit] {0|n}
Copytypecheck {on| OFF}
Def[ine] {&|c| on| OFF}
DESCRIBE [DEPTH {1|n| All}][linenum {on| off}][indent {on| OFF}]
ECHO {on| OFF}
Editf[ile] File_name[.ext]
Emb[edded] {on| OFF}
Esc[ape] {|c| on| OFF}
Feed[back] {6|n| on| OFF}
Flagger {off| ENTRY | intermed[iate]| Full}
Flu[sh] {on| OFF}
Hea[ding] {on| OFF}
HEADS[EP] {| | c| on| OFF}
INSTANCE [instance_path| Local]
Lin[esize] {80|n}
Lobof[fset] {n|1}
Logsource [Pathname]
LONG {80|n}
Longc[hunksize] {80|n}
Mark[up] HTML [on| OFF] [head text] [body text] [Entmap {on| Off}] [SPOOL
{on| Off}] [Pre[format] {on| OFF}]
Newp[age] {1|n| NONE}
NULL text
Numf[ormat] Format
Num[width] {10|n}
Pages[ize] {24|n}
Pau[se] {on| Off|text}
RECSEP {wr[apped]| ea[ch]| OFF}
Recsepchar {_|c}
Serverout[put] {on| OFF} [SIZE n] [For[mat] {wra[pped]| Wor[d_
wrapped]| Tru[ncated]}]
Shift[inout] {vis[ible]| Inv[isible]}
Show[mode] {on| OFF}
Sqlbl[anklines] {on| OFF}
Sqlc[ase] {mix[ed]|lo[wer]| Up[per]}
Sqlco[ntinue] {> |text}
Sqln[umber] {on| OFF}
Sqlpre[fix] {#|c}
Sqlp[rompt] {Sql>|text}
Sqlt[erminator] {; |c| on| OFF}
Suf[fix] {Sql|text}
TAB {on| OFF}
Term[out] {on| OFF}
Ti[me] {on| OFF}
Timi[ng] {on| OFF}
Trim[out] {on| OFF}
Trims[pool] {on| OFF}
Und[erline] {-|c| on| OFF}
Ver[ify] {on| OFF}
Wra[p] {on| OFF}
1). Set whether the current session automatically submits the modified data
Sql>set Auto[commit] {on| Off| imm[ediate]| N
2. Whether to display the SQL statement that is executing in the script when executing a SQL script with the start command
Sql> SET ECHO {on| OFF}
3. Whether to display the current SQL statement query or modify the number of rows
Sql> SET Feed[back] {6|n| on| OFF}
Defaults to the number of rows that display results only if the result is greater than 6 rows. If set feedback 1, it is returned regardless of how many rows the query is to. The number of rows in the query is not displayed when off
4). Whether column headings are displayed
Sql> SET hea[ding] {on| OFF}
When set heading off, column headings are not displayed on top of each page, instead of blank lines
5. Set the number of characters a row can hold
Sql> SET lin[esize] {80|n}
If the output of a row is greater than the number of characters that can be accommodated on a set line, the row is displayed.
6). Set the separation between pages and pages
Sql> SET Newp[age] {1|n| NONE}
When set newpage 0 o'clock, there is a small black box at the beginning of each page.
When set NewPage n, it is separated by n empty rows between pages and pages.
When set newpage none, there is no spacing between pages and pages.
7). When displayed, replace the null value with the text value
sql> SET NULL Text
. Set the number of rows on a page
Sql> SET pages[ize] {24|n}
If set to 0, all output is one page and column headers are not displayed
9). Whether to display with Dbms_output. Put_Line the package for output information.
Sql> SET Serverout[put] {on| OFF}
When writing stored procedures, we sometimes use dbms_output.put_line to output the necessary information to debug the stored procedure, and the information can be displayed on the screen only when the Serveroutput variable is set to ON. Dbms_output.put_line will "eat" the front space? Add the format wrapped parameter after set serveroutput on!
10. When the length of the SQL statement is greater than linesize, the SQL statement is intercepted when it is displayed.
Sql> SET Wra[p] {on| OFF}
When the length of the output row is greater than the length of the set line (set linesize n command), when set wrap on, more characters from the output line are displayed on a separate line, otherwise the output line is removed more than characters and is not displayed.
11. Whether to display the output on the screen, mainly used in conjunction with the spool.
Sql> SET Term[out] {on| OFF}
When you export the contents of a large table to a file using the spool command, the content output on the screen will take a lot of time, set Termspool off, the output will only be saved in the output file, will not be displayed on the screen, greatly improve the speed of the spool.
12. Remove the extra space after each line in the spool output
Sql> SET Trims[out] {on| OFF}
13 shows the execution time spent on each SQL statement
Set TIMING {on| OFF}
14. Modify the current line in SQL buffer, the first occurrence of the string
C[hange]/old_value/new_value
Sql> L
1* SELECT * FROM dept
Sql> c/dept/emp
1* SELECT * from emp
15. Edit SQL statements in SQL buffer
EDI[T]
16. Displays SQL statements in SQL buffer, List n shows the nth row in SQL buffer, and makes nth row the current line
L[ist] [n]
17. Add one or more lines below the current line in SQL buffer
I[nput]
18. Add the specified text after the current line in SQL buffer
A[ppend]
Sql> Select Deptno,
2 dname
3 from Dept;
DEPTNO dname
---------- --------------
Ten ACCOUNTING
The
SALES
OPERATIONS
Sql> L 2
2* dname
Sql> A, loc
2* Dname,loc
Sql> L
1 Select Deptno,
2 Dname,loc
3* from Dept
Sql>/
DEPTNO dname LOC
---------- -------------- -------------
Ten ACCOUNTING NEW YORK
DALLAS
SALES CHICAGO
OPERATIONS BOSTON
19. Save SQL statements in SQL buffer to a file
SAVE file_name
20. Import SQL statements from one file into SQL buffer
Get file_name
21. Execute the SQL statement that has just been executed again
RUN
Or
/
22. Execute a Stored procedure
EXECUTE procedure_name
23. Connecting to the specified database in Sql*plus
CONNECT User_name/passwd@db_alias
24. Set the top caption for each report
Ttitle
25. Set the tail caption of each report
Btitle
26. Write a note
Remark [text]
27. Output the specified information or a blank line to the screen
PROMPT [Text]
28. Suspend the execution of the process, wait for the user to respond and continue execution
PAUSE [Text]
Sql>pause Adjust paper and press RETURN to continue.
29. Copy some of the data from one database to another (such as copying the data from one table to a different database)
COPY {from database | to database | From database to database}
{append| create| Insert| REPLACE} destination_table
[(Column, Column, column, ...)] USING Query
Sql>copy from SCOTT/TIGER@HQ to John/chrome@west
Create Emp_temp
USING a SELECT * from EMP
30. Do not exit Sql*plus and execute an operating system command in Sql*plus:
HOST
Sql> host hostname
This command may be supported under Windows.
31. In Sql*plus, switch to the operating system command prompt, and after running the operating system command, you can switch back to Sql*plus:
！
sql>!
$hostname
$exit
Sql>
This command is not supported under Windows.
32. Display Help for the Sql*plus command
Help
How to install the Help file:
Sql>@? Sqlplusadminhelphlpbld.sql? Sqlplusadminhelphelpus.sql
Sql>help Index
33. Displays the value of the Sql*plus system variable or the value of the Sql*plus environment variable
Syntax
SHO[W] Option
Where option represents one of the following terms or clauses:
System_variable
All
Bti[tle]
Err[ors] [{function| procedure| package| PACKAGE body|
trigger| view| type| TYPE body} [schema.] Name
LNO
PARAMETERS [Parameter_name]
PNO
Rel[ease]
Repf[ooter]
Reph[eader]
Sga
SPOO[L]
SQLCODE
Tti[tle]
USER
1). Displays the value of the current environment variable:
Show All
2). Displays error messages for objects that are currently in the process of creating functions, stored procedures, triggers, packages, and so on
Show Error
When an error occurs, such as creating a function, stored procedure, and so on, the variable can use the command to see the error in that place and the corresponding error message, make the modification and compile again.
3). Displays the value of the initialization parameter:
Show PARAMETERS [Parameter_name]
4). To display the version of the database:
Show Rel[ease]
5). Show the size of the SGA
Show SGA
6). Display the current user name
Show user
_________________
XSB Note:
The difference between @2.sql and @@2.sql:
For example, in E: down Sqlplus @e:temp1.sql
The script called in 1.sql is located at E: @2.sql
The script called by @@2.sql is located in the E:temp directory.