Shulou(Shulou.com)06/01 Report--

I) introduction of basic concepts

There is a relational database of independent intellectual property rights (Dameng) in China, and its basic principle and management mode are similar to those of Oracle, so the main basic concepts and representative meanings are basically the same, so it is no longer recalcitrant here, please refer to another article on the introduction of Dameng database, "introduction to DM (Dameng) Database".

II) install Oracle

Whether it's a Windows installation or a Linux installation, there are a large number of installation and configuration tutorials available online, and I won't go into details here. This post focuses on database principles and basic usage, so you still need to instantiate an Oracle database quickly. Here is a brief introduction to docker-based installation and configuration.

a. First of all, make sure that docker has been installed and configured. If not, please refer to the "Centos 7.5 installation Docker" installation configuration.

b. Get Oracle image information in Docker

# docker search oracle / / Select the required version in the list. If you need the data persistence feature, it is recommended to select the sath89/oracle-xe-xxx version.

c. Start the docker instance

# docker run-d-p 49160 name xe sauth89/oracle-xe-11g 22-p 49161 purl 1521-v / hostfolder:/dockerfolder-- name xe sauth89/oracle-xe-11g / / take sauth89/oracle-xe-11g as an example

1.-d parameter indicates that the container is running in the background

2.-p parameter for port mapping, mapping ports 22 and 1521 of docker to 49160 and 49161 of host, respectively

3. The-v parameter indicates the directory mapping, and the result is that the files that exist in the dockerfolder directory will also be stored in the corresponding directory of the host

4.-- the parameter name specifies the container service name

d. Using step c above, you will create an Oracle database instance. The default connection information is as follows:

Hostname: localhost (host)

Port: 49161

SID: xe

Service name: xe

Username: system

Password: oracle

e. Graphical function to connect to the database

III) basic use

i. Management command

a. Query version

Select banner from sys.v_$version

Or

Select * from v$version

b. View database name (instance name) and status

Select name from v$database

Select status from v$instance

c. View all users of the system

Select * from all_users

d. Create a user and give it permission to link to the database

Create user username identified by passwd

Grant connect to username

e. Delete user

Drop user username

f. View current user roles

Select * from user_role_privs

g. View tablespaces and usage

Select * from vested tablespace; / / View all tablespaces of the system

Select tablespace_name,sum (bytes) / 1024 from dba_data_files group by tablespace_name; / / Unit is M

h. Create a tablespace

Create tablespace test_tbsp datafile'/ home/oracle/oradata/test_tbsp.dbf' size 100m; / / data files must be enclosed in single quotation marks

i. View default tablespaces

Select username,default_tablespace from dba_users

j. Modify user default tablespace

Alter user user1 default tablespace test_tbsp

k. Turn on tablespace automatic expansion, view, and close

Alter database datafile'/ home/oracle/oradata/test_tbsp.dbf' autoextend on; / / on

Select file_name,autoextensible,increment_by from dba_data_files where tablespace_name='TEST_TBSP'; / / View

Alter database datafile'/ home/oracle/oradata/test_tbsp.dbf' autoextend off; / / close

ii. Roles, users, and rights management

a. Create a role

Create role roleName

b. Grant permissions to roles

Grant permission to roleName:

c. Grant roles to users

Grant roleName to userName

d. Query the role owned by the current user

Select * from user_role_privs

e. Delete a role

Drop role roleName

Note about the role:

1. When permissions are granted to a role, the user with that role also increases permissions

two。 When role permissions are revoked, the corresponding permissions of users with this role are also revoked

3. When a role is deleted, the user with this role loses all permissions of the previous role.

f. Grant session permissions to users

Grant create session to userName

g. Give users permission to create tables

Grant create table to userName

h. Grant users unrestricted access to tablespaces

Grant unlimited tablespace to userName

i. Granting and revocation of authority

Grant permission to userName

Revoke permission from userName

Grant select on mytab to userName

Grant update on mytab to userName

Grant delete on mytab to userName

Grant insert on mytab to userName

Revoke select on mytab from userName

Batch weighting: https://www.cnblogs.com/abcwt112/p/5507917.html

j. View that the user has permissions

Select * from user_sys_privs

k. Grant / revoke all permissions for other users to manipulate tables

Grant all on mytab to userName

Revoke all on mytab from userName

l. Query the operation permissions of other users on the current user table

Select * from user_tab_privs

m. Authority transfer

Grant create session to userName with admin option; / / means to grant system permissions to userName and allow it to be granted to other users

Grant update on mytab to userName with grant option; / / means to grant permissions on the operation table to userName and allow it to be granted to other users

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.