如何通过imp/exp重建oracle数据库的对列表_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 4007 | 回复: 0

主题： 如何通过imp/exp重建oracle数据库的对列表

匿名用户

发表于：

2011-12-27 15:52:55 | [全部帖] [楼主帖]

楼主

如何通过imp/exp重建oracle数据库的对列表

试用版版本8.1.7.0.0-9.2.0.8.0，这个文档中的信息适用于任何平台。

文档目的是提供一个脚本来降低高水位线，任何用户可以对高级队列（AQ）进行导入导出。

DBA在使用次脚本中，需要在书库中有关AQ的对象，以减少空间消耗，提高入队/出队的现象，并减少资源消耗队列的监控进程。

执行脚本的环境sqlplus或者isqlplus

配置脚本。在这个文件中包含两个脚本，generator.sql和run.sql第一个脚本是构建
前期的一些准备，而后运行run.sql执行生成的脚本。当然您可以修改这些脚本以保证这是
适合您的。

请打开一个新的sqlplus会话上正确运行generator.sql脚本，以正确生成脚本。

注:由于脚本生的都是截断操作，就其性质之言其破坏性是很大的，应在运行前先检查脚本，在一个适当的测试环境使用。

运行脚本

权限：需要sys或sysdba身份

用法：SQLPLUS“SYS <PASSWORD> / AS SYSDBA”@ generator.sql
SQLPLUS“SYS <PASSWORD> / AS SYSDBA”@ run.sql

注意：这个脚本只提供学习，而不是oracle支持的服务。它虽然已被内部测试，但是
我们保证一定合适您的测试环境，请使用之间校对脚本。

generator.sql
connect / as sysdba
ACCEPT user CHAR PROMPT 'Owner of Queue Tables to be rebuilt (in uppercase): '
SET heading off
SET feedback off
SET linesize 300
SET verify off
set pages 4444
spool unscheduleremote.sql
select 'exec dbms_aqadm.unschedule_propagation('''||SCHEMA||'.'||QNAME||''','''||DESTINATION||''');' RunIt
from dba_queue_schedules
where SCHEMA||QNAME||DESTINATION in
(select SCHEMA||QNAME||DESTINATION from dba_queue_schedules where
SCHEMA ='&user' and DESTINATION !='AQ$_LOCAL');
spool off
set pages 4444
spool unschedulelocal.sql
select 'exec dbms_aqadm.unschedule_propagation('''||SCHEMA||'.'||QNAME||''');' RunIt
from dba_queue_schedules
where SCHEMA||QNAME in
(select SCHEMA||QNAME from dba_queue_schedules where
SCHEMA ='&user' and DESTINATION ='AQ$_LOCAL');
spool off
set pages 4444
spool scheduleremote.sql
select 'exec dbms_aqadm.schedule_propagation('''||SCHEMA||'.'||QNAME||''','''||DESTINATION||''');' RunIt
from dba_queue_schedules
where SCHEMA||QNAME||DESTINATION in
(select SCHEMA||QNAME||DESTINATION from dba_queue_schedules where
SCHEMA ='&user' and DESTINATION !='AQ$_LOCAL');
spool off
set pages 4444
spool schedulelocal.sql
select 'exec dbms_aqadm.schedule_propagation('''||SCHEMA||'.'||QNAME||''');' RunIt
from dba_queue_schedules
where SCHEMA||QNAME in
(select SCHEMA||QNAME from dba_queue_schedules where
SCHEMA ='&user' and DESTINATION ='AQ$_LOCAL');
spool off
set pages 4444
spool stop.sql
select 'exec dbms_aqadm.stop_queue('''||OWNER||'.'||NAME||''');' RunIt
from dba_queues
where OWNER||NAME in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'QUEUE' and OWNER ='&user');
spool off
set pages 4444
spool exptables.dat
select 'LOG=export.log' from dual;
select 'FILE=&user' from dual;
select 'TABLES=(' from dual;
select ''||OWNER||'.'||QUEUE_TABLE||',' RunIt
from dba_queue_tables
where OWNER||QUEUE_TABLE in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'TABLE'and OWNER ='&user') and
RECIPIENTS='SINGLE';
select ''||OWNER||'.'||QUEUE_TABLE||',' RunIt
from dba_queue_tables
where OWNER||QUEUE_TABLE in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'TABLE'and OWNER ='&user') and
RECIPIENTS='MULTIPLE' and COMPATIBLE='8.1.3';
select ''||OWNER||'.AQ$_'||QUEUE_TABLE||'_I,' RunIt
from dba_queue_tables
where OWNER||QUEUE_TABLE in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'TABLE'and OWNER ='&user') and
RECIPIENTS='MULTIPLE' and COMPATIBLE='8.1.3';
select ''||OWNER||'.AQ$_'||QUEUE_TABLE||'_T,' RunIt
from dba_queue_tables
where OWNER||QUEUE_TABLE in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'TABLE'and OWNER ='&user') and
RECIPIENTS='MULTIPLE' and COMPATIBLE='8.1.3';
select ''||OWNER||'.AQ$_'||QUEUE_TABLE||'_H,' RunIt
from dba_queue_tables
where OWNER||QUEUE_TABLE in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'TABLE'and OWNER ='&user') and
RECIPIENTS='MULTIPLE' and COMPATIBLE='8.1.3';
select ''||OWNER||'.AQ$_'||QUEUE_TABLE||'_S,' RunIt
from dba_queue_tables
where OWNER||QUEUE_TABLE in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'TABLE'and OWNER ='&user') and
RECIPIENTS='MULTIPLE' and COMPATIBLE='8.1.3';
select ''||OWNER||'.AQ$_'||QUEUE_TABLE||'_NR,' RunIt
from dba_queue_tables
where OWNER||QUEUE_TABLE in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'TABLE'and OWNER ='&user') and
RECIPIENTS='MULTIPLE' and COMPATIBLE='8.1.3';
select ''||OWNER||'.'||QUEUE_TABLE||',' RunIt
from dba_queue_tables
where OWNER||QUEUE_TABLE in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'TABLE'and OWNER ='&user') and
RECIPIENTS='MULTIPLE' and COMPATIBLE='8.0.3';
select ''||OWNER||'.AQ$_'||QUEUE_TABLE||'_I,' RunIt
from dba_queue_tables
where OWNER||QUEUE_TABLE in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'TABLE'and OWNER ='&user') and
RECIPIENTS='MULTIPLE' and COMPATIBLE='8.0.3';
select ')' from dual;
spool off
set pages 4444
spool imptables.dat
select 'FULL=Y' from dual;
select 'LOG=import.log' from dual;
select 'FILE=&user' from dual;
select 'TOID_NOVALIDATE=(' from dual;
select distinct ''||OBJECT_TYPE||',' RunIt
from dba_queue_tables
where OBJECT_TYPE in
(select distinct OWNER||'.'||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'TYPE'and OWNER ='&user');
select ')' from dual;
spool off
set pages 4444
spool truncatesingle.sql
select 'truncate table '||OWNER||'.'||QUEUE_TABLE||';' RunIt
from dba_queue_tables
where OWNER||QUEUE_TABLE in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'TABLE'and OWNER ='&user') and
RECIPIENTS='SINGLE';
spool off
set pages 4444
spool truncatemultiple81.sql
select 'truncate table '||OWNER||'.'||QUEUE_TABLE||';' RunIt
from dba_queue_tables
where OWNER||QUEUE_TABLE in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'TABLE'and OWNER ='&user') and
RECIPIENTS='MULTIPLE' and COMPATIBLE='8.1.3';
select 'truncate table '||OWNER||'.AQ$_'||QUEUE_TABLE||'_I;' RunIt
from dba_queue_tables
where OWNER||QUEUE_TABLE in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'TABLE'and OWNER ='&user') and
RECIPIENTS='MULTIPLE' and COMPATIBLE='8.1.3';
select 'truncate table '||OWNER||'.AQ$_'||QUEUE_TABLE||'_T;' RunIt
from dba_queue_tables
where OWNER||QUEUE_TABLE in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'TABLE'and OWNER ='&user') and
RECIPIENTS='MULTIPLE' and COMPATIBLE='8.1.3';
select 'truncate table '||OWNER||'.AQ$_'||QUEUE_TABLE||'_H;' RunIt
from dba_queue_tables
where OWNER||QUEUE_TABLE in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'TABLE'and OWNER ='&user') and
RECIPIENTS='MULTIPLE' and COMPATIBLE='8.1.3';
spool off
set pages 4444
spool truncatemultiple80.sql
select 'truncate table '||OWNER||'.'||QUEUE_TABLE||';' RunIt
from dba_queue_tables
where OWNER||QUEUE_TABLE in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'TABLE'and OWNER ='&user') and
RECIPIENTS='MULTIPLE' and COMPATIBLE='8.0.3';
select 'truncate table '||OWNER||'.AQ$_'||QUEUE_TABLE||'_I;' RunIt
from dba_queue_tables
where OWNER||QUEUE_TABLE in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'TABLE'and OWNER ='&user') and
RECIPIENTS='MULTIPLE' and COMPATIBLE='8.0.3';
spool off
set pages 4444
spool dropnormalq.sql
select 'exec dbms_aqadm.drop_queue('''||OWNER||'.'||NAME||''');' RunIt
from dba_queues
where OWNER||NAME in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'QUEUE'and OWNER ='&user') and
QUEUE_TYPE='NORMAL_QUEUE';
spool off
set pages 4444
spool dropqt.sql
select 'exec dbms_aqadm.drop_queue_table('''||OWNER||'.'||QUEUE_TABLE||''', TRUE);' RunIt
from dba_queue_tables
where OWNER||QUEUE_TABLE in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'TABLE'and OWNER ='&user');
spool off
set pages 4444
spool startnormal.sql
select 'exec dbms_aqadm.start_queue('''||OWNER||'.'||NAME||''');' RunIt
from dba_queues
where OWNER||NAME in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'QUEUE'and OWNER ='&user') and
QUEUE_TYPE='NORMAL_QUEUE';
spool off
set pages 4444
spool startexception.sql
select 'exec dbms_aqadm.start_queue('''||OWNER||'.'||NAME||''', FALSE);' RunIt
from dba_queues
where OWNER||NAME in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'QUEUE'and OWNER ='&user') and
QUEUE_TYPE='EXCEPTION_QUEUE';
spool off
set pages 4444
spool startnonpersistent.sql
select 'exec dbms_aqadm.start_queue('''||OWNER||'.'||NAME||''', FALSE);' RunIt
from dba_queues
where OWNER||NAME in
(select OWNER||OBJECT_NAME from dba_objects
where OBJECT_TYPE = 'QUEUE'and OWNER ='&user') and
QUEUE_TYPE='NON_PERSISTENT_QUEUE';
spool off
quit
run.sql
connect / as sysdba
@unscheduleremote
@unschedulelocal
@stop
host exp system/oracle parfile=exptables.dat
@truncatesingle
@truncatemultiple81
@truncatemultiple80
@dropnormalq
@dropqt
host imp system/oracle parfile=imptables.dat
@startexception
@startnonpersistent
@startnormal
@scheduleremote
@schedulelocal

本版精华
热门帖子

操作引用/回复

总帖数

每页帖数

1/1页

返回列表

用户登录

Weblogic中间件技术论坛

Tuxedo中间件技术论坛

数据库论坛

Java论坛

Linux/unix论坛

网站地图