一、 实验一
a) 实验要求:
i. 将vip用户下的表 move到DATA表空间中,达到整理表的碎片的目的;--我认为应该是检查某个或某些表空间的使用情况,来针对表空间的表进行碎片整理,而不应该是针对用户的,除非该用户下的表都在同一个表空间里,且该表空间存在大量碎片,否则命题不太清晰。
ii. 观察move以后,表会发生哪些变化;以及对相关的查询有何影响;
b) 实验分析:
i. 目的分析:通过move表达到整理表的碎片的目的。因为数据库中的表经历了多次的DML操作后,会在表空间里产生很多碎片,大大影响存储效率和查询效率。
ii. 技术分析:为了消除碎片可以采用移动表的方法解决;就是一个表空间的所有表移动到一个干净的表空间中,由于对表进行了移动,在表上创建的索引将失效,所以在移动表的同时也要将表的索引重建。不对临时表进行移动。
iii. 场景分析:不管是OLTP还是OLAP系统,其中频繁做DML操作的表都会产生很多碎片,OLTP的特点是每次DML的记录条数较少,但是很频繁,OLAP的特点是每次都是批量DML操作,涉及记录条数很多,尤其是在ETL的过程中,但是操作很集中。
c) 实验过程:
i. 备份:将vip用户用数据泵备份出来
expdp vip/vip@PROD directory=dir1 dumpfile=expdp_vip schemas=vip |
ii. 检查碎片:
如何检查? VIP@PROD>col table_name for a12; VIP@PROD>col tablespace_name for a6; VIP@PROD>select table_name,tablespace_name,status,num_rows,blocks,empty_blocks from user_tables where table_name='COUNTRIES2'; TABLE_NAME TABL STATUS NUM_ROWS BLOCKS EMPTY_BLOCKS ------------ ---- -------- ---------- ---------- ------------ COUNTRIES2 SYSTEM VALID 19 4 0 分析碎片的方法:??还不会 参考文档: |
iii. 收集信息:
1. 查看当前库中有哪些表空间,如果没有要求的DATA表空间,则需要新建
SYS@PROD>select tablespace_name, status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMPTS1 ONLINE TUNING_TBS ONLINE |
2. 查看需要move表的用户下的所有对象及其所在表空间
select segment_name, partition_name, segment_type, tablespace_name, trim(bytes/1024/1024) M from user_segments; |
发现segment_type包括”TABLE”、”TABLE PARTITION”、”INDEX”三大类段对象,需要分别进行处理。结果请参考
3. 统计segment大小,以确定新建表空间的指定大小等参数
VIP@PROD>select sum(bytes)/1024/1024 from user_segments; SUM(BYTES)/1024/1024 -------------------- 98.375 |
4. 查看磁盘空间大小
SYS@PROD>host df -h 文件系统 容量 已用 可用 已用% 挂载点 /dev/mapper/VolGroup00-LogVol00 26G 15G 9.7G 60% / /dev/sda1 99M 23M 72M 24% /boot tmpfs 1007M 400M 607M 40% /dev/shm /dev/sr0 3.1G 3.1G 0 100% /media/OL5.8 i386 dvd 20120229 |
5. 检查索引状态
select index_name,table_name,tablespace_name,status from user_indexes; |
结果参考
iv. 执行:
1. 新建表空间DATA
drop tablespace DATA including contents and datafiles; create tablespace DATA datafile '/u01/app/oracle/oradata/PROD/disk1/DATA01.dbf' size 150M autoextend on next 1m maxsize 1g extent management local segment space management auto; |
2. 移动表
conn / as sysdba Alter user vip QUOTA unlimited ON DATA; |
conn vip/vip alter table 表名 move tablespace DATA; -- segment_type=’TABLE’ alter table 分区表表名 move partition 分区名 tablespace DATA;--segment_type=’TABLE PARTITION’ 脚本 |
3. 移动后检查索引状态
select index_name,table_name,tablespace_name,status from user_indexes; --均显示为INVALID |
4. 重建索引
alter index 索引名rebuild tablespace DATA[online]; |
5. 重建后检查索引状态
select index_name,table_name,tablespace_name,status from user_indexes; --VALID |
v. 错误处理
重新执行下面的语句发现有两个segment的表空间还是system没有改为DATA,检查日志发现有两处报错:
select segment_name, partition_name, segment_type, tablespace_name, trim(bytes/1024/1024) M from user_segments; |
1. alter index COUNTRY_C_ID_PK rebuild tablespace DATA
*
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt
解决:IOT表上的Primary index不能rebuild,只能作如下操作:
VIP@PROD>select index_name,table_name,status,tablespace_name from user_indexes where index_name='COUNTRY_C_ID_PK'; INDEX_NAME TABLE_NAME STATUS TABLESPACE_NAME ------------------------------ ------------------------------ -------- ------------------------------ COUNTRY_C_ID_PK COUNTRIES VALID SYSTEM VIP@PROD>select table_name,tablespace_name,iot_name,status from user_tables where table_name='COUNTRIES'; TABLE_NAME TABLESPACE_NAME IOT_NAME STATUS ------------------------------ ------------------------------ ------------------------------ -------- COUNTRIES VALID --对于IOT而言,只有索引段,没有数据段,也就不存在move到另一个表空间的说法。 常用的rebuild操作不能使用在IOT主键索引中,而且disable索引也没有办法实现。整理IOT的方法,可以选择数据表的move方法。 VIP@PROD>alter table COUNTRIES move; Table altered. 效果是可以将存在大量死叶子节点的IOT表的索引高水位线下降。参考文章: |
2. alter table CUSTOMER move tablespace DATA
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
查看一下CUSTOMER表的结构:
VIP@PROD>desc customer
Name Null? Type
--------------------- -------- ------------
CUSTOMER_ID NOT NULL NUMBER(6)
NAME VARCHAR2(45)
ADDRESS VARCHAR2(40)
CITY VARCHAR2(30)
STATE VARCHAR2(2)
ZIP_CODE VARCHAR2(9)
AREA_CODE NUMBER(3)
PHONE_NUMBER NUMBER(7)
SALESPERSON_ID NUMBER(4)
CREDIT_LIMIT NUMBER(9,2)
COMMENTS LONG
问题可能就出在最后一个字段:COMMENTS LONG
解决:
If you get an "ORA-00997: illegal use of LONG datatype" error, metalink (note 165901.1) advises you to
对于Oracle来说,LONGs are deprecated since 8.0 ,建议将LONG/LONG RAW类型改为BLOB/CLOB等。 |
vi. move以后,表会发生哪些变化;以及对相关的查询有何影响
VIP@PROD>select sum(bytes)/1024/1024 from user_segments; SUM(BYTES)/1024/1024 -------------------- 99.6875—为什么会比move之前大? 除此之外,还应该检查新表空间DATA的碎片情况,和之前在SYSTEM表空间时,SYSTEM表空间的碎片情况进行对比: ?如何做? |
二、 实验二
a) 实验要求:服务器一上的数据库PROD,服务器二上的数据库PROD2。在PROD2库上创建用户testuser,使其通过database link访问PROD库中VIP用户的表。
b) 实验分析:涉及到的步骤包括,创建用户、授权、创建database link、测试和删除database link
c) 实验执行:
i. 在PROD2上创建表空间:
create tablespace test_tbs datafile '/u01/app/oracle/oradata/PROD/disk1/test_tbs01.dbf' size 10m; |
ii. 在PROD2上创建用户:
create user testuser identified by testuser; alter user testuser default tablespace test_tbs quota unlimited on test_tbs; |
iii. 在PROD2所在服务器上创建PROD库的连接串
vi /oracle/product/10.2.0/db_1/network/admin/tnsnames.ora 添加下面一段: PROD_oelr5u8-1= (description= (address=(protocol=tcp)(host=oelr5u8-1)(port=1521)) (connect_data= (server=dedicated) (service_name=PROD) ) ) 切换至root vi /etc/hosts 添加一行:192.168.182.131 oelr5u8-1 sqlplus下执行测试 sqlplus sys/oracle@ PROD_oelr5u8-1 as sysdba 连接进入成功 |
iv. 在PROD2上创建database link并测试
sys@PROD2> create public database link dblink_pub1 connect to "vip" identified by "vip" using 'PROD_oelr5u8-1'; 或 sys@PROD2> create public database link dblink_pub2 connect to "vip" identified by "vip" using ' (description= (address=(protocol=tcp)(host=oelr5u8-1)(port=1521)) (connect_data= (server=dedicated) (service_name=PROD) )'; --注意,两处加双引号分别是username/password,因database link所在数据库是10g,目标库是11g,不加双引号,用户名和密码会被变为大写,导致使用dblink查询时报错:ORA-01017: invalid username/password; logon denied。如果dblink所在库是11g,目标库是10g,则可以不加双引号 |
SYS@PROD2>grant create database link to testuser; Grant succeeded. SYS@PROD2>conn testuser/testuser Connected. TESTUSER@PROD2>create database link dblink_pri1 connect to "vip" identified by "vip" using 'PROD_oelr5u8-1'; Database link created. TESTUSER@PROD2>select * from countries2@dblink_pri1; CO COUNTRY_NAME COUNTRY_SUBREGION -- ---------------------------------------- ------------------------------ COUNTRY_REGION -------------------- US United States of America Northern America Americas DE Germany Western Europe |
v. 删除PROD2上的database link
drop public database link dblink_pub1; drop database link dblink_pri1; |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26521853/viewspace-1107425/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26521853/viewspace-1107425/