Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Oracle 7.3.4 on Solaris 2.7 - Export File Size Limit

Posted on 2001-09-10
5
624 Views
Last Modified: 2013-12-05
We have several Oracle 7.3.4 databases running on Solaris 2.7.  During my full database exports, I receive error messages EXP-00002, which means that I have hit the physical file limit on the server.  The limit for file size is set to Infinity on the OS side.  Is there a limit on the Oracle side?  What do I need to change?
0
Comment
Question by:120864
5 Comments
 

Expert Comment

by:iamlhb
ID: 6472847
It's not oracle limit!
You can change the OS level file size by kernel parameter!
If you have the file size limit, you can use the split command on the UNIX!
More detail usage exist on Metalink!
0
 
LVL 1

Expert Comment

by:balasubram
ID: 6473509
Hi,
On your export script please check whether the 'COMPRESS=Y' exists or else add it.
This should be helpful for exports more than 2GB in size.
Bala
0
 

Expert Comment

by:yo_hou
ID: 6474847
You can specify filesize parameter and enough numbers of file name.
0
 
LVL 1

Accepted Solution

by:
marek_wiechula earned 50 total points
ID: 6475809
The COMPRESS=Y recommended by balasubram makes not difference to the size of the exported dump file.  It is an option which allows you to combine all your extents for each table/index into one extent.  So if you have table ABC with 5 extents and 500K of assigned disk space, then you end up with 1 extent of 500K.  

We use Oracle on Solaris and we had a problem that even if the OS file system allowed files > 2GB the Oracle software had problems with it.  We found that we could pipe our exports and avoid the problem  This is because when Oracle is writing to a named pipe it doesn't really know how big the file is, it just passes the export information along.  Since we were already going to that much trouble we put in another command to compress the export file on the fly and save some space while we were at it.

Basically we do something like this:

{set Oracle sid, locate to correct directory etc. etc.}
mknod export_pipe p
cat export_pipe | compress > export.dmp &
exp user/password file=export_pipe log=export_log <other export parameters>

In fact, in our environment, it is a little more complicated because we have UNIX variables (with embeded ORACLE_SID values) for the file and pipe names and additional code to identify where the cat export_pipe and compress processes are correctly terminated at the end of the export.

It is a little simpler in that we run this from a UNIX user who is also an externally identified DBA user in Oracle so we don't have to supply user or password, just the /.  

Also as a matter of interest we ALWAYS use COMPRESS=N.  We feel that the major reason for taking exports is as quick route to recovering any single table that might be accidentally dropped or corrupted.  To make quick use of that we may want to put the table back into the same space it used to use and we cannot guarantee that we can find a single piece of free space large enough to hold the whole table.  (Well we can guarantee it if we are willing to increase the tablespace size but we often aren't.)
0
 

Author Comment

by:120864
ID: 6477800
I incorporated your suggestions into my current script which exports all of our databases.  This works great.  Although it took some time, my import of a table from one of these files worked also.

Thank you.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
selective queries 7 38
Oracle SQL - Query help 7 66
Oracle Query - Convert letters to numbers and display the difference 3 30
format dd/mm/yyyy parameter 16 30
FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question