You are here: Home Administration Archivelogs in the flash recovery area
19 | 04 | 2024
Main Menu
Online database
Resources
Latest Articles
Popular Articles

Administration

Which DDL & DML is possible in read only databases, read only tablespaces and read only tables

  • Print
User Rating:  / 0
PoorBest 
Details
Parent Category: Articles
Created on Wednesday, 10 August 2011 12:57
Last Updated on Saturday, 26 October 2013 20:46
Published on Wednesday, 10 August 2011 12:57
Written by Guy Lambregts
Hits: 7728

Which DDL & DML is possible in read only databases, read only tablespaces and read only tables

 

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.

All Rights Reserved © 2024