Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2010
    Posts
    23

    Unanswered: XE's 4g limitation

    I am about to ask what historically has been considered (from several previous “and hilarious” posts) some kind of “wrong question” to ask. Having been generously and courteously treated in another forum on the site by one of your brethren, I hope for restraint. I am well aware there is a mountain of documentation on this particular discipline, but I am in need of opinions from those who have an understanding...as this is the value of an overview. I am attempting to "access" mature knowledge outside of my core competency.

    I have previously attempted to extract and prune data from a relatively large Medicare database (relational) with little success having bumped into the 2gig ceiling of Access. As a result, not only do I now hate Access for posing as a db program, but my pet project was summarily scrapped so I am taking it on myself in my spare time. As I appreciate it, Oracle 10g Express Edition (XE) has a 4gig ceiling. I would like to know what exactly that means so I can attempt to calculate the chances of the same thing happening again. I can determine the unzipped file sizes of the tables (4 each reporting year, approximately 30million records per table, and 3 years to be considered). But I don't think this will give me a reliable answer (or account for the phenomenon of bloat). I would appreciate a few opinions about how to calculate, in advance, whether I will have a problem with the now 4gig limitation.

    I would also appreciate opinions as to the suitability of Oracle as the “right” choice for a newcomer such as myself when compared to, say...MySQL or Postgres.

    All opinions appreciated...humor applauded...btw, the google search thing was mighty darn funny. Please direct that impressive ingenuity toward the question.

    Thanks to all,
    Hittman
    Andy Bruce

  2. #2
    Join Date
    Mar 2010
    Posts
    23

    There's nothing like a good faq

    Kindly disregard the comparative software request.

    It is well handled in the Postgres FAQ.

    Thanks
    Hittman

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >It is well handled in the Postgres FAQ.
    If you can, please post URL to Postgres FAQ so that I can see what it has to say.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Mar 2010
    Posts
    23

    Postgres FAQ

    http://www.dbforums.com/postgresql/1...resql-faq.html

    Some of this may be out of date for daily users but I found it generally informative.

    You guys might want to include this list on your FAQ page.

    Hittman

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I agree - size of (un)zipped files probably isn't the right way to estimate database size.

    As there are only 4 tables there, perhaps you could check average size of data stored into every column, sum those values, multiply by number of records - this might provide at least some information. If it is, for example, 2 GB, fine - you could try with XE. If it is 3.8 GB, you'd probably stop thinking of XE.

    If I'm not interpreting the XE limits wrong, this is how it goes: if it was possible to create a table with a single column whose datatype would be CHAR(4 GB), then the first value stored into that column would fill the database up. However, if you used VARCHAR2(4 GB), you'd be able to store much more information (as you probably wouldn't fill the whole column at once). Due to differences between CHAR and VARCHAR2, choosing a correct column datatype might make the difference.

  6. #6
    Join Date
    Aug 2009
    Posts
    262
    4 G.B is the limit for USER.DATA .

    system and sysaux table spaces ( their respective data etc bla and bla and bla ) are not included in this 4 GB limit .

    an other thing to consider . this 4 gigabites is the Only data which you store internally in a table space .
    have 4 external tables ... libraries .. or mapped storage . or even simple folders to store your files. and u can play with 4 terabites .

    i am currently having 57 GB and playing in apex


    true , there are tons of documents . There is no limit of this universe that can not be breached .

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    True, but external tables (actually, files stored in file system) aren't really part of an Oracle database. I mean, you can't export those data using EXP utility, you can't modify them (using simple SQL statements such as INSERT or UPDATE).

    Therefore, having all those information as read only probably works fine (just like in your case), but I guess there are certain limitations that make your life miserable if you want to implement something that already exists within Oracle, but you can't use it.

  8. #8
    Join Date
    Aug 2009
    Posts
    262
    "that make your life miserable if you want to implement something that already exists within Oracle, but you can't use it."

    ah touchy feelings .. i am honored


    true one cannot modify ( insert /update) " Directly " in external tables .... which version are we talking about ? ..

    the solution asked is ... " I am well aware there is a mountain of documentation on this particular discipline,"


    he is not saying about inserting every document in the database ? or open every document and copy paste the info in the rows and columns .

    4 tables containing 30million records per table ... lol .. heck thats lots of rows


    a manageable solution within the bondage of XE ... shell we ?

  9. #9
    Join Date
    Mar 2010
    Posts
    23

    Thanks

    Thanks for the replies everyone.

    Even though ya'll dumbed it down a bit for me...I'll have to do some reading to understand...which is exactly what I was hoping to get...a somewhat narrower field to plow.

    I just need to figure out what all goes into USER.DATA

    I'll report back with my calculations to discuss.

    THanks again

    Hittman
    Andy Bruce

  10. #10
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    *Giggles* Access.

    Ok, now that that is out of my system. What are you hoping to do besides the immediate dump, import and prune? What happens once you do that? Is it just a learning experience or will you use it to drive a website? And what do you want to get out of it?

    If you really want to understand how databases work then use Oracle or Postgres. Both are extremely capable and follow the standards pretty will so you'll learn to write good code and those skills will be portable to other RDBMS's. I'd probably recommend PostgreSQL over OracleXE, it will be a bit easier to learn and if you end up going over XE's limitations it will get real costly real fast.

    If you are simply trying to get this one job done then I'd suggest MySQL it will handle your workload and is more forgiving because they don't follow the standards as strictly.

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by hittman View Post

    I would also appreciate opinions as to the suitability of Oracle as the “right” choice for a newcomer such as myself when compared to, say...MySQL or Postgres.
    If you are a newcomer to Oracle anyway, I don't really see why you should struggle with the Oracle XE size limits when you are aware of the alternatives that don't have that. I would even argue that something like MySQL will be easier to deal with for a beginner. There's also a "grown-up database" alternative that, still being free, does not impose database size limitations. I won't name names, but it has "d", "b", and "2" in it.
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by n_i View Post
    If you are a newcomer to Oracle anyway, I don't really see why you should struggle with the Oracle XE size limits when you are aware of the alternatives that don't have that.
    I agree, PostgreSQL is just as good as Oracle (and definitely better choice than Oracle XE)
    There are some areas like Clustering (RAC), flashback queries and the magic that can be done with materialized views where Oracle is better. But these are very special features that won't be needed by everyone. And for some there are "manual" alternatives anyway.
    I don't agree that MySQL is a suitable match though

    There's also a "grown-up database" alternative that, still being free, does not impose database size limitations. I won't name names, but it has "d", "b", and "2" in it.
    DB2 is pretty cool database but according to this article:

    Compare the distributed DB2 9.5 data servers

    DB2 Express-C (which is the free version, don't confuse that with DB2 Express which is not free) has a 2GB memory limitation and can only use 2 cores/CPUs. I don't think there is a limit on the physical on-disk size of the database though. DB2 also has the best XML support of those three (Oracle, DB2, PostgreSQL)

  13. #13
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by shammat View Post
    I don't think there is a limit on the physical on-disk size of the database though.
    We were indeed discussing on-disk size, weren't we? For occasional use 2 cores / 2 GB of RAM should be sufficient. Besides, Oracle XE limits you to 1 core / 1 GB...
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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