[20120926]只读表空间.txt
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
CREATE TABLESPACE TEST DATAFILE
'/u01/app/oracle11g/oradata/test/test01.dbf' SIZE 64M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
create table t tablespace test as select rownum id,'testtest' name from dual connect by level
ALTER TABLESPACE TEST READ ONLY;
SQL> create table tx tablespace test as select rownum id,'testtest' name from dual connect by level
create table tx tablespace test as select rownum id,'testtest' name from dual connect by level
*
ERROR at line 1:
ORA-01647: tablespace 'TEST' is read-only, cannot allocate space in it
SQL> drop table t purge ;
Table dropped.
-- 即使建立在只读表空间的对象,其建立的对象可以删除,但是占用的空间呢?
SQL> column objectname format a20
SQL> SELECT owner, DECODE (partition_name, NULL, segment_name, segment_name || ':' || partition_name) objectname,
segment_type objecttype, NVL (BYTES, 0) "SIZE", NVL (initial_extent, 0) initialext, NVL (next_extent, 0) nextext,
NVL (extents, 0) numextents, NVL (max_extents, 0) "MAXEXTENTS",HEADER_FILE,HEADER_BLOCK
FROM dba_segments
WHERE tablespace_name = 'TEST';
OWNER OBJECTNAME OBJECTTYPE SIZE INITIALEXT NEXTEXT NUMEXTENTS MAXEXTENTS HEADER_FILE HEADER_BLOCK
------- -------------------- ------------------ ---------- ---------- ---------- ---------- ---------- ----------- ------------
SCOTT 8.130 TEMPORARY 262144 65536 1048576 4 2147483645 8 130
--objectname=8.130. 看看能访问吗? TEMPORARY段不能这样访问.
SQL> select * from "8.130";
select * from "8.130"
*
ERROR at line 1:
ORA-00942: table or view does not exist
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:
该blog还提到了deferred segment creation的问题,就是表空间只读,在11GR2下依旧可以建立表,但是不分配段.
SQL> select status from dba_tablespaces where tablespace_name = 'TEST';
STATUS
---------
READ ONLY
SQL> create table test(a int) tablespace test;
Table created.
SQL> drop table test purge;
Table dropped.
SQL> create table test (a int) segment creation IMMEDIATE tablespace test;
create table test (a int) segment creation IMMEDIATE tablespace test
*
ERROR at line 1:
ORA-01647: tablespace 'TEST' is read-only, cannot allocate space in it
SQL> select owner,segment_name,segment_type from dba_segments where tablespace_name = 'TEST';
OWNER SEGMENT_NAME SEGMENT_TYPE
------ ------------- ------------------
SCOTT 8.130 TEMPORARY
按照blog的介绍,只要打开读写模式,smon就会清除这些临时段.
SQL> alter tablespace test read write;
Tablespace altered.
SQL> select owner,segment_name,segment_type from dba_segments where tablespace_name = 'TEST';
no rows selected
--OK,临时段被清除.
