Oracle learning-literacy: oracle literacy

Oracle learning-literacy: oracle literacy

Preface

 

I have been dealing with Oracle databases over the past few days. Since I have not learned much about Oracle before, I just used Oracle as a data source. I don't know much about it. For example, databases, database instances, tablespaces, users, and tables, how they are created, and what is the relationship between them, so I will take a look at it this time.

 

 

 

1 Database

 

A Complete Oracle database is usually composed of two parts: an Oracle database and an Oracle database instance. The first Oracle database is an Oracle-type relational database management system, it contains physical data and database management systems, that is, the combination of physical data, memory, and operating system processes. The Oracle database mentioned later refers to the data set of a series of physical files, these collections include data files, control files, online logs, and parameter files.

 

The following figure shows a step for creating a database:

 

The first is the global database name, and the second is the database instance SID (which will be described below ).

Global Database Name: it is the identifier of a database. In short, it is the name, which must be considered during creation. It will not be modified in the future, but it can also be modified, but it is troublesome to modify it because after the database is installed, the database identity name is written into the control file, database table, and many other places.

 

Database commands:

 

View the name of the current database:

 

select name from v$database;

 

2. database instances

 

Officially, Oracle instances are part of the computer memory required to access the Oracle database and assist in processing background processes. They are a collection of processes and the memory used by these processes (SGA.

 

When we need to call the object method during code writing, we need to create an instance, which is also stored in the memory; our oracle instance is similar to the new object. It also creates a process in the memory when you need to access the oracle database. It also exists only in the memory.

 

 

When accessing oracle, we actually access the oracle instance. when accessing the oracle instance, we sometimes need to enable the instance first. My Instance name is orcltest:

 

startup open orcltest；

Instance name and SID

 

The instance name (instance_name) refers to the name of the database management system used to respond to a database operation. When it comes to the Oralce Instance name, you cannot mention Oracle_SID. SID is actually an identifier of the Oracle instance.

 

However, there is a difference between the two. instance_name is an oracle database parameter. Oracle_SID is the environment variable of the operating system. Oracle_SID is used to interact with the operating system. That is, the Instance name accessed from the operating system must be accessed through Oracle_SID. And Oracle_SID must be consistent with the value of instance_name. Otherwise, you will receive an error. On the unix platform, it is "invalid lenot available". On the windows platform, it is "TNS: protocol adapter error ".

 

In addition to interacting with the operating system, the database instance name is also used as the oracle server ID for network connection. When configuring an oracle host connection string, you need to specify the Instance name as follows:

 

Jdbc: oracle: thin: @ localhost: 1521: orcltest (orcltest is the database instance name)

Query the current database instance Name:

 

select instance_name from v$instance;<span style="font-size: 16pt; font-family: SimSun; "> </span>

View the information of the current database instance:

 

Show parameter instance_name;

Note: A database can have multiple instances, which can be used as a database service cluster, or different database instances can be used for different projects. This content will be further described later.

 

3. tablespace

 

Oracle databases store physical tables through tablespaces. tablespaces are logical partitions of databases. They belong to Oracle database instances. A database instance can have N tablespaces, there can be N tables in a tablespace.

 

So with the database instance, you can create a tablespace.

 

 

Create a tablespace:

 

create tablespace OracleTest logging datafile 'C:\oracle\product\10.2.0\oradata\ORCLTEST\OracleTest.dbf' size 32m autoextend on next 32m maxsize2048m extent management local;

The created tablespace is named OracleTest. The created tablespace is at: C: \ oracle \ product \ 10.2.0 \ oradata \ ORCLTEST \ OracleTest. dbf: The size is 32 MB, and the space can be automatically expanded. Each expansion is 32 MB, and the maximum tablespace is 2048 MB.

 

Query the table space name:

 

select tablespace_name from dba_data_files group by tablespace_name;

Query the table space size:

 

select tablespace_name,count(*),sum(blocks),sum(bytes)/1024/1024 from dba_data_filesgroup by tablespace_name;

Check whether the tablespace is automatically expanded:

 

select file_name,autoextensible,increment_by from dba_data_files;

If there are tables in the tablespace, you can view:

Select tablespace_name,table_name from dba_tables where tablespace_name='ORACLETEST'; 

4 Users

 

For Oracle users, after an Oracle database is installed, an Oracle instance is created, a tablespace is created, a tablespace is specified for the user, and a physical table is created.

 

We have already created database instances and tablespaces. Now we are creating the tablespace specified by the user:

 

Create user testuser identified by test default tablespace OracleTest;

The user who created a testuser. The password is test, and the tablespace is OracleTest.

One thing to note here is that the same tablespace can be granted to multiple users, and different users can be assigned different levels of operation permissions on the table space.

 

There are three types of permissions granted to the testuser:

 

grant connect to testuser; grant resource to testuser; grant dba to testuser;

The testuser user is granted the third dba administrator permission.

 

After logging on to the created user, we can find the current user:

 

show user;

View all users in the current database:

 

select  * from dba_users;

5 tables

 

When database instances, tablespaces, and users are available, you can create tables in the tablespaces to which your users belong. We will not talk about the tables that we usually use here. Take a look at the common commands:

 

View the table tablespace

Select table_name,tablespace_name from all_tables where table_name='t_student';

View table structure

desc t_student;

Summary

 

For Oracle usage, first install the oracle database software, then create a database instance, then create a tablespace, and then create a user and assign permissions to the tablespace, then create a table, and finally test the table, the order of creating tablespaces and creating users can be reversed. These things have been played back and forth several times over the past few days. If you do not perform these Oracle operations step by step, you will never know how many of them are born, you don't know what mistakes it will make you crazy.

 

Oracle learning methods and techniques

Learning Oracle is a long and arduous process. If you are not interested but forced to learn, it is difficult to learn well. To a certain extent, if you want to further improve your knowledge, you have to get in touch with a lot of things outside of Oracle, such as Unix, such as network and storage. Therefore, you must be interested in learning Oracle. If you are interested, everything will become simple and happy. To sum up, it is interest, learning, and practice. We recommend that you enroll in the training course. Because of the learning environment, the atmosphere is good, and the progress will be fast. Find an oracle wdp organization, such as CUUG.

How to Learn from oracle

Interest is the best teacher!
Keep an eye on everything about Oracle and have the greatest enthusiasm for it. You will be able to learn it well!