External Tables in database

[Cayden]

Hello dear !

I heard about the database tables but I got a new concept which I am not getting exactly and the concept is "External tables".It is already created by database or user creates this table manually.What is the use of this table and how to create and manage it.Don't forget to explore the advantages and disadvantages.You help would be appreciated.

Thank you.

[Praetor]

[B][External Tables in database :/B]

An external table provides a way tom insert the data in a regular table using the data file of database and it can be achieved without executing the Sql Loader.

In case, if a data file contains more space,it will perform the load operation in parallel manner and the external table is created by INSERT statement.The DML operations are not allowed on external tables and it supports only SELECT statement.

[Katty]

Creating External Tables:

The external table would be created in the following procedure :

1- create a database directory and map it with local PC directory with Admin privilege.
2- Grant the read and write privilege on database directory to user.
3- Now you are ready to create External table and select database directory as default directory and perform the given steps :

• CREATE OR REPLACE DIRECTORY My_load_dir AS 'C:\myexttable';
• GRANT CREATE ANY DIRECTORY to user_name
• Grant DROP ANY DIRECTORY to user_name

[kelfro]

Creating External Tables :

Now the further steps should be taken in the grant section of the external table and the steps would be as follows :

Grant the read and write access to the user specified.
1- GRANT READ ON DIRECTORY My_load_dir TO user_name;
2- GRANT WRITE ON DIRECTORY My_load_dir TO user_name;

create table ext_table_csv (
 a  Number,
  b   Varchar2(20),
  c   Varchar2(20)
)
organization external (
  type              oracle_loader
  default directory ext_dir
  access parameters (
    records delimited  by newline
    fields  terminated by ','
    missing field values are null
  )
  location ('file.csv')
)
reject limit unlimited;

[Zecho]

Reading alert.log file :

The alert.log file store the every actions performed on the database with the time stamp information.There are so many operations are performed when database is initially open and under operation.

The Alert.log file contains the startup,shut down and block corruption information is also stored,you can access the information of alert.log file using the below written command :

select value from v$parameter where name = 'background_dump_dest';