Re: Can't see records in large table
Date: Thu, 16 May 2013 09:01:18 -0700 (PDT)
Message-ID: <e6b58b3a-8306-44df-b487-1eeec24fb339_at_tz3g2000pbb.googlegroups.com>
On May 15, 11:40 pm, Guyren Howe <guy..._at_gmail.com> wrote:
> I consider myself pretty decent with Postgres and MySQL. But I'm really struggling with Oracle, clearly because the administration model is really different.
>
> I've very little experience with Oracle. A week ago, I was given a 150GB Oracle dump and asked to import it into our MySQL (I know: horror) database.
>
> I gave up after about 3 days getting Oracle installed on Ubuntu, and wound up installing it on a Windows Vista VM (I know: horror).
>
> After a great deal of faffing about, I *think* I got the dump imported. I was days on this because it would seem to sort of work maybe, but I got a lot of errors. It seems that Oracle won't actually expand a database past 32GB until you manually create extra files for the tablespace, so after I created about 6, I ran the import again, and when I ran a query against dba_log and dba_segments that I found online, to show me how much space all my tables are taking up, I have over 3000 tables, many of them taking up a non-trivial amount of space.
As you may have noticed, the data as kept in the database may be larger than in the export. Three normal reasons for this: A table may have an incorrect initial extent (as for example, someone adds millions of rows and then deletes them, without lowering the high water mark); Some data may be in a different format in the export, including numeric, and there may be character translation on import; And some objects like indices are actually just create statements in the export. (And modern versions also have compression in the database so it could be smaller, too).
>
> My current problem (this last week has been a litany of such confusions) is that when I query against any of the top half dozen tables from this query, I get no rows.
>
> The tables involved are owned by a variety of users (I understand that rather than separate databases, I use user accounts as some sort of proxy for separate databases -- is that right?), but I am querying them from an account for which I did:
Oracle users/schemata are what some other engines call databases. It will be worth your while to go through the Concepts manual for your version, available at tahiti.oracle.com. Which version are you on? Which kind of export dump?
>
> grant dba to <user> with admin option
That (and select any table) are the wrong way to go about it. Since you are a beginner, you would best just login as the user directly. The normal way to have cross-schema access is for the owner to grant proper authority to other schemata, and sometimes synonyms are used to simplify (or complicate, as the case may be) things.
It also matters which tools you use to access the data, ie, sqlplus, sqldeveloper, various ODBC, apps, etc.
You might check the import logs to see what errors you've gotten. The grants may have already been made, you can check tables to see those. You don't want to develop bad habits like accessing user data through admin accounts.
>
> So my current question (I'm sure there will be more -- is there a beginners list where this would be better?) is: how can I have a large table where I can't see any rows in it?
There are a number of places, with varying amounts of help and accuracy. Welcome to the group! See http://www.dbaoracle.net/readme-cdos.htm
As to not being able to see rows in a large table, it could be there are none (that initial extent issue I mentioned above), or it could be you haven't granted yourself access. Again, login as the user and select count(*) from the table to see. In operational environments, you can also have situations of multiversion consistency, which means, you see a version of the data from when you start your query, if someone else has added a million rows without committing them, you won't see those. I highly recommend Tom Kyte's books to explain these kinds of things, he often has explanations geared to unlearning other db engine mythos.
>
> To make things more fun, the folks we got the dump from should be considered hideously expensive if not actively hostile.
Wheeee! :-D
jg
-- _at_home.com is bogus. https://blogs.oracle.com/securityinsideout/entry/oracle_database_security_at_microsoftReceived on Thu May 16 2013 - 18:01:18 CEST