Re: Some questions from database newbie

On 16 Oct 1998 17:58:07 -0400, payerle_at_Glue.umd.edu (Thomas M. Payerle) wrote:

I can answer some of your questions.

<snip>
>1) The existing database code makes reference to a table sys.dual. I can't
>find any references to this table in my books, and this table seems to behave
>really strangely: eg describe sys.dual lists a single column DUMMY, yet
>select user from sys.dual; returns my userid. What's going on here?

DUAL exists in all databases, at least it should, and contains one row and one column. It's often used as a target for select statements when you just need to get the value of a function or a sequence, e.g.

	select my_sequence.nextval from dual;
	select sysdate from dual;

Now I have to say, I would be curious to know the history of how the name DUAL was chosen, and if there was originally some other purpose for the table.

>2) After spending close to an hour trying to enter a stored procedure in
>sqlplus, I eventually learned that I need to terminate the definition with
>a '/', although can't find any explanation of this in books. Did see something
>about terminating with a '.', which actually succeeds in terminating (ie no
>longer left with Oracle prompting me for rest of command), but also seems to
>discard what I did.

The period probably did not discard what you did. If you were in SQL*Plus, try entering the "L" command at the prompt. Your function should be listed back to you. You can enter the "/" command to execute your statement, or you can do further editing. SQL*Plus supports line editing.

I believe the Oracle SQL*Plus manual talks about the need for the "/" on the last line, but it's possible that it's not empasized enough. I had problems with that myself when I first learned PL/SQL.

>Since a ';' seems sufficient to terminate most every other
>command, I there's some logic to all this that I am missing.

There's a very good reason. PL/SQL requires each statement to be terminated with a semi-colon. If SQL*Plus tried to execute your PL/SQL block after the first semicolon was encountered, you would never be able to finish it.

Let me try and explain that better. When you enter an SQL statement in SQL*Plus, that statement is buffered until you have entered ALL of it. Then it is sent to the database. The same thing happens when you enter a PL/SQL code block. SQL*Plus buffers it until you terminate entry. Since semi-colons are valid PL/SQL syntax, another way is needed to terminate entry. Hence the need for the final, forward-slash.

>
>3) In my crash recovery, it seems that a sequence that was used to ensure the
>primary key of a table got reset several days. I assume this is because I
>restored from a several day old cold backup and then imp'ed fromuser= the user
>who owns all the important tables from a full export done shortly before the
>crash (which I think is just a lucky coincidence, but who knows).
<snip>
> I.e, would an imp fromuser=dba
>not update the sequences owned by user?

It would not update the sequence, but it would try to create the sequence. So if you dropped it first, and then did the import, the sequence should be recreated with the more recent value.

> Should I have the misfortune of being
>here when this database crashes again, is there a better way to do the import?

My personal preference is not to do an import over the top of existing data. In your case, consider that a record that was deleted following the operating system backup, and prior to the export, would still exist after you were done. Deletes don't get logged to an export file. If I were wanting to restore all a user's data, I would drop the user, recreate the user, and then do the import.

>b) I need to reset the sequence (set it ahead 100 or so). The best way I see
>of doing that is to alter sequence's increment to something like 100, call
>nextval for the sequence, then reset increment to 1. Is there an easier way?

That's what I usually do. The alternative is to drop the sequence and recreate it. Try to be sure that no one is accessing the sequence when you have the increment bumped up.

regards,

Jonathan Received on Sat Oct 17 1998 - 17:25:27 CDT