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

This article mainly introduces the example analysis of TABLESPACE in MANAGE, which is very detailed and has certain reference value. Friends who are interested must finish it!

I. official files

BOOK → Database SQL Language Reference → 12 SQL Statements: ALTER TABLE to ALTER TABLESPACE → ALTER TABLESPACE

Second, expand the table space

The third way to expand the database is to manually increase the capacity of the data file (datafile) or so that the capacity of the data file in the table space (tablespace) can grow dynamically as needed. Users can set dynamic extended properties (dynamic extension properties) for existing or newly created data files.

1. Expand the database by dynamically increasing the data file.

ALTER TABLESPACE

DATAFILE''

AUTOEXTEND ON NEXT-how much does it grow at a time when the size of the table space is not enough

MAXSIZE / MAXSIZE UNLIMITED

2. Increase the size of the data file

ALTER DATABASE DATAFILE''RESIZE

3. Add data files to the tablespace

ALTER TABLESPACE ADD DATAFILE''RESIZE

4. Delete data files for tablespaces

ALTER TABLESPACE DROP DATAFILE''

III. Offline/online tablespace

When some errors occur, Oracle automatically switches the relevant online tablespace (online tablespace) to the offline (offline) state. Users who try to access the data table in the offline tablespace will get an error message. The user must restore (recover) the affected tablespaces after handling the failure. You can manually offline tablespaces:

ALTER TABLESPACE OFFLINE

1. Temporary (temporary):

If one or more files in the tablespace have an error state, you can also take the tablespace offline temporarily. When the Oracle database takes data files that are not offline, checkpoints are set on those data files. If no files are offline, but you use a temporary clause, you do not need to perform a media recovery when you bring the tablespace back online. However, if one or more files in the tablespace are offline due to a write error, and you take the tablespace offline temporarily, the tablespace needs to be restored before it can come back online.

2. Immediate (immediately):

Tablespaces can be taken offline immediately, and the Oracle database does not need to checkpoint any data files. If Immediate is specified, a media restore must be performed on the tablespace before the tablespace can be brought online. If the database is running in NOARCHIVELOG mode, the tablespace cannot be taken offline immediately.

When the tablespace is offline, you should pay attention to the following:

1. Oracle does not allow any SQL statements to continue to reference objects (schema object) in this tablespace.

2. Oracle uses the deferred rollback segment (deferred rollback segment) in the SYSTEM tablespace to save rollback information (rollback data) for completed SQL statements in running transactions.

3. The data dictionary (data dictionary) in the SYSTEM table space will record it.

4. SYSTEM tablespace, temporary tablespace and rollback tablespace are not allowed to be offline.

Tablespaces online:

ALTER TABLESPACE ONLINE

IV. Read only tablespace

1. Tablespace read-only

ALTER TABLESPACE READ ONLY

2. Table space is readable and writable

ALTER TABLESPACE READ WRITE

5. Drop tablespace

DROP TABLESPACE

[including contents/contents and datafiles]-- Delete segments in tablespace | Delete segments and datafiles

Cascade constraints

(note: delete fallback tablespace: you need to switch first and wait for the transaction to finish before deleting)

VI. Rename datafile

ALTER TABLESPACE USERS RENAME DATAFILE'='TO'* *

ALTER DATABASE RENAME FILE'= ='TO'* *

The above is all the content of the article "sample Analysis of TABLESPACE in MANAGE". Thank you for reading! Hope to share the content to help you, more related knowledge, 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.