SQL*Plus common commands

1. How to link to the database
Verification method by operating system:

SQL>conn / as sysdba

Verification method by database

SQL>CONN username/password @databaseIdentified AS sysdba

databaseIdentified is the link identifier, which has nothing to do with the database and can be named freely.
AS is followed by the role
2. How to 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 we want to execute all sql statements in this file, use any of the above commands. Yes, this is similar to batch processing in DOS.

3. Rerun the last sql statement run

SQL> run

4. Output the displayed content to the specified file

SQL> SPOOL file_name

All content on the screen is included in the file, including the sql statement you entered.

5. Turn off spool output

SQL> SPOOL OFF

Only when you turn off spool output will you see the output content in the output file.

6. Display the structure of a table

SQL> desc table_name

7. COL command:
I use the formatting method

COL columnname format a20

to change the default column header

COLUMN column_name HEADING column_heading 
For example: 
Sql>select * from dept; 
DEPTNO DNAME LOC

---------- --------- ------------------ ---------
10 ACCOUNTING NEW YORK

sql>col LOC heading location 
sql>select * from dept; 
DEPTNO DNAME location

--------- ------- --------------------- -----------
10 ACCOUNTING NEW YORK
8. Set command:
My normal use
set linesize 1000
set wrap off
When the length of the SQL statement is greater than LINESIZE, whether to intercept the SQL statement during display.

SQL> SET WRA[P] {ON|OFF}

When the length of the output line is greater than the set line length (set with the set linesize n command), when set wrap on, the excess characters in the output line will be displayed in another line. Otherwise, the output line will be displayed in another line. More characters are cut off and will not be displayed.
9. Modify the first string

C[HANGE] /old_value/new_value 
SQL> l 
1* select * from dept 
SQL> c/dept/emp 
1* select * from emp

10 that appears in the current line in sql buffer. Display the sql statement in sql buffer, list n displays the nth line in sql buffer, and makes the nth line the current line

L[IST] [n]

10. Add one or more lines below the current line of sql buffer

I[NPUT]

11. Add the specified text to the end of the current line of sql buffer

A[PPEND] 
SQL> select deptno, 
2 dname 
3 from dept; 
DEPTNO DNAME

---------- --------------

10 ACCOUNTING 
20 RESEARCH 
30 SALES 
40 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 
---------- -------------- ------------- 
10 ACCOUNTING NEW YORK 
20 RESEARCH DALLAS 
30 SALES CHICAGO 
40 OPERATIONS BOSTON

12. Execute the sql statement just executed again

RUN 
or
/

13. Execute a stored procedure

EXECUTE procedure_name

14． Display help for sql*plus command

HELP

15. Display the value of the sql*plus system variable or the value of the sql*plus environment variable

Syntax 
SHO[W] option

1). Display the value of the current environment variable:

Show all

2). Display the error message currently creating functions, stored procedures, triggers, packages and other objects

Show error

When an error occurs when creating a function, stored procedure, etc., you can use this command to check where the error occurred and the corresponding error message, make modifications, and compile again.
3) . Display the value of the initialization parameter:

show PARAMETERS [parameter_name]

4) . Display the database version:

show REL[EASE]

5) . Display the size of SGA

show SGA

6) Display the current user name

show user

************ ************************************
ORA-00054: resource busy and acquire with NOWAIT specified
Symptoms:
Locked_mode of 2, 3, and 4 does not affect DML (insert, delete, update, select) operations, but DDL (alter, drop, etc.) operations will prompt an ora-00054 error. ​
​ When there are primary and foreign key constraints, update / delete ... ; may generate 4 or 5 locks.
　DDL statement has a lock of 6.
Processing method:
As a DBA, you can use the following SQL statement to check the lock situation in the current database:
select object_id,session_id,locked_mode from v$locked_object;
or select t2.username,t2.sid,t2.serial#, t2.logon_time
　from v$locked_object t1,v$session t2
　where t1.session_id=t2.sid order by t2.logon_time;
　If there is a column that appears for a long time, the lock may not be released.
　　We can use the following SQL statement to kill abnormal locks that have not been released for a long time:
　　alter system kill session 'sid,serial#';
Finally return to normal.