18_oracle_admin_ read-only table space and offline table space

a , read-only table space

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4B/5F/wKiom1Qq0iHgzt1NAAFwVpIK3jg452.jpg "title=" 1.png " alt= "Wkiom1qq0ihgzt1naafwvpik3jg452.jpg"/>

A read-only table space can only be a select operation, and the command to set the read-only table space is:

ALTER tablespace UserData READ only;

setting a read-only tablespace triggers checkpoint towrite the data in the cache into data file,But even though the read-only attribute is set, the table space can be drop operations (such as drop table), because the drop operation does not alter the structure of the tablespace, but only changes the values in the data dictionary, and the data dictionary is stored in the system tablespace ,the system Tablespace is not allowed to be set to READ only .

======== Create a table space ========

Sql> Create Tablespace Wiley

2 datafile '/oracle/oradata/orcl/wiley.dbf ' size 20M

3 Extent management local uniformsize 128k;

Tablespace created.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4B/61/wKioL1Qq0mWxEma4AACWBCu3A74430.jpg "title=" 2.PNG " alt= "Wkiol1qq0mwxema4aacwbcu3a74430.jpg"/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4B/5F/wKiom1Qq0kqhmaccAADOmVgHWJ8142.jpg "title=" 3.png " alt= "Wkiom1qq0kqhmaccaadomvghwj8142.jpg"/>

========== Creates a user that specifies its default tablespace as a newly created tablespace ===========

Sql> Create user Wiley identified by******

2 default tablespace Wiley;

--Create user-related information to view the online document SQL REFERENCE, where user Wiley is created, and the default tablespace is specified as the newly created Tablespace Wiley. User created.

Sql> Grant Connect, resource to Wiley;

--Assign the appropriate permissions to the new user Grant succeeded.

=========== New User login database ==============

[Email protected] ~]$ Sqlplus Wiley

Sql*plus:release 11.2.0.1.0 Production on Sat 3014:44:12 Copyright (c) 1982,----Oracle. All rights reserved. Enter password:connected to:oracle Database 11g Enterprise Edition Release 11.2.0.1.0-productionwith the partitioning, O LAP, Data Mining and realapplication testing options

========== Create a table and insert a piece of data ==========

Sql> CREATE TABLE t (Id Integer, Namechar (10));

Table created.

Sql> INSERT into t values (0, ' Wiley ');

1 row created.

Sql> commit;

Commit complete.

Sql> select * from T;

ID NAME--------------------0 Wiley

======== Insert One more piece of data, but not commit===========

Sql> INSERT INTO t values (1, ' 2014 ');

1 row created.

Sql> select * from T;

ID NAME--------------------0 Wiley 1 2014

============ switch to SYS user ==============

Sql> Show User

USER is "SYS"

Sql> Alter Tablespace Wiley Read only;

-- Execution Alter command, the database is hold Live because the user does not have Commit

====== Switch to Wiley User,commit The value you just inserted ========

Sql> Show User

USER is "WILEY"

Sql> commit;

Commit complete.

======== switch to sys again user ========

Sql> Alter Tablespace Wiley Read only;

Tablespace altered.--tablespace has been altered.

=========== Switch to Wiley again user ============

Sql> INSERT INTO t values (2, ' Mickey ');

INSERT into t values (2, ' Mickey ') *error @ Line 1:ora-00372:file 6 cannot is modified at this timeORA-01110: Data file 6: '/oracle/oradata/orcl/wiley.dbf '--cannot be inserted.

Sql> Delete from t where id = 1;

Delete from t where id = 1 *error on line 1:ora-00372:file 6 cannot is modified at this timeora-01110:data fi Le 6: '/oracle/oradata/orcl/wiley.dbf '--also cannot delete data

sql> truncate TABLE t;

TRUNCATE TABLE T *error at line 1:ora-00372:file 6 cannot is modified at this timeora-01110:data file 6: ' /ORACLE/ORADATA/ORCL/WILEY.DBF '--also cannot truncate the table, because truncate only deletes the data without deleting the table structure, which is equivalent to delete

Sql> select * from T;

ID NAME--------------------0 Wiley 1 2014--can be queried

sql> drop table t;

Table dropped.--can drop table.

======== switch to sys User, will Wiley set to read-write tablespace ==========

Sql> Show User

USER is "SYS"

sql> alter Tablespace Wiley Read write;  

Tablespace altered.

read-only table spaces can be used to hold static data that is not frequently modified because read-only table spaces do not require recovery (recovery), which can improve database performance.

Second, set the table space offline

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/4B/5F/wKiom1Qq0l_jJhqfAAIASHFj9hI766.jpg "title=" 4.png " alt= "Wkiom1qq0l_jjhqfaaiashfj9hi766.jpg"/>

It is more reliable to make the table space offline when the database is being restored, and when the tablespace is set offline, the user will not be able to access the data. However, there are cases where the table space cannot be set offline:

1. The SYSTEM table space cannot be set offline;

2. if certain things aregoing on in the tablespace (transaction), the database will add a undosegment in the Undotablespace , The undosegment is out of active state, then the table space can not be set offline;

3. The default global temporary table space, if the temporary table space is not default , can be offline;

========= with Wiley user creates a table =========

sql> CREATE TABLE TT (ID integer, Namechar (Ten));  

Table created.

sql> INSERT INTO TT values (0, ' AAA ');  

1 row created.

sql> commit;  

Commit complete.

Sql> select * from TT;

ID NAME--------------------0 AAA

 

sql> INSERT INTO TT values (1, ' BBB ');  

1 row created.

sql> SELECT * from TT;  

ID NAME--------------------0 AAA 1 BBB

======= switch to SYS User, Wiley the tablespace Set as offline =========

sql> alter tablespace Wiley offline;  

Tablespace altered.--You can set the tablespace offline even if the user has no commit

======== with Wiley user re-login ===========

[Email protected]~]$ sqlplus Wiley

Sql*plus:release 11.2.0.1.0 Production on Sat 3015:18:30 Copyright (c) 1982,----Oracle. All rights reserved. Enter password:connected to:oracle Database 11g Enterprise Edition Release 11.2.0.1.0-productionwith the partitioning, O LAP, Data Mining and realapplication testing options# offline tablespace can also be logged in

Sql> select * from TT;

SELECT * from Tt*error in line 1:ora-00376:file 6 cannot is read at this timeora-01110:data file 6: '/oracle/oradata/orc L/WILEY.DBF '

======== cut back to SYS User, and then the Wiley This table space is set to online ========

Sql> Alter tablespace Wiley Online;

Tablespace altered.

=========== cut back to Wiley user ==========

Sql> select * from TT;

--Can execute query statement ID NAME--------------------0 AAA 1 BBB

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4B/61/wKioL1Qq0pyCIhYfAACSTYnbzZk089.jpg "title=" 5.png " alt= "Wkiol1qq0pycihyfaacstynbzzk089.jpg"/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/4B/5F/wKiom1Qq0oHxIVj6AAGbo4iqBGs978.jpg "title=" 6.png " alt= "Wkiom1qq0ohxivj6aagbo4iqbgs978.jpg"/>

There are three options to attach when setting up offline: NORMAL, temporary, and IMMEDIATE, respectively, correspond to different enforcement levels, and you can query online document SQL REFERENCE to get relevant information.

This article is from the "Big sword without front of the great Qiao Not Work" blog, please make sure to keep this source http://wuyelan.blog.51cto.com/6118147/1560213

18_oracle_admin_ read-only table space and offline table space