This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Nov 29, 2012 5:02 PM by Srini Chavali-Oracle RSS

Export exluding one Tablespace Oracle 9i

973679 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    Just curious - why are you including tables from SYS and SYSTEM schemas ?

    Srini
  • 10. Re: Export exluding one Tablespace Oracle 9i
    973679 Newbie
    Currently Being Moderated
    I have no particular reason, dude.
  • 11. Re: Export exluding one Tablespace Oracle 9i
    Richard Harrison . Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points