Oracle实验(move表空间和database link)
一、 实验一
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
1. Export the table.
2. Recreate the table in the new tablespace.
3. Import the table.
对于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表空间的碎片情况进行对比：
？如何做？