Oracle Foundation (i): oeacle instance and sql*plus command use

I.three-tier structure of Oracle database

1, Client-client: Dedicated to access the database. such as sqlplus, PL/SQL dev, etc.2, the database server-oracle DBMS Management system, we install this. 3, the database instance, the default is to start a oracl. 4. Data objects: Many kinds of tables, views, sequences, functions, packages, indexes, triggers, stored procedures, etc.5, Program (Schema): The user created after the table with the user name consistent (must be created after a data object will be created), MySQL has the concept of database.

Ii. Examples of oeacle

(i) Understanding of database instances1. Start Oracle just launches an instance of Oracle, where a single database (green section ORCL) is launched. You can start multiple instances. 2, the initial installation will have SYS, system, Scott three users, you can log on the start of the single database instance (ORCL). 3, three users log on to the same DB instance, see the data objects are different, because they have different permissions.

4. The most obvious concept in PL/SQL Developer is that you can select a database instance to log in when you log in. This instance can be configured in the \network\admin\tnsnames.ora file.

(ii) methods for creating a new DB instance1 . Through the wizard tools provided by Oracle (recommended) 1) Start the Database Configuration Assistant: All Programs-->oracle--> configuration and porting tools2) Follow the steps to complete.

2, manual steps to create directly (slightly)

three , classification of common databases, etc.

1. Database comparison

2. UNIX series

• UNIX has a number of series, such as Sun's Solaris,freebsd, HP's HP UNIX, AIX

3. Oracle Certification

Iv. Management Tools1, Sqlplus and SQLPLUSW tools

• Doc input SQLPLUS/SQLPLUSW.

2, PL/SQL Developer: third-party software. 1) Database options: Select the starting DB instance, such as ORAC.
3. Enterprise Management Tools (WEB):1) Prerequisites:

• Start Oracledbconsole[orac].

2) Access:/HTTP ip:1158/em3) Generally do not use can not start.

v. Sql*plus common Commands
(i) connection commands1,conn : Connect the database, switch users.

• Conn username/password @ NETWORK service name "as Sysdba/sysoper"
• When connecting as a privileged user, you must bring as SYSDBA or as Sysoper.

2) Example:

• Conn Scott/[email Protected]_sam
• Conn Scott/[email Protected]_sam as Sysdba
  • This is not Scott, but Sys.
  • This is related to the Oracle calibration method, which is specific to user management.

2,disc "Onnect": Disconnect from the current database

3,PASSW "Ord": Modify user Password

• Change the password for yourself password do not need to add user name, to modify the needs of others
• Usually change the password, use ALTER to modify the keyword

4.Show User: Displays the currently logged in users

5.exit: Disconnect and exit

(ii) document Operation command1.start and @

• Description: Run SQL script
• Example: @ d:\a.sql or start d:\a.sql
  • Run the script in A.sql

2.Edit

• Description: The command can edit the specified SQL script
• Example: Edit D:\a.sql

3,spool

• Note: This command can output the contents of the Sql*plus screen to the specified file.
• Example: Spool d:\b.sql and enter spool off. The contents of the output are recorded in the B.sql

(iii) interactive commands1.&

• Note: You can override a variable, which needs to be entered by the user when it executes.
• Example: SELECT * from emp where job= ' &job ';

(iv) Display and setting of environment variables (similar usage)1,linesize 1) Description: Sets the width of the display line, the default is 80 characters2) Example:

• Show Linesize
• Set Linesize 90

2,pagesize 1) Description: Set the number of rows displayed per page, default is 14. 2) Example

• Show pagesize
• Set PageSize 90

Oracle Foundation (i): oeacle instance and sql*plus command use