Summary of Common commands for executing scripts in Oracle databases and common commands in oracle

Summary of Common commands for executing scripts in Oracle databases and common commands in oracle

1. Execute an SQL script file

Copy codeThe Code is as follows:
Sqlplus user/pass @ servicename <file_name. SQL

Or

Copy codeThe Code is as follows:
SQL> start file_names

Or

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

SQL> @ c: tempstart. SQL

2. Run the last SQL statement again.

SQL>/

3. output the displayed content to the specified file.

Copy codeThe 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

Set colsep '|';-set | set trimspool on as the column separator; 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

Set trimspool onset linesize 120 set pagesize 2000 set newpage 1 set heading offset term offspool path + file name select col1 | ',' | col2 | ', '| col3 |', '| col4 | '.. 'from tablename; spool off

Attach some basic commands

1. Obtain the Database Name and creation date.

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

Copy codeThe Code is as follows:
SELECT host_name, instance_name, version FROM v $ instance;

3. In order to know some special information about the oracle database version

Copy codeThe Code is as follows:
Select * from v $ version;

4. Get the control file name

Copy codeThe Code is as follows:
Select * from v $ controlfile;

5. Obtain the redo log configuration information of the Oracle database.

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

Copy codeThe Code is as follows:
Select * from v $ logfile;

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

Copy codeThe Code is as follows:
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$archivedselect * from v$archived_logselect * from v$backupselect * from v$databaseselect * from v$datafileselect * from v$logselect * from v$logfileselect * from v$loghistselect * from v$tablespaceselect * from v$tempfile

12. The control file consists of two parts: reusable and reusable.Size of reusable parts available

The CONTROL_FILE_RECORD_KEEP_TIME parameter is controlled. The default value of this parameter is 7 days. The content of the reusable part is 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:

MAXDATAFILESMAXINSTANCESMAXLOGFILESMAXLOGHISTORYMAXLOGMEMBERS

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_usedFROM v$controlfile_record_sectionWHERE 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

PS: file configuration for oracle client connection:

Oracle directory/network/ADMIN/tnsnames. ora

Content:

MIMI (Client connection name) = (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 (20), bbbbb varchar2 (10) alter table m_gl_gls3_history modify (aaaaa varchar2 (10 )) -- to change the field type in the table or reduce the field length, all record values of this field must be empty. -- If the modified field has a record value, the length of the field can only be expanded and cannot be reduced. Alter table m_gl_gls3_history drop (aaaaa, bbbbb)

Articles you may be interested in:

• Oracle11.2 the process of manually creating a database using the command line
• How to view the execution plan of an ORACLE Database
• Oracle archive mode Common commands for ORACLE Database archive logs
• Discussion: how to view the actual SQL statements executed by a process in Oracle Database
• Summary of common Oracle database commands
• How to use oracle Database Import and Export commands
• Oracle Database Password Reset, Import and Export command example application