你的dmp文件导出时若只能以指定tables的方式。
那么其它的数据对象你必须要另外想方法创建。
比如对于
sequence
你可以先建立一个临时表,把sequence的各参数都保存下来
CREATE TABLE SEQUENCES_TMP(
SEQUENCE_NAME VARCHAR2(30) NOT NULL,
MIN_VALUE NUMBER ,
MAX_VALUE NUMBER ,
INCREMENT_BY NUMBER NOT NULL,
CYCLE_FLAG VARCHAR2(1) ,
ORDER_FLAG VARCHAR2(1) ,
CACHE_SIZE NUMBER NOT NULL,
LAST_NUMBER NUMBER NOT NULL);insert into SEQUENCES_TMP(SEQUENCE_NAME,MIN_VALUE,
MAX_VALUE,INCREMENT_BY,CYCLE_FLAG,ORDER_FLAG,
CACHE_SIZE,LAST_NUMBER)select SEQUENCE_NAME,
MIN_VALUE,MAX_VALUE,INCREMENT_BY,CYCLE_FLAG,
ORDER_FLAG,CACHE_SIZE,LAST_NUMBER from USER_SEQUENCES
/然后从表中恢复
--以下必须9.0.1以上版本oracle才可使用。8.1.7以下版本需要改写EXECUTE IMMEDIATE sSql;
CREATE OR REPLACE PROCEDURE PRestoreSequences
AS
--用于从临时表中恢复sequence
sSql VARCHAR2(400);
nExist NUMBER;
TYPE REF_CURSOR IS REF CURSOR;
cur_create_seq REF_CURSOR;
CURSOR cur_drop_seq is
select ' drop '||object_type ||' '||'"'||object_name||'"'
from obj where object_type='SEQUENCE';
BEGIN
nExist := 0;
SELECT COUNT(*) INTO nExist FROM OBJ WHERE OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = 'SEQUENCES_TMP';
IF nExist>0 THEN
--删除原来的sequence
OPEN cur_drop_seq;
LOOP
sSql := '';
FETCH cur_drop_seq INTO sSql;
EXIT WHEN cur_drop_seq%NOTFOUND;
EXECUTE IMMEDIATE sSql;
END LOOP;
CLOSE cur_drop_seq;
--根据SEQUENCES_TMP中保留的参数重建sequence
sSql := 'select ''create sequence ''||sequence_name||'||
''' increment by ''||increment_by||'||
''' start with ''||last_number||'||
''' maxvalue ''||max_value||'||
'decode(upper(cycle_flag),''Y'','' cycle '',''N'','' nocycle '','' '')||'||
'decode(nvl(cache_size,0),0,'' nocache '','' cache ''||nvl(cache_size,0))||'||
'decode(upper(order_flag),''Y'','' order '',''N'','' noorder '','' '')'||
'from SEQUENCES_TMP';
OPEN cur_create_seq for sSql;
LOOP
sSql := '';
FETCH cur_create_seq INTO sSql;
EXIT WHEN cur_create_seq%NOTFOUND;
EXECUTE IMMEDIATE sSql;
END LOOP;
CLOSE cur_create_seq;
--删除SEQUENCES_TMP临时表
sSql := 'drop table SEQUENCES_TMP';
EXECUTE IMMEDIATE sSql;
END IF;
END PRestoreSequences;对于View,Index等可以用类似方法。
那么其它的数据对象你必须要另外想方法创建。
比如对于
sequence
你可以先建立一个临时表,把sequence的各参数都保存下来
CREATE TABLE SEQUENCES_TMP(
SEQUENCE_NAME VARCHAR2(30) NOT NULL,
MIN_VALUE NUMBER ,
MAX_VALUE NUMBER ,
INCREMENT_BY NUMBER NOT NULL,
CYCLE_FLAG VARCHAR2(1) ,
ORDER_FLAG VARCHAR2(1) ,
CACHE_SIZE NUMBER NOT NULL,
LAST_NUMBER NUMBER NOT NULL);insert into SEQUENCES_TMP(SEQUENCE_NAME,MIN_VALUE,
MAX_VALUE,INCREMENT_BY,CYCLE_FLAG,ORDER_FLAG,
CACHE_SIZE,LAST_NUMBER)select SEQUENCE_NAME,
MIN_VALUE,MAX_VALUE,INCREMENT_BY,CYCLE_FLAG,
ORDER_FLAG,CACHE_SIZE,LAST_NUMBER from USER_SEQUENCES
/然后从表中恢复
--以下必须9.0.1以上版本oracle才可使用。8.1.7以下版本需要改写EXECUTE IMMEDIATE sSql;
CREATE OR REPLACE PROCEDURE PRestoreSequences
AS
--用于从临时表中恢复sequence
sSql VARCHAR2(400);
nExist NUMBER;
TYPE REF_CURSOR IS REF CURSOR;
cur_create_seq REF_CURSOR;
CURSOR cur_drop_seq is
select ' drop '||object_type ||' '||'"'||object_name||'"'
from obj where object_type='SEQUENCE';
BEGIN
nExist := 0;
SELECT COUNT(*) INTO nExist FROM OBJ WHERE OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = 'SEQUENCES_TMP';
IF nExist>0 THEN
--删除原来的sequence
OPEN cur_drop_seq;
LOOP
sSql := '';
FETCH cur_drop_seq INTO sSql;
EXIT WHEN cur_drop_seq%NOTFOUND;
EXECUTE IMMEDIATE sSql;
END LOOP;
CLOSE cur_drop_seq;
--根据SEQUENCES_TMP中保留的参数重建sequence
sSql := 'select ''create sequence ''||sequence_name||'||
''' increment by ''||increment_by||'||
''' start with ''||last_number||'||
''' maxvalue ''||max_value||'||
'decode(upper(cycle_flag),''Y'','' cycle '',''N'','' nocycle '','' '')||'||
'decode(nvl(cache_size,0),0,'' nocache '','' cache ''||nvl(cache_size,0))||'||
'decode(upper(order_flag),''Y'','' order '',''N'','' noorder '','' '')'||
'from SEQUENCES_TMP';
OPEN cur_create_seq for sSql;
LOOP
sSql := '';
FETCH cur_create_seq INTO sSql;
EXIT WHEN cur_create_seq%NOTFOUND;
EXECUTE IMMEDIATE sSql;
END LOOP;
CLOSE cur_create_seq;
--删除SEQUENCES_TMP临时表
sSql := 'drop table SEQUENCES_TMP';
EXECUTE IMMEDIATE sSql;
END IF;
END PRestoreSequences;对于View,Index等可以用类似方法。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货