Oracle Study Notes: sqlplus usage

1. Connect to the database

OS: only enters the sqlplus environment and does not connect to the database

 
Sqlplus/nolog
 

OS: Local Connection

Method 1: Operating system authentication Logon: The Role After logon cannot be omitted.

 
Set oracle_sid = test_sidsqlplus/as {sysdba | sysoper | sysasm}
 

Method 2: Standard User Logon: Password, port (default 1521), role can be omitted. If the password is omitted, the system prompts you to enter the password.

 
Set oracle_sid = test_sidsqlplus user [/password] [as {sysdba | sysoper | sysasm}] Or sqlplus user [/password] @ local Oracle service name [as {sysdba | sysoper | sysasm}] or sqlplus user [/password] @ host [: port]/Oracle service name [as {sysdba | sysoper | sysasm}] Or sqlplus user [/password] @//Host [: Port]/Oracle service name [as {sysdba | sysoper | sysasm}]
 

OS: remote connection

 
Same as "Mode 2" of "local connection" (except for the set oracle_sid Mode)
 

Sqlplus:Exit to OS:The default behavior is commit, and then successfully exits.

{Exit | quit} [success | Failure | warning | n | variable |: bindvariable] [commit | rollback]
 

Sqlplus:Disconnect

 
Disc [onnect]
 

Sqlplus:New Connection

 
-- Prompt user name and password: only for local logon to conn [ect] or the same as "OS connected to DB"
 

Switch users:

 
Disconnect, reconnect, or exit, and then log on.
 

 

Ii. Set the sqlplus execution environment

Set Environment Variables

 
Set environment_variable {?}
 

View environment variable settings: Show

 
-- View the settings of all environment variables show all -- view the settings of the specified environment variables show environment_variable or -- view the settings of the specified environment variables: Use setset environment -- it will give related syntax prompts
 

Set Information Display Mode

Prompt

 
Set sqlp [rompt] "_ user' @ '_ connect_identifier>"
 

Current OS time

 
-- Display the current execution time
Set Time on

-- Disable the current execution time
Set Time Off
 

Number of pages and number of rows

 
Set pages [ize] = 500 set LiN [esize] = 3000
 

Dbms_output Information Display

 
-- Enable display set serverout [put] on [size {n | unlimited}] [format {wrapped | word_wrapped | truncated}] -- disable display set serverout [put] Off
 

SET transaction control:

 
-- Enable automatic transaction commit: Submit set auto [Commit] {on | Imm [ediate] | n} immediately}
-- Enable automatic commit transaction: Submit after accumulating n transactions
Set auto [Commit] n
 

 
-- Disable automatic transaction commit
Set auto [Commit] Off
 

 

Execution time tracking

 
Set TIMI [ng] On
 

 

Number of affected rows

 
-- Enable display of affected rows Set feed [Back] {1 | on | n} -- disable Set feed [Back] {0 | off}
 

 

Execution Plan

 
-- Display Execution Plan set autot [race] {on | trace [only]} [exp [lain] [stat [istics] -- close Execution Plan set autot [race] Off
 

 

 

Iii. Editing of buffer execution statements (SQL, PL/SQL)

Call the external editor: Ed [it]

 
Ed [it]
 

 

Sqlplus editing commands: add, delete, and modify: A [ppend], Del, C [hange], I [nput]

A [ppend] -- enter a character directly after the current row to create,Do not wrapDel -- similar to list, see C [hange] -- very good stuff below. Format: "Change sep_char"Old[Sep_char [New[Sep_char] ":
1) sep_char is a non-alphanumeric character not in the old and new (basically only punctuation marks)
2) The last separator sep_char can be omitted.
3) Old is case insensitive.
4) Old can specify the wildcard "... ", used to match the range :... old (beginning .. first appears), old... old (full match before and after), old... (first appearance .. last)
5) if the second separator sep_char (also omitted) or new (also included) is omitted, the old character is deleted.
I [nput] -- enter a new string after the current row,Line feed
 

Change current row: Always the last row by default: L [ist]

 
-- View all rows {list |;} -- view the specified row: * The current row; N in the buffer row list {n | * | last} -- view the rows in the specified range from N to M: m> = NLIST {n | *} {M | * | last}
 

Save the SQL statement: sav [e]: The default extension is SQL, and the default behavior is create.Note: After saving, the semicolon ";" at the end is automatically removed, and the buffer zone has only one SQL statement or PL/SQL compound statement.

 
Sav [e] file_name [. Ext] [Create | replace | append]
 

Load SQL statement: Get: opposite to save

 
-- Opposite to the Save command
 

When an SQL statement is entered, the editing mode is temporarily exited:Exit editing mode temporarily with the # ticket

 
-- In the input SQL mode, if the input is not completed. You can temporarily exit the editing mode with the # on the new line to enter the interaction mode of normal sqlplus.
 

Iv. Definition, interaction, display and processing of execution results, replacement variables, and binding Variables

 

Note the sqlplus command: REM [Mark]

 
Rem [Ark] any text-like OS rem, or the SQL line comment "--"
 

Run the OS command: {Host | !} : OS-specific

 
-- Switch out of sqlplus and enter the shell Environment {Host |! | $} -- If sqlplus is not switched, run the OS command {Host |! | $} Command
 

Clear historical data: cl [Ear]

 
-- Clear screen output information, similar to OS clcl [Ear] SCR [een] -- clear buffer SQL statement cl [Ear] buff [er] -- clear SQL Buffer Pool records cl [Ear] SQL -- clear other cl [Ear] {breaks | computer | columns | timing}
 

Result of the transfer: SPO [Ol]

 
-- Output the execution result to the file.Note: although the spool file is generated immediately, the result is written only when the spool off is used!
SPO [Ol] filename [. Ext] [Create | replace | append] -- disable spoolspo [Ol] Off
 

Replace variables: sqlplus's [pre-compiled] macro function. The essence is simple: def [ine], ACC [EPT], unde [Fine], Pro [MPT], Pau [se]

-- Define and reset the new value. Method 1: def [ine] substitution_var = text Method 2: ACC [EPT] substitution_var [char | date | [num [ber] | binary_float | binary_double] -- variable type [for [Mat] format_str] -- format [Default default_value] -- Variable default Value: [{prompt prompt_text} | nopr [OMPT] -- prompt [hide] -- user input is not displayed, similar to the input with no echo password, display the specified replace variable def [ine] substitution_var. display the prompt information of all replace variables def [ine] -- sqlplus: similar to OS echopro [MPT] [message_text]

-- Sqlplus pause prompt message: similar to OS pause
Pau [se] [message_text]
 

Sqlplus [pre-defined replacement variables]: Be sure to note that these pre-defined replacement variables can all beRedefinitionOrDelete definition. Is sqlplus session-level

_ Editor: OS text editor called by the edit command of sqlplus _ User: Current logon username of sqlplus _ connect_identifier: Current connection string of sqlplus _ Date: current OS Date of sqlplus
 

Binding variable: In sqlplus? Or is it the [session variable?] of the Oracle engine ?] More suitable

 

 
-- Definition: bind_variablevar [iable] bind_variable {number | char (N [byte | char]) | varchar2 (N [byte | char]) | nchar (n) | nvarchar (n) | clob | nclob | binary_float | binary_double | refcursor} -- View: all defined binding variables: defining var [iable] -- View: specify the name of the binding variable: Define var [iable] bind_variable -- print, output: All bound variables: Content print -- print, output: specify the name of the binding variable: content print bind_variable -- print and output: automatically print the bound variable involved: Content: Allow automatic printing, output set autoprint on -- print, output: content: Disable Automatic printing and output set autoprint off
 

5. Execute the OS script

When the OS starts sqlplus, it executes the SQL script in the specified OS: sqlplus startup Parameter

 
Sqlplus [Options] [logon] @ {URL | filename [. Ext] [ARGs...]}
-- Note the "@" symbol.
 

In sqlplus: run the SQL script in OS: {START | @}: the difference between "@" and "@" is that "@" specifies the SQL script (parent script) the SQL script (sub-script) to be run in. the query path of the (sub-script) is in the same directory as the (parent script. Of course, if you use absolute paths, there is no difference.

 
{Sta [RT] | @ |} {URL | OS _filename [. Ext]} [arg...]
 

Script content in sqlplus: {run | /}

 
{R [UN] | /}
 

 

Vi. DBA Functions

Display initialization parameters: Show Parameters

 
-- Show all initialization parameter information show parameters -- display the specified initialization parameter information: Like %? % Show parameters {?}
 

Display SGA Information

 
Show SGA
 

Displays Oracle execution error information

 
-- Display Oracle error informationCodeShow sqlcode -- display [All Oracle error messages] Show err [ors] -- display [error messages] Show err [ors] {function | procedure | package | package body | trigger | View | type body | dimension | Java class} [schema.]Name
 

Displays Oracle recycle bin Information

 
-- Display [all] recycle bin information show Recyc [lebin] -- display the recycle bin information of [specified original object name] Show Recyc [lebin] original_name
 

 Startup: syntax

Startup [[Force] [restrict] [pfile = filename] [Quiet][{Nomount | Mount [dbname] | open [read {only | write [recover]} | recover] [dbname]}]

-- Force: forcibly shut down the running Oracle database instance in abort mode, and then restart the instance. Warning: dangerous operations and use with caution in the production environment
-- Restrict: Enter the connection mode of the restrict session permission.
-- Pfile: starts with the specified pfile.
[Filename]: Replace the default start spfile or default pfile
-- Quiet: SGA information is not displayed after startup.
[Dbname]: replaces the db_name configuration in the initialization parameter.
 

 Start: instance only, nomount Database

 
Startup nomount
 

 Start: instance, Mount Database

 
Startup Mount
 

 Start: instance, read only read-only open database

 
Startup open read only
 

 Start:Instance, read write to open the database: that is, the normal way to open the database

Startup open read write
Startup open
 

 Start:Instance, media recovery mode is Enabled: equivalent to: recover DATABASE Command + startup command

 
Startup open recover ????????????
 

Close: Shutdown

 
-- Abnormal shutdown: similar to power failure: (1) disconnect immediately without the client's consent; (2) shutdown abort must be restored after startup -- close immediately: (1) disconnect the client immediately; (2) shutdown immediate does not need to be restored during startup -- normal shutdown: (1) Wait for the client to actively disconnect; (2) Start does not need to be restored; (3) Unfinished transaction rollback;Shutdown[Normal] -- close with caution: (1) Wait for the client to actively disconnect; (2) Start without recovery; (3) Wait for the transaction to complete; shutdown transaction
 

Recovery: recover

Log Archiving: archive log

Copy Data: Copy

Modify the current user password:If no user name is specified, it is the current user.

 
Passw [ord]
 

Modify other specified user passwords:User Name

 
Passw [ord] {username}
 

 

Report Function