[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> select table_name,read_only from dba_tables where wner=user and table_name='T1';
TABLE_NAME REA
---------- ---
T1         YES

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
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."T1"

SQL> truncate table t1 ;
truncate table t1
               *
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."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;
Table created.

SQL> insert into t1 values (1);
1 row created.

SQL> commit ;
Commit complete.

SQL> alter tablespace aaa read only;
Tablespace altered.

SQL> select tablespace_name,block_size,status from dba_tablespaces  where tablespace_name='AAA';
TABLESPACE_NAME                BLOCK_SIZE STATUS
------------------------------ ---------- ---------
AAA                                  8192 READ ONLY

SQL> insert into t1 values (2);
insert into t1 values (2)
            *
ERROR at line 1:
ORA-00372: file 11 cannot be modified at this time
ORA-01110: data file 11: '/u01/app/oracle11g/oradata/test/aaa01.dbf'

SQL> delete from t1 ;
delete from t1
            *
ERROR at line 1:
ORA-00372: file 11 cannot be modified at this time
ORA-01110: data file 11: '/u01/app/oracle11g/oradata/test/aaa01.dbf'

SQL> truncate table t1 ;
truncate table t1
               *
ERROR at line 1:
ORA-00372: file 11 cannot be modified at this time
ORA-01110: data file 11: '/u01/app/oracle11g/oradata/test/aaa01.dbf'

--能drop table吗?

SQL> drop table t1;
Table dropped.

--依旧可以删除.

SELECT owner, segment_name, segment_type, BYTES, initial_extent, next_extent, extents, max_extents, header_file, header_block
  FROM dba_segments
 WHERE tablespace_name = 'AAA';
OWNER  SEGMENT_NAME         SEGMENT_TYPE            BYTES INITIAL_EXTENT NEXT_EXTENT    EXTENTS MAX_EXTENTS HEADER_FILE HEADER_BLOCK
------ -------------------- ------------------ ---------- -------------- ----------- ---------- ----------- ----------- ------------
SCOTT  BIN$3nPoiscXV9DgQyhk TABLE                   65536          65536     1048576          1  2147483645          11          138
       qMDi8w==$0

SQL> purge table t1;
Table purged.

SELECT owner, segment_name, segment_type, BYTES, initial_extent, next_extent, extents, max_extents, header_file, header_block
  FROM dba_segments
  WHERE tablespace_name = 'AAA';
OWNER  SEGMENT_NAME         SEGMENT_TYPE            BYTES INITIAL_EXTENT NEXT_EXTENT    EXTENTS MAX_EXTENTS HEADER_FILE HEADER_BLOCK
------ -------------------- ------------------ ---------- -------------- ----------- ---------- ----------- ----------- ------------
SCOTT  11.138               TEMPORARY               65536          65536     1048576          1  2147483645          11          138

--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,这点切记.

时间： 2024-11-08 21:35:51

[20130606]11G的表以及表空间的只读.txt的相关文章

[20170520]利用undo表空间保护数据.txt

[20170520]利用undo表空间保护数据.txt --//undo表空间是用来记录前映像信息,也用来保证查询时一致性的.上个星期去听一些课,提到不打开归档情况下一些维护技巧, --//就是建立多个redo日志文件,用来保存日志,至少维持3-4天甚至1个星期的日志,这样可以一定程度减少错误以及会查问题. --//另外提到一种利用undo表空间避免异常操作的恢复方法,就是再建立1个undo表空间,出现异常dml语句时切换使用新的undo表空间. --//这样可以非常从容的恢复信息.当然最好不要

[20150112]系统管理表空间的疑问.txt

[20150112]系统管理表空间的疑问.txt http://www.itpub.net/thread-1903121-1-1.html 10g下每个数据文件3-8块为位图区. 6个块＝48K(假设数据块大小8k) 48*8*1024＝393216 区 对于系统表空间管理.1区＝64K. SCOTT@test> set numw 20 SCOTT@test> select 48*8*1024*64*1024 from dual;    48*8*1024*64*1024 ----------

[20170623]传输表空间补充测试.txt

[20170623]传输表空间补充测试.txt --//昨天测试了使用dblink+传输表空间,链接如下:http://blog.itpub.net/267265/viewspace-2141115/ --//今天补充测试看看加参数SQLFILE生成的脚本是什么内容. impdp scott/book network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.d

[20150312]了解表空间使用情况.txt

[20150312]了解表空间使用情况.txt --11g,想了解表空间使用情况: --首先查询 select * from sys.ts$ --获得ts#.   SELECT LAG (RTIME) OVER (ORDER BY snap_id) begin_time         ,a.*         ,  tablespace_usedsize          - LAG (tablespace_usedsize) OVER (ORDER BY snap_id)          

[20130708]传输表空间与data_object_id.txt

[20130708]传输表空间与data_object_id.txt 传输表空间与scn.txt:http://space.itpub.net/267265/viewspace-750140 初学者经常搞混视图dba_objects中object_id和data_object_id,一般建立表两者是相等的,容易混淆.建立分区表的话,表仅仅有object_id,没有data_object_id,分区表才有data_object_id. 实际上有些对象比如view,sequence等仅仅有objec

[20121128]传输表空间与scn.txt

[20121128]传输表空间与scn.txt 今天测试如果使用传输表空间,数据文件的scn的变化.如果传输表空间的数据文件scn很大,传过来后数据库的scn是否同步到最大的情况. 测试机器A(10g) SQL> select * from v$version where rownum BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edi

[20140428]建立表空间的bug?.txt

[20140428]建立表空间的bug?.txt http://teymur-hajiyev.blogspot.com/2014/04/never-hurry-up-for-telling-it-is-bug-in.html SYS@test> @ver BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edi

更改Oracle数据库表的表空间

oracle|数据|数据库 在Oracle数据库管理系统中,创建库表(table)时要分配一个表空间(tablespace),如果未指定表空间,则使用系统用户确省的表空间. 在Oracle实际应用中,我们可能会遇到这样的问题.处于性能或者其他方面的考虑,需要改变某个表或者是某个用户的所有表的表空间.通常的做法就是首先将表删除,然后重新建表,在新建表时将表空间指定到我们需要改变的表空间.如果该用户已经保存了大量数据,这种办法就就显得不是很方便,因为有大量数据需要提前备份出来.下面介绍一种利用数据库

如何查看用户表所占空间的大小

如何查看用户表所占空间的大小? 说明:  SQL> col SEGMENT_NAME format a20 SQL> col TABLESPACE_NAME format a20 SQL> select segment_name,tablespace_name,bytes,blocks from user_segments where segment_type='TABLE';