Oracle Note (ii) sqlplus command

Oracle Note (ii) sqlplus command

For Oracle database operations, the command line is used primarily, and all commands are done using sqlplus, and there are two forms for sqlplus.

• One is the sqlplus:sqlplus.exe of DOS style;
• The other is the Windows-style sqlplus:sqlplusw.exe;

The main use of the Oracle 10g is the SQLPLUSW command, as it can make some adjustments to the display data.

The default database instance that is already available is found.

Example: entering query directives

SELECT * from EMP;

The data is now displayed correctly, but there is still a problem with the data wrapping, because this is the default data size for each row, which can now be adjusted with two commands:

• Set the record length to be displayed per line: Set Linesize 300;
• Set the length of records displayed per page: Set PAGESIZE 30;

The unification of the two commands later is called a format command, but the settings for these formatting commands under Sqlplus are not obvious.

But SQLPLUSW although the display is convenient, but for the editing of the command is not very convenient, such as normal operation, the command is wrong, you must use the arrow keys to move to the wrong position to modify, but in SQLPLUSW, the key can only control the movement of the screen, So this command is not suitable for editing at all, so the ED and @ instructions are provided in SQLPLUSW, which can be called and executed by Notepad;

• Using ED, call Notepad Program: Ed A;

The user is prompted to say that the "a.sql" file is not found now, asks whether to create it, selects "Yes", but after using Ed to open Notepad, the SQLPLUSW window in Oracle will go into blocking state and cannot be used.

• Use @ Execute program: @a;

However, in Windows, due to the provision of many convenient editing software, it is generally in the external editing programs, for example, now on the D disk to create a demo.txt file, which saves the program code.

This time to read the file on the hard disk, you must write the full path: @d:\demo.txt, and if now the suffix of this file is "*.sql", you do not have to enter the file suffix (demo.sql), enter: @d:\demo, or more convenient direct input: @D: Demo

In a database, there will be many users (now known to be four users), this each user will have more than one of their own data table, so to see all the data table, you can use the following command:

SELECT * from Tab;

And because of the number of users, so if you want to know the current login user, you can use: SHOW user; Command complete.

And in the SQLPLUSW can also be directly user login switch, using the following syntax to complete:

CONN username/password [as SYSDBA];

Login with sys User: CONN sys/change_on_install as SYSDBA;

However, once the SYS connection is used, the EMP table data cannot be queried directly.

For each table, there are users who belong to it, so the full name of a table is "User name. Table name" or "Schema name. Table name", so that different users want to access other users ' tables, you should add the user name, which is now the access name:

SELECT * from Scott.emp;

Once the Super administrator login is used, it is possible to manually control the opening and closing of the database instance by command;

• To close a DB instance: SHUTDOWN IMMEDIATE;
  • Once closed, the user cannot directly connect to the SQLPLUSW command, you can use "/nolog" login, and then use the administrator login;

Sql> Conn Sys/sys as SYSDBA;

is connected.

sql> shutdown immediate;

The database is closed.

The database has been uninstalled.

The ORACLE routine has been closed.

• To start a DB instance: STARTUP;

In addition, the SQLPLUSW Command window can also invoke native operation commands, using "host" as the prefix.

HOST COPY D:\demo.sql D:\hello.txt

The above command must be mastered and will definitely be used in the future.

Oracle Note (ii) sqlplus command