Oracle BASICS (1): Use the oracle instance and SQL * plus command

Oracle BASICS (1): Use the oracle instance and SQL * plus command
I. Three-tier structure of oracle Database
1. Client: used to access databases. Such as sqlplus and pl/SQL dev. 2. database Server-oracle dbms (database management system). This is what we install. 3. database instance. The default value is to start a L. 4. Data Objects: There are many types of data objects, including tables, views, sequences, functions, packages, indexes, triggers, and stored procedures. 5. solution (Schema ): the user created after creating a table is consistent with the user name (it must be created after a data object is created). mysql has the concept of database.



Ii. Oeacle instance

(1) Understanding of database instances 1. Starting Oracle only starts an Oracle instance, that is, starting a single database (the green part of ORCL ). You can start multiple instances. 2. Three users, namely sys, system, and scott, can log on to the single database instance (ORCL) started on ). 3. When three users log on to the same database instance, the data objects are different because they have different permissions.



4. This concept is the most obvious in PL/SQL Developer. You can select the database instance to log on. This instance can be configured in the \ network \ admin \ tnsnames. ora file.


(2) how to create a new database instance 1. Use the wizard tool provided by oracle (recommended) 1) Start the Database Configuration ASSISTANT: all programs --> oracle --> Configure and port the tool 2) follow these steps.


2. manually create an instance (omitted)
Iii. Classification of common databases
1. database comparison


2. Unix Series
Unix has many series, such as sun's solaris, freebsd, hp's hp unix, aix 3, and oracle certification.

 

 

Iv. Management Tools1. sqlplus and sqlplusw tools
Enter sqlplus/sqlplusw In the doc. 2. PL/SQL Developer: third-party software. 1) database option: select the database instance to be started, such as ORAC.
3. enterprise management tools (Web): 1) Prerequisites:
Start OracleDBConsole [orac].

2) Access: http: // IP: 1158/em 3) generally, it can be disabled if it is not used.

 

 

5. Common SQL * plus commands
(1) connection command 1. conn: connect to the database and switch users.
Conn username/password @ network service name [as sysdba/sysoper]
When connecting with a privileged user identity, you must include as sysdba or as sysoper. 2) instance:

 

 

Conn scott/tiger @ DEV_SAM as sysdba is not actually logged on to scott, but sys. This is related to the Oracle verification method. For details, see user management.

 

2. disc [onnect]: disconnect from the current database

3. passw [ord]: Change User Password

 

You do not need to add a user name after changing your password.
Change the password for others and use alter to modify the keyword.

 

4. show user: displays the current logon user

5. exit: disconnect and exit

 

(2) file operation commands 1. start and @
Run the SQL script
Instance: @ d: \ a. SQL or start d: \ a. SQL
Run scripts in a. SQL

 

 

2. edit
Note: This command can be used to edit the specified SQL script.
Instance: edit d: \ a. SQL 3, spool
Note: This command can output the content on the SQL * plus screen to a specified file.
Instance: spool d: \ B. SQL and enter spool off. The output content is recorded in B. SQL.

 

 

(3) Interactive commands 1 ,&
Note: This variable can be used as an alternative, and must be input during execution.
Instance: select * from emp where job = '& job ';

(4) display and set environment variables (usage is similar) 1. linesize 1) Description: sets the width of the display line. The default value is 80 characters. 2) instance:
Show linesize
Set linesize 90 2. pagesize 1) Description: sets the number of rows displayed on each page. The default value is 14. 2) Instances
Show pagesize
Set pagesize 90