Export file Limitations
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Export file Limitations

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Posts
    449
    Hello DBAs : I guess export dumps have a limitation of 2GB per file or something right. If I have a table more than 2GB, then how would I do it.. What are all the choices I might have.
    Thanks ST

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    it's OS dependent, it is not an Oracle feature ... if your system cannot create files larger than 2 Gb, then there is no workaround for Oracle to do so ...

  3. #3
    Join Date
    Oct 2000
    Posts
    449
    Thanks pipo. So what are the workarounds.. How can I get this done... Thanks ST

  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    if you are under Unix, you can use compress or gzip to compress on the fly what is exported, so maybe you won't reach 2 Gigs.
    also note that a 2Gb table will surely have a really smaller dump since all the allocated space is not used !!

  5. #5
    Join Date
    Oct 2000
    Posts
    449
    Thanks Pipo.. My table size is 4.5GB and how do I handle this.. Please xplain in detail or an example by steps as I know only how to export and import files less than 2GB.. I am not a pro in unix as well and this is AIX machine.. ST

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Prior to 8i, you need to compress the file while exp dump file is being written. This can be done using named pipe in UNIX.
    Example:
    # prod_export.sh
    # This shell script exports jpmc db and compress it
    # using named pipe
    # Author Tamil
    # Date : Aug 17, 2001
    # In Unix prompt, run jpmc_export.sh 2>jpmc_export.log

    NLS_LANG=American_America.US7ASCII; export NLS_LANG

    # First create a named pipe
    mknod exp_pipe p

    # export prod db


    DX=`date '+%Y%m%d'`; export DX

    exp system/password full=Y recordlength=65535 compress=N direct=Y file=exp_pip
    e|compress /data/u04/prod/export/prod_full_exp_$DX.dmp.Z

    # EOF prod_export.sh

    Another method is using filesize in exp parameters. This is very easy to accomplish >2gb problem

    exp system/password full=Y file=(a1.dmp, a2.dmp, a3.dmp) filesize=2000000000 compress=N direct=Y consistent=Y buffer=100000000 log=exp_prod.log

  7. #7
    Join Date
    Oct 2000
    Location
    Cambridge, MA (Boston)
    Posts
    144
    have you ever compressed on the fly AND used multiple export files (in one export)? i've been wondering about this as my compressed exports get up to 1.8G...

    d.

  8. #8
    Join Date
    Apr 2001
    Posts
    108

    Large Export Files

    If your using 8i, you can break the export file into smaller seperate files. If your using an earlier version and on Unix, there are tricks you can pull to break your export file into seperate chunks. I have to do this as I have a large database on 7.3.4.

    Another point to remember on exporting large databases is that I believe that Oracle still has a limit of 2Gbytes per extent size. So if you export with compress = Y, your dump file may have a table create statement with an initial extent value greater than 2Gbytes. Just something else to think about in this topic.

  9. #9
    Join Date
    Aug 2001
    Posts
    134

    -win2000
    -oracle 8.1.5
    I have export 4G data in one file .

    Jani

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width