1 2 Previous Next 18 Replies Latest reply on May 8, 2013 2:45 PM by Sudhir

    export sequence from once schema to another using dbms_datapump

    Sudhir
      Hello,

      Can anybody help me how to export only sequences from one schema to another using dbms_datapump or using any other packages.

      I cannot use the expdp/impdp from command prompt.


      Thanks.
        • 1. Re: export sequence from once schema to another using dbms_datapump
          You would need to use dbms_datapump.meta_datafilter as indicated in http://psoug.org/reference/dbms_datapump.html
          or in the online Oracle documentation at http://docs.oracle.com

          --------------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: export sequence from once schema to another using dbms_datapump
            Richard Harrison .
            Hi,
            If it's in the same database and you have the correct rights you could use this which i just created....
            declare
            v_sql varchar2(4000);
            v_newsql varchar2(4000);
            cursor c is select sequence_name from user_sequences;
            begin
            for rec in c loop
            v_sql := dbms_metadata.get_ddl('SEQUENCE',rec.sequence_name);
            v_newsql := replace(v_sql,'PUMPY','SYSTEM');
            execute immediate v_newsql;
            end loop;
            end;
            Here 'PUMPY' is the original owner of the sequences and I am logged on as that, SYSTEM is the account I'm copying them all to.

            Does that work for you?

            Regards,
            Harry

            http://dbaharrison.blogspot.com
            1 person found this helpful
            • 3. Re: export sequence from once schema to another using dbms_datapump
              Sudhir
              Thanks Guys,

              Will check this and update.
              • 4. Re: export sequence from once schema to another using dbms_datapump
                Sudhir
                Hello Richard,

                thanks for the help this is helpful as you mentioned when it is in the same database,but i need to bring the sequence from another database using the dblink and with the same user name.

                Please help.
                • 5. Re: export sequence from once schema to another using dbms_datapump
                  Richard Harrison .
                  Hi,
                  Take a look at this - i just created the routine using DBMS_DATAPUMP:

                  http://dbaharrison.blogspot.de/2013/05/dbmsdatapump-import-of-only-sequences.html

                  It worked for me but it seemed to need quite a high level of priveleges on both sides of the link which might be a problem for you....

                  Cheers,
                  Harry
                  • 6. Re: export sequence from once schema to another using dbms_datapump
                    Sudhir
                    Hello Richard,

                    thanks for the advice.
                    I am running the below code as your advice ,but it is erroring out with the below message.

                    get_sequence - Started
                    exception :ORA-39001: invalid argument value

                    PL/SQL procedure successfully completed.

                    set serveroutput on;
                    declare
                    l_dp_handle NUMBER;
                    v_job_state varchar2(4000);
                    BEGIN

                    dbms_output.put_line('get_sequence - Started');

                    l_dp_handle := DBMS_DATAPUMP.open(operation => 'IMPORT',
                    job_mode => 'IRIS_DATA',
                    remote_link => 'PSUP_DATA',
                    version => 'LATEST');
                    dbms_output.put_line('get_sequence - get handle');

                    DBMS_DATAPUMP.add_file(handle => l_dp_handle,
                    filename => 'test.log',
                    directory => 'tmp',
                    filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE); dbms_output.put_line('get_sequence - add file');

                    DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle, 'SCHEMA_LIST', '''PSUP_DATA'''); dbms_output.put_line('get_sequence - metadata filer1');

                    DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle,
                    'INCLUDE_PATH_EXPR',
                    'IN (''SEQUENCE'')'); dbms_output.put_line('get_sequence - metadata filer2');

                    DBMS_DATAPUMP.METADATA_REMAP(l_dp_handle,
                    'REMAP_SCHEMA',
                    'PSUP_DATA',
                    'IRIS_DATA'); dbms_output.put_line('get_sequence - metadata remap');

                    DBMS_DATAPUMP.start_job(l_dp_handle); dbms_output.put_line('get_sequence - starting job');

                    DBMS_DATAPUMP.WAIT_FOR_JOB(l_dp_handle, v_job_state); dbms_output.put_line('get_sequence - waiting for job');

                    dbms_output.put_line('get_sequence - completed');
                    exception
                    when others then
                    dbms_output.put_line('exception :'||sqlerrm);
                    end;


                    Please advice.
                    • 7. Re: export sequence from once schema to another using dbms_datapump
                      Richard Harrison .
                      I think you need to change this part:

                      l_dp_handle := DBMS_DATAPUMP.open(operation => 'IMPORT',
                      job_mode => 'SCHEMA',
                      remote_link => 'PSUP_DATA',
                      version => 'LATEST');
                      dbms_output.put_line('get_sequence - get handle');

                      Regards,
                      Harry

                      http://dbaharrison.blogspot.de/
                      • 8. Re: export sequence from once schema to another using dbms_datapump
                        Sudhir
                        Hello Richard,

                        I changed my code accordingly,still facing the error.

                        get_sequence - Started
                        exception :ORA-31631: privileges are required

                        PL/SQL procedure successfully completed.

                        I granted the below to clonedev user

                        GRANT IMPORT FULL DATABASE TO CLONEDEV;
                        GRANT IMP_FULL_DATABASE TO CLONEDEV;
                        GRANT DBA TO CLONEDEV;

                        Thanks.

                        Please advice.
                        • 9. Re: export sequence from once schema to another using dbms_datapump
                          Richard Harrison .
                          Hi,
                          This is what i had initially - i had to grant extra rights to the schema i was 'pulling' from as well as the 'clone' schema. I was lazy and just granted DBA which fixed it - but it probably needs less than this to make it work.

                          Cheers,
                          Harry
                          • 10. Re: export sequence from once schema to another using dbms_datapump
                            Sudhir
                            Hello Richard,

                            Still facing the problem even after granting DBA to the source user.now the error is different.

                            set serveroutput on;
                            declare
                            l_dp_handle NUMBER;
                            v_job_state varchar2(4000);
                            BEGIN

                            dbms_output.put_line('get_sequence - Started');

                            l_dp_handle := DBMS_DATAPUMP.open(operation => 'IMPORT',
                            job_mode => 'SCHEMA',
                            remote_link => 'PSUP_DATA',
                            version => 'LATEST');
                            dbms_output.put_line('get_sequence - get handle');

                            DBMS_DATAPUMP.add_file(handle => l_dp_handle,
                            filename => 'test.log',
                            directory => 'dmpdir',
                            filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE); dbms_output.put_line('get_sequence - add file');

                            DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle, 'SCHEMA_LIST', '''PSUP_DATA'''); dbms_output.put_line('get_sequence - metadata filer1');

                            DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle,
                            'INCLUDE_PATH_EXPR',
                            'IN (''SEQUENCE'')'); dbms_output.put_line('get_sequence - metadata filer2');

                            DBMS_DATAPUMP.METADATA_REMAP(l_dp_handle,
                            'REMAP_SCHEMA',
                            'PSUP_DATA',
                            'IRIS_DATA'); dbms_output.put_line('get_sequence - metadata remap');

                            DBMS_DATAPUMP.start_job(l_dp_handle); dbms_output.put_line('get_sequence - starting job');

                            DBMS_DATAPUMP.WAIT_FOR_JOB(l_dp_handle, v_job_state); dbms_output.put_line('get_sequence - waiting for job');

                            dbms_output.put_line('get_sequence - completed');
                            exception
                            when others then
                            dbms_output.put_line('exception :'||sqlerrm);
                            end;


                            get_sequence - Started
                            get_sequence - get handle
                            exception :ORA-39002: invalid operation

                            PL/SQL procedure successfully completed.

                            I am having the below list of privileges as well.

                            SQL> SELECT * FROM SESSION_ROLES;

                            ROLE
                            ------------------------------
                            DBA
                            SELECT_CATALOG_ROLE
                            HS_ADMIN_ROLE
                            EXECUTE_CATALOG_ROLE
                            DELETE_CATALOG_ROLE
                            EXP_FULL_DATABASE
                            IMP_FULL_DATABASE
                            GATHER_SYSTEM_STATISTICS
                            SCHEDULER_ADMIN
                            WM_ADMIN_ROLE
                            JAVA_ADMIN
                            JAVA_DEPLOY
                            XDBADMIN
                            XDBWEBSERVICES


                            Thanks.

                            Edited by: Sudhir on May 8, 2013 10:22 AM
                            • 11. Re: export sequence from once schema to another using dbms_datapump
                              Richard Harrison .
                              Hi,
                              I suspect the error is because you have the directory name in lower case?

                              DBMS_DATAPUMP.add_file(handle => l_dp_handle,
                              filename => 'test.log',
                              directory => *'dmpdir'*,
                              filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE); dbms_output.put_line('get_sequence - add file');

                              dbms_datapump errors are sometimes hard to extract - try just making that small change first if you still get errors try adding this exception block from this note: http://dbaharrison.blogspot.de/2013/05/dbmsdatapump-finding-out-what-actual.html

                              Cheers,
                              Harry
                              • 12. Re: export sequence from once schema to another using dbms_datapump
                                Sudhir
                                Hello Richard,

                                No luck yet, it's new one now.

                                declare
                                l_dp_handle NUMBER;
                                v_job_state varchar2(4000);
                                l_last_job_state VARCHAR2(30) := 'UNDEFINED';
                                l_job_state VARCHAR2(30) := 'UNDEFINED';
                                l_sts KU$_STATUS;
                                v_logs ku$_LogEntry;
                                v_row PLS_INTEGER;

                                BEGIN

                                dbms_output.put_line('get_sequence - Started');

                                l_dp_handle := DBMS_DATAPUMP.open(operation => 'IMPORT',
                                job_mode => 'SCHEMA',
                                remote_link => 'PSUP_DATA',
                                version => 'LATEST');
                                dbms_output.put_line('get_sequence - get handle :'||l_dp_handle);

                                DBMS_DATAPUMP.add_file(handle => l_dp_handle,
                                filename => 'test.log',
                                directory => 'DMPDIR',
                                filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE); dbms_output.put_line('get_sequence - add file');

                                DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle, 'SCHEMA_LIST', '''PSUP_DATA'''); dbms_output.put_line('get_sequence - metadata filer1');

                                DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle,
                                'INCLUDE_PATH_EXPR',
                                'IN (''SEQUENCE'')'); dbms_output.put_line('get_sequence - metadata filer2');

                                DBMS_DATAPUMP.METADATA_REMAP(l_dp_handle,
                                'REMAP_SCHEMA',
                                'PSUP_DATA',
                                'IRIS_DATA'); dbms_output.put_line('get_sequence - metadata remap');

                                DBMS_DATAPUMP.start_job(l_dp_handle); dbms_output.put_line('get_sequence - starting job');

                                DBMS_DATAPUMP.WAIT_FOR_JOB(l_dp_handle, v_job_state);

                                dbms_output.put_line('get_sequence - completed');
                                exception
                                WHEN OTHERS THEN
                                dbms_datapump.get_status(NULL, 8, 0, v_job_state, l_sts);
                                v_logs := l_sts.error;

                                v_row := v_logs.FIRST;
                                LOOP
                                EXIT WHEN v_row IS NULL;
                                dbms_output.put_line('logLineNumber=' || v_logs(v_row).logLineNumber);
                                dbms_output.put_line('errorNumber=' || v_logs(v_row).errorNumber);
                                dbms_output.put_line('LogText=' || v_logs(v_row).LogText);
                                v_row := v_logs.NEXT(v_row);
                                END LOOP;
                                RAISE;
                                end;


                                get_sequence - Started
                                get_sequence - get handle :58
                                get_sequence - add file
                                declare
                                ***
                                ERROR at line 1:
                                ORA-31623: a job is not attached to this session via the specified handle
                                ORA-06512: at "SYS.DBMS_DATAPUMP", line 2772
                                ORA-06512: at "SYS.DBMS_DATAPUMP", line 3886
                                ORA-06512: at line 43
                                ORA-39001: invalid argument value


                                I go ogled some of adive i could see is grant create table to user, but my user is having DBA role as well.

                                Please help.
                                • 13. Re: export sequence from once schema to another using dbms_datapump
                                  Richard Harrison .
                                  Hi,
                                  As this is the last activity we don't see an output message for then this must be the problem line:

                                  DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle, 'SCHEMA_LIST', '''PSUP_DATA'''); dbms_output.put_line('get_sequence - metadata filer1');

                                  is PSUP_DATA your schema_name that you are extracting from?

                                  You have to grant explicit 'CREATE TABLE' privileges only if you turn the routine into a stored procedure as this will disable all roles (includng DBA). As this is an 'anonymous' block of plsql that shoudn't be an issue.

                                  If PSUP_DATA is correct then perhaps try granting explict CREATE TABLE anyway even though i don't think thats a problem.

                                  Regards,
                                  Harry

                                  http://dbaharrison.blogspot.com
                                  • 14. Re: export sequence from once schema to another using dbms_datapump
                                    Sudhir
                                    Hello Richard,

                                    thanks for the help.
                                    PSUP_DATA is the dblink name.


                                    Regards
                                    1 2 Previous Next