• oracle导出序列的几种办法


    oracle导出序列的几种办法

    注：本文来源于《oracle导出序列的几种办法》

    方法一:

    

      

    1. 

      

      

      

      select  'create sequence ' ||sequence_name||   
      2. 

    2. 

      

      

      

              ' minvalue ' ||min_value||   
      3. 

    3. 

      

      

      

              ' maxvalue ' ||max_value||   
      4. 

    4. 

      

      

      

              ' start with ' ||last_number||   
      5. 

    5. 

      

      

      

              ' increment by ' ||increment_by||   
      6. 

    6. 

      

      

      

             ( case  when cache_size= 0  then  ' nocache'   else   ' cache ' ||cache_size end) || ';'   
      7. 

    7. 

      

      

      

      from dba_sequences where sequence_owner= 'JXDB'  ;-- JXDB貌似要大写才行

    执行此操作后会生成创建序列的语句

    方法二：

    select dbms_metadata.get_ddl('SEQUENCE',u.object_name) from user_objects u where object_type='SEQUENCE'

    方法三：

    用plsql，直接用鼠标操作，右边对象窗口，找到Sequences，在要导出的序列名称上右键--查看，然后点击右下角的查看sql，即可查看该序列的创建sql，然后复制即可







    实验：Oracle数据泵导出导入之序列问题

    注：本文来源于《实验：Oracle数据泵导出导入之序列问题》
    使用数据泵expdp导出1个schema，有个表主键是触发器自增的id，导入测试库测试时，发现表里的数据比自增序列的值要大。导致插入数据报错。
    最终结论是：
    由于数据库先进行序列导出，然后再进行表数据导出。然后在导出的过程中，该表一直有插入操作，最终导致了这种差异。
    解决方法：
    重建触发器中的序列，让序列的开始值为表主键最大值+1。

    下面我构造实验完整演示下这种场景。

    1.准备测试环境

    需要建立测试表，序列，触发器和模拟业务插入数据的存储过程。
    以下是实际的创建语句：

    --在测试用户jingyu下创建测试表book2
drop table book2 purge;
create table book2(       
   bookId number(10) primary key,   
   name varchar2(20)         
); 

--创建序列  
drop sequence book2_seq;    
create sequence book2_seq start with 1 increment by 1;    
  
--创建触发器      
create or replace trigger book2_trigger       
before insert on book2     
for each row       
begin       
select book2_seq.nextval into :new.bookId from dual;      
end ;  
/ 

--创建实现循环添加数据的存储过程

/*
--存储过程中使用需要显示赋权
grant execute on dbms_lock to jingyu;
*/

create or replace procedure proc_insert_book2 is
begin
  loop
    insert into book2(name) values ('xx');
    commit;
    dbms_lock.sleep(1);
  end loop;
end;
/

    2.开始模拟该表不断插入

    由于我这里实际使用的是死循环，所以只要开始执行存储过程，每秒都会向测试表插入1条测试数据，直到手工停止。

    --执行该存储过程
exec proc_insert_book2;

--查询表的数量，确认是每秒多一条数据
select count(*) from book2;

    3.进行数据泵导出操作

    确认导出目录，编写expdp导出语句，最终将jingyu这个schema导出。实际命令如下：

    --expdp 导出
create or replace directory jy as '/opt/app/orabak/';
expdp jingyu/jingyu directory=jy dumpfile=jingyu.dmp schemas=jingyu

    实际执行导出的输出如下：

    [oracle@jyrac1 orabak]$ expdp jingyu/jingyu directory=jy dumpfile=jingyu.dmp schemas=jingyu

Export: Release 11.2.0.4.0 - Production on Thu Jun 8 17:08:29 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "JINGYU"."SYS_EXPORT_SCHEMA_05":  jingyu/******** directory=jy dumpfile=jingyu.dmp schemas=jingyu 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 10.12 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "JINGYU"."T2"                               6.649 MB  100000 rows
. . exported "JINGYU"."SYS_EXPORT_SCHEMA_01"             142.0 KB    1195 rows
. . exported "JINGYU"."SYS_EXPORT_SCHEMA_02"             142.2 KB    1196 rows
. . exported "JINGYU"."SYS_EXPORT_SCHEMA_03"             142.6 KB    1198 rows
. . exported "JINGYU"."SYS_EXPORT_SCHEMA_04"             149.7 KB    1201 rows
. . exported "JINGYU"."T_OLD"                            160.8 KB   20000 rows
. . exported "JINGYU"."T"                                82.94 KB   10000 rows
. . exported "JINGYU"."T_NOLOG"                          51.53 KB    5998 rows
. . exported "JINGYU"."BOOK"                             5.421 KB       2 rows
. . exported "JINGYU"."BOOK2"                            6.734 KB     123 rows
. . exported "JINGYU"."EMP"                              8.562 KB      14 rows
. . exported "JINGYU"."T1"                               11.75 KB     100 rows
Master table "JINGYU"."SYS_EXPORT_SCHEMA_05" successfully loaded/unloaded
******************************************************************************
Dump file set for JINGYU.SYS_EXPORT_SCHEMA_05 is:
  /opt/app/orabak/jingyu.dmp
Job "JINGYU"."SYS_EXPORT_SCHEMA_05" successfully completed at Thu Jun 8 17:10:26 2017 elapsed 0 00:01:36

    4.进行数据泵导入操作

    将上一步的导出文件，导入到另一个新建的测试用户jingyu2下。实际命令如下：

    --创建测试用户并赋予一定的权限
create user jingyu2 identified by jingyu2 default tablespace dbs_d_jingyu;
grant connect, resource to jingyu2;

--impdp 导入到用户jingyu2
impdp jingyu/jingyu directory=jy dumpfile=jingyu.dmp REMAP_SCHEMA=jingyu:jingyu2

    实际执行导入的输出如下：

    [oracle@jyrac1 orabak]$ impdp jingyu/jingyu directory=jy dumpfile=jingyu.dmp REMAP_SCHEMA=jingyu:jingyu2

Import: Release 11.2.0.4.0 - Production on Thu Jun 8 17:11:21 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "JINGYU"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "JINGYU"."SYS_IMPORT_FULL_01":  jingyu/******** directory=jy dumpfile=jingyu.dmp REMAP_SCHEMA=jingyu:jingyu2 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"JINGYU2" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "JINGYU2"."T2"                              6.649 MB  100000 rows
. . imported "JINGYU2"."SYS_EXPORT_SCHEMA_01"            142.0 KB    1195 rows
. . imported "JINGYU2"."SYS_EXPORT_SCHEMA_02"            142.2 KB    1196 rows
. . imported "JINGYU2"."SYS_EXPORT_SCHEMA_03"            142.6 KB    1198 rows
. . imported "JINGYU2"."SYS_EXPORT_SCHEMA_04"            149.7 KB    1201 rows
. . imported "JINGYU2"."T_OLD"                           160.8 KB   20000 rows
. . imported "JINGYU2"."T"                               82.94 KB   10000 rows
. . imported "JINGYU2"."T_NOLOG"                         51.53 KB    5998 rows
. . imported "JINGYU2"."BOOK"                            5.421 KB       2 rows
. . imported "JINGYU2"."BOOK2"                           6.734 KB     123 rows
. . imported "JINGYU2"."EMP"                             8.562 KB      14 rows
. . imported "JINGYU2"."T1"                              11.75 KB     100 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:"JINGYU2"."PRO_SELECT" created with compilation warnings
ORA-39082: Object type ALTER_PROCEDURE:"JINGYU2"."PROC_INSERT_BOOK2" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "JINGYU"."SYS_IMPORT_FULL_01" completed with 3 error(s) at Thu Jun 8 17:11:52 2017 elapsed 0 00:00:26

    导入完成，但存在一些警告，与本实验有关的只有"JINGYU2"."PROC_INSERT_BOOK2" 编辑警告需要处理，在下面的步骤中详细说明。

    5.问题现象重现并解决

    问题现象重现：
    查询到表最大的BOOKID大于序列的当前值，具体情况如下：

    SQL> select max(BOOKID) from book2;

MAX(BOOKID)
-----------
        505

SQL> select book2_seq.currval from dual;
select book2_seq.currval from dual
                              *
ERROR at line 1:
ORA-08002: sequence BOOK2_SEQ.CURRVAL is not yet defined in this session


SQL> select book2_seq.nextval from dual;

   NEXTVAL
----------
       341

    导入的存储过程存在编译警告的问题，排查原因是权限问题，需要先处理下：

    --执行存储过程报错对象无效
SQL> exec proc_insert_book2
BEGIN proc_insert_book2; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object JINGYU2.PROC_INSERT_BOOK2 is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

--重新编译存储过程依然有错误
SQL> alter procedure proc_insert_book2 compile;

Warning: Procedure altered with compilation errors.

--显示具体的错误
SQL> show errors
Errors for PROCEDURE PROC_INSERT_BOOK2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/5      PL/SQL: Statement ignored
6/5      PLS-00201: identifier 'DBMS_LOCK' must be declared

--根据错误提示，赋权解决
SQL> show user
USER is "SYS"
SQL> grant execute on dbms_lock to jingyu2;

Grant succeeded.

--再次编译成功
SQL> alter procedure proc_insert_book2 compile;

Procedure altered.

    编译存储过程成功后，执行它模拟插入数据，意料之中的会报错：

    SQL> exec proc_insert_book2
BEGIN proc_insert_book2; END;

*
ERROR at line 1:
ORA-00001: unique constraint (JINGYU2.SYS_C0011351) violated
ORA-06512: at "JINGYU2.PROC_INSERT_BOOK2", line 4
ORA-06512: at line 1

--查询测试表主键bookid的最大值
SQL> select max(bookid) from book2;

MAX(BOOKID)
-----------
        505

    重新创建序列，序列开始值设置为MAX(BOOKID)+1，再次执行就可以正常插入了。
    重新创建序列的语句如下：

    --重新创建序列  
drop sequence book2_seq;    
create sequence book2_seq start with 506 increment by 1;

    至此，整个实验完成。
  • 相关阅读:
    转载：网关的概念以及形象的比喻
    IP地址的分类
    Linux TOP 交互命令
    Unix系统引导过程（简单步骤）
    常用shell命令中你所不熟悉的参数
    3.通过现有的PDB创建一个新的PDB
    2.oracle 12c 创建-访问-关闭-删除PDB
    1.oracle 12c基础
    笔记本设置无线热点
    Pivot 和 Unpivot
  • 原文地址：https://www.cnblogs.com/ios9/p/9644730.html
Copyright © 2020-2023  润新知