Several common SQL plus commands

I think in this age, few developers will use sqlplus, which is a primitive tool. However, many DBAs may like it, but I really don't like it, toad is easy to use! Unfortunately, sqlplus is not often used. Here, we despise the Security Department in XX insurance company. Maybe they really think they are very reasonable, they are also the normal existence of rigid Chinese companies, but what I want to say is that you are doing this without security, and your work efficiency is greatly reduced! In fact, developers should be given at least sufficient permissions to the core developers. If you are worried about security, you can completely record the operation track and be held accountable afterwards. This is not a rigid system! Moreover, this can also extend to our politics and society to form an post-event accountability system, which is conducive to maintaining enough elasticity and innovation for society.

It's too much!

Export a large amount of data to a file

SQL> spool OBJ;/* export to the obj. LST file in the current directory */
SQL> set heading off; -- disable the output Column Title
SQL> set feedback off; -- disable display of Count feedback from the last row
SQL> set pagesize 50000; -- sets the number of rows displayed to print the column name once.
SQL> select * From tb_src;
1 13132520000
2 13132520001
3 13132520002
4 13132520003
5 13132520004
6 13132520005
7 13132520006
8 13132520007
9 13132520008
10 13132520009
SQL> spool off;

Download table data in text format
Oracle lacks a tool to output table data to text files. Therefore, it can only use sqlplus and UNIX tools for flexible processing.

Sqlplus-s dbuser/Oracle <EOF>/dev/null
Set colsep |;
Set echo off;
Set feedback off;
Set heading off;
Set pagesize 0;
Set linesize 1000;
Set numwidth 12;
Set termout off;
Set trimout on;
Set trimspool on;
Spool tmp.txt;
Select * from EMP;
Spool off;
Exit
EOF
Tr-D ''<tmp.txt> emp.txt: Optional.
Note: spool must be used. If you use> tmp.txt directly in the command line, data may be missing, at least on unixware7.
Assume that a domain is Char (N). If the carriage return \ n is displayed in the middle, the format of the downloaded record will be disordered. This method is not recommended.

It is a little simpler to query at ordinary times. below is enough

Set serveroutput on size 1000000
Set trimspool on
Sets long 5000
Set linesize 100
Set pagesize 9999

Of course, you need to query the table structure frequently.

-- Oracle query table structure
Select
Column_name,
Data_type,
Data_length,
Nullable
From
All_tab_columns
Where table_name = upper ('t_ clmvhl ')

The table name must be capitalized.

---------------------------------------

Let's despise the so-called security department!

I have to use Telnet twice to export a data file.

We have to go through su once, although the great securecrt is very powerful

However, skipping and skipping are really annoying.