2 Replies Latest reply on Oct 5, 2009 3:59 PM by LKBrwn_DBA

    script to drop/re-create sequences is generated with errors

    518459
      Hello,

      I'm migrating an Oracle 10g instance running on Solaris 10 to an Oracle 11g instance running on RedHat Linux. I'm following the "Platform Migration" White Paper ( URLhttp://www.oracle.com/technology/deploy/availability/pdf/maa_wp_11g_platformmigrationtts.pdf ).

      In the appendix, there's the code for a script that's supposed to generate an SQL script that drops and re-creates the sequences on the target machine:

      set heading off feedback off trimspool on escape off
      set long 1000 linesize 1000 pagesize 0
      col SEQDDL format A300
      spool tts_create_seq.sql
      prompt /* ========================= */
      prompt /* Drop and create sequences */
      prompt /* ========================= */
      select regexp_replace(
      dbms_metadata.get_ddl('SEQUENCE',sequence_name,sequence_owner),
      '^.*(CREATE SEQUENCE.*CYCLE).*$',
      'DROP SEQUENCE "'||sequence_owner||'"."'||sequence_name
      ||'";'||chr(10)||'\1;') SEQDDL
      from dba_sequences
      where sequence_owner not in
      (select name
      from system.logstdby$skip_support
      where action=0)
      ;
      spool off

      I have run it on my Solaris machine, and it generates a file that looks like this:

      /* ========================= */
      /* Drop and create sequences */
      /* ========================= */

      CREATE SEQUENCE "MDSYS"."TMP_COORD_OPS" MINVALUE 1000000 MAXVALUE 2000000 INCREMENT BY 1 START WITH 1000000 NOCACHE NOORDER CYCLE

      CREATE SEQUENCE "MDSYS"."SDO_TOPO_TRANSACT_SUBSEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE ORDER NOCYCLE

      ....

      I don't have any "drop sequence" commands, and there is no semicolon (;) at the end of the "create sequence" lines, so those are not executed.
      I'm not at all familiar with PL/SQL, could somebody point me to the error?

      Thank you,
      Adrian
        • 1. Re: script to drop/re-create sequences is generated with errors
          12826
          That's an odd way of simply saying
          select  'DROP SEQUENCE "'||sequence_owner||'"."'||sequence_name ||'";'||chr(10) ,
                    regexp_replace(
                              dbms_metadata.get_ddl('SEQUENCE',sequence_name,sequence_owner),
                                        '^.*(CREATE SEQUENCE.*CYCLE).*$') ||';' 
          from dba_sequences
          where sequence_owner not in
                  (select name
                   from system.logstdby$skip_support
                   where action=0)
          ;
          • 2. Re: script to drop/re-create sequences is generated with errors
            LKBrwn_DBA
            Why do you need the "REGEXP_REPLACE ()" function?
            This would be simpler:
            SET long 32000 longc 80 pages 0 lin 80 trims on
            COL ddl wor
            SELECT    'DROP SEQUENCE "'
                   || sequence_owner
                   || '"."'
                   || sequence_name
                   || '";'
                   || CHR (10)
                   || DBMS_METADATA.get_ddl ('SEQUENCE', sequence_name, sequence_owner)
                   || ';'
                   || CHR (10) DDL
              FROM dba_sequences
             WHERE sequence_owner NOT IN (SELECT NAME
                                            FROM SYSTEM.logstdby$skip_support
                                           WHERE action = 0);
            
            /
            :p