Oracle database Execute script common commands summary _oracle

1. Execute a SQL script file

Copy Code code as follows:

Sqlplus User/pass@servicename<file_name.sql

Or

Copy Code code as follows:

Sql>start File_names

Or

Copy Code code as follows:

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 www.111Cn.net 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:

sql> @ c:tempstart.sql

2. Rerun the SQL statement that was last run

sql>/

3. Output the displayed content to the specified file

Copy Code code as follows:

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

Set Colsep ' | '; – Set | For column delimiter
set trimspool on;
Set linesize;
Set pagesize;
Set newpage 1;
Set heading off;
Set term off;
Set num;
Set feedback off;
Spool path + filename;
SELECT * FROM TableName;
Spool off;

Method Two: Use the following script

Set Trimspool on
set Linesize a
set pagesize
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

Copy Code code as follows:

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

Copy Code code as follows:

SELECT host_name, instance_name, version from V$instance;

3. To know some special information about Oracle database version

Copy Code code as follows:

SELECT * from V$version;

4. Get control File name

Copy Code code as follows:

SELECT * from V$controlfile;

5, get the Oracle database redo log configuration information

Copy Code code as follows:

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

Copy Code code as follows:

SELECT * from V$logfile;

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

Copy Code code as follows:

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 * to
v$backup select * from
v$database
Sele CT * FROM V$datafile SELECT * to V$log SELECT * from
v$logfile select * to
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 is available

Control_file_record_keep_time parameter to control, the default value of this parameter is 7 days, the content of the part can be reused for 7 days, after a week this part of the content may be overwritten. 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:d Isk9control03.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

Ps:oracle file configuration for client connections:

Oracle's Directory/network/admin/tnsnames.ora

Content:

MIMI (Name of client connection) =


(DESCRIPTION = (
address_list = (Address
= (PROTOCOL = TCP) (HOST = 192.168.1.254) (PORT = 1521 ))
(connect_data =
(service_name = Ntdb. RUNNER)
)
)

Modify Table Structure

ALTER TABLE m_gl_gls3_history Add (aaaaa varchar2 (), bbbbb varchar2 ())
ALTER TABLE m_gl_gls3_history Modify ( AAAAA varchar2)
--To change the type of a field in a table or to reduce the length of a field, all record values for that field must be empty.
--If there is a record value in the field, the field length can only be enlarged and cannot be shrunk.
ALTER TABLE M_gl_gls3_history drop (AAAAA, BBBBB)