How to export oracle undetermined schemas using expdp












0















I want to export Oracle database, but not full and not determined schemas; for example schemas that their names like 'IAS%' Or schema name like 'YS%'



I have written this command:



Expdp admin/admin@orcl schemas like 'IAS%'  file=my_data.dmp directory=exp_dir 


But i face error:




invalid positional parameter value 'like'











share|improve this question





























    0















    I want to export Oracle database, but not full and not determined schemas; for example schemas that their names like 'IAS%' Or schema name like 'YS%'



    I have written this command:



    Expdp admin/admin@orcl schemas like 'IAS%'  file=my_data.dmp directory=exp_dir 


    But i face error:




    invalid positional parameter value 'like'











    share|improve this question



























      0












      0








      0








      I want to export Oracle database, but not full and not determined schemas; for example schemas that their names like 'IAS%' Or schema name like 'YS%'



      I have written this command:



      Expdp admin/admin@orcl schemas like 'IAS%'  file=my_data.dmp directory=exp_dir 


      But i face error:




      invalid positional parameter value 'like'











      share|improve this question
















      I want to export Oracle database, but not full and not determined schemas; for example schemas that their names like 'IAS%' Or schema name like 'YS%'



      I have written this command:



      Expdp admin/admin@orcl schemas like 'IAS%'  file=my_data.dmp directory=exp_dir 


      But i face error:




      invalid positional parameter value 'like'








      oracle export expdp






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 26 '18 at 16:59









      Littlefoot

      22.7k71533




      22.7k71533










      asked Nov 26 '18 at 15:55









      Saddam MeshaalSaddam Meshaal

      1391316




      1391316
























          1 Answer
          1






          active

          oldest

          votes


















          0














          You will not be able to run your export using that command. The Data Pump export utility (EXPDP) does not use regular SQL. You cannot use shorthand SQL commands in clauses - the clauses have to be defined in accordance to Oracle's documentation. According to the documentation, an example of the EXPDP schemas clause looks like the following:



          expdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr,sh,oe



          They have the schemas listed as comma separated values, and you will have to do something like this to perform your export. Instead of trying to use a query in the command itself, you could create a query that will return all of those schemas in comma separated format and simply copy and paste that list into the command.



          To get that list, you could try running the following query:



          select listagg(username,', ') within group(order by username) csv
          from all_users
          where username like 'IAS%';


          This query uses the listagg function. If you are using a version of Oracle that does not support listagg, you could use the following query to accomplish the same thing. I found this query on a separate StackOverflow question, but it should work just fine:



          SELECT SUBSTR (SYS_CONNECT_BY_PATH (username , ','), 2) csv
          FROM (SELECT username , ROW_NUMBER () OVER (ORDER BY username ) rn,
          COUNT (*) OVER () cnt
          FROM all_users where username like 'IAS%')
          WHERE rn = cnt
          START WITH rn = 1
          CONNECT BY rn = PRIOR rn + 1;


          Regardless of which query you use, you will now have a list of comma separated schemas that start with the string 'IAS'. For this example, let's say that list looks like IAS_1, IAS_2, IAS_3, etc. Your command would then change from:



          Expdp admin/admin@orcl schemas like 'IAS%' file=my_data.dmp directory=exp_dir



          To something more like:



          Expdp admin/admin@orcl schemas=IAS_1,IAS_2,IAS_3 file=my_data.dmp directory=exp_dir



          If schemas are created and dropped often within this database, you could run the query to update the list of schemas that are impacted and manually copy and paste the new list. If this is something that will happen to frequently for that, you could also create a Dynamic SQL script to generate and execute your EXPDP command.



          Hope this helps!



          Documentation on Data Pump: https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL200



          Documentation on listagg function:
          https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030






          share|improve this answer

























            Your Answer






            StackExchange.ifUsing("editor", function () {
            StackExchange.using("externalEditor", function () {
            StackExchange.using("snippets", function () {
            StackExchange.snippets.init();
            });
            });
            }, "code-snippets");

            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "1"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53484794%2fhow-to-export-oracle-undetermined-schemas-using-expdp%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            0














            You will not be able to run your export using that command. The Data Pump export utility (EXPDP) does not use regular SQL. You cannot use shorthand SQL commands in clauses - the clauses have to be defined in accordance to Oracle's documentation. According to the documentation, an example of the EXPDP schemas clause looks like the following:



            expdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr,sh,oe



            They have the schemas listed as comma separated values, and you will have to do something like this to perform your export. Instead of trying to use a query in the command itself, you could create a query that will return all of those schemas in comma separated format and simply copy and paste that list into the command.



            To get that list, you could try running the following query:



            select listagg(username,', ') within group(order by username) csv
            from all_users
            where username like 'IAS%';


            This query uses the listagg function. If you are using a version of Oracle that does not support listagg, you could use the following query to accomplish the same thing. I found this query on a separate StackOverflow question, but it should work just fine:



            SELECT SUBSTR (SYS_CONNECT_BY_PATH (username , ','), 2) csv
            FROM (SELECT username , ROW_NUMBER () OVER (ORDER BY username ) rn,
            COUNT (*) OVER () cnt
            FROM all_users where username like 'IAS%')
            WHERE rn = cnt
            START WITH rn = 1
            CONNECT BY rn = PRIOR rn + 1;


            Regardless of which query you use, you will now have a list of comma separated schemas that start with the string 'IAS'. For this example, let's say that list looks like IAS_1, IAS_2, IAS_3, etc. Your command would then change from:



            Expdp admin/admin@orcl schemas like 'IAS%' file=my_data.dmp directory=exp_dir



            To something more like:



            Expdp admin/admin@orcl schemas=IAS_1,IAS_2,IAS_3 file=my_data.dmp directory=exp_dir



            If schemas are created and dropped often within this database, you could run the query to update the list of schemas that are impacted and manually copy and paste the new list. If this is something that will happen to frequently for that, you could also create a Dynamic SQL script to generate and execute your EXPDP command.



            Hope this helps!



            Documentation on Data Pump: https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL200



            Documentation on listagg function:
            https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030






            share|improve this answer






























              0














              You will not be able to run your export using that command. The Data Pump export utility (EXPDP) does not use regular SQL. You cannot use shorthand SQL commands in clauses - the clauses have to be defined in accordance to Oracle's documentation. According to the documentation, an example of the EXPDP schemas clause looks like the following:



              expdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr,sh,oe



              They have the schemas listed as comma separated values, and you will have to do something like this to perform your export. Instead of trying to use a query in the command itself, you could create a query that will return all of those schemas in comma separated format and simply copy and paste that list into the command.



              To get that list, you could try running the following query:



              select listagg(username,', ') within group(order by username) csv
              from all_users
              where username like 'IAS%';


              This query uses the listagg function. If you are using a version of Oracle that does not support listagg, you could use the following query to accomplish the same thing. I found this query on a separate StackOverflow question, but it should work just fine:



              SELECT SUBSTR (SYS_CONNECT_BY_PATH (username , ','), 2) csv
              FROM (SELECT username , ROW_NUMBER () OVER (ORDER BY username ) rn,
              COUNT (*) OVER () cnt
              FROM all_users where username like 'IAS%')
              WHERE rn = cnt
              START WITH rn = 1
              CONNECT BY rn = PRIOR rn + 1;


              Regardless of which query you use, you will now have a list of comma separated schemas that start with the string 'IAS'. For this example, let's say that list looks like IAS_1, IAS_2, IAS_3, etc. Your command would then change from:



              Expdp admin/admin@orcl schemas like 'IAS%' file=my_data.dmp directory=exp_dir



              To something more like:



              Expdp admin/admin@orcl schemas=IAS_1,IAS_2,IAS_3 file=my_data.dmp directory=exp_dir



              If schemas are created and dropped often within this database, you could run the query to update the list of schemas that are impacted and manually copy and paste the new list. If this is something that will happen to frequently for that, you could also create a Dynamic SQL script to generate and execute your EXPDP command.



              Hope this helps!



              Documentation on Data Pump: https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL200



              Documentation on listagg function:
              https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030






              share|improve this answer




























                0












                0








                0







                You will not be able to run your export using that command. The Data Pump export utility (EXPDP) does not use regular SQL. You cannot use shorthand SQL commands in clauses - the clauses have to be defined in accordance to Oracle's documentation. According to the documentation, an example of the EXPDP schemas clause looks like the following:



                expdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr,sh,oe



                They have the schemas listed as comma separated values, and you will have to do something like this to perform your export. Instead of trying to use a query in the command itself, you could create a query that will return all of those schemas in comma separated format and simply copy and paste that list into the command.



                To get that list, you could try running the following query:



                select listagg(username,', ') within group(order by username) csv
                from all_users
                where username like 'IAS%';


                This query uses the listagg function. If you are using a version of Oracle that does not support listagg, you could use the following query to accomplish the same thing. I found this query on a separate StackOverflow question, but it should work just fine:



                SELECT SUBSTR (SYS_CONNECT_BY_PATH (username , ','), 2) csv
                FROM (SELECT username , ROW_NUMBER () OVER (ORDER BY username ) rn,
                COUNT (*) OVER () cnt
                FROM all_users where username like 'IAS%')
                WHERE rn = cnt
                START WITH rn = 1
                CONNECT BY rn = PRIOR rn + 1;


                Regardless of which query you use, you will now have a list of comma separated schemas that start with the string 'IAS'. For this example, let's say that list looks like IAS_1, IAS_2, IAS_3, etc. Your command would then change from:



                Expdp admin/admin@orcl schemas like 'IAS%' file=my_data.dmp directory=exp_dir



                To something more like:



                Expdp admin/admin@orcl schemas=IAS_1,IAS_2,IAS_3 file=my_data.dmp directory=exp_dir



                If schemas are created and dropped often within this database, you could run the query to update the list of schemas that are impacted and manually copy and paste the new list. If this is something that will happen to frequently for that, you could also create a Dynamic SQL script to generate and execute your EXPDP command.



                Hope this helps!



                Documentation on Data Pump: https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL200



                Documentation on listagg function:
                https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030






                share|improve this answer















                You will not be able to run your export using that command. The Data Pump export utility (EXPDP) does not use regular SQL. You cannot use shorthand SQL commands in clauses - the clauses have to be defined in accordance to Oracle's documentation. According to the documentation, an example of the EXPDP schemas clause looks like the following:



                expdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp SCHEMAS=hr,sh,oe



                They have the schemas listed as comma separated values, and you will have to do something like this to perform your export. Instead of trying to use a query in the command itself, you could create a query that will return all of those schemas in comma separated format and simply copy and paste that list into the command.



                To get that list, you could try running the following query:



                select listagg(username,', ') within group(order by username) csv
                from all_users
                where username like 'IAS%';


                This query uses the listagg function. If you are using a version of Oracle that does not support listagg, you could use the following query to accomplish the same thing. I found this query on a separate StackOverflow question, but it should work just fine:



                SELECT SUBSTR (SYS_CONNECT_BY_PATH (username , ','), 2) csv
                FROM (SELECT username , ROW_NUMBER () OVER (ORDER BY username ) rn,
                COUNT (*) OVER () cnt
                FROM all_users where username like 'IAS%')
                WHERE rn = cnt
                START WITH rn = 1
                CONNECT BY rn = PRIOR rn + 1;


                Regardless of which query you use, you will now have a list of comma separated schemas that start with the string 'IAS'. For this example, let's say that list looks like IAS_1, IAS_2, IAS_3, etc. Your command would then change from:



                Expdp admin/admin@orcl schemas like 'IAS%' file=my_data.dmp directory=exp_dir



                To something more like:



                Expdp admin/admin@orcl schemas=IAS_1,IAS_2,IAS_3 file=my_data.dmp directory=exp_dir



                If schemas are created and dropped often within this database, you could run the query to update the list of schemas that are impacted and manually copy and paste the new list. If this is something that will happen to frequently for that, you could also create a Dynamic SQL script to generate and execute your EXPDP command.



                Hope this helps!



                Documentation on Data Pump: https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL200



                Documentation on listagg function:
                https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030







                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited Nov 27 '18 at 14:58

























                answered Nov 26 '18 at 19:28









                cdb_dbacdb_dba

                552816




                552816
































                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Stack Overflow!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53484794%2fhow-to-export-oracle-undetermined-schemas-using-expdp%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks

                    Calculate evaluation metrics using cross_val_predict sklearn

                    Insert data from modal to MySQL (multiple modal on website)