[20130606]11G的表以及表空间的只读.txt

[20130606]11G的表以及表空间的只读.txt

1.只读表:

SQL> @ver

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> create table t1 (id number);

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> commit ;

Commit complete.

SQL> alter table t1 read only ;

Table altered.

SQL> insert into t1 values (2);

insert into t1 values (2)

            *

ERROR at line 1:

ORA-12081: update operation not allowed on table "SCOTT"."T1"

SQL> delete from t1 ;

delete from t1

SQL> truncate table t1 ;

truncate table t1

               *

--标识的是update操作.

--drop table t1可以吗?

SQL> drop table t1;

Table dropped.

--这个跟只读表空间一样的,里面的表也可以删除.这点切记,标识表只读,依旧可以drop表.

SQL> purge table t1;

Table purged.

--这样就无法恢复了.

2.只读表空间.

--去年写过一篇,再重复测试一次.

http://space.itpub.net/267265/viewspace-745131

CREATE TABLESPACE AAA DATAFILE

  '/u01/app/oracle11g/oradata/test/aaa01.dbf' SIZE 65528K AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

SQL> create table t1 (id number) tablespace aaa;

SQL> alter tablespace aaa read only;

Tablespace altered.

ORA-00372: file 11 cannot be modified at this time

ORA-01110: data file 11: '/u01/app/oracle11g/oradata/test/aaa01.dbf'

--能drop table吗?

--依旧可以删除.

--SEGMENT_TYPE编程了TEMPORARY.

google找到一段解析:

http://blog.tanelpoder.com/2010/07/11/dropping-and-creating-tables-in-read-only-tablespaces-what/

    Wow, Oracle has converted the table segment into a temporary segment instead (see segment_type)! Bur our tablespace is

read only, how can it do that?! The answer is that neither the regular DROP nor DROP PURGE need to write anything into the

tablespace where the segment resides! The initial DROP operation just updated data dictionary, like renaming the table to

BIN$… in OBJ$ and so on. The second DROP PURGE operation just ran a bunch of deletes against data dictionary to indicate

that the table object is gone. But why is the TEMPORARY segment left behind? This has to do with locally managed tablespaces.

Before LMT days, when you dropped a segment, then the segment space was released and acquired back to tablespace through

inserts/updates to UET$/FET$ (used/free extents) base tables, which resided in system tablespace like all other data dictionary

base tables. But with LMTs, the free space information is kept in bitmaps in the tablespace files themselves! Thus, if you

drop a table in a read only LMT tablespace, the table will be gone, but the space will not be physically released (as you

can't update the LMT bitmaps in read only tablespace files). However, Oracle doesn't want to lose that space should someone

make the tablespace read write later on, so the table segment is updated to be a TEMPORARY segment instead of completely

deleting it from data dictionary. That's how the SMON can clean it up later on should that tablespace become read-write

again. The 9.130 in SEGMENT_NAME column means relative file# 9 and starting block# 130, that's a segment's unique identifier

in a tablespace. Let's move on. This example is executed on Oracle 11.2, while logged on to a non-SYS/SYSTEM user:

总结:只读表以及表空间的对象依旧可以DROP,这点切记.