Create an Oracle table

Here is an example on how to create a table in Oracle.

The table consists of two columns, an ID and a description.

Both columns are mandatory and the ID column has been set as the primary key.

CREATE TABLE NEW_TABLE
(
  id           NUMBER                           NOT NULL,
  description  VARCHAR2(100)                    NOT NULL
);

ALTER TABLE ONLINEMASTER.NEW_TABLE ADD (
  CONSTRAINT NEW_TABLE_PK
 PRIMARY KEY
 (id));

The next step might be to grant certain privileges on the table.

Grant privileges on an Oracle table to a specific user or role

When you create an object, you have full privileges over that object. If you want to grant specific privileges to another user, or role, then this should explain how to do it.

This example grants the ability to select on a table called TEST_TABLE to the user called JSMITH:

GRANT SELECT ON TEST_TABLE TO JSMITH;

Here is the full list of possible privileges for an Oracle table.

Privilege Description
Select Ability to query the table.
Insert Ability to add new rows to the table.
Update Ability to update rows in the table.
Delete Ability to delete rows from the table.
References Ability to create a constraint that refers to the table.
Alter Ability to change the table definition.
Index Ability to create an index on the table.

Alternatively you could grant full privileges by using the following:

GRANT ALL ON test_table TO JSMITH;

You can of course do the opposite and revoke privileges.

Creating an external table

It is possible to create a table based on a csv file using the following example.

 CREATE TABLE EXT_TABLE_EXAMPLE 
   (	"COL_A" VARCHAR2(250 BYTE), 
	"COL_B" VARCHAR2(250 BYTE), 
	"COL_C" VARCHAR2(250 BYTE), 
	"COL_D" VARCHAR2(250 BYTE)	
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "DIR"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
    BADFILE     DIR:'EXT_TABLE_EXAMPLE.bad'
    LOGFILE     DIR:'EXT_TABLE_EXAMPLE.log'
    DISCARDFILE DIR:'EXT_TABLE_EXAMPLE.dis'
 
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    missing FIELD VALUES are NULL
      )
      LOCATION
       ( 'example.csv'
       )
    )
   REJECT LIMIT UNLIMITED;

The example assumes that you have placed example.csv within the Oracle directory called DIR.