Use an Oracle External table to view related alarm information

The following articles mainly introduce how to correctly use Oracle External tables to view related alarm information. First, we need to understand the actual application of Oracle External tables, and the differences from other tables, so we need to create a simple Oracle External table (mainly based on the operation process), and 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 Oracle 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

The test is successful.

 
 

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

You can query the OS file data in the database. The above content is an introduction to the use of Oracle External tables to view alarm information. I hope you will gain some benefits.