Summary of Common commands used to execute scripts in oracle databases and common commands used in oracle

Summary of Common commands used to execute scripts in oracle databases and common commands used in oracle

1. Execute an SQL script file

The Code is as follows:
Sqlplus user/pass @ servicename <file_name. SQL
Or
SQL> start file_names
Or
SQL> @ file_name
 

We can save multiple SQL statements in a text file, so that when we want to execute all the SQL statements in this file, we can use any of the following commands, which is similar to batch processing in dos.
What is the difference between @ and?
@ Equals to the start command, used to run an SQL script file.
@ Command to call the script file in the current directory, or specify the full path, or you can use the SQLPATH environment variable to search for the script file. This command is generally used to specify the full path of the file to be executed, otherwise the specified file is read from the default path (specified by the SQLPATH variable) www.111Cn.net.
@ Is used in the SQL script file to indicate that the SQL script file executed with @ is in the same directory as the file where @ is located, instead of specifying the full path of the SQL script file to be executed, or finding the SQL script file from the path specified by the SQLPATH environment variable, this command is generally used in the script file.
For example, in the c: temp directory, files start. SQL and nest_start. SQL are available. The content of the start. SQL script file is:
@ Nest_start. SQL--equivalent to @ c: tempnest_start. SQL
In SQL * plus, run the following command:

The Code is as follows:
SQL> @ c: tempstart. SQL
 

2. Run the last SQL statement again.
SQL>/
3. output the displayed content to the specified file.

The Code is as follows:
SQL> SPOOL file_name
 

All content on the screen is input to this file, including the SQL statement you entered.
Generally, we use the SPOOL method to export the tables in the database as text files in two ways, as shown below:
Method 1: script in the following format

The Code is as follows:
Set colsep '|';-set | column Separator
Set trimspool on;
Set linesize 120;
Set pagesize 2000;
Set newpage 1;
Set heading off;
Set term off;
Set num 18;
Set feedback off;
Spool path + file name;
Select * from tablename;
Spool off;
 

Method 2: Use the following script

The Code is as follows:
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

Attach some basic commands

1. Obtain the Database Name and creation date.
SELECT name, created, log_mode, open_mode FROM v $ database;

2. Host Name of the ORACLE database computer, Instance name of the ORACLE database, and version information of the ORACLE Database Management System
SELECT host_name, instance_name, version FROM v $ instance;

3. In order to know some special information about the oracle database version
Select * from v $ version;

4. Get the control file name
Select * from v $ controlfile;

5. Obtain the redo log configuration information of the Oracle database.
SELECT group #, members, bytes, status, archived FROM v $ log;
Select GROUP #, MEMBER from v $ logfile;

6. Obtain the location where each redo log (member) file in oracle is stored
Select * from v $ logfile;

7. Know the backup and recovery policies of the ORACLE database and the specific location of the archive file
Archive log list

8. Know the number of tablespaces in the ORACLE database and the status of each tablespace.
Select tablespace_name, block_size, status, contents, logging from dba_tablespaces;
Select tablespace_name, status from dba_tablespaces;

9. Know the disk on which each tablespace exists and the file name.
SELECT file_id, file_name, tablespace_name, status, bytes from dba_data_files;
Select file_name, tablespace_name from dba_data_files;

10. Know how many users and when are created on the Oracle Database System
Select username, created from dba_users;
Select username, DEFAULT_TABLESPACE from dba_users;

11. retrieving information from the control file involves the following commands:

 

Select * from v $ archived
Select * from v $ archived_log
Select * from v $ backup
Select * from v $ database
Select * from v $ datafile
Select * from v $ log
Select * from v $ logfile
Select * from v $ loghist
Select * from v $ tablespace
Select * from v $ tempfile

12. The control file consists of two parts: reusable and reusable. The size of reusable parts can be controlled by the CONTROL_FILE_RECORD_KEEP_TIME parameter. The default value of this parameter is 7 days. The content of reusable parts can be retained for 7 days. After one week, the content of this Part may be overwritten. The Reusable part is used by the recovery manager, which can be automatically expanded. The Oracle DATABASE administrator can use the following keywords (parameters) in the creat database or creat controlfile statement to indirectly affect the size of reusable parts:

MAXDATAFILES
MAXINSTANCES
MAXLOGFILES
MAXLOGHISTORY
MAXLOGMEMBERS

13. view the configuration of the Control File
SELECT type, record_size, records_total, records_used FROM v $ controlfile_record_section;

14. If your display is divided into two parts, you must use the SQL * Plus Command similar to set pagesize 100 to format the output first. Related formatting output Commands include the following:
Record_size: the number of bytes for each record.
Records_total: number of records allocated for this segment.
Records_used: number of records used in this segment.

15. Know the records used by all data files (DATAFILE), TABLESPACE (TABLESPACE), and REDO logs in the control file.
SELECT type, record_size, records_total, records_used
FROM v $ controlfile_record_section
WHERE type IN ('datafile', 'tablespace', 'redo log ');

16. Get the control file name
Select value from v $ parameter where name = 'control _ files ';
Or: select * from v $ controlfile

17. How do I add or move control files to an installed Oracle database?
Follow these steps to add or move a control file to an installed Oracle database:

A. Use the data dictionary v $ controlfile to obtain the name of the existing control file.

B. Shut down the Oracle database normally.

C. Add the new control file name to the CONTROL_FILES parameter of the parameter file.

D. Use the Copy command of the operating system to copy the existing control file to the specified location.

E. Restart the Oracle database.

F. Use the data dictionary v $ controlfile to verify that the new control file name is correct.

G. If the preceding operations are redone incorrectly, delete useless old control files without errors.

Note: If you use the server initialization parameter file (SPFILE), you cannot close the Oracle database and you should use the Oracle command of alter system set control_files in step 1 to change the location of the control file.

 

SQL> alter system set control_files =
'D: Disk3CONTROL01. CTL ',
'D: Disk6CONTROL02. CTL ',
'D: Disk9CONTROL03. CTL 'SCOPE = SPFILE;

18. Because the control file is an extremely important file, in addition to the above mentioned measures to protect multiple copies of the control file on different hard disks, after the database structure changes, you should immediately back up the control file. You can use the Oracle command to back up the control file:
Alter database backup controlfile to 'd: backupcontrol. bak ';

19. You can also back up data to a trace file. The tracing package contains the SQL statements required to recreate the control file. You can use the following SQL statement to generate this Tracing file:
Alter database backup controlfile to trace;

20. Close the oracle command normally
Shutdown immeditae