Shulou(Shulou.com)06/01 Report--

Sqlplus command learning

$sqlplus / as sysdba

You can enter the sqlplus command for SQL > help index

Enter Help [topic] for help.

@ COPY PAUSE SHUTDOWN

@ @ DEFINE PRINT SPOOL

/ DEL PROMPT SQLPLUS

ACCEPT DESCRIBE QUIT START

APPEND DISCONNECT RECOVER STARTUP

ARCHIVE LOG EDIT REMARK STORE

ATTRIBUTE EXECUTE REPFOOTER TIMING

BREAK EXIT REPHEADER TTITLE

BTITLE GET RESERVED WORDS (SQL) UNDEFINE

CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE

CLEAR HOST RUN WHENEVER OSERROR

COLUMN INPUT SAVE WHENEVER SQLERROR

COMPUTE LIST SET XQUERY

CONNECT PASSWORD SHOW

Sqlplus A local sql statement that allows you to enter sql statements to control access to the database

1 find out the number and name of the scott.emp table

SQL > select empno. Enter at the end of ename from scott.emp; semicolon.

EMPNO ENAME

--

7369 SMITH

7499 ALLEN

7521 WARD

7566 JONES

7654 MARTIN

2 how to view the statements of the current sql buffer

? Command-> see help

? List

SQL >? List

LIST

-

Lists one or more lines of the most recently executed SQL command

Or PL/SQL block which is stored in the SQL buffer. Enter LIST with

No clauses to list all lines. In SQL*Plus command-line you can also

Use ";" to list all the lines in the SQL buffer. The buffer has no

Command history list and does not record SQL*Plus commands.

L [IST] [n | n m | n * | n LAST | * | * n | * LAST | LAST]

Enter l

3 I want to modify a buffer statement, such as adding a conditional ename = 'SCOTT'

SQL >? EDIT

EDIT

-

Invokes calls an operating system text editor text editor on the contents of the

Specified file or on the contents of the SQL buffer. The buffer

Has no command history list and does not record SQL*Plus commands.

ED [IT] [file_name [.ext]]

SQL > ed

Wrote file afiedt.buf

thirty-eight

?

?

Q exit

Be sure to specify which text editor to use in linux

Notepad for windows

How to specify which editor to use? Variable assignment

Method 1: specify temporarily

Define _ editor=vi

Enter ed

Method 2: permanently specify that the variable be placed in .bash _ profile

[oracle@yutianedu ~] $vim / home/oracle/.bash_profile

Join: export EDITOR=vi

[oracle@yutianedu ~] $source / home/oracle/.bash_profile

[oracle@yutianedu ~] $echo $EDITOR

Vi

Ed modification

SQL > l View

1 select empno, ename from scott.emp

2 * where ename = 'SCOTT'

4 how to execute statements in buffer cache

SQL >? /

/ (slash)

-

Executes the most recently executed SQL command or PL/SQL block

Which is stored in the SQL buffer. Use slash (/) at the command

Prompt or line number prompt in SQL*Plus command line. The buffer

Has no command history and does not record SQL*Plus commands.

SQL > l

1 select empno, ename from scott.emp

2 * where ename = 'SCOTT'

SQL > / execute the sql statement in sql buffer

EMPNO ENAME

--

7788 SCOTT

5 save the code a script and execute it next time

SQL >? SAVE

SAVE

-

Saves the contents content of the SQL buffer in a script. The

Buffer has no command history list and does not record SQL*Plus commands.

SAV [E] [FILE] file_name [.ext] [create] | REP [LACE] | app [end]]

Save file command create / replace / append

Create by default

Replace

Additional

Note: the script must have a directory with permission (oracle user has permission to write)

Save / u01/app/oracle/1.sql

6 after saving the script, I want to take a look at the operating system, and then come back to execute the sqlplus command (do not exit sqlplus to execute the command on the system)

SQL >? HOST

HOST

-

Executes an operating system command without leaving SQL*Plus.

Enter HOST without command to display an operating system prompt.

You can then enter multiple operating system commands.

HO [ST] [command]

Usage 1: host enter-"operating system -" exit-"sqlplus"

Usage 2: host operating system command

SQL > host ls-l / u01/app/oracle

Total 24

-rw-r--r-- 1 oracle oinstall 58 Oct 11 15:26 1.sql

Drwxr-x--- 3 oracle oinstall 4096 Oct 11 14:10 admin

Clear the screen clear-> host clear

Copy 1.sql to the / tmp directory

SQL > host cp / u01/app/oracle/1.sql / tmp

SQL > host ls-l / tmp

Total 524

-rw-r--r-- 1 oracle oinstall 58 Oct 11 15:30 1.sql

Delete 1.sql under / tmp

Host rm / tmp/1.sql

All host systems support it.

Can be used under the linux platform! Replace host

7 exit sqlplus

Exit

Quit

8 there is a sql script in / u01/app/oracle that executes the script in sqlplus.

SQL >? @

@ ("at" sign)

-

Runs the SQL*Plus statements in the specified script. The script can be

Called from the local file system or a web server.

Or use

SQL >? Start

START

-

Runs the SQL*Plus statements in the specified script. The script can be

Called from the local file system or a web server.

STA [RT] {url | file_name [.ext]} [arg...]

@ / u01/app/oracle/1.sql

Start / u01/app/oracle/1.sql

9 will save all the output on the screen to a file-"for log purposes."

? Spool

Usage

Spool a file

Execute a command

End of spool off

10 how to switch between users

Conn account name / password

SQL > show user

USER is "SYS"

SQL > conn scott/oracle

SConnected.

SQL > show user

USER is "SCOTT"

SQL > conn hr/hr

Connected.

SQL > conn / as sysdba

Connected.

SQL > show user

USER is "SYS"

Sqlplus / nolog login requires connect to connect to the database

SQL > conn scott/oracle

Connected.

SQL > show user

USER is "SCOTT"

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.