OracleBlog

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
Then:
shutdown
or
startup

3. How do you start and stop a listener?
lsnrctl status
lsnrctl start
lsnrctl stop
http://www.psoug.org/reference/listener.html

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;

SQL and PL/SQL:

7. How do you output a line from PL/SQL?
DBMS_OUTPUT.PUT_LINE('Hello.');

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

SQLPlus:

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?
SET SERVEROUTPUT ON;

14. How do I execute a SQL file?
@filename.sql

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(p.USERNAME,1,10) "OS_USER", SUBSTR(s.USERNAME,1,16) "ORACLE_USER",
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
WHERE s.PADDR=p.ADDR
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:
@?/rdbms/admin/utlrp.sql

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:
execute DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

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:
http://thinkoracle.blogspot.com/2005/09/analyzing-query-performance.html

20. How do I tell which database am I in?
select name from v$database;
or
select instance_name, host_name from v$instance;
or
SELECT SYS_CONTEXT(‘USERENV’,’DB_NAME’) FROM DUAL;
http://thinkoracle.blogspot.com/2005/07/which-instance-am-i-in.html

Bonus:

21. How do I set up an Oracle client?
http://thinkoracle.blogspot.com/2005/06/oracle-client.html

22. How do I get data into and out of Oracle?
http://thinkoracle.blogspot.com/2005/08/import-export.html