Solution 2: Use an Oracle External table to view related alarm information

If you want to better use Oracle External tables to view related alarm information, you must first understand the concept of Oracle External tables and the differences with other tables, to create a relatively simple Oracle External table (mainly depends on the operation process), finally we use the External table to view the Oracle alarm log.

1. Understand Oracle External tables

External table definition: The structure is stored in the data dictionary, and the table data is stored in the OS file.

Purpose: query the OS file data in the database and load the OS file data to the database.

Differences from other tables: you cannot perform DML operations on External tables, nor create indexes on External tables. You can only perform select operations.

2. Create a simple External table

1. Create an OS file

Because Oracle External tables are mainly used to view files on the OS, a file is first created on the OS.

 
 

  
  
mkdir -p /Oracle/ext   
  
  
vi /Oracle/ext/ext.dat   
  
  
10,20,30   
  
  
40,50,60   
  
  
70,80,90  
 
 

2. Grant user permissions and create directory objects

Create a new user

 
 

  
  
create user test identified by “123” default tablespace test quota unlimited on test;  
 
 

User authorization

 
 

  
  
SQL> grant create any directory to test;  
 
 

Create a directory object

 
 

  
  
SQL> conn test / 123   
  
  
Connected.   
  
  
SQL> create directory ext as '/Oracle/ext';   
  
  
Directory created.  
 
 

3. Create an Oracle External table

 
 

  
  
SQL> create table exttable(   
  
  
id number,name varchar2(10),i number   
  
  
)organization external   
  
  
(type Oracle_loader   
  
  
default directory ext   
  
  
Access parameters   
  
  
(records delimited by newline   
  
  
fields terminated by ','   
  
  
)location('ext.dat')   
  
  
);  
 
 

4. Test

 
 

  
  
SQL> select * from exttable;   
  
  
ID NAME I   
  
  
---------- ---------- ----------   
  
  
10 20 30   
  
  
40 50 60   
  
  
70 80 90  
 
 

The test is successful. You can view the OS file data in the database.