Tables in Oracle read-only tablespaces can be dropped.

Source: Internet
Author: User
Each database can contain multiple UNDO tablespaces. In the automatic revocation management mode, each Oracle instance specifies an UNDO tablespace (with only one tablespace ). Withdrawal

Each database can contain multiple UNDO tablespaces. In the automatic revocation management mode, each Oracle instance specifies an UNDO tablespace (with only one tablespace ). Withdrawal

In Oracle databases, there are at least the following table spaces:
1) SYSTEM tablespace. Each database contains a SYSTEM tablespace. When a database is created, the SYSTEM tablespace is automatically created. When the database is opened, the SYSTEM tablespace is always ONLINE. The important thing about the SYSTEM tablespace is that it can store the data dictionary of the entire database. In addition, some storage units in PLSQL, for example, stored procedures, functions, packages, and related triggers must be stored in the tablespace of the database.
2) The database component uses the SYSAUX tablespace as the default location for storing data. Therefore, when the database is being created and upgraded, the SYSAUX tablespace is automatically created. The SYSAUX tablespace can reduce the number of tablespaces created by default. During common database operations, ORACLE Database Service does not allow you to delete SYSAUX tablespaces or change names.
3) UNDO tablespace
Each database can contain multiple UNDO tablespaces. In the automatic revocation management mode, each Oracle instance specifies an UNDO tablespace (with only one tablespace ). UNDO partitions are used for UNDO tablespace management. UNDO partitions are automatically created and maintained by ORACLE. In Oracle, we can add, delete, update, and other transaction operations on the Data. Database Operations in the transaction can be undone, and the execution speed is usually slow ,, but the revocation speed is relatively fast. Why? This is because Oracle binds the database operation to the UNDO interval in the UNDO tablespace when the transaction operates in the database. If you want to cancel a transaction, Oracle will UNDO it based on the UNDO interval in the UNDO tablespace.
4) Temporary tablespace. If the system tablespace is local, at least one default temporary tablespace TEMP must be specified. If you delete all temporary tablespaces, Oracle will specify the SYSTEM tablespace as the temporary tablespace.

Benefits of temporary tablespace:
1) when you use the SELECT statement to query the database, you can use multiple temporary tablespace for sorting to avoid the problem of insufficient temporary tablespace space.
2) Temporary tablespace can be effectively used in parallel operations.
3) a user can use multiple temporary tablespaces at the same time in different sessions.

Here:
To prevent the data in the tablespace from being modified, you can change the tablespace attribute to a read-only tablespace. The primary purpose of setting the tablespace is to prevent the static data from being modified, in this way, you can perform database backup and recovery operations, and protect the data in the read-only tablespace from being modified.

Set the syntax of the read-only tablespace:
ALTER TABLESPACE <表空间> Read only;
Set the tablespace to the read/write Syntax:
Alter tablespace <> read write;

During the tablespace setting process, we found that tables in the read-only tablespace can be dropped. Why?
As mentioned above, each database will have an ONLINE tablespace during operation. The tablespace name is SYSTEM, and the data dictionary is saved in the SYSTEM tablespace, and database objects such as stored procedures, triggers, functions, and packages in PLSQL. When you delete a database using DDL, It is the operating SYSTEM tablespace. ORACLE sets the deleted table to the DROP state in the data dictionary stored in the SYSTEM, when the status of the tablespace changes to read write, the table will be deleted from the database.

The related code is as follows:

SQL> list
1 create table chenzw (
2 username varchar2 (100)
3 *)
SQL> run
1 create table chenzw (
2 username varchar2 (100)
3 *)
The table has been created.
SQL> insert into chenzw values ('1 ');
One row has been created.
SQL> alter tablespace data read only;
The tablespace has been changed.
SQL> insert into chenzw values ('2 ');
Insert into chenzw values ('2 ')
*
Row 3 has an error:
ORA-00372: file cannot be modified at this time
ORA-01110: DATA File 5: 'e: \ GXSIDB \ data'
SQL> truncate table chenzw;
Truncate table chenzw
*
Row 3 has an error:
ORA-00372: file cannot be modified at this time
ORA-01110: DATA File 5: 'e: \ GXSIDB \ data'
SQL> delete from chenzw;
Delete from chenzw
*
Row 3 has an error:
ORA-00372: file cannot be modified at this time
ORA-01110: DATA File 5: 'e: \ GXSIDB \ data'
SQL> drop table chenzw;
The table has been deleted.
SQL> commit;
Submitted.
SQL> alter tablespace data read write;
The tablespace has been changed.
SQL> select * from chenzw;
Select * from chenzw
*
Row 3 has an error:
ORA-00942: Table or view does not exist
SQL>

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.