Basic Oracle knowledge and problem solving

Overview

 

As a large relational database, Oracle database has many characteristics compared with other relational databases. The following is a simple description, this includes the knowledge we may use in daily operation and maintenance, and we hope to help you.

 

I. Basic Knowledge

 

1. The tablespace is unique to the database, and each database must have at least one tablespace.

 

Tablespaces can be divided into system tablespaces, non-system tablespaces, and rollback segments. They can also be divided into user tablespaces and non-user tablespaces. They only have different classification methods, but they are actually similar;

 

Temporary tablespace: the temporary tablespace does not contain real data. The restoration method is to delete the temporary tablespace and recreate it .. System tablespace: If the backup is unavailable, the database must be rebuilt.

 

The size of the tablespace can be set as needed and as required by the device.

 

The tablespace can be simply understood as the space for storing information. We generally create a database system in the following order: create a tablespace --- modify a tablespace --- create a rollback segment --- create a user and authorize --- create a user table --- create a table index, users can build their own database systems in this order. As to whether the created database has the best performance, I will discuss it in future articles. Table space creation can be easily created using a graphical user interface. I believe everyone will. I will not detail it here, the following describes the SQL statements for creating tablespaces, modifying tablespaces, creating rollback segments, creating users and authorizations, creating tables, and creating indexes. For more information, see:

 

2. The control file is essential to each database. To improve the database performance, we recommend that you create several more control files on different disks. The Oracle database consists of three types of files: data files, control files, and online log files. The database administrator may consider moving database files due to disk space changes or adjustments based on database disk I/O performance. The following uses the UNIX platform as an example to describe how to move three types of database files.

 

Database Log Files that cannot be ignored

 

Note that log files are important when restoring the database.

 

Logs can be divided into online logs and offline logs. Online logs can record all modifications made in each Oracle database. An online log is composed of multiple online log files. Each running Oracle database instance has an online log, which works with the Oracle background process lgwr, record all modifications made to the instance immediately. Offline logs are optional. When Oracle fills up online log files, archive them.

 

Ii. syntax for creating tablespaces, rollback segments, users, and tables

 

1. Create a tablespace (this is the first step in the database creation process. The tablespace is like a container that contains all kinds of data in the database)

 

Create tablespace test datafile '/dev/test_name1 'size 1000 m,'/dev/test_name2 'size 1000 m, '/dev/test_name3' size 1000 m

Default storage (initial 64 K next 64 K maxextents unlimited pctincrease 50 );

 

Note: There is no limit on table space expansion.

 

2. Modify the tablespace

 

Alter tablespace ts_name1 add datafile '/dev/name4' size 1000 m;

 

3. rollback segments

 

Create rollback segment "rstest" tablespace "test_name"

Storage (initial 16 m next 16 m maxextents unlimited );

 

Do not create rollback segments of different sizes, because Oracle does not select the rollback segments that best match the requirements.

 

4. Create a user and authorize the user

 

Create user test_user identified by test_user

Default tablespace test_name1 temporary tablespace test_name2;

Grant connect to test_user;

Grant DBA to test_user;

Grant resource to test_user;

 

5. Create a table

 

Create Table test_name1

(

A number (10) Not null,

B Number (10) null,

C Number (3) defalut 0,

D Number (3) not null,

Constraint pK _ test_user primary key ()

Using Index

Tablespace test_name1

Storage

(

Initial 1 m

Next 1 m

Pctincrease 0

)

)

Pctfree 10

Tablespace test_name1

Storage

(

Initial 1 m

Next 1 m

Pctincrease 0

)

Partition by range (d)

(Partition part000 values less than (1) tablespace test_name1,

Partition part001 values less than (2) tablespace test_name1,

)

/

 

6. Create an index

 

Create index id_tablename1 on test_name1 (F2)

Tablespace ts_name

Storage

(

Initialize 500 k

Next 500 k

Pctincrease 0

)

 

/

 

Iii. query table space

 

Select substr (A. tablespace_name, 1, 10) tablespacename,

 

Sum (A. Bytes/1024/1024) totle_size,

 

Sum (nvl (B. free_space1/1024/1024, 0) free_space,

 

Sum (A. Bytes/1024/1024)-sum (nvl (B. free_space1/1024/1024, 0) used_space,

 

Round (sum (A. Bytes/1024/1024)-sum (nvl (B. free_space1/1024/1024, 0) * 100/sum (A. Bytes/1024/1024), 2) used_percent

 

From dba_data_files,

 

(Select sum (nvl (bytes, 0) free_space1, file_id

 

From dba_free_space

 

Group by file_id) B

 

Where a. file_id = B. file_id (+)

 

Group by A. tablespace_name