Summary of COMMON commands for Oracle database execution scripts

1. Execute a SQL script file

The code is as follows	Copy Code
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 you execute all the SQL statements in this file, use any of the above commands, which is similar to the batch processing in DOS.
What is the difference between @ and @@ 的
@ equals the start command, used to run a SQL script file.
The @ command invokes the current directory, or specifies a full path, or a script file that can be searched through the SQLPATH environment variable. This command is 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).
@@ 用 used in SQL script files, used to describe the SQL script file executed with @@ 用 the same directory as the @@ 用, without specifying the full path to execute the SQL script file, or looking for the SQL script file from the path specified by the SQLPATH environment variable, which is typically used in script files.
For example, the contents of the file Start.sql and Nest_start.sql,start.sql script files in the C:temp directory are:
@ @nest_start. sql–-equals @ c:tempnest_start.sql
Then we do this in the Sql*plus:

The code is as follows	Copy Code
sql> @ c:tempstart.sql

2. Rerun the SQL statement that was last run
sql>/
3. Output the displayed content to the specified file

The code is as follows	Copy Code
Sql> SPOOL file_name

All the content on the screen is entered into the file, including the SQL statement you entered.
In general, we use the spool method to export a table in a database to a text file in two ways, such as the following:
Method One: Use the following format script

The code is as follows	Copy Code
Set Colsep ' | '; – Set | As 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 + filename; SELECT * FROM TableName; Spool off;

Method Two: Use the following script

The code is as follows	Copy Code
Set Trimspool on Set Linesize 120 Set PageSize 2000 Set NewPage 1 Set Heading off Set term off Spool path + filename Select Col1| | ', ' | | col2| | ', ' | | col3| | ', ' | | col4| | '. ' FROM TableName; Spool off

With some basic commands

1. Get database name and date created
SELECT name, created, Log_mode, open_mode from V$database;

2, the Oracle database computer host name, Oracle database instance name and Oracle Database management system version information
SELECT host_name, instance_name, version from V$instance;

3. To know some special information about Oracle database version
SELECT * from V$version;

4. Get control File name
SELECT * from V$controlfile;

5, get the Oracle database redo log configuration information
SELECT group#, members, Bytes, status, archived from V$log;
Select Group#,member from V$logfile;

6. Get the exact location of each redo log (member) file for Oracle
SELECT * from V$logfile;

7, know the Oracle database backup and recovery strategy and the location of the archive
Archive Log List

8. Know how many table spaces are in the Oracle database and the state of each table space
Select Tablespace_name, Block_size, status, contents, logging from dba_tablespaces;
Select Tablespace_name, status from Dba_tablespaces;

9, know each table space on which disk and the name of the file and other information
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 the Oracle database system was created
Select username,created from Dba_users;
Select Username, default_tablespace from dba_users;

11, from the control file to remove information related to the following relevant 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: a reusable part and a component that is not reusable. The size of the reusable part can be controlled by the Control_file_record_keep_time parameter, the default value of which is 7 days, and the content of the part that can be reused is retained for 7 days, and the content of this part may be overwritten after a week. The reusable part is for the recovery manager to use, and this part of the content can be automatically extended. Oracle database administrators can indirectly influence the size of creat parts by using the following keywords (parameters) in the database or creat controlfile statement:

Maxdatafiles
Maxinstances
Maxlogfiles
Maxloghistory
Maxlogmembers

13, view the configuration of control files
SELECT type, record_size, Records_total, records_used from V$controlfile_record_section;

14, if your display is divided into two parts, you need to use a sql*plus command similar to set pagesize 100 to format the output first. The following are the formatted output commands for:
Record_size: The number of bytes per record.
Records_total: The number of records assigned to the segment.
Records_used: The number of records used for this segment.

15, know the control file of all data files (datafile), tablespace (tablespace), and redo logs (REDO log) used by the record
SELECT type, record_size, Records_total, records_used
From V$controlfile_record_section
WHERE type in (' DataFile ', ' tablespace ', ' REDO LOG ');

16. Get control File name
Select value from v$parameter where name = ' Control_files ';
Or: SELECT * from V$controlfile

17. How to add or move control files in an Oracle database that has already been installed?
The following are steps to add or move a control file in an Oracle database that is already installed:

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

b, gracefully shut down the Oracle database.

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 files 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 error redo the above operation, if the correct deletion of useless old control files.

Note: If you use the server initialization parameter file (SPFILE), you cannot close the Oracle database and should use the Oracle command of ALTER system set control_files in step 3rd 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 kind of file, in addition to the above mentioned will control a number of copies of the file on different hard disk protection measures, after the structure of the database changes, you should immediately back up the control files. You can use the Oracle command to back up the control file:
ALTER DATABASE backup Controlfile to ' D:backupcontrol.bak ';

19, you can also backup to a tracking file. The trace file contains the SQL statements required to rebuild the control file. You can use the following SQL statement to generate this trace file:
ALTER DATABASE backup Controlfile to trace;

20, the normal shutdown Oracle command
Shutdown Immeditae