Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to create tablespaces and users in Oracle. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

How Oracle creates tablespaces and how to create users. On Windows, click start, then run, enter cmd and click OK to open the command line window

Enter the following command:

Sqlplus / nolog

After entering, the prompt SQL > will appear.

Enter at this time

Conn / as sysdba

You can usually log in. If it fails, you can try again with the password as sysdba of the conn sys/sys user.

Next, let's take a look at where your current database files are typically placed:

Select name from v$datafile

The results you may see under windows are as follows:

SQL > select name from v$datafile

Before you create an object, you first allocate storage space, and to allocate storage, you create a tablespace.

An example of creating a table space is as follows:

CREATE TABLESPACE "SAMPLE"

LOGGING

DATAFILE'D:\ ORACLE\ ORADATA\ ORA92\ LUNTAN.ora' SIZE 5m

EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

1. CREATE TABLESPACE "SAMPLE" creates a table space named "SAMPLE".

To name the tablespace, follow the Oracle naming convention.

There are three types of table spaces that ORACLE can create:

(1) TEMPORARY: temporary tablespace for storing temporary data

The syntax for creating temporary tablespaces is as follows:

CREATE TEMPORARY TABLESPACE "SAMPLE".

(2) UNDO: restore tablespace. Used to save redo log files.

The syntax for creating a restore tablespace is as follows:

CREATE UNDO TABLESPACE "SAMPLE".

(3) user tablespace: the most important, but also used to store user data tablespace

It can be written directly as: CREATE TABLESPACE "SAMPLE"

TEMPORARY and UNDO table spaces are special table spaces managed by ORACLE. It is only used to store system-related data.

2 、 LOGGING

There are two options: NOLOGGING and LOGGING

NOLOGGING: when creating a tablespace, no redo log is created.

LOGGING is the opposite of NOLOGGING, which generates a redo log when a tablespace is created.

When using NOLOGGING, the advantage is that there is no need to generate logs when creating the table space, so the creation of the table space is faster, but the log cannot be recovered after the data is lost, but generally, when we create the table space, there is no data. According to the usual practice, we need to back up the data after building the table space and importing the data, so we usually do not need to create the log of the table space, therefore, when creating the table space Select NOLOGGING to speed up the creation of tablespaces.

3. DATAFILE is used to specify the location and size of the data file.

DATAFILE'D:\ ORACLE\ ORADATA\ ORA92\ LUNTAN.ora' SIZE 5m indicates that the location of the file is'D:\ ORACLE\ ORADATA\ ORA92\ LUNTAN.ora', and the size of the file is 5m.

If you have multiple files, you can separate them with commas:

DATAFILE'D:\ ORACLE\ ORADATA\ ORA92\ LUNTAN.ora' SIZE 5m camera D:\ ORACLE\ ORADATA\ ORA92\ dd.ora' SIZE 5m

But each file needs to be specified in size. The units are in specified units, such as 5m or 500K.

According to different needs, specific files can be stored on different media, such as disk arrays, to reduce IO competition. When specifying a file name, it must be an absolute address, not a relative address.

4. EXTENT MANAGEMENT LOCAL storage area management method

Before Oracle 8i, there were two options, one is to manage (DICTIONARY) in the dictionary, and the other is local management (LOCAL). Starting from 9i, it can only be managed locally. Because LOCAL management has many advantages.

DICTIONARY in the dictionary: each storage unit in the data file is treated as a record, so when doing DM operations, there will be a large number of Delete and Update operations on this management table. When doing a lot of data management, there will be a lot of DM operations, which will severely affect the performance. at the same time, the operation of table data for a long time will produce a lot of disk fragments, which is why disk defragmentation should be done.

Local management (LOCAL): manage disks in a binary way, with high efficiency, and maximize the use of disks. At the same time, it can automatically track and record the situation of the adjacent free space and avoid the merging operation of the free area.

5 、 SEGMENT SPACE MANAGEMENT

Disk expansion management method:

SEGMENT SPACE MANAGEMENT: the size of the time zone using this option is determined automatically by the system. Since Oracle can determine the optimal size of each zone, the area size is variable.

UNIFORM SEGMENT SPACE MANAGEMENT: specify the size of the zone, or use the default value (1 MB).

6. the management mode of segment space.

AUTO: can only be used in locally managed tablespaces. When using LOCAL to manage tablespaces, as the free space in the data block increases or decreases, its new state is reflected in the bitmap. Bitmap makes the behavior of Oracle managing free space more automatic and provides better performance for managing free space, but it can not automatically manage tables with LOB fields.

MANUAL: not used now, mainly for backward compatibility.

7. Specify the block size. You can specify the size of the tablespace data block.

Examples of creation are as follows:

CREATE TABLESPACE "SAMPLE"

LOGGING

DATAFILE'D:\ ORACLE\ ORADATA\ ORA92\ SAMPLE.ora' SIZE 5m

'd:\ ORACLE\ ORADATA\ ORA92\ dd.ora' SIZE 5m

EXTENT MANAGEMENT LOCAL

UNIFORM SEGMENT SPACE MANAGEMENT

AUTO

SQL > /

The tablespace has been created.

To delete the tablespace feed, you can

SQL > DROP TABLESPACE SAMPLE

The table space has been discarded.

8. Modify the tablespace

Alert database DATAFILE'D:\ ORACLE\ ORADATA\ ORA92\ SAMPLE.ora' autoextend on next 100m

Next, let's start to create a user. The format of the command to create a user is as follows:

Create user username identified by password which tablespace does the default tablespace user use by default

Modify the permissions of the user:

Grant role 1, role 2 to user name

Examples are as follows:

Create user wbppcs identified by wbppcs123 default tablespace wbppcs

Grant dba, connect to wbppcs

Delete tablespaces and users

Delete tablespace

Drop tablespace test_data including contents and datafiles

Delete user

Drop user solidwang cascade

This is how to create tablespaces and users in the Oracle shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.