Concepts and user permissions of tablespaces in Oracle learning notes

An Oracle database is composed of only some files. An Oracle instance is an Oracle Server process set with its own system global zone and related database files. The newly installed Oracle database management system has a pre-installed database (usually called ORCL), and starts an instance. In the future, database connections are connected to the database through this instance by default.

You can create a database by using graphical or manual code. The new database, like ORCL, may be named differently (assumed as newdb) and will start an instance of newdb. This means that multiple Oracle instances can be run on one machine. During connection, you must specify the connection parameters to connect to different instances, alternatively, you can use alter session set instance = newdb to specify the connected INSTANCE. There is usually a default instance!

In my understanding, we can simply look at the composition of ORacle:

Database -- tablespace -- a user's table set -- a table -- rows, columns ......, Table reference and authorization, usually in the user unit. Of course, in essence, a table space is a physical concept, while a user table is an object in a database. It is only stored in a space under a table space.

Tablespace, segment, interval, and database object

 The physical storage structure of the Oracle database is composed of data files. However, logically, Oracle divides the disk space occupied by all database data files into one or more tablespaces for storage and management. A tablespace can contain multiple data files, but a single data file can only belong to one tablespace.

The logical storage structure of Oracle has the following layers: tablespace -- segment -- interval -- data block. Usually, table space, segments, and segments are used. segments include data segments, index segments, temporary segments, and rollback segments. Tables and indexes in Oracle Objects are stored in different segments. A database table only belongs to a data segment, but it may span multiple ranges (Extent). Because the range includes fixed data block sizes, a single interval may not have to be placed in the next table, the Division interval is only for the convenience of space allocation and recovery.

New users and their authorization

When you add a new user to the Oracle database, you can perform the following steps:

1. Create a new table space (new USERS do not need to create a new table space, such as USERS ). When creating a tablespace, you must specify parameters for some tablespaces, such as data files.

2. Create a user. A newly created user can specify no permission attribute or its default tablespace.

3. user authorization. Specify the user's default tablespace, and grant the user the create session (database connection), create table, select talbe and other permissions. In this way, you can perform various operations in the default tablespace. If you want to grant the table permissions of other users to new users, you can also do so at this time.

4. Allocate space limit. This step is required. Otherwise, you cannot create a table. Allocate the space quota of each user in a tablespace to make the use of the space more reasonable.

5. You can perform operations.

Below is an instance found online:

1. 1. Create a tablespace
2. Create tablespace OSDB datafile'F: \ oracle \ oradata \ glsqjz \ OSDB. ora'Size 100 m reuseDefaultStorage (initial 500 k next 500 k pctincrease 20 );
3. 2. Create a user
4. Create user OSUSER identified by OSUSER;// The password behind identified by and the user name before it
5. 3. user authorization
6. Grant resource, connect, RECOVERY_CATALOG_OWNER to OSUSER;
7. Grant create table to OSUSER;
8. Alter user OSUSER quota unlimited on osdb;
9. Alter user OSUSERDefaultTablespace OSDB;
10. 4. delete a tablespace
11. Drop tablespace TableSpaceName including contents and datafiles;
12. 5. delete a user
13. Drop user User_Name CASCADE
14. 6. Notes for deleting a table
15. When deleting all data in a TABLE, the truncate table name must be used. Because when the drop table or DELETE * from table name is used, the occupied space of the TABLE in the TABLESPACE is not released, after repeated DROP and DELETE operations, the hundred megabytes of space on the TABLESPACE will be exhausted.