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之前大？

除此之外，還應該檢查新表空間DA他的碎片情況，和之前在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/，如需轉載，請註明出處，否則將追究法律責任。

ORACLE實驗(move表空間和database link)

相關文章