Use of oracle sqlplus

Source: Internet
Author: User

Use of oracle sqlplus 1. log on to sqlplus oracle @ omu:> sqlplus/assysdba oracle @ omu:> sqlplus sys/oracle as sysdba 2 as a system administrator. connect to the database (You need to log on to sqlplus first) SQL> conn cgp_1/cgp_1 SQL> conn NE5_1/NE5_1 SQL> conn pds/pd3. query the database status (You need to log on to sqlplus using the system administrator) SQL> selectdb_unique_name, database_role, open_mode, log_mode, protection_mode fromv $ database; 4. database Start and Stop (use the system administrator to log on to sqlplus) Start the database: SQL> startup stop count Database: SQL> shutdownnormal 5. query the table space and corresponding data files (you must first log on to sqlplus) SQL> select tablespace_name, file_name fromdba_data_files; 6. query the table space (You need to log on to sqlplus first) SQL> select * from dba_tablespaces; 7. query the relationship between users and tablespaces (You need to log on to sqlplus first) SQL> select USERNAME, DEFAULT_TABLESPACEfrom dba_users; 8. basic Format of the query statement for data query, insertion, modification, and deletion (you must log on to sqlplus first: SELECT query field list FROM table name/view name/synonym WHERE query condition group by group content having group condition order by sorting method ;. basic Format of the insert statement: INSER T into Table Name (column name) VALUES (value); basic format of UPDATE statement: UPDATE list name SET column name 1 = expression 1, column name 2 = expression 2 ...... WHERE condition; basic format of the DELETE statement: delete from table name WHERE condition; if the WHERE clause is not added, all records in the table will be deleted. 9. Oracle error code query method (executed by omu or oracle users without entering sqlplus) oracle @ omu0: ~> Oerr ora 12991 restart the listening service (which needs to be executed under omu or oracle users without entering sqlplus) oracle @ omu0:> lsnrctl stop oracle @ omu0:> the lsnrctl start Show command and the set command use the show command to display the current SQLPLUS environment variable value. Use the set command to set or modify the environment variable value Show all to display the value information of all stage variables. 1. The Autocommit variable is used to set whether to submit automatically. When it is set to ON, the variable is automatically submitted every time the user DML statement is set to ON. Set autocommiton 2. The Colsep variable is used to Set the delimiter between the selected columns. The default value is space. Set colsep |; 3. Feedback variable when at least N rows of records are selected for a query, the number of returned rows is displayed. The default value is 6. to disable the number of rows, set feedback to OFF Set feedbackoff; 4. Heading indicates whether to display the column title, ON by default, and Set heading OFF; 5. Linesize and pagesize Linesize indicate the row width. The default value is 80. If the row data length is 80 characters, the line feed is displayed. Pagesiez indicates the number of lines displayed per month. The default value is 24. Set linesize 10 Set pagesize 10 6. The Serveroutput variable is used to control whether the output of the stored procedure is displayed. For example, the default value of dbms_output.put_line ('Hello sqlplus ') is OFF, to display, set it to ON 7 and Sqlprompt to set the command prompt for SQLPLUS, the default value is SQL> For example, Set sqlpromptprintout> 8. The TIME command is used to Set whether the Set time on 9 and Underline variables are displayed in the command prompt. The default value is: '_' Set underline "|" Note: To save the SQLPLUS environment, you can use the store setfilename (spool command) command to save the defined SQLPLUS environment to a script file, if the configuration of a specific environment variable is automatically used every time SQLPLUS is started in the afternoon, you can change it to sqlplus \ admin \ glogin. SQL script

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.