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 (“).
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“.