Oracle Learning-Literacy Articles

Objective

these last few days have been working with Oracle database, due to previous Oracle The study is not deep, just put Oracle As a data source to use, a lot of things do not know very deep, such as databases, database instances, table space, users, tables, how they are created, what is the relationship between them, so this time to learn.

1Database

a completeOraclea database is usually made up of two parts, namelyOracledatabase, andOracleDB instance, the first oneOralcedatabase refers to theOracletype of relational database management system, including physical data, database management system, which is the combination of physical data, memory, operating system process;and the latter mentionedOracledatabase refers to thea collection of data for a series of physical files, including data files, control files, online logs, parameter filesand so on.

The following diagram is a step in creating a database:

The first one to fill out is the global database name, and the second one to fill out is the DB instance sid (described later).

Global database name: is the identity of a database, simply speaking is the name, this general in the creation must think well. After the general is not modified, but can also be modified, but it is difficult to modify, because the database is installed, the name of the database will be written to control files, database tables and many other places.

Commands about the database:

View the name of the current database:

Select name from V$database;

 

2DB Instance

officials say Oracle An instance is a subset of the computer memory and secondary processing background processes required to access an Oracle database, which is a collection of processes and the memory (SGA) used by those processes.

we need to call the method of the object when we write the code . New out an instance of this New out of the example is also put in memory; Oracle The instance is similar to this new object, and is also when you need to access Oracle database creates a process in memory, and it is only present in memory.

us to visit Oracle is actually visiting Oracle the instance, Access Oracle instance, it is sometimes necessary to first open the instance , my instance is named orcltest :

Startup open orcltest;

Instance name and SID

The instance name (instance_name) refers to the name of the database management system used in response to a database operation; Oralce The instance name cannot be mentioned Oracle _sid , SID actually Oracle an identity for the instance.

But there is a difference between the two, instance_name is the Oracle database parameter. Oracle_sid is an environment variable for the operating system. Oracle_sid is used to interact with the operating system, meaning that the instance name is accessed from the operating system's perspective and must be passed through the Oracle_sid. and Oracle_sid must be consistent with the value of instance_name, otherwise you will receive an error on the UNIX platform, which is "Oraclenot available" in WinDow s platform, is "TNS: Protocol Adapter Error".

The database instance name is also used for the Oracle server identity of the network connection in addition to interacting with the operating system. When you configure the Oracle host connection string, you need to specify the instance name as follows :

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

Query The current DB instance name:

Select instance_name from V$instance;<span style= "FONT-SIZE:16PT; Font-family:simsun; "> </span>

To view information about the current DB instance:

Show parameter instance_name;

Note: A database can have multiple instances, can be used as a database service cluster, or different projects with different database instances can also, this piece of content will continue to explain.

3Table Space

Oracle database is a table space to store physical tables, table space (tablespace) is the logical division of the database, it is subordinate to the Oracle DB instance below, a DB instance can have N table space, a table space can have N sheet.

So with a DB instance you can create a tablespace.

To create a table space:

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;

where the table space created is named oracletest , the location of the table space is established: C:\oracle\product\10.2.0\oradata\ORCLTEST\OracleTest.dbf , the size is 32M , the space can be automatically expanded, each expansion 32M , the maximum table space is 2048M .

Query table space Name:

Select Tablespace_name from Dba_data_files Group by Tablespace_name;

Size of query table space:

Select Tablespace_name,count (*), sum (blocks), sum (bytes)/1024/1024 from Dba_data_filesgroup by Tablespace_name;

To see if the tablespace is automatically expanded:

Select file_name,autoextensible,increment_by from Dba_data_files;

If you have a table in the table space, you can view:

4User

about the Oracle the user, a Oracle after the database was installed, Oracle instance, create a tablespace, specify a tablespace for the user, and finally create a physical table.

We have created the database instance and table space above, and now we are creating the user for the specified tablespace:

Create user testuser identified by test default tablespace oracletest;

has created aTestUserthe user, the password isTest, the table space isoracletest.

To grant permissions to a user:

Grant connect to TestUser; Grant resource to TestUser; Grant DBA to TestUser;

I'm here totestuserthe user is granted theDBAadministrator privileges.

We can find the current user when we log in via our created User:

Show user;

View all users of the current database:

Select  * from Dba_users;

  

5Table

database instance, table space, user these all have later, you can in their own user to the table space to create a table, about the table this piece we all often use, here is not much to say. Take a look at the usual commands:

To view table spaces to which tables belong

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

View Table Structure

Desc t_student;

Summary

about the Oracle the use, first install Oracle database software, create a database instance, create a table space next, then create a user and assign permissions to the Tablespace, and then create the table and the final test. These days these things go back and forth several times, about Oracle of these operations, if you do not go to their own step-by-step practice, you never know how much you live, you do not know what it will make you crazy mistakes.

Oracle Learning-Literacy Articles