Commands In Oracle

Changing the Password

ALTER USER <yourName> IDENTIFIED BY <newPassword>;

Creating a Table

CREATE TABLE test (
         i int,
         s char(10)
     );

Creating a Table With a Primary Key

CREATE TABLE <tableName> (..., a <type> PRIMARY KEY, b, ...);

Inserting Tuples

INSERT INTO test 
VALUES(10, 'foobar');

Getting the Value of a Relation

SELECT * FROM test;

Dropping a Table

DROP TABLE test;

Getting Information About the Specific Database

The system keeps information about your own database in certain system tables. The most important is USER_TABLES. You can recall the names of your tables by issuing the query:

SELECT TABLE_NAME
FROM USER_TABLES;

More information about your tables is available from USER_TABLES. To see all the attributes of USER_TABLES, try:

SELECT *
FROM USER_TABLES;

It is also possible to recall the attributes of a table once you know its name. Issue the command:

DESCRIBE <tableName>;

to learn about the attributes of relation <tableName>.

Executing SQL From a File

Instead of executing SQL commands typed at a terminal, it is often more convenient to type the SQL command(s) into a file and cause the file to be executed.

To run the file foo.sql, type:

@foo

Editing Commands in the Buffer

If you end a command without a semicolon, but with an empty new line, the command goes into a buffer. You may execute the command in the buffer by either the command RUN or a single slash (/).

You may also edit the command in the buffer before you execute it.

LIST lists the command buffer, and makes the last line in the buffer the "current" line
LIST n prints line n of the command buffer, and makes line n the current line
LIST m n prints lines m through n, and makes line n the current line
INPUT enters a mode that allows you to input text following the current line; you must terminate the sequence of new lines with a pair of "returns"
CHANGE /old/new replaces the text "old" by "new" in the current line
APPEND text appends "text" to the end of the current line
DEL deletes the current line

Recording a Session

There are several methods for creating a typescript to turn in for your programming assignments. The most primitive way is to cut and paste your terminal output and save it in a file (if you have windowing capabilities). Another method is to use the Unix command script to record the terminal interaction. The script command records everything printed on your screen. The syntax for the command is

script [ -a ] [ filename ]

The record is written to filename. If no file name is given, the record is saved in the file typescript. The -a option allows you to append the session record to filename, rather than overwrite it. To end the recording, type

exit

sqlplus provides the command spool to save query results to a file. At the SQL> prompt, you say:

spool foo;

and a file called foo.lst will appear in your current directory and will record all user input and system output, until you exit sqlplus or type:

spool off;

Note that if the file foo.lst existed previously, it will be overwritten, not appended.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-Share Alike 2.5 License.