Common SQL * Plus commands

Oracle SQL * plus and web-side isqlplus are tools for interacting with oracle. In SQL * Plus, you can run SQL statements. the DML, DDL, and DCL statements we usually call are SQL * Plus statements. After they are executed, they can be stored in a memory area called sqlbuffer, and only one SQL statement can be saved recently.

Oracle SQL * plus and web-side isqlplus are tools for interacting with oracle. In SQL * Plus, you can run SQL statements. the DML, DDL, and DCL statements we usually call are SQL * Plus statements. After they are executed, they can be stored in a memory area called SQL buffer, and only one SQL statement can be saved recently.

Oracle SQL * plus and web-side isqlplus are tools for interacting with oracle. In SQL * Plus, you can run SQL statements.
The DML, DDL, and DCL statements we usually call are SQL * Plus statements. After they are executed, they can be stored in a memory area called SQL buffer, in addition, only one recently executed SQL statement can be saved. We can modify the SQL statement stored in SQL buffer and execute it again. SQL * Plus generally deals with databases.
What is ddl dml dcl.
DDL database definition language, including create drop alter
DML database management language, including select update delete
DCL Database Control Language, including grant and revoke

The following describes some common SQL * Plus commands:

<无>

1. how to connect to a database is verified by the operating system: SQL> conn/as sysdba database verification method SQL> CONN username/password @ databaseIdentified AS sysdbadatabaseIdentified is the link identifier and has nothing to do with the database, you can name it as needed. AS is followed by role 2. how to execute an SQL script file SQL> start file_name SQL> @ file_name we can save multiple SQL statements in a text file, in this way, when you want to execute all the SQL statements in this file, you can use any of the following commands, which is similar to batch processing in dos. 3. re-run the last running SQL statement SQL> run4. output the displayed content to the specified file SQL> SPOOL file_name. All the content on the screen is included in the file, include the SQL statement you entered. 5. Disable spool output SQL> SPOOL OFF. Only when spool output is disabled will the output content be displayed in the output file. 6. displays the structure SQL of a table> desc table_name 7. COL command: I used the formatting method COL columnname format a20 to change the default COLUMN Title COLUMN column_name HEADING column_heading For example: SQL> select * from dept; deptno dname loc ---------- accounts --------- 10 accounting new york SQL> col LOC heading location SQL> select * from dept; DEPTNO DNAME location --------- -------------------------- ------------- 10 ACCOUNTING NEW YO RK 8. Set command: Generally, set linesize 1000 set wrap off is used to determine whether to intercept an SQL statement when the length of an SQL statement is greater than LINESIZE. SQL> SET WRA [P] {ON | OFF} when the length of the output row is greater than the length of the set row (set with the SET linesize n command), when set wrap on, more than one character in the output row is displayed on another line. Otherwise, more than one character in the output row is removed and not displayed. 9. modifying the current row in SQL buffer, the first string C [HANGE]/old_value/new_value SQL> l 1 * select * from dept SQL> c/dept/emp 1 * select * from emp 10. displays the SQL statements in SQL buffer. list n displays the nth row in SQL buffer and makes the nth row the current row L [IST] [n] 10. add one or more rows under the current row of SQL buffer I [NPUT] 11. add the specified text to the current row of the 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. RUN or/13. EXECUTE a stored procedure EXECUTE procedure_name 14. displays the HELP of the SQL * plus command 15. displays 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 Ll 2 ). displays the error information about the objects such as creating a function, stored procedure, trigger, or package. Show error: When an error occurs, such as creating a function or stored procedure, you can use this command to view the errors and corresponding error information in that location, modify the information, and then compile again. 3 ). display the value of the initialization parameter: show PARAMETERS [parameter_name] 4 ). show REL [databases] 5 ). show SGA 6) display the current user name: show user ********************************** * ORA-00054: resource busy and acquire with NOWAIT specified symptom: locked_mode is 2, 3, 4 does not affect DML (insert, delete, update, select) operations, but DDL (alter, drop, etc) an ora-00054 error is prompted for the operation. When there is a primary foreign key constraint, update/delete...; may generate 4 or 5 locks. The DDL statement is a 6 lock. Solution: Use the DBA role to view the locks in the current database. Use the following SQL statement: 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 a long-standing column appears, it 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 # '; and finally restore to normal.