Oracle SQL Plus command collection

Oracle SQL * plus is a client tool that interacts with oracle. In SQL * plus, you can run the SQL * plus command and the SQL * plus statement.
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.

Apart from SQL * plus statements, other statements executed in SQL * plus are called SQL * plus commands. After they are executed, they are not stored in the memory area of the SQL buffer. They are generally used to format and display the output results to facilitate the preparation of reports.
The following describes some common SQL * plus commands:

1) enter the SQL command line status without logon
> Sqlplus/nolog
SQL>

2) log on to the database with a specified account
> Sqlplus [user name]/[User Password] @ [local service name]
Or
> Sqlplus [user name]/[User Password] @ [server IP address]: [server port, usually 1521]/[SID]
If the account is sysdba, add the parameter as sysdba to the command line, as shown in figure
> Sqlplus [user name]/[User Password] @ [local service name] as sysdba

3) execute the SQL script file
SQL> start [SQL file]
Or
SQL> @ [SQL file]

4) re-run the last running SQL statement
SQL>/

5) output the displayed content to the specified file
SQL> SPOOL [file name]
All content on the screen is included in this file, including the SQL statement you entered.
To disable spool output, run the following command:
SQL> SPOOL OFF
The output content is displayed in the output file only when spool output is disabled.

6) display the structure of a table
SQL> desc table_name

7) display the value of the current environment variable:
SQL> Show all

8) displays the error information of the currently created function, stored procedure, trigger, package, and other objects.
SQL> Show error
When an error occurs in creating a function or stored procedure, you can use this command to view the error and related error information at that place, modify the information, and compile the code again.

9) display the value of the initialization parameter:
SQL> show PARAMETERS [parameter_name]

10) display the database version:
SQL> show REL [statement]

11) display the SGA size
SQL> show SGA

12) display the current user name
SQL> show user

13) create a user and grant permissions
SQL> create user [user name] identified by [Password] default tablespace [default tablespace name];
SQL> grant connect, resource to [user name];
Or SQL> grant create session, create table to [user name];
Connect only has the create session permission, which is the basic permission to connect to the database. resource is the developer permission. You can query the specific content in the system table as follows:
SQL> select * from role_sys_privs where role = 'resource ';
Resource generally includes the following permissions:
CREATE SEQUENCE
CREATE TRIGGER
CREATE CLUSTER
CREATE PROCEDURE
CREATE TYPE
CREATE OPERATOR
CREATE TABLE
CREATE INDEXTYPE
To change the password, run the following command:
SQL> alert user [user name] identified by [New Password];

14) create a user and grant permissions
SQL> drop user [user name] cascade;
If the urer has just created objects without tables, you can use the drop user command to delete them. Otherwise, you must use the drop user cascade command to delete the objects owned by the user.

15) common functions
Sysdate -- current date and time
Lower (utl_raw.cast_to_raw (DBMS_OBFUSCATION_TOOLKIT.MD5 (INPUT_STRING => '20140901') -- md5 Encryption
Concat ('A', 'B') -- merge two strings
'A' | 'B' | 'C' -- Merge multiple strings
To_date ('1970-01-01 20:28:03 ', 'yyyy-MM-DD HH24: MI: ss') --> converts a string to the date type. For more information about how to process the date Field in ORACLE, see
To_char (t. create_date, 'yyyy-MM-DD HH24: MI: ss') --> convert date to string

16) query of some special tables
Query all tables of the specified user (note that the user name is capitalized): select * from all_tables where OWNER = '[USERNAME ]';
The value of TABLESPACE_NAME In the result column is the name of the tablespace.
Query all constraints of a specified table: select * from all_constraints where TABLE_NAME = '[TABLE_NAME ]';
Find all foreign keys in the specified table: select * from all_constraints where TABLE_NAME = '[TABLE_NAME]' and CONSTRAINT_TYPE = 'R ';
For CONSTRAINT_TYPE values, R -- foreign key, P -- primary key, U -- uniqueness constraint, and C -- general constraint (for example, a column cannot be blank)
Query all stored procedures and functions of a specified user: select * from all_procedures where OWNER = '[USERNAME ]';
In the field, the value of the OBJECT_TYPE column is FUNCTION, indicating the FUNCTION. The value of PROCEDURE indicates the stored PROCEDURE.
Specify the index: select/* + index (tablename index_name) */from tablename

Note:
1) run the SQL file using the SQL Plus command line. No blank rows are allowed in the table creation statement; otherwise, a syntax error is reported.
2) run SQL file with SQL Plus command line, if the file contains Chinese, file format must be ANSI (GBK), set to UTF-8 will be Chinese garbled