1 2 Previous Next 15 Replies Latest reply: Nov 29, 2012 7:02 PM by Srini Chavali-Oracle RSS

    Export exluding one Tablespace Oracle 9i

    973679
      Hi,

      I have to perform an Oracle Export for just five schemas, but one of them I have to exclude a Tablespace.

      I made a parfile where I specified all the tables that I want to backup. Something like this.

      userid=user1/passwd
      file=/myexport/schemas.dmp
      rows=y
      buffer=10971520
      direct=y
      statistics=none
      indexes=y
      tables=('TABLE1',
      'TABLE2',
      'TABLE3',
      'TABLE4',
      ....
      ....
      'TABLE1206')

      I got the name of the tables through a shell scripting dynamic query, so the result is vertical and the export shows me:

      "EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help"


      My questions are:

      Is there any way to exclude a tablespace directly in the export options?

      Otherwise:

      I tried to make a query that shows me all table names in only one row:

      select xmlagg (xmlelement (e, table_name || ',')).extract ('//text()'),','
      from dba_tables where owner in ('OWNER1','OWNER2','OWNER3','OWNER4','OWNER5')
      and tablespace_name <> 'TSXXX' order by rownum

      but I got: ORA-19011: Character string buffer too small

      Do you have other ideas?


      Thanks!
        • 1. Re: Export exluding one Tablespace Oracle 9i
          sb92075
          970676 wrote:
          Hi,

          I have to perform an Oracle Export for just five schemas, but one of them I have to exclude a Tablespace.

          I made a parfile where I specified all the tables that I want to backup. Something like this.

          userid=user1/passwd
          file=/myexport/schemas.dmp
          rows=y
          buffer=10971520
          direct=y
          statistics=none
          indexes=y
          tables=('TABLE1',
          'TABLE2',
          'TABLE3',
          'TABLE4',
          ....
          ....
          'TABLE1206')

          I got the name of the tables through a shell scripting dynamic query, so the result is vertical and the export shows me:

          "EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help"


          My questions are:

          Is there any way to exclude a tablespace directly in the export options?

          Otherwise:

          I tried to make a query that shows me all table names in only one row:

          select xmlagg (xmlelement (e, table_name || ',')).extract ('//text()'),','
          from dba_tables where owner in ('OWNER1','OWNER2','OWNER3','OWNER4','OWNER5')
          and tablespace_name <> 'TSXXX' order by rownum

          but I got: ORA-19011: Character string buffer too small

          Do you have other ideas?


          Thanks!
          00019, 00000, "failed to process parameters, type 'EXP HELP=Y' for help"
          // *Cause:  Invalid command-line parameters were specified. 
          // *Action: Check the online help screen for the set of valid parameters, then 
          //          retry. 
          It appears to be complaining about the command line; which you neglected to actually post.

          as a test can you shorten the list of table to see if you can successfully invoke the export utility
          • 2. Re: Export exluding one Tablespace Oracle 9i
            vlethakula
            1)Is there any way to exclude a tablespace directly in the export options?

            EXP utility does not support. From 10g onwards expdp does that support using EXCLUDE option.

            One tricky could be, you can make that tablespace offline (provided you can do and does not affect your business) so that your exp utility does not read that tablespace. Please test if that works.

            2) for second option

            set long 100000
            and then run command in sql*plus

            Edited by: vlethakula on Nov 28, 2012 10:44 AM
            • 3. Re: Export exluding one Tablespace Oracle 9i
              sb92075
              vlethakula wrote:

              2) for second option

              set long 100000
              and then run command in sql*plus
              RIDICULOUS!

              exp is an OS utility that has NOTHING to do with sql*plus!
              • 4. Re: Export exluding one Tablespace Oracle 9i
                973679
                I've reduced the number of tables in the export and it was successful.

                Actually my list has 1376 tables, so do you think Oracle 9i has a limit of tables to export in the "tables" option?
                • 5. Re: Export exluding one Tablespace Oracle 9i
                  sb92075
                  970676 wrote:
                  I've reduced the number of tables in the export and it was successful.

                  Actually my list has 1376 tables, so do you think Oracle 9i has a limit of tables to export in the "tables" option?
                  I am unaware of any limit, but such a limit could exist.
                  Can you successfully export by splitting the file list into two (equal) parts?
                  • 6. Re: Export exluding one Tablespace Oracle 9i
                    jgarry
                    I'm hazy on the details, as it was long ago, but I seem to recall doing exactly that with a similar number of tables.
                    • 7. Re: Export exluding one Tablespace Oracle 9i
                      973679
                      This is my complete shell script:


                      mytables=`sqlplus -s 'user1/passwd' << EOF
                      set heading off
                      set pages 0
                      set feedback off
                      select case when rownum=(select max(rownum) from dba_tables where owner in ('OWNER1','OWNER2','OWNER3','SYS','SYSTEM')
                      and tablespace_name <> 'TSXX') then ''''||''|| owner ||'.'|| table_name || ''''||'' else
                      ''''||'' || owner||'.'||table_name || ''''||'' || ',' end
                      from dba_tables where owner in ('OWNER1','OWNER2','OWNER3','SYS','SYSTEM')
                      and tablespace_name <> 'TSXX' order by rownum
                      /
                      EOF`

                      #echo $mytables

                      echo "userid=user1/passwd\n\
                      file=/logs/exppip.pip\n\
                      rows=y\nbuffer=20971520\ndirect=y\nstatistics=none\n\
                      indexes=y\n\
                      tables=($mytables)" > parfile.par



                      rm exppip.pip
                      mknod exppip.pip p

                      nohup compress < exppip.pip > export.dmp.Z &

                      exp parfile=parfile.par > /logs/export.log 2>&1 &
                      • 8. Re: Export exluding one Tablespace Oracle 9i
                        vlethakula
                        He has 2 questions in the thread

                        1)Option to exclude tablespace in exp utility

                        2) I tried to make a query that shows me all table names in only one row:

                        select xmlagg (xmlelement (e, table_name || ',')).extract ('//text()'),','
                        from dba_tables where owner in ('OWNER1','OWNER2','OWNER3','OWNER4','OWNER5')
                        and tablespace_name 'TSXXX' order by rownum


                        for second question

                        this was my reply

                        set long 100000
                        and then run sql command


                        I am not sure, what you made my reply to feel RIDICULOUS :-(

                        Edited by: vlethakula on Nov 28, 2012 12:54 PM
                        • 9. Re: Export exluding one Tablespace Oracle 9i
                          Srini Chavali-Oracle
                          Just curious - why are you including tables from SYS and SYSTEM schemas ?

                          Srini
                          • 10. Re: Export exluding one Tablespace Oracle 9i
                            973679
                            I have no particular reason, dude.
                            • 11. Re: Export exluding one Tablespace Oracle 9i
                              Richard Harrison .
                              Hi,
                              It appears table= has a limit of how long that string can be. Can you not just have 2 export commands - one with the first set of tables and another with the second set - meaning you stay below the limitation of the string length?

                              Cheers,
                              Harry
                              • 12. Re: Export exluding one Tablespace Oracle 9i
                                973679
                                Do you think I can discard SYSTEM and SYS owners in order to reduce the long of the string?

                                Does it really necessary export those owners?
                                • 13. Re: Export exluding one Tablespace Oracle 9i
                                  Srini Chavali-Oracle
                                  You should not export SYS and SYSTEM objects - pl exclude them from your export.

                                  Schemas CTXSYS MDSYS ORDSYS Are Not Exported [ID 228482.1]
                                  EXP-10 Usernames Which Cannot be Exported [ID 217135.1]

                                  HTH
                                  Srini
                                  • 14. Re: Export exluding one Tablespace Oracle 9i
                                    973679
                                    I've discarded SYS and SYSTEM owners and I made two exports and it works fine.

                                    Thanks a lot for your support, guys!
                                    1 2 Previous Next