OCP043第十一讲 Managing Storage
本讲内容主要包括：
1.段收缩和表空间使用量监控
2.进行段收缩，回退高水位线
3.由于空间问题，设置会话暂停
4.传输表空间
一：段收缩，段收缩分row chaining和row migrate
1.模拟row chaining 行链接情况
当第一次插入行时，由于行太长而不能容纳在一个数据块中时，就会发生行链接。在这种情况下，oracle会使用与该块链接的一块或多块数据块来容纳该行的数据。行连接经常在插入比较大的行时才会发生，如包含long, long row, lob等类型的数据。在这些情况下行链接是不可避免的。
[root@rhel6 ~]# su - oracle
[oracle@rhel6 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 3 09:28:11 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> @?/rdbms/admin/utlchain.sql
Table created
SQL> create table hr.tocp11_long(a varchar2(2000), b varchar2(2000), c varchar2(2000),
2 d varchar2(2000), e varchar2(2000),f varchar2(2000) ,g varchar2(2000), h varchar2(2000),
3 i varchar2(2000), j varchar2(2000) );
Table created.
SQL> insert into tocp11_long(a) values('此处为2000个A');
1 row created.
SQL> update tocp11_long set b=('此处为2000个A')；
1 row created.
SQL> update tocp11_long set c=('此处为2000个A')；
1 row created.
SQL> commit;
Commit complete.
SQL> analyze table hr.tocp11_long list chained rows;
Table analyzed.
SQL> select * from chained_rows;
no rows selected
SQL> update tocp11_long set d=('此处为2000个A')；
1 row created.
SQL> commit;
Commit complete.
SQL> analyze table hr.tocp11_long list chained rows;
Table analyzed
SQL> select owner_name,table_name,head_rowid from chained_rows;
OWNER_NAME TABLE_NAME HEAD_ROWID
---------- --------------- ------------------
HR TOCP11_LONG AAAQxUAAEAAAHL9AAA
2.模拟row migrate行迁移情况
当修改不是行链接的行时，当修改后的行长度大于修改前的行长度，并且该数据块中的空闲空间已经比较小而不能完全容纳该行的数据时，就会发生行迁移。在这种情况下，Oracle会将整行的数据迁移到一个新的数据块上，而将该行原先的空间只放一个指针，指向该行的新的位置，并且该行原先空间的剩余空间不再被数据库使用。
SQL> create table hr.t04311_migrate (a varchar2(2000)) pctfree 0;
Table created.
SQL> begin
2 for i in 1..2000
3 loop
4 insert into hr.t04311_migrate values ('A');
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> update hr.t04311_migrate set a='AAAAAAAAAAAAAAAAAAAAAAAAA';
2000 rows updated.
SQL> commit;
Commit complete.
SQL> analyze table hr.t04311_migrate list chained rows;
Table analyzed.
SQL> select owner_name,table_name,head_rowid from chained_rows where rownum <= 5;
OWNER_NAME TABLE_NAME HEAD_ROWID
---------- --------------- ------------------
HR TOCP11_LONG AAAQxUAAEAAAHL9AAA
HR T04311_MIGRATE AAAQxWAAEAAAHMFAAA
HR T04311_MIGRATE AAAQxWAAEAAAHMFAAB
HR T04311_MIGRATE AAAQxWAAEAAAHMFAAC
HR T04311_MIGRATE AAAQxWAAEAAAHMFAAD
3.使用dbconsole监控表空间的使用量
二.进行段收缩，回退高水位线，进行段收缩后，将无法进行闪回，同时必须开启行移动，对于非ASSM管理的表空间，无法进行段收缩
SQL> create table hr.t04311_big as select * from dba_source;
Table created.
SQL> select count(*) from dba_extents e where e.owner='HR' and e.segment_name='T04311_BIG';
COUNT(*)
----------
67
SQL> delete from hr.t04311_big;
323191 rows deleted.
SQL> select count(*) from dba_extents e where e.owner='HR' and e.segment_name='T04311_BIG';
COUNT(*)
----------
67
SQL> alter table hr.t04311_big enable row movement;
Table altered.
SQL> alter table hr.t04311_big shrink space compact;
Table altered.
SQL> select count(*) from dba_extents e where e.owner='HR' and e.segment_name='T04311_BIG';
COUNT(*)
----------
67
SQL> alter table hr.t04311_big shrink space;
Table altered.
SQL> select count(*) from dba_extents e where e.owner='HR' and e.segment_name='T04311_BIG';
COUNT(*)
----------
1
如果有索引，在收缩段的时候可以加上cascade关键字，或者使用下面的命令来单独收缩，重建索引
SQL> alter index hr.EMP_LAST_NAME_IDX coalesce;
Index altered
SQL> alter index hr.EMP_LAST_NAME_IDX rebuild online;
Index altered.
三：由于空间问题，设置会话暂停，默认超时时间为7200秒
SQL> revoke unlimited tablespace from hr;
Revoke succeeded
SQL> grant resumable to hr;
Grant succeeded.
SQL> create table t100 as select * from departments;
create table t100 as select * from departments *
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'USERS'
SQL> alter session enable resumable timeout 5;
Session altered.
SQL> create table t100 as select * from departments;
create table t100 as select * from departments
*
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01536: space quota exceeded for tablespace 'USERS'
SQL> select status,sql_text from dba_resumable;
STATUS SQL_TEXT
---------- ------------------------------
SUSPENDED create table t100 as select *
from departments
利用触发器来自动处理由于空间问题导致的会话挂起
SQL> create tablespace tbs04311_small datafile '/u01/app/oradata/ora10g/tbs04311_small01.dbf' size 512k autoextend off;
Tablespace created.
SQL> grant unlimited tablespace to hr;
Grant succeeded.
SQL> conn hr/hr
Connected.
SQL> create table t04311_t( a number ) tablespace tbs04311_small storage ( initial 1M);
create table t04311_t( a number ) tablespace tbs04311_small storage ( initial 1M)
*
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01659: unable to allocate MINEXTENTS beyond 7 in tablespace TBS04311_SMALL
SQL> conn /as sysdba
SQL> CREATE OR REPLACE TRIGGER SYS.TRG_SUSPEND
2 AFTER SUSPEND
3 ON DATABASE
4 declare
5 v_size number;
6 pragma AUTONOMOUS_TRANSACTION;
7 begin
8 select bytes into v_size from dba_data_files where file_name='/u01/app/oradata/ora10g/tbs04311_small01.dbf';
9 v_size := v_size + 1048576;
10 execute immediate 'alter database datafile ''/u01/app/oradata/ora10g/tbs04311_small01.dbf'' resize '||v_size;
11 commit;
12* end;
SQL> /
Trigger created.
SQL> select * from user_errors;
no rows selected
SQL> select bytes from dba_data_files where tablespace_name=upper('tbs04311_small');
BYTES
----------
524288
SQL> conn hr/hr
Connected.
SQL> alter session enable resumable;
Session altered.
SQL> create table t04311_t( a number ) tablespace tbs04311_small storage ( initial 1M);
Table created.
SQL> select bytes from dba_data_files where tablespace_name=upper('tbs04311_small');
BYTES
----------
1572864
四.传输表空间
可以跨平台，跨越indian format，但要求字符集必须一致，COMPATIBLE参数必须大于10.0.0，传输之前需要将表空间置于只读状态
源服务器端：
C:\>set ORACLE_SID=winorcl2
C:\>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三8月3 13:23:27 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
已连接。
SQL> select property_value from database_properties where property_name='NLS_CHARACTERSET';
PROPERTY_VALUE
---------------
AL32UTF8
SQL> create tablespace tbs_transport datafile 'E:\oracle\product\10.2.0\oradata\winorcl2\tbs_transport01.dbf' size 10M;
表空间已创建。
SQL> create table hr.t04311_trans (a number) tablespace tbs_transport;
表已创建。
SQL> insert into hr.t04311_trans values (1000);
已创建1 行。
SQL> commit;
提交完成。
SQL> create directory dir01 as 'e:\data';
目录已创建。
SQL> alter tablespace tbs_transport read only;
表空间已更改。
C:\>expdp 'sys/123456 as sysdba' directory=dir01 dumpfile=1.dmp transport_tablespaces=tbs_transport
Export: Release 10.2.0.1.0 - Production on 星期三, 03 8月, 2011 13:39:08
Copyright (c) 2003, 2005, Oracle. All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
启动"SYS"."SYS_EXPORT_TRANSPORTABLE_01": 'sys/******** AS SYSDBA' didirectory=di
r01 dumpfile=1.dmp transport_tablespaces=tbs_transport
处理对象类型TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型TRANSPORTABLE_EXPORT/TABLE
处理对象类型TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
已成功加载/卸载了主表"SYS"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYS.SYS_EXPORT_TRANSPORTABLE_01 的转储文件集为:
E:\DATA\1.DMP
作业"SYS"."SYS_EXPORT_TRANSPORTABLE_01" 已于13:40:13 成功完成
C:\>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三8月3 13:42:02 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
已连接。
SQL> alter tablespace tbs_transport read write;
表空间已更改。
目的端：
[root@rhel6 ~]# mount -t cifs -o username=yang //192.168.50.195/data /mnt
Password:
[root@rhel6 ~]# ls /mnt
1.DMP export.log TBS_TRANSPORT01.DBF
[oracle@rhel6 ~]$ cp /mnt/* /home/oracle/data/
[oracle@rhel6 ~]$ ls /home/oracle/data/
1.dmp export.log tbs_transport01.dbf
[oracle@rhel6 ~]$ cp /home/oracle/data/tbs_transport01.dbf /u01/app/oradata/ora10g/
[oracle@rhel6 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 3 13:50:59 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> create directory dir01 as '/home/oracle/data';
Directory created.
[oracle@rhel6 data]$ impdp \'sys/123456 as sysdba\' directory=dir01 dumpfile=1.dmp transport_datafiles='/u01/app/oradata/ora10g/tbs_transport01.dbf'
Import: Release 10.2.0.1.0 - 64bit Production on Wednesday, 03 August, 2011 14:16:46
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": 'sys/******** AS SYSDBA' directory=dir01 dumpfile=1.dmp transport_datafiles=/u01/app/oradata/ora10g/tbs_transport01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 14:16:54
SQL> select file_name from dba_data_files where tablespace_name='TBS_TRANSPORT';
FILE_NAME
-------------------------------------------
/u01/app/oradata/ora10g/tbs_transport01.dbf
SQL> select * from hr.t04311_trans;
A
----------
1000
本文出自 “月牙天冲” 博客
相关新闻>>
- 发表评论
-
- 最新评论 更多>>
