Re: database space and export file size ?

cc wrote in message ...
>Oracle 7.3.4 on Unix
>
>I use "select sum(bytes) from user_segments where owner='JOHN'" and the
>result is about 3GB.
>
>But use export owner=JOHN, the dump file only 800MB. Even full=y the dump
>file is only about 1.2GB. Why is so different? Is there anyway can tell
>roughly what the export dump file size will be?

sum(bytes) is the total number of bytes used by blocks which have been allocated to the table. If your table initialisation parameters have been set up generously most of these blocks will be empty.

An export file is effectively an SQL script to rebuild your database, containing CREATE TABLESPACE, CREATE TABLE, CREATE INDEX and INSERT INTO ... VALUES (.....) statements. The number of INSERT INTO statements is obviously dependent on the number of actual rows in the table. However, there is a lot of textual overhead in an INSERT INTO statement compared to the row's compressed format in the database.

It is therefore very difficult to correlate table size with export size. The only way to do it is "try it and see". One variation, if you are on Unix is to export to a named pipe and run a "wc" command at the other end of the pipe rather than create the export file.

This was discussed in a thread here a few months ago. Try searching dejanews: (http://www.dejanews.com/home_ps.shtml)

Specify:  Subject  = "Estimate the Size of an Full Database Export"
          Forum    = "comp.databases.oracle.server"
          Date     = 27/5/00 (dd/mm/yy)

Dave.

--
If you reply to this posting by email, remove the "nospam" from my email
address first.