See also Read Only Tables
SQL> show release
release 1102000200
SQL> drop user boris cascade;
drop user boris cascade
*
ERROR at line 1:
ORA-01918: user 'boris' does not exist
SQL> create user boris identified by boris;
User created.
SQL> create tablespace BORIS_READ_WRITE datafile '/opt/oradata/SILVER/boris_read_write.dbf' size 100M extent management local segment space management auto;
Tablespace created.
SQL> create tablespace BORIS_READ_ONLY datafile '/opt/oradata/SILVER/boris_read_only.dbf' size 100M extent management local segment space management auto;
Tablespace created.
SQL> alter user boris default tablespace BORIS_READ_WRITE;
User altered.
SQL> grant create session,create table to boris;
Grant succeeded.
SQL> alter user boris quota unlimited on BORIS_READ_WRITE;
User altered.
SQL> alter user boris quota unlimited on BORIS_READ_ONLY;
User altered.
SQL> connect boris/boris;
Connected.
SQL> create table T1 (col1 number) tablespace BORIS_READ_ONLY;
Table created.
SQL> create table T2 (col1 number) tablespace BORIS_READ_WRITE;
Table created.
SQL> create table T3 (col1 number) tablespace BORIS_READ_WRITE;
Table created.
SQL> insert into t1 values (1);
1 row created.
SQL> insert into t2 values (1);
1 row created.
SQL> insert into t3 values (1);
1 row created.
SQL> commit;
Commit complete.
-- read only table in read write tablespace;
SQL> alter table t3 read only;
Table altered.
SQL> connect / as sysdba
SQL> alter tablespace BORIS_READ_ONLY read only;
Tablespace altered.
SQL> connect boris/boris
Connected.
SQL> column read_only format a10
SQL> select table_name,read_only,tablespace_name from user_tables;
TABLE_NAME READ_ONLY TABLESPACE_NAME
------------------------------ ---------- ------------------------------
T3 YES BORIS_READ_WRITE -- read only table in read write tablespace
T2 NO BORIS_READ_WRITE -- read write table in read write tablespace
T1 NO BORIS_READ_ONLY -- read write table in read only tablespace
Which tables can we index, can we drop, is DML allowed ?
A. when the database is opened in read only mode
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2229080 bytes
Variable Size 482348200 bytes
Database Buffers 134217728 bytes
Redo Buffers 7532544 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> connect boris/boris;
Connected.
SQL> drop table t1;
drop table t1
*
ERROR at line 1:
ORA-16000: database open for read-only access
SQL> drop table t2;
drop table t2
*
ERROR at line 1:
ORA-16000: database open for read-only access
SQL> drop table t3;
drop table t3
*
ERROR at line 1:
ORA-16000: database open for read-only access
Boris cannot drop any table even though he is the owner
B. When the database is in read write mode
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2229080 bytes
Variable Size 482348200 bytes
Database Buffers 134217728 bytes
Redo Buffers 7532544 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> connect boris/boris;
Connected.
SQL> drop table t3;
Table dropped.
SQL> drop table t2;
Table dropped.
SQL> drop table t1;
Table dropped.
SQL> flashback table t1 to before drop;
Flashback complete.
SQL> flashback table t2 to before drop;
Flashback complete.
SQL> flashback table t3 to before drop;
Flashback complete.
Boris can drop any table since he is the owner, no matter the read only status of the tablespace, no matter the read only status of the table
SQL> delete from t1;
delete from t1
*
ERROR at line 1:
ORA-00372: file 7 cannot be modified at this time
ORA-01110: data file 7: '/opt/oradata/SILVER/boris_read_only.dbf'
SQL> delete from t2;
1 row deleted.
SQL> delete from t3;
delete from t3
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "boris"."T3"
SQL> rollback;
Rollback complete.
We cannot perform any dml on the table in read only tablespaces, nor on read only tables
SQL> alter table t1 add constraint pk_t1 primary key (col1);
Table altered.
SQL> alter table t2 add constraint pk_t2 primary key (col1);
Table altered.
SQL> alter table t3 add constraint pk_t3 primary key (col1);
Table altered.
SQL> select index_name,tablespace_name from user_indexes;
INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PK_T3 BORIS_READ_WRITE
PK_T2 BORIS_READ_WRITE
PK_T1 BORIS_READ_WRITE
SQL> alter table t1 drop constraint pk_t1;
Table altered.
SQL> alter table t2 drop constraint pk_t2;
Table altered.
SQL> alter table t3 drop constraint pk_t3;
Table altered.
SQL> alter table t1 add constraint pk_t1 primary key (col1) using index tablespace BORIS_READ_ONLY;
alter table t1 add constraint pk_t1 primary key (col1) using index tablespace BORIS_READ_ONLY
*
ERROR at line 1:
ORA-01647: tablespace 'BORIS_READ_ONLY' is read-only, cannot allocate space in it
We can create indexes, constraints on read only tables but we cannot create indexes in read only tablespaces
SQL> alter table t3 add col2 number;
alter table t3 add col2 number
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "boris"."T3"
SQL> alter table t2 add col2 number;
Table altered.
SQL> alter table t1 add col2 number;
Table altered.
We cannot change the structure of read only tables,
We can change the structure ( add column ) of a table in a read only tablespace ( since it is the metadata we change )
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2229080 bytes
Variable Size 482348200 bytes
Database Buffers 134217728 bytes
Redo Buffers 7532544 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL> drop user boris cascade;
drop user boris cascade
*
ERROR at line 1:
ORA-16000: database open for read-only access
SQL> drop tablespace boris_read_write;
drop tablespace boris_read_write
*
ERROR at line 1:
ORA-16000: database open for read-only access
SQL> drop tablespace boris_read_only;
drop tablespace boris_read_only
*
ERROR at line 1:
ORA-16000: database open for read-only access
To fully protect the database aginst any DDL, DML we can open the database in read only mode
Note that read only tablespace are not checkpointed anymore, which is important for backup and recovery
SQL> column name format a45
SQL> set numwidth 20
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
--------------------------------------------- --------------------
/opt/oradata/SILVER/system01.dbf 11192308
/opt/oradata/SILVER/sysaux01.dbf 11192308
/opt/oradata/SILVER/undotbs01.dbf 11192308
/opt/oradata/SILVER/users01.dbf 11192308
/opt/oradata/SILVER/test_tspits.dbf 11192308
/opt/oradata/SILVER/boris_read_write.dbf 11192308
/opt/oradata/SILVER/boris_read_only.dbf 11185717
7 rows selected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2229080 bytes
Variable Size 482348200 bytes
Database Buffers 134217728 bytes
Redo Buffers 7532544 bytes
Database mounted.
Database opened.
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
--------------------------------------------- --------------------
/opt/oradata/SILVER/system01.dbf 11192311
/opt/oradata/SILVER/sysaux01.dbf 11192311
/opt/oradata/SILVER/undotbs01.dbf 11192311
/opt/oradata/SILVER/users01.dbf 11192311
/opt/oradata/SILVER/test_tspits.dbf 11192311
/opt/oradata/SILVER/boris_read_write.dbf 11192311
/opt/oradata/SILVER/boris_read_only.dbf 11185717
7 rows selected.
SQL> alter system checkpoint;
System altered.
SQL> select name,checkpoint_change# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
--------------------------------------------- --------------------
/opt/oradata/SILVER/system01.dbf 11192533
/opt/oradata/SILVER/sysaux01.dbf 11192533
/opt/oradata/SILVER/undotbs01.dbf 11192533
/opt/oradata/SILVER/users01.dbf 11192533
/opt/oradata/SILVER/test_tspits.dbf 11192533
/opt/oradata/SILVER/boris_read_write.dbf 11192533
/opt/oradata/SILVER/boris_read_only.dbf 11185717
7 rows selected.