Tuesday, December 20, 2005

20 Beginner Oracle Questions

Continuing in my newfound tradition for lists of 20, here is a "cheat sheet" of 20 common beginner Oracle questions.

Before we begin, one simple warning. Very few of these are complete answers, consider them pointers or starting points or else the incomplete understanding could be dangerous.

Edit: This has been highly edited since its original version.

Oracle General:

1. What are the key environment variables?
Among many others, your home and instance:
export ORACLE_HOME=oracle_home_dir
export ORACLE_SID=instance_name

2. How do you shut down or start up an Oracle instance?
Must log on as a sys user:
sqlplus sys/***@instance as sysdba

3. How do you start and stop a listener?
lsnrctl status
lsnrctl start
lsnrctl stop

4. What are the key Oracle files?
They are in network/admin folder.
tnsnames.ora: list of database connection information (client/server)
sqlnet.ora: communication parameters setup
listener.ora: list of databases to listen for on this machine

5. How do you connect to the database to execute queries?
sqlplus user/password@server_instance
On a default system, sometimes you can use scott/tiger.

6. How do you see the errors from your recently created view/procedure?
show errors;


7. How do you output a line from PL/SQL?

8. How do you get the current date?
SELECT sysdate FROM dual;
SELECT systimestamp FROM dual;

9. What are some other syntax considerations?
Commands must end with a semi-colon;
Strings must be single-quoted


10. How do you show the structure of a table?
desc table

11. How do I re-execute the most recent query/command?

12. How do I see my most recent query?
l (for "list")

13. How do I see the PL/SQL procedure output?

14. How do I execute a SQL file?

More Advanced:

15. How do I see who is currently connected?
SELECT username, program FROM v$session WHERE username IS NOT NULL;

Or, courtesy of a colleague of mine, something like this:

SELECT p.SPID "OS_PID", to_char(s.SID,'999') SID, s.serial#,
SUBSTR(TO_CHAR(s.logon_time, 'DD Month YY "at" HH:MI:SS'),1,30) "LOGON TIME", s.program, s.machine
FROM v$process p, v$session s
AND s.username IS NOT NULL
ORDER BY s.logon_time;

16. How do I find all invalid objects?
Query dba_objects for status = 'INVALID', something like this:
SELECT owner, decode(object_type,'PACKAGE BODY','PACKAGE',object_type) OBJECT_TYPE, count(object_name)
FROM dba_objects WHERE status = 'INVALID' GROUP BY owner, object_type;

17. How do I recompile invalid objects?
Must be logged in with privileges, and use this:

18. How do I compute table and index statistics for a schema?
Answer from Steve Ensslen:
10g: Don't, it can be set up to analyze itself.
Pre-10g: Must be logged in with privileges:

19. How do I analyze the performance of a query/procedure (query plans, index choice, etc)?
Many ways, one way is SQL Trace with TKPROF, which I have explained here:

20. How do I tell which database am I in?
select name from v$database;
select instance_name, host_name from v$instance;


21. How do I set up an Oracle client?

22. How do I get data into and out of Oracle?

On #9 what is getdate()? Do you mean sysdate?

On #18 shouldn't you use a procedure from dbms_stats, instead?
I have to disagree with the answer to question 18. "How do I compute table and index statistics for a schema?".

The first answer is that you don't. In 10g the database analyzes itself as needed in the default install.

The second answer would reference DBMS_STATS.GATHER_SCHEMA_STATS which has been the preferred procedure to call since 8.1.7 .
Thanks guys, I should have proof-read! Edited original post.
"4. What are the key Oracle files?"
had me puzzled. I was thinking about data files, redo, control files, that kind of thing. Turns out TNS listener config files are more important...

"5. How do you connect to the database to execute queries? sqlplus..."
also a bit of a trick question. Who says SQL*Plus is the only way to connect to Oracle?

"6. How do you see the errors from your last query?"
Er, "show errors"? Your last query?

"8. How do you show the structure of a table? desc table"
seems like it should be in the SQL*Plus section, since you have one.

"9. How do you get the current date? SELECT sysdate FROM dual;"
Would I lose points for not mentioning DUAL in my answer? Or SYSDATE?

"12. How do I see my query/command history? l (for 'list')"
Surely that only shows the most recent command, not the whole history.
Thanks William, I did another round of editing to fix those errors.

#4: I made a judgment that those files are the first ones a beginner is usually looking for.

#5: I chose SQL*Plus because in my opinion it is the most common and the most likely to be available anywhere Oracle is installed.

#6: Ack, I thought I had fixed that. I had pulled that from a question I got when someone was building a view (aka stored query).

#9: No bonus points, just thought I'd show it that way.

#12: Good catch. In fact, I was planning to blog on workarounds to that.

Question 1: whatever happened to ORACLE_BASE?? Especially when the installation instructions for 10g tell you not to bother setting ORACLE_HOME? And I would argue that PATH is pretty damn important!

Question 2: In the first place, "connect internal" was deprecated back in version 8.0 (that's a zero!!). In the second place, it has been impossible to log on as SYS as an ordinary user unless you tinker with init.ora parameters since 9i Release 1, and therefore your second example won't work. The correct form of log on is 'sqlplus sys/password@instance as sysdba'

Question 2: 20 question lists are fun, but not if the answers are over-simplified. The correct way to shut down should be 'shutdown'. Or, you can start to list 'shutdown normal|transactional|immediate|abort'. But just saying 'shutdown immediate' is wrong.

Question 3: Your answer is correct only if your listener has been named 'Listener'. If it's been called anything else, the answer is 'lsnrctl name-of-listener stop|start' -and that revised answer would then be true whatever name your listener possesses.

Question 4: is meaningless. What are the key files in MS Office? Er, do you mean winword.exe or very-important-doc.doc, or a DLL or several?? "What are the key Oracle Network Configuration files" maybe, but the question as it stands means nothing.

Question 5: "On a default system you can use scott/tiger"... not since about version 8.0 you can't. Scott is not installed by default, even when you select to install the 'sample schemas' in recent versions.

Question 8: It could be argued (and I would) that your answer, whilst correct, is version 7 stuff. The world has moved on since then, and select systimestamp from dual; is just as effective at showing you the date, but rather more useful in that it shows you the current system time, too.

Question 18: Steve Ensslen is wrong. The 10g database doesn't analyze anything itself. There is a DBMS_SCHEDULER job which *might* be created in your database, but might not be (if you didn't use DBCA, for example, it won't be). If it has been created, it might or might not be enabled and thus might or might not run. And if it runs, your schema's tables and index statistics might or might not be locked from being re-computed. Given all that lot, just saying "the database does it" is an over-simplification too far.

Generally, a lot of your answers are the sort of thing users in version 7 or 8.0 would have expected, and are therefore out-of-date when it comes to version 9i or 10g.

And whilst I'm all for a bit of fun, bits of fun which over-simplify or simply get things wrong are bits of dangerous fun.
Enter: The Pedants.


The question is "How do I find all invalid objects?"

The "answer" query only counts objects that are invalid by type.


How do you output a line from PL/SQL?


Those double quotes don't work and also contradict your answer to question 9?
Howard, thanks for the corrections, I've made the fixes and added a disclaimer.

Yes, you're quite astute - this list came from my old notes which did indeed cover version 7, 8 and sometimes 9. The questions came from casual users who didn't know Kyte from byte.

#5: I have done default installations of version 9 and 10, and scott/tiger was there in both cases.

#8 In PL/SQL of course you'd just set a variable with:
l_sysdate := sysdate;

Selecting it from dual would be a waste of resources
Hey, I got named in ComputerWorld’s “Best IT Blogs on the Net.” Granted, only in the “buffer overflow” section, but still – that’s pretty cool!


I wonder if that is another consequence of the Thomas Kyte Effect?

I received my Oracle PL/SQL certification from brainbench.
Can somebody tell me if this is a well recognised certificatio.
it's helpful for a beginners like me.. i need some more help from you..
pls visit my blog and give ur suggestios
my blog is

here, i am trying to educate oracle for a layman..
thanks for the info ...

This comment has been removed by a blog administrator.
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?