Common Oracle commands

How to back up one or more users separately:
D: \> exp Scott/tiger file = Export File
D: \> exp system/manager owner = (user 1, user 2 ,..., User N) file = Export File
2. How to back up one or more tables separately:
D: \> exp user/password tables = table
D: \> exp user/password tables = (table 1 ,..., Table 2)
D: \> exp system/manager tables = (user. Table)
D: \> exp system/manager tables = (user 1. Table 1, user 2. Table 2)
3. How to import a specified table
D: \> exp Scott/tiger file = A. dmp
D: \> imp test/test fromuser = Scott tables = EMP file = A. dmp
D: \> imp test/test tables = dept file = A. dmp
(Note: If the export user does not have the DBA permission, you do not need to specify the fromuser and touser parameters for the import user.
If the export user has the DBA permission, the import user must also have the DBA permission)
4. How to add comments to tables and columns
SQL> comment on table is 'table annotation ';
Note created.
SQL> comment on column table. The column is 'column annotation ';
Note created.
SQL> select * From user_tab_comments where comments is not null;
SQL> select * From user_col_comments where comments is not null;

5,
View the objects (tables, views, synonyms, and snapshots) of the current user)
SQL> select * From tab;
View table structure
SQL> describe table name
Abbreviated command
SQL> DESC table name
6,
DDL and Data Definition Language: Create, alter, drop, and truncate (create, modify, delete, and truncate) (OTHERS: Rename)
DML, Data Control Language: insert, delete, select, update (add, delete, query, modify)
DCL, Data Control Language: Grant, revoke (authorization, reclaim), set role
Transaction Control: commit, rollback, savepoint (OTHERS: Lock table, set constraint (s), SET transaction)
Audit Control: Audit and noaudit
System Control: Alter System
Session control: Alter session
Other statements: Comment (add comments), explain Plan, analyze (collect statistics), validate, and call
7,
1) view all current objects

SQL> select * From tab;

2) create an empty table with the same structure as Table.

SQL> Create Table B as select * from a where 1 = 2;

SQL> Create Table B (B1, B2, B3) as select A1, A2, A3 from a where 1 = 2;

3) Check the database size and space usage

SQL> Col tablespace format A20
SQL> select B. file_id File ID,
B. tablespace_name tablespace,
B. file_name physical file name,
B. Total Bytes bytes,
(B. bytes-sum (nvl (A. bytes, 0) already in use,
Sum (nvl (A. bytes, 0) remaining,
Sum (nvl (A. bytes, 0)/(B. bytes) * 100 percentage remaining
From dba_free_space A, dba_data_files B
Where a. file_id = B. file_id
Group by B. tablespace_name, B. file_name, B. file_id, B. bytes
Order by B. tablespace_name
/
Dba_free_space -- the remaining space in the tablespace
Dba_data_files -- data file space usage

4. view existing rollback segments and their statuses

SQL> Col segment format A30
SQL> select segment_name, owner, tablespace_name, segment_id, file_id, status from dba_rollback_segs;

5. view the data file placement path

SQL> Col file_name format A50
SQL> select tablespace_name, file_id, Bytes/1024/1024, file_name from dba_data_files order by file_id;

6. display the current connected user

SQL> show user

7. Use SQL * Plus as a calculator

SQL> select 100*20 from dual;

8. connection string

SQL> Select column 1 | Column 2 from table 1;
SQL> select Concat (column 1, column 2) from table 1;

9. query the current date

SQL> select to_char (sysdate, 'yyyy-mm-dd, hh24: MI: ss') from dual;

10. Data replication between users

SQL> copy from user1 to user2 create Table2 using select * From Table1;

11. Order by cannot be used in views, but it can be replaced by group by for sorting purposes.

SQL> Create View A as select B1, B2 from B group by B1, B2;

12. Create a user through authorization

SQL> grant connect, resource to test identified by test;

13. Backup
Create Table yhda_bak as select * From yhda;

14. Submit the same record

Select Sid, count (*) from yhda a group by SID having count (*)> 1

15. Select Functions

Order by-clause that sorts the returned results by specified Columns
Distinct-only return the keyword of the unique row in the result set
Count -- returns a function that matches the total number of data rows in a query.
AVG-this function returns the average value of the specified Column
Sum-this function adds up the numbers in the specified Column
Min-this function returns the smallest non-null value in the column.
Max-this function returns the maximum value in the column
Group by-clause that collects query results by Column

13. How to export ORACLE data directly to a text file

The spool buffer pool technology in Oracle can be used to export Oracle Data to text files.
1) enter the buffer start command in Oracle PL/SQL and specify the output file name:
Spool D: \ output.txt
2) enter your SQL query in the command line:
Select mobile from customer;
Select mobile from client;
......
3) enter the buffer result command in the command line:
Spool off;
The system outputs the results in the buffer pool to the "output.txt" file.
Separated by the tab key