Sqlplus Landing Use

SELECT * FROM dept;

Input order by dname; Append text command del n DELETE statement

Celar buffer; command to clear the buffer

Conn SYS as SYSDBA login user

Typically, Sqlplus is installed by default as long as the server-side or client of Oracle is installed. Instead, SQLPLUSW just adds a form on sqlplus that executes the SQL statement exactly like Sqlplus. Sqlplus's main application scenario is to view/set database parameters, execute SQL scripts, and more.

1. Log in to the database using Sqlplus

Both Sqlplus and SQLPLUSW are executables in the Oracle installation directory. You can log in by executing the following command in the Command window.

Sqlplus Username/[email protected]

Where Username/password is the user name and password used to log in to the database; Netservicename is the local net service name for the database connection.

To log in to the Net service TST, for example, you need to execute the following code on the command line:

Sqlplus System/[email protected]

Interface after successfully logging in to the TST database:

2. View database Parameters

In Oracle, the show parameter command should be used to view database parameters. For example, the command to view the current DB instance name is as follows.

Where show parameter requires the database to display parameters; instance_name is the parameter name. The result of the analysis execution shows that the instance name of the current database is TST.

Of course, for the user, it is not possible to specify the parameter name accurately every time. As a result, show parameter supports partial matching, meaning that the user only needs to enter part of the parameter name.

When the user executes the show parameter instance command, Oracle displays all parameters that contain the instance typeface to the user.

Note that the show parameter command here does not support wildcard characters.

3. Close/Start the database

(1) Log in to the database as DBA

There is no need to enter a username/password on the local computer of the database server, as SYSDBA to log in to the database as the database administrator.

(2) Closing the database

To close the database, you should use the Shutdown command followed by the close option, typically using the immediate ———— to close the database immediately. The process for database shutdown is:

Database shutdown ———— Unload ———— instance of the database.

(3) Restart the database. You can restart the database by using the startup command.

In the process of starting the database, if an exception occurs, Oracle will give an error message. For example, Ora-32004:obsolete and/or deprecated parameter (s) specified is caused by improper database startup parameter settings.

4. Modifying system parameters

System parameters can be easily modified using Sqlplus. The most common is to modify the flash memory size.

The show parameter command allows you to view the existing size of the flash memory as 2G and now you need to set it to 5G, you can use the following command:

Where alter system is used to modify the systems environment; Set DB_RECOVERY_FILE_DEST_SIZE=5G sets the value of the parameter db_recovery_file_dest_size to 5g;scope=both. Represents the application of parameter modifications to the current environment and database startup parameters.

5. Querying data

Using Sqlplus to query a small data table is acceptable, but querying a larger data table using Sqlplus is very poor readability. Because the default formatting effect of Sqlplus is not ideal. Using the Scott user login data default database ORCL, and attempting to query the table EMP, will get the following result:

At this point, logging in to the database with Scott may prompt an error:

Workaround:

Alter user Scott account unlock;

Alter user Scott identified by Tiger;

Re-login to the database with Scott users after unlocking

The query results are not displayed on a single line and can be modified by a command to appear on the same row.

Sqlplus Landing Use