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

[basic] Oracle tablespaces and data files

Advantages of multiple tablespaces:

1. It can separate the data dictionary from the user data and avoid the conflict between the dictionary object and the user object in the same data file.

two。 The ability to separate fallback data from user data to avoid permanent data loss due to hard disk damage

3. The data files of the table space can be distributed to different hard drives, and the physical Imax O operations can be evenly distributed.

4. You can set a tablespace offline or online to back up and restore part of the database

5. You can set a tablespace to read-only, thus setting part of the database to read-only

6. You can set up a table space for a special purpose, such as a temporary table space, to optimize the efficiency of the table space.

7. It is more flexible to set tablespace limits for users.

Storage in SYSTEM tablespaces:

1. Data dictionary of database

two。 Source code and parsing code for all PL/SQL programs

3. Definition of database objects

(the sum of the data files for all tablespaces cannot exceed the limit of the maxdatafiles parameter specified when the database is created)

Manage tablespaces locally:

1. There is no need to access the database in the process of storage allocation, so the speed of storage allocation operation can be improved.

two。 It can avoid the recursion in the storage management operation in the table space.

3. There will be no redo and revocation of records

4. Simplify DBA's management of tablespaces

5. Reduce users' dependence on data dictionaries

Table spaces managed by dictionaries:

The management information of all storage spaces in the tablespace is stored in the data dictionary, and fallback and redo records are generated during storage space management.

Tablespaces managed locally:

The management information of all storage spaces in the tablespace is stored in the bitmap in the header of the data file.

(creating tablespaces in 9i defaults to local management prior to .8i, and tablespaces can only be managed by dictionary.)

Create tablespaces (dictionary management)

Create tablespace dmusertbs

Datafile'i:\ oracle\ oradata\ dmusertbs.dbf' size 50m

Extent management dictionary

Multiple data files (dictionary management)

Create tablespace dmusertbs

Datafile'i:\ oracle\ oradata\ dmusertbs01.dbf' size 50m

'I:\ oracle\ oradata\ dmusertbs02.dbf' size 50m

'I:\ oracle\ oradata\ dmusertbs03.dbf' size 50m

Extent management dictionary

Specify default storage parameters (dictionary management)

Create tablespace dmusertbs

Datafile'i:\ oracle\ oradata\ dmusertbs.dbf' size 50m

Default storage (

Initial 256K

Next 256K

Minextents 2

Pctincrease 0

Maxextents 4096)

Minimum extent 128K

Logging

Online

Permanent

Extent management dictionary

Create tablespaces (managed locally)

1 .create tablespace lmusertbs

Datafile'i:\ oracle\ oradata\ lmusertbs.dbf' size 50m

Extent management local autoallocate

(oracle automatically manages the allocation of extents, which is the default setting. In autoallocate mode, the smallest extent in the tablespace is 64K)

2 .create tablespace lmusertbs

Datafile'i:\ oracle\ oradata\ lmusertbs.dbf' size 50m

Extent management local uniform size 512K

(all extents must have a uniform size, and the biggest advantage of Uniform is that it does not produce any storage fragmentation in the tablespace

If you do not specify a value for the size parameter after the uniform keyword, the SIZE parameter will use 1MB as the default)

3. Create tablespace lmusertbs

Datafile'i:\ oracle\ oradata\ lmusertbs.dbf' size 50m

Extent management local uniform size 512K

Segment space management auto

(tablespaces with automatic segment storage management)

Create temporary tablespaces (dictionary management)

A temporary tablespace can be shared by multiple database users. Oracle only creates a temporary period for an instance, which is shared by all sorting operations in the instance, but each zone in the temporary period can only be used by one transaction. A temporary period is created when the first sort operation is performed after the database is started.

Note: temporary tablespaces managed by dictionaries can be changed using alter tablespace like regular tablespaces

It is recommended that the initial and next parameters be set to the same value, and should be initialization parameters sort_area_size and db_block_size

The integer multiple of the sum of the two values, the pctincrease parameter should be set to 0

Create tablespace usertemp

Datafile'i:\ oracle\ oradata\ sort01.dbf' size 50m

Extent management dictionary

Default storage (

Initial 192K

Next 192K

Minextents 1

Pctincrease 0)

Temporary

Create temporary tablespaces (local management mode, zone allocation management mode can only be uniform)

(in Oracle 9i, it is strongly recommended to use locally managed temporary tablespaces instead of dictionary managed temporary tablespaces)

Create temporary tablespace lmtemp

Tempfile'i:\ oracle\ oradata\ lmtemp01.dbf' size 50m

Extent management local uniform size 136K

(you must use the tempfile clause. Tempfile is a temporary data file. Compared with ordinary data files, temporary data files cannot be created with alter database.)

For locally managed temporary table spaces, the function of the alter tablespace statement is only to add new temporary files to the temporary table space.

Add new temporary files

Alter tablespace lmtemp

Add tempfile'i:\ oracle\ oradata\ lmtemp02.dbf' size 50m

(for locally managed tablespaces, the role of the alter tablespace statement is only to add new temporary files to the temporary tablespace, but not to make any other changes to the temporary tablespace)

The management of tablespace mainly includes modifying default storage parameters, merging fragments manually, setting the availability of tablespace and so on. Make tablespaces use as few data files as possible, because in some operating systems there is a limit on the number of system files that a process can open at the same time, which affects the number of tablespaces that are online at the same time.

Modify the default storage parameters for a tablespace

Alter tablespace duusertbs

Default storage (

Next 128K

Maxextents unlimited

Pctincrease 20)

(the initial and minextents parameters cannot be modified after the tablespace is created)

If the default storage parameter pctincrease for the tablespace is not 0, oracle will periodically start the SMON background process to merge the storage fragments adjacent to the tablespace

If pctincrease is 0, the SMON process will not perform the merge operation.

If all extents in the tablespace are the same size, there is no need to manually merge storage fragments.

Locally managed tablespaces do not need to merge storage fragments because oracle automatically uses bitmaps to merge automatically.

You only need to manually merge storage fragments in a dictionary-managed table space.

Alter tablespace dmusertbs coalesce

(if the coalesce clause is used, no other clause can be used.)

You can see which storage fragments exist in the tablespace users by using the following query

SQL > select block_id,bytes,blocks

2 from dba_free_space

3 where tablespace_name='users'

4 order by block_id

Change the availability of the tablespace:

1. Alter tablespace user01 offline normal (normal mode)

Oracle performs a checkpoint, writes all the dirty cache blocks associated with the tablespace in the SGA area to the data file, then closes the data file corresponding to the tablespace, and next time writes the tablespace. When you return to the online state, no database recovery is required.

2. Alter tablespace user01 offline temporary (temporary)

When oracle performs a checkpoint, it does not check the status of individual data files. Even if some data files are not available, oracle will ignore these errors. The next time you restore the tablespace to the online state, you may need to perform a database recovery. If a data file is available, oracle writes dirty cache blocks associated with the tablespace to the data file.

3. Alter tablespace user01 offline immediate (immediate mode)

Oracle does not perform checkpoints or check the status of individual data files, but directly sets the data files belonging to the tablespace offline. The next time the tablespace is restored to the online state, the database recovery is required. Databases running in noarchivelog mode are not allowed to switch offline in this way.

Restore the tablespace to online

Alter tablespace user01 online

Set the tablespace to read-only

Alter tablespace user01 read only

Set the tablespace to read and write

Alter tablespace user01 read write

Delete tablespaces (excluding corresponding data files)

Drop tablespace users including contents

Delete tablespaces (including corresponding data files)

Drop tablespace users including contents and datafiles

Tablespace data dictionary

The name and numbering information of the table space obtained in the v$tablespace control file

V$datafile controls the name and numbering information of the data file obtained in the file

Basic information for all temporary data files in v$tempfile

Information about the sort area created by the v$sort_segment instance

User usage information for the v$sort_user sort area

Name and numbering information of tablespaces in dba_tablespaces database

Information in the middle segment of the dba_segments tablespace

Information about extents in dba_extents tablespaces

Information about free areas in dba_free_space tablespaces

Dba_data_files data file, that is, the information of the tablespace to which it belongs

Dba_temp_files temporary data file, that is, the information of the tablespace to which it belongs

This includes adding new data files to the tablespace and changing the size, name, or location of existing data files. The initialization parameter db_files specifies the maximum number of data files that can be saved in the SGA area, that is, the maximum number of data files that an instance can support, which can be modified during the instance operation.

1. Set the data file to auto-grow mode when creating tablespace lmusertbs

Create tablespace lmusertbs

Datafile'i:\ oracle\ oradata\ lmusertbs01.dbf' size 50m

Autoextend on

Next 5M

Maxsize 500M

Extent management local

(the next parameter specifies the size of each automatic growth, and maxsize is the maximum size of the data file)

two。 Add an auto-growing data file to the tablespace lmusertbs

Alter tablespace lmusertbs

Add datafile'i:\ oracle\ oradata\ lmusertbs02.dbf' size 50m

Autoextend on

Next 5M

Maxsize 500M

3. If the data file has been created, set it to auto-growth mode

Alter database

Datafile'i:\ oracle\ oradata\ dmusertbs01.dbf'

Autoextend on

Next 5M

Maxsize 500M

4. Cancel the automatic growth mode of existing data files

Alter database

Datafile'i:\ oracle\ oradata\ dmusertbs01.dbf'

Autoextend off

5. Manually resize the data file:

Increase the data file dmusertbs01.dbf to 500MB

Alter database datafile'i:\ oracle\ oradata\ dmusertbs01.dbf' resize 500m

Change the availability of the data file separately (the database runs in archive mode):

Alter database datafile'i:\ oracle\ oradata\ dmusertbs01.dbf' online; (online status)

Alter database datafile'i:\ oracle\ oradata\ dmusertbs01.dbf' offline; (offline status)

Change the availability of the data file separately (the database runs in non-archiving mode):

Alter database datafile'/ u02Universe oracleandoradata offline drop user01.dbf'

Use offline drop to ensure that data files are discarded as soon as they are offline

6. Change the name and location of the data file:

a. The data file to be changed belongs to the same tablespace

1. Set the tablespace containing the data file to offline

Alter tablespace users offline normal

two。 Rename or move data files in the operating system

3. Modify the name or location of the data file inside the database (using alter tablespace. Rename datafile clause, the data file after the to clause must exist)

Change the name:

Alter tablespace users

Rename datafile

'I:\ oracle\ oradata\ user01.dbf'

'I:\ oracle\ oradata\ user02.dbf'

To

'I:\ oracle\ oradata\ lmuser01.dbf'

'I:\ oracle\ oradata\ lmuser02.dbf'

Change position:

Alter tablespace users

Rename datafile

'I:\ oracle\ oradata\ user01.dbf'

'I:\ oracle\ oradata\ user02.dbf'

To

'h:\ oracle\ oradata\ user01.dbf'

'h:\ oracle\ oradata\ user02.dbf'

4. Reset the tablespace to online

Alter tablespace users online

5. Backup control file

b. The data file to be changed belongs to multiple tablespaces

1. Shut down the database

two。 Rename or move data files in the operating system

3. Load database, startup mount

4. Modify the name or location of the data file inside the database (using alter database. Rename file clause, the data file after the to clause must exist)

Alter database

Rename file

'I:\ oracle\ oradata\ user01.dbf'

'I:\ oracle\ oradata\ temp01.dbf'

To

'I:\ oracle\ oradata\ lmuser01.dbf'

'I:\ oracle\ oradata\ lmtemp01.dbf'

5.alter database open

6. Backup control file

Data file data dictionary

Information for all data files in the DBA_DATA_FILES database

Information for all temporary data files in the DBA_TEMP_FILES database

Description of the assigned extents in the DBA_EXTENTS tablespace, including the number of the data file to which the extent belongs

Information about free areas in DBA_FREE_SPACE tablespaces

Delete data files for tablespaces

(applicable: accidentally add a data file to a tablespace, or you set the file size too large, so you want to delete it)

(note: Oracle does not provide tables such as delete tables. Views are the same way to delete data files, which are part of the tablespace, so you cannot "remove" the tablespace.

Make a full backup of the database before any offline or deletion of the tablespace / data file)

A: if the data file is the only data file in the table space, you can simply delete the table space:

DROP TABLESPACE INCLUDING CONTENTS

B: if your tablespace has multiple data files and you don't need the contents of the tablespace, or you can easily recreate the contents of the tablespace, you can use the

DROP TABLESPACE INCLUDING CONTENTS; command to delete the contents of tablespaces, data files, and tablespaces from the Oracle data dictionary. Oracle no longer accesses anything in this tablespace. Then recreate the tablespace and re-import the data.

C: if you have multiple data files in your tablespace and you need to retain the contents of other data files in that tablespace, you must first export all the contents in that tablespace. To determine what is contained in the table space, run:

Select owner,segment_name,segment_type

From dba_segments

Where tablespace_name=''

Export gives you what you want to keep. If export ends, you can use DROP TABLESPACE tablespace INCLUDING CONTENTS. Permanently delete the contents of the table space, physically delete the data file using operating system commands, recreate the table space according to the desired data file, and import the data to the table space

Note:

The ALTER DATABASE DATAFILE OFFLINE DROP command does not allow you to delete a data file, its purpose is to take the data file offline to delete the tablespace. If you are in archive mode, use ALTER DATABASE DATAFILE OFFLINE DROP instead of OFFLINE DROP. Once the data file is offline, Oracle no longer accesses the contents of the data file, but it is still part of the tablespace. This data file marks OFFLINE in the control file and does not compare it with the control file SCN when the database is started. Keeping the entry of this data file in the control file is convenient for later recovery.

If you don't want to delete tablespaces as described above, there are other solutions.

1. If the reason you want to delete data files is because you have allocated an inappropriate file size, you can consider the RESIZE command.

two。 If you accidentally add a data file that has not allocated space, you can use the

The ALTER DATABASE DATAFILE RESIZE; command makes it less than 5 Oracle block sizes, and if the data file size is smaller than this, Oracle will not extend the data file. In the future, Oracle can be rebuilt to eliminate this incorrect file.

-

Question:

For example, my uses tablespace has two data files: users01.dbf and users02.dbf, (the database is in non-archive mode) if I execute

Alter tablespace users offline

Alter database datafile users02.dbf offline drop

At this point, if I delete users02.dbf at the operating system level, I will be prompted to lose files the next time I restart.

I estimate that this command has only been modified at the controlfile level, but this file is still recorded in dictionary. How can I completely delete a data file in the tablespace?

ASK:

In noarchivelog mode, alter database datafile... offline drop

The drop option must be added, but the drop option does not remove datafile from database. In order to drop datafile, you must delete the tablespace where datafile is located. With the drop option, datafile remains in the data dictionary with a status of recover or offline.

Alter database datafile... offline drop

Once the data file offline,oracle no longer accesses that datafile, but it still is part of that tablespace. Datafile is marked as offline in controlfile, and there is no comparison of scn between controlfile and datafile in startup (this also runs your startup database, along with an uncritical datafile loss). Datafile is not removed from controlfile, thus giving you recover the datafile.

If you really want to delete user02.dbf, you can use the transport tablespace feature, or export the object you want to save in user tablespace space and create a new new tablespace

If you do not wish to follow any of these procedures, there are other things

That can be done besides dropping the tablespace.

If the reason you wanted to drop the file is because you mistakenly created

The file of the wrong size, then consider using the RESIZE command.

If you really added the datafile by mistake, and Oracle has not yet allocated any space within this datafile, then you can use ALTER DATABASE DATAFILE RESIZE; command to make the file smaller than 5 Oracle blocks. If the datafile is resized to smaller than 5 oracle blocks, then it will never be considered for extent allocation. At some later date, the tablespace can be

Rebuilt to exclude the incorrect datafile.

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.