| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Big usr1xxx.ora Files ??
A copy of this was sent to "Markus Schwitter (Bitec)" <MrMagoo_at_plaza.ch>
(if that email address didn't require changing)
On Mon, 30 Nov 1998 18:55:35 +0100, you wrote:
>Is here a real PRO wich can tell me why Oracle uses so much space in the USR
>file ? When I generate a DMP-File of a DB, wich uses 1.8 Gig, the Dump is
>only 200Meg's. Also, very funny, when I set the compress-attribute (under
>NTFS, WIndows NT 4.0), the 1.8 Gig shrinks to a size of only 328Meg, and,
>really funny, the Database becomes faster at all !
>
when you export, no indexes created on tables (except for CREATE INDEX statements -- not the index data itself) is exported. Hence the export will always be smaller then the database.
Likewise, only source code for pl/sql comes along -- not the compiled pcode.
Likewise much of the system tablespace data is derived and not exported at all (user$ is not exported, but sometimes a create user command might be).
Likewise, when you export, free space on blocks (pctfree settings) are not 'exported' -- an export file is dense where as table data might (by design) have holes.
USR is a tablespace where you put your stuff -- no one here could really tell you why your USR tablespace is so large -- its got your tables and indexes in it.
My database exports down to less then half of its size (2 gigs of database exports to less then 1gig of export file) -- thats normal and expected (i also have my datafiles sized just so, there is no free space, you probably have lots of free space in your database as well, that won't get exported either).
DO NOT USE OS COMPRESSION ON DATAFILES. Of course they compress very well right now (it sounds like they are mostly empty). When mostly empty -- they are mostly all zeroed out -- compressing the same character over and over is very easy.
The reason you CANNOT compress them is that you might not have room to grow them later. Oracle really, truely believes it has 1.8 gig of space. If it does not really have 1.8 gig of space as the data files grow (and the compression ratio DROPS dramatically), it will get write errors on the datafiles (or even worse -- on redo logs or control files). The database will believe the disk it is using is bad and you will have to possibly recover the database (but only if you are running in archive log mode -- otherwise you are just out of luck). Don't compress your datafiles.
>any hints, or explanations ?
>
>Regards Markus
>
>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Nov 30 1998 - 12:11:38 CST
 |
 |