Oracle XE installation and user operation

install

resources

1)OracleXE112_Win64.zip database service management platform

2)plsqldeveloper_x64.rar third party client

Oracle is a software company. This software company, founded in California in 1977, is the first company in the world to launch relational data management system (RDBMS).

Now, their RDBMS are widely used in various operating environments: Windows NT, UNIX based minicomputers, IBM mainframes and some special hardware operating system platforms. In fact, Oracle has become the world's largest RDBMS supplier and the world's leading information processing software supplier.

Oracle Database 11g Express is a free version of Oracle database. It supports most of the functions of the standard version and occupies a small memory. It is very good. It's worth recommending.

• 11g XE is available in Windows and Linux versions.
• As a free Oracle database version, XE has the following limitations:
  • The maximum database size is 11 GB
  • The maximum memory available is 1G
  • Only one XE instance can be installed on a machine
  • XE can only use a single CPU and cannot perform distributed processing on multiple CPUs

• Cannot store in Chinese Directory
• Remember password

Oracle services

Oracle is a service-based database system. Oracle database can be used only after the service is started

1. OracleJobSchedulerXE: an external scheduling task service, which is responsible for scheduling tasks in Oracle. It is only available after version 10.1. It's generally useless.
2. OracleMTSRecoveryService: when Oracle participates in the distributed transaction of Microsoft Transaction Server, the transaction is responsible for solving the questionable transaction.
3. OracleServiceXE: it is the service of ORCL library. It is responsible for the foundation of Oracle and database startup. The Oracle database can start normally only when the service is started.
4. OracleXEClrAgent: Oracle provides CLR integration on Windows platform. Since CLR operations are run using the extproc process, they are usually done in a single session using dedicated (single threaded) extproc. This may not be the best way to handle CLR calls. ClrAgent provides a multithreading mechanism, so a single extproc process can serve multiple CLR calls.
5. OracleXETNSListener: this service is responsible for listening for incoming connections and passing successful connections to the database engine. Note that if this service is turned off, you will not be able to connect to the database remotely. Existing connections will not be affected.

Start OracleServiceXE and OracleXETNSListener to use Oracle database normally.

• Start and stop Oracle server commands from the console

net start OracleServiceXE

net stop OracleServiceXE

• Right click in the service panel to stop and start the service

[external chain picture transfer failed. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-udiz6rhv-1634730510516)( https://i.loli.net/2021/10/18/sfWUHzIhgJ2CQnE.png )]

Oracle client

After Oracle is installed, it comes with a console client called sqlplus. With this client, there are at least available clients when you maintain the server. Although it is not easy to use, it is better than none.

Using sqlplus requires two steps

• Step 1: start the client

Enter sqlplus /nolog in the console to start the client. Note that only the client has been started and has not logged in to Oracle.

[external chain picture transfer failed. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-wlt2ohor-1634730510517)( https://i.loli.net/2021/10/17/aI7cvYzr4yW8Owe.png )]

• Step 2: log in to Oracle

  On the client side, enter conn system/manager as sysdba; Log in to oracle

  • conn is the command to connect to oracle
  • system is the administrator account of oracle
  • manager is the default password of the system account. You can use any password when connecting to this computer. If you don't believe it, try it
  • as is a keyword, fixed writing
  • sysdba is the login identity and represents the system database administrator. This identity has the highest authority

[external chain picture transfer failed. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-jy3mtduy-1634730510520)( https://i.loli.net/2021/10/17/N2anpQcm9gWIBvZ.png )]

• Practice several common commands in sqlplus

• select * from all_users; -- View all users select * from dba_roles; -- View all roles

  plsqldeveloper client

  PL SQL developer is a visual interface.

  

  • Practice several common commands in the Command windows window and compare them with the sqlplus client

  select * from all_users; --View all users select * from dba_roles; --View all roles

  • SQL cache for plsqldeveloper
    • PL sql developer always caches the last sql statement by default
    • Use the ed command to open the cache [editable]
    • Use / execute commands in cache
  • Tip: Oracle commands end with a semicolon (;), which means that the command is completed and executed. The system will save the command in the cache at the same time. Only the recently executed commands will be saved in the cache. If the command in the cache is re executed, the left slash symbol (/) will be used directly. If the command does not end with a semicolon, the command is written to the cache and saved, but not executed.
  Permissions in Oracle

  System permission refers to the power to execute specific types of SQL commands, which is used to control one or a kind of database operations performed by users

  System permissionseffectCREATE SESSIONConnect to databaseCREATE TABLEBuild tableCREATE TABLESPACECreate tablespaceCREATE VIEWCreate viewCREATE SEQUENCEEstablish sequenceCREATE USEREstablish user

If multiple users need the same multiple permissions, it is cumbersome to assign multiple permissions to each user. Therefore, multiple permissions can be combined into one role, and the role can be assigned to multiple users, so that each user has the same permissions.

A role is a collection of related permissions. The main purpose of using a role is to simplify permission management.

• – temporary users, especially users who do not need to create tables, are usually given only connect role
• – connect is a simple oracle permission. This permission only has access to other users' tables, including select/insert/update and delete.
• – users with the connect role can also create tables, views, sequences, clusters, synonyms, sessions, and other data links

• -More reliable and formal database users can grant resource role s.
• – resource gives users additional permissions to create their own tables, sequences, procedures, triggers, indexes, and clusters.

• – dba role has all system permissions
• – includes unlimited space quotas and the ability to grant various permissions to other users. The system is owned by dba users

That is, the database operator. The permissions include: opening the database server, closing the database server, backing up the database, restoring the database, log archiving and session restriction.

That is, the database administrator. The permissions include: opening the database server, closing the database server, backing up the database, restoring the database, log archiving, session restriction, management functions, and creating the database. The sys user must log in as sysdba, and the system user can log in as normal.

That is, ordinary users can only query the data of some data tables. The default identity is normal user.

Users in Oracle

• Oracle: a project corresponds to a user [generally, there are many users in a database, and there are many tables below]
• MySql: a project corresponds to a database

Although multiple databases can be installed in an Oracle database server, a database needs to occupy a very large memory space, so generally, only one database is installed in a server. Each database can have many users. Different users have their own database objects (such as database tables). If a user accesses the database objects of other users, the other user must grant certain permissions. Tables created by different users can only be accessed by the current user. Therefore, in Oracle development, different applications only need to be accessed by different users.

• User: sys, password: change_on_install
• User: system, password: manager

Recommended system user

Think: what if the password of a company's database account is lost?

Answer: any password can be used to log in this machine, and then the user password can be modified [any password can be entered on this machine to log in to the database, but not remotely]

At the same time, Oracle provides an ordinary user scott for program testing. The XE version does not have scott user by default. You can import scott user into oracle by yourself

Prerequisite: login with system user

Step 1: find scott.sql script

Find the scott.sql script file in the oracle installation path, which is located in the oracle\product.2.0\server\rdbms\admin directory

Step 2: use @ to import scott script

@C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\scott.sql

Step 3: reset password and login status

alter user scott identified by tiger; alter user scott account unlock;

User: scott, password: tiger

• Department table: dept

desc dept; --see dept Table structure

select * from dept;

• Employee table: emp

desc emp; --see emp Table structure

select * from emp;

• Salary scale: salgrade

desc salgrade; --see salgrade Table structure

select * from salgrade;

• Payroll: bonus

desc bonus; --see bonus Table structure

select * from bonus;

When developing a project, you need to create users for the project

• Syntax: CREATE USER username IDENTIFIED BY password [ACCOUNT LOCK/UNLOCK]

• CREATE USER GPB IDENTIFIED BY 123 ACCOUNT LOCK; -- The account cannot start with a number or something

• Syntax: ALTER USER username ACCOUNT LOCK/UNLOCK;

• ALTER USER GPB ACCOUNT UNLOCK;

• Syntax: ALTER USER username IDENTIFIED BY new password (new password);

• ALTER USER GPB IDENTIFIED BY 123456;

• Syntax: DROP USER user name

• DROP USER GPB;

Although the user was created successfully, he cannot log in to the Oracle database system normally because he does not have any permissions. If the user can log in normally, at least the CREATE SESSION system permission is required. Generally, an ordinary user (such as scott) with two roles of CONNECT and RESOURCE can carry out routine database development.

Syntax: GRANT role | permission TO user (role)

GRANT CONNECT TO TOM;

GRANT RESOURCE TO TOM;

After TOM user is given the Connect role and Resource role, jerry user can work normally.

REVOKE role | permission FROM user (role)

REVOKE CONNECT FROM TOM;

REVOKE RESOURCE FROM TOM;

-- Write together GRANT CONNECT, RESOURCE,DBA TO TOM; REVOKE DBA FROM TOM;

Syntax: grant permission on table to user

eg: scott user's dept table is authorized to kaifamiao user

grant all on dept to kaifamiao;

eg: kaifamiao user views scott user's dept table

select * from scott.dept;

Tip: the user. Table name must be written, such as scott.dept in this example;

enter one user name: SYSTEM enter your password: connection to: Oracle Database 11g Express Edition Release SQL> grant all on kaifamiao.infos to scott; Authorization succeeded. SQL> exit; from Oracle Database 11g Express Edition Relea D:\Tool\cmder>sqlplus SQL*Plus: Release 11.2.0.2.0 Production on Copyright (c) 1982, 2014, Oracle. All righ enter one user name: scott enter your password: connection to: Oracle Database 11g Express Edition Release SQL> select * from infos; select * from infos * Error on line 1: ORA-00942: The table or view does not exist SQL> select * from scott.infos; select * from scott.infos * Error on line 1: ORA-00942: The table or view does not exist SQL> select * from kaifamiao.infos; No rows selected SQL>

-- View the version of the database SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for 64-bit Windows: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production -- Single-Line Comments /** multiline comment */ -- Create user Syntax: CREATE USER user name IDENTIFIED BY Password [ACCOUNT LOCK|UNLOCK] eg: CREATE USER LZY IDENTIFIED BY 123 ACCOUNT UNLOCK; -- Assign user database roles( CONNECT role|RESOURCE role|DBA Role) Syntax: GRANT role|jurisdiction TO user eg: GRANT CONNECT, RESOURCE, DBA TO LZY; -- Syntax for reclaiming permissions Syntax: REVOKE role|jurisdiction FROM user eg: REVOKE DBA FROM LZY; -- Change Password Syntax: ALTER USER user name IDENTIFIED BY New password eg: ALTER USER LZY IDENTIFIED BY 456; -- The modified user is locked (unlocked) eg: ALTER USER LZY ACCOUNT LOCK; -- dual Virtual table(dual Is a virtual table used to form select Grammar rules, oracle ensure dual There is always only one record in it. We can use it to do many things.) SELECT SYSDATE,SYSTIMESTAMP FROM dual; -- Get system time SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual;-- Get host name select * from all_tables where owner='SCOTT'；-- TEST The user name must be uppercase. -- View the table of currently logged in users: select table_name from user_tables;

Oracle data type

Tips:

• MySql DATE: mm / DD / yy

• DATETIME: mm / DD / yyyy H / min / S

• MySQL string: varchar type

• Oracle string: varchar2: type

• The difference between char and varchar2
  • char: you can save as many extra spaces as you give a fixed length
  • varchar2: how much variable length do you give him? How much do you use? Don't use less. You can't save it
  • Both save strings

For the DATE type, you can use the built-in sysdate function to obtain the current system DATE and time and return the DATE type. You can use the systimestamp function to return the current DATE, time and time zone.

CREATE TABLE INFOS ( STUID VARCHAR2(7) NOT NULL, --Student number='S'+Class number+2 Bit serial number STUNAME VARCHAR2(10) NOT NULL, --full name GENDER VARCHAR2(4) NOT NULL, --Gender AGE NUMBER(2) NOT NULL, --Age SEAT NUMBER(2) NOT NULL, --Seat number ENROLLDATE DATE, --Admission time STUADDRESS VARCHAR2(50) DEFAULT 'The address is unknown', --address CLASSNO VARCHAR2(4) NOT NULL --Shift number=Semester serial number+Class serial number ) / ALTER TABLE INFOS ADD CONSTRAINT PK_INFOS PRIMARY KEY(STUID) / ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_GENDER CHECK(GENDER = 'male' OR GENDER = 'female') / ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_SEAT CHECK(SEAT >=0 AND SEAT <=50) / ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_AGE CHECK(AGE >=0 AND AGE<=100) / ALTER TABLE INFOS ADD CONSTRAINT CK_INFOS_CLASSNO CHECK((CLASSNO >='1001' AND CLASSNO<='1999') OR (CLASSNO >='2001' AND CLASSNO<='2999')) / ALTER TABLE INFOS ADD CONSTRAINT UN_STUNAME UNIQUE(STUNAME) /

Resolution:

• Created infos table
• The STUID column adds a primary key constraint
• GENDER adds a check constraint, which only allows "male" and "female" to be filled in
• A check constraint is added to the SEAT column, which is only allowed between 0 and 50
• The AGE column adds a check constraint, which is only allowed between 0 and 100
• The CLASSNO column adds a check constraint, which is only allowed between 2001 and 2999

CREATE TABLE SCORES ( ID NUMBER , --ID TERM VARCHAR2(2), --semester S1 or S2 STUID VARCHAR2(7) NOT NULL, --Student number EXAMNO VARCHAR2(7) NOT NULL, --Test number E+Class number+Serial number WRITTENSCORE NUMBER(4,1) NOT NULL, --Written examination results LABSCORE NUMBER(4,1) NOT NULL --Machine test results ) / ALTER TABLE SCORES ADD CONSTRAINT CK_SCORES_TERM CHECK(TERM = 'S1' OR TERM ='S2') / ALTER TABLE SCORES ADD CONSTRAINT FK_SCORES_INFOS_STUID FOREIGN KEY(STUID) REFERENCES INFOS(STUID) /

Resolution:

• The TERM column adds a check constraint with a value of S1 or S2
• The STUID column is set as a foreign key column and references the STUID column of the INFOS table

Create emp and dept tables:

D:\Tool\cmder>sqlplus SQL*Plus: Release 11.2.0.2.0 Production on Monday, October 18, 20:26:17 2021 Copyright (c) 1982, 2014, Oracle. All rights reserved. enter one user name: kaifamiao enter your password: connection to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production SQL> CREATE TABLE DEPT(deptid int, deptname varchar2(20), deptaddr varchar2(13), datetime date); Table created. SQL> ALTER TABLE DEPT ADD CONSTRAINT pk_deptid PRIMARY KEY(deptid); Table changed. SQL> CREATE TABLE EMP(empid int, deptid int, empname varchar2(20), job varchar2(20), manager int, entrydate date, salary float, bonus float); Table created. SQL> ALTER TABLE EMP ADD CONSTRAINT pk_empid PRIMARY KEY(empid); Table changed. SQL> ALTER TABLE EMP ADD CONSTRAINT fk_deptid FOREIGN KEY(deptid) REFERENCES DEPT(deptid); Table changed. SQL>

CRUD operation

1,Data manipulation language( DML): Commands used to manipulate data in a database. These include: select,insert,update,delete. 2,Data definition language( DDL): Commands for creating databases, database objects, and defining columns. These include: create,alter,drop. 3,Data control language( DCL): Commands used to control access permissions, permissions, etc. of database components. They include: grant,deny,revoke. 4,Other language elements: such as process control language, embedded functions, batch statements, etc.

• Insert date into MySql: insert into tablename (date) values('2021-10-10'), values('2000-10-01');
• Oracle: insert into tablename (date) values(to_date('2021-10-10 13:13:13','YYYY-MM-DD HH24:MI:SS'));
  • Oracle cannot be connected with the interpolated date. Use the to_date function to insert the month, month, day, hour, minute and second

• MySql: auto_increment constraint

• Oracle: you have to customize a Sequence to define self growth

  CREATE SEQUENCE myseq; SELECT myseq.nextval from dual;-- Self incremented serial number [serial number plus one] select myseq.currval from dual;-- Current Sn [no change]

INSERT INTO "INFOS" VALUES ('s100102', 'Lin Chong', 'male', '22', '2', TO_DATE('2009-08-09 06:30:10', 'YYYY-MM-DD HH24:MI:SS'), 'Xi'an', '1001'); INSERT INTO "INFOS" VALUES ('s100104', 'Ruan Xiaoer', 'male', '26', '3', TO_DATE('2021-10-18 21:16:10', 'YYYY-MM-DD HH24:MI:SS'), '', '1001');

Resolution:

• The insert statement inserts data into the cache by default, not directly into the library.
• commit refers to submitting user operations (add, delete and modify). Only after submitting the operations can the data be really updated to the table. Otherwise, other users cannot query the results of the current user's operations.
• It can't insert multiple pieces of data together like mysql. It can only insert one by one

SELECT STUNAME,GENDER,AGE,STUADDRESS FROM INFOS WHERE GENDER='male' ORDER BY AGE

Try paging queries

SELECT * FROM ( select rownum as r ,t.* from dept t where rownum<=4 ) where r > 2

SQL> UPDATE INFOS SET CLASSNO='1002',STUADDRESS='Laiwu, Shandong' WHERE STUNAME='Ruan Xiaoer'; 1 rows updated SQL> commit;

SQL> DELETE FROM INFOS WHERE STUID='s100102'; 1 ROW DELETED SQL> COMMIT;

In addition to the Delete statement deleting data, the truncate statement can also Delete statements

truncate table infos;

TRUNCATE command can delete all data in the table at one time.

TRUNCATE and DELETE can DELETE all the data in the table. Their differences are:

• TRUNCATE is a DDL command, and the deleted data cannot be recovered; DELETE is a DML command, and the deleted data can be recovered through log files.
• If there are many data records in a table, TRUNCATE is faster than DELETE.
• Because the TRUNCATE command is dangerous, it should be used with caution in practical development.

Try paging queries

SELECT * FROM ( select rownum as r ,t.* from dept t where rownum<=4 ) where r > 2

SQL> UPDATE INFOS SET CLASSNO='1002',STUADDRESS='Laiwu, Shandong' WHERE STUNAME='Ruan Xiaoer'; 1 rows updated SQL> commit;

SQL> DELETE FROM INFOS WHERE STUID='s100102'; 1 ROW DELETED SQL> COMMIT;

In addition to the Delete statement deleting data, the truncate statement can also Delete statements

truncate table infos;

TRUNCATE command can delete all data in the table at one time.

TRUNCATE and DELETE can DELETE all the data in the table. Their differences are:

• TRUNCATE is a DDL command, and the deleted data cannot be recovered; DELETE is a DML command, and the deleted data can be recovered through log files.
• If there are many data records in a table, TRUNCATE is faster than DELETE.
• Because the TRUNCATE command is dangerous, it should be used with caution in practical development.

20 October 2021, 14:42 | Views: 4515