Oracle中提取和存儲(chǔ)數(shù)據(jù)庫(kù)對(duì)象的DDL

2024-08-29 13:29:00

字體：大中小

供稿：網(wǎng)友

注冊(cè)會(huì)員，創(chuàng)建你的web開發(fā)資料庫(kù)，從對(duì)象（例如數(shù)據(jù)庫(kù)表、索引、約束、觸發(fā)器等）中提取ddl命令的普通方法涉及到的操作包括從這些對(duì)象中提取元數(shù)據(jù)（metadata），并把這些數(shù)據(jù)存儲(chǔ)在內(nèi)存中。盡管目前有很多腳本可以實(shí)現(xiàn)這樣的功能，但是它們通常都是不完整的或者過時(shí)的。幸運(yùn)的是，oracle 9.2提供了一個(gè)實(shí)現(xiàn)這樣的功能的api：dbms_metadata程序包。

　　在很多情況下，數(shù)據(jù)庫(kù)中數(shù)據(jù)的維護(hù)操作要求我們提取多種對(duì)象（例如數(shù)據(jù)庫(kù)表、索引、約束、觸發(fā)器等）的ddl（data definition language，數(shù)據(jù)定義語(yǔ)言）。

　　最近我承擔(dān)了一個(gè)任務(wù)，我需要編寫一組數(shù)據(jù)庫(kù)程序包來執(zhí)行高性能的大量的數(shù)據(jù)刪除（delete）操作。這樣的操作要求我擁有提取和存儲(chǔ)數(shù)據(jù)庫(kù)對(duì)象ddl的相關(guān)技術(shù)。

　　提取和存儲(chǔ)數(shù)據(jù)庫(kù)對(duì)象的ddl的方法如下：

　　· 建立與源表結(jié)構(gòu)相同的數(shù)據(jù)表，但是它不帶主鍵、備用鍵和外部鍵約束。

　　· 例如，使用mytable_x，其中mytable是要被刪除的目標(biāo)數(shù)據(jù)表。

　　· 把需要保存的數(shù)據(jù)插入新建立的數(shù)據(jù)表（mytable_x）中。

　　· 使用nologging parallel選項(xiàng)在新數(shù)據(jù)表上建立索引。

　　· 在新數(shù)據(jù)表上建立約束。

　　· mytable和mytable_x數(shù)據(jù)表進(jìn)行交換。把主表改名為mytable_t，把mytable_x改名為mytable。

　　· 驗(yàn)證結(jié)果并刪除mytable_t表。

　　很明顯，為了編寫實(shí)現(xiàn)上面目標(biāo)的代碼，你必須提取數(shù)據(jù)庫(kù)對(duì)象的元數(shù)據(jù)（定義和被選中的屬性），并把它存儲(chǔ)在內(nèi)存中，這樣在執(zhí)行上面的操作的時(shí)候才能夠使用它。

　　在網(wǎng)上存在大量的腳本，它們可以從多種oracle數(shù)據(jù)字典（user_tables、user_indexes、user_ind_columns、user_constraints、user_cons_columns等）中提取數(shù)據(jù)庫(kù)對(duì)象的元數(shù)據(jù)，接著為特定的對(duì)象構(gòu)造ddl命令。這些腳本的一個(gè)問題是，它們通常是sql*plus腳本，它會(huì)生成客戶端文本文件，而這個(gè)文件不能被服務(wù)器端代碼訪問。它們的主要問題有：

　　· 不完整：不能提取所有的選項(xiàng)，并組合進(jìn)ddl語(yǔ)句中。

　　· 過時(shí)了：這些腳本通常不支持oracle最新的數(shù)據(jù)庫(kù)特性--分區(qū)（partitioning）、基于函數(shù)的索引、自動(dòng)段空間管理（assm）等。這些腳本可能崩潰或生成錯(cuò)誤的ddl語(yǔ)句。

　　問題總結(jié)：盡管有大量的從oracle數(shù)據(jù)字典中提取數(shù)據(jù)庫(kù)對(duì)象元數(shù)據(jù)的腳本，但是它們中的大多數(shù)要么不完整，要么過期了。

　　解決方案：使用dbms_metadata程序包，學(xué)習(xí)如何用最佳的、沒有錯(cuò)誤的和易于維護(hù)的方式執(zhí)行上面的事務(wù)。

　　使用oracle的本地api：dbms_metadata程序包

　　oracle數(shù)據(jù)庫(kù)采用補(bǔ)充pl/sql程序包的形式提供了豐富的預(yù)先包裝好的api。oracle 9.2版本中引入的dbms_metadata程序包可能正好適合你的需求。它包含了用于檢索數(shù)據(jù)庫(kù)對(duì)象定義的api。

　　我們將使用的api主要是dbms_metadata.get_ddl函數(shù)。這個(gè)函數(shù)返回的對(duì)象定義sql字符串是clob。它擁有下面一些輸入?yún)?shù)：

　　· object_type varchar2

　　· name varchar2

　　· schema varchar2 default null

　　· version varchar2 default ’compatible’

　　· model varchar2 default ’oracle’,

　　· transform varchar2 default ’ddl’

　　下面建立了一個(gè)用于測(cè)試的emptest數(shù)據(jù)表，它帶有索引和約束：

create table emptest
(
empno integer not null,
lastname varchar2(30) not null,
firstname varchar2(20) not null,
job varchar2(9) ’
hiredate date ’
isactive number(1)
constraint emptest_ck1
check (isactive in (0,1)) ,
salary number(9,2) ,
commision number(9,2) ,
deptno number(2) ,
constraint emptest_pk
primary key (empno),
constraint emptest_ak1
unique (lastname, firstname)
);

create index emptest_hiredate_salary
on emptest
(
salary,
hiredate
);
　　運(yùn)行上面的腳本之后，就建立了一個(gè)帶有三個(gè)索引（兩個(gè)唯一的和一個(gè)不唯一的索引）的emptest表：

select index_name, index_type, uniqueness
from user_indexes
where table_name = ’emptest’;
索引名稱索引類型唯一性emptest_ak1normaluniqueemptest_hiredate_salarynormalnonuniqueemptest_pknormalunique
　　emptest表還包括六個(gè)約束：

　　· 一個(gè)主鍵-emptest_pk

　　· 一個(gè)備用鍵-emptest_ak

　　· 一個(gè)檢查約束-emptest_ck1

　　· 系統(tǒng)生成的（sys_*）三個(gè)非空的約束，名稱如下：

約束名稱約束類型索引名稱sys_c002144065c sys_c002144066c sys_c002144067c emptest_ck1c emptest_pkp emptest_pkemptest_ak1u emptest_ak1
　　現(xiàn)在我們執(zhí)行匿名的pl/sql代碼塊來調(diào)用dbms_metadata.get_ddl函數(shù)，檢索數(shù)據(jù)表的定義。

　　dbms_output程序包只能輸出最長(zhǎng)為255個(gè)字符的字符串，由于在處理數(shù)據(jù)表的ddl字符串的時(shí)候太容易超過這個(gè)限制，所以這是一個(gè)問題。為了解決這個(gè)問題，我們使用了本地過程show()（列表1所示）。

　　列表1：調(diào)用dbms_metadata.get_ddl()函數(shù)的pl/sql代碼塊

declare
vclob clob;
vlongstring varchar2(32767);
voffset pls_integer := 0;
vlength pls_integer := 0;
vtable varchar2(30) := ’emptest’;

procedure show (pvariable varchar2, plinesize pls_integer := 80)
is
begin
dbms_output.enable(1000000);
if (length(pvariable) > plinesize)
then
dbms_output.put_line(substr(pvariable, 1, plinesize));
show(substr(pvariable, plinesize + 1), plinesize);
else
dbms_output.put_line(pvariable);
end if;
end show;
begin
-- 獲取 ddl
vclob := dbms_metadata.get_ddl(’table’, upper(vtable));

-- 獲取 clob 長(zhǎng)度
vlength := dbms_lob.getlength(vclob);
dbms_output.put_line(’ddl length: ’ || to_char(vlength));

voffset := 1;
dbms_lob.read(vclob, vlength, voffset, vlongstring);
-- 關(guān)閉 clob
if (dbms_lob.isopen(vclob) > 0)
then
dbms_lob.close(vclob);
end if;
show(vlongstring, 80);
end;
　　列表1生成下面的輸出信息：

ddl length: 461
create table "boris"."emptest"
( "empno" number(*,0) not null enable,
"lastname" varchar2(30) not null enable,
"firstname" varchar2(20) not null enable,
"job" varchar2(9),
"hiredate" date,
"isactive" number(1,0),
"salary" number(9,2),
"commision" number(9,2),
"deptno" number(2,0),
constraint "emptest_ck1" check (isactive in (0,1)) enable,
constraint "emptest_pk" primary key ("empno")
using index pctfree 10 initrans 2 maxtrans 255
storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645
pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)
tablespace "tools" enable, constraint "emptest_ak1" unique ("lastname", "firstname")
using index pctfree 10 initrans 2 maxtrans 255
storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645
pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)
tablespace "tools" enable) pctfree 10 pctused 40 initrans 1
maxtrans 255 nocompress logging
storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645
pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)
tablespace "tools"

　　它運(yùn)行的情況太好了，返回的數(shù)據(jù)表的ddl字符串帶有主鍵emptest_pk、備用鍵emptest_ak1和檢查約束emptest_ck1。它建立了兩個(gè)唯一的索引來支持主鍵和備用鍵約束。這不是你需要的結(jié)果：你需要一個(gè)表，但是為了加快數(shù)據(jù)載入速度，它不要包含約束和索引。只有在數(shù)據(jù)載入工作完成以后，你才建立索引和約束。

　　保證對(duì)象的定義獨(dú)立的另外一個(gè)原因在于靈活性：你可能需要改變對(duì)象建立的次序。

　　現(xiàn)在可以設(shè)計(jì)一個(gè)數(shù)據(jù)結(jié)構(gòu)來存儲(chǔ)對(duì)象的元數(shù)據(jù)了。

上一篇：Oracle數(shù)據(jù)庫(kù)密碼文件的使用與維護(hù)

下一篇：Oracle數(shù)據(jù)庫(kù)的備份及恢復(fù)策略研究