X-Received: by 10.224.200.202 with SMTP id ex10mr26313377qab.8.1368727655038; Thu, 16 May 2013 11:07:35 -0700 (PDT) X-Received: by 10.49.24.145 with SMTP id u17mr623601qef.21.1368727654966; Thu, 16 May 2013 11:07:34 -0700 (PDT) Path: news.cambrium.nl!textnews.cambrium.nl!feeder3.cambriumusenet.nl!feed.tweaknews.nl!212.6.121.138.MISMATCH!newsfeeder.ewetel.de!newsfeed.xs4all.nl!newsfeed1.news.xs4all.nl!xs4all!news.glorb.com!l3no9024324qak.0!news-out.google.com!y6ni49017qax.0!nntp.google.com!gj8no578585qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.databases.oracle.server Date: Thu, 16 May 2013 11:07:34 -0700 (PDT) In-Reply-To: Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=69.4.5.254; posting-account=KXUmygkAAABvBFmgDBe4RBLFwhTRAMZC NNTP-Posting-Host: 69.4.5.254 References: User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: Subject: Re: [Beginner]Can't see records in large table From: ddf Injection-Date: Thu, 16 May 2013 18:07:35 +0000 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Xref: news.cambrium.nl 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 s= truggling with Oracle, clearly because the administration model is really d= ifferent. >=20 >=20 >=20 > 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= . >=20 >=20 >=20 > I gave up after about 3 days getting Oracle installed on Ubuntu, and woun= d up installing it on a Windows Vista VM (I know: horror). >=20 >=20 >=20 > 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 3= 2GB until you manually create extra files for the tablespace, so after I cr= eated about 6, I ran the import again, and when I ran a query against dba_l= og and dba_segments that I found online, to show me how much space all my t= ables are taking up, I have over 3000 tables, many of them taking up a non-= trivial amount of space. >=20 >=20 >=20 > 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 que= ry, I get no rows. >=20 >=20 >=20 > The tables involved are owned by a variety of users (I understand that ra= ther 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 acco= unt for which I did: >=20 > In Oracle there is one database, one or more instances (but in your case th= ere is a one-to-one correspondence since I doubt very seriously you've crea= ted 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 a= ccess the objects owned by other users in the database. =20 >=20 > grant dba to with admin option >=20 > Yes, this gets you SELECT ANY TABLE privilege; it doesn't create synonyms f= or 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>=20 >=20 > So my current question (I'm sure there will be more -- is there a beginne= rs list where this would be better?) is: how can I have a large table where= I can't see any rows in it? >=20 >=20 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 spa= ce. The *_SEGMENTS views report on each segment (table/index/etc) and repor= t 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, auto= allocate tablespace. We can create another tablespace with uniform extents= : SQL> create tablespace users2 datafile 'C:\APP\FITZJARRELL.DAVID\ORADB\ORAD= ATA\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 d= id. 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 m= aking one LARGE extent out of a number of smaller extents. That becomes th= e 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 th= is. 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 in= serts failed. That is the most likely reason you're seeing 'no rows selec= ted'; if you didn't have select privilege on the table you would see a diff= erent 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 tha= t were created. >=20 > To make things more fun, the folks we got the dump from should be conside= red 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