[Beginner]Can't see records in large table

[Guyren Howe]

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:

grant dba to <user> with admin option

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?

To make things more fun, the folks we got the dump from should be considered hideously expensive if not actively hostile.

[Harry Tuttle]

Guyren Howe, 16.05.2013 08:40:

> 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,

To be precise: _smallfile_ tablespace files will not expand beyond 32GB.

You can create a so called "BIGFILE" tablespace which can then grow to 128TB

> 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.

Do you get any errors? Or simply "no rows found"?

Do those tables have row estimates in "DBA_ALL_TABLES"?

You can check this using:

select owner, table_name, num_rows, blocks
from dba_all_tables;

(maybe limit that to the owners you are interested in).

> 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:
>
> grant dba to <user> with admin option

I'm not sure if DBA actually includes the "SELECT ANY TABLE" privilege.
Try granting that explicitely.

[joel garry]

On May 15, 11:40 pm, Guyren Howe <guy...@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
--
@home.com is bogus.
https://blogs.oracle.com/securityinsideout/entry/oracle_database_security_at_microsoft

[ddf]

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

[Guyren Howe]

Most of the larger tables are owned by a user called flexadmin. However, if I log in as flexadmin and do:

select table_name, num_rows from user_tables;

This user has over 500 tables, but the tables all have 0 rows.

I've logged in as sys and done:

grant all privileges to flexadmin;

So unless there's something peculiar going on, I'm guessing something went wrong with the import.

I did the import with a -full option, I think it was. But this was after I'd done the import with only one 32GB file so it failed.

If I look at the import log for one of the larger tables belonging to flexadmin, I get "All dependent metadata and data will be skipped due to table_exists_action of skip".

The obvious guess was that the failed import created all the tables but not the records, and when I ran it again, it skipped the extant tables. Which is reasonable, except for why the table is so big. Perhaps that's space it reserved?

The most obvious thing to do I guess is to blow away the database and start again, but it took so much faffing about to get to this point that I believe I'm fairly likely to wind up with something that's unusable that will take me days to fix again.

I can do that, but if there is a way to run the data pump import again and have it create the records I appear to be missing, I think that would be better.

[Guyren Howe]

Addendum: now trying impdp with table_exists_action=truncate and reuse_datafiles=y.

[Harry Tuttle]

Guyren Howe wrote on 16.05.2013 22:12:
> Most of the larger tables are owned by a user called flexadmin.
> However, if I log in as flexadmin and do:
>
> select table_name, num_rows from user_tables;
>
> This user has over 500 tables, but the tables all have 0 rows.
>

num_rows will only have a value if you analyze the tables. It's only an estimate.

What happens if you run a select count(*) on one of them?

[Guyren Howe]

I get 0 records still. My current working hypothesis is that the original import failed, though.

My current impdp job mentioned above isn't obviously doing a whole lot. The log hasn't updated in an hour or more, but it hasn't finished.

Doing status in the impdp prompt says the job is executing, bytes processed is 0, and the percent done is 100. This hasn't changed in a couple of hours. Might impdp have stalled for some reason without so indicating?

Is it possible that impdp is importing everything in a transaction, and I won't see the results in a different sql prompt until it has completed?

[Harry Tuttle]

Guyren Howe, 17.05.2013 01:26:

> Is it possible that impdp is importing everything in a transaction,
>and I won't see the results in a different sql prompt until it has completed?

Yes.

[joel garry]

An import log would show things like:

. . imported "YOURSCHEMA"."YOURTABLE" 1.169 GB
16228256 rows

But of course it wouldn't say that until the table is done, and there
could be a delay writing the output filling output buffers.

jg
--
@home.com is bogus.

We need Rockem Sockem Robots with Balmer and Page figures.
http://www.cio.com/article/733546/Microsoft_responds_to_Larry_Page_remarks_but_Oracle_is_quiet

[ddf]

impdp uses resumable transactions, so if you're short on space the job will wait until the situation is corrected. You should query DBA_FREE_SPACE to see what's available in the tablespace or tablespaces this import is using. You can also check the alert log for insufficient space messages. You may need to extend the datafile or datafiles or add more datafiles to get the import to resume processing.


David Fitzjarrell