CREATE TABLE IN ORACLE - Online Tech Support

How to use Oracle Create Table

online tech support Comments Off

Latest Oracle Create Table hints and tips from Online Tech Support. How to use Oracle Create Table and gain better performance and/or how to write the syntax for Oracle Create Table.

The Oracle create table has quite many options and depending on them the table may act very different ways. For example when you will create a temporary table then all inserted rows will be kept while the session is alive. That does mean after closing the Oracle database connection the global temporary table will be cleaned up and once you are logging on the database with a new session the temporary table will be empty again. This Oracle temporary table can be created to keeping data after doing commit (ON COMMIT PRESERVE ROWS) or trashing the lines (by default). The simple statement of creating temporary table is following:

CREATE GLOBAL TEMPORARY TABLE sales_temp
  ( id         NUMBER(17)  NOT NULL,
    sale_date  DATE        NOT NULL,
    sale_month VARCHAR2(6) NOT NULL,
    product_id NUMBER(17)  NOT NULL 
  ) ON COMMIT PRESERVE ROWS;

The next we will demonstrates how to create a table that does not keep the data with commit. Take a look at the script below and as you see the last keyword has changed to “ON COMMIT DELETE ROWS“.

CREATE GLOBAL TEMPORARY TABLE sales_temp
 ( id NUMBER(17) NOT NULL,
   sale_date DATE NOT NULL,
   sale_month VARCHAR2(6) NOT NULL,
   product_id NUMBER(17) NOT NULL 
  ) ON COMMIT DELETE ROWS;

The third example is about of how to create table in Oracle is using table “SALES” that contains daily basis sale information and we are working with data depending on the sale dates. Even the syntax looks very similar to the temporary table there are some differences. Take a close look and compare the statements. The Oracle Create Table statement is as following:

CREATE TABLE sales
 ( id         NUMBER(17)  NOT NULL,
   sale_date  DATE        NOT NULL,
   sale_month VARCHAR2(6) NOT NULL,
   product_id NUMBER(17)  NOT NULL );

We suggest to use Oracle Partition for big tables. This way your data in oracle tables will more easily removed or re-indexed. For partition oracle computer help suggest to use local indexes instead of global index. Oracle local index is created automatically per partition and adding or removing the table partition in oracle you would not need to rebuild the indexes.

The third type of tables the computer help recommends to know is the oracle external table type. The external table oracle is for to import data from external sources like csv file types. The oracle external table is using ORACLE_LOADER access driver to load in the csv file. This way importing data from external source is one of the fastest way of importing and this highly used in oracle warehouse systems. The statement for to create external table oracle is:

CREATE TABLE sales_extern (
    id         NUMBER(17),
    sale_date  DATE,
    sale_month VARCHAR2(2000),
    product_id NUMBER(17) 
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
 DEFAULT DIRECTORY import_files
 ACCESS PARAMETERS
 (
  RECORDS DELIMITED BY newline
  BADFILE 'import_sales.bad'
  DISCARDFILE 'import_sales.dis'
  LOGFILE 'import_sales.log'
  SKIP 1
  FIELDS TERMINATED BY ","  OPTIONALLY ENCLOSED BY '"'
  (
    id         INTEGER EXTERNAL(17),
    sale_date  CHAR(10) date_format DATE mask "dd/mm/yyyy",
    sale_month CHAR(2000),
    product_id INTEGER EXTERNAL(17) 
  )
 )
 LOCATION ('import_sales.csv')
)
REJECT LIMIT UNLIMITED;

The create external table oracle statement you can the same structure as the other tables above. Online tech support would like to point out some parameter like the import_sales.csv should be located in oracle directory named import_files. The directory in oracle should have read-write permission to user oracle since the log and bad (“error”) files will be written in it. The oracle external table will skip 1st line as usually it is column names and the value is separated with coma (,) and text can be in quotes (“).

Important to know:

While you tried to create the external table and it returned error ORA-06564: object IMPORT_FILES does not exist then that does mean you didn’t create Oracle directory IMPORT_FILES and you would need to do it first before creating the table.

To create a directory in Oracle for Unix or Linux environment use the following command:

create directory import_files as '/tmp';

And this example is for Windows environment:

create directory import_files as 'C:\Temp';

Now the sales_extern table has been created and you may try to see what is inside it. This Oracle external table is a table like any other so to the the data inside we are using the Oracle Select statement as on the following example:

SELECT * FROM sales_extern;

When you did run the SQL query Oracle will return the error above. The following error is raised because you don’t have the “import_sales.csv” in the directory you have just created.

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file import_sales.csv in IMPORT_FILES not found

To fix the error just create empty file “import_sales.csv” into your directory “/tmp” or “C:\Temp“. When you will try again the Select statement the output should be following:

To see some data just insert the following lines into your empty file and run the query again.

ID,Sale Date,Sale Month,Product_id
1,01/02/2012,”February”,14
2,03/03/2012,”March”,14
3,20/08/2013,”August”,13

As you see from the output all lines except the 1st line appeared in the query. We did declare in the external table source that it should always skip the first line “SKIP 1“.

Home

How To Write Oracle Alter Table Statement

online tech support Comments Off

This tutorial page is based on examples to be easier to follow. Statement Oracle Alter Table allows you to amend Oracle table properties. The alter table in Oracle does change the table parameters, table partitions or sub-partitions. Also you’ll be able to modify table columns, column size or type. The basic Oracle Alter Table syntax is:

ALTER TABLE <table_name> <changing conditions>;

To go through the following Alter Table examples we would need a table. This Oracle Create Table statement will use a Select statement to create an empty table named CUSTOMERS. The table has only one column named “ID“. The table will be created as an empty one because the examples will modify table properties mainly.

CREATE TABLE customers AS
 SELECT rownum AS ID
   FROM dual
  WHERE 1 = 2;

The first syntax is about how to add a new column to the table. Take a look at the syntax because we will use it in the following example.

ALTER TABLE <table_name>
      ADD <column_name> <column_type> [<additional column conditions>];

This example based on the syntax above will add a new column named “SURNAME” to table CUSTOMERS. The column data type is VARCHAR2 and length 500 characters. The following “NOT NULL” constraint is added because every our customer has a surname and with that condition we will set it as mandatory. The “NOT NULL” condition does not have to be added and then the column can be left empty – without any value.

ALTER TABLE customers
      ADD surname VARCHAR2(500) NOT NULL;

To add more than one column the syntax looks almost the same, only there will be more columns in one statement.

ALTER TABLE <table_name>
  ADD ( <column_name_1> <column_type> [<additional column conditions>],
        <column_name_2> <column_type> [<additional column conditions>],
        ...
        <column_name_n> <column_type> [<additional column conditions>]);

The next example will add three new columns named “FORENAME“, “DATE_OF_BIRTH” and “EMAIL“. The upper two columns are mandatory and the “EMAIL” can be left empty. The “FORENAME” and “EMAIL” are text columns VARCHAR2 and the “DATE_OF_BIRTH” is a date type (“DATE“).

ALTER TABLE customers
      ADD ( forename      VARCHAR2(500) NOT NULL,
            date_of_birth DATE          NOT NULL,
            email         VARCHAR2(500)           );

After executing this statement you should have 5 columns in the table: “ID“, “SURNAME“, “FORENAME“, “DATE_OF_BIRTH” and “EMAIL“.

The third example is about how to amend your column setting. You can change your column data type or the size amount when it has no data. This statement also allows adding or removing constraints and restrictions. The Oracle Alter Table syntax for MODIFY is following.

ALTER TABLE <table_name>
      MODIFY <column_name> [<column_type>] [<additional column conditions>];

In this example we will reduce the size of column “FORENAME” from existing 500 to 200 byte. Rest of the columns will remain as is.

ALTER TABLE customers
  MODIFY forename VARCHAR2(200);

The fourth statement will rename a column name and the syntax is below.

ALTER TABLE <table_name>
  RENAME COLUMN <column_name> TO <new_column_name>;

The example of renaming a column will change “DATE_OF_BIRTH” column to “DOB” and rest of the column settings will remain as they were before.

ALTER TABLE customers
  RENAME COLUMN date_of_birth TO dob;

Now has left to try to remove a column from the table. The keyword for removing is “DROP COLUMN“. Once the column is removed it will be quite complicated to get it back so be very careful with this statement.

ALTER TABLE <table_name>
      DROP COLUMN <column_name>;

The example below will remove the “SURNAME” column from table CUSTOMERS and to do it we are using the Oracle Alter Table statement as all examples above.

ALTER TABLE customers
      DROP COLUMN surname;

Besides renaming the table columns you also can rename the table name and the keyword is “RENAME TO“. Renaming tables can be quite useful to do switching and replace existing table with an empty one. The meaning of this action is to empty a big table from the data. The Oracle TRUNCATE TABLE statement can remain slow for a very big table and the fastest way to empty is replacing it with an empty table and dropping the existing one.

ALTER TABLE <table_name>
  RENAME TO <new_table_name>;

The following statement will rename table CUSTOMERS to CLIENTS. After executing this statement all your SQL queries need to be written to table CLIENTS.

ALTER TABLE customers
  RENAME TO clients;

To rename a table will not erase the data. It only renames the table name and this statement is not as dangerous as DROP COLUMN because you can always rename the table back as it was before chaining the name. It’s important to know that renaming a table will put your existing code into invalid state either you will need to recompile it or do some tailoring.

See Also:
Oracle Select Home