Oracle create database and user, table space, manage table space and data file

Time:2021-5-7

introduce

A table space is a logical part of a database

Physically: the database data is stored in the data file

Logically, the database is stored in the table space

A table space consists of one or more data files

 

Logical structure of database

Introduction:

Logical structure in Oracle includes table space, segment, zone, block

explain:

A database consists of table spaces

The table space is composed of segments

And the section is composed of districts

The region is composed of Oracle blocks

 

 

Such a result can improve the efficiency of the database

 

Table space

Introduction:

Table spaces are used to logically organize data in a database

A database is logically composed of one or more table spaces

 

The role of table space:

1. Control the disk space occupied by the database

2. DBAs can deploy different data types to different locations. This is beneficial to improve the I / O performance, as well as backup and recovery management operations

 

 

Oracle creates database, user and table space

The usage of Oracle is different from that of MySQL and SQL server. When creating a database, Oracle needs to correspond to a user, and the database and the user usually correspond one by one,

MySQL and SQL server can directly create a database through create databse “database name”, while Oracle needs the following steps to create a databaseThree steps

  1. Create two table spaces and corresponding data files (. DBF). If there are users and associated databases, you need to delete them first
  2. Create a mapping relationship between the user and the file created above
  3. Add permissions to users

CMD command login sqlplus sys / password as SYSDBA

 

1、 Create two table spaces and corresponding data files (monitor. DBF and monitor_ Temp.dbf (two files)   ,If there are users and associated databases, you need to delete them first

CREATE TABLESPACE monitor LOGGING DATAFILE 'D:\app\owner\oradata\orcl\monitor.dbf' 
SIZE 100M AUTOEXTEND ON NEXT 32M MAXSIZE 500M EXTENT MANAGEMENT LOCAL;

create temporary tablespace monitor_temp tempfile 'D:\app\owner\oradata\orcl\monitor_temp.dbf'
size 100m autoextend on next 32m maxsize 500m extent management local;

Table space: table space name

Datafile: ‘data file path’

Size: the size of the data file

Maxsize: maximum size of table data file

uniform   size   The size of the zone;

 

2、 Create a mapping relationship between the user and the file created above (user name is monitor, password is monitor)

CREATE USER monitor IDENTIFIED BY monitor DEFAULT TABLESPACE monitor TEMPORARY TABLESPACE monitor_temp;

 

3、 Add permissions

grant connect,resource,dba to monitor;
grant create session to monitor;

 

Sometimes, the operations of deleting database and user are also used. The deletion statement is also given here

4、 Delete database

DROP TABLESPACE monitor INCLUDING CONTENTS AND DATAFILES;

 

5、 Delete user

drop user monitor cascade;

 

 

Introduction to managing table spaces and data files

Create a table space

To create a table space, use create   In general, creating a table space is performed by a privileged user or DBA,

If other users are used to create a table space, the user must have create   System permissions of tablespace

Create data table space

After the establishment of the database, in order to facilitate the management of tables, it is best to establish their own table space

create tablespace tangtao001 datafile ‘d:\t001.dbf’ size 20m uniform size 128k;

 

Using datasheet spaces

To add a table to a new table space:

create table myDept(deptno number(4),dname varchar2(15),loc varchar2(13)) tablespace tangtao001;

Note: after executing the above command, the name of tangtao001 will be created   And create a data file named t001.dbf for the table space with the size of 128K

 

1. Display table space information

a)   Query data dictionary view DBA_ tablespaces。 Display table space information

select tablespace_name from dba_tablespaces;

2. Displays the data files contained in the table space

a)   Query data dictionary view DBA_ data_ Files to display the data files contained in the table space

select   file_ name,bytes   from   dba_ data_ files   where   tablespace_ Name =’table space name ‘;

 

Changing the state of a table space

When creating a table space, the table space is in the online state. At this time, the table space can be accessed, and the table space can be read and written. That is, the data of the table space can be queried, and various statements can be executed in the table space.

However, in the process of system maintenance or data maintenance, it may be necessary to change the state of the table space. Generally, it is operated by privileged users or DBAs

1. Take table space offline

alter   tablespace   Table space name   offline;

 

2. Bring a table space online

alter   tablespace   Table space name   online;

 

3. Read only table space

a)   When creating a table space, the table space can be read and written. If you do not want to perform update, delete, and insert operations on the table space, you can modify the table space to read-only

alter   tablespace   Table space name   read   only;

 

Case study:

1. Know the name of the table space. Displays all tables in the table space

select  *  from   tall_ tables   where   tablespace_ Name =’table space name ‘;

2. Know which table space the table belongs to

select tablespace_name,table_name from user_tables where table_name=’emp’;

 

be careful:

Through 2, we can know that scott.emp is in the system table space. Now we can change the system to read-only, but it will not succeed. Because system is a system table space, if it is a normal table space, it can be set as read-only

 

Delete table space

In general, there are privileged users or DBAs to operate. If it is operated by other users, the user is required to have drop   Table space system permission

drop   tablespace   Table space name   including   contents   and   datafiles;

 

explain:

Including   contents   It means to delete a table space and all database objects in the table space, while datafiles means to delete the database files as well

 

Extended table space

The table space is composed of data files, and the size of the table space is actually the size of the added data files. So we can imagine. Suppose that the table employee is stored in the table space of tangtao001, and the initial size is 2m.

When the data is full of 2m space, if the data is inserted into the employee table, the error of insufficient space will be displayed

 

Case description:

1. Create a table space tangtao001

2. Create a common label mydment on the table space   Its structure is the same as Dept

3. Add data insert to the table   into   mydment   select  *  from   dept;

4. When there is a certain time, there will be problems that cannot be expanded. What should we do?

5. Expand the table space to add more storage space. There are three ways:

 

 

1. Add data file

alter   tablespace   Table space name   add   datafile  ‘ Data file storage path ‘   size   Data file size;

 

2. Increase the size of the data file

alter   tablespace   Table space name   add   datafile  ‘ Data file storage path ‘   resize   New size of data file;

Note that the size of the data file should not exceed 500m

 

3. Set up automatic file growth

alter   tablespace   Table space name   add   datafile  ‘ Data file storage path ‘   autoextend   on   next   Size of each increment   maxsize   The maximum size of the data file;

 

Mobile data file

Sometimes, if the disk where the data file is located is damaged, the data file can no longer be used. In order to reuse, you need to move the copies of these files to other disks, and then restore them

 

Case description:

1. Determine the table space of the data file

select tablespace_name from dba_data_files where file_name=’d:\t01.dbf’;

2. Take table space offline

a)   Ensure the consistency of data files and change the table space to offline state

alter tablespace tangtao001 offline;

3. Use the command to move the data file to the specified destination

host move d:t01.dbf c:t01.dbf;

4. Execute alter   Tablespace command

a)   After the data is physically moved, alter must also be performed   The table space command modifies the database file logically

alter tablespace tangtao001 rename datafile ‘d:t01.dbf’ to ‘c:t01.dbf’;

5. Bring the table space online

a)   After moving the data file, in order to enable users to access the table space, it must be changed to online state

alter tablespace tangtao001 online;

 

Table space summary

1. Understand the role of table spaces and data files

2. Master the establishment methods of common table space, undo table space and temporary table space

3. Understand the various states of the table space

a) Online

b) Offline

c) Read write

d) Read only

4. Understand the reason for moving data files, that is, using alter   Tablespace and alter   The method of moving data file with datatable command

 

Reference blog:

https://www.cnblogs.com/qintangtao/archive/2012/11/04/2753549.html

https://www.cnblogs.com/0201zcr/p/4669548.html

,

introduce

A table space is a logical part of a database

Physically: the database data is stored in the data file

Logically, the database is stored in the table space

A table space consists of one or more data files

 

Logical structure of database

Introduction:

The logical structure in Oracle includes table space, segment, area and block

explain:

A database consists of table spaces

The table space is composed of segments

And the section is composed of districts

The region is composed of Oracle blocks

 

 

Such a result can improve the efficiency of the database

 

Table space

Introduction:

Table spaces are used to logically organize data in a database

A database is logically composed of one or more table spaces

 

The role of table space:

1. Control the disk space occupied by the database

2. DBAs can deploy different data types to different locations. This is beneficial to improve the I / O performance, as well as backup and recovery management operations

 

 

Oracle creates database, user and table space

The usage of Oracle is different from that of MySQL and SQL server. When creating a database, Oracle needs to correspond to a user, and the database and the user usually correspond one by one,

MySQL and SQL server can directly create a database through create databse “database name”, while Oracle needs the following steps to create a databaseThree steps

  1. Create two table spaces and corresponding data files (. DBF). If there are users and associated databases, you need to delete them first
  2. Create a mapping relationship between the user and the file created above
  3. Add permissions to users

CMD command login sqlplus sys / password as SYSDBA

 

1、 Create two table spaces and corresponding data files (monitor. DBF and monitor_ Temp.dbf (two files)   ,If there are users and associated databases, you need to delete them first

CREATE TABLESPACE monitor LOGGING DATAFILE 'D:\app\owner\oradata\orcl\monitor.dbf' 
SIZE 100M AUTOEXTEND ON NEXT 32M MAXSIZE 500M EXTENT MANAGEMENT LOCAL;

create temporary tablespace monitor_temp tempfile 'D:\app\owner\oradata\orcl\monitor_temp.dbf'
size 100m autoextend on next 32m maxsize 500m extent management local;

Table space: table space name

Datafile: ‘data file path’

Size: the size of the data file

Maxsize: maximum size of table data file

uniform   size   The size of the zone;

 

2、 Create a mapping relationship between the user and the file created above (user name is monitor, password is monitor)

CREATE USER monitor IDENTIFIED BY monitor DEFAULT TABLESPACE monitor TEMPORARY TABLESPACE monitor_temp;

 

3、 Add permissions

grant connect,resource,dba to monitor;
grant create session to monitor;

 

Sometimes, the operations of deleting database and user are also used. The deletion statement is also given here

4、 Delete database

DROP TABLESPACE monitor INCLUDING CONTENTS AND DATAFILES;

 

5、 Delete user

drop user monitor cascade;

 

 

,

Introduction to managing table spaces and data files

Create a table space

To create a table space, use create   In general, creating a table space is performed by a privileged user or DBA,

If other users are used to create a table space, the user must have create   System permissions of tablespace

Create data table space

After the establishment of the database, in order to facilitate the management of tables, it is best to establish their own table space

create tablespace tangtao001 datafile ‘d:\t001.dbf’ size 20m uniform size 128k;

 

Using datasheet spaces

To add a table to a new table space:

create table myDept(deptno number(4),dname varchar2(15),loc varchar2(13)) tablespace tangtao001;

Note: after executing the above command, the name of tangtao001 will be created   And create a data file named t001.dbf for the table space with the size of 128K

 

1. Display table space information

a)   Query data dictionary view DBA_ tablespaces。 Display table space information

select tablespace_name from dba_tablespaces;

2. Displays the data files contained in the table space

a)   Query data dictionary view DBA_ data_ Files to display the data files contained in the table space

select   file_ name,bytes   from   dba_ data_ files   where   tablespace_ Name =’table space name ‘;

 

Changing the state of a table space

When creating a table space, the table space is in the online state. At this time, the table space can be accessed, and the table space can be read and written. That is, the data of the table space can be queried, and various statements can be executed in the table space.

However, in the process of system maintenance or data maintenance, it may be necessary to change the state of the table space. Generally, it is operated by privileged users or DBAs

1. Take table space offline

alter   tablespace   Table space name   offline;

 

2. Bring a table space online

alter   tablespace   Table space name   online;

 

3. Read only table space

a)   When creating a table space, the table space can be read and written. If you do not want to perform update, delete, and insert operations on the table space, you can modify the table space to read-only

alter   tablespace   Table space name   read   only;

 

Case study:

1. Know the name of the table space. Displays all tables in the table space

select  *  from   tall_ tables   where   tablespace_ Name =’table space name ‘;

2. Know which table space the table belongs to

select tablespace_name,table_name from user_tables where table_name=’emp’;

 

be careful:

Through 2, we can know that scott.emp is in the system table space. Now we can change the system to read-only, but it will not succeed. Because system is a system table space, if it is a normal table space, it can be set as read-only

 

Delete table space

In general, there are privileged users or DBAs to operate. If it is operated by other users, the user is required to have drop   Table space system permission

drop   tablespace   Table space name   including   contents   and   datafiles;

 

explain:

Including   contents   It means to delete a table space and all database objects in the table space, while datafiles means to delete the database files as well

 

Extended table space

The table space is composed of data files, and the size of the table space is actually the size of the added data files. So we can imagine. Suppose that the table employee is stored in the table space of tangtao001, and the initial size is 2m.

When the data is full of 2m space, if the data is inserted into the employee table, the error of insufficient space will be displayed

 

Case description:

1. Create a table space tangtao001

2. Create a common label mydment on the table space   Its structure is the same as Dept

3. Add data insert to the table   into   mydment   select  *  from   dept;

4. When there is a certain time, there will be problems that cannot be expanded. What should we do?

5. Expand the table space to add more storage space. There are three ways:

 

 

1. Add data file

alter   tablespace   Table space name   add   datafile  ‘ Data file storage path ‘   size   Data file size;

 

2. Increase the size of the data file

alter   tablespace   Table space name   add   datafile  ‘ Data file storage path ‘   resize   New size of data file;

Note that the size of the data file should not exceed 500m

 

3. Set up automatic file growth

alter   tablespace   Table space name   add   datafile  ‘ Data file storage path ‘   autoextend   on   next   Size of each increment   maxsize   The maximum size of the data file;

 

Mobile data file

Sometimes, if the disk where the data file is located is damaged, the data file can no longer be used. In order to reuse, you need to move the copies of these files to other disks, and then restore them

 

Case description:

1. Determine the table space of the data file

select tablespace_name from dba_data_files where file_name=’d:\t01.dbf’;

2. Take table space offline

a)   Ensure the consistency of data files and change the table space to offline state

alter tablespace tangtao001 offline;

3. Use the command to move the data file to the specified destination

host move d:t01.dbf c:t01.dbf;

4. Execute alter   Tablespace command

a)   After the data is physically moved, alter must also be performed   The table space command modifies the database file logically

alter tablespace tangtao001 rename datafile ‘d:t01.dbf’ to ‘c:t01.dbf’;

5. Bring the table space online

a)   After moving the data file, in order to enable users to access the table space, it must be changed to online state

alter tablespace tangtao001 online;

 

Table space summary

1. Understand the role of table spaces and data files

2. Master the establishment methods of common table space, undo table space and temporary table space

3. Understand the various states of the table space

a) Online

b) Offline

c) Read write

d) Read only

4. Understand the reason for moving data files, that is, using alter   Tablespace and alter   The method of moving data file with datatable command

 

Reference blog:

https://www.cnblogs.com/qintangtao/archive/2012/11/04/2753549.html

https://www.cnblogs.com/0201zcr/p/4669548.html

Recommended Today

Looking for frustration 1.0

I believe you have a basic understanding of trust in yesterday’s article. Today we will give a complete introduction to trust. Why choose rust It’s a language that gives everyone the ability to build reliable and efficient software. You can’t write unsafe code here (unsafe block is not in the scope of discussion). Most of […]