Tips for using sqlplus

Tips for using sqlplus

During Oracle maintenance, Oracle currently has many easy-to-use client tools, such as PLSQL, delveloer, and toad. However, as a database administrator, you also need to deal with sqlplus frequently, some common operations often need to be completed in sqlplus. If sqlplus is unfamiliar, it will greatly reduce our maintenance efficiency, this article mainly introduces some useful sqlplus skills in database management.

Tip 1: Modify the sqlplus prompt

1 Problem description:

Oracle's sqlplus does not have a system prompt by default. Due to the frequent need to perform high-risk operations such as Shutdown under sqlplus during routine maintenance operations, if the operation object is incorrect, a device accident occurs.

2 solution:

By modifying the sqlplus prompt symbol, you can clearly identify the database to be operated to avoid Operation errors;

3. handling process:

When the sqlplus program is started, you can set relevant parameters to execute login. SQL, and then modify the login. the SQL script can define some statements to modify the sqlplus prompt for easy identification and login. the SQL file path needs to be set through the operating system environment variable sqlpath:

In Windows, you can set the registry project to D:/Oracle/ora92/DBS by modifying the registry project in sqlpath (usually in Oracle-home ).

For UNIX, you can directly add the sqlpath environment variable to the profile file of an Oracle user. You can set export sqlpath =/home/Oracle/APP/Oracle/product/9.2/dbs. The specific login. SQL, as shown below, can be modified as needed: Set serveroutput on size 1000000

Column old_name new_value pname

Set termout off

Select lower (User) | '@' | instance_name old_name from V $ instance;

Set sqlprompt '& pname>'

Set termout on

In this way, the username @ instancname> is displayed every time you log on to the system through sqlplus to better identify the database to be connected. Note: The connected user must have the permission to view v $ instance.

Tip 2: Execute the operating system command under sqlplus

When using sqlplus, you often need to execute some operating system commands. You can use either of the following methods:

1) run the host command in the sqlplus command line. You can switch to the operating system command line. After the execution, exit can switch back to the sqlplus command line.

2) Run "!" In sqlplus directly "!" Followed by the specific command line (but not supported in Windows ).

Tip 3: Modify the SQL statement in sqlplus:

When using sqlplus, you often need to modify SQL statements. However, the commands for modifying SQL statements under sqlplus are too cumbersome and hard to remember. You can define relevant variables to achieve good results.

1) define_editor = VI can be defined in Unix. If you need to modify an SQL statement, you can directly use Edit in the command line to enter the VI environment. This is the same as the VI operation function for convenient operations, the setting method is directly typed in the sqlplus command line:

Define_editor = VI;

In Windows, define_editor = notepad can be defined. If you need to modify the SQL statement, you can directly use Edit in the command line to enter the notepad compiling environment. In Windows, the operation is much easier;

Tip 4: view the execution time of SQL statements under sqlplus

1) run the set time on command directly on the sqlplus command line (display the execution time of the SQL statement );

2) execute the set timing on command on the sqlplus command line (count the execution time of the SQL statement );

Tip 5: view the SQL statement execution plan under sqlplus

During service maintenance, you often need to check the execution plan of an SQL statement to see if appropriate indexes are used. You can perform the following operations to complete set autotrace traceonly exp.

Select * From cncdlp114.t _ unit where unitcode = 'hwjsgs ';

Set autotrace off

After the statement is executed, you can see the statement execution plan:

Note: before performing the operation, the user has a plan_table table (usually ).

Statement to create -- Create Table
Create Table plan_table
(
Statement_id varchar2 (30 ),
Plan_id number,
Timestamp date,
Remarks varchar2 (4000 ),
Operation varchar2 (30 ),
Options varchar2 (255 ),
Object_node varchar2 (128 ),
Object_owner varchar2 (30 ),
Object_name varchar2 (30 ),
Object_alias varchar2 (65 ),
Object_instance integer,
Object_type varchar2 (30 ),
Optimizer varchar2 (255 ),
Search_columns number,
Id integer,
Parent_id integer,
Depth integer,
Position integer,
Cost integer,
Cardinality integer,
Bytes Integer,
Other_tag varchar2 (255 ),
Partition_start varchar2 (255 ),
Partition_stop varchar2 (255 ),
Partition_id integer,
Other long,
Distribution varchar2 (30 ),
Cpu_cost integer,
Io_cost integer,
Temp_space integer,
Access_predicates varchar2 (4000 ),
Filter_predicates varchar2 (4000 ),
Projection varchar2 (4000 ),
Time integer,
Qblock_name varchar2 (30)
)

Conclusion:

In fact, the sqlplus function of Oracle is very powerful. If you want to study other functions, refer

Oracle documents to improve maintenance skills

From: http://www.ixpub.net/thread-870623-1-5.html