SQL*Plus common commands

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

Verification method by database
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 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

4. Output the displayed content to the specified file
All content on the screen is included in the file, including the sql statement you entered.

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

6. Display the structure of a table

7. COL command:
I use the formatting method

to change the default column header---------- --------- ------------------ ---------
10 ACCOUNTING NEW YORK --------- ------- --------------------- -----------
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.
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 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

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

11. Add the specified text to the end of the current line of sql buffer ---------- -------------- 12. Execute the sql statement just executed again 13. Execute a stored procedure
14． Display help for sql*plus command
15. Display the value of the sql*plus system variable or the value of the sql*plus environment variable1). Display the value of the current environment variable:
2). Display the error message currently creating functions, stored procedures, triggers, packages and other objects
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:
4) . Display the database version:
5) . Display the size of SGA
6) Display the current user name


************ ************************************
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.