How do you create tables in Oracle

Introduction to ORACLE

Status: November 13, 1999

Application for a user ID

The prerequisite for using ORACLE is a UNIX ID for the computer center of the Trier University of Applied Sciences. You apply for an ORACLE user ID with Prof. Dr. Klösener or Prof. Dr. Capricorn.

Register with ORACLE

By logging in with your UNIX ID from the data center, you open a session on the Trier University of Applied Sciences' database computer.

You log in to ORACLE with (if you have not been explicitly assigned a password):

sqlplus / and if you have received a password with: sqlplus You will then be asked for your password. Avoid entering in the (also permitted) form: sqlplus / because your password is then accessible to every other user via the UNIX ps command. is the name of the ORACLE program that allows you to edit an ORACLE database with the help of SQL commands.

You will then receive the prompt:


Log out of ORACLE

To exit sqlplus enter SQL> exit; a.

Create a table

You can execute any SQL command in. To create a table, enter the following command: CREATE TABLE (); You can enter them in one or more lines. If your command extends over several lines, after pressing the Enter key you will receive the next line number as a prompt until you complete the command with a semicolon.

Danger: An empty line ends the command but does not execute it. In this case you can use

SQL> r [or SQL> run] [or SQL> /]

the SQL command can be executed; With

SQL> l [or SQL> list]

the last entered SQL command is displayed again.

An example table is created with:

create table student (matriculation number (6), name char (20), first name varchar2 (30), date_of_enrollment date); Note that SQL commands case insensitive are, then means the same as. This command creates a table with names and four attributes. The first is the integer, which consists of a maximum of six digits. Then comes with 20 alphanumeric characters (if less than 20 characters are assigned to this field, it is padded right-justified to twenty places with blanks). The can contain a maximum of 30 characters; However, only the characters entered are saved here. (This has the data type date, which only allows dates that are valid in the calendar.

Deleting tables

A table is deleted with: DROP TABLE ; In the example you delete the table with:

DROP TABLE student;

Inserting sentences

Records can be inserted into an existing table with INSERT INTO () VALUES (); For example, the tuple (123456, 'McKinnock', 'Hugo', '01-OCT-97') can be inserted into the table by: INSERT INTO student (matriculation no, first name, name, date of enrollment) VALUES (123456, 'Hugo' , 'McKinnock', '01-OCT-97'); Character values ​​(and dates) must be enclosed in single quotation marks, numeric values ​​do not. The values ​​within the quotation marks differ from the SQL commands case-sensitive !

Note: The order of surname and first name were reversed (compared to the create of the table); Of course, this only makes sense if the order of the attribute values ​​in the list is behind the same. Entering is shorter and more convenient if you leave the list behind the table name. However, the order of this list is then used as it was entered in the create table statement; however, if the database administrator has planned a different sequence when restoring a data backup, scrap data may accumulate during the insert.

INSERT INTO student VALUES (123456, 'Hugo', 'McKinnock', '01-OCT-97'); is allowed, but causes the insertion of a person with the surname 'Hugo' and the first name 'McKinnock'. You should only use this form of input if you are absolutely certain that the order has not changed. (Using this form in programs with embedded SQL commands is therefore grossly negligent.)

View information from tables

The tuples that are stored in a relation can be viewed with: SELECT * FROM ; For example in the example (after the create and the first insert) SELECT * FROM student results; the result: MATRICLE NO NAME FIRST NAME DATE_DER ---------- --------- ------- --------- 123456 McKinnock Hugo 01-OCT-97 Instead of the wildcard * behind which all attributes are displayed, there can also be a list of attributes, eg: SELECT first name, last name FROM student; would result in: FIRST NAME ---------- --------- Hugo McKinnock

Information about your database

ORACLE provides you with tables that provide you with information about your database. To find out all tables (and other objects) that have been created in your user ID, you can enter: SELECT TABLE_NAME FROM USER_TABLES; An older form of this query (which was already available before ORACLE8) is: SELECT * FROM tab; However, not only the tables are displayed here, but also VIEWS, SYNONYME .. (for these database objects there are meanwhile the relations USER_VIEWS, USER_SYNONYMS ...), which can be recognized by the attribute TABTYPE.

You can determine the structure of a table (i.e. name of the attributes, data type and the fact whether there is a requirement to enter values ​​in this field -NOT NULL in contrast to the default setting NULL) by:

] In the example, SQL> DESC student; the output: NAME NULL? TYPE ----------------------- -------- ------------ matriculation number (6) name char (20) first name varchar2 (30) datum_der_einschreibung date If there was an entry in the column, the associated attribute is a mandatory input field.

Batch execution of SQL commands

Every now and then it is desirable to send SQL commands from a file to the database instead of the interactive input on the screen (for example when inserting several tuples in "one go").

Sign in again with

sqlplus / or sqlplus at ORACLE; If the file to be executed (with the extension .sql) is in the directory from which you started sqlplus, you can execute the SQL commands contained therein with you (otherwise you have to put the path name in front of the file name and with the different extension : SQL> start If, for example, several students are to be added to the table in one step, the file could be created in your home directory with the following content: INSERT INTO student (matriculation number, first name, name, date of enrollment) VALUES (123456, 'Hugo', 'McKinnock', '01-OCT-97'); INSERT INTO student (matriculation number, first name, name, date of enrollment) VALUES (234567, 'Lisa', 'Jansen', '01-OCT-97'); INSERT INTO student (matriculation number, first name, name, date of enrollment) VALUES (234567, 'Anja', 'Schmidt', '01-OCT-97') / Please note that the final semicolon is missing in the third insert and at the beginning of the last line there is a slash.

The three sentences are then inserted into the table with the following command:

SQL> start input

Edit SQL command

The last SQL command used is in the SQL buffer. It can be executed through SQL> r [un] or SQL> /

The command can be edited before execution with the built-in (rudimentary) editor in sqlplus. (But this editor also runs identically on all platforms!) Like SQL commands, the commands are not case-sensitive.

  1. L (completely lists the command in the buffer).
  2. L n (only reproduces line n of the command; also makes this line the current Line - recognizable by the preceding *). (Instead of L n, it is also sufficient to enter n alone)
  3. L n , m (reproduces line n through line m).
  4. I (gives you a new input line following the current line; the lines that previously followed the current line are appended afterwards). You end your entry by pressing the button twice.
  5. C / old / new (replaces the first Occurrence of the character string 'old' through 'new' in the current line). (Contains 'old' or 'new' but e.g. the slash / you should use the form
  6. A text (appends 'text' to the end of the current line).
  7. DEL (deletes the current line).
  8. n text (replaces line n with 'text'; 0 text inserts a line with 'text' before the previous line 1)
ORACLE also allows the use of an editor defined by you. You define him through

SQL> define _editor =

If the editor is not in your path, the complete path must of course also be given. For example, if you want to use this, enter:

SQL> define _editor = 'vi'

If you now want to edit the last SQL command used, enter:

SQL> edit

Your editor will open and load your SQL command. After you have finished editing, have your editor save. After exiting the editor, you will automatically return to. Now execute the modified command with SQL> r [un] or SQL> /

Log your session

Inside of use the command:

SQL> spool


SQL> spool liste.lst

Everything that is written on the screen in your session (including your entries) is logged in the ASCII file liste.lst. The file is saved in the directory from which you called sqlplus. The log ends with:

SQL> spool off

HOST command

It is possible to issue operating system commands from within your sqlplus session without exiting.


SQL> host [operating system command]

the operating system command is executed and you get back the SQL> prompt.


SQL> host pwd

you will e.g. receive information about the current directory. If you omit the operating system command, you will get an operating system shell from which you will return to the SQL> prompt after entering.

Session settings

Settings such as the output formatting of SQL commands are set with SET for the duration of the sqlplus session. The current settings are displayed with SHOW ALL. Settings and also SQL commands contained in the login.sql file are executed when sqlplus is started if this file is contained in the directory from which sqlplus is started.

SQL> set pagesize 40

SQL> set linesize 120

SQL> set numwidth 7

SQL> set pause on

SQL> set pause "continue with any key ..."

This sets the page length to 40 lines, the line length to 120 characters, the width of numeric columns to 7 digits and a pause after each page with the message 'continue with any key ...'.

SQL> set long 1000

Is necessary if more than 80 characters (this is the default) are to be displayed for a column with the data type LONG (e.g. the definition of a view, the definition of a check condition or the source of a procedure in the data dictionary).


The complete ORACLE documentation version 8 is located on the dublin computer both in html form and as a pdf file.

Start on your computer with the URL

(In the directory / usr / oracle_neu / doc / on the dublin there is also the file index.pdf for the acrobat reader, if you want to print out parts of the documentation.)


You can save your database objects (tables etc.) in a file (with ORACLE's internal format). This file can also be imported into another ORACLE database (e.g. on your PC) if the version there is 8.1.5 or higher. A 1.44MB floppy disk is sufficient for storage of seminar papers and most diploma theses.

exp userid = / (or userid = ) [file = ]

saves all of your database objects in the file filename in the directory from which you called. The default for the file name is expdat.dmp (dmp stands for dump). With

exp userid = / (or userid = ) file = export.dmp

create the export file export.dmp.

You can obtain all parameters for the export program from:

exp help = yes

If you just enter after the Unix prompt, you will get an interactive export session.

If you want to further process a small part of your data with other tools (e.g. a word processing or another data storage system), you can use the command within (see Logging your session).

With the command sequence:

SQL> spool liste.lst

SQL> set heading off

SQL> set pagesize 0

(This avoids the column headings and blank lines for page breaks.)

SQL> select * from emp;

SQL> spool off

create a file liste.lst in which the required information is stored in ASCII format.


You can also import a file that has been exported into another ORACLE database with at least version 8.1.5. (You can easily import the export file of older ORACLE versions such as ORACLE7 in higher versions.)

imp userid = / (or userid = ) full = yes [file = ]

The default file name is again expdat.dmp. The above command imports the entire content of the exported file. If you only want to import some of the exported tables, use the parameter instead of


imp userid = / (or userid = ) tables = (emp, dept) file = export.dmp

only the tables and are imported from the export file export.dmp.

You can see all parameters of the command again with:

imp help = yes

Do you want to change parameters for the tables before importing, e.g. change the storage clauses - use the parameter indexfile = .


imp userid = / (or userid = ) full = yes indexfile = temp.sql file = export.dmp

just create an ASCII file - without performing the import - in which all, commands, etc. are located, as they were based on the exported tables. You can send the file temp.sql you have modified against the database (see Batch Execution of SQL Commands).

Load data with SQL * LOADER

Larger databases can be loaded with the ORACLE tool. Are there about thousands of student records in the ASCII file eingabe.dat in the form that the individual fields are separated by commas 123456, Mayer, Efriede, 01-10-1996 234567, Mustermann, Anja, 01-10-1997 345678, Schmidt, Hugo, 01-10-1995:: you can create a control file (e.g. with the name control.dat). This could then have the following content: LOAD DATA INFILE 'eingabe.dat' INTO TABLE student FIELDS TERMINATED BY ',' (matriculation number, name, first name, date_of_enrollment DATE "dd-mm-yyyy") Explanations:
  • (Line 1): LOAD DATA is always at the beginning of an SQL * LOADER control file
  • (Line 2): INFILE indicates that the data records are in a file called file name
  • (Line 3): INTO TABLE means that the data records are to be inserted into the existing (but empty) table. (If the new rows are to be added to the rows that already exist in the table, the clause must be APPEND INTO TABLE.)
  • (Line 4): FIELDS TERMINATED BY ',' means that the fields in the file are separated by commas.
  • (Line 5): Here the input fields are assigned to the table fields taking into account the order. The DATE "dd-mm-yyyy" clause specifies the format in which dates are in .
Note: If there are blanks in the input data, they are also transferred to the database. For example, if the first input line were 123456, Mayer, Efriede, 01-10-1996 (with a blank between the first comma and the name Mayer), the person in the database would also be called 'Mayer' instead of 'Mayer'

You call the SQL * LOADER as follows:

sqlldr userid = / (or userid = ) control = control.dat log = logfile.log

If the input data are in fixed positions, the control file can be adjusted. See the documentation for details.