Re: [Beginner]Can't see records in large table
Date: Thu, 16 May 2013 11:07:34 -0700 (PDT)
Message-ID: <d0138a8f-38ba-4331-a90b-ed18347d44e2_at_googlegroups.com>
On Thursday, May 16, 2013 12:40:29 AM UTC-6, Guyren Howe 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.
>
>
>
> 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:
>
>
In Oracle there is one database, one or more instances (but in your case there is a one-to-one correspondence since I doubt very seriously you've created a RAC database), schemas and users. Schemas are owned by users but it's possible to have user accounts which don't own any objects. Such users access the objects owned by other users in the database.
>
> grant dba to <user> with admin option
>
>
Yes, this gets you SELECT ANY TABLE privilege; it doesn't create synonyms for those other objects, though, so you may need to use the owner.table_name syntax if the import didn't create those synonyms:
SQL> select *
2 from gribnaut.wheee;
no rows selected
SQL>
>
> 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?
>
>
This is one way:
SQL> create table wheee(
2 myid number, 3 mydata varchar2(40), 4 mydt date
5 )
6 segment creation immediate
7 storage(initial 100M next 100M)
8 tablespace users;
Table created.
SQL> SQL> column segment_name format a15 SQL> SQL> select segment_name, bytes, blocks
2 from user_segments;
SEGMENT_NAME BYTES BLOCKS
--------------- ---------- ----------
WHEEE 109051904 13312
SQL>
SQL> select count(*)
2 from wheee;
COUNT(*)
0
SQL> The table is completely empty yet the initial extent consumes 100 MB of space. The *_SEGMENTS views report on each segment (table/index/etc) and report on its storage. The *_EXTENTS views report on each extent a segment has:
SQL> select segment_name, segment_type, extent_id, bytes, blocks 2 from user_extents;
SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BYTES BLOCKS
--------------- ------------------ ---------- ---------- ---------- WHEEE TABLE 0 67108864 8192 WHEEE TABLE 1 8388608 1024 WHEEE TABLE 2 8388608 1024 WHEEE TABLE 3 8388608 1024 WHEEE TABLE 4 8388608 1024 WHEEE TABLE 5 8388608 1024
6 rows selected.
SQL> The 100 MB of table storage required six extents in a locally managed, autoallocate tablespace. We can create another tablespace with uniform extents:
SQL> create tablespace users2 datafile 'C:\APP\FITZJARRELL.DAVID\ORADB\ORADATA\SMEDLEY\USERS201.DBF' size 200M uniform size 100M;
Tablespace created.
SQL> and create the table again using it and see how many extents we get:
SQL> create table wheee(
2 myid number, 3 mydata varchar2(40), 4 mydt date
5 )
6 segment creation immediate
7 storage(initial 100M next 100M)
8 tablespace users2;
Table created.
SQL> SQL> column segment_name format a15 SQL> SQL> select segment_name, bytes, blocks
2 from user_segments;
SEGMENT_NAME BYTES BLOCKS
--------------- ---------- ----------
WHEEE 104857600 12800
SQL>
SQL> select count(*)
2 from wheee;
COUNT(*)
0
SQL>
SQL> select segment_name, segment_type, extent_id, bytes, blocks
2 from user_extents;
SEGMENT_NAME SEGMENT_TYPE EXTENT_ID BYTES BLOCKS
--------------- ------------------ ---------- ---------- ---------- WHEEE TABLE 0 104857600 12800
SQL> We now have one extent, consuming the same space the previous six extents did. Of course the table is still empty.
Depending on which utility was used to export the table (exp [the old way] or expdp [the new way]) the behavior can change. Using the old exp utility some releases defaulted to compressing extents, which is the practice of making one LARGE extent out of a number of smaller extents. That becomes the initial extent which can be a problem on import as some block sizes won't allow extents to be too large; the table will fail to create because of this.
To know why you have no rows in your tables you really need to read through the import log (you did generate one, hopefully) so you can see why the inserts failed. That is the most likely reason you're seeing 'no rows selected'; if you didn't have select privilege on the table you would see a different error:
SQL> select count(*)
2 from wheee;
from wheee
*
ERROR at line 2:
ORA-00942: table or view does not exist
SQL> so your access is fine. You simply have no data in these large extents that were created.
>
> To make things more fun, the folks we got the dump from should be considered hideously expensive if not actively hostile.
"It's the Future Fair! A Fair For All And No Fair To Anybody!!" (Firesign Theatre)
Enjoy the ride.
David Fitzjarrell Received on Thu May 16 2013 - 20:07:34 CEST