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 Export the table. Recreate the table in the new tablespace. 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表空间的碎片情况进行对比： ？如何做？

二、 实验二

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/