Guidelines for the configuration and use of oracle environments

1. Oracle installation

First of all, talking about oracle service, I use oracle 11g, the version downloaded directly from the official website is: win64_11gR2_database_1of2.zip, win64_11gR2_database_2of2.zip two unzipped packages, pay attention to the merger of the two packages before setup.exe.

Most of the time, we don't need to install service, we just need a client, so we can use instantclient and then install PL/SQL to manage the interface. Because PL/SQL only has 32-bit, 64-bit instantclient can not successfully connect with it, so if you install instantclient, you should also install 32-bit.

It is worth noting that the service side and instantclient cannot co-exist (in fact, after installing service, there is client). I configured instantclient at first, and then I wanted to install service, because I didn't delete the configuration of instantclient and kept installing errors.

Second, create storage space

After service starts, we have to create storage space if we want to use the database. We first log in to PL/SQL with system (DBA permission), then open a command window, and then execute the following statement:

CREATE TABLESPACE "ST_DATA" LOGGING DATAFILE'+ FILEPATH/st_data.ora' SIZE 1000m AUTOEXTEND ON NEXT 100m MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;CREATE TEMPORARY TABLESPACE "ST_TEMP" TEMPFILE'+ FILEPATH/st_temp.ora' SIZE 1000m AUTOEXTEND ON NEXT 10M MAXSIZE 4096m EXTENT MANAGEMENT LOCAL

Two storage spaces have been created above, one for data and later for temp. Normally, we do not use the database role with DBA permission to directly manage the business data in the database, so we need to create users and grant permissions to manage:

Create user st identified by st default tablespace ST_DATA temporary tablespace ST_TEMP;grant connect to st;grant resource to st;grant debug any procedure to st;grant debug connect session to st;grant create database link to st;grant create procedure to st;grant create table to st;grant create trigger to st;grant create any index to st;grant create any sequence to st;grant create any view to st;grant create any synonym to st;grant create any job to st;grant unlimited tablespace to st;grant select on dba_data_files to st;grant select on dba_free_space to st

Above, we have created the role, so we can use the st role, password st users to log in to PL/SQL to manage business data!