Example of using SQL plus basic commands

Time:2022-4-28

1. The difference between DBA, all, and user in Oracle

1. Conclusion: 'different permission sizes': dba_ * > all_* >  user_*
   (1) dba_* :  Can access all objects in the 'database' (premise: the user is a DBA user)
   (2) all_* : All objects that a user 'owns' or' can access'
   (3) user_*: All objects' owned 'by a user

2. Query whether it is a DBA user
   select * from dba_role_privs t where t.granted_role = 'DBA';

2、desc

Desc: universal view command

View dept table
SQL> desc dept
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

3. Set the running environment of SQL * plus, including the number of characters displayed in each line, the number of lines displayed on each page, and the number of blank lines before the print title on each page. Basic syntax of set command

In Oracle 11g database, users can use the set command to set the running environment of SQL * plus;

set system_variable value;

system_ Variable: variable name;
Value: variable value;

The environment variables set by the set command are temporary, not permanent;

When the user exits the SQL * plus environment, all the environment parameters set by the user will disappear;

Use the set command to set the running environment

PageSize variable: this variable is used to set the number of lines from the top title to the end of the page;
set pagesize value;
The default value of value is 14;

Use the show PageSize command to display the number of lines on a page in the current SQL * plus environment;
show pagesize;

Similarly:
Newpage variable: this variable is used to set the number of blank lines in a page;
set newpage value;
The default value of value is 1;

show newpage;

Linesize variable:
This variable is used to set the maximum total number of characters displayed in one line in SQL * plus environment;
set linesize value;
The default value of value is 80;

show linesize;

Pause variable:
This variable is used to set whether the SQL * plus output results are scrolled;
set pause value;

The value variable has the following three conditions:
-Off: the default value, which means that the returned results are output at one time, and each page in the middle will not be suspended;
-On: indicates that each page of the output result is suspended and will continue to be displayed after the user presses the Enter key;
-Text: set the value of text after the value of pause is set to on, and then the string will be displayed every time the pause occurs; 
        When the value of pause is set to off, setting the value of text has no meaning;

Oracle executes SQL script file with command

When there are too many SQL commands (the SQL file is too large), the execution of PLSQL is slow and easy to timeout. At this time, you can directly execute the SQL script file with sqlplus command.

@ H:/sql/test. SQL (absolute path)

Spool in Oracle writes the queried data to a file

Usually, we use the spool method. When exporting tables in the database into text files, we use two methods, as follows:

Method 1: use the following format script
Set colsep '' ----- set column separator
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
Spool path + file name
select * from tablename;
spool off

Method 2: use the following script
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
Spool path + file name
select col1||','||col2||','||col3||','||col4||'..' from tablename;
spool off


Difference:

Compare the above methods, that is, method 1 uses the set separator, and then sqlplus uses the set separator to segment the field, and method 2 splices the separator in the select statement, that is, manually control the output format.

In practice, I found that the data exported through method 1 has great uncertainty. The probability of error when the data exported by this method is imported by SQL is more than 95%, especially for a large number of data tables, such as tables with 1 million records, and the exported data files are crazy.

The format of the data file exported by method 2 is very regular, and the size of the data file may be about 1 / 4 of that of method 1. When the data files exported by this method are imported by sqll, the possibility of error is very small, and they can be imported successfully.

Therefore, in practice, I suggest you use second-hand workers to control the format of spool files, which can reduce the possibility of errors and avoid many detours.

Ttitle, btitle command

It mainly sets the header title and footer title, as follows:
Eg: set the middle header title and the right date of the day as the tail title for the query result, and define the column title for the query result column.

SQL > ttitle center 'result';
SQL> btitle right '2021/03/30';
SQL> select * from dept; ;

                                      result
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON





                                                                      2021/03/30
If you want to undo ttitle and btitle:
SQL> ttitle off;
SQL> btitle off;
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

Column command

Format format output

Format options
Used to format the specified column
SQL> column sal format $99,99,99;
SQL> select empno,ename,sal from scott.emp;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH           $8,00
      7499 ALLEN          $16,00
      7521 WARD           $12,50
      7566 JONES          $29,75
      7654 MARTIN         $12,50
      7698 BLAKE          $28,50
      7782 CLARK          $24,50
      7788 SCOTT          $30,00
      7839 KING           $50,00
      7844 TURNER         $15,00
      7876 ADAMS          $11,00

     EMPNO ENAME             SAL
---------- ---------- ----------
      7900 JAMES           $9,50
      7902 FORD           $30,00
      7934 MILLER         $13,00

14 rows selected.

Heading option
Used to define column headings
SQL > col empno heading employee number;
SQL > col ename heading employee name;
SQL > col Sal heading employee salary;
SQL> select empno,ename,sal from scott.emp;

  Employee number employee name employee salary
---------- ---------- ----------
      7369 SMITH           $8,00
      7499 ALLEN          $16,00
      7521 WARD           $12,50
      7566 JONES          $29,75
      7654 MARTIN         $12,50
      7698 BLAKE          $28,50
      7782 CLARK          $24,50
      7788 SCOTT          $30,00
      7839 KING           $50,00
      7844 TURNER         $15,00
      7876 ADAMS          $11,00

  Employee number employee name employee salary
---------- ---------- ----------
      7900 JAMES           $9,50
      7902 FORD           $30,00
      7934 MILLER         $13,00

14 rows selected.


Operation on cache

(1) View the SQL commands in the current cache.
>list
(2) Execute SQL commands stored in the cache.
>run
(3) Save the contents of the cache to an SQL script file (the file name is self-made).
>save C:/sql.sql
(4) Put the contents of a script file into the cache and execute the statements in the cache.
>get C:/sql.sql
>start C:/sql.sql
(5) Clears the contents of the cache.
>clear buffer

SQL> save H:/sql/test3.txt;
Created file H:/sql/test3.txt
SQL> get H:/sql/test.sql;
  1  select table_name from user_tables;
  2* desc dept
SQL> start H:/sql/test.sql;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL> clear buffer;
buffer cleared
SQL>

summary

This is the end of this article on the use of SQL plus basic commands. For more information about the use of SQL plus commands, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!