Shulou(Shulou.com)06/01 Report--

Original author: Changsha Shibata (Oracle, Japan)

For the first time, let's do some exercises for tablespaces.

For each exercise, we will summarize the answers and examples of answers, which you can compare with your own answers.

In addition, the directory structure used in SQL is based on Linux.

■ exercise 1. Please create an Small File tablespace named [TBS_SMALL]. This tablespace needs to be constructed using three 100MB data files.

SQL > create tablespace TBS_SMALL datafile'/ oracle_datafile1/tbs_small01.dbf' size 100m

'/ oracle_datafile1/tbs_small02.dbf' size 100m

'/ oracle_datafile2/tbs_small03.dbf' size 100m

This may be the first time some people have heard the term [Small File tablespace]. Actually, from Oracle Database10g Release 1

Big File tablespaces began to appear, so the traditional tablespaces were called Small File tablespaces.

I hope it can be understood here that Small File tablespaces can be made up of multiple data files. On the previous 32-bit operating system

The maximum limit for a file is 2GB, which is very effective when you want to make a tablespace that exceeds 2GB.

Of course, after evolving into a 64-bit system, there is no need to think too much about the limitation of this file size 2GB at the operating system level.

However, as an Oracle database, it is important to note that the size of the data file is limited.

The maximum size of a datafile in the Small File tablespace:

[Block Size in table space] × 4194303 (= 2 ^ 22-1) Block Size=8KB is about 32GB (in a data file)

Block numbers are managed using 22bit. In addition, the size of Block Size will also affect the calculation results.)

By the way, like DDL in the answer above, it's okay to use data files in different folders to make a tablespace.

But in the case of a combination of ASM and BigFile tablespaces, you don't really need to consider this (we'll talk about it later).

■ exercise 2. Confirm the size of the TBS_SMALL tablespace through the data dictionary view [DBA_DATA_FILES].

SQL > select tablespace_name, sum (bytes) / 1024 lap 1024 "size (MB)" from DBA_DATA_FILES

Where tablespace_name = 'TBS_SMALL' group by tablespace_name

TABLESPACE_NAME size (MB)

TBS_SMALL 300

There are a large number of data dictionary views in the Oracle database, which can be used to confirm the internal setting information of the database.

As a DBA, it is not just the execution of the DDL statement that ends, but the confirmation of the results after execution is also very important.

The data dictionary view [DBA_DATA_FILES] used in exercise 2 can be used to confirm all kinds of information about all data files.

For example, which tablespace each data file belongs to.

From the Select statement above, you can see that the total size of the three data files in the TBS_SMALL tablespace

(300MB = 100MB + 100MB + 100MB). If the result is not 300MB

Then there may be a misoperation when the tablespace is made in exercise 1.

For the meaning expressed by the columns in the data dictionary view, please refer to the relevant documentation.

■ exercise 3. Append a new data file to the TBS_SMALL tablespace to increase its size to 400MB.

SQL > alter tablespace TBS_SMALL add datafile'/ oracle_datafile2/tbs_small04.dbf' size 100m

Isn't it easy? The expansion of the tablespace can be achieved by appending data files to the SMALL FILE tablespace.

■ exercise 4. Expand the size of the TBS_SMALL tablespace to 500MB.

SQL > alter tablespace TBS_SMALL add datafile'/ oracle_datafile2/tbs_small05.dbf' size 100m

For those of you who are going to use the same sentences as exercise 3, wait a minute.

This statement is fine, of course, but what if I want to expand the tablespace to 50GB?

It takes a considerable amount of time to repeat this operation 500 times.

After it is done, it is obvious that the maintenance of data files will also become a hassle.

Moreover, the upper limit of the number of data files that can be contained in a SMALL FILE table space is 1022, which cannot be appended indefinitely.

So, exercise 4, we use another way to expand the tablespace, that is, to increase the size of the data file.

SQL > alter database datafile'/ oracle_datafile1/tbs_small01.dbf' resize 125m

SQL > alter database datafile'/ oracle_datafile1/tbs_small02.dbf' resize 125m

SQL > alter database datafile'/ oracle_datafile2/tbs_small03.dbf' resize 125m

SQL > alter database datafile'/ oracle_datafile2/tbs_small04.dbf' resize 125m

Of course, it is possible to change the size of only the fourth of these files to 200MB, but this composition is not neat, is it?

As a veteran DBA, of course, all data file sizes are comfortable all the time.

In fact, there is a reason for doing so. For example, the data in the table is distributed equally among the data files.

When the 100MB data file is full, only this 200MB data file has room.

At this point, the writing of the data file is all focused on this file.

When the directories [/ oracle_datafile] and [/ oracle_datafile2] are on different disks

Writes to specific disks will be concentrated, resulting in the possibility of performance degradation.

In addition, the amount of data read from each disk may also be offset.

Therefore, this time we have expanded the four existing data files by the same size.

Through this exercise, I hope you will realize two points.

First, although changes are made to the tablespace, the statement used is not [alter tablespace]

Instead, [alter database] is used to change the size of the data file.

Second, the greater the number of data files, the more difficult it is to maintain.

Especially when using bare devices to place data files, it is difficult to expand the size of data files.

So as the table space size expands, the number of bare devices that need to be managed and the number of data files will increase.

In addition, some people may have had this experience, because the new additional data files are empty.

So after appending the data file, you have to avoid the problem by exporting the data once and then importing it again.

(however, this problem can be solved on ASM.)

After you have finished expanding the data file, you can use the following Select statement to query, which is convenient if you use rollup.

SQL > select tablespace_name, file_name, sum (bytes) / 1024 Universe 1024 "size (MB)" from DBA_DATA_FILES

Where tablespace_name = 'TBS_SMALL'

Group by tablespace_name, rollup (file_name)

TABLESPACE_NAME FILE_NAME size (MB)

-

TBS_SMALL / oracle_datafile1/tbs_small01.dbf 125

TBS_SMALL / oracle_datafile1/tbs_small02.dbf 125

TBS_SMALL / oracle_datafile2/tbs_small03.dbf 125

TBS_SMALL / oracle_datafile2/tbs_small04.dbf 125

TBS_SMALL 500

■ activity 5. Make a Big File tablespace called [TBS_BIG]. The size of the table space is set to 300MB.

SQL > create bigfile tablespace TBS_BIG datafile'/ oracle_datafile1/tbs_big01.dbf' size 100m

'/ oracle_datafile1/tbs_big02.dbf' size 100m

'/ oracle_datafile2/tbs_big03.dbf' size 100m

The above statement will fail. Error code ORA-32774.

The error occurs because only one data file is allowed to be specified in the BIGFILE tablespace.

In this way, the workload for the management of multiple data files will be much less.

But isn't it possible to expand SMALLFILE tablespaces that can contain multiple data files? What does BIGFILE look like?

First take a look at the maximum data file size in the Big File tablespace:

[Block Size in table space] × 4294967295 (= 2 ^ 32-1) Block Size=8KB, it is about 32TB.

The maximum datafile size for a BigFile tablespace is 1000 times the maximum size of a datafile in the SmallFile tablespace.

This is because there is only one data file in the BigFile table space, while multiple data files are identified in the SmallFIle table space.

The 10bit used can be used to store block numbers (22bit+10bit=32bit).

However, because there can be 1022 data files in the SmallFile table space, the maximum size of the table space is about 32TB.

So what are the benefits of using BigFile tablespaces.

Here, new database restrictions are coming.

That is, the maximum number of data files in a database (usually 64000).

Because of this limitation, consider using BigFile tablespaces to significantly increase the capacity of the database.

The explanation is a little long, and the answer to exercise 5 is here:

SQL > create bigfile tablespace TBS_BIG datafile'/ oracle_datafile1/tbs_big.dbf' size 300m

■ exercise 6. Confirm that the tablespace TBS_BIG is a BIGFILE tablespace through the data dictionary view DBA_TABLESPACES

SQL > select tablespace_name,bigfile from dba_tablespaces

Where tablespace_name in ('TBS_SMALL','TBS_BIG')

TABLESPACE_NAME BIGFILE

TBS_BIG YES

TBS_SMALL NO

A little more here. The create tablespace command executed in exercise 1 is actually a command when making a tablespace

It's create smallfile tablespace. People who think that smallfile tablespaces will be created by default when smallfile is not specified must have

In fact, however, the type of default tablespace depends on the default tablespace type. Confirm that the SQL for the default tablespace type is as follows:

SQL >-- Checking Default Tablespace Type

Col property_value for a32

Select PROPERTY_NAME, PROPERTY_VALUE

From DATABASE_PROPERTIES

Where PROPERTY_NAME = 'DEFAULT_TBS_TYPE'

SQL >-- Setting Default Tablespace Type [BIGFILE]

Alter database set default BIGFILE tablespace

SQL >-- Setting Default Tablespace Type [SMALLFILE]

Alter database set default SMALLFILE tablespace

■ exercise 7. Expand the size of the tablespace TBS_BIG to 500MB in any way.

SQL > alter database datafile'/ oracle_datafile1/tbs_big.dbf' resize 500m

If you use the same method as exercise 4, of course you can't go wrong.

However, this is also a question that the author tries to take into account all aspects. Don't you have any doubts?

Yes, the perfect answer I'm looking forward to is something like this:

SQL > alter tablespace TBS_BIG resize 500m

I hope you can have a "Bigfile table space, it will be easier to manage" feeling.

The expansion of smallfile tablespaces needs to be implemented using alter database and specifying the name of the data file.

For the expansion of bigfile tablespaces, you can use intuitive commands like alter tablespace without considering the data file name.

This eliminates the need to confirm the name of the data file through the data dictionary view.

Through the above connection, we have learned how to generate tablespaces. Create tablespace statement, except for the one introduced here

There are actually many other options that can be specified. But in most cases, it's okay to leave these options unspecified (using default values).

In order to understand in detail, the structure of the section and area also needs to be grasped. There won't be too much extension here.

The difference between SMALLFILE tablespaces and BIGFILE tablespaces should be carefully studied through the manual.

If you want to be a DBA, be sure to read these manuals.

Next time, let's talk about the relationship between tables and tablespaces.

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.